Sql Command

Sql Command

SQL COMMAND:

Sql Command is a connected class of Ado.Net Object model. Sql Command Class is used to perform various database operations over a given connection object (Sql server Database). Sql Command Class is used to perform both synchronous and asynchronous operations. The available methods and properties of Command Object allow you to execute a Sql query or stored procedure.

Synchronous Operation:

ExecuteNonQuery:
ExecuteNonQuery method of command object allows performing insert, deleting or updating the records in the database. The following steps are involved to execute a Command Object:

1. Initialize the SqlConnection object with the connection string.
2. Initialize the SqlCommand object and use the connection property and command text property.
3. Open the connection and use the ExecuteNonQuery method of Command Object to execute the query and then close the connection.

To demonstrate the command object methods, consider a sample Employee Details Table. Open Visual Studio and create a Windows Form Application Project, give it a name and save the application.
Drag and drop five label and five textbox controls to the form and change the properties of the controls. Change the Text Property of all label controls to Employee ID, Employee Name, Employee Phone no, Employee Address and Employee DOB respectively. Now change the Name Property of all the textbox controls to txtemployeeid, txtemployeename, txtphoneno, txtemployeeaddress and txtemployeedob respectively.
The following query is used to design the Table in the Sql server database.
Create database demo
Create table EmployeeDetails

 create database Demo
 use demo
 create table employeedetails
 (
  EmpolyeeID int identity primary key,
  EmployeeName varchar(235),
  EmployeePhoneNO varchar(235),
  EmployeeAddress varchar(235),
  EmployeeDOB varchar(235),
 ) 

Method 1: Using Properties (Command Text, Command Type and Connection):
The following code snippet shows u the usage of the default constructor to execute the sql query statement. The following query is used to insert the record(s) to the database using the above said properties.

 //Read the value of connectionstring form the web.config file
 and assign it to cnnstring variable.
 cnnstring = ConfigurationManager.ConnectionStrings["cnn"].ConnectionString;
 SqlConnection cnn = new SqlConnection();
 //connection properties.
 cnn.ConnectionString = cnnstring;
 SqlCommand cmd = new SqlCommand();
 cmdtext = "Insert into employeedetails values('" + txtemployeeid.Text +
    "','" + txtempname.Text + "','" + txtphoneno.Text + "','" 
 + txtaddress.Text + "','" +          txtdob.Text + "')";
 //cmd text and cmd connection properties.
 cmd.CommandText = cmdtext;
 cmd.Connection = cnn;
 // Open the connection and execute the query and then close the connection.
 cnn.Open();
 cmd.ExecuteNonQuery();
 cnn.Close();

Method 2: Constructor with parameters.
The following code explains the constructor which takes “sql query statement” as parameter.

 cnnstring = ConfigurationManager.ConnectionStrings["cnn"].ConnectionString;
 SqlConnection cnn = new SqlConnection(cnnstring);
 cmdtext = "Insert into employeedetails values('" + txtemployeeid.Text + 
      "','" +txtempname.Text + "','" +txtphoneno.Text + 
            "','"+ txtaddress.Text + "','" +txtdob.Text + "')";
 SqlCommand cmd = new SqlCommand(cmdtext);
 cmd.Connection = cnn;
 cnn.Open();
 cmd.ExecuteNonQuery();
 cnn.Close();

Method3: Using SqlParameters:
In the above example we have directly the textbox properties in the sql query, the Command object allows us to embed these values by using the properties. The following code uses the third constructor from the above table which takes string and connection as parameters.

 cnnstring = ConfigurationManager.ConnectionStrings["cnn"].ConnectionString;
 SqlConnection cnn = new SqlConnection(cnnstring);
 SqlCommand cmd = new SqlCommand("insert into employeedetails values 
                  (@empid,@empname,@phoneno,@address,@dob)", cnn);
 cnn.Open();
 cmd.Parameters.Add(new SqlParameter("@empid", txtempid.Text));
 cmd.Parameters.Add(new SqlParameter("@empname", txtempname.Text));
 cmd.Parameters.Add(new SqlParameter("@phoneno", txtphoneno.Text));
 cmd.Parameters.Add(new SqlParameter("@dob", txtdob.Text));
 cmd.Parameters.Add(new SqlParameter("@address", txtaddress.Text));
 cmd.ExecuteNonQuery();
 cnn.Close();

ExecuteScalar:
Execute Scalar method is used to retrieve a single value from the table. It returns the first column of the first row value in the result set.
The following code demonstrates the use of Execute Scalar Method:

 cnnstring = ConfigurationManager.ConnectionStrings["cnn"].ConnectionString;
        SqlConnection cnn = new SqlConnection(cnnstring);
        SqlCommand cmd = new SqlCommand("select employeename from employeedetails "+
   "where empolyeeid ='100' ", cnn);
        cnn.Open();
        cmd.ExecuteNonQuery();
        Response.Write("EmployeeName:" + cmd.ExecuteScalar() + "");
        cnn.Close();

Asynchronous Operations:

BeginExecuteNonQuery and EndExecuteNonQuery:
BeginExecuteNonQuery Method initiates the asynchronous execution of the Transact-SQL statement or stored procedure that is described by the SqlCommand. (Reference: MSDN library).

cnnstring = ConfigurationManager.ConnectionStrings["cnn"].ConnectionString;
SqlConnection cnn = new SqlConnection(cnnstring);
SqlCommand cmd = new SqlCommand("Insert into employeedetails 
     values('"+txtemployeeid.Text +
     "','" +txtempname.Text + "','" + txtphoneno.Text + 
     "','"+txtaddress.Text + "','" + txtdob.Text + "')",
     cnn);
IAsyncResult result = cmd.BeginExecuteNonQuery();
cmd.EndExecuteNonQuery(result);
cnn.Close();