ASP.NET System.Data.SqlClient - Parameter Query (SqlParameter) |
ASP.NET System.Data.SqlClient - Parameter Query() การเขียน ASP.NET ใช้งาน NameSpace System.Data.SqlClient กับ Parameter Query() - SqlParameter() เพื่อกำหนดคุณสมบัติของพารามิเตอร์ที่จะทำการโยนค่าให้กับ Statement
Instance NameSpace
VB.NETImports System.Data
Imports System.Data.SqlClient
Language Code :  VB.NET || C#
SqlParameter Syntax
Dim QueryString As String = "SELECT * FROM Table1 WHERE Field1 LIKE @p1"
Dim Cmd As SqlCommand = 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="VB" %>
<script runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Sample1()
End Sub
Sub Sample1()
Dim objConn As System.Data.SqlClient.SqlConnection
Dim objCmd As System.Data.SqlClient.SqlCommand
Dim strConnString,strSQL As String
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 ***'
'With objCmd
' .Parameters.AddWithValue("@sCustomerID","C005")
' .Parameters.AddWithValue("@sName","Weerachai Nukitram")
' .Parameters.AddWithValue("@sEmail","[email protected]")
' .Parameters.AddWithValue("@sCountryCode","TH")
' .Parameters.AddWithValue("@sBudget","2000000")
' .Parameters.AddWithValue("@sUsed","1000000")
'End With
'*** Sample 2 ***'
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
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)
'With objCmd
' .Parameters.Add(New SqlParameter("@sBudget","4000000"))
' .Parameters.Add(New SqlParameter("@sCustomerID","C005"))
'End With
'objCmd.ExecuteNonQuery()
'*** FOR DELETE ***'
'strSQL = "DELETE FROM customer " & _
'" WHERE CustomerID = @sCustomerID "
'objCmd = New System.Data.SqlClient.SqlCommand(strSQL,objConn)
'With objCmd
' .Parameters.Add(New SqlParameter("@sCustomerID","C005"))
'End With
'objCmd.ExecuteNonQuery()
objCmd = Nothing
objConn.Close()
objConn = Nothing
End Sub
</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 01
