007 ADO.Net DataRow & DataColumn

DataRow is a important part of ADO.Net. DataTable is a collection of DataRow . DataRow contain DataColumn . Here is the most commonly used attribute of  DataRow 

    DataRow dr = new DataRow()

• Constructor 

1. DataRow : Initialize new instance of DataRow with new operator.

• Property

1. HasError : Get an indicator value , if there is any error in the DataRow.
2. ItemArray : Get or Set an array of data representation of DataRow. 
3. RowState : Current State of row.
4. Table : Get the name of the table which DataRow belongs. 

• Method 

1. AcceptChanges : Commit all changes made in a DataRow , after that DataRow was last loaded. 
2. Delete : Delete a DataRow. 
3. HasVersion : Get a value indicating specific Verson exist or not.
4. IsNull : Get an indicator value of the specific DataColumn value is Null or not. 

DataRow  is Programmable, can be created programmatically and can be added data to programmatically also . DataRow can also added in DataTable programmatically.

1)  DataRow add to DataTable

using System;

using System.Data;

namespace ConsoleApplication1

{

         class Program

         {

            static void Main(string[] args)

            {

                DataTable dt = new DataTable();

                dt.Columns.Add("Name", typeof(string));

               dt.Columns.Add("Roll", typeof(int));

               dt.Columns.Add("Address", typeof(string));

             dt.Rows.Add("John1", 123, "California1");

             dt.Rows.Add("John2", 124, "California2");

             dt.Rows.Add("John3", 125, "California3");

            dt.Rows.Add("John4", 126, "California4");

            dt.Rows.Add("John5", 127, "California5");

           dt.Rows.Add("John6", 128, "California6");

           foreach (DataRow row in dt.Rows)

          {

                    Console.WriteLine("--- Row ---");

                   foreach (var item in row.ItemArray)

                  {

                             Console.Write("Item: "); 

                             Console.WriteLine(item); 

                 }

       }

   Console.ReadKey();

      }

   }

}

Output :

--- Row ---
Item: John1
Item: 123
Item: California1
--- Row ---
Item: John2
Item: 124
Item: California2
--- Row ---
Item: John3
Item: 125
Item: California3
--- Row ---
Item: John4
Item: 126
Item: California4
--- Row ---
Item: John5
Item: 127
Item: California5
--- Row ---
Item: John6
Item: 128
Item: California6

2)  DataRow Delete from  DataTable

using System;

using System.Data;

namespace ConsoleApplication1

{

          class Program

        {

            static void Main(string[] args)

            {

               DataTable dt = new DataTable();

              dt.Columns.Add("Name", typeof(string));

              dt.Columns.Add("Roll", typeof(int));

              dt.Columns.Add("Address", typeof(string));

             dt.Rows.Add("John1", 123, "California1");

            dt.Rows.Add("John2", 124, "California2");

            dt.Rows.Add("John3", 125, "California3");

            dt.Rows.Add("John4", 126, "California4");

            dt.Rows.Add("John5", 127, "California5");

            dt.Rows.Add("John6", 128, "California6");

           dt.Rows[2].Delete();

            foreach (DataRow row in dt.Rows)

           {

                  Console.WriteLine("--- Row ---");

                  foreach (var item in row.ItemArray)

                  {

                        Console.Write("Item: "); 

                       Console.WriteLine(item);

                }

         }

        Console.ReadKey();

    }

}

}

 Output :

--- Row ---
Item: John1
Item: 123
Item: California1
--- Row ---
Item: John2
Item: 124
Item: California2
--- Row ---
Item: John4
Item: 126
Item: California4
--- Row ---
Item: John5
Item: 127
Item: California5
--- Row ---
Item: John6
Item: 128
Item: California6



3)  DataRow SetField And Add New Row

using System;

using System.Data;

namespace ConsoleApplication1

{

                    class Program

                  {

                        static void Main(string[] args)

                        {

                            DataTable dt = new DataTable();

                            dt.Columns.Add("Name", typeof(string));

                           dt.Columns.Add("Roll", typeof(int));

                           dt.Columns.Add("Address", typeof(string));

                           DataRow dr = dt.Rows.Add();

                           dr.SetField(0, "John1");

                           dr.SetField(1, "123");

                          dr.SetField(2, "California1");

                          DataRow dr1 = dt.Rows.Add();

                         dr1.SetField(0, "John2");

                         dr1.SetField(1, "124");

                         dr1.SetField(2, "California2");

                       foreach (DataRow row in dt.Rows)

                      {

                              Console.WriteLine("--- Row ---");

                               foreach (var item in row.ItemArray)

                              {

                                  Console.Write("Item: "); 

                                 Console.WriteLine(item);

                            }

                }

              Console.ReadKey();

         }

     }

}

