ADO.
NET DataTable
DataTable represents relational data into tabular form. ADO.NET provides a DataTable
class to create and use data table independently. It can also be used with DataSet also.
Initially, when we create DataTable, it does not have table schema. We can create table
schema by adding columns and constraints to the table. After defining table schema, we
can add rows to the table.
We must include System.Data namespace before creating DataTable.
DataTable Class Signature
1. public class DataTable : System.ComponentModel.MarshalByValueComponent, Sys
tem.ComponentModel.IListSource,
2. System.ComponentModel.ISupportInitializeNotification, System.Runtime.Serializatio
n.ISerializable,
3. System.Xml.Serialization.IXmlSerializable
DataTable Constructors
The following table contains the DataTable class constructors.
Constructors Description
It is used to initialize a new instance of the
DataTable()
DataTable class with no arguments.
It is used to initialize a new instance of the
DataTable(String)
DataTable class with the specified table name.
It is used to initialize a new instance of the
DataTable(SerializationInfo,
DataTable class with the SerializationInfo and
StreamingContext)
the StreamingContext.
It is used to initialize a new instance of the
DataTable(String, String) DataTable class using the specified table
name and namespace.
DataTable Properties
The following table contains the DataTable class properties.
Property Description
It is used to get the collection of columns that
Columns
belong to this table.
It is used to get the collection of constraints
Constraints
maintained by this table.
It is used to get the DataSet to which this table
DataSet
belongs.
It is used to get a customized view of the table
DefaultView
that may include a filtered view.
It is used to get a value indicating whether there
HasErrors are errors in any of the rows in the table of the
DataSet.
It is used to get or set the initial starting size for
MinimumCapacity
this table.
It is used to get or set an array of columns that
PrimaryKey
function as primary keys for the data table.
It is used to get the collection of rows that belong
Rows
to this table.
TableName It is used to get or set the name of the DataTable.
DataTable Methods
The following table contains the DataTable class methods.
Method Description
It is used to commit all the changes made to
AcceptChanges()
this table.
Clear() It is used to clear the DataTable of all data.
It is used to clone the structure of the
Clone()
DataTable.
It is used to copy both the structure and data
Copy()
of the DataTable.
It is used to returns a DataTableReader
CreateDataReader() corresponding to the data within this
DataTable.
It is used to create a new instance of
CreateInstance()
DataTable.
GetRowType() It is used to get the row type.
GetSchema() It is used to get schema of the table.
It is used to copy a DataRow into a
ImportRow(DataRow)
DataTable.
It is used to fill a DataTable with values from a
Load(IDataReader)
data source using the supplied IDataReader.
It is used to merge the specified DataTable
Merge(DataTable, Boolean)
with the current DataTable.
It is used to create a new DataRow with the
NewRow()
same schema as the table.
It is used to get an array of all DataRow
Select()
objects.
It is used to write the current contents of the
WriteXml(String)
DataTable as XML using the specified file.
DataTable Example
Here, in the following example, we are creating a data table that populates data to the
browser. This example contains the following files.
// DataTableForm.aspx
1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehin
d="DataTableForm.aspx.cs"
2. Inherits="DataTableDemo.DataTableForm" %>
3. <!DOCTYPE html>
4. <html xmlns="http://www.w3.org/1999/xhtml">
5. <head runat="server">
6. <title></title>
7. </head>
8. <body>
9. <form id="form1" runat="server">
10. <div>
11. </div>
12. <asp:GridView ID="GridView1" runat="server">
13. </asp:GridView>
14. </form>
15. </body>
16. </html>
CodeBehind
// DataTableForm.aspx.cs
1. using System;
2. using System.Collections.Generic;
3. using System.Data;
4. using System.Linq;
5. using System.Web;
6. using System.Web.UI;
7. using System.Web.UI.WebControls;
8. namespace DataTableDemo
9. {
10. public partial class DataTableForm : System.Web.UI.Page
11. {
12. protected void Page_Load(object sender, EventArgs e)
13. {
14. DataTable table = new DataTable();
15. table.Columns.Add("ID");
16. table.Columns.Add("Name");
17. table.Columns.Add("Email");
18. table.Rows.Add("101", "Rameez","rameez@example.com");
19. table.Rows.Add("102", "Sam Nicolus", "sam@example.com");
20. table.Rows.Add("103", "Subramanium", "subramanium@example.com");
21. table.Rows.Add("104", "Ankur Kumar", "ankur@example.com");
22. GridView1.DataSource = table;
23. GridView1.DataBind();
24. }
25. }
26. }
Output:
C# Public Access Specifier Example
1. using System;
2.
3. namespace AccessSpecifiers
4. {
5. class PublicTest
6. {
7. public string name = "Santosh Singh";
8. public void Msg(string msg)
9. {
10. Console.WriteLine("Hello "+ msg);
11. }
12. }
13.
14. class Program
15. {
16. static void Main(string[] args)
17. {
18. PublicTest publicTest = new PublicTest();
19. // Accessing public variable
20. Console.WriteLine("Hello "+publicTest.name);
21. // Accessing public method
22. publicTest.Msg("Peter Dicosta");
23. }
24. }
25. }
ADO.NET Web Form Example
We can create a web form that has ADO.NET connectivity. A simple web form that has
form controls can be submitted to the server. ADO.NET allows us to store the submitted
values to store into SQL Server database.
Here, we are creating a web form application that connects to the SQL Server database.
This web form contains the following source code.
WebForm
// WebFormAdoNet.aspx
1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehin
d="WebFormAdoNet.aspx.cs"
2. Inherits="ado.netWebFormExample.WebFormAdoNet" %>
3. <!DOCTYPE html>
4. <html xmlns="http://www.w3.org/1999/xhtml">
5. <head runat="server">
6. <title></title>
7. <style type="text/css">
8. .auto-style1 {
9. width: 100%;
10. }
11. .auto-style2 {
12. width: 100px;
13. }
14. .auto-style3 {
15. width: 95px;
16. }
17. </style>
18. </head>
19. <body>
20. <form id="form1" runat="server">
21. <div>
22. <table class="auto-style1">
23. <tr>
24. <td class="auto-style2">
25. <asp:Label runat="server" Text="User Name" ID="usernamelabelId">
</asp:Label></td>
26. <td>
27. <asp:TextBox ID="UsernameId" runat="server"></asp:TextBox></td>
28. </tr>
29. <tr>
30. <td class="auto-style2">
31. <asp:Label runat="server" Text="Email ID"></asp:Label></td>
32. <td>
33. <asp:TextBox ID="EmailId" runat="server"></asp:TextBox></td>
34. </tr>
35. <tr>
36. <td class="auto-style2">
37. <asp:Label runat="server" Text="Contact"></asp:Label></td>
38. <td>
39. <asp:TextBox ID="ContactId" runat="server"></asp:TextBox></td>
40. </tr>
41. <tr>
42. <td class="auto-style2"></td>
43. <td>
44. <asp:Button ID="ButtonId" runat="server" Text="Submit" OnClick="Bu
ttonId_Click" /></td>
45. </tr>
46. </table>
47. </div>
48. <div>
49. <asp:Label ID="Label1" runat="server"></asp:Label>
50. </div>
51. </form>
52. <table class="auto-style1">
53. <tr>
54. <td class="auto-style3">
55. <asp:Label ID="Label2" runat="server"></asp:Label></td>
56. <td>
57. <asp:Label ID="Label5" runat="server"></asp:Label></td>
58. </tr>
59. <tr>
60. <td class="auto-style3">
61. <asp:Label ID="Label3" runat="server"></asp:Label></td>
62. <td>
63. <asp:Label ID="Label6" runat="server"></asp:Label></td>
64. </tr>
65. <tr>
66. <td class="auto-style3">
67. <asp:Label ID="Label4" runat="server"></asp:Label></td>
68. <td>
69. <asp:Label ID="Label7" runat="server"></asp:Label></td>
70. </tr>
71. </table>
72. </body>
73. </html>
CodeBehind
// WebFormAdoNet.aspx.cs
1. using System;
2. using System.Data.SqlClient;
3. namespace ado.netWebFormExample
4. {
5. public partial class WebFormAdoNet : System.Web.UI.Page
6. {
7. protected void Page_Load(object sender, EventArgs e)
8. {
9. }
10. protected void ButtonId_Click(object sender, EventArgs e)
11. {
12. SqlConnection con = null;
13. try
14. {
15. // Creating Connection
16. con = new SqlConnection("data source=.; database=student; integrated s
ecurity=SSPI");
17. // Writing insert query
18. string query = "insert into student(name,email,contact)values('"+Usernam
eId.Text+ "',
19. '" + EmailId.Text + "','" + ContactId.Text + "')";
20. SqlCommand sc = new SqlCommand(query,con);
21. // Opening connection
22. con.Open();
23. // Executing query
24. int status = sc.ExecuteNonQuery();
25. Label1.Text = "Your record has been saved with the following details!";
26. // ----------------------- Retrieving Data ------------------ //
27. SqlCommand cm = new SqlCommand("select top 1 * from student", con);
28. // Executing the SQL query
29. SqlDataReader sdr = cm.ExecuteReader();
30. sdr.Read();
31. Label2.Text = "User Name"; Label5.Text = sdr["name"].ToString();
32. Label3.Text = "Email ID"; Label6.Text = sdr["email"].ToString();
33. Label4.Text = "Contact"; Label7.Text = sdr["contact"].ToString();
34. }
35. catch (Exception ex)
36. {
37. Console.WriteLine("OOPs, something went wrong." + ex);
38. }
39. // Closing the connection
40. finally
41. {
42. con.Close();
43. }
44. }
45. }
46. }
Output:
It produces the following output to the browser.
Fill the form and submit data.
After submitting, it store and retrieve the data from the SQL Server database.
