Update Dataset data back to Database

本文介绍了如何使用C#语言和DataSet在数据库中进行数据的插入、更新和删除操作。首先需将数据从数据库填充到DataSet,之后可对数据进行修改,最后使用SqlDataAdapter的Update方法将DataSet中的数据提交到数据库。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

This post explains how to insert, update, and delete data in a Database using Dataset.

The DataSet can be considered an in-memory cache of data retrieved from a database. The DataSet consists of a collection of tables, relationships, and constraints.

Firstly, we have to fill data into a DataSet from Database.
Secondly, when the DataSet is loaded, you can modify the data, and the DataSet will keep track of the changes made bu user.

The Add method of DataTable accepts either an array of the expected data columns, or a DataRow.

(Please note that all code is in c# language)

Step 1: Create a new Connection and SqlDataAdapter
SqlConnection myConnection = new SqlConnection(“connectionStringGoesHere”);
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(“Select * from tblNews”, myConnection);
DataSet myDataSet = new DataSet();
DataRow myDataRow;

Step 2: Create SqlCommandBuilder(The SqlDataAdapter does not automatically generate the Transact-SQL statements required to match changes made to a DataSet with SQL Server. But, you can create a SqlCommandBuilder object to automatically generate Transact-SQL statements for single-table updates.)
SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(mySqlDataAdapter);

// Set the MissingSchemaAction property to AddWithKey because Fill will not cause primary
// key & unique key information to be retrieved unless AddWithKey is specified.
mySqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

mySqlDataAdapter.Fill(myDataSet, “tblNews”);

Step 3: Add rows to DataTable

myDataRow = myDataSet.Tables["tblNews"].NewRow();
myDataRow["cNewsId"] = “NewID”;
myDataRow["cHeading"] = “New Heading”;
myDataRow["cContent"] = “Content here”;

myDataSet.Tables["tblNews"].Rows.Add(myDataRow);

Step 4: Editing Row Data
We can change the data in a DataRow by accessing the DataRow. Use the index of the row in the RowsCollection accessed through the Rows property:

myDataSet.Tables["tblNews"].Rows[0]["cHeading"]=”Abc”;

Access a specific row by specifying the Primary Key value:

DataRow myDataRow1 = myDataSet.Tables["tblNews"].Rows.Find(“124″);
myDataRow1["cHeading"]=”Abc”;

Here “124″ is the value of the Primary Key “cHeading” in the “tblNews” table.

Step 5: Deleting a record
Use Delete method to delete a Row. All changes are done in dataset until you don’t explicitly update DataSet data to the Database. Use RejectChanges method of DataSet to reverse all changes made to DataSet.

myDataSet.Tables["tblNews"].Rows[0].Delete();

Note: The original and new values are maintained in the row. The RowChanging event allows you to access both original and new values to decide whether you want the edit to proceed.

SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(mySqlDataAdapter);

Step 6: Finally Update DataBase
To submit the data from the DataSet into the database, use dataAdapter’s Update method.

mySqlDataAdapter.Update(myDataSet, “tblNews”);

A Visual Studio Solution code sample will be coming soon……

**End of article**

转载于:https://www.cnblogs.com/zhangchenliang/archive/2012/11/01/2749931.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值