OutPut :

--- Row ---
Item: John1
Item: 123
Item: California1
--- Row ---
Item: John2
Item: 124
Item: California2

DataColumn : DataTable is the heart of ADO.Net . DataTable contain DataRow and DataRow  contain DataColumns. DataColumn is memory representation of the data .DataColumn hold data for specific column under rows.
 

DataColumn dc= new DataColumn();

• Constructor

1. DataColumn :New instance of a data column is created .

• Property  

1. AllowDBNull : Get or Set indicating value that the column will allow null or not. 
2. AutoIncrement : Get or Set starting value of auto increment, if no value specified the default  will be zero (0).
3. ColumnName : Get or Set name of the column. 
4. DefaultValue : Get or Set default value for this column. 
5. MaxLength : Get or Set maximum length of data for this column. 
6. ReadOnly : Get or Set Column is Read only or not. 
7. Dispose : Release all resource hold by data column.

 

1) DataColumn Attribute

using System;

using System.Data;

namespace ConsoleApplication1

{

        class Program

          {

              static void Main(string[] args)

               {

                    DataTable dt = new DataTable();

                    dt.Columns.Add("Name", typeof(string));

                    dt.Columns.Add("Roll", typeof(int));

                    dt.Columns.Add("Address", typeof(string));

 

            Console.WriteLine("Column : DataType |Maximum Length |ReadOnly");

                  foreach (DataColumn column in dt.Columns)

                   {

                         Console.WriteLine("{0} : {1} | {2} | {3}", column, column.DataType,  column.MaxLength,column.ReadOnly);

                  }

             Console.ReadKey();

            }

     }

}

Output :

Column : DataType |Maximum Length |ReadOnly
Name : System.String | -1 | False
Roll : System.Int32 | -1 | False
Address : System.String | -1 | False

2) DataColumn Add dynamically

using System;

using System.Data;

namespace ConsoleApplication1

{

      class Program

      {

           static void Main(string[] args)

          {

            DataTable dt = new DataTable();

           // Create column ID

           System.Type tp0;

           tp0 = System.Type.GetType("System.Int32");

           DataColumn Col0 = new DataColumn("ID", tp0);

           Col0.ReadOnly = true;

           Col0.AllowDBNull = false;

           Col0.Unique = true;

           Col0.AutoIncrement = true;

           Col0.AutoIncrementSeed = 1;

           Col0.AutoIncrementStep = 1;

          dt.Columns.Add(Col0);

 

      // Create column Name

          System.Type tp;

         tp = System.Type.GetType("System.String");

        DataColumn Col1 = new DataColumn("Name", tp);

        Col1.ReadOnly = false;

        Col1.AllowDBNull = false;

        Col1.Unique = true;

        Col1.AutoIncrement = false;

        dt.Columns.Add(Col1);

       // Create column Roll

        System.Type tp1;

       tp1 = System.Type.GetType("System.Int32");

       DataColumn Col2 = new DataColumn("Roll", tp1);

      Col2.ReadOnly = false;

      Col2.AllowDBNull = false;

     Col2.Unique = true;

     Col2.AutoIncrement = false;

     dt.Columns.Add(Col2);

    // Create column Address

    System.Type tp2;

    tp2 = System.Type.GetType("System.String");

   DataColumn Col3 = new DataColumn("Address", tp2);

   Col3.ReadOnly = false;

  Col3.AllowDBNull = false;

  Col3.Unique = true;

  Col3.AutoIncrement = false;

  dt.Columns.Add(Col3);

for (int i = 0; i < 5; i++)

{

DataRow row = dt.NewRow();

row["Name"] = "John"+i.ToString();

row["Roll"] = 123+i;

row["Address"] = "USA"+i.ToString();

dt.Rows.Add(row);

}

foreach (DataRow row in dt.Rows)

{

Console.WriteLine("--- Row ---");

foreach (var item in row.ItemArray)

{

Console.Write("Item: ");

Console.WriteLine(item);

}

}

Console.ReadKey();

}

}

}

Output :
--- Row ---
Item: 1
Item: John0
Item: 123
Item: USA0
--- Row ---
Item: 2
Item: John1
Item: 124
Item: USA1
--- Row ---
Item: 3
Item: John2
Item: 125
Item: USA2
--- Row ---
Item: 4
Item: John3
Item: 126
Item: USA3
--- Row ---
Item: 5
Item: John4
Item: 127
Item: USA4