Csharp/CSharp Tutorial/ADO.Net/DataTable

Accessing Data Values in a DataTable or DataSet

using System;
using System.Data;
using System.Data.SqlClient;
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
            string sqlSelect = "SELECT ContactID, FirstName, LastName FROM Person.Contact";
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            DataTable dt = new DataTable( );
            da.Fill(dt);
            for (int i = 0; i < 5; i++)
            {
                DataRow row = dt.Rows[i];
                Console.WriteLine(i);
                Console.WriteLine(row[0]);
                Console.WriteLine(row["FirstName"]);
                Console.WriteLine(row[2, DataRowVersion.Default]);
            }

        }
    }

Accessing FirstName value in row 3 directly

using System;
using System.Data;
using System.Data.SqlClient;
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
            string sqlSelect = "SELECT ContactID, FirstName, LastName FROM Person.Contact";
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            DataTable dt = new DataTable( );
            da.Fill(dt);
            Console.WriteLine("FirstName = {0}", dt.Rows[2][1]);
            Console.WriteLine("FirstName = {0}", dt.Rows[2]["FirstName"]);
            Console.WriteLine("FirstName = {0}",dt.Rows[2]["FirstName", DataRowVersion.Default]);
            Console.WriteLine("FirstName = {0}", dt.Rows[2][dt.Columns[1]]);
            Console.WriteLine("FirstName = {0}",dt.Rows[2][dt.Columns["FirstName"]]);
            Console.WriteLine("FirstName = {0}",dt.Rows[2].Field<string>(1));
            Console.WriteLine("FirstName = {0}",dt.Rows[2].Field<string>("FirstName"));
            Console.WriteLine("FirstName = {0}",dt.Rows[2].Field<string>("FirstName", DataRowVersion.Default));
            Console.WriteLine("FirstName = {0}",dt.Rows[2].Field<string>(dt.Columns[1]));
            Console.WriteLine("FirstName = {0}",dt.Rows[2].Field<string>(dt.Columns["FirstName"]));
        }
    }

Adding a Calculated Column to a DataTable

using System;
using System.Data;
using System.Data.SqlClient;
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
            string sqlSelect = "SELECT TOP 5 * FROM Sales.SalesOrderDetail";
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            DataTable dt = new DataTable();
            da.Fill(dt);
            dt.Columns.Add(new DataColumn("ExtendedPrice", typeof(Decimal),"UnitPrice * (1 - UnitPriceDiscount) * OrderQty"));
            foreach (DataRow row in dt.Rows)
                Console.WriteLine(row["SalesOrderDetailID"]);
        }
    }

Adding a Column to a Child DataTable That Displays Data from the Parent Table

using System;
using System.Data;
using System.Data.SqlClient;
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
            string sqlSelect = @"SELECT * FROM Sales.SalesOrderHeader;SELECT * FROM Sales.SalesOrderDetail;";
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            da.TableMappings.Add("Table", "SalesOrderHeader");
            da.TableMappings.Add("Table1", "SalesOrderDetail");
            da.Fill(ds);
            DataRelation dr = new DataRelation("SalesOrderHeader_SalesOrderDetail",
                ds.Tables["SalesOrderHeader"].Columns["SalesOrderID"],
                ds.Tables["SalesOrderDetail"].Columns["SalesOrderID"]);
            ds.Relations.Add(dr);
            ds.Tables["SalesOrderDetail"].Columns.Add("CustomerID", typeof(int),"Parent(SalesOrderHeader_SalesOrderDetail).CustomerID");
        }
    }

Adding a Column to a Parent DataTable That Aggregates a Child Table"s Column Values

