ADO.NET Concepts With example

COMMAND OBJECT
 Command object is the biggest object in ADO.NET
 It is the only object which can perform actions with database
 It  can be created directly using Command class or can be created using Connection.Create
command (factory classes also contain creation of Command).
Ex:  SqlConnection sqlCon = new SqlConnection("......");
   SqlCommand sqlCmd = sqlCon.CreateCommand();
 Commands that we run depend upon the kind of query that we want to execute with database.
All databases support two types of queries.
i. Action Queries
ii. Non-action Queries
Action queries are those which change the state of database and which don‟t return any query
results(though they return the number of records affected). Ex: Insert, Delete and Update
statements
Non-action queries are those which don‟t affect the database but return the results to the
user. Ex: Select statement
Method of execution of queries:

Command object provides the following methods to execute queries:
1. ExecuteNonQuery()
2. ExecuteReader()
3. ExecuteScalar()
4. ExecuteXMLReader()
1. ExecuteNonQuery():  This method is used for executing the queries which perform some action
and change the state of the database. This method is used to execute insert, update and delete
statements.
Ex: Delete * from  tmpjobs;
        Insert into jobs values(„abc‟, 100, 120);
2. ExecuteReader(): This method can run any select statement.


Ex: Select ename from emp;   //which returns multiple values
       Select count(*) from emp; //which returns a single value
       Select E.Empno, E.Ename, D.Deptno, D.Dname  from emp E, Dept D where 
      E.Deptno=D.Deptno  //which returns values from multiple tables
3. ExecuteScalar(): This method is used for select statements which return only single values.
Ex: Select count(*) from emp;
4. ExecuteXMLReader(): This method is applicable only for SqlClient namespace. It is used for
queries which return xml data. But Oledb managed provider does not support this method.
Ex: Select * from jobs FOR XML AUTO

Note: ExecuteNonQuery(),  which is included from ADO.NET Version 1.0/1.1 is used for synchronous
execution of queries, whereas  BeginExecuteQuery() and EndExecuteQuery() methods which were introduced
in ADO.NET Version 2.0 are  used for asynchronous execution of queries.
All the methods of Command object can run all the actions. But we have to use this object carefully
depending upon our requirements, otherwise performance will go down.
Command Object Demo:
This program demonstrates the use of Command object for a delete statement. In this program when
the linklabel is clicked, the corresponding record in the job table of pubs database will be deleted
depending on job_id value given in textbox.
The following connection string is declared in app.config file:
<configuration>
  <appSettings>

    <add key="sqlCnStr" value="data source=mytoy; user id=sa; database=pubs"/>
  </appSettings>
</configuration>


Design:
The form is designed with just a textbox and a linklabel as shown below

Code:
    public partial class cmdobj : Form
    {
  SqlConnection cn = new SqlConnection(ConfigurationSettings.AppSettings["sqlCnStr"]);
                 SqlCommand cmd;
                 string stmt;
                 public cmdobj()
                 {
                       InitializeComponent();
                  }
                   private void cmdobj_Load(object sender, EventArgs e)
                  {
                         cn.Open();           
                   }
                   private void lnklblDelete_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
                  {
                 stmt = "delete from jobs where job_id=" + txtJobId.Text;
             cmd = new SqlCommand();
             cmd.CommandText = stmt;
             cmd.CommandType = CommandType.Text;
             cmd.Connection = cn;
             cmd.CommandTimeout = 20;
             int i = cmd.ExecuteNonQuery();
            if (i > 0)
                     MessageBox.Show("Record deleted successfully");
            else MessageBox.Show("Records not found");
            MessageBox.Show("No. of records deleted: "+i);
        
            }       
      
       }
When the job id is given in the textbox and delete linklabel is clicked, it should delete the
corresponding records from the database. For this purpose ExecuteNonQuery() method of command
object is used for executing the delete statement. If the records are successfully deleted it returns the
number of records deleted, otherwise returns zero value.


