Working in connected environment in ADO.NET

Discussion in 'ASP.NET' started by MinalS, Jan 16, 2015.

  1. MinalS

    MinalS New Member

    Joined:
    Jul 8, 2014
    Messages:
    138
    Likes Received:
    32
    Trophy Points:
    0
    The Data Reader is the component of the data provider in ADO.NET. It is useful in accessing data. It provides data to the application in read only, forward only mode.

    A command object is useful for changing the data in the data source. It can be used as a DML statement. User can perform operations like insert, delete, retrieve or modify the data in the data source.

    The OleDbCommand or SqlCommand classes are useful in creating the instance of the command. For accessing the data through the data commands, a connection object is created for connecting to the database where the data is to be retrieved. Next, create a command object. The data command must posses the information about the connection with the data source, DML statement, and the parameters for execution of data command.

    User can perform two operation types for accessing and retrieving the data from the data source. They are as mentioned below:
    1. Synchronous operations
    2. Asynchronous operations

    1. Synchronous operations



    In the synchronous operations, the command objects are connected to each other. The database commands are executed synchronously. The first command must complete its execution before user starts working on the second command. There are many command objects that are useful in the synchronous communication. They are as listed below:
    1. DbCommand object
    2. DbDataReader object
    3. DbParameters object
    1. DbCommand object

    The DbCommand object is useful for executing the command in the data source. The DbCommand object can be DML command or the DDL command. The DML command can perform operations like insert, update, retrieve, or delete data from the database. The DDL command can be useful in creating tables and modifying schema in the database.

    User must create the DbConnection object that is used as open connection to the data source. It is created prior to the execution of the data source. The CreateCommand() method is useful the connection object to create an appropriate provider.

    Some of the properties of the DbCommand object are as listed below:
    1. CommandText: It is used to get or set the command text useful for execution against the data source.
    2. CommandType: It is used for stating the CommandText property to be interpreted.
    3. Connection: It is used to get or set the connection using the DbCommand object.
    4. CommandTimeout: It is used to set or get the wait time before user executes the command and an error is generated.
    5. Parameters: It gets the collection of the DbParameter objects.
    6. Site: It is used to get or set the ISite interface that contains the methods for binding the component to the container.
    Some of the methods of the DbCommand object are as mentioned below:
    1. CreateParameter() : It is used for creating the new instance of the DbParameter object.
    2. Cancel(): It is used for cancellation of execution of the DbCommand object.
    3. Dispose(): It releases the resources used by the component.
    4. ExecuteNonQuery(): It executes the DML statement and returns the number of rows affected.
    5. ExecuteScalar(): It executes the query and returns the first column of the first row in the result set.
    6. GetType(): It gets the type of the current instance of the DbCommand object.
    Consider an example to demonstrate the use of the DbCommand object for generating the list of order data having total cost value as 1200. The data is retrieved through the SQL command to the database through the DbCommand object.

    The following code snippet demonstrates the DbCommand object in ADO.NET.

    Code:
    
    public static void ExecuteDbCommand ( DbConnection connection )
    {
    	if ( connection != null )
    	{
    		try
    		{
    			string connectionString = “Data Source =SQLSERVER08; Initial Catalog = Master; User id =sa; Password=abcd1234” ; 
    			SqlConnection con = new SqlConnection();
    			con.ConnectionString = connectionString;
    			con.Open();
    			SqlCommand cmd = con.CreateCommand();
    			cmd.CommandType = CommandType.Text;
    			cmd.CommandText = “ select * from Orders where 
    totolcost =   1200”;
    cmd.ExecuteNonQuery();
    		}
    		catch( Execption ex )
    		{
    			Console.WriteLine( “ Exception is :{0}”, ex.Message);
    		}
    		
    } 
    
    
    In the above code, the SqlCommand is used for executing the SQL query. The CommandText property contains the SQL query.

    2. DbDataReader Object

    The DbDataReader object is used for accessing the data in read only, forward only mode from the data source. It contains Read() method used for retrieving data from the data source. Only one row can be viewed at a time. The complete data is not available before processing.

    Some of the properties of the DbDataReader object are as follows:
    1. HasRows: It returns the value indicating the DbDataReader contains one or more rows.
    2. Depth: It returns value indicating the depth of the current row.
    3. IsClosed: It returns the value indicating the DbDataReader is closed
    4. Item: It returns the value of the specified column as an object instance.
    5. RecordsAffected: It returns a value indicating that the object contains rows.
    6. VisibleFieldCount: It returns the number of fields in the object that are not hidden.
    The methods for DbDataReader object are as mentioned below:
    1. CreateObjRef(): It is used for creating the object containing the information for creating the proxy for remote object communication.
    2. Equals(): It checks whether the two object instances are equal
    3. GetHashCode(): It servers the hash function for the object
    4. GetLifeTimeService(): It retrieves the lifetime service object for controlling the instance.
    5. Read(): It moves the reader to the next record in the result set.
    6. ToString(): It returns the string representing the current object.
    Consider an example to demonstrate the use of SqlDataReader object for accessing the student data from the database.

    Code:
    
    public static void Main ()
    {
    	SqlConnection con = new SqlConnection ( “ Data Source=SQLServer02; Initial Catalog=master; Integrated Security=SSPI”);
    	SqlCommand cmd = new SqlCommand “ select * from Students”, con );
    	con.Open();
    	SqlDataReader dr = cmd.ExecuteReader();
    	while ( dr.Read())
    	{
    		Console.ReadLine(dr[0]+” “ +dr[1]);
    	}
    	Con.Close();
    	Console.ReadLine();
    }
    
    
    3. DbParameters Object

    The DbParameter object are used for assigning the parameterized values to the stored procedures. The CreateParameter() method is used for creating the DbParameter object.

    Some of the properties of the DbParameter object are as follows:
    1. DbType: It is used for getting and setting the data type of the DbParameter object of the .NET frameowrk
    2. IsNullable: It gets or sets the value indicating the parameter accepts null values.
    3. Size: It is used to get or set the maximum size of the data present in the column.
    4. SourceColumn: It gets or sets the name of the source column mapped to the DtaaSet.
    5. SourceVersion: It is used to get or set the version of the datarow
    6. Value: It gets or sets the value of the parameter
    Some of the methods of the DbParameter object are as listed below:
    1. ResetDbType(): It resets the DbType property to the initial settings
    2. ReferenceEquals(): It checks whether the specified DbParameter object instances
    3. CreateObjRef(): It creates an object containing the information about the proxy generated for the communication object
    4. ToString(): It returns the string that represents the current object
    5. GetType(): It gets the type of the current instance
    Consider an example to demonstrate the student details passed as the parameter.

    Code:
    
    public void button2_click( object sender, EventArgs e)
    {
    	SqlConnection con = new SqlConnection( ConfigurationManager.ConnectionStrings[“constr”].ConnectionString);
    	con.Open();
    	string str = “ select * from student where StudID = @StudID”;
    	SqlCommand cmd = new SqlCommand( str,con );
    	cmd.Connection=con;
    	cmd.CommandType = CommandType.Text;
    	cmd.Parameters.Add(new SqlParameter (“StudID”, Int );
    	con.Open();
    	cmd.ExecuteQuery();
    	con.Close();
    	
    }
    
    

    2. Asynchronous Operations through Command Objects



    The connected objects are always linked with each other. As a result, the processing takes large amount of time. To overcome this issue, the user can asynchronously execute the commands. The performance and responsiveness is increased due to the asynchronous operations. The ADO.NET supports the asynchronous command execution and wait for the response from the previous commands that are executing.

    The SqlCommand class provides methods that helps user for asynchronous communication.
    1. BeginExecuteNonQuery(): It starts the process asynchronously by executing the T-SQL statement or the stored procedure which does not return rows.
    2. BeginExecuteXmlReader(): The method initiates the asynchronous execution of the T-SQL statement or stored procedure.
    3. EndExecuteNonQuery(): It completes the execution of the asynchronous T-SQL statement or stored procedure
    4. EndExecuteReader(): It completes the execution of the T-SQL statement and returns the SqlDataReader.
    5. EndExecuteXmlReader(): It completes the asynchronous execution of the T-SQL statement or a stored procedure

    Data Adapters in ADO.NET



    A data adapter is used in the connected environment. It is used for transferring the data to and from the database. It retrieves the data into the dataset. The comparison of data in the dataset with the database is performed and the database is updated.

    There are many connection objects that can be used with the data adapters. They are OleDbConnection, SqlConnection, OdbcConnection, and OracleConnection. The data adapter communicates with the database while it performs insert, delete and update.

    The properties that can be used in the data adapter are as mentioned below:
    1. InsertCommand: It is used for referring the data command for inserting data in the dataset.
    2. SelectCommand: It is used to refer to the DML statement for retrieving the data from the dataset.
    3. UpdateCommand: It is used for updating the dataset.
    4. DeleteCommand: It is used to delete data from the dataset.
    The methods of the data adepter are as mentioned below:
    1. Fill(): It is used for filling the dataset with the records from the database
    2. Update(): It is used to executing the appropriate commands for the corresponding Insert, Update and Delete command.
    Consider an example to demonstrate the use of the DataAdapter object and the SelectCommand property of the object.

    Code:
    
    public static void Main( string[] args )
    {
    	SqlConnection con = new SqlConnection();
    	con.ConnectionString =” Data Source = SQLSERVER2; Initial Catalog=master; 
    	User id=sa; Password=abcd1234”;
    	DataSet ds1 = new DataSet();
    	SqlDataAdapter da = new SqlDataAdapter();
    	SqlCommand cmd = new SqlCommand (“ Select * from OrderDetails”, con);
    	da.SelectCommand = cmd;
    	da.Fill ( ds1);
    }
    
    
    In the above code, the SqlDataAdapter object da is created. The select command accesses the records from the OrderDetails table. The Fill() method of the data adapter.

    Factory Classes in ADO.NET



    ADO.NET provides user with the classes created for any provider specific objects. The providers can be SqlClient, Odbc, OracleClient, and OleDb. They are known as DbProviderFactory classes. It contains the method for returning the data table. The System.Data.Common assembly contains the provider classes.

    There are some steps for displaying the data providers in ADO.NET.
    1. Open the Windows Form application in Visual Studio. Add a Button and DataGridView control on it.

      [​IMG]
    2. Assign the Name and Text property to the added controls.
    3. Add the following code on the button click event in the application.
      Code:
      
      using System;
      using System.Data;
      using System.Data.SqlClient;
      using System.Data.Common;
      
      public partial class Form1: Form
      {
      	public Form1()
      	{
      		InitializeComponent();
      	}
      	private void button1_Click ( object sender, EventArgs e)
      	{
      		DataTable provider = null;
      		Provider = DbProviderFactories.GetFactoryClasses();
      		dataGridView1.DataSource = provider;
      	}
      }
      
      
    4. Click on the Button in the Form, the following output is displayed.

      [​IMG]
     
    Last edited by a moderator: Jan 21, 2017

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice