using DataAdapter to populate table

Question

I have a disconnected dataTable that contains a few records.

I am using the following function to get the dataTable.

static System.Data.DataTable ReadSetUpTable(string queryStr,SqlConnection sc)
{
    try
    {
        var command = new SqlCommand()
                          {Connection = sc, CommandText = queryStr};
        var dataAdapter = new SqlDataAdapter() {SelectCommand = command};
        var dataTable = new System.Data.DataTable();
        dataAdapter.Fill(dataTable);
        return dataTable;
    }
    catch (Exception)
    {
        throw;
    }
} 

No issues so far.

What I want to know is if there's an easy to populate this dataTable into another schema using a different connection string.

For the sake of this post, assume that there is a table with two columns

Create Table Student(StudentId NUMBER(6), StudentName varchar2(50));

I wish to fill this table with the dataTable that I have in the above code.

I could do it using a command Object and an insert statement. For example this code:

static int LoadDataTable(OracleConnection oc, System.Data.DataTable dataTable)
{
    try
    {
        var command = 
            new OracleCommand
            {
                CommandText = "INSERT INTO STUDENT (STUDENTID, STUDENTNAME) VALUES(:studentid, :studentname)",
                CommandType = CommandType.TableDirect,
                Connection = oc
            };
        var op1 = 
            new OracleParameter
            {
                ParameterName = "StudentId",
                Size = 6,
                OracleDbType = OracleDbType.Int32,
            Direction = System.Data.ParameterDirection.Input
            };
        command.Parameters.Add(op1);
        var op2 = 
        new OracleParameter
            {
                ParameterName = "studentName",
                OracleDbType = OracleDbType.Varchar2,
                Size = 50,
                Direction = System.Data.ParameterDirection.Input
            };
        command.Parameters.Add(op2);                                   
       /*
        foreach (var row in dataTable.Rows)
        {
            op1.Value = int.Parse(row[0].ToString());
            op2.Value = row[1].ToString();
            command.ExecuteNonQuery();
        }*/
            foreach (System.Data.DataRow row in dataTable.Rows)
            {
                row.SetAdded();
            }    

            var dataAdapter = new OracleDataAdapter() {InsertCommand = command};
            dataAdapter.Update(dataTable); //This updates the table, but all column values are NULL.

    }
    catch(Exception)
    {
        throw;
    }
} 

Is there a quicker and easier way where I will not have to loop through the records?

Answer 1

In your first block of code, you're setting the SelectCommand. There's also an InsertCommand, UpdateCommand, and DeleteCommand.

These commands also exist on OracleDataAdapter objects... Since the DataTable is endpoint-neutral, all you would need to do is create an OracleCommand to do the insert, set it as the OracleDataAdapter's InsertCommand, and call oracleDataAdapter.Update(dataTable).

Will modify this with more details as I check up on them.

---

A good example of setting the InsertCommand is here. Note that when you add parameters to the command, the last value you pass to .Add( ... ) is the name of the column you're mapping to.

Because you're retrieving the data into the DataTable, but not changing it, you'll need to change the RowState of each row to 'Added' before calling oracleDataAdapter.Update(). You'll need to do something like this:

foreach (DataRow row in dataTable.Rows) {
    row.SetAdded();
}

Let me know if you need more code examples... from the code you've posted, I think you've pretty much got the gist of where I'm going here.

---

Edit

When you create the OracleParameters, you need to set the source column to the name of the column in the DataTable. By default, that's the name returned by the select statment, so:

var op1 = new OracleParameter {
                                ParameterName = "StudentId",
                                Size = 6,
                                OracleDbType = OracleDbType.Int32,
                                Direction = System.Data.ParameterDirection.Input
                                SourceColumn = "StudentId" // If that's what it's called in the DataTable
                              };
command.Parameters.Add(op1);

The AcceptChanges() method is on the DataTable, as is the HasErrors property (it also exists on the DataRows, and DataSets, too).

AcceptChanges() only tells the DataTable that you've handled the updates to the database... the changes are committed to the database when you called oracleDataAdapter.Update(). The reason you call AcceptChanges() is to reset the row states... else the next time you updated the DataTable, you'd be trying to add rows that were already added.

The property AcceptChangesDuringUpdate on the OracleDataAdapter will automatically call AcceptChanges() as part of the update to the database... I don't usually do that, because I'm used to testing the HasErrors property and handling those before calling AcceptChanges()... however I'm reading that AcceptChanges() is called by default on the update now. I'm not familiar with the details of how rows that are in error are handled, if the changes are accepted, or not.