(C#) ASP.NET System.Data.SqlClient - Parameter Query (SqlParameter) |
(C#) ASP.NET System.Data.SqlClient - Parameter Query() การเขียน ASP.NET ใช้งาน NameSpace System.Data.SqlClient กับ Parameter Query() - SqlParameter() เพื่อกำหนดคุณสมบัติของพารามิเตอร์ที่จะทำการโยนค่าให้กับ Statement
Instance NameSpace
C#Using System.Data;
Using System.Data.SqlClient;
Language Code : VB.NET ||  C#
SqlParameter Syntax
String QueryString = "SELECT * FROM Table1 WHERE Field1 LIKE @p1";
SqlCommand Cmd = new SqlCommand(QueryString, Connection);
Cmd.Parameters.Add("@p1", SqlDbType.VarChar, 3).Value = "a";
ParameterQuery.aspx
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.SqlClient"%>
<%@ Page Language="C#" Debug="true" %>
<script runat="server">
void Page_Load(object sender, EventArgs e)
{
Sample1();
}
void Sample1()
{
System.Data.SqlClient.SqlConnection objConn;
System.Data.SqlClient.SqlCommand objCmd;
String strConnString,strSQL;
strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase;Max Pool Size=400;Connect Timeout=600;";
objConn = new System.Data.SqlClient.SqlConnection(strConnString);
objConn.Open();
//*** FOR INSERT ***//
strSQL = "INSERT INTO customer (CustomerID,Name,Email,CountryCode,Budget,Used) " +
"VALUES (@sCustomerID,@sName,@sEmail,@sCountryCode,@sBudget,@sUsed)";
objCmd = new System.Data.SqlClient.SqlCommand(strSQL,objConn);
//*** Sample 1 ***//
/*
objCmd.Parameters.AddWithValue("@sCustomerID","C005");
objCmd.Parameters.AddWithValue("@sName","Weerachai Nukitram");
objCmd.Parameters.AddWithValue("@sEmail","[email protected]");
objCmd.Parameters.AddWithValue("@sCountryCode","TH");
objCmd.Parameters.AddWithValue("@sBudget","2000000");
objCmd.Parameters.AddWithValue("@sUsed","1000000");
*/
//*** Sample 2 ***//
objCmd.Parameters.Add(new SqlParameter("@sCustomerID","C005"));
objCmd.Parameters.Add(new SqlParameter("@sName","Weerachai Nukitram"));
objCmd.Parameters.Add(new SqlParameter("@sEmail","[email protected]"));
objCmd.Parameters.Add(new SqlParameter("@sCountryCode","TH"));
objCmd.Parameters.Add(new SqlParameter("@sBudget","2000000"));
objCmd.Parameters.Add(new SqlParameter("@sUsed","1000000"));
objCmd.ExecuteNonQuery();
lblText.Text = lblText.Text + "- Record Inserted";
//*** FOR UPDATE ***'
/*
strSQL = "UPDATE customer SET Budget = @sBudget " +
" WHERE CustomerID = @sCustomerID ";
objCmd = new System.Data.SqlClient.SqlCommand(strSQL,objConn);
objCmd.Parameters.Add(new SqlParameter("@sBudget","4000000"));
objCmd.Parameters.Add(new SqlParameter("@sCustomerID","C005"));
objCmd.ExecuteNonQuery();
*/
//*** FOR DELETE ***'
/*
strSQL = "DELETE FROM customer " +
" WHERE CustomerID = @sCustomerID ";
objCmd = new System.Data.SqlClient.SqlCommand(strSQL,objConn);
objCmd.Parameters.Add(new SqlParameter("@sCustomerID","C005"));
objCmd.ExecuteNonQuery();
*/
objCmd = null;
objConn.Close();
objConn = null;
}
</script>
<html>
<head>
<title>ThaiCreate.Com ASP.NET - System.Data.SqlClient</title>
</head>
<body>
<form id="form1" runat="server">
<asp:Label id="lblText" runat="Server"></asp:Label>
</form>
</body>
</html>
Screenshot
SqlDbType
SqlDbType.BigInt
SqlDbType.Binary
SqlDbType.Bit
SqlDbType.Char
SqlDbType.DateTime
SqlDbType.Decimal
SqlDbType.Float
SqlDbType.Image
SqlDbType.Int
SqlDbType.Money
SqlDbType.NChar
SqlDbType.NText
SqlDbType.NVarChar
SqlDbType.Real
SqlDbType.UniqueIdentifier
SqlDbType.SmallDateTime
SqlDbType.SmallInt
SqlDbType.SmallMoney
SqlDbType.Text
SqlDbType.Timestamp
SqlDbType.TinyInt
SqlDbType.VarBinary
SqlDbType.VarChar
SqlDbType.Variant
SqlDbType.Xml
SqlDbType.Udt
SqlDbType.Structured
SqlDbType.Date
SqlDbType.Time
SqlDbType.DateTime2
SqlDbType.DateTimeOffset
ASP.NET - Transaction
ตัวอย่างการเขียน .NET ร่วมกับ Parameter
|
ช่วยกันสนับสนุนรักษาเว็บไซต์ความรู้แห่งนี้ไว้ด้วยการสนับสนุน 
Load balance : Server 05
