访问和操作SQL server数据库中的数据
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim cn As New SqlConnection("Data Source=(local);Initial Catalog =northwind;Integrated Security=true")
Dim da As New SqlDataAdapter
Dim comselect As New SqlCommand("select productid,productname from product where categoryid=1", cn)
Dim cominsert As New SqlCommand("instrt into products(productname) values(@productname)", cn)
cominsert.Parameters.Add(New SqlParameter("@productname", SqlDbType.NVarChar, 40, ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "productname", DataRowVersion.Current, Nothing))
Dim comupdate As New SqlCommand("update products set productname=@productname where productid=@productid", cn)
comupdate.Parameters.Add(New SqlParameter("@productname", SqlDbType.NVarChar, 40, ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "productname", DataRowVersion.Current, Nothing))
comupdate.Parameters.Add(New SqlParameter("@productid", SqlDbType.Int, 4, ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "productid", DataRowVersion.Current, Nothing)) Dim comdelete As New SqlCommand("delete from product where productid=@productid", cn)
comdelete.Parameters.Add(New SqlParameter("@productid", SqlDbType.Int, 4, ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "productid", DataRowVersion.Current, Nothing))
da.SelectCommand = comselect
da.InsertCommand = cominsert
da.UpdateCommand = comupdate
da.DeleteCommand = comdelete
Dim ds As New DataSet
da.Fill(ds, "products")
Dim i As Integer
For i = 0 To ds.Tables(0).Rows.Count - 1
Response.Write(ds.Tables(0).Rows(i).Item(1) & "br")
Next i
Dim row As DataRow = ds.Tables(0).NewRow()
row.Item("productname") = "go juice"
ds.Tables(0).Rows.Add(row)
ds.Tables(0).Rows(0).Item("productname") = "good tea"
Dim count As Integer = da.Update(ds, "products")
ds.AcceptChanges()
Response.Write("p" & count & "records affected..../p")
For i = 0 To ds.Tables(0).Rows.Count - 1
Response.Write(ds.Tables(0).Rows(i).Item(1) & "br")
Next i
End Sub
Dim cn As New SqlConnection("Data Source=(local);Initial Catalog =northwind;Integrated Security=true")
Dim da As New SqlDataAdapter
Dim comselect As New SqlCommand("select productid,productname from product where categoryid=1", cn)
Dim cominsert As New SqlCommand("instrt into products(productname) values(@productname)", cn)
cominsert.Parameters.Add(New SqlParameter("@productname", SqlDbType.NVarChar, 40, ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "productname", DataRowVersion.Current, Nothing))
Dim comupdate As New SqlCommand("update products set productname=@productname where productid=@productid", cn)
comupdate.Parameters.Add(New SqlParameter("@productname", SqlDbType.NVarChar, 40, ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "productname", DataRowVersion.Current, Nothing))
comupdate.Parameters.Add(New SqlParameter("@productid", SqlDbType.Int, 4, ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "productid", DataRowVersion.Current, Nothing)) Dim comdelete As New SqlCommand("delete from product where productid=@productid", cn)
comdelete.Parameters.Add(New SqlParameter("@productid", SqlDbType.Int, 4, ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "productid", DataRowVersion.Current, Nothing))
da.SelectCommand = comselect
da.InsertCommand = cominsert
da.UpdateCommand = comupdate
da.DeleteCommand = comdelete
Dim ds As New DataSet
da.Fill(ds, "products")
Dim i As Integer
For i = 0 To ds.Tables(0).Rows.Count - 1
Response.Write(ds.Tables(0).Rows(i).Item(1) & "br")
Next i
Dim row As DataRow = ds.Tables(0).NewRow()
row.Item("productname") = "go juice"
ds.Tables(0).Rows.Add(row)
ds.Tables(0).Rows(0).Item("productname") = "good tea"
Dim count As Integer = da.Update(ds, "products")
ds.AcceptChanges()
Response.Write("p" & count & "records affected..../p")
For i = 0 To ds.Tables(0).Rows.Count - 1
Response.Write(ds.Tables(0).Rows(i).Item(1) & "br")
Next i
End Sub
