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