Update Dataset data back to Database


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**

For more articles updates and sourcecodes please visit dotnetcoderoom

Advertisements

About dotnetcoderoom

Working as an Onsite coordinator in USA. Handling UAT, CR (Change Requests), Product Support/Development, Build Management. Supervise and assist in live product implementation (at client side) and training. Providing technical direction for the development, design, and system integration. Closely monitoring and working with offshore team for Enhancements, Bug Fixing, Enforce coding standards, perform code reviews and mentors junior developers. Proven track of timely delivery of enterprise level web based applications. Skilled in a variety of software languages, tools, and methodologies, with a special expertise in Microsoft .NET technologies. MCTS .Net 4.0 Web applications MCTS .Net 2.0 Web applications GNIIT, MCA, B.Com Specialties 8+ Years of experience in .NET Technologies. Expertise in .NET, ASP.NET, MVC, C#, VB.NET, WCF, Web Services, MS SQL, JQuery. Excellent R&D and troubleshooting skills. Extensive knowledge in E-Commerce and Healthcare domain. View all posts by dotnetcoderoom

2 responses to “Update Dataset data back to Database

  • Alex

    Your blog is interesting!

    Keep up the good work!

  • Shifty

    Nice tutorial. I’ve been reading everywhere and still can’t seem to get the dataAdapter.Update(dataSet, “Name”) to actually update the database.

    If I don’t do .AcceptChanges() before the .Update() then I get an error.

    My code is similar to yours so I don’t see a problem.

    Is your code for a sql server database?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: