Connection Oriented architecture is achieved by the use of Connection, Command and DataReader object.
The Command object works with the Connection object and used to execute SQL queries or Stored Procedures against the data source. You can perform insert, update, delete, and select operations with this object. It requires an instance of a Connection Object for executing the SQL statements as example.
SqlCommand cmd = new SqlCommand("SQL query or stored procedure", conn);
Steps for executing SQL query with command object
- Create a Connection Object and initialize with connection string.
- Create the command object and initialize with SQL query and connection object.
- Open the connection.
- Execute query with the help of any one method of command object.
- Store the result in DataReader object (In case of select SQL query)
- Close the connection.
Let’s take one example that will use command object. The table structure is as follows.
Example
using System;
using System.Data;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
SqlDataReader reader;
SqlConnection con;
protected void Page_Load(object sender, EventArgs e)
{
con = new SqlConnection("Data Source=(local);Initial Catalog=Employee;Integrated Security=True");
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "select * from tblemps";
cmd.CommandType = CommandType.Text;
try
{
con.Open();
reader = cmd.ExecuteReader();
GridView1.DataSource = reader;
GridView1.DataBind();
}
catch (Exception ex)
{
Label1.Text = "ERROR :: " + ex.Message;
}
finally
{
reader.Close();
con.Close();
}
}
}
Execute the above program, you will get the following output.
Output:
Important method of command object
- ExecuteReader: This method works on select SQL query. It returns the DataReader object. Use DataReader read () method to retrieve the rows.
- ExecuteScalar: This method returns single value. Its return type is Object. When you want single value (First column of first row), then use ExecuteScalar () method. Extra columns or rows are ignored. ExecuteScalar method gives better performance if SQL statements have aggregate function.
- ExecuteNonQuery: If you are using Insert, Update or Delete SQL statement then use this method. Its return type is Integer (The number of affected records).
- ExecuteXMLReader: It returns an instance of XmlReader class. This method is used to return the result set in the form of an XML document.
Important properties of Command class
- Connection
- CommandText
- CommandType
- CommandTimeout
CommandType is an important property of Command class. This property is used to determine which type of command being executed. It supports the following enumerators.
CommandType.StoredProcedure: It informs the Command object that the stored procedure will be used in place of simple SQL statements.
CommandType.Text: It informs the Command object that the CommandText value contains a SQL query.
CommandType.TableDirect: It indicates that the CommandText property contains the name of a table.
Using stored procedure with command object
First we understand that what is stored procedure and then we will use the stored procedure in our database program.
Stored Procedure
A stored procedure in SQL Server is a group of one or more SQL statements. Stored procedure are created and stored in the database.
When you execute the application, the SQL statements are parsed and execution plan is created. This happened every time whenever you execute the application.
It is not happened in case of stored procedure. Stored procedure compiles only once and stores in database with its execution plan. If we use stored procedure, then we can avoid recompilation of the query. It increases the performance of application. Another advantage is that it can be tested independent of the application. Stored procedures are easy to maintain. If any changes occur, then just update the stored procedure. The change will reflect in all pages automatically. It is a big advantage that rather than change SQL queries in all the pages just we need to update a single stored procedure.
Creating Stored Procedure
Open Microsoft SQL Server, select your database, right click and select create new stored procedure tab.
Here we have created a simple stored procedure named
"GetAllEmployees". It selects all the record from table tblEmps.
CREATE PROCEDURE [dbo].[GetAllEmployees]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT * from tblEmps
END
Just change the previous code as follows and you will get the same result.
SqlConnection con = new SqlConnection("Data Source=(local);Initial Catalog=Employee;Integrated Security=True");
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetAllEmployees";
First create a simple input screen to insert the data into database as given below.
Create a Stored Procedure with Input Parameter in the database.
CREATE PROCEDURE InsertEmployees
@EmpID integer,
@EmpName varchar(100),
@Gender varchar(50),
@Salary money,
@Address varchar(200),
@DepID integer
AS
BEGIN
-- Insert statements for procedure here
insert into tblEmps values(@EmpID,@EmpName ,@Gender ,@Salary ,@Address ,@DepID )
END
GO
<configuration>
<connectionStrings>
<add name="conString" connectionString="Data Source=(local);Initial Catalog=Employee;Integrated Security=True"/>
</connectionStrings>
</configuration>
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
public partial class Default2 : System.Web.UI.Page
{
SqlConnection conObject;
SqlCommand cmd;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnAdd_Click(object sender, EventArgs e)
{
string con =ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
conObject = new SqlConnection(con);
cmd = new SqlCommand();
cmd.Connection = conObject;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "InsertEmployees";
cmd.Parameters.Add("@EmpID",SqlDbType.Int).Value=txtEmpID.Text;
cmd.Parameters.Add("@EmpName",SqlDbType.VarChar).Value=txtName.Text;
cmd.Parameters.Add("@Gender", SqlDbType.VarChar).Value=txtGender.Text;
cmd.Parameters.Add("@Salary", SqlDbType.Money).Value=txtSalary.Text;
cmd.Parameters.Add("@Address", SqlDbType.VarChar).Value=txtAddress.Text;
cmd.Parameters.Add("@DepID", SqlDbType.Int).Value=txtDepID.Text;
try
{
conObject.Open();
int i=cmd.ExecuteNonQuery();
if(i>0)
{
Label1.Text = "Record Added Successfully !!";
}
}
catch(Exception ex)
{
Label1.Text="ERROR ::"+ex.Message;
}
finally
{
conObject.Close();
}
}
}
Using DataReader object
DataReader object works in connected mode. It is read only and forward only object. It is fast compare to DataSet. DataReader provides the easy way to access the data from database. It can increase the performance of application because it reads records one by one. Use read() method of DataReader to access the record.
For initializing the DataReader object, call the ExecuteReader method of the Command object. It returns an instance of the DataReader.
SqlCommand cmd = new SqlCommand("Your SQL query", conn);
SqlDataReader readerObj = cmd.ExecuteReader();
Once your task completed with the data reader, call the Close() method to close the DataReader.
readerObj.Close();
Important properties of DataReader object
FieldCount: It provides the number of columns in the current row.
HasRows: It provides information that, whether data reader contains row or not.
IsClosed: It indicates that whether data reader is closed or not.
RecordsAffected: Returns the number of affected records.
You can also get the value of particular column of the table by using the data reader object.
while(reader.Read())
{
string ID = reader["EmpID"].ToString();
string name = reader["Name"].ToString();
string gender = reader["Gender"].ToString();
string salary = reader["Salary"].ToString();
}
