日期:2011-08-05  浏览次数:20871 次

asp.net 中使用sqlcommandbuilder,用它来产生增加,删除,选择等SQL语句,也比较方便的,下面给出个例子

   Private Sub UseCommandBuilder()
  Dim connectionString As String = _
  "Integrated Security=SSPI;Persist Security Info=False;" + _
  "Initial Catalog=Northwind;Data Source=LAP800"
  Dim connection As SqlConnection = _
  New SqlConnection(connectionString)
  connection.Open()
  Try
  Dim command As SqlCommand = _
  New SqlCommand("SELECT * FROM Customers", connection)

  Dim adapter As SqlDataAdapter = _
  New SqlDataAdapter(command)
  Dim builder As SqlCommandBuilder = _
  New SqlCommandBuilder(adapter)
  ' Show commands
  Console.WriteLine(builder.GetInsertCommand().CommandText)
  Console.WriteLine(builder.GetUpdateCommand().CommandText)
  Console.WriteLine(builder.GetDeleteCommand().CommandText)

  Dim table As DataTable = New DataTable
  adapter.Fill(table)

  'Use Insert
  Dim row As DataRow  = table.NewRow
  row("CustomerID")   = "PAULK"
  row("CompanyName")  = "Pauly's Edibles"
  row("ContactName")  = "Paul Kimmel"
  row("ContactTitle") = "The Fat Man"
  table.Rows.Add(row)

  'Use Update
  Dim results() As DataRow = _
  table.Select("CustomerID = 'ALFKI'")
  results(0)("Region") = "DE"

  'Use Delete
  Dim deleteThese() As DataRow = _
  table.Select("CustomerID = 'FARLA'")
  For Each row In deleteThese
  row.Delete()
  Next

  adapter.Update(table)
  table.AcceptChanges()

  Catch ex As Exception
  Debug.WriteLine(ex.Message)
  Throw
  Finally
  connection.Close()
  End Try
  End Sub

  End Module