VB.Net/Database ADO.net/DataSet Update
Содержание
Modify Data table: add a row and update a cell
<source lang="vbnet"> Imports System Imports System.Data Imports System.Data.SqlClient
public class MainClass
Shared Sub Main()
Dim thisConnection As New SqlConnection("server=(local)\SQLEXPRESS;" & _
"integrated security=sspi;database=MyDatabase")
" Sql Query
Dim sql As String = "SELECT * FROM Employee"
Try
" Create Data Adapter
Dim da As New SqlDataAdapter
da.SelectCommand = New SqlCommand(sql, thisConnection)
" Create and fill Dataset
Dim ds As New DataSet
da.Fill(ds, "Employee")
" Get the Data Table
Dim dt As DataTable = ds.Tables("Employee")
" Display Rows Before Changed
Console.WriteLine("Before altering the dataset")
For Each row As DataRow In dt.Rows
Console.WriteLine("{0} | {1} | {2}", _
row("ID").ToString().PadRight(10), _
row("FirstName").ToString().PadRight(10), _
row("LastName"))
Next
" FirstName column should be nullable
dt.Columns("FirstName").AllowDBNull = True
" Modify city in first row
dt.Rows(0)("FirstName") = "Birm"
" Add A Row
Dim newRow As DataRow = dt.NewRow()
newRow("ID") = "99"
newRow("FirstName") = "Ever"
newRow("LastName") = "Dame"
dt.Rows.Add(newRow)
" Display Rows After Alteration
Console.WriteLine("=========")
Console.WriteLine("After altering the dataset")
For Each row As DataRow In dt.Rows
Console.WriteLine("{0} | {1} | {2}", _
row("ID").ToString().PadRight(10), _
row("FirstName").ToString().PadRight(10), _
row("LastName"))
Next
Catch ex As SqlException
" Display error
Console.WriteLine("Error: " & ex.ToString())
Finally
" Close Connection
thisConnection.Close()
Console.WriteLine("Connection Closed")
End Try
End Sub
End Class
</source>
Set new cell value in DataSet
<source lang="vbnet"> Imports System Imports System.Collections Imports System.Data Imports System.IO Imports System.Xml.Serialization Imports System.Xml Imports System.Windows.Forms Imports System.Data.SqlClient Imports System.Data.OleDb Public Class MainClass
Shared Sub Main()
Dim form1 As Form = New Form1
Application.Run(form1)
End Sub
End Class
Public Class Form1
Inherits System.Windows.Forms.Form
- Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
"This call is required by the Windows Form Designer.
InitializeComponent()
"Add any initialization after the InitializeComponent() call
End Sub
"Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
"Required by the Windows Form Designer
Private components As System.ruponentModel.IContainer
"NOTE: The following procedure is required by the Windows Form Designer
"It can be modified using the Windows Form Designer.
"Do not modify it using the code editor.
Friend WithEvents button1 As System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.button1 = New System.Windows.Forms.Button()
Me.SuspendLayout()
"
"button1
"
Me.button1.Font = New System.Drawing.Font("Microsoft Sans Serif", 9.75!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.button1.Location = New System.Drawing.Point(10, 8)
Me.button1.Name = "button1"
Me.button1.Size = New System.Drawing.Size(240, 64)
Me.button1.TabIndex = 1
Me.button1.Text = "Update a record"
"
"Form1
"
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(260, 81)
Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.button1})
Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle
Me.MaximizeBox = False
Me.MinimizeBox = False
Me.Name = "Form1"
Me.Text = "Row Updating"
Me.ResumeLayout(False)
End Sub
- End Region
Dim dbConn As New SqlConnection("Server=(local)\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=SSPI")
Dim WithEvents da As New SqlDataAdapter("SELECT ID, FirstName, LastName FROM Employee", dbConn)
Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button1.Click
Dim cb As New SqlCommandBuilder(da)
Dim ds As New DataSet("Employee")
da.Fill(ds)
ds.Tables(0).Rows(0)("ID") = "1"
da.Update(ds.GetChanges())
End Sub
Private Sub da_RowUpdating(ByVal sender As Object, _
ByVal e As SqlRowUpdatingEventArgs) _
Handles da.RowUpdating
" Only perform processing if this is an UPDATE statement
If (e.StatementType = StatementType.Update) Then
" Check that the original record is not changed
Dim strSQL As String = "SELECT ID, FirstName, LastName " & _
"FROM Employee WHERE "
strSQL &= "ID="" & e.Row("ID", DataRowVersion.Original)
strSQL &= "" AND "
strSQL &= "FirstName="" & e.Row("FirstName", DataRowVersion.Original)
strSQL &= "" AND "
strSQL &= "LastName="" & e.Row("LastName", DataRowVersion.Original) & """
" Open the connection
dbConn.Open()
" Create a command to retrieve the number of records affected
Dim cmm As New SqlCommand(strSQL, dbConn)
" If the number of records retrieved is zero, the record has changed
If (cmm.ExecuteNonQuery() = 0) Then
" Display the confirm message
If (MessageBox.Show("The record you are attempting to modify has " & _
"already changed by another user. Do you " & _
"want to overwrite it?", "Update", _
MessageBoxButtons.YesNo) = DialogResult.No) Then
" Skip the update for the current row
e.Status = UpdateStatus.SkipCurrentRow
End If
End If
" Close the connection
dbConn.Close()
End If
End Sub
End Class
</source>
Update a row in DataSet update event
<source lang="vbnet"> Imports System Imports System.Collections Imports System.Data Imports System.IO Imports System.Xml.Serialization Imports System.Xml Imports System.Windows.Forms Imports System.Data.SqlClient Imports System.Data.OleDb Public Class MainClass
Shared Sub Main()
Dim form1 As Form = New Form1
Application.Run(form1)
End Sub
End Class
Public Class Form1
Inherits System.Windows.Forms.Form
- Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
"This call is required by the Windows Form Designer.
InitializeComponent()
"Add any initialization after the InitializeComponent() call
End Sub
"Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
"Required by the Windows Form Designer
Private components As System.ruponentModel.IContainer
"NOTE: The following procedure is required by the Windows Form Designer
"It can be modified using the Windows Form Designer.
"Do not modify it using the code editor.
Friend WithEvents button1 As System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.button1 = New System.Windows.Forms.Button()
Me.SuspendLayout()
"
"button1
"
Me.button1.Font = New System.Drawing.Font("Microsoft Sans Serif", 9.75!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.button1.Location = New System.Drawing.Point(10, 8)
Me.button1.Name = "button1"
Me.button1.Size = New System.Drawing.Size(240, 64)
Me.button1.TabIndex = 1
Me.button1.Text = "Update a record"
"
"Form1
"
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(260, 81)
Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.button1})
Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle
Me.MaximizeBox = False
Me.MinimizeBox = False
Me.Name = "Form1"
Me.Text = "Row Updating"
Me.ResumeLayout(False)
End Sub
- End Region
Dim dbConn As New SqlConnection("Server=(local)\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=SSPI")
Dim WithEvents da As New SqlDataAdapter("SELECT ID, FirstName, LastName FROM Employee", dbConn)
Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button1.Click
Dim cb As New SqlCommandBuilder(da)
Dim ds As New DataSet("Employee")
da.Fill(ds)
ds.Tables(0).Rows(0)("ID") = "1"
da.Update(ds.GetChanges())
End Sub
Private Sub da_RowUpdating(ByVal sender As Object, _
ByVal e As SqlRowUpdatingEventArgs) _
Handles da.RowUpdating
" Only perform processing if this is an UPDATE statement
If (e.StatementType = StatementType.Update) Then
" Check that the original record is not changed
Dim strSQL As String = "SELECT ID, FirstName, LastName " & _
"FROM Employee WHERE "
strSQL &= "ID="" & e.Row("ID", DataRowVersion.Original)
strSQL &= "" AND "
strSQL &= "FirstName="" & e.Row("FirstName", DataRowVersion.Original)
strSQL &= "" AND "
strSQL &= "LastName="" & e.Row("LastName", DataRowVersion.Original) & """
" Open the connection
dbConn.Open()
" Create a command to retrieve the number of records affected
Dim cmm As New SqlCommand(strSQL, dbConn)
" If the number of records retrieved is zero, the record has changed
If (cmm.ExecuteNonQuery() = 0) Then
" Display the confirm message
If (MessageBox.Show("The record you are attempting to modify has " & _
"already changed by another user. Do you " & _
"want to overwrite it?", "Update", _
MessageBoxButtons.YesNo) = DialogResult.No) Then
" Skip the update for the current row
e.Status = UpdateStatus.SkipCurrentRow
End If
End If
" Close the connection
dbConn.Close()
End If
End Sub
End Class
</source>
Use DataSet to query and update Row
<source lang="vbnet"> Imports System Imports System.Xml Imports System.Xml.Schema Imports System.IO Imports System.Data.OleDb Imports System.Data.rumon Imports System.Data
Public Class MainClass
Shared Sub Main()
Dim dsUsers As New DataSet("Employee")
Try
" Define a connection object
Dim dbConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Password=;User ID=Admin;Data Source=Employee.mdb")
" Create a data adapter to retrieve records from db
Dim daUsers As New OleDbDataAdapter("SELECT ID, FirstName, LastName FROM Employee", dbConn)
" Define each column to map
Dim dcmUserID As New DataColumnMapping("ID", "EmployeeID")
Dim dcmFirstName As New DataColumnMapping("FirstName", "FirstName")
Dim dcmLastName As New DataColumnMapping("LastName", "LastName")
" Define the table containing the mapped columns
Dim dtmUsers As New DataTableMapping("Table", "Employee")
dtmUsers.ColumnMappings.Add(dcmUserID)
dtmUsers.ColumnMappings.Add(dcmFirstName)
dtmUsers.ColumnMappings.Add(dcmLastName)
" Activate the mapping mechanism
daUsers.TableMappings.Add(dtmUsers)
" Fill the dataset
daUsers.Fill(dsUsers)
" Set the primary key in order to use the Find() method
" below.
Dim dcaKey() As DataColumn = {dsUsers.Tables(0).Columns("EmployeeID")}
dsUsers.Tables(0).PrimaryKey = dcaKey
" Declare a command builder to create SQL instructions
" to create and update records.
Dim cb = New OleDbCommandBuilder(daUsers)
" Update an existing record in the DataSet
Dim r As DataRow = dsUsers.Tables(0).Rows.Find(1)
If Not r Is Nothing Then
r("FirstName") = "Venus"
r("LastName") = "Williams"
" Update the record even in the database
daUsers.Update(dsUsers.GetChanges())
" Align in-memory data with the data source ones
dsUsers.AcceptChanges()
" Print successfully message
Console.WriteLine("The record has been updated successfully.")
Else
Console.WriteLine("No record found...")
End If
Catch ex As Exception
" Reject DataSet changes
dsUsers.RejectChanges()
" An error occurred. Show the error message
Console.WriteLine(ex.Message)
End Try
End Sub
End Class
</source>
<A href="http://www.vbex.ru/Code/VBDownload/Employee.zip">Employee.zip( 7 k)</a>