using System;
using System.Data;
using System.Data.SqlClient;
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
            string sqlSelect = @"SELECT * FROM Sales.SalesOrderHeader;SELECT * FROM Sales.SalesOrderDetail;";
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            da.TableMappings.Add("Table", "SalesOrderHeader");
            da.TableMappings.Add("Table1", "SalesOrderDetail");
            da.Fill(ds);
            // Relate the Header and Order tables in the DataSet
            DataRelation dr = new DataRelation("SalesOrderHeader_SalesOrderDetail",
                ds.Tables["SalesOrderHeader"].Columns["SalesOrderID"],
                ds.Tables["SalesOrderDetail"].Columns["SalesOrderID"]);
            ds.Relations.Add(dr);
            ds.Tables["SalesOrderHeader"].Columns.Add("SumDetailLineTotal",typeof(decimal), "SUM(Child.LineTotal)");
            for (int i = 0; i < 2; i++){
                DataRow rowHeader = ds.Tables["SalesOrderHeader"].Rows[i];
                Console.WriteLine(rowHeader["SalesOrderID"]);
                Console.WriteLine(rowHeader["CustomerID"]);
                Console.WriteLine(rowHeader["SumDetailLineTotal"]);
                foreach (DataRow rowDetail in rowHeader.GetChildRows(dr))
                {
                    Console.WriteLine(rowDetail["SalesOrderID"]);
                    Console.WriteLine(rowDetail["SalesOrderDetailID"]);
                    Console.WriteLine(rowDetail["LineTotal"]);
                }
            }
        }
    }

Adding Columns to DataTable

using System;
using System.Data;
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt1 = new DataTable("Table-1");
            DataColumn pkCol = dt1.Columns.Add("Id", typeof(int));
            dt1.Columns.Add("Field1", typeof(string)).MaxLength = 50;
            dt1.PrimaryKey = new DataColumn[] { pkCol };
            DataTable dt2 = new DataTable("Table-2");
            dt2.Columns.Add("Id", typeof(int));
            dt2.Columns.Add("Field1", typeof(string)).MaxLength = 50;
            UniqueConstraint uc = new UniqueConstraint("PrimaryKey-2", dt2.Columns["Id"], true);
            dt2.Constraints.Add(uc);
            DataTable dt3 = new DataTable("Table-3");
            dt3.Columns.Add("Id1", typeof(int));
            dt3.Columns.Add("Id2", typeof(int));
            dt3.Columns.Add("Field1", typeof(string)).MaxLength = 50;
            dt3.Constraints.Add("PrimaryKey-3",new DataColumn[] { dt3.Columns["Id1"], dt3.Columns["Id2"] },true);
        }
    }

Adding Constraint to DataTable

using System;
using System.Data;
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt1 = new DataTable("Table-1");
            DataColumn pkCol = dt1.Columns.Add("Id", typeof(int));
            dt1.Columns.Add("Field1", typeof(string)).MaxLength = 50;
            dt1.PrimaryKey = new DataColumn[] { pkCol };
            DataTable dt2 = new DataTable("Table-2");
            dt2.Columns.Add("Id", typeof(int));
            dt2.Columns.Add("Field1", typeof(string)).MaxLength = 50;
            UniqueConstraint uc = new UniqueConstraint("PrimaryKey-2", dt2.Columns["Id"], true);
            dt2.Constraints.Add(uc);
            DataTable dt3 = new DataTable("Table-3");
            dt3.Columns.Add("Id1", typeof(int));
            dt3.Columns.Add("Id2", typeof(int));
            dt3.Columns.Add("Field1", typeof(string)).MaxLength = 50;
            dt3.Constraints.Add("PrimaryKey-3",new DataColumn[] { dt3.Columns["Id1"], dt3.Columns["Id2"] },true);
        }
    }

Add Row to DataTable

using System;
using System.Data;
using System.Data.SqlClient;
class ModifyDataTable
{
   static void Main(string[] args)
   {
      string connString = @"server = .\sqlexpress;integrated security = true;database = northwind";
      string sql = @"select * from employee where country = "UK"";
      SqlConnection conn = new SqlConnection(connString);
      try
      {
         SqlDataAdapter da = new SqlDataAdapter();
         da.SelectCommand = new SqlCommand(sql, conn);
         DataSet ds = new DataSet();
         da.Fill(ds, "employees");
         DataTable dt = ds.Tables["employees"];
         dt.Columns["firstname"].AllowDBNull = true;
         dt.Rows[0]["city"] = "w";
         DataRow newRow = dt.NewRow();
         newRow["firstname"] = "R";
         newRow["lastname"] = "B";
         newRow["titleofcourtesy"] = "Sir";
         newRow["city"] = "B";
         newRow["country"] = "USA";
         dt.Rows.Add(newRow);
         foreach (DataRow row in dt.Rows)
         {
            Console.WriteLine("{0} {1} {2}",
               row["firstname"].ToString().PadRight(15),
               row["lastname"].ToString().PadLeft(25),
               row["city"]);
         }
      }
      catch(Exception e)
      {
         Console.WriteLine("Error: " + e);
      }
      finally
      {
         conn.Close();
      }
   }
}

