Inserting and deleting data using DataAdapter and DatSet

Question

4.00/5 (1 vote)

See more:

I want to update, delete and insert data through data set and data adapter. Here is a patch of code for inserting the data. Can anyone help me on updating and deleting the data in the Access database?

C#

DataRow oOrderRow = oDS.Tables["Orders"].NewRow();
oOrderRow["CustomerName"] = "Customer ABC";
oOrderRow["ShippingAddress"] = "ABC street, 12345";
oDS.Tables["Orders"].Rows.Add(oOrderRow);
  
DataRow oDetailsRow = oDS.Tables["OrderDetails"].NewRow();
oDetailsRow["ProductId"] = 1;
oDetailsRow["ProductName"] = "Product 1";
oDetailsRow["UnitPrice"] = 1;
oDetailsRow["Quantity"] = 2;
oDetailsRow.SetParentRow(oOrderRow);
oDS.Tables["OrderDetails"].Rows.Add(oDetailsRow);
 
oOrdersDataAdapter.Update(oDS, "Orders");
oOrderDetailsDataAdapter.Update(oDS, "OrderDetails");
conn.Close();

Posted 12-Apr-11 11:49am

codegeekalpha

Updated 12-Apr-11 12:04pm

Nish Nishant

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Oshtri Deka · Answer 1 · 2011-04-12T12:29:00

First you must find particular row, one way or another. I'll assume you know row's ID (PK) or index in table and that you don't use typed dataset.

//find your row by ID
DataRow myRow = oDS.Orders.Rows.Find(rowID);

or

//find your row by index
DataRow myRow = oDS.Orders.Rows[index];

//similar thing can be done with typed dataSet, consider this as pseudo-code because I don't know all names.
YourDataSetName.OrdersRow myRow = oDS.Orders.FindByOrdersID(rowID);

Now you can manipulate with row as you wish.

//deleting  
myRow.Delete();

or

//editing
myRow["CustomerName"]= "New name";
//etc.

When you are done call the adapter's Update method like you do after insert.

oOrdersDataAdapter.Update(oDS);

Just play with it, I've learned, so can you :)!

sk saini · Answer 2 · 2011-04-12T17:56:00

I shall use SqlCommandBuilder for the purpose of Record insertion and deletion for your code. After adding new row (with records) to your table add following code :

int NofRecs_Added = 0; // to indicate a records has been added
int NofRecs_Edited = 0;// to indicate a records has been edited 

oDS.Tables["Orders"].Rows.Add(oOrderRow); // Your line of code
NofRecs_Added++;   // record added indication
               // do this for NofRecs_Edited also (in case any record edited)

 // Now adding records to SQL Database
    SqlCommandBuilder sqbld = new SqlCommandBuilder(oOrdersDataAdapter);
    try
    {
        if (oDS.HasChanges() == true)
        {
           if (NofRecs_Added >= 1) 
           {
             // AddDataSet and EditDataSet to declared earlier like your oDS                   with Same DataAdapter and table to oDS dataset
               AddDataSet = oDS.GetChanges(DataRowState.Added); 
               oOrdersDataAdapter.Update(AddDataSet, "Orders");
           }
     // For deletion     
     if (NofRecs_Edited >= 1)  
     {
        EditDataSet = oDS.GetChanges(DataRowState.Deleted);
        oOrderDataAdapter.Update(EditDataSet, "Orders");
     }

        oDS.AcceptChanges();
   }
}
catch (Exception s)
{
    MessageBox.Show(s.Message);
}

Inserting and deleting data using DataAdapter and DatSet

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0