Object|visual How to To:update a Database from a DataSet Object Using visual Basic. NET
This is article discusses a Beta release of a Microsoft product. The information in this article are provided as-is and is subject to change without notice.
No formal product support is available from Microsoft for this Beta product. For information about obtaining support for a beta release, please do not have the documentation included with the Beta product fi Les, or check the Web location from which to you downloaded the release.
--------------------------------------------------------------------------------
The information in this article applies to:
In the This TASK
SUMMARY
Requirements
How to Update a Database from a DataSet Object
Complete Code Listing
REFERENCES
SUMMARY
DataSet objects, a key part of data access to the Microsoft. NET Framework, are in-memory objects that can hold tables, VI EWS, and relationships. This article demonstrates you to take a DataSet that contains data (which are loaded from a database), modify so data, an D then send it back to the database to update the original source.
Back to the top
Requirements
The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:
Microsoft windows Professional, Windows Server, Windows Advanced Server, or Windows NT 4.0 Server
Microsoft SQL Server version 7.0 or, or Microsoft Data Engine (MSDE), with the PUBS sample database installed
Microsoft Visual Studio. NET
This article assumes to are familiar with the following topics:
Database terminology
Structured Query Language (SQL)
Back to the top
How to Update a Database from a DataSet Object
This section demonstrates the "use" DataSet object to update data in a database. It is important to remember this can also use a SqlCommand object to insert, update, and delete data in a database dir ectly.
Understanding the concepts in the following Microsoft knowledge Base article'll help you understand this article:
Q301216 How to To:populate a DataSet Object from a Database
Some of the topics that are covered in Q301216 include how to retrieve data from a database and into a DataSet, and how T The He DataSet is separate and distinct from the database.
After the "DataSet is loaded", you can modify the data, and the dataset would track the changes. The DataSet object can be considered a in-memory cache of data This is retrieved to a database and consists of a Collec tion of tables, relationships, and constraints.
To update a DataSet and send those updates the database, follow these steps:
Open Visual Studio. NET
Create a new Console application in Visual Basic. NET. By default, Visual Studio creates a Static Module and a Empty Main () procedure.
Make sure this project contains a reference to the System and System.Data namespaces. Use the "Imports on" System, SystemData, and System.Data.SqlClient namespaces so-you are don't required to qualify Declarations from this namespaces later in your code. You are must use this statements prior to any other declarations.
Imports System
Imports System.Data
Imports System.Data.SqlClient
Before you can modify the the data and submit the changes back to the database, your must load the information into the DataSet . For the detailed procedure, refer to Q301216. To avoid duplication, the "code in", "is not" presented in detail.
The connection string in the following code points to a SQL Server which located on the local computer (or the computer Where the code is running this has a blank password for the ' sa ' account. Replace This string with your own settings, if required. In brief, a connection are created, and then a data adapter is created, which are used to fill the DataSet with data.
Dim sConnectionString as String
' Modify the following code to correctly connect to your SQL Server.
sConnectionString = "password=; User Id=sa; "& _
"Initial catalog=pubs;" & _
"Data source= (local)"
Dim objconn as New SqlConnection (sConnectionString)
objConn.Open ()
' Create An instance of a DataAdapter.
Dim Daauthors As _
New SqlDataAdapter ("select * from Authors", objconn)
' Create An instance's a DataSet and retreive data from the Authors table.
Dim dspubs as New DataSet ("Pubs")
daAuthors.FillSchema (dspubs, SchemaType.Source, "Authors")
daAuthors.Fill (dspubs, "Authors")
Now, the "data is loaded," can modify it. There are many ways to add a row (or record). This code sample uses a three step procedure:
Obtain a new DataRow object from the DataTable.
Set the DataRow field values as necessary.
Pass this new object into the Add method of the DataTable.Rows collection.
Paste the following code after the code in step 4:
'*****************
' BEGIN ADD CODE
' Create a new instance of a DataTable
Dim tblauthors as DataTable
tblauthors = Dspubs.tables ("Authors")
Dim drcurrent as DataRow
' Obtain a new DataRow object from the DataTable.
drcurrent = Tblauthors.newrow ()
' Pass ' is the new object into the Add method of the DataTable.Rows collection.
TBLAUTHORS.ROWS.ADD (drcurrent)
MsgBox ("Add was successful.")
' End ADD CODE
To edit existing rows, obtain the appropriate DataRow object, and provide new values for one or more columns. You must the correct row, a process which is made much easier because you loaded the schema of the table as a As the data (the call to FillSchema in step 4). With the schema in place, the table knows which column is it primary key, and the Find method of the Rows collection is a vailable.
The Find method returns the DataRow object and a specific value in its primary key (in this case, au_id). After you have this DataRow, you can modify the columns. You don't have to wrap of the modifications in BeginEdit and EndEdit, but this simplifies the work of the DataSet has to Do and allows the DataSet to perform it validation checks all in once the upon call. Paste the following code after the ADD code:
' End EDIT CODE
To update the original database and all of this changes, pass the DataSet into the Update method of the DataAdapter Obje Ct.
However, before can call Update, you must set the InsertCommand, UpdateCommand, and DeleteCommand properties of the DataAdapter object. You can manually write SQL and populate this three properties with corresponding SqlCommand objects, but your can also use Visual Studio. NET to generate these three commands automatically.
To generate the required commands when they are needed, you must create a instance the the SqlCommandBuilder object and US E The DataAdapter in the constructor. If you are want to use this method, which are illustrated in the code sample to follow, for you must have the primary key information a vailable for your table. To access primary key information, call FillSchema and set the MissingSchemaAction property of your DataAdapter to Addwith Key, or manually set the primary key in your code. Paste the following code after the EDIT code:
'*****************
' BEGIN SEND CHANGES to SQL SERVER
Dim objCommandBuilder as New SqlCommandBuilder (daauthors)
daAuthors.Update (dspubs, "Authors")
MsgBox ("SQL Server updated successfully" & Chr () & "Check Server Explorer to? Changes")
' End SEND CHANGES to SQL SERVER
To delete a row completely and use the Delete method of the DataRow object. Note This Rows collection contains two methods, Remove and RemoveAt, which seem to delete the row but instead just re Move the row from the collection. Only the Delete method sends your deletion the source database. Paste the following code after the SEND CHANGES to SQL SERVER code:
' End DELETE CODE
Send the changes to SQL Server to-remove the record this you added earlier. Paste the following code after the DELETE code:
'*****************
' Clean up SQL SERVER
daAuthors.Update (dspubs, "Authors")
MsgBox ("SQL Server updated successfully" & Chr (A) & Chr (a) & Check Server Explorer to I-changes)
Save your project.
On the Debug menu and click Start to run the project. Notice that several message boxes appear, which indicate the progress of the code and allow your to review the current stat E of the data as the code progresses.
Back to the top
Complete Code Listing
Imports System
Imports System.Data
Imports System.Data.SqlClient
Module Module1
Sub Main ()
Dim sConnectionString as String
' Modify the following code to correctly connect to your SQL Server.
sConnectionString = "password=; User Id=sa; "& _
"Initial catalog=pubs;" & _
"Data source= (local)"
Dim objconn as New SqlConnection (sConnectionString)
objConn.Open ()
' Create An instance of a DataAdapter.
Dim Daauthors As _
New SqlDataAdapter ("select * from Authors", objconn)
' Create An instance's a DataSet and retreive data from the Authors table.
Dim dspubs as New DataSet ("Pubs")
daAuthors.FillSchema (dspubs, SchemaType.Source, "Authors")
daAuthors.Fill (dspubs, "Authors")
'*****************
' BEGIN ADD CODE
' Create a new instance of a DataTable
Dim tblauthors as DataTable
tblauthors = Dspubs.tables ("Authors")
Dim drcurrent as DataRow
' Obtain a new DataRow object from the DataTable.
drcurrent = Tblauthors.newrow ()
' End EDIT CODE
'*****************
' BEGIN SEND CHANGES to SQL SERVER
Dim objCommandBuilder as New SqlCommandBuilder (daauthors)
daAuthors.Update (dspubs, "Authors")
MsgBox ("SQL Server updated successfully" & Chr () & "Check Server Explorer to? Changes")
' End SEND CHANGES to SQL SERVER
'*****************
' BEGIN DELETE CODE
' End DELETE CODE
'*****************
' Clean up SQL SERVER
daAuthors.Update (dspubs, "Authors")
MsgBox ("SQL Server updated successfully" & Chr (A) & Chr (a) & Check Server Explorer to I-changes)
End Sub
End Module
Back to the top
REFERENCES
For more information about using ActiveX Data Objects (ADO). NET, DataSet Objects, and SQL, refer the following Microsoft Web sites:
' Diving into Data Access ' (An MSDN voices column)
Http://msdn.microsoft.com/voices/data.asp
"Ado.net for the ADO programmer"
Http://msdn.microsoft.com/library/techart/adonetdev.htm
MSDN Online. NET Developer Center
Http://msdn.microsoft.com/net
Back to the top
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
