• Loading
    • ADO.NET

      Objectives
      After completing this session, you will be able to:

      • Understand ADO.Net Objects
      • Understand the methods to access/manipulate data from database using .Net
      • Understand the way to use Select, Insert, Delete, Update Queries in .Net
      • Create Database oriented applications using .Net


      ADO.NET(ActiveX Data Objects) is a technology for transferring data between Microsoft Visual Studio (.Net) and the Database. The Database could be a Relational database or a Non-Relational database. The database could be referred in this tutorial as a Data Provider.

      ADO.NET basically supports 4 Data Providers. They are

      1. Data Provider for SQL Server
      Backend: Microsoft SQL Server

      Package: System.Data.SqlClient

      2. Data Provider for OLEDB
      Backend: Microsoft Access, Microsoft Excel
      Package: System.Data.OleDb

      3. Data Provider for ODBC
      Backend: Older Databases
      Package: System.Data.Odbc

      4. Data Provider for Oracle
      Backend: Oracle
      Package: System.Data.OracleClient

      In simple terms, we can say that if you want to connect .Net with a Backend, the backend could be any one of the following (MS SQL Server, MS Access, MS Excel, Oracle)

      ADO.NET DATA OBJECTS

      To connect and work with a database in .Net, ADO.Net provides 5 necessary Data Objects. They are

      1. Connection Object
      2. Command Object
      3. DataReader Object
      4. DataAdapter Object
      5. DataSet Object

      CONNECTION OBJECT:


      The connection object handles the physical communication between .Net and the Database. In other words, connection object is responsible for making a connection between the application and the database. After the connection is made, the data can be transferred to/from the database by using the valid SQL commands.

      The following operations should be performed in sequence to get a hassle free data access.

      1. Specify the database and server name in Connection object
      2. Open the connection
      3. Perform database operations
      4. Close the connection after all the database operations are over

      Example:
      (Here Microsoft SQL Server is assumed as Data Provider)

      To Establish a Connection
      Code:
      SqlConnection con = new SqlConnection(connetionString);
      To Open the Connection
      Code:
      con.open();
      To Close the Connection
      Code:
      con.close();

      COMMAND OBJECT:


      The command object is used to execute the SQL commands on the Data Provider. For each command object, an explicit Open database is required. In easy words, SQL Queries can be executed on the database by using the command object. Queries such as Insert, Delete, Update, Select can be executed by using the command object. Each command object will have a result set to store the result of a SQL Query.

      Result Set can be Empty or Single Valued or a Set of Records

      Following the SQL Command any of the following methods need to invoked to store the result sets.
      i) ExecuteNonQuery
      ii) ExecuteReader
      iii) ExecuteScalar

      ExecuteNonQuery()


      ExecuteNonQuery can be used to alter the data in the database. Insert, Update, Delete Queries can be issued using ExecuteNonQuery. Retrieval of values from the database is not possible using ExecuteNonQuery. Simply we can say that Select Query cannot be issued using ExecuteNonQuery.
      Consider the following SQL Commands

      Code:
      SqlCommand cmd = new SqlCommand
               ("insert into Customer Values (101,'Raj','Kumar','MCA'", con);
        SqlCommand cmd = new SqlCommand
               ("update Customer set Dept='MBA' where id=101",con);
        SqlCommand cmd = new SqlCommand
               ("delete FROM Customer WHERE   LastName='Jones'", con);
      In the above examples, consider the query alone and predict the output. Assume that you are using a data provider such as oracle or access and predict the output. The output of the above queries could be one of these

      1 Row(s) Inserted or 5 Row(s) Updated or 1 Row(s) Deleted

      It means that the operation is performed but it doesnt return any result set. In such case the method ExecuteNonQuery() should be uses as in the example below



      Code:
      SqlCommand cmd = new SqlCommand
               ("insert into Customer Values (101,'Raj','Kumar','MCA'", con);
        cmd.ExecuteNonQuery();
       
        SqlCommand cmd = new SqlCommand
               ("update Customer set Dept='MBA' where id=101",con);
        cmd.ExecuteNonQuery();
       
        SqlCommand cmd = new SqlCommand
               ("delete FROM Customer WHERE   LastName='Jones'", con);
        cmd.ExecuteNonQuery();
      ExecuteReader()

      ExecuteReader is used to store the result set to the SqlDataReader Object in the case the the query returns one or more records. A dis-advantage of using ExecuteReader is that, the SqlDataReader Object is Forward Only and Read Only. In other words, ExecuteReader cannot insert or update or delete data from the database. It can be used only in select queries.

      Example:


      Code:
      SqlCommand cmd = new SqlCommand("select * from Customer", con);
              SqlDataReader rdr=cmd.ExecuteReader();
              while(rdr.Read())
              {
                     rdr.GetValue(0);
                     rdr.GetValue(1);
              }
              rdr.Close();
      ExecuteScalar()

      The ExecuteScalar() is used for retrieving a single value from Database after the execution of the SQL Statement.
      If the Result Set contains more than one columns or rows, it will take only the value of first column of the first row, and all other values will ignore. If the Result Set is empty it will return a NULL reference.
      It is very useful to use with aggregate functions like Count(*) or Sum() etc. When compare to ExecuteReader() , ExecuteScalar() uses fewer System resources.

      Examples:


      The following example retrieves the First name of the customer whose id is 101

      Code:
       SqlCommand cmd = new SqlCommand
                     ("select FirstName from Customer where id=101",con);  
              String name=cmd.ExecuteScalar().ToString();
      The following example retrieves the Maximum Value from the field sno

      Code:
              SqlCommand cmd = new SqlCommand
                     ("select Max(sno) from Customer",con);
              int maxval = Convert.ToInt32(cmd.ExecuteScalar());
      The following example retrieves the total number of records from the table Customer
      Code:
              SqlCommand cmd = new SqlCommand
                     ("select Count(*) from Customer",con);
              int recount = Convert.ToInt32(cmd.ExecuteScalar());


      DATA READER OBJECT


      The DataReader object is used to create an object which store the result of an ExecuteReader() Method. It cannot be created directly from code, they created only by calling the ExecuteReader method of a Command Object.
      When started to read from a DataReader, Connection should always be open and positioned prior to the first record. The Read() method in the DataReader is used to read the rows from DataReader and it always moves forward to a new valid row.

      Example:


      Code:
       SqlCommand cmd = new SqlCommand("select * from Customer", con);
              SqlDataReader rdr=cmd.ExecuteReader();
              while(rdr.Read())
              {
                     rdr.GetValue(0);
                     rdr.GetValue(1);
              }
              rdr.Close();


      DATA ADAPTER OBJECT


      Data Adapter object is used to retrieve more than one Result set from a database. They can be stored in a dataset and they can be manipulated. The advantage of Data Adapter object over the Data Reader object is that, Data Adapter Object provides Random Access to data where as the Data Reader Object provides only a Sequential Access to the data.
      In simple words, Data Adapter Object can be used to execute Select Queries.

      Example:


      Code:
      SqlDataAdapter dsa=new SqlDataAdapter("Select * from Customers");
      DataSet ds = new DataSet();
      dsa.Fill(ds);
       
      int i;
      for(i=0;i<ds.Tables[0].Rows.Count;i++)
      {
              ds.Tables[0].Rows[i][0];/*ds.Tables[0].Rows[i]["sno"]*/
              ds.Tables[0].Rows[i][1];/*ds.Tables[0].Rows[i]["FirstName"]*/
              ds.Tables[0].Rows[i][2];/*ds.Tables[0].Rows[i]["LastName"]*/
              ds.Tables[0].Rows[i][3];/*ds.Tables[0].Rows[i]["Dept"]*/
      }


      DATA SET OBJECT


      The ADO.NET DataSet represents a collection of data retrieved from the Data Source. We can use Dataset in combination with DataAdapter class. The output of the Data Adapter Object can be stored and manipulated using the Data Set Object. Data Set object stores a copy of data retrieved from the Data Adapter Object. The data can be filled to the dataset using Fill Method.

      Example


      Code:
      SqlDataAdapter dsa=new SqlDataAdapter("Select * from Customers");
      DataSet ds = new DataSet();
      dsa.Fill(ds);
       
      int i;
      for(i=0;i<ds.Tables[0].Rows.Count;i++)
      {
              ds.Tables[0].Rows[i][0];/*ds.Tables[0].Rows[i]["sno"]*/
              ds.Tables[0].Rows[i][1];/*ds.Tables[0].Rows[i]["FirstName"]*/
              ds.Tables[0].Rows[i][2];/*ds.Tables[0].Rows[i]["LastName"]*/
              ds.Tables[0].Rows[i][3];/*ds.Tables[0].Rows[i]["Dept"]*/
      }
      A Quick Overview
      SqlConnection To Specify the Database Path
      SqlCommand To Specify an SQL Query
      ExecuteNonQuery(); - For Queries which do not return Result Sets [Insert,Update,Delete Queries]
      ExecuteReader(); - For Queries which return one or more Result Sets (Forward) [Select Query]
      ExecuteScalar(); - For Queries which return only a single value [Select Query]
      SqlDataReader To store the result of an ExecuteReader();
      SqlDataAdapter To Specify an SQL Query which return one or more Result Sets (Any Direction)[Select Query]
      DataSet() To store the data received from a data source. (Ex: SqlDataAdapter)



    Disclaimer: Users of techforum4u.com are responsible for ensuring that any material they post (article, blog posts, images or other mulitimedia content) does not violate or infringe upon the copyright, patent, trademark, or any personal or proprietary rights of any third party, and is posted with the permission of the owner of such rights.Anyone who violates these rules may have their access privileges removed without warning.