ADO.NET中连接sql数据库的方法

Method1:
       Sub Page_Load(Sender As Object, E As EventArgs)

        Dim DS As DataSet
        Dim MyConnection As SqlConnection Dim MyCommand As SqlDataAdapter

        MyConnection = New SqlConnection("server=(local)\NetSDK;database=pubs;Trusted_Connection=yes")
        MyCommand = New SqlDataAdapter("select * from Authors", MyConnection)

        DS = new DataSet()
        MyCommand.Fill(ds, "作者")

        MyDataGrid.DataSource=ds.Tables("作者").DefaultView
        MyDataGrid.DataBind()
      End Sub
Method2:
     
      Sub Page_Load(Sender As Object, E As EventArgs)

        Dim MyConnection As SqlConnection = New SqlConnection("server=(local)\NetSDK;database=pubs;Trusted_Connection=yes")
        Dim MyCommand As SqlCommand = New SqlCommand("select * from Authors", MyConnection)

        MyConnection.Open()

        Dim dr As SqlDataReader = MyCommand.ExecuteReader()

        MyDataGrid.DataSource = dr
        MyDataGrid.DataBind()

        MyConnection.Close()
    End Sub
Method3://是Method1的扩展,加入了条件。使用 SqlDataAdapter 对象执行参数化选择。SqlDataAdapter 维护一个可用于用值替换变量标识符（由名称前的"@"表示）的 Parameters 集合。在该集合中添加一个指定参数的名称、类型和大小的新SqlParameter，然后将它的 Value 属性设置为选择的值。 
       
    Sub GetAuthors_Click(Sender As Object, E As EventArgs)

        Dim DS As DataSet
        Dim MyConnection As SqlConnection
        Dim MyCommand As SqlDataAdapter

        Dim SelectCommand As String = "select * from Authors where state = @State"

        MyConnection = New SqlConnection("server=(local)\NetSDK;database=pubs;Trusted_Connection=yes")
        MyCommand = New SqlDataAdapter(SelectCommand, MyConnection)

        MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@State", SqlDbType.NVarChar, 2))
        MyCommand.SelectCommand.Parameters("@State").Value = MySelect.Value

        DS = new DataSet()
        MyCommand.Fill(DS, "作者")

        MyDataGrid.DataSource=DS.Tables("作者").DefaultView
        MyDataGrid.DataBind()
    End Sub
其他数据库操作:
1)增加操作:
 Sub AddAuthor_Click(Sender As Object, E As EventArgs)

        Dim DS As DataSet
        Dim MyCommand As SqlCommand

        If au_id.Value = "" Or au_fname.Value = "" Or au_lname.Value = "" Or phone.Value = ""

          Message.InnerHtml = "错误：""作者 ID""、""姓名""或""电话""不允许使用空值"
          Message.Style("color") = "red"
          BindGrid()
        End If

        Dim InsertCmd As String = "insert into Authors (au_id, au_lname, au_fname, phone, address, city, state, zip, contract) values (@Id, @LName, @FName, @Phone, @Address, @City, @State, @Zip, @Contract)"

        MyCommand = New SqlCommand(InsertCmd, MyConnection)

        MyCommand.Parameters.Add(New SqlParameter("@Id", SqlDbType.NVarChar, 11))
        MyCommand.Parameters("@Id").Value = au_id.Value

        MyCommand.Parameters.Add(New SqlParameter("@LName", SqlDbType.NVarChar, 40))
        MyCommand.Parameters("@LName").Value = au_lname.Value

        MyCommand.Parameters.Add(New SqlParameter("@FName", SqlDbType.NVarChar, 20))
        MyCommand.Parameters("@FName").Value = au_fname.Value

        MyCommand.Parameters.Add(New SqlParameter("@Phone", SqlDbType.NChar, 12))
        MyCommand.Parameters("@Phone").Value = phone.Value

        MyCommand.Parameters.Add(New SqlParameter("@Address", SqlDbType.NVarChar, 40))
        MyCommand.Parameters("@Address").Value = address.Value

        MyCommand.Parameters.Add(New SqlParameter("@City", SqlDbType.NVarChar, 20))
        MyCommand.Parameters("@City").Value = city.Value

        MyCommand.Parameters.Add(New SqlParameter("@State", SqlDbType.NChar, 2))
        MyCommand.Parameters("@State").Value = stateabbr.Value

        MyCommand.Parameters.Add(New SqlParameter("@Zip", SqlDbType.NChar, 5))
        MyCommand.Parameters("@Zip").Value = zip.Value

        MyCommand.Parameters.Add(New SqlParameter("@Contract", SqlDbType.NVarChar,1))
        MyCommand.Parameters("@Contract").Value = contract.Value

        MyCommand.Connection.Open()

        Try
            MyCommand.ExecuteNonQuery()
            Message.InnerHtml = "<b>已添加记录</b><br>" & InsertCmd.ToString()

        Catch Exp As SQLException
            If Exp.Number = 2627
                Message.InnerHtml = "错误：已存在具有相同主键的记录"
            Else
                Message.InnerHtml = "错误：未能添加记录，请确保正确填写了字段"
            End If
            Message.Style("color") = "red"

        End Try

        MyCommand.Connection.Close()

        BindGrid()
    End Sub

    Sub BindGrid()

        Dim MyCommand As SqlDataAdapter = new SqlDataAdapter("select * from Authors", MyConnection)

        Dim DS As DataSet = new DataSet()
        MyCommand.Fill(DS, "Authors")

        MyDataGrid.DataSource=DS.Tables("Authors").DefaultView
        MyDataGrid.DataBind()
    End Sub

2)删除操作:
   Sub MyDataGrid_Delete(Sender As Object, E As DataGridCommandEventArgs)

        Dim MyCommand As SqlCommand
        Dim DeleteCmd As String = "DELETE from Employee where emp_id = @Id"

        MyCommand = New SqlCommand(DeleteCmd, MyConnection)
        MyCommand.Parameters.Add(New SqlParameter("@Id", SqlDbType.NVarChar, 11))
        MyCommand.Parameters("@Id").Value = MyDataGrid.DataKeys(CInt(E.Item.ItemIndex))

        MyCommand.Connection.Open()

        Try
            MyCommand.ExecuteNonQuery()
            Message.InnerHtml = "<b>已删除记录</b><br>" & DeleteCmd
        Catch Exc As SQLException
            Message.InnerHtml = "错误：未能删除记录"
            Message.Style("颜色") = "红色"
        End Try

        MyCommand.Connection.Close()

        BindGrid()
    End Sub
3)排序操作:

    Dim MyConnection As SqlConnection

    Sub Page_Load(Sender As Object, E As EventArgs)

        MyConnection = New SqlConnection("server=(local)\NetSDK;database=pubs;Trusted_Connection=yes")

        If Not (IsPostBack)
            BindGrid("au_id")
        End If
    End Sub

    Sub MyDataGrid_Sort(Sender As Object, E As DataGridSortCommandEventArgs)

        BindGrid(E.SortExpression)
    End Sub

    Sub BindGrid(SortField As String)

        Dim DS As DataSet
        Dim MyCommand As SqlDataAdapter
        MyCommand = New SqlDataAdapter("select * from Authors", MyConnection)

        DS = new DataSet()
        MyCommand.Fill(DS, "作者")

        Dim Source As DataView = DS.Tables("作者").DefaultView
        Source.Sort = SortField

        MyDataGrid.DataSource = Source
        MyDataGrid.DataBind()
    End Sub