Suitable
The release number of this article has been CHS307587
For Microsoft Visual Basic .NET versions of this article, see
301248.
This task content
summary
Require how to update the database complete code list reference from the data set object
summary
The data set object is a key section of data access in the Microsoft .NET framework, which is a memory that can save tables, views, and relationships. This article describes how to get the data containing data (loaded from database)
Dataset, how to modify this data, and how to send it back to the database to update the original data source.
Back to top
The following table summarizes the recommended hardware, software, network structure, and the required service pack:
Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server or Microsoft Windows NT 4.0 Server Microsoft SQL Server version 7.0, Microsoft SQL Server 2000 or install Microsoft Data Engine PUBS sample database (MSDE) Microsoft Visual Studio .NET article Assume that you are familiar with the following topics:
Database Technology Structured Query Language (SQL)
Back to top
How to update the database this section of the Data Set Object Release How to use
Data set object updates the data in the database. Can also use
SQLCommand objects are inserted, updated, and deleted data directly in the database, remember this is important.
If you want to better understand this article, click the article number below to view the article in the Microsoft Knowledge Base:
314145 How to: populate a dataset object from a database by using visual c # .NET (using Visual C # .NET from Database Database)
314145 describes how to retrieve data from a database and transfer this data to
Data set, and tell why
The data set is independent of the database and it is from the database.
load
After the data set, you can modify the data.
The dataset will track these changes. Can
Dataset objects are deemed to be cached in memory from the database.
The data set object consists of a collection of tables, relationships, and constraints.
To update
Data sets and send these updates back to the database, follow these steps:
Open Microsoft Visual Studio .NET. Create a console application in Visual C # .NET. Visual Studio will create a static class and an empty main () process by default. Make sure the project contains references to System and System.Data namespaces. Using the USING statement for System, SystemData, and System.Data.sqlclient namespace, so that there is no need to define declarations in these namespaces in the later code. These statements must be used before any other statement. Using system;
Using system.data;
Using system.data.sqlclient; you must load this information into the DataSet (Data) before modifying the data and sends the changes back to the database. For details, see 314145. To avoid repetition, the code in this step will not be provided in detail. The connection string in the following code points to the SQL Server on the local computer (or computer running this section) of the "SA" account. If you need, replace the string with your own settings. In short, create a connection first, then create a data adapter; the adapter is used to fill the data into the DataSet. String sconnectionstring; // modify the fol your sql server.
Sconnectionstring = "Password =; user ID = sa;"
"Initial Catalog = PUBS;"
"Data Source = (local)";
SqlConnection Objconn
= New SqlConnection (SCONNNEctionstring);
Objconn.open ();
// CREATE AN Instance of a DataAdapter.
SqlDataAdapter Daauthors
= New SqldataAdapter ("Select * from authors", objconn);
// Create An Instance of a Dataset, AND RETRIEVE DATA from the Authors Table.
DataSet DSPUBS = New Dataset ("Pubs");
Daauthors.Fillschema (DSPUBS, Schematype.Source, "Authors");
Daauthors.Fill (DSPUBS, "Authors"); now there is already loaded, you can modify it. Adding a line (or record) has a variety of ways. This code example uses a three-step process:
Get a new DataRow object from DataTable. Set the value of the DATAROW field as needed. Pass the new object to the add method of the DataTable.Rows set. Paste the following code to the back of the code in step 4: // ***************
// Begin Add Code
// CREATE A New Instance of a DataTable.
DataTable tblauthors;
TBLAUTHORS = DSPUBS.TABLES ["authors"];
DataRow Drcurrent;
// Obtain a new DataRow Object from the datable.
DRCURRENT = TBLAUTHORS.NEWROW ();
// set the datarow field value 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 DataSle.
TBLAUTHORS.ROWS.ADD (DRCURRENT);
Console.writeline ("Add Was Successful, Click Any Key To Continue !!);
Console.readline ();
// end add code Paste the following code to the following: To edit an existing row, get the corresponding DataRow object and provide a new value for a column or multiple columns. You must first find the right row, because you load the schema and data of the table (call to FillSchema in step 4), this process is very simple. With the architecture, the table knows which column is its primary key, and the Find method of the ROWS collection can be used. The Find method returns a DataRow object that has a specific value in this object's primary key (AU_ID in this case) has a specific value. After you have this DATAROW, you can modify the column. You don't need to pack changes in BeGinedit and Endedit, but packaging simplifies the work that DataSet must complete, and make DataSet to execute its verification check while calling endedit. Paste the following code to 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 ();
Console.writeline ("Record Edited Success, Click Any KEY TO Continue !!);
Console.readline ();
// end Edit Code paste the following code to the following: To update the original database with all of these Changes, pass the DataSet to the Update method of the DataAdapter object. However, before you call Update, you must set the InsertCommand, UpdateCommand, and DeleteCommand properties of the DataAdapter object. SQL can be written manually and populate these three properties with the corresponding SQLCommand object, but you can also use Visual Studio .NET to automatically generate these three commands. To generate the required commands when needed, you must create an instance of the SQLCommandBuilder object and use DataAPter in the constructor. If you want to use this method (interpretation in the following code example), your table must have primary key information. To access the primary key information, call FillSchema, then set the DataAdapter's MissingsChemaAction property to addWithKey, or manually set the primary key in the code. Paste the following code to the EDIT code: // ***************** // Begin Send Changes to SQL Server
Sqlcommandbuilder objcommandbuilder = new sqlcommandbuilder (daauthors);
Daauthors.Update (DSPUBS, AUTHORS ");
Console.Writeline ("SQL Server Updated Success, Check Server Explorer to See Changes");
Console.readline ();
// End send changes to SQL Server paste the following code to the following: To completely delete a row, use the Detarow object's delete method. Note that the ROWS collection includes two methods of remove and removeat, which seems to be deleted, and actually only removes the rows from the collection. Only the Delete method will send the delete result back to the source database. Paste the following code to Send Changes to SQL Server code: // **************************
// Begin delete code
DRCURRENT = TBLAUTHORS.ROWS.FIND ("993-21-3427");
DRCURRENT.DELETE ();
Console.writeline ("Record Deleted Success, Click Any Key To Continue !!);
Console.readline ();
// end delete code Paste the following code to the end of the code in step 4: Send these changes to SQL Server to remove the previously added records. Paste the following code to the Delete code: // **************************
// Clean Up SQL Server
Daauthors.Update (DSPUBS, AUTHORS ");
Console.Writeline ("SQL Server Updated Success, Check Server Explorer to See Changes");
Console.readLine (); paste the following code to the following: Save the item. Paste the following code to the back of the code in step 4: On the Debug menu, click Start to run the item. Note that a few message boxes will appear, and they indicate the progress of the code and allow you to view the current state of the data during the execution process. Back to top
Complete code list
Using system;
Using system.data;
Using system.data.sqlclient;
Namespace populateDataSet
{
///
/// summary description for class1.
///
Class class1
{
Static void main (string [] args)
{
String sconnectionstring;
// Modify The Following String to Correctly Connect To your SQL Server.
Sconnectionstring = "Password =; user ID = sa;"
"Initial Catalog = PUBS;"
"Data Source = (local)";
SqlConnection Objconn
= New SqlConnection (SCONNNEctionstring);
Objconn.open ();
// CREATE AN Instance of a DataAdapter.
SqlDataAdapter Daauthors
= New SqldataAdapter ("Select * from authors", objconn);
// Create An Instance of a Dataset, And Retrieve
// Data from the authors table.
DataSet DSPUBS = New Dataset ("Pubs");
Daauthors.Fillschema (DSPUBS, Schematype.Source, "Authors");
Daauthors.Fill (DSPUBS, "Authors");
// ****************
// Begin Add Code
// CREATE A New Instance of a DataTable.
DataTable tblauthors;
TBLAUTHORS = DSPUBS.TABLES ["authors"];
DataRow Drcurrent;
// Obtain a new DataRow Object from the datable.
DRCURRENT = TBLAUTHORS.NEWROW ();
// set the datarow field value 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 DataSle.
TBLAUTHORS.ROWS.ADD (DRCURRENT);
Console.writeline ("Add Was Successful, Click Any Key To Continue !!);
Console.readline ();
// end add code
// *****************
// Begin Edit Code
DRCURRENT = TBLAUTHORS.ROWS.FIND ("213-46-8915");
DRCURRENT.BEGINEDIT ();
Drcurrent ["phone"] = "342" DRCURRENT ["phone"]. TOSTRING (). Substring (3);
Drcurrent.endedit ();
Console.writeline ("Record Edited Success, Click Any KEY TO Continue !!);
Console.readline ();
// end Edit Code
// *****************
// Begin Send Changes to SQL Server
Sqlcommandbuilder objcommandbuilder = new sqlcommandbuilder (daauthors);
Daauthors.Update (DSPUBS, AUTHORS ");
Console.Writeline ("SQL Server Updated Success, Check Server Explorer to See Changes");
Console.readline ();
// end send changes to SQL Server
// *****************
// Begin delete code
DRCURRENT = TBLAUTHORS.ROWS.FIND ("993-21-3427");
DRCURRENT.DELETE ();
Console.writeline ("SRecord deleted successfully, click any key to pay !!");
Console.readline ();
// end delete code
// *****************
// Clean Up SQL Server
Daauthors.Update (DSPUBS, AUTHORS ");
Console.Writeline ("SQL Server Updated Success, Check Server Explorer to See Changes");
Console.readline ();
}
}
}
Back to top
Refer to the use of ADO.NET,
For more information on dataset objects and SQL, please visit the following Microsoft Web site:
