Csharp/CSharp Tutorial/ADO.Net/DataRow

Accessing Data Values in a DataRow Array by column name

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);
            DataRow[] dra = new DataRow[dt.Rows.Count];
            dt.Rows.CopyTo(dra, 0);
            Console.WriteLine("FirstName = {0}", dra[2]["FirstName"]);
        }
    }

Accessing Data Values in a DataRow Array by column name and return default value

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);
            DataRow[] dra = new DataRow[dt.Rows.Count];
            dt.Rows.CopyTo(dra, 0);
            Console.WriteLine("FirstName = {0}",dra[2]["FirstName", DataRowVersion.Default]);
        }
    }

Accessing Data Values in a DataRow Array by index

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);
            DataRow[] dra = new DataRow[dt.Rows.Count];
            dt.Rows.CopyTo(dra, 0);
            Console.WriteLine("FirstName = {0}", dra[2][1]);
        }
    }

Accessing Data Values in a DataRow Array in a generic way by index

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);
            DataRow[] dra = new DataRow[dt.Rows.Count];
            dt.Rows.CopyTo(dra, 0);
            Console.WriteLine("FirstName = {0}", dra[2].Field<string>(1));
        }
    }

Accessing Data Values in a DataRow Array in a generic way by name

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);
            DataRow[] dra = new DataRow[dt.Rows.Count];
            dt.Rows.CopyTo(dra, 0);
            Console.WriteLine("FirstName = {0}",dra[2].Field<string>("FirstName"));
            Console.WriteLine("FirstName = {0}",dra[2].Field<string>("FirstName", DataRowVersion.Default));
        }
    }

Create and fill the DataRow array

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);
            DataRow[] dra = new DataRow[dt1.Rows.Count];
            dt1.Rows.CopyTo(dra, 0);
            for (int i = 0; i < dra.Length; i++){
                Console.WriteLine(
                    "ContactID = {0}\tFirstName = {1}\tLastName = {2}",
                    dra[i].Field<int>("ContactID"), dra[i].Field<string>("FirstName"),
                    dra[i].Field<string>("LastName"));
            }
        }
    }

DataRowState manipulation

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;
  class Program
  {
    static void Main(string[] args)
    {
      DataTable temp = new DataTable("Temp");
      temp.Columns.Add(new DataColumn("TempColumn", typeof(int)));
      DataRow row = temp.NewRow();           
      Console.WriteLine("After calling NewRow(): {0}", row.RowState);
      temp.Rows.Add(row);
      Console.WriteLine("After calling Rows.Add(): {0}", row.RowState);
      row["TempColumn"] = 10;
      Console.WriteLine("After first assignment: {0}", row.RowState);
      temp.AcceptChanges();
      Console.WriteLine("After calling AcceptChanges: {0}", row.RowState);
      row["TempColumn"] = 11;
      Console.WriteLine("After first assignment: {0}", row.RowState);
      temp.Rows[0].Delete();
      Console.WriteLine("After calling Delete: {0}", row.RowState);
    }
  }

DataRow Update

