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

002 ADO.Net Connection Class

Connection Class
                                Connection class is essential component of ADO.Net. An application connect database with the connection class.There are  two type of connection class, Sql connection and Oledb connection. SQL connection is for connecting Microsoft SQL server instance and Oledb  for Acess ,Excel etc.

                                       Instance of a connection class is created with a new operator. Connection string tell connection class to which server and database to connect. Open method create a channel between database and an application. Here is some most commonly used attribute of a connection class .

                           string ConncetionString = "Data Source= xxx.xxx.xx.xx; initial catalog=xxxx; Connect Timeout=6000; User ID= xxx;Password=xxxx;";

                          SqlConnection con = new SqlConnection(ConncetionString);

• Constructor

1. SqlConnection : New instance of an connection class is created with parameter connection string, string connection , Sql credential  integrated security to true  user id password is needed. 

• Property 

1. Connection String : Get or Set string to connect the database. 
2. Connection Timeout : Waiting time , will try to connect database, after that error is generated. 
3. Database: Get the name of a database, when the connection is open. 
4. Data sources : Get the name of the Sql server instance which to connect. 
5. Packet size : Network packet size in byte, while communicating with the data source. 
6. Server version : Get information about the version of SQL server. 
7. State : Get the state of SQL connection. Open close. 

• Method

1. Beginconnection : Starts transaction. 
2. Clear pools : All connection pool associate with the current connection is cleared. 
3. Close : Close a connection to the database. 
4. Dispose : Release all resources. 
5. Getschema : Return the schema of the data source 
6. Open : Open a connection to the database. 

Connection pool : Connection pool increase the performance of an application. Instead of creating a new connection, connection pool try to reuse existing connection to reduce overhead and time. To use connection pull , you need to do pooling true and set Max pool size and Mean pool size  properly. At the initial stage, SQL server create new connection  when connection is required. When this connection are free , connection pool put them in a queue. When request for a connection  comes, connection pool check its Queu, if there is any connection available , the existing connection assign immediately , either a new connection is established again . This makes an application is faster in performance  as  no connection established , resources and time saved,   and as it is using the existing resources, no extra overhead on the application.

1)Example of Sql Coneection to connect a Sql Server

using System;

using System.Data;

using System.Data.SqlClient;

namespace ConsoleApplication1

{

         class Program

         {

                static void Main(string[] args)

                 {

                          string conStr = "Data Source= ***.***.**.**; initial catalog=*****;Connect Timeout=6000; User ID= *****;Password=****;";

                         string cmd = "select top 5 * from tbl_student";

                         using (SqlConnection con = new SqlConnection(conStr))

                        {

                                SqlCommand command =new SqlCommand(cmd, con);

                                con.Open();

                                SqlDataReader reader = command.ExecuteReader();

                                DataTable schemaTable = reader.GetSchemaTable();

                                foreach (DataColumn column in schemaTable.Columns)

                                {

                                           Console.WriteLine(column.ColumnName + ":" + schemaTable.Rows[0][0]);

                                }

                              reader.Close();

                      }

                      Console.ReadKey();

             }

         }

}

2) Example of Sql Coneection to connect a Sql Server with connection pull

using System;

using System.Data;

using System.Data.SqlClient;

namespace ConsoleApplication1

{

            class Program

             {

                  static void Main(string[] args)

                 {

                       string conStr = "Data Source= ****.***.***.***; initial catalog=*****;Connect Timeout=6000; User ID= *****;Password=****;Min Pool Size=0;Max Pool Size=100;Pooling=true;";

                    string cmd = "select top 5 * from tbl_member";

                    using (SqlConnection con = new SqlConnection(conStr))

                    {

                             SqlCommand command =new SqlCommand(cmd, con);

                             con.Open();

                             SqlDataReader reader = command.ExecuteReader();

                            DataTable schemaTable = reader.GetSchemaTable();

                            foreach (DataColumn column in schemaTable.Columns)

                              {

                                       Console.WriteLine(column.ColumnName + ":" + schemaTable.Rows[0][0]);

                             }

                        reader.Close();

                }

                 Console.ReadKey();

            }

}

}