VB.NET SqlDataAdapter.SelectCommand属性代码示例

Public Function CreateCustomerAdapter( _
  ByVal connection As SqlConnection) As SqlDataAdapter

    Dim adapter As SqlDataAdapter = New SqlDataAdapter()

    ' Create the SelectCommand.
    Dim command As SqlCommand = New SqlCommand( _
        "SELECT * FROM Customers " & _
        "WHERE Country = @Country AND City = @City", connection)

    ' Add the parameters for the SelectCommand.
    command.Parameters.Add("@Country", SqlDbType.NVarChar, 15)
    command.Parameters.Add("@City", SqlDbType.NVarChar, 15)

    adapter.SelectCommand = command

    ' Create the InsertCommand.
    command = New SqlCommand( _
        "INSERT INTO Customers (CustomerID, CompanyName) " & _
        "VALUES (@CustomerID, @CompanyName)", connection)

    ' Add the parameters for the InsertCommand.
    command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")
    command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName")

    adapter.InsertCommand = command

    ' Create the UpdateCommand.
    command = New SqlCommand( _
        "UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " & _
        "WHERE CustomerID = @oldCustomerID", connection)

    ' Add the parameters for the UpdateCommand.
    command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")
    command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName")
    Dim parameter As SqlParameter = command.Parameters.Add( _
        "@oldCustomerID", SqlDbType.NChar, 5, "CustomerID")
    parameter.SourceVersion = DataRowVersion.Original

    adapter.UpdateCommand = command

    ' Create the DeleteCommand.
    command = New SqlCommand( _
        "DELETE FROM Customers WHERE CustomerID = @CustomerID", connection)

    ' Add the parameters for the DeleteCommand.
    command.Parameters.Add( _
        "@CustomerID", SqlDbType.NChar, 5, "CustomerID")
    parameter.SourceVersion = DataRowVersion.Original

    adapter.DeleteCommand = command

    Return adapter
End Function

' 导入命名空间
Imports System
 Imports System.Drawing
 Imports System.Collections
 Imports System.ComponentModel
 Imports System.Windows.Forms
 Imports System.Data
 Imports System.Data.SqlClient

 
Public Class MainClass

    Shared Sub Main(  )
        Application.Run(New ADOForm1() )
    End Sub

   
End Class

  Public Class ADOForm1
     Inherits System.Windows.Forms.Form
     Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid

     Private components As System.ComponentModel.Container

     ' private System.Data.ADO.ADOConnection myConnection;
     Private myConnection As System.Data.SqlClient.SqlConnection
     Private myDataSet As System.Data.DataSet
     Private myCommand As System.Data.SqlClient.SqlCommand
     Private myCommand2 As System.Data.SqlClient.SqlCommand
     Private myDataAdapter As System.Data.SqlClient.SqlDataAdapter
     Private myDataAdapter2 As System.Data.SqlClient.SqlDataAdapter

     Public Sub New(  )
         InitializeComponent(  )

         ' create the connection object and open it
         Dim connectionString As String = _
             "server=localhost; uid=sa; " & _
             "pwd=YourPassword; database=northwind"

         myConnection = _
         New System.Data.SqlClient.SqlConnection(connectionString)
         myConnection.Open(  )

         ' create the DataSet and set a property
         myDataSet = New System.Data.DataSet(  )
         myDataSet.CaseSensitive = True

         ' create the SqlCommand  object and assign the
         ' connection and the select statement
         myCommand = New System.Data.SqlClient.SqlCommand(  )
         myCommand.Connection = myConnection
         myCommand.CommandText = "Select * from Employees"

         myCommand2 = New System.Data.SqlClient.SqlCommand(  )
         myCommand2.Connection = myConnection
         myCommand2.CommandText = "Select * from Orders"

         ' create the myDataAdapter object and pass in the
         ' SQL Command object and establish the table mappings
         myDataAdapter = New System.Data.SqlClient.SqlDataAdapter(  )
         myDataAdapter2 = New System.Data.SqlClient.SqlDataAdapter(  )
         myDataAdapter.SelectCommand = myCommand
         myDataAdapter2.SelectCommand = myCommand2
         myDataAdapter.TableMappings.Add("Table", "Employees")
         myDataAdapter2.TableMappings.Add("Table", "Orders")

         ' Tell the myDataAdapter object to fill the DataSet
         myDataAdapter.Fill(myDataSet)
         myDataAdapter2.Fill(myDataSet)

         Dim myDataRelation As System.Data.DataRelation
         Dim dataColumn1 As System.Data.DataColumn
         Dim dataColumn2 As System.Data.DataColumn
         dataColumn1 = _
             myDataSet.Tables("Employees").Columns("EmployeeID")
         dataColumn2 = _
             myDataSet.Tables("Orders").Columns("EmployeeID")

         myDataRelation = New System.Data.DataRelation( _
             "EmployeesToOrders", dataColumn1, dataColumn2)

         myDataSet.Relations.Add(myDataRelation)

         Dim dataSetView As DataViewManager = _
             myDataSet.DefaultViewManager

         ' display it in the grid
         DataGrid1.DataSource = _
             dataSetView
         DataGrid1.DataMember = "Employees"

     End Sub 'New

     Private Sub InitializeComponent(  )
         Me.components = New System.ComponentModel.Container(  )
         Me.dataGrid1 = New System.Windows.Forms.DataGrid(  )
         dataGrid1.Location = New System.Drawing.Point(48, 24)
         dataGrid1.Size = New System.Drawing.Size(368, 160)
         dataGrid1.TabIndex = 0
         Me.Text = "ADOFrm1"
         Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
         Me.ClientSize = New System.Drawing.Size(464, 273)
         Me.Controls.Add(dataGrid1)
     End Sub 

 End Class