using System;
using System.Drawing;
using System.Collections;
using System.ruponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.rumon;
public class DataRowUpdateState : System.Windows.Forms.Form
{
  private System.Windows.Forms.DataGrid dataGrid1;
  private System.ruponentModel.Container components = null;
  public DataRowUpdateState()
  {
    InitializeComponent();
    CreateCustomersTable();
  }
  protected override void Dispose( bool disposing )
  {
    if( disposing )
    {
      if (components != null) 
      {
        components.Dispose();
      }
    }
    base.Dispose( disposing );
  }
  private void InitializeComponent()
  {
    this.dataGrid1 = new System.Windows.Forms.DataGrid();
    ((System.ruponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
    this.SuspendLayout();
    // 
    // dataGrid1
    // 
    this.dataGrid1.DataMember = "";
    this.dataGrid1.Location = new System.Drawing.Point(8, 8);
    this.dataGrid1.Name = "dataGrid1";
    this.dataGrid1.Size = new System.Drawing.Size(416, 296);
    this.dataGrid1.TabIndex = 0;
    // 
    // DataRowUpdateState
    // 
    this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
    this.ClientSize = new System.Drawing.Size(432, 309);
    this.Controls.AddRange(new System.Windows.Forms.Control[] {
                                    this.dataGrid1});
    this.Name = "DataRowUpdateState";
    this.Text = "DataRowUpdateState";
    ((System.ruponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
    this.ResumeLayout(false);
  }
  [STAThread]
  static void Main() 
  {
    Application.Run(new DataRowUpdateState());
  }
  private void CreateCustomersTable()
  {
    System.Data.DataTable custTable = new DataTable("Customers");
    DataColumn dtColumn;
    
    dtColumn = new DataColumn();
    dtColumn.DataType = System.Type.GetType("System.Int32");
    dtColumn.ColumnName = "id";
    dtColumn.Caption = "Cust ID";
    dtColumn.ReadOnly = true;
    dtColumn.Unique = true;
    custTable.Columns.Add(dtColumn);
    dtColumn = new DataColumn();
    dtColumn.DataType = System.Type.GetType("System.String");
    dtColumn.ColumnName = "Name";
    dtColumn.Caption = "Cust Name";
    dtColumn.AutoIncrement = false;
    dtColumn.ReadOnly = false;
    dtColumn.Unique = false;
    custTable.Columns.Add(dtColumn);
    dtColumn = new DataColumn();
    dtColumn.DataType = System.Type.GetType("System.String");
    dtColumn.ColumnName = "Address";
    dtColumn.Caption = "Address";
    dtColumn.ReadOnly = false;
    dtColumn.Unique = false;
    // Add Address column to the table.
    custTable.Columns.Add(dtColumn);
    DataColumn[] PrimaryKeyColumns = new DataColumn[1];
    PrimaryKeyColumns[0] = custTable.Columns["id"];
    custTable.PrimaryKey = PrimaryKeyColumns;
    DataSet ds = new DataSet("Customers");
    ds.Tables.Add(custTable);
    DataRow row1 = custTable.NewRow();
    row1["id"] = 1;
    row1["Address"] = "USA";
    row1["Name"] = "George";
    custTable.Rows.Add(row1);
        MessageBox.Show(row1.RowState.ToString());
    row1.RejectChanges();
    MessageBox.Show(row1.RowState.ToString());
    row1.Delete();
    MessageBox.Show(row1.RowState.ToString());
    
    dataGrid1.DataSource = ds.DefaultViewManager;      
  }
}

DataView and DataRowView

using System;
using System.Data;
using System.Data.SqlClient;
    class DataViews
    {
        static void Main(string[] args)
        {
            string connString = @"server = .\sqlexpress;integrated security = true;database = northwind";
            string sql = @"select contactname,country from customers";
            SqlConnection conn = new SqlConnection(connString);
            try
            {
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = new SqlCommand(sql, conn);
                DataSet ds = new DataSet();
                da.Fill(ds, "customers");
                DataTable dt = ds.Tables["customers"];
                DataView dv = new DataView(dt,"country = "Germany"","country",DataViewRowState.CurrentRows);
                foreach (DataRowView drv in dv)
                {
                    for (int i = 0; i < dv.Table.Columns.Count; i++)
                        Console.Write(drv[i] + "\t");
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
            }
            finally
            {
                conn.Close();
            } Console.ReadLine();
        }
    }

Filling a DataTable from the DataRow array using CopyToDataTable()

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);
            DataRow[] dra = new DataRow[dt1.Rows.Count];
            dt1.Rows.CopyTo(dra, 0);
            for (int i = 0; i < dra.Length; i++){
                Console.WriteLine(
                    "ContactID = {0}\tFirstName = {1}\tLastName = {2}",
                    dra[i].Field<int>("ContactID"), dra[i].Field<string>("FirstName"),
                    dra[i].Field<string>("LastName"));
            }
            DataTable dt2 = dra.CopyToDataTable();
            foreach (DataRow row in dt2.Rows)
            {
                Console.WriteLine(
                    "ContactID = {0}\tFirstName = {1}\tLastName = {2}",
                    row["ContactID"], row["FirstName"], row["LastName"]);
            }
        }
    }

Filling a DataTable from the DataRow array using CopyToDataTable(DataTable, LoadOption)

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);
            DataRow[] dra = new DataRow[dt1.Rows.Count];
            dt1.Rows.CopyTo(dra, 0);
            for (int i = 0; i < dra.Length; i++){
                Console.WriteLine(
                    "ContactID = {0}\tFirstName = {1}\tLastName = {2}",
                    dra[i].Field<int>("ContactID"), dra[i].Field<string>("FirstName"),
                    dra[i].Field<string>("LastName"));
            }
            DataTable dt2 = dra.CopyToDataTable();
            foreach (DataRow row in dt2.Rows)
            {
                Console.WriteLine(
                    "ContactID = {0}\tFirstName = {1}\tLastName = {2}",
                    row["ContactID"], row["FirstName"], row["LastName"]);
            }
            DataTable dt3 = dt1.Clone();
            dra.CopyToDataTable(dt3, LoadOption.Upsert);
            foreach (DataRow row in dt3.Rows)
            {
                Console.WriteLine("ContactID = {0}\tFirstName = {1}\tLastName = {2}",row["ContactID"], row["FirstName"], row["LastName"]);
            }            
        }
    }

For loop over DataRow array

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);
            DataRow[] dra = new DataRow[dt1.Rows.Count];
            dt1.Rows.CopyTo(dra, 0);
            for (int i = 0; i < dra.Length; i++){
                Console.WriteLine(
                    "ContactID = {0}\tFirstName = {1}\tLastName = {2}",
                    dra[i].Field<int>("ContactID"), dra[i].Field<string>("FirstName"),
                    dra[i].Field<string>("LastName"));
            }
        }
    }

Get column value in a row

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]);
            }

        }
    }

Get first index in a row

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]);
            }

        }
    }

Get value from a row with default value

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]);
            }

        }
    }

Using foreach statement with DataRow 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);
            int j = 0;
            foreach (DataRow row in dt.Rows)
            {
                j++;
                Console.WriteLine("Row = {0}\tContactID = {1}\tFirstName = {2}\tLastName = {3}", j, row[0], row["FirstName"],row["LastName", DataRowVersion.Default]);
            }
        }
    }