How to: Use Visual C #. Net to update a database from a DataSet object (from msdn)

How to: update a database from a DataSet object using Visual C #. net

Applicable

The release number of this article was chs307587. For the Microsoft Visual Basic. Net version in this article, see 301248.

Content of this task

• Summary
• • Requirements
  • How to update a database from a DataSet object
  • Complete code list
• Reference

Summary

DatasetObjects are a key part of data access in the Microsoft. NET Framework. They are memory objects that can store tables, views, and relationships. This document describes how to obtain data that contains (load from a database)Dataset, How to modify the data, and then how to send it back to the database to update the original data source.

Back to Top

Requirements

The following table summarizes the recommended hardware, software, network structure, and required service packages:

• Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server, or Microsoft Windows NT 4.0 Server
• Microsoft SQL Server 7.0, Microsoft SQL Server 2000, or Microsoft Data Engine (MSDE) with the pubs sample database installed)
• Microsoft Visual Studio. NET

This document assumes that you are familiar with the following topics:

• Database Technology
• Structured Query Language (SQL)

Back to Top

How to update a database from a DataSet object

This section describes how to useDatasetObject updates data in the database. You can also useSqlcommandIt is important to remember that objects insert, update, and delete data directly in the database.

To better understand this article, click the following article number to view the article in the Microsoft Knowledge Base:

• 314145 how to: populate a DataSet object from a database by using Visual C #. Net (Use Visual C #. Net to populate the DataSet object from the database)

314145 describes how to retrieve data from the database and transfer the dataDatasetAnd explains whyDatasetIt is independent of the database and what is the difference between it and the database.

LoadDatasetThen you can modify the data.DatasetThese changes will be tracked. You canDatasetThe object is regarded as the data cached in the memory retrieved from the database.DatasetAn object consists of a set of tables, relationships, and constraints.

To updateDatasetTo send these updates back to the database, follow these steps:

1. Open Microsoft Visual Studio. NET.
2. Create a new console application in Visual C #. net. Visual Studio creates a static class and an emptyMain ()Process.
3. Make sure that this project includesSystemAndSystem. DataNamespace reference. PairSystem,SystemdataAndSystem. Data. sqlclientNamespace usageUsingIn this way, you do not need to limit the declarations in these namespaces in the subsequent code. These statements must be used before any other declaration.

   using System;using System.Data;using System.Data.SqlClient;

4. Before you modify data and send the changes back to the database, you must load the informationDataset(Dataset. For detailed procedure, see 314145. To avoid duplication, the code in this step is not provided in detail.

   The connection string in the following code points to the SQL server on the local computer whose "sa" account has an empty password (or the computer on which the Code is run. If necessary, replace the string with your own settings. In short, first create a connection and then create a data adapter, which is used to populate the dataDataset.

   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(sConnectionString);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");

5. Now that you have loaded the data, you can modify it. You can add rows (or records) in multiple ways. The sample code uses a three-step process:
   1. • SlaveDatatableObtain a newDatarowObject.
      • Set as neededDatarowField Value.
      • Pass the new objectDatatable. RowsSetAddMethod.

   Paste the following code behind the code in Step 1:

   //****************// 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 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.tblAuthors.Rows.Add(drCurrent);Console.WriteLine("Add was successful, Click any key to continue!!");Console.ReadLine();// END ADD CODE  

6. Paste the following code to the end of the Code in Step 1: To edit an existing row, obtain the correspondingDatarowObject, and then provide new values for one or more columns. You must first find the correct row because you have loaded the schema and data of the table (in step 4thFillschemaSo this process is very simple. With the architecture, the table will know which column is its primary key.RowsSetFindThe method is also available.
   FindMethod will returnDatarowObject. The primary key of this object (in this example, au_id) has a specific value. With this datarow, you can modify the column. You do not need to packBegineditAndEndeditBut the packaging can be simplified.DatasetAnd makeDatasetYou can callEndeditAt the same time. Paste the following code into 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 successfully, Click any key to continue!!");Console.ReadLine();// END EDIT CODE  

7. Paste the following code behind the code in Step 1: to use all these changes to update the original databaseDatasetPassDataadapterObjectUpdateMethod.

   However, when you callUpdateBefore, you must setDataadapterObjectInsertcommand,UpdatecommandAndDeletecommandAttribute. You can manually write SQL statements and use the correspondingSqlcommandYou can also use Visual Studio. NET to automatically generate these three commands.

   To generate the required command as needed, you must createSqlcommandbuilderInstance of the object and used in the constructorDataadapter. If you want to use this method (explained in the following code example), your table must have primary key information. To access the primary key information, callFillschema, And thenDataadapterOfMissingschemaactionSet propertyAddwithkeyOr manually set the primary key in the code. Paste the following code into the edit code:

   //*****************// BEGIN SEND CHANGES TO SQL SERVER SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(daAuthors);daAuthors.Update(dsPubs, "Authors");Console.WriteLine("SQL Server updated successfully, Check Server explorer to see changes");Console.ReadLine();// END SEND CHANGES TO SQL SERVER

8. Paste the following code to the end of the Code in Step 1: to delete a row completely, useDatarowObjectDeleteMethod. Please note that,RowsSet inclusionRemoveAndRemoveatTwo methods, they seem to be able to delete rows, but actually only remove rows from the set. OnlyDeleteMethod to send the deletion result back to the source database. Paste the following code after sending changes to SQL Server:

   //*****************//BEGIN DELETE CODE drCurrent = tblAuthors.Rows.Find("993-21-3427");drCurrent.Delete();Console.WriteLine("Record deleted successfully, Click any key to continue!!"); Console.ReadLine();//END DELETE CODE 

9. Paste the following code behind the code in step 1: Send these changes to SQL Server to remove the records that were previously added. Paste the following code into the Delete Code:

   //*****************// CLEAN UP SQL SERVERdaAuthors.Update(dsPubs, "Authors");Console.WriteLine("SQL Server updated successfully, Check Server explorer to see changes");Console.ReadLine();

10. Paste the following code behind the code in Step 1: Save the project.
11. Paste the following code behind the code in Step 1:DebuggingClickStartTo run the project. Note that several message boxes will appear, which indicate the code execution progress and allow you to view the current status of data during execution.

Back to Top

Complete code list

using System;using System.Data;using System.Data.SqlClient;namespace PopulateDataSet{/// <summary>/// Summary description for Class1./// </summary>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(sConnectionString);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 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.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 successfully, 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 successfully, 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 continue!!"); Console.ReadLine();       //END DELETE CODE              //*****************// CLEAN UP SQL SERVERdaAuthors.Update(dsPubs, "Authors");            Console.WriteLine("SQL Server updated successfully, Check Server explorer to see changes");Console.ReadLine();                 }    }}

Back to Top reference

About using ADO. net,DatasetFor more information about object and SQL, visit the following Microsoft Web site:

In-depth understanding of data access (Msdn soundColumn)
Http://msdn.microsoft.com/voices/data.asp

ADO. NET and ADO programmers
Http://msdn.microsoft.com/library/default.asp? Url =/library/en-US/dndotnet/html/adonetprogmsdn. asp

Msdn online. Net Developer Center
Http://msdn.microsoft.com/net

The information in this article is applicable:

• Microsoft Visual C #. Net (2002)

Latest updates:	(1.0)
Keywords	Kbdsupport kbgrpdsvbdb kbhowto kbhowtomaster kb307587 kbauddeveloper