[RESOLVED] using dataAdapter with dataTable.

[abhijit]

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 int, 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.Int,
                                  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)
                {
                    command.Parameters("studentId").Value = int.Parse(row[0].ToString());
                    command.Parameters("studentName").Value = row[1].ToString();
                    command.ExecuteNonQuery();
                }    
            }
            catch(Exception)
            {
                throw;
            }
            
        }

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

[jmcilhinney]

So, basically you're asking how to copy data from one DataTable to another, correct? If so, there are various ways. You can simply loop through the Rows collection and get the data you want, Adding rows to the other table as you go. You can use a DataTableReader to read the table. You can use a LINQ query to read the desired data and either loop through it and populate an existing table or have it generate a new one. You can also generate a new DataTable from a DataView based on the first table.

[abhijit]

So, basically you're asking how to copy data from one DataTable to another, correct? If so, there are various ways. You can simply loop through the Rows collection and get the data you want, Adding rows to the other table as you go. You can use a DataTableReader to read the table. You can use a LINQ query to read the desired data and either loop through it and populate an existing table or have it generate a new one. You can also generate a new DataTable from a DataView based on the first table.

jmc,
So far I have tried the following piece of code to avoid adding each row.

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;
    }
}

Something's not all cop with this code though, as it creates rows but all the column values are Null.

[abhijit]

I have fixed the above code by making a few changes. This is now resolved.

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,
                SourceColumn="StudentId"
                };
            command.Parameters.Add(op1);
            var op2 =
            new OracleParameter
                {
                    ParameterName = "studentName",
                    OracleDbType = OracleDbType.Varchar2,
                    Size = 50,
                    Direction = System.Data.ParameterDirection.Input,
                    SourceColumn="StudentName"
                };
            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;
        }
    }

I had to add a source column property to the parameter.