C# DataTable

last edited on: 19-Jan-2018; tagged: c#,datatable


In C#, a DataTable is a class of System.Data namespace. It stores data in rows and columns as like as a database table. A DataTable is an in-memory representation of data.

Here we create a DataTable with three columns:

DataTable table = new DataTable();
table.Columns.Add("studentId", typeof(int));
table.Columns.Add("Full Name", typeof(string));
table.Columns.Add("DOB", typeof(DateTime));

 

Adding data to a DataTable in various ways:

table.Rows.Add(1, "John",”2001-05-01”);

or this way:

DataRow dr;
dr = table.NewRow();
dr["studentId"] = 2;
dr["Full Name"] = “Flora”;
dr["DOB "] = “2001-02-01”; // Better if we use a DateTime object
table.Rows.Add(dr);

A datatable can be a Data Source of many objects like GridView, Repeater,  DropDownList, ListBox, RadioButtonList etc.

 

GridView                               

gridStudents.DataSource = table;
gridStudents.DataBind();              

 

DropDownList

DropDownList1.DataSource = table;
DropDownList1.DataTextField = "Full Name";
DropDownList1.DataValueField = "StudentId";
DropDownList1.DataBind();
DropDownList1.Items.Insert(0, new ListItem("--Select--", "0"));

 

RadioButtonList

RadioButtonList1.DataSource = table;
RadioButtonList1.DataTextField = "Full Name";
RadioButtonList1.DataValueField = "StudentId";
RadioButtonList1.DataBind();

 

ListBox

ListBox1.DataSource = table;
ListBox1.DataTextField = "Full Name";
ListBox1.DataValueField = "StudentId";
ListBox1.DataBind();

 

Select/Retrive/Extract data from DataTable:

foreach (DataRow row in table.Rows)
{
string StudentId = row["StudentId"].ToString();
string FullName = row["Full Name"].ToString();
string icoFileName = row["DOB"].ToString();
}

 

Edit/update/modify data from DataTable in various ways:

First approach:

foreach (DataRow dr1 in table.Rows) // search whole table
 {
            int StudentId;
            int.TryParse(dr1["StudentId"].ToString(), out StudentId);
            if (StudentId == 2) // if StudentId==2
            {
                dr1["Full Name"] = "New Name"; //Changing the student name               
            }
}

Second approach:

DataRow dr3 = table.Select("StudentId=2").FirstOrDefault(); // This will finds all rows with id==2 and select the first row or null if not found
if (dr3 != null)
{
dr3["Full Name"] = " New Name"; //changes the Student Name
}

 

Deleting data from DataTable:

for (int i = table.Rows.Count - 1; i >= 0; i--)
{
            DataRow row = table.Rows[i];
            if (row["Full Name"] == "New Name")  // or we can use the StudentId
           {
                           row.Delete();
           }
}

 

Sort a C# DataTable:

table.DefaultView.Sort = "Full Name DESC";  // or Full Name ASC
table = table.DefaultView.ToTable(); // Need to retrieve the datatable  after sorting

 

Filter C# DataTable

table.DefaultView. RowFilter= "StudentId =1 ";
table.DefaultView. RowFilter= "StudentId >1 ";
table.DefaultView. RowFilter= "Full Name LIKE '%Jo%'";
table.DefaultView. RowFilter= "Full Name = 'John' AND (StudentId>1)";
table = table.DefaultView.ToTable(); // Need to retrieve