HOW TO: Update a Database from a DataSet Object Using Visual Basic .NET
敬业的IT人
互联网
佚名
2008-5-23 19:20:36
HOW TO: Update a Database from a DataSet Object Using Visual Basic .NET
This article discusses a Beta release of a Microsoft product. The information in this article is provided as-is and is subject to change without notice.
--------------------------------------------------------------------------------
The information in this article applies to:
Microsoft Visual Basic .NET Beta 2
--------------------------------------------------------------------------------
IN 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
back to the top
Requirements
The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:
Microsoft
Microsoft
Microsoft Visual Studio .NET
This article assumes that you 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 how to use the DataSet object to update data in a database. It is important to remember that you can also use a SqlCommand object to insert, update, and delete data in a database directly.
Understanding the concepts in the following Microsoft Knowledge Base article will help you understand this article:
Q301216 HOW 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 the DataSet is separate and distinct from the database.
After the DataSet is loaded, you can modify the data, and the DataSet will track the changes. The DataSet object can be considered an in-memory cache of data that is retrieved from a database and consists of a collection of tables, relationships, and constraints.
To update a DataSet and send those updates back to 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 an empty Main() procedure.
Make sure that the project contains a reference to the System and System.Data namespaces. Use the Imports on the System , SystemData , and System.Data.SqlClient namespaces so that you are not required to qualify declarations from these namespaces later in your code. You must use these statements prior to any other declarations.
Imports System
Imports System.Data
Imports System.Data.SqlClient
Before you can modify the data and submit the changes back to the database, you must load the information into the DataSet . For the detailed procedure, refer to Q301216 . To avoid duplication, the code in this step is not presented in detail.
The connection string in the following code points to a SQL Server that is located on the local computer (or the computer where the code is running) that has a blank password for the 'sa' account. Replace this string with your own settings, if required. In brief, a connection is created, and then a data adapter is created, which is 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 of 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 that the data is loaded, you 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 that 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()
' Set the DataRow field values as necessary.
drCurrent("au_id") = "993-21-3427"
drCurrent("au_fname") = "George"
drCurrent("au_lname") = "Johnson"
drCurrent("phone") = "800 226-0752"
drCurrent("address") = "1956 Arlington Pl."
drCurrent("city") = "Winnipeg"
drCurrent("state") = "MB"
drCurrent("contract") = 1
'Pass that 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 first find the correct row, a process that is made much easier because you loaded the schema of the table as well as the data (the call to FillSchema in step 4). With the schema in place, the table knows which column is its primary key, and the Find method of the Rows collection is available.
The Find method returns the DataRow object with a specific value in its primary key (in this case, au_id). After you have that DataRow, you can modify the columns. You do not have to wrap the modifications in BeginEdit and EndEdit , but this simplifies the work that the DataSet has to do and allows the DataSet to perform its validation checks all at once upon the EndEdit call. Paste the following code after the ADD code:
'*****************
'BEGIN EDIT CODE
drCurrent = tblAuthors.Rows.Find("213-46-8915")
drCurrent.BeginEdit()
drCurrent("phone") = "342" & drCurrent("phone").ToString.Substring(3)
drCurrent.EndEdit()
MsgBox("Record edi
- 最新文章
- VisualBasic.NET带来的新方法(一)[05-23]
- 从VisualBasic6.0到VisualBasic.NE..[05-23]
- VB6中水晶报表(CrystalReport4.6)的使用经..[05-23]
- VB.NET中使用GDI画图具体应用。[05-23]
- VB.NET中实现关机/休眠/重启/注销的类[05-23]
- vb.net执行外部程序的代码[05-23]
- 相关文章
