VB.Net/Database ADO.net/SqlCommand Parameter
Содержание
Pass Parameter into SQL command
<source lang="vbnet"> Imports System Imports System.Collections Imports System.Data Imports System.IO Imports System.Xml.Serialization Imports System.Windows.Forms Imports System.Data.SqlClient
Public Class MainClass
Shared Dim WithEvents con As SqlConnection
Shared Sub Main()
con = New SqlConnection("Server=(local)\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=SSPI")
Dim cmd As New SqlCommand("SELECT FirstName, LastName FROM Employee WHERE FirstName = @fn", con)
cmd.Parameters.Add(New SqlParameter("@fn", SqlDbType.VarChar, 10)).Value = "Joe"
Try
con.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()
While reader.Read()
Console.WriteLine("{0} - {1}", reader.GetString(0), reader.GetString(1))
End While
reader.Close()
Finally
con.Close()
End Try
End Sub
End Class
</source>
Pass two parameters
<source lang="vbnet"> Imports System Imports System.Collections Imports System.Data Imports System.IO Imports System.Xml.Serialization Imports System.Xml Imports System.Windows.Forms Imports System.Data.SqlClient
Public Class MainClass
Shared Dim WithEvents con As SqlConnection
Shared Sub Main()
con = New SqlConnection("Server=(local)\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=SSPI")
Dim cmd As New SqlCommand( _
"SELECT FirstName FROM Employee WHERE FirstName = @fn;" & _
"SELECT @count = COUNT(*) FROM Employee WHERE FirstName = @fn", con)
cmd.Parameters.Add( _
New SqlParameter("@fn", SqlDbType.VarChar, 50)).Value = "Joe"
cmd.Parameters.Add(New SqlParameter("@count", SqlDbType.Int))
cmd.Parameters("@count").Direction = ParameterDirection.Output
Try
con.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()
While reader.Read()
Console.WriteLine("{0} - {1}", reader.GetString(0), reader.GetString(1))
End While
reader.Close()
Finally
con.Close()
End Try
Console.WriteLine("{0} - {1}", _
"Count", cmd.Parameters("@count").Value.ToString())
End Sub
End Class
</source>
Simple Database Command Tester
<source lang="vbnet"> Imports System.Data.SqlClient Imports System.Windows.Forms <Global.Microsoft.VisualBasic.rupilerServices.DesignerGenerated()> _ Partial Class TestForm
Inherits System.Windows.Forms.Form
<System.Diagnostics.DebuggerStepThrough()> _
Private Sub InitializeComponent()
Me.cmdParameterized = New System.Windows.Forms.Button
Me.cmdInsert = New System.Windows.Forms.Button
Me.UpdateDB = New System.Windows.Forms.Button
Me.SuspendLayout()
"
Me.cmdParameterized.Location = New System.Drawing.Point(12, 124)
Me.cmdParameterized.Name = "cmdParameterized"
Me.cmdParameterized.Size = New System.Drawing.Size(161, 42)
Me.cmdParameterized.TabIndex = 5
Me.cmdParameterized.Text = "Perform Insert with Parameterized Command"
"
Me.cmdInsert.Location = New System.Drawing.Point(12, 75)
Me.cmdInsert.Name = "cmdInsert"
Me.cmdInsert.Size = New System.Drawing.Size(161, 43)
Me.cmdInsert.TabIndex = 4
Me.cmdInsert.Text = "Perform Insert with Stored Procedure"
"
Me.UpdateDB.Location = New System.Drawing.Point(12, 27)
Me.UpdateDB.Size = New System.Drawing.Size(161, 42)
Me.UpdateDB.TabIndex = 3
Me.UpdateDB.Text = "Perform Direct Update"
"
"TestForm
"
Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
Me.ClientSize = New System.Drawing.Size(292, 266)
Me.Controls.Add(Me.cmdParameterized)
Me.Controls.Add(Me.cmdInsert)
Me.Controls.Add(Me.UpdateDB)
Me.Font = New System.Drawing.Font("Tahoma", 8.25!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.ResumeLayout(False)
End Sub
Friend WithEvents cmdParameterized As System.Windows.Forms.Button
Friend WithEvents cmdInsert As System.Windows.Forms.Button
Friend WithEvents UpdateDB As System.Windows.Forms.Button
Private Sub Update_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateDB.Click
Dim Connect As String = "Settings.Northwind"
Dim con As New SqlConnection(Connect)
con.Open()
Dim SQL As String = "UPDATE Orders SET ShipCountry="O" WHERE OrderID="11""
Dim cmd As New SqlCommand(SQL, con)
Dim NumAffected As Integer
NumAffected = cmd.ExecuteNonQuery()
con.Close()
MessageBox.Show(NumAffected.ToString & " records updated", "Results", MessageBoxButtons.OK)
End Sub
Private Sub cmdInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdInsert.Click
Dim Connect As String = "My.Settings.Northwind"
Dim con As New SqlConnection(Connect)
con.Open()
Dim cmd As New SqlCommand("AddNewCustomer", con)
cmd.rumandType = CommandType.StoredProcedure
Dim param As SqlParameter
param = cmd.Parameters.AddWithValue("@CustomerID", "111")
param = cmd.Parameters.AddWithValue("@CompanyName", "A")
param = cmd.Parameters.AddWithValue("@ContactName", "M")
Dim NumAffected As Integer
NumAffected = cmd.ExecuteNonQuery()
con.Close()
MessageBox.Show(NumAffected.ToString & " records inserted", "Results", MessageBoxButtons.OK)
End Sub
Private Sub cmdParameterized_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdParameterized.Click
Dim Connect As String = "My.Settings.Northwind"
Dim con As New SqlConnection(Connect)
con.Open()
Dim SQL As String = "UPDATE Orders SET ShipCountry=@ShipCountry WHERE OrderID=@OrderID"
Dim cmd As New SqlCommand(SQL, con)
cmd.Parameters.AddWithValue("@ShipCountry", "O")
cmd.Parameters.AddWithValue("@OrderID", 1)
Dim NumAffected As Integer
NumAffected = cmd.ExecuteNonQuery()
con.Close()
MessageBox.Show(NumAffected.ToString & " records inserted", "Results", MessageBoxButtons.OK)
End Sub
End Class
</source>
Use Parameters in your sql command
<source lang="vbnet"> Imports System Imports System.Data Imports System.Data.SqlClient public class MainClass
Shared Sub Main()
Dim thisConnection As New SqlConnection("server=(local)\SQLEXPRESS;" & _
"integrated security=sspi;database=MyDatabase")
"Create Command object
Dim nonqueryCommand As SqlCommand = thisConnection.CreateCommand()
Try
" Open Connection
thisConnection.Open()
Console.WriteLine("Connection Opened")
" Create INSERT statement with named parameters
nonqueryCommand.rumandText = _
"INSERT INTO Employee VALUES (@MyID, @MyFirstName, @MyLastName?"
" Add Parameters to Command Parameters collection
nonqueryCommand.Parameters.Add("@MyFirstName", SqlDbType.VarChar, 30)
nonqueryCommand.Parameters.Add("@MyLastName", SqlDbType.VarChar, 30)
nonqueryCommand.Parameters.Add("@MyID", SqlDbType.Int)
" Prepare command for repeated execution
nonqueryCommand.Prepare()
" Data to be inserted
Dim names() As String = {"Z", "S", "J", "D"}
For i As Integer = 0 To 3
nonqueryCommand.Parameters("@MyFirstName").Value = names(i)
nonqueryCommand.Parameters("@MyLastName").Value = names(i)
nonqueryCommand.Parameters("@MyID").Value = i+10
Console.WriteLine("Executing {0}", _
nonqueryCommand.rumandText)
Console.WriteLine("Number of rows affected : {0}", _
nonqueryCommand.ExecuteNonQuery())
Next i
Catch ex As SqlException
" Display error
Console.WriteLine("Error: " & ex.ToString())
Finally
" Close Connection
thisConnection.Close()
Console.WriteLine("Connection Closed")
End Try
End Sub
End Class
</source>