Batch SQL:
We can write more than one SQL statement in a single string and execute them as a group(internally
execution takes place one after the other) at the same time. This is called batch SQL.
The statements are separated by a semi-colon and written in the same string.
Ex: “select ename from emp; select * from jobs; select * from dept”
Batch SQL is very effective and saves time because it is not necessary to wait for the execution of
first  statement, in order to run the next statement. But there is also a great drawback in this approach,
which relates to the SQL injection attacks.
SQL Injection attacks:
If even a single statement which is harmful to the database is provided in the batch, it results in
stalling of the whole database. This is a great disadvantage of command objects.
For example, take the same demo written earlier. In that program there is a provision for the user to
enter the job_id in the given text box.
stmt = "delete from jobs where job_id=" + txtJobId.Text;
cmd = new SqlCommand();
cmd.CommandText = stmt;
cmd.CommandType = CommandType.Text;
This works fine as long as the user enters the correct job_id. But the drawback here is that the user can
include another statement in the same text box after providing the required input. Suppose the job_id
of the record to be deleted is 15. So the user is expected to enter 15 in the textbox, but instead if he
enters something like this:
15; drop table emp
So the total string becomes:
“delete from jobs where job_id=15; drop table emp”
As the command object is capable of performing batch SQL operations, it tries to execute both
statements on the database. This results in dropping of the complete emp table which is not desirable.
Some validations, permissions and stored procedures can be used to overcome these problems.
Moreover SQL2005 and 2008 also provide some utilities to avoid these scenarios.
Making little changes in the design and code of the application can also help in avoiding SQL
injection attacks to some extent.
Demo2: Adding and updating records in a table from front-end
In this program the operations add and update can be performed on the jobs table from the front-end
by using the command object. Three different ways of adding the records are shown here. The form
design is as follows:

The four text boxes correspond to the four columns in the jobs table. Since  job_id is an identity
column, there is no need to enter any values for that column while adding records. But while updating
records it is necessary to provide job_id value to identify the correct record.
Code:
string stat;
SqlConnection cn;
SqlCommand cmdobj;
//to open connection and execute the command statement
public int performAction(string st)
{
    cn = new SqlConnection("data source=mytoy; user id=sa; database=pubs");
    cn.Open();
cmdobj = new SqlCommand(stat, cn);
    int i = cmdobj.ExecuteNonQuery();
    return i;
}
//To update records
private void btnUpdate_Click(object sender, EventArgs e)
{
    stat = "update jobs set job_desc='" + txtDesc.Text + "', min_lvl=" +  txtMinLvl.Text + ", max_lvl=" + 
                txtMaxLvl.Text + " where job_id=" + txtJobId.Text;
    int a = performAction(stat);
    MessageBox.Show("Records updated:" + a.ToString());
}
//First Method of adding records
private void btnAdd_Click(object sender, EventArgs e)
{
    stat = "insert into jobs values('" + txtDesc.Text + "'," + 
                     txtMinLvl.Text + "," + txtMaxLvl.Text + ")";
   int a = performAction(stat);
    if (a > 0)

     MessageBox.Show("Record added sucessfully");
    else
        MessageBox.Show("Unsuccessful operation");
}
//Second Method of adding records
private void btnAdd_Click(object sender, EventArgs e)
{

    stat=string.Format("Insert into jobs values('{0}',{1},{2})", txtDesc.Text, txtMinLvl.Text, txtMaxLvl.Text);
    int a = performAction(stat);
    if (a > 0)
       MessageBox.Show("Record added sucessfully");
    else
       MessageBox.Show("Unsuccessful operation");
}
//Third Method of adding records
private void btnAdd_Click(object sender, EventArgs e)
{
    cn = new SqlConnection("data source=mytoy; user id=sa; database=pubs");
    cn.Open();
stat = "insert into jobs values(@a,@b,@c)";
    cmdobj = new SqlCommand(stat, cn);
    cmdobj.Parameters.AddWithValue("@a", txtDesc.Text);
    cmdobj.Parameters.AddWithValue("@b", txtMinLvl.Text);
    cmdobj.Parameters.AddWithValue("@c", txtMaxLvl.Text);
    int a=cmdobj.ExecuteNonQuery();
          
    if (a > 0)
       MessageBox.Show("Record added sucessfully");
    else
       MessageBox.Show("Unsuccessful operation");
}

In the above form all front-end objectives can be viewed with respect to databasees. A front-end
should provide design and that design should be interactive with databases.
Three ways of adding records to the database are shown in this program. In the first one, a command
string is provided to the command object with the textbox values directly embedded in it. This string
is executed by using ExecuteNonQuery method.
In the second approach also a command string is provided,but the values are  inserted into the string
as arguments using string.Format method.
In the third approach, command parameters are used to supply the textbox values to the command
string which is executed by using the ExecuteNonQuery() method.

DataReader
In ADO.NET there are only two objects which can store data. One is DataSet while the other is
DataReader.
Features of DataReader:
 DataReader is a forward only accessible object
 It is a read-only object
 It provides sequential access for rows and can be extended for sequential column access also.
