The SqlCommand Class is used for T-SQL, SQL statements and stored procedures to execute against a SQL Server database. This class cannot be inherited because it is a sealed class. The SqlCommand is a sealed class that inherits DbCommand Class and the ICloneable interface.
public sealed class SqlCommand : DbCommand, Cloneable
The DbCommand class is a base class for database-specific classes that represent commands. The ICloneable interface creates a new instance of a class with the same value as an existing instance.
Sealed classes are primarily used to prevent derivation. Because they can never be used as a base class, some run-time optimizations can make calling sealed class members slightly faster.
The System.Data.SqlClient namespace is used for the SqlCommand class.This namespace is .NET Framework Data Provider for SQL Server.
SqlCommand Class Constructor
A Constructor is a method that is used for instantiating a class. It never returns a value. It's name is the same as the class name but it might not have a parameter or it might have parameters. The SqlCommand Class contains four types of constructors.
- SqlCommand( )
-
SqlCommand(string)
-
SqlCommand(string,SqlConnection)
-
SqlCommand(string,SqlConnection,SqlTransaction)
To explain all of them first we need to create a table tbl_employee.
Fig 1. tbl_employee Definition
This tbl_employee table contains some data that are used in the following methods.
Here we define the SqlCommand class Constructors one by one.
(A). SqlCommand()
It is a SqlCommand class constructor. It has no parameters, which means it is a default constructor. We will use it to create an object of a SqlCommand class.
//create connection to Sql server database
SqlConnection con = new SqlConnection(@"Data Source=SANPEEP-PC\SANDEEP;Initial Catalog=Employee;User ID=sa; Password=123;Pooling=False");
con.Open();
SqlCommand cmd=new SqlCommand();//use SqlCommand() constructor
cmd.CommandText="select Emp_id,Emp_name,Emp_exp,Emp_address from tbl_employee";
cmd.Connection=con;
SqlDataAdapter dap=new SqlDataAdapter(cmd);
DataSet ds=new DataSet();
dap.Fill(ds,"EmployeeTable");
grdemployee.DataSource=ds;//grdemployee is a GridView Id
grdemployee.DataBind();
In the above code SqlCommand cmd=new SqlCommand() is used to create an object of the SqlCommand class. In this code CommandText is a property of the SqlCommand class object that is used to assign a string to the SqlCommand object. In the CommandText property we can assign a query or stored procedure for excution against a SQL Server database in string format. Another SqlCommand class object property is Connection which is used to assign a SqlConnection to the SqlCommand object. The remaining code excutes in a manner shown in Figure 2.
Fig 2. Represention of DataSet connection to SqlDataAdapter
(B). SqlCommand(string)
It is an constructor of SqlCommand class that is used to create an object of the SqlCommand class. It has one parameter that is a string type which means we pass a parameter that has a SQL query or stored procedure in a string format. Like:
//create connection to Sql server database
SqlConnection con = new SqlConnection(@"Data Source=SANPEEP-PC\SANDEEP;Initial Catalog=Employee;User ID=sa; Password=123;Pooling=False");
con.Open();
SqlCommand cmd = new SqlCommand("select Emp_id,Emp_name,Emp_exp,Emp_address from tbl_employee");//use SqlCommand(string) constructor
cmd.Connection=con;//con SqlConnection class object
SqlDataAdapter dap=new SqlDataAdapter(cmd);
DataSet ds=new DataSet();
dap.Fill(ds,"EmployeeTable");
grdemployee.DataSource=ds;//grdemployee is a GridView Id
grdemployee.DataBind();
(C). SqlCommand(string,SqlConnection)
It is an another constructor of SqlCommand class. It has two parameters. The first parameter is a string type that is used to assign a SQL Query or Stored Procedure in string format. The second parameter is of SqlConnection type that is used to assign a connection for a database. It is the simplest way. We mostly use it comparision above. Like:
//create connection to Sql server database
SqlConnection con = new SqlConnection(@"Data Source=SANPEEP-PC\SANDEEP;Initial Catalog=Employee;User ID=sa; Password=123;Pooling=False");
con.Open();
SqlCommand cmd = new SqlCommand("select Emp_id,Emp_name,Emp_exp,Emp_address from tbl_employee",con);//use SqlCommand(string,SqlConnection) constructor
SqlDataAdapter dap=new SqlDataAdapter(cmd);
DataSet ds=new DataSet();
dap.Fill(ds,"EmployeeTable");
grdemployee.DataSource=ds;//grdemployee is a GridView Id
grdemployee.DataBind();
(D). SqlCommand(string,SqlConnection,SqlTransaction)
This is another constructor of SqlCommand Class that has three parameters. The first parameter is a string type that is used to assign a SQL query or stored procedure in string format. The second parameter is a SqlConnection type that is used to assign a SqlConnection to the SqlCommand. And the last parameter is a SqlTransaction type that is used to assign which transaction we are using for a particular SqlCommand. The SqlTransaction instance is used to define our transaction commit and rollback in our C# code.
//create connection to Sql server database
SqlConnection con = new SqlConnection(@"Data Source=SANPEEP-PC\SANDEEP;Initial Catalog=Employee;User ID=sa; Password=123;Pooling=False");
con.Open();
SqlTransaction mytransaction = con.BeginTransaction();//a transaction begins
SqlCommand cmd = new SqlCommand("select Emp_id,Emp_name,Emp_exp,Emp_address from tbl_employee",con,mytransaction);//use SqlComman
(string,SqlConnection,SqlTransaction) constructor
mytransaction.Commit();//Method of SqlTransaction
SqlDataAdapter dap=new SqlDataAdapter(cmd);
DataSet ds=new DataSet();
dap.Fill(ds,"EmployeeTable");
grdemployee.DataSource=ds;//grdemployee is a GridView Id
grdemployee.DataBind();
SqlTransaction is not really used in the above metioned code; it is used when we use more than one DML Sql Statement.
OUTPUT
The above four samples of code gives us the same output no matter which we are used.
Fig. 3 is the output of the above mentioned code.
Conclusion:
We define the SqlCommand Class constructor. It will be useful for beginners.