Update database table with datatable in c#

Question

_{Sunday, April 28, 2013 9:30 AM}

Hello everyone! 

I am retrieving some data from excel displaying it  on datagridview and then adding one extra column to the datatable as primary key and this changes are again displayed back to the datagridview.

Now i want to save this datatable to database as a new table but my code is not reflecting any changes in database. what am i doing wrong?

  private void button4_Click(object sender, EventArgs e)
       {
            string connetionString = null;
            OleDbConnection connection ;
            OleDbDataAdapter oledbAdapter ;
            OleDbCommandBuilder oledbCmdBuilder ;
            DataSet ds = new DataSet();
            int i = 0;
            string sql = null;
            connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Sonali\Desktop\test1.mdb;";
            connection = new OleDbConnection(connetionString);
            sql = "SELECT * From test_table3";
            try
            {
               
                oledbAdapter = new OleDbDataAdapter(sql, connection);
                oledbCmdBuilder = new OleDbCommandBuilder(oledbAdapter);
                oledbAdapter.Fill(ds, "test_table3");
                connection.Open();
                DataTable dt;
                dt = (DataTable)dataGridView1.DataSource;             
                ds.Tables.Add(dt);
                ds.AcceptChanges();
                int cont;
                cont = oledbAdapter.Update(ds,"Table1");
                connection.Close();
                MessageBox.Show ("table updated");
                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }                               

All replies (4)

_{Sunday, April 28, 2013 7:25 PM ✅Answered}

I'm Assumng your using a Data Adapter to Fill the Data Set, with a Select Command?

To edit the data in your Data Table and save changes back to your database you will require an Update Command for you Data Adapter. Something like this :-

SQLConnection connector = new SQLConnection(@"Your connection string");

SQLAdaptor Adaptor = new SQLAdaptor();

Updatecmd = new sqlDbCommand("UPDATE YOURTABLE SET FIELD1= @FIELD1, FIELD2= @FIELD2   WHERE ID = @ID", connector);

You will also need to Add Parameters for the fields :-

Updatecmd.Parameters.Add("@FIELD1", SQLDbType.VarCHar, 8, "FIELD1");
Updatecmd.Parameters.Add("@FIELD2", SQLDbType.VarCHar, 8, "FIELD2");

var param = Updatecmd.Parameters.Add("@ID", SqlDbType.Interger, 6, "ID");
param.SourceVersion = DataRowVersion.Original;

Once you have created an Update Command with the correct SQL statement, and added the parameters, you need to assign this as the Insert Command for you Data Adapter :-

Adaptor.UpdateCommand = Updatecmd;

You will need to read up on doing this yourself, go through some examples, this is a rough guide.

The next step is to Enumerate through your data table, you dont need LINQ, you can do this :-

  foreach(DataRow row in Dataset.Tables[0].Rows)
    {
        row["YourColumn"] = YOURVALUE;
    }

One this is finished, you need to call the Update() method of yout Data Adapter like so :-

DataAdapter.Update(dataset.Tables[0]);

What happens here, is the Data Adapter calls the Update command and saves the changes back to the database.

Please Note, If wish to ADD new rows to the Database, you will require am INSERT Command for the Data Adapter.

This is very roughly coded out, from the top of my head, so the syntax may be slightly out. But will hopefully help.

_{Sunday, April 28, 2013 10:42 AM | 1 vote}

Hi Abhijeet,
there are some forums dedicated specially to database related questions. You'll find the links to those forums at the beginning of our forum here. I am sure, there you might get a wider response to your question.

BTW, if I understand you right, you'll try to create a completely new datatable within your database by filling your dataset with a new table object created from your dataGridView1.datasource and by using the oledbAdapter.Update() command automatically created by your OleDbCommandBuilder object!?

A CommandBuilder might be a pretty simple way of creating the appropriate commands for updating or changing already existing datatables. The CommandBuilder creates the commands according to the pattern he gets from the mandatory select command. But I think, it is not suitable to create a completely new datatable within your database.

Kind regards
wizend

_{Sunday, April 28, 2013 11:48 AM}

Thanks for your reply wizend! 

yaa wizend you got it right. I am trying to create a complete new table with datatable! but the changes are just not reflecting back. But if i try to insert the data without appending that primary column to the datatable  it works well. 

_{Sunday, April 28, 2013 12:36 PM}

The point where I'm in doubt about your explanation is, whether you intend to create a new additional datatable or if you modify your original datatable to something 'new'.

But from what I remember about CommandBuilder objects, it might be the wrong tool anyway. I just refreshed my recollections by refering to the respective msdn library entry and it says:

"... Automatically generates single-table commands that are used to reconcile changes made to a DataSet with the associated SQL Server database.
...
..., you can create a SqlCommandBuilder object to automatically generate Transact-SQL statements for single-table updates if you set the SelectCommand property of the SqlDataAdapter.
..."

So if you have more than one single table Command Builder drops out. If you want to modify your original datatable thoroughly, that means, including the primary key or unique column, it drops out, either. Because it depends heavily on this unique identifier.

wizend