ADO stands for ActiveX Data Objects. Command object is the biggest object in ADO.NET. It is the only object which can perform actions with database. You can create instances of Command object using Command class or Connection.Create. For absolute beginners in this session let us explore more about ADO.NET architecture with examples.
Connection using ADO.NET
SqlConnection sqlConn = new SqlConnection("............");
SqlCommand sqlCmd = sqlConn.CreateCommand();
All databases supports 2 types of queries.
Action Queries – Action queries are those which change the state of database and which don’t return any results except the number of records affected. Example of few action based queries are Insert, Delete & Update statements.
Non-action Queries – Non-action queries are those which don’t affect the database but return the result. Example of non-action based queries are Select statements.
ADO.NET Methods to Executing queries
ADO.NET Command object provides the following methods to execute queries.
- ExecuteNonQuery()
- ExecuteReader()
- ExecuteScalar()
- ExecuteXMLReader()
1. ExecuteNonQuery
This method is used to execute those queries which perform some action & changes against the state of the database. We use this method to execute action based queries such as Insert, Delete or Update records.
Ex:
Delete * from EmpDetails;
Or
INSERT INTO EmpDetails VALUES(‘Biswabhusan’, ‘Software Developer’, ‘InfoSys’);
2. ExecuteReader
Using this method we can execute any kind of Select Statements. ExecuteReader returns single or multiple value depending upon the Query.
Ex:
SELECT EmpName FROM EmpDetails; //This query returns multiple values. SELECT count(*) FROM EmpDetails; //This query returns a single value. SELECT emp.EmpID, emp.EmpName, dep.DepNo, dep.DepName from EmpDetails emp, DepDetails dep where emp.DepNo = dep.DepNo //This conditional query returns values from multiple tables.
3. ExecuteScalar
This method is used for Select statements which return only single values.
Ex:
SELECT count(*) FROM EmpDetails;
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 JobDetails 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.
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 semicolon and written in the same string.
Ex:
“SELECT empName FROM EmpDetails; SELECT * FROM JobDetails; SELECT * FROM DepDetails”
Batch SQL is very effective & 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.
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. Using DataReader we can only read the data. It is a read-only object of ADO.NET. It provides sequential access for rows & 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 EmpDetails”, conn); 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 of DataReader – 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 Framework.
More DataReader Methods and Properties
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.
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.
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 performs faster.
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.
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.
Stored Procedures
Different methods of writing programs in back-end are Blocks (T-SQL for SQL Server, PL/SQL for Oracle) – Blocks compile every time when called. They are not Stored Procedures – Procedures get compiled and stored as part of database objects. So they perform faster in execution. They need to be explicitly called using an execute statement or 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
A typical procedure looks like the following one.
CREATE PROCEDURE dbo.AddJob ( @jobdesc varchar(50), @minlvl int, @maxlvl int ) AS INSERT INTO JobDetails 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:
Text: This is default. It can be any query including the table name.
StoredProcedure: To call a procedure at the back-end.
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.
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).
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.
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.
