Asp.net,
C#,
Parameter,
SqlConnection
The process of SqlParameter using parameter contains two steps:
Create SqlParameter object and insert there value with applicable properties define the parameter in the SqlCommand command string, and assign the SqlParameter object to the SqlCommand object. When the SqlCommand executes, parameters will be replaced with values specified by the SqlParameter object.
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Configuration" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
protected void Page_Load(object sender, System.EventArgs e) {
if (!Page.IsPostBack) {
SqlConnection MyConnection;
SqlCommand MyCommand;
SqlDataReader MyReader;
SqlParameter ITEMParam;
MyConnection = new SqlConnection();
MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings["AppConnectionString1"].ConnectionString;
MyCommand = new SqlCommand();
MyCommand.CommandText = "SELECT * FROM ITEM WHERE ITEMNAME = @ITEMNAME";
MyCommand.CommandType = CommandType.Text;
MyCommand.Connection = MyConnection;
ITEMParam = new SqlParameter();
ITEMParam.ParameterName = "@ITEMNAME";
ITEMParam.SqlDbType = SqlDbType.VarChar;
ITEMParam.Size = 25;
ITEMParam.Direction = ParameterDirection.Input;
ITEMParam.Value = "COMPUTER";
MyCommand.Parameters.Add(ITEMParam);
MyCommand.Connection.Open();
MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);
GridViewExample.DataSource = MyReader;
GridViewExample.DataBind();
MyCommand.Dispose();
MyConnection.Dispose();
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>SqlParameter Example: How to use SqlParameter in ASP.Net C#</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridViewExample" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
The SqlParameter type provides an easy and fast way to parameterize queries, allowing for bullet-proof and simple data access code. Here we examine the SqlParameter in an example written in the C# programming language, and then discuss some relevant points to parameterized queries and finally mention SQL injection issues.
Public method :: SqlParameter() -> Initializes a new instance of the SqlParameter class.
Public method :: SqlParameter(String, SqlDbType) -> Initializes a new instance of the SqlParameter class that uses the parameter name and the data type.
Public method :: SqlParameter(String, Object) -> Initializes a new instance of the SqlParameter class that uses the parameter name and a value of the new SqlParameter.
Public method :: SqlParameter(String, SqlDbType, Int32) -> Initializes a new instance of the SqlParameter class that uses the parameter name, the SqlDbType, and the size.
Public method :: SqlParameter(String, SqlDbType, Int32, String) -> Initializes a new instance of the SqlParameter class that uses the parameter name, the SqlDbType, the size, and the source column name.
Public method :: SqlParameter(String, SqlDbType, Int32, ParameterDirection, Boolean, Byte, Byte, String, DataRowVersion, Object) -> Initializes a new instance of the SqlParameter class that uses the parameter name, the type of the parameter, the size of the parameter, a ParameterDirection, the precision of the parameter, the scale of the parameter, the source column, a DataRowVersion to use, and the value of the parameter.
Public method :: SqlParameter(String, SqlDbType, Int32, ParameterDirection, Byte, Byte, String, DataRowVersion, Boolean, Object, String, String, String) -> Initializes a new instance of the SqlParameter class that uses the parameter name, the type of the parameter, the length of the parameter the direction, the precision, the scale, the name of the source column, one of the DataRowVersion values, a Boolean for source column mapping, the value of the SqlParameter, the name of the database where the schema collection for this XML instance is located, the owning relational schema where the schema collection for this XML instance is located, and the name of the schema collection for this parameter.
Public property :: CompareInfo -> Gets or sets the CompareInfo object that defines how string comparisons should be performed for this parameter.
Public property :: DbType -> Gets or sets the SqlDbType of the parameter.
Public property :: Direction -> Gets or sets a value that indicates whether the parameter is input -> only, output -> only, bidirectional, or a stored procedure return value parameter. (Overrides DbParameter.Direction.)
Public property :: IsNullable -> Gets or sets a value that indicates whether the parameter accepts null values. (Overrides DbParameter.IsNullable.)
Public property :: LocaleId -> Gets or sets the locale identifier that determines conventions and language for a particular region.
Public property :: Offset -> Gets or sets the offset to the Value property.
Public property :: ParameterName -> Gets or sets the name of the SqlParameter.
Public property :: Precision -> Gets or sets the maximum number of digits used to represent the Value property.
Public property :: Scale -> Gets or sets the number of decimal places to which Value is resolved.
Public property :: Size -> Gets or sets the maximum size, in bytes, of the data within the column.
Public property :: SourceColumn -> Gets or sets the name of the source column mapped to the DataSet and used for loading or returning the Value (Overrides DbParameter.SourceColumn.)
Public property :: SourceColumnNullMapping -> Sets or gets a value which indicates whether the source column is nullable. This allows SqlCommandBuilder to correctly generate Update statements for nullable columns. (Overrides DbParameter.SourceColumnNullMapping.)
Public property :: SourceVersion -> Gets or sets the DataRowVersion to use when you load Value (Overrides DbParameter.SourceVersion.)
Public property :: SqlDbType -> Gets or sets the SqlDbType of the parameter.
Public property :: SqlValue -> Gets or sets the value of the parameter as an SQL type.
Public property :: TypeName -> Gets or sets the type name for a table -> valued parameter.
Public property :: UdtTypeName -> Gets or sets a string that represents a user -> defined type as a parameter.
Public property :: Value -> Gets or sets the value of the parameter.
Public property :: XmlSchemaCollectionDatabase -> Gets the name of the database where the schema collection for this XML instance is located.
Public property :: XmlSchemaCollectionName -> Gets the name of the schema collection for this XML instance.
Public property :: XmlSchemaCollectionOwningSchema -> The owning relational schema where the schema collection for this XML instance is located.
Create SqlParameter object and insert there value with applicable properties define the parameter in the SqlCommand command string, and assign the SqlParameter object to the SqlCommand object. When the SqlCommand executes, parameters will be replaced with values specified by the SqlParameter object.
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Configuration" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
protected void Page_Load(object sender, System.EventArgs e) {
if (!Page.IsPostBack) {
SqlConnection MyConnection;
SqlCommand MyCommand;
SqlDataReader MyReader;
SqlParameter ITEMParam;
MyConnection = new SqlConnection();
MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings["AppConnectionString1"].ConnectionString;
MyCommand = new SqlCommand();
MyCommand.CommandText = "SELECT * FROM ITEM WHERE ITEMNAME = @ITEMNAME";
MyCommand.CommandType = CommandType.Text;
MyCommand.Connection = MyConnection;
ITEMParam = new SqlParameter();
ITEMParam.ParameterName = "@ITEMNAME";
ITEMParam.SqlDbType = SqlDbType.VarChar;
ITEMParam.Size = 25;
ITEMParam.Direction = ParameterDirection.Input;
ITEMParam.Value = "COMPUTER";
MyCommand.Parameters.Add(ITEMParam);
MyCommand.Connection.Open();
MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);
GridViewExample.DataSource = MyReader;
GridViewExample.DataBind();
MyCommand.Dispose();
MyConnection.Dispose();
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>SqlParameter Example: How to use SqlParameter in ASP.Net C#</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridViewExample" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
The SqlParameter type provides an easy and fast way to parameterize queries, allowing for bullet-proof and simple data access code. Here we examine the SqlParameter in an example written in the C# programming language, and then discuss some relevant points to parameterized queries and finally mention SQL injection issues.
Public method :: SqlParameter() -> Initializes a new instance of the SqlParameter class.
Public method :: SqlParameter(String, SqlDbType) -> Initializes a new instance of the SqlParameter class that uses the parameter name and the data type.
Public method :: SqlParameter(String, Object) -> Initializes a new instance of the SqlParameter class that uses the parameter name and a value of the new SqlParameter.
Public method :: SqlParameter(String, SqlDbType, Int32) -> Initializes a new instance of the SqlParameter class that uses the parameter name, the SqlDbType, and the size.
Public method :: SqlParameter(String, SqlDbType, Int32, String) -> Initializes a new instance of the SqlParameter class that uses the parameter name, the SqlDbType, the size, and the source column name.
Public method :: SqlParameter(String, SqlDbType, Int32, ParameterDirection, Boolean, Byte, Byte, String, DataRowVersion, Object) -> Initializes a new instance of the SqlParameter class that uses the parameter name, the type of the parameter, the size of the parameter, a ParameterDirection, the precision of the parameter, the scale of the parameter, the source column, a DataRowVersion to use, and the value of the parameter.
Public method :: SqlParameter(String, SqlDbType, Int32, ParameterDirection, Byte, Byte, String, DataRowVersion, Boolean, Object, String, String, String) -> Initializes a new instance of the SqlParameter class that uses the parameter name, the type of the parameter, the length of the parameter the direction, the precision, the scale, the name of the source column, one of the DataRowVersion values, a Boolean for source column mapping, the value of the SqlParameter, the name of the database where the schema collection for this XML instance is located, the owning relational schema where the schema collection for this XML instance is located, and the name of the schema collection for this parameter.
Public property :: CompareInfo -> Gets or sets the CompareInfo object that defines how string comparisons should be performed for this parameter.
Public property :: DbType -> Gets or sets the SqlDbType of the parameter.
Public property :: Direction -> Gets or sets a value that indicates whether the parameter is input -> only, output -> only, bidirectional, or a stored procedure return value parameter. (Overrides DbParameter.Direction.)
Public property :: IsNullable -> Gets or sets a value that indicates whether the parameter accepts null values. (Overrides DbParameter.IsNullable.)
Public property :: LocaleId -> Gets or sets the locale identifier that determines conventions and language for a particular region.
Public property :: Offset -> Gets or sets the offset to the Value property.
Public property :: ParameterName -> Gets or sets the name of the SqlParameter.
Public property :: Precision -> Gets or sets the maximum number of digits used to represent the Value property.
Public property :: Scale -> Gets or sets the number of decimal places to which Value is resolved.
Public property :: Size -> Gets or sets the maximum size, in bytes, of the data within the column.
Public property :: SourceColumn -> Gets or sets the name of the source column mapped to the DataSet and used for loading or returning the Value (Overrides DbParameter.SourceColumn.)
Public property :: SourceColumnNullMapping -> Sets or gets a value which indicates whether the source column is nullable. This allows SqlCommandBuilder to correctly generate Update statements for nullable columns. (Overrides DbParameter.SourceColumnNullMapping.)
Public property :: SourceVersion -> Gets or sets the DataRowVersion to use when you load Value (Overrides DbParameter.SourceVersion.)
Public property :: SqlDbType -> Gets or sets the SqlDbType of the parameter.
Public property :: SqlValue -> Gets or sets the value of the parameter as an SQL type.
Public property :: TypeName -> Gets or sets the type name for a table -> valued parameter.
Public property :: UdtTypeName -> Gets or sets a string that represents a user -> defined type as a parameter.
Public property :: Value -> Gets or sets the value of the parameter.
Public property :: XmlSchemaCollectionDatabase -> Gets the name of the database where the schema collection for this XML instance is located.
Public property :: XmlSchemaCollectionName -> Gets the name of the schema collection for this XML instance.
Public property :: XmlSchemaCollectionOwningSchema -> The owning relational schema where the schema collection for this XML instance is located.
