Friday, 30 December 2016

How to update, insert and delete record in DataTable with C#

In this article, I will explain you how to perform update, select and delete record in DataTable with C#.

Creating DataTable

First of all, we need to create a DataTable. Below is the code to create a DataTable with some dummy records. Here, First two records are having EmpId 1.

DataTable dt = new DataTable();
dt.Columns.Add("EmpId", typeof(Int32));
dt.Columns.Add("EmpName", typeof(string));
dt.Columns.Add("EmpCountry", typeof(string));
dt.Rows.Add(1, "Rahul", "India");
dt.Rows.Add(1, "John", "USA");
dt.Rows.Add(3, "Mary", "UK");
dt.Rows.Add(4, "Mathew", "Australia");

Select and Update single record using LINQ

Select and Update single record using LINQ

Here we need to select single employee record whose EmpName is Rahul. Below is the code to select and update EmpCountry from India to USA.You can see updated record in the DataSet Visualizer

DataRow dr = dt.AsEnumerable().Where(r => ((string)r["EmpName"]).Equals("Rahul")).First();
dr["EmpCountry"] = "USA";
DataRow after update

You can add multiple condition using && or || operator. Here, I am using FirstOrDefault() which will return first element of the sequence or default value if the sequence is null.

Check is DataRow is not null

DataRow dr = dt.AsEnumerable().Where(x => (string)x["EmpName"] == "Rahul"
                         && ((Int32)x["EmpId"] ==1)).FirstOrDefault();
if (dr != null)
    dr["EmpCountry"] = "USA";

Select and Update list of record using DataTable's Select method

Below is the code to select list of record and update the same.

DataRow[] dr = dt.Select("[EmpId]=4 AND [EmpCountry]='Australia'"); 
dr[0]["EmpCountry"] = "USA";
dr[1]["EmpCountry"] = "USA";

Since, DataRow contains only one record, so it will throw exception while updating second record. We can avoid, datarow row index out of range using for loop.

Avoid Index was outside the bounds of the array

    int EmpId ;
    string EmpName = string.Empty;
    string EmpCountry = string.Empty;
    for (int i = 0; i < dr.Length; i++)
        EmpId = Convert.ToInt32(dr[i]["EmpId"].ToString());
        EmpName = dr[i]["EmpId"].ToString();
        EmpCountry = dr[i]["EmpId"].ToString();

Selecting and converting into List

List<DataRow> dr = dt.AsEnumerable().Where(x => (Convert.ToInt32(x["EmpId"]) == 1)).ToList();

Selecting using foreach loop

foreach (DataRow row in dt.Rows)
    if (row["EmpCountry"].ToString() == "India")
        int EmpId = Convert.ToInt32(row["EmpId"].ToString());
        string Name = row["EmpName"].ToString();                 

Deleting DataTable record using RemoveAt


Deleting a record from DataRow

DataRow dr = dt.AsEnumerable().Where(r => ((string)r["EmpName"]).Equals("Rahul")).First();


Post a Comment

Email Subscription

Subscribe to our newsletter to get the latest articles directly into your inbox

Blog Archive