.NET Parameterized Queries |
|
|
|
.NET Parameterized Queries (Parameter Query) รูปแบบการเขียนเพื่อโยนค่า Parameter ให้กับ SQL Statement เหตุผลที่ใช้พารามิเตอร์ ทั้งนี้ก็เพื่อต้องการที่จะทำการกำหนดคุณสมบัติให้กับค่าตัวแปร ก่อนที่จะมีการ Input ลงใน Statement และทั้งยังสามารถลดปัญหาการแบ่งแยกระหว่างค่า Argument ของแต่ล่ะ Statement เช่น Values ('1','I don't no')
Parameter Syntax
Dim QueryString As String = "SELECT * FROM Table1 WHERE Field1 LIKE @p1"
Dim Cmd As OleDbCommand = New OleDbCommand(QueryString, Connection)
Cmd.Parameters.Add("@p1", OleDbType.Char, 3).Value = "a"
สำหรับในตัวอย่างผมได้ออกแบบไว้สำหรับ NameSpace 5 ตัวครับ
1.System.Data.OleDb - Parameter Query(OleDbParameter)
strSQL = "INSERT INTO customer (CustomerID,Name,Email,CountryCode,Budget,Used) " & _
"VALUES (@sCustomerID,@sName,@sEmail,@sCountryCode,@sBudget,@sUsed)"
objCmd = New System.Data.OleDb.OleDbCommand(strSQL,objConn)
With objCmd
.Parameters.Add(New OleDbParameter("@sCustomerID","C005"))
.Parameters.Add(New OleDbParameter("@sName","Weerachai Nukitram"))
.Parameters.Add(New OleDbParameter("@sEmail","[email protected]"))
.Parameters.Add(New OleDbParameter("@sCountryCode","TH"))
.Parameters.Add(New OleDbParameter("@sBudget","2000000"))
.Parameters.Add(New OleDbParameter("@sUsed","1000000"))
End With
2.MySql.Data.MySQLClient - Parameter Query (MySQLParameter)
strSQL = "INSERT INTO customer (CustomerID,Name,Email,CountryCode,Budget,Used) " & _
"VALUES (?sCustomerID,?sName,?sEmail,?sCountryCode,?sBudget,?sUsed)"
objCmd = New MySql.Data.MySQLClient.MySQLCommand(strSQL,objConn)
With objCmd
.Parameters.Add(new MySQLParameter("?sCustomerID", "C005"))
.Parameters.Add(new MySQLParameter("?sName", "Weerachai Nukitram"))
.Parameters.Add(new MySQLParameter("?sEmail", "[email protected]"))
.Parameters.Add(new MySQLParameter("?sCountryCode", "TH"))
.Parameters.Add(new MySQLParameter("?sBudget", "2000000"))
.Parameters.Add(new MySQLParameter("?sUsed", "100000"))
End With
3.System.Data.SqlClient - Parameter Query (SqlParameter)
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)
With objCmd
.Parameters.Add(New SqlParameter("@sCustomerID","C005"))
.Parameters.Add(New SqlParameter("@sName","Weerachai Nukitram"))
.Parameters.Add(New SqlParameter("@sEmail","[email protected]"))
.Parameters.Add(New SqlParameter("@sCountryCode","TH"))
.Parameters.Add(New SqlParameter("@sBudget","2000000"))
.Parameters.Add(New SqlParameter("@sUsed","1000000"))
End With
4.System.Data.OracleClient - Parameter Query (OracleParameter)
strSQL = "INSERT INTO customer (CustomerID,Name,Email,CountryCode,Budget,Used) " & _
"VALUES (:sCustomerID,:sName,:sEmail,:sCountryCode,:sBudget,:sUsed)"
objCmd = New System.Data.OracleClient.OracleCommand(strSQL,objConn)
With objCmd
.Parameters.Add(new OracleParameter(":sCustomerID", "C005"))
.Parameters.Add(new OracleParameter(":sName", "Weerachai Nukitram"))
.Parameters.Add(new OracleParameter(":sEmail", "[email protected]"))
.Parameters.Add(new OracleParameter(":sCountryCode", "TH"))
.Parameters.Add(new OracleParameter(":sBudget", "2000000"))
.Parameters.Add(new OracleParameter(":sUsed", "100000"))
End With
5.System.Data.Odbc - Parameter Query (OdbcParameter)
strSQL = "INSERT INTO customer (CustomerID,Name,Email,CountryCode,Budget,Used) " & _
"VALUES (?,?,?,?,?,?)"
objCmd = New System.Data.Odbc.OdbcCommand(strSQL,objConn)
With objCmd
.Parameters.Add(New OdbcParameter("@sCustomerID","C005"))
.Parameters.Add(New OdbcParameter("@sName","Weerachai Nukitram"))
.Parameters.Add(New OdbcParameter("@sEmail","[email protected]"))
.Parameters.Add(New OdbcParameter("@sCountryCode","TH"))
.Parameters.Add(New OdbcParameter("@sBudget","2000000"))
.Parameters.Add(New OdbcParameter("@sUsed","1000000"))
End With
Transaction()
บทความอื่น ๆ เกี่ยวกับการ การใช้ Parameter Query
Go to : ASP.NET Access BLOB Binary Data and Parameterized Query
Go to : ASP.NET MySQL BLOB Binary Data and Parameterized Query
Go to : ASP.NET SQL Server BLOB Binary Data and Parameterized Query
Go to : ASP.NET Oracle BLOB Binary Data and Parameterized Query
|
|
|
|
 |
|
|
|
|
|
|
|
|
|
|
|
By : |
TC Admin
|
|
Score Rating : |
- |
|
Create Date : |
2009-01-15 00:05:27 |
|
Download : |
No files |
|
|
|
|
Related Links |
|
|
|
|
|
.NET Smart Device เขียนโปรแกรมบน Smartphone, Pocket PC , Windows CE , Window Mobile 5-6, Hand Held,...
.NET Smart Device Project เขียนโปรแกรมบน Smartphone, Pocket PC , Windows CE , Window Mobile 5-6, Hand Held,...
Rating :    
Update :
2017-03-24 21:17:48 View :
42,012 |
|
| |
|
|