Append columns to DataTable

using System;
using System.Data;
using System.IO;
using System.Collections.Generic;
using System.Text;
    class Program
    {
        static DataTable dt;
        static void Main(string[] args)
        {            
            dt = new DataTable("Employees");
            dt.ReadXml("Employees.xml");
            Console.WriteLine("{0} Employees Found", dt.Rows.Count);
            foreach (DataRow row in dt.Rows)
            {
                Console.WriteLine("[{0}] {1} {2}", row["EmployeeID"], row["FirstName"], row["LastName"]);
            }
            //dt.Columns.Add("EmployeeID", typeof(int));
            //dt.Columns.Add("FirstName", typeof(string));
           // dt.Columns.Add("LastName", typeof(string));
           // dt.Rows.Add(new object[] { 1, "A", "H" });
           // dt.Rows.Add(new object[] { 2, "J", "D" });
            string[] cust = "a b c ".Split(" ");
            dt.Rows.Add(new object[] { dt.Rows.Count + 1, cust[0], cust[1] });
            dt.WriteXml("Employees.xml", XmlWriteMode.WriteSchema);
            
        } 
    }

Creating a DataColumn and Adding It to a DataTable

using System;
using System.Data;
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            DataColumn col1 = dt.Columns.Add();
            col1.ColumnName= "First Column";
            col1.DataType = typeof(int);
            col1.DefaultValue = 0;
            col1.Unique = true;
            col1.AllowDBNull = false;
            DataColumn col2 = new DataColumn();
            col2.ColumnName = "Second Column";
            col2.DataType = typeof(string);
            col2.MaxLength = 50;
            dt.Columns.Add(col2);
            dt.Columns.Add("Column-3", typeof(string)).MaxLength = 50;
            DataColumn col4 = new DataColumn("Column-4");
            DataColumn col5 = new DataColumn("Column-5", typeof(int));
            dt.Columns.AddRange(new DataColumn[] { col4, col5 });
            Console.WriteLine("DataTable has {0} DataColumns named:",dt.Columns.Count);
            foreach (DataColumn col in dt.Columns)
                Console.WriteLine("\t{0}", col.ColumnName);
        }
    }

Creating a DataTable and Adding It to a DataSet

using System;
using System.Data;
    class Program
    {
        static void Main(string[] args)
        {
            DataSet ds = new DataSet();
            DataTable dt1 = ds.Tables.Add("Table-1");
            DataTable dt2 = new DataTable("Table-2");
            ds.Tables.Add(dt2);
            DataTable dt3 = new DataTable("Table-3");
            DataTable dt4 = new DataTable("Table-4");
            ds.Tables.AddRange(new DataTable[] { dt3, dt4 });
            Console.WriteLine("DataSet has {0} DataTables named: ",ds.Tables.Count);
            foreach (DataTable dt in ds.Tables)
                Console.WriteLine("\t{0}", dt.TableName);
        }
    }

Creating a Unique Constraint

using System;
using System.Data;
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable("Table-1");
            dt.Columns.Add("Id", typeof(int));
            dt.Columns.Add("Field1", typeof(string)).MaxLength = 50;
            UniqueConstraint uc1 = new UniqueConstraint("UniqueConstraint", dt.Columns["Field1"]);
            dt.Constraints.Add(uc1);
            try
            {
                AddRow(dt, 1, "Value 1");
                AddRow(dt, 2, "Value 1");
                AddRow(dt, 3, "Value 1");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: {0}", ex.Message);
            }
        }
        private static void AddRow(DataTable dt, int id, string field1)
        {
            Console.WriteLine("\nAdding row: {0}, {1}", id, field1);
            dt.Rows.Add(new object[] { id, field1 });
            Console.WriteLine("Row added.");
        }
    }

Creating Single- and Multi-Column Primary Keys

