Insert Datatable into Oracle using OracleDataAdapter

Question

I need to insert specific rows from DataGridView into Oracle DB table. What I do is to set InsertCommand for OracleDataAdapter, create a new empty DataTable with same column names and fill OracleDataAdapter with It. Then I loop over rows in DataGridView, add desired rows to DataTable and then call da.Update() method - but It doesn't work, I get "Value cannot be null. Parameter name:command" error at same line - da.Fill()...This is my code as described:

private OracleDataAdapter da = new OracleDataAdapter();
private void Inicialize_adapter()
{
    OracleConnection conn = new OracleConnection(conn_string);
    conn.Open();

    OracleCommand insert = new OracleCommand("Myschema.InsertToTable", conn);

    insert.Parameters.Add("ID_IN", OracleDbType.Decimal, 4, "ID");
    insert.Parameters.Add("ID_FK_IN", OracleDbType.Decimal, 4, "ID_FK");
    insert.Parameters.Add("SERIAL_IN", OracleDbType.Decimal, 4, "SERIAL_NO");

    da.InsertCommand = insert;
    da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
    conn.Close();
}

And code in my ButtonSave_Click:

...

Inicialize_adapter();

DataTable dt_Test = new DataTable();
DataColumn dc = new DataColumn("ID", typeof(int));
dt_Test.Columns.Add(dc);

dc = new DataColumn("ID_FK", typeof(int));
dt_Test.Columns.Add(dc);

dc = new DataColumn("SERIAL_NO", typeof(int));
dt_Test.Columns.Add(dc);

var sequence = New_id(1); // this get's me next sequence in DB
int increase_seq = 0;

foreach (DataGridViewRow row in MyDGV.Rows)
{
    if (row.Cells[0].Value == "Added")
    {
       DataRow newRow = dt_Test.NewRow();

       newRow[0] = sequence+increase_seq;
       newRow[1] = 2";
       newRow[2] = row.Cells[1].Value;

       dt_Test.Rows.Add(newRow.ItemArray);
       ++increase_seq;
     }
 }
 //Fill Adapter -error here
 da.Fill(dt_Test);

 da.Update(dt_Test);

  ...

I've tried also other options like Array Binding, but that didn't produce good results, so I wish I could get It working this way as It's simpler. Does anybody know what could be wrong in my code ? Thanks for help in advance !!

Answer 1

Update:

First: To get the selected Rows from your DataGridView you could do that:

    foreach (DataGridViewRow dr in MyDGV.Rows)
    {
        if (dr.Selected)
        {
            //Add to DataTable
        }
    }

    //Or even easier:

    foreach (DataGridViewRow row in MyDGV.SelectedRows)
    {
        //Add to DataTable
    }

The other part is the da.Fill()/da.Update(). I'm not sure if the "Myschema.InsertToTable" is a stored procedure (I haven't much experience with that) but I'am assuming it. So here are some ideas on how to insert a DataTable to an Oracle-Database with stored procedures: OracleDataAdapter

So now I hope that I got it right and you can solve your problem (:

Old answer:

As far as I can tell this looks like you are trying to insert a DataRow with more Columns than you fill (Column-Name: command) and your DataBase doesn't accept null-value for that DataColumn. So you have eighter to change the DataBase to allow null-value on that Column or you give that column a value before insert it:

foreach (DataGridViewRow row in MyDGV.Rows){
if (row.Cells[0].Value == "Added")
{
   DataRow newRow = dt_Test.NewRow();

   newRow[0] = sequence+increase_seq;
   newRow[1] = 2";
   newRow[2] = row.Cells[1].Value;
   newRow["command"] = "someValue";

   dt_Test.Rows.Add(newRow.ItemArray);
   ++increase_seq;
 }

}

Also have you tryed to use DataBinding? In my opinion this should make it all alot easier.

Edit:

OK then I got it wrong. Maybe you comand is wrong, but when we look at it with Databinding it should work. Here is a Tutorial

What you Need is a DB-Connection, a DataAdapter and a DataSet and a BindingSource.

You already got the DataAdapter (OracleDataAdapter da) and the Connection (OracleConnection conn) but you don't use it correctly.

1. Initialise DB-Connection

   OracleConnection conn = new OracleConnection(conn_string);

2. Initialise DataAdapter

   string Query = "SELECT * FROM YourTable"; OracleDataAdapter da = new OracleDataAdapter(Query, conn);

3. Fill DataSet

   DataSet ds; da.Fill(ds, "Tablename");

4. Bind the Data to BindingSource

   BindingSource bs = new BindingSource(ds, "Tablename");

5. Now you can populate the DataGridView

   DataGridView dgv; dgv.DataSource = bs;

After that you can work with the Data, edit them in the DataGridView etc. In the and you could generate the Insertcommands etc. and Update the Data (Changed Data, new Data, whatever you want).

bs.EndEdit();

OracleCommandBuilder ocb = new OracleCommandBuilder(da);
da.UpdateCommand = ocb.GetUpdateCommand(true);
da.InsertCommand = ocb.GetInsertCommand(true);
da.DeleteCommand = ocb.GetDeleteCommand(true);

da.Update(ds, "Tablename");

This is just a short overview, you need to look at it more detailed, i.e. it's important to make a "bs.EndEdit()" before doing the da.Update() so the changes are taken.

I hope this helps you.

Edit2:

Here is a better viewable code, also I made this with SQLConnection etc. (so some calls maybe have to be changed) but it is the same with Oracle (Did that some time ago):

string Query = "SELECT * FROM YourTable"; 
DataSet ds;  
OracleConnection conn;
OracleDataAdapter da; 
BindingSource bs;
DataGridView dgv = new DataGridView();

private void GetDataAndFillDataGridView()
{
    conn = new OracleConnection(conn_string);
    OracleDataAdapter da = new OracleDataAdapter(Query, conn);
    da.Fill(ds, "Tablename");
    bs = new BindingSource(ds, "Tablename");
    dgv.DataSource = bs;
}

private void buttonSave_Click(object sender, EventArgs e)
{
    bs.EndEdit();

    OracleCommandBuilder ocb = new OracleCommandBuilder(da);
    da.UpdateCommand = ocb.GetUpdateCommand(true);
    da.InsertCommand = ocb.GetInsertCommand(true);
    da.DeleteCommand = ocb.GetDeleteCommand(true);

    da.Update(ds, "Tablename");
}