Ado.Net Tutorial: Introduction to Ado.Net- Part2 Sending SqlCommand object and Getting Result using DataReader.

Introduction to Ado.Net:

Introduction:

This is Second Part of Ado.Net Introduction part1. In the first part i explained Basics of Ado.Net and How to Establish Connection With Database.

               Now in this part we will see how to send request using Sql Command Object and How to Get the Result from Database using DataReader Object.

Description:

2). Sending Request as an Sql Statement:

So After Establishing Connection with the Database next step is sending a request as an Sql Statement.We will use command object to do this thing. Command Object have following Constructors.

                                 Command( )
                                 Command(string CommandText, Connection Conn)

CommandText: Here Command Text means any sql statement like select,insert,update,delete statements or Stored Procedure name.

Properties:

Connection: Gets or Sets the Connection associated with the Command Object.
CommandText : Gets or Sets the Sql Statement Associated with the Command Object.

We can create the Command Object in the following manner
Connection Con=new Connection(string ConnectionString); Command ObjCmd=new Command(); ObjCmd.CommandText="Sql Statement/Stored Procedure"; ObjCmd.Connection=Con; or string ConnectionString=" Credentials"; string SqlStatement="Some Statement"; Connection Con=new Connection(ConnectionString); Command ObjCmd=new Command(SqlStatement,Con);
Note: One thing you should remember here i.e You have to prefix the data provider api prefix before Connection and Command Classes.

 3). Capturing Results given by the data source:

Now we completed step2 of Sending request as an Sql Statement. Now our next step is to Get the result using Sql Statement in the Command Object.

Command Object itself have the methods to capture the results. These are

- Call ExecuteReader( ) method to Execute the Select statement in Command Object which returns data as Rows and Columns. This method returns an object of class DataReader which contains data that is retrieved from data source in the form of rows and columns.

-Call ExecuteScalar() method when we want to execute a Select Statement that returns a single value result. The method returns result of the query in the form of a object. We have to cast it our required type later.

-Call ExecuteNonQuery( ) method when we want to execute any Statement other than select, like Insert, Update, Delete etc. The method returns a numeric value telling the number of rows affected by the statement.

So Calling one of above three methods to capture the results is our third step.

 Next step is to retrieve the data from Data Reader:

DataReader class holds the data in the form Rows and Columns. To access data from this DataReader it provides methods. They are

GetName(int ColumnIndex):

Returns the Name of Column in the Specified Column Index. Here Return type is String.

Read( ):

If we call this method, it moves the record pointer to next record in datareader object. It returns true if datareader object contains data in the next row, returns false if it contains no data. Return type is boolean.

GetValue(int ColumnIndex ):

We use this method to get the Column values from the row pointer is Pointing. 

We can access the row pointer in the following ways

                       DR[Column Index]                  Return Object Type

                       DR[Column Name]                 Return Type is Object

GetNextResult( ):

  Suppose if your Select Statement returns multiple tables then we need to move between tables to access them. So GetNextResult( ) method used to move the pointer to next table in DataReader object. It returns  true if the Datareader object contains table else returns false.

Conclusion:

I hope you got full pledge information on Introduction to Ado.Net and Ado.Net Object Communication Model. In later sessions we will see how to establish Connection with Sql Server Database, How to work with SqlCommand Object ,How to retrieve Data From DataReader with Example.

Don't Forget to Comment and Share if you like this post for our friends. Let me know if you have any other requirement. Thank You.