using System;
using System.Data;
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt1 = new DataTable("Table-1");
            DataColumn pkCol = dt1.Columns.Add("Id", typeof(int));
            dt1.Columns.Add("Field1", typeof(string)).MaxLength = 50;
            dt1.PrimaryKey = new DataColumn[] { pkCol };
            DataTable dt2 = new DataTable("Table-2");
            dt2.Columns.Add("Id", typeof(int));
            dt2.Columns.Add("Field1", typeof(string)).MaxLength = 50;
            UniqueConstraint uc = new UniqueConstraint("PrimaryKey-2", dt2.Columns["Id"], true);
            dt2.Constraints.Add(uc);
            DataTable dt3 = new DataTable("Table-3");
            dt3.Columns.Add("Id1", typeof(int));
            dt3.Columns.Add("Id2", typeof(int));
            dt3.Columns.Add("Field1", typeof(string)).MaxLength = 50;
            dt3.Constraints.Add("PrimaryKey-3",new DataColumn[] { dt3.Columns["Id1"], dt3.Columns["Id2"] },true);
        }
    }

Filter Sort

using System;
using System.Data;
using System.Data.SqlClient;
    class FilterSort{
        static void Main(string[] args)
        {
            string connString = @"server = .\sqlexpress;integrated security = true;database = northwind";
            string sql1 = @"select * from customers";
            string sql2 = @"select * from products where unitprice < 10";
            string sql = sql1 + sql2;
            SqlConnection conn = new SqlConnection(connString);
            try
            {
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = new SqlCommand(sql, conn);
                DataSet ds = new DataSet();
                da.Fill(ds, "customers");
                DataTableCollection dtc = ds.Tables;
                string fl = "country = "Germany"";
                string srt = "companyname asc";
                foreach (DataRow row in dtc["customers"].Select(fl, srt))
                {
                    Console.WriteLine(row["CompanyName"]);
                    Console.WriteLine(row["ContactName"]);
                }
                foreach (DataRow row in dtc[1].Rows)
                {
                    Console.WriteLine(row["productname"]);
                    Console.WriteLine(row["unitprice"]);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
            }
            finally
            {
                conn.Close();
            } Console.ReadLine();
        }
    }

Loop through DataTable by DataRow

using System;
using System.Data;
using System.Data.SqlClient;
class MainClass
{
   static void Main(string[] args)
   {
      string connString = @"server = .\sqlexpress;integrated security = true;database = northwind";
      string sql = @"select productname,unitprice from products where unitprice < 20";
      SqlConnection conn = new SqlConnection(connString);
      try
      {
         conn.Open();
         SqlDataAdapter da = new SqlDataAdapter(sql, conn);
         DataTable dt = new DataTable();
         da.Fill(dt);
         foreach (DataRow row in dt.Rows) 
         {
            foreach (DataColumn col in dt.Columns)
               Console.WriteLine(row[col]);
         }
      }
      catch(Exception e)
      {
         Console.WriteLine("Error: " + e);
      }
      finally
      {
         conn.Close();
      }
   }
}

Loop through the rows in DataTable

using System;
using System.Data;
using System.Data.SqlClient;
    class Program{
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
            string sqlSelect = "SELECT ContactID, FirstName, LastName FROM Person.Contact";
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            DataTable dt = new DataTable( );
            da.Fill(dt);
            for (int i = 0; i < 5; i++)
            {
                DataRow row = dt.Rows[i];
                Console.WriteLine(i);
                Console.WriteLine(row[0]);
                Console.WriteLine(row["FirstName"]);
                Console.WriteLine(row[2, DataRowVersion.Default]);
            }

        }
    }

Modify DataTable

using System;
using System.Data;
using System.Data.SqlClient;
    class ModifyDataTable
    {
        static void Main(string[] args)
        {
            string connString = @"server = .\sqlexpress;integrated security = true;database = northwind";
            string sql = @"select * from employees where country = "UK"";
            SqlConnection conn = new SqlConnection(connString);
            try
            {
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = new SqlCommand(sql, conn);
                DataSet ds = new DataSet();
                da.Fill(ds, "employees");
                DataTable dt = ds.Tables["employees"];
                dt.Columns["firstname"].AllowDBNull = true;
                dt.Rows[0]["city"] = "Wilmington";
                DataRow newRow = dt.NewRow();
                newRow["firstname"] = "Roy";
                newRow["lastname"] = "Beatty";
                newRow["titleofcourtesy"] = "Sir";
                newRow["city"] = "Birmingham";
                newRow["country"] = "UK";
                dt.Rows.Add(newRow);
                foreach (DataRow row in dt.Rows)
                {
                    Console.WriteLine(row["firstname"]);
                    Console.WriteLine(row["lastname"]);
                    Console.WriteLine(row["city"]);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
            }
            finally
            {
                conn.Close();
            }
        }
    }

Output Constraint Properties

using System;
using System.Data;
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable("Table-1");
            dt.Columns.Add("Id", typeof(int));
            dt.Columns.Add("Field1", typeof(string)).MaxLength = 50;
            UniqueConstraint uc1 = new UniqueConstraint("UniqueConstraint", dt.Columns["Field1"]);
            dt.Constraints.Add(uc1);
            OutputConstraintProperties(dt);
        }
        private static void OutputConstraintProperties(DataTable dt)
        {
            Console.WriteLine(dt.TableName);
            Console.WriteLine(dt.Constraints[0].ConstraintName);
            Console.WriteLine(((UniqueConstraint)dt.Constraints[0]).IsPrimaryKey);
            foreach (DataColumn col in ((UniqueConstraint)dt.Constraints[0]).Columns)
            {
                Console.WriteLine(col.ColumnName);
            }
        }
    }

Output the rows from the DataTable with foreach loop over DataRowCollection

using System;
using System.Data;
using System.Data.SqlClient;
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
            string sqlSelect = "SELECT ContactID, FirstName, LastName FROM Person.Contact";
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            
            DataTable dt1 = new DataTable();
            da.Fill(dt1);
            foreach (DataRow row in dt1.Rows)
            {
                Console.WriteLine(
                    "ContactID = {0}\tFirstName = {1}\tLastName = {2}",
                    row["ContactID"], row["FirstName"], row["LastName"]);
            }
        }
    }

Pop DataTable

using System;
using System.Data;
using System.Data.SqlClient;
    class PopDataTable
    {
        static void Main(string[] args)
        {
            string connString = @"server = .\sqlexpress;integrated security = true;database = northwind";
            string sql = @"select productname,unitprice from products where unitprice < 20";
            SqlConnection conn = new SqlConnection(connString);
            try{
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                foreach (DataRow row in dt.Rows)
                {
                    foreach (DataColumn col in dt.Columns)
                        Console.WriteLine(row[col]);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
            }
            finally
            {
                conn.Close();
            }
        }
    }

Retrieves a schema table for a query

using System;
using System.Data;
using System.Data.SqlClient;
public class GetSchema
{
    public static void Main() 
    {
        string connectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI";
        string SQL = "SELECT * FROM CUSTOMERS";
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(SQL, con);
        SqlDataReader r;
        DataTable schema;
        con.Open();
        r = cmd.ExecuteReader();
        schema = r.GetSchemaTable();
        con.Close();
        foreach (DataRow row in schema.Rows)
        {
            foreach (DataColumn col in schema.Columns)
            {
                Console.WriteLine(col.ColumnName + " = " + row[col]);
            }
        }
    }
}

Retrieving a Result Set Using a DataTable or a DataSet

using System;
using System.Data;
using System.Data.SqlClient;
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
            string sqlSelect = "SELECT FirstName, LastName FROM Person.Contact";
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            DataTable dt = new DataTable( );
            da.Fill(dt);
            foreach (DataRow row in dt.Rows)
                Console.WriteLine("{0} {1}", row[0], row["LastName"]);

            DataSet ds = new DataSet( );
            da.Fill(ds, "Contact");
            Console.WriteLine(ds.Tables.Count);
            Console.WriteLine(ds.Tables[0].TableName);
            foreach (DataRow row in ds.Tables["Contact"].Rows)
                Console.WriteLine("{0} {1}", row[0], row[1]);
        }
    }

Use DataTable to insert a Row

using System;
using System.Drawing;
using System.Collections;
using System.ruponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
public class MainClass {
    [STAThread]
    static void Main() 
    {
        string ConnectionString ="server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
        SqlConnection conn = new SqlConnection(ConnectionString);
        conn.Open();
        SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Employee ORDER BY ID", conn);
        SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
        // Create a dataset object
        DataSet ds = new DataSet("EmployeeSet");
        adapter.Fill(ds, "Employee");
        // Create a data table object and add a new row
        DataTable EmployeeTable = ds.Tables["Employee"];
        DataRow row = EmployeeTable.NewRow();
        row["FirstName"] = "R";
        row["LastName"] = "D";
        row["ID"] = "10";
        EmployeeTable.Rows.Add(row);
        // Update data adapter
        adapter.Update(ds, "Employee");
        Console.WriteLine(row["FirstName"].ToString().Trim() + " " + row["LastName"].ToString().Trim() + " added to Employees");
    }
}