How to use SqlCommand in asp.net c#
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 require a extra text of the query.
SqlCommand(String, SqlConnection) constructor require query text and a SqlConnection.
SqlCommand(String, SqlConnection, SqlTransaction) need query text, a SqlConnection and SqlTransaction.
SqlCommand class have many useful properties such as CommandText, CommandTimeout, CommandType, Connection, Notification, Parameters, Transaction, UpdatedRowSource etc. CommandText get or set Transact-SQL-Statement, table name or stored procedure to executed at the data source. CommandType property get or set a value which indicate how the CommandText property is to be interpreted. Connection property get or set SqlConnection. Parameters property get the SqlParameterCollection. Transaction property get or sets the SqlTransaction.
Many methods exists 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 build a SqlDataReader. ExecuteReader(CommandBehavior) method build a SqlDataReader using one of the CommandBehavior values (such as CloseConnection, Default, SingleResult, SingleRow etc).
ExecuteNonQuery method execute a Transact-SQL-Statement and returns the number of rows affected. ExecuteXmlReader method build an XmlReader object. ExecuteReader() method sends the Commandtext to the Connection and builds a SqlDataReader object. ExecuteScaler method execute the query and returns the first column of the first row in the result set.
SqlCommand Disposed event occurs when the component is disposed by Dispose method.
the following example code describe you more about SqlCommand in asp.net environment.
SqlCommand class have many useful properties such as CommandText, CommandTimeout, CommandType, Connection, Notification, Parameters, Transaction, UpdatedRowSource etc. CommandText get or set Transact-SQL-Statement, table name or stored procedure to executed at the data source. CommandType property get or set a value which indicate how the CommandText property is to be interpreted. Connection property get or set SqlConnection. Parameters property get the SqlParameterCollection. Transaction property get or sets the SqlTransaction.
Many methods exists 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 build a SqlDataReader. ExecuteReader(CommandBehavior) method build a SqlDataReader using one of the CommandBehavior values (such as CloseConnection, Default, SingleResult, SingleRow etc).
ExecuteNonQuery method execute a Transact-SQL-Statement and returns the number of rows affected. ExecuteXmlReader method build an XmlReader object. ExecuteReader() method sends the Commandtext to the Connection and builds a SqlDataReader object. ExecuteScaler method execute the query and returns the first column of the first row in the result set.
SqlCommand Disposed event occurs when the component is disposed by Dispose method.
the following example code describe you more about SqlCommand in 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>