How DataReader Works:

The select statement from the command object is processed and a ResultSet with the required data is
created in the form of rows and columns at the database server. The address of this ResultSet is
provided to the DataReader.
cmd=new SqlCommand(“select * from emp”, cn); 
dr=cmd.ExecuteReader();   //provides the address of the ResultSet to the DataReader(dr)
When the read() method of the DataReader is used it returns true if records are present and false if
records are not present. At the same time if records are present, a similar structure like the ResultSet is
created at the client side and the first record is fetched into it. The DataReader reads the record from
this structure.
dr[n] is an indexer to access its created structure.
Ex:  dr[0] provides the first column of the retrieved row
        dr[1] provides the second column of the retrieved row

A pointer exists on ResultSet to identify the record being read. When the read() method is again called
from the DataReader, the pointer moves to the next record if present. The structure at the DataReader
now gets overwritten by this record.
Drawbacks:
 It is a forward-only and read-only object
 DataReader is a connection-oriented object, which means that the access to data is possible
only as long as the connection exists.
 DataReader works only on databases, but not on other data sources like XML files.
Note: It is always necessary to close the DataReader object after the required data is read.
Inspite of all drawbacks, DataReader usage is in its performance. To access data, DataReader is the
fastest object in entire .NET(not only in ADO.NET).
DataReader capabilities are enhanced in .NET 2.0. The new features are:
 It can communicate with other objects like DataSet
             DataReader DataSet(DataTable)
             DataTable  DataReader
 The biggest enhancement is MARS(Multiple Active ResultSets)
