VB.Net/Database ADO.net/DataSet Update — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Текущая версия на 15:44, 26 мая 2010
Содержание
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>