SqlCommand
SqlCommand represents a Transact-SQL-Statement or stored procedure to execute against a SQL Server Database. To create a new instance of SqlCommand instance you can use any one of these constructors SqlCommand(), SqlCommand(String), SqlCommand(String, SqlConnection), or SqlCommand(String, SqlConnection, SqlTransaction). SqlCommand() initializes a new instance of the SqlCommand class. SqlCommand(String) constructor requires an extra text of the query. SqlCommand(String, SqlConnection) constructor requires query text and a SqlConnection. SqlCommand(String, SqlConnection, SqlTransaction) needs query text, a SqlConnection, and SqlTransaction.
The SqlCommand class has many useful properties such as CommandText, CommandTimeout, CommandType, Connection, Notification, Parameters, Transaction, UpdatedRowSource, etc. CommandText gets or set Transact-SQL-Statement, table name, or stored procedure to execute at the data source. CommandType property gets or sets a value that indicates how the CommandText property is to be interpreted. Connection property gets or sets SqlConnection. The Parameters property gets the SqlParameterCollection. Transaction property gets or sets the SqlTransaction.
Many methods exist in SqlCommand class such as BeginExecuteNonQuery(), BeginExecuteReader(), BeginExecuteXmlReader(), Cancel, Clone, CreateParameter, Dispose(), EndExecuteNonQuery, EndExecuteXmlReader, ExecuteNonQuery, ExecuteNonQueryAsync(), ExecuteReader(), ExecuteReaderAsync(), ExecuteScaler, ExecuteXmlReader, GetType, ResetCommandTimeout, ToString, etc.
ExecuteReader() method sends the CommandText to the connection and builds a SqlDataReader. ExecuteReader(CommandBehavior) method builds a SqlDataReader using one of the CommandBehavior values (such as CloseConnection, Default, SingleResult, SingleRow, etc).
ExecuteNonQuery method executes a Transact-SQL-Statement and returns the number of rows affected. ExecuteXmlReader method builds an XmlReader object. ExecuteReader() method sends the Commandtext to the Connection and builds a SqlDataReader object. The ExecuteScaler method executes the query and returns the first column of the first row in the result set.
SqlCommand Disposed event occurs when the component is disposed of by Dispose method.
The following example code describes more about SqlCommand in the asp.net environment.
SqlCommandExample.aspx
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Configuration" %>
<!DOCTYPE html>
<script runat="server">
protected void Page_Load(object sender, System.EventArgs e) {
if(!Page.IsPostBack){
SqlConnection mySqlConnection;
SqlCommand mySqlCommand;
SqlDataReader mySqlDataReader;
mySqlConnection = new SqlConnection();
mySqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
mySqlCommand = new SqlCommand();
mySqlCommand.CommandText = "SELECT TOP 8 ProductID, ProductName, UnitPrice FROM Products";
mySqlCommand.CommandType = CommandType.Text;
mySqlCommand.Connection = mySqlConnection;
mySqlCommand.Connection.Open();
mySqlDataReader = mySqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
GridView1.DataSource = mySqlDataReader;
GridView1.DataBind();
mySqlCommand.Dispose();
mySqlConnection.Dispose();
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>asp.net SqlCommand example: how to use SqlCommand</title>
</head>
<body>
<form id="form1" runat="server">
<asp:Label ID="Label1" runat="server" Font-Size="X-Large" Font-Bold="true" ForeColor="Teal">
SqlCommand Example
</asp:Label>
<br /><br />
<div>
<asp:GridView
ID="GridView1"
runat="server"
BackColor="HotPink"
ForeColor="WhiteSmoke"
HeaderStyle-BackColor="DarkOrange"
BorderColor="Orange"
>
</asp:GridView>
</div>
</form>
</body>
</html>