(In earlier versions it was SAR(Single Active ResultSet)
Multiple Active ResultSets(MARS):

Multiple ResultSets are created when Batch SQL is provided. i.e. for each individual statement in the
batch, a different ResultSet is created.  But normally only one ResultSet is active at a time. If the next
ResultSet has to be active, then the previous one should become inactive. This is called Single Active
ResultSet eventhough multiple ResultSets are present.  But with MARS, more than one ResultSet can
be active at a time. This property can be specified in the connection string by giving true or false .

SqlConnection cn=new SqlConnection(“data source=...... ; MultipleActiveResultSets=true; ...”);
SingleActiveResultSet is the default and so we need to specify MARS explicitly in connection string.
Using MARS eliminates the need to open and close connection for each ResultSet and hence
improves the performance. But this is not the case at all times. The performance is improved only
when multiple commands have to be executed using a single connection. On the other hand it is not
worth using for very few commands and it even results in some overhead in certain situations. So
MARS should be used only when it is really needed.
Note:
We can use ExecuteReader() method for action queries also(insert, update, delete). When there is a
combination of select and any action query in Batch SQL , we have to use ExecuteReader, which gets
address of the ResultSet of select query and also does the action of the action query.
DataReader Demo:
This program shows how a DataReader can be used to read the data fetched from the database.  A
job_id is provided in the relevant textbox and searched for the record with that job_id. If matching
record is present in the database, it is fetched into the datareader, which reads the column values and
fills the textboxes provided for the purpose.

SqlConnection cn = new SqlConnection("data source=mytoy; user id=sa; 
                                      database=pubs");
SqlCommand cmd;
SqlDataReader dr;
private void btnSearch_Click(object sender, EventArgs e)
{
     string stat = "select * from jobs where job_id=" + txtJobId.Text;
     if (cn.State.ToString() == "Closed")
        cn.Open();
     cmd = new SqlCommand(stat, cn);
     dr = cmd.ExecuteReader();
if (dr.Read())
     {

txtDesc.Text = dr[1].ToString();
txtMinVal.Text = dr[2].ToString();
txtMaxVal.Text = dr[3].ToString();
dr.Close();
      }
else
         MessageBox.Show("Record not found");
          
}
More DataReader Methods and Properties:
1.dr.GetString(0):  This statement is used to retrieve the data in the required format. Here the
requirement is to retrieve the first column value of the DataReader in the form of a string. Normally
for this purpose, the below method is adopted:
textBox1.Text=dr[0].ToString();
Here the data is first retrieved and then converted into string format. But if dr.GetString(0) is used, the
data is directly retrieved in the form of string, which aids in  better performance.
2.dr.GetInt32(1): This works similar to the above method, but fetches data in the form of an integer.
   Similarly there are methods like GetDouble, GetByte,GetDateTime etc. for different datatypes.
3. HasRows Property: It checks whether records are present or not and returns true or false
accordingly. This property provides better performance than Read() because Read method checks the
presence of the records and also reads the records. Since HasRows property does not read the records
it is faster.
4.IsClosed: It returns true if the DataReader is closed, otherwise returns false.
If cmd.ExecuteReader(CommandBehaviour.CloseConnection) is used then connection gets closed
when DataReader is closed. In such cases, the state of the connection  can be checked by using
IsClosed property.
5.IsDBNull: This property is used to check if a DataRow is null. Returns true if it is null, otherwise
returns false.
Ex: if(dr.IsDBNull[0]) checks whether the first row is null or not.
DataReader Demo2:
This wpf program demonstrates the retrieval of columnwise data from database using DataReader.
This kind of sequential access is required  when there are columns with more volume of data like
structures, MS-Word documents, Excel etc.
Here a listbox, image and a button are taken in the window. The button code is written so that the
image names in the table are shown in the list box. When the required image name is chosen in the
listbox, the actual image is shown beside it.

SqlConnection cn = new SqlConnection("data source=mytoy; user id=sa;
                       database=pubs");
SqlCommand cmd;
SqlDataReader dr;
private void btnViewPubs_Click(object sender, EventArgs e)
{
    cmd = new SqlCommand("select pub_id, logo from pub_info", cn);
    FileStream stream;
    BinaryWriter writer;
    int bufferSize = 100;
    byte[] outByte = new byte[buffersize];
    long retval;
    long startIndex = 0;
    string pubId = "";
    cn.Open();
SqlDataReader reader = 
                    cmd.ExecuteReader(CommandBehavior.SequentialAccess);
    while (reader.Read())
    {
        pubId = reader.GetString(0);
string fname = "logo" + pubId + ".bmp";
        stream = new FileStream(fname, FileMode.OpenOrCreate,
                                FileAccess.Write);
        writer = new BinaryWriter(stream);
        startIndex = 0;
retval = reader.GetBytes(1, startIndex, outByte, 0, bufferSize);
while (retval == bufferSize)
        {
            writer.Write(outByte);
writer.Flush();
startIndex += bufferSize;
retval = reader.GetBytes(1,startIndex,outByte,0,bufferSize);
        }
writer.Write(outByte, 0, (int)retval - 1);
        writer.Flush();
writer.Close();
        stream.Close();
        listBox1.Items.Add(fname);

}
reader.Close();
cn.Close();
}
private void listBox1_SelectionChanged(object sender,
                                    SelectionChangedEventArgs e)
{
    BitmapImage bi = new BitmapImage();
    bi.BeginInit();
bi.UriSource=new 
Uri(Environment.CurrentDirectory+@"\"+listBox1.SelectedItem.ToString());
    bi.EndInit();
image1.Source=bi;
}
The namespace System.IO is needed for file stream operations. Bitmap images are accessed
sequentially by using the ExecuteReader method of the command object. Finally the filenames from
the table are displayed in the listbox. When image name is selected in the listbox, it displays the
image in the image placeholder.
The result will be as shown in this screen shot:

Stored Procedures:
Different methods of writing programs in back-end are:
 Blocks (T-SQL for SQL Server, PL/SQL for Oracle) - Blocks compile everytime when called.
They are not stored
 Procedures - Procedures get compiled and stored as part of database objects. So they are
faster in execution. They need to be explicitly called using an execute statement
 Functions -  They also get compiled and stored like procedures. They can be called with a
select statement.
 Triggers – They are executed automatically(implicitly called)

Why use Stored Procedures?
Stored Procedures are used because of the following features they offer:
 Modular programming
 Distribution of work
 Database security
 Faster execution
 Network traffic reduction
 Flexibility
ADO.NET uses Command object to invoke procedures.  SQL 2005/.NET 2.0 provided a new feature
of writing procedures in C#.NET and storing them in SQL Server. This option is provided as a
separate project option.  From Visual Studio we can open SQL Server by following the below path:
FileNew Project  Database SQL Server
Apart from this, VS.NET provides options like Server Explorer, T-SQL Editor where we can write,
compile, execute and store procedures at back-end.
Procedures:
A typical procedure looks like the following one:
CREATE PROCEDURE dbo.AddJob
(
@jobdesc varchar(50),
@minlvl int,
@maxlvl int
)
AS
Insert into jobs values(@jobdesc, @minlvl, @maxlvl)
RETURN
Every parameter in a procedure should be preceded by a special character @ to differentiate from the
column names of the table. By default all the declared parameters are input parameters. If the
procedure has to return values, then the output parameters must be explicitly specified by using
OUTPUT keyword for SQL Server and OUT for Oracle.

Calling procedures from client-side:
This is the code for adding records into the jobs table. The three column values are entered into the
text boxes(no need to add values to the first column because it is an identity column) and at the click
of a button the whole record should be entered into the table. This is done by using the stored
procedure shown earlier. To call that procedure, the button click code should be written as follows:
cmd = new SqlCommand("AddJob", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@jobdesc", txtJobDesc.Text);
            cmd.Parameters.AddWithValue("@minlvl", txtMinLvl.Text);
            cmd.Parameters.AddWithValue("@maxlvl", txtMaxLvl.Text);
            cmd.ExecuteNonQuery();
MessageBox.Show("Record Added");
Here the textbox values are assigned to the parameters of the procedure by using
Parameters.AddWithValue() method using command object. When the command is executed, it
executes the procedure at the database server and records are added into the table.
Note: If OleDb is used then the parameters should be in same order as  the columns in the table, but
for SQL Server this is not required.
The command types that are supported by ADO.NET are :
i. Text: This is default. It can be any query including the table name.
ii. StoredProcedure: To call a procedure at the back-end
iii. TableDirect: This is mainly for OleDb, where the table name has to be specified in place
of command text.
Procedures can be written to return query results also(i.e. select statement results). To execute the
procedures that return query results, ExecuteReader should be used from the command object instead
of ExecuteNonQuery as shown below:
   cmd = new SqlCommand("jobsinfo", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr = cmd.ExecuteReader();
But it is not preferred to write such procedures because all databases do not support them. But for
returning values from the procedures,  nearly all back-ends support directional parameters-IN, OUT,
INOUT.
i. IN Parameter: It is read-only in procedure i.e.the procedure can only read the value from
it and but cannot return value to it. The direction of this parameter is from the calling
program (client) to called program(server).
ii. OUT Parameter: It is write-only which means that the procedure can only return values to
it, but cannot read any values from it. Direction is from server to client
iii. INOUT Parameter: It can be used for both Read and Write. It is bi-directional i.e. from
server to  client as well as from client to server.
Demo-Stored Procedures:
This program demonstrates the use of OUTPUT direction parameter and also introduces the SQL
provider specific parameter class for working with stored procedures.

Here when job id is provided and searched, the corresponding job description should be displayed in
the second text box. For this purpose a stored procedure is used which takes job id  as the input
parameter and then gives its job description as the output parameter.
First the stored procedure is created as follows:
CREATE PROCEDURE dbo.GetJobInfo
(
@jobId int,
@jobDesc varchar(50)OUTPUT
)
AS
    select @jobDesc=job_desc from jobs where job_id=@jobId
  
RETURN
When the Search linklabel is clicked the stored procedure has to be called. So the code for calling this
procedure is written in the click event of that link label. The code is as follows:
SqlConnection cn = new SqlConnection("data source=mytoy; user id=sa; 
                                      database=pubs");
SqlCommand cmd;
private void llblSearch_LinkClicked(object sender,
                                        LinkLabelLinkClickedEventArgs e)
{
    SqlParameter pJobId, pJobDesc;
    cn.Open();
cmd = new SqlCommand("GetJobInfo", cn);
    cmd.CommandType = CommandType.StoredProcedure;
    pJobId = new SqlParameter("@jobId", SqlDbType.Int);
    pJobId.Value = int.Parse(txtJobId.Text);
    cmd.Parameters.Add(pJobId);
pJobDesc = new SqlParameter("@jobDesc", SqlDbType.VarChar, 50);
    pJobDesc.Direction = ParameterDirection.Output;
    cmd.Parameters.Add(pJobDesc);
cmd.ExecuteNonQuery();
MessageBox.Show("Procedure Executed");
    txtJobDesc.Text = cmd.Parameters["@jobDesc"].Value.ToString();
}
In this demo SqlParameter is used, which is another provider specific class used to prepare
parameters. The created parameters can be added in command object parameters collection.

In addition to the above, parameters can also be prepared as shown below:
(i) cmd.Parameters.Add("@jobId", SqlDbType.VarChar, 50);
(ii) cmd.Parameters.Add(new SqlParameter("@jobId",
                               SqlDbType.VarChar,50));
Note: For single valued queries ExecuteScalar() should be used, otherwise it takes the first value by
default from the retrieved values. ExecuteScalar() provides better performance because of its single
return value.