locked
How to Insert rows from a Dataset Table to a Table in a ConnectedDatabase RRS feed

  • Question

  • Hello,

    I have read the FAQ and tested this code that works nice:

    First, we have to iterate through the DataTable.Rows collection property. Then,

    1.       If the target and source DataTable have the same schemas, in the iteration, we can just call DataTable.ImportRow(DataRow row) to add and fill a new record of data.

                foreach (DataRow row in Table1.Rows)

                {

                    Table2.ImportRow(row);

                }

    2.       If the target and source DataTable do not the same schemas, in the iteration, we have to call NewRow() on the target table, fill the its content, and call DataTable.Rows.Add() to add it.

                foreach (DataRow row in dt.Rows)

                {

                    DataRow newRow = Table2.NewRow();

                    newRow["MyTable2Column"] = row["MyTable1Column"];

                    Table2.Rows.Add(newRow);

                }

     My question is:

    How to insert the same rows in a Disk Table (under any supported SQL NET provider) instead of inserting in a in-memory Table ?

    My output schema exists and is working properly with identical structure as the Dataset Table.

    Thanks a lot,

    HBB

     


    HBB
    Saturday, September 3, 2011 1:34 AM

Answers

  • What you would do is create a .CommandText with parameters in it, then loop the .Rows collection of your DataTable to set the parameters in your .CommandText to values extracted from the current Row.  You would then .ExecuteNonQuery for each Row of the DataTable.  So your code may look something like this ...

    DataTable dt = new DataTable();
                    
    // code to populate DataTable
                    
    SqlConnection cnnSql = new SqlConnection();
    
    SqlCommand cmd = new SqlCommand();
    cmd.CommandType = System.Data.CommandType.Text;
    cmd.CommandText = "INSERT Region (RegionID, RegionDescription) VALUES (@RegionID, @RegionDescription)";
    cmd.Connection = cnnSql;
    
    cnnSql.Open();
    
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        cmd.Parameters.AddWithValue("@RegionID", int.Parse(dt.Rows[i]["RegionID"].ToString()));
        cmd.Parameters.AddWithValue("@RegionDescription", dt.Rows[i]["ReqionDescription"].ToString());
        cmd.ExecuteNonQuery();
    }
                                    
    cnnSql.Close();
    
    

     

    Hope that helps!!

     


    Brent Spaulding | Access MVP
    • Marked as answer by HBB_BA Wednesday, September 7, 2011 1:24 AM
    Tuesday, September 6, 2011 8:09 PM

All replies

  •  My question is:

    How to insert the same rows in a Disk Table (under any supported SQL NET provider) instead of inserting in a in-memory Table ?

    Hello,

    I'm not very sure what's meaning of Disk Table that you provided. Based on my understanding, did you want to save the record into Database, but its scheme didn't match the table. Perhaps like you mentioned previously, you want to add a new column for the inserting data. If I'm correct, I think this idea makes sense, but personally I didn't suggest you to alter your table of Database. Normally, we aforehand reserved more columns for each table and prevented this question from happening. We can use these reserved columns to be satisfied with our new need.

    If I misunderstood, please feel free to elaborate your question kindly.

    Hope this helps.

    Thanks,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, September 5, 2011 6:41 AM
  • Hello Larcolais,

    Thanks for your answer.

    This is the detail:

    The goal of the console module is to read a txt tab delimited file, analyze it, split and put data in a Database table.

    The first part is done without trouble (I mean the conversion of delimited txt to an in-memory Table in a newly created Dataset)

    The second part is the populating of an existing Table a a connected database (of known schema) with the data obtained in the Dataset Table.

    I am trying to use a DataAdapter for this but have trouble designing the Insert syntax or using the CommandBuilder options to build it.

    In short: I need a method to insert a known structure of records (residing in a Dataset) into a known structure of a Table (residing in a Connected DB).

    Thanks again for your help, and have a nice day,

     


    HBB
    Monday, September 5, 2011 11:26 AM
  • The following MS article may help you out a bit ...

    http://msdn.microsoft.com/en-us/library/ms233812(v=VS.80).aspx#Y350 


    Brent Spaulding | Access MVP
    Monday, September 5, 2011 2:12 PM
  • Thanks very much, Brent.

    I will study this article, that seems to fit well, and let you know.

    Have a nice day,


    HBB
    Monday, September 5, 2011 2:56 PM
  • Hello

    The example mentionned in the article has:

    System.Data.SqlClient.SqlConnection sqlConnection1 =
        new System.Data.SqlClient.SqlConnection("YOUR CONNECTION STRING");

    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
    cmd.CommandType = System.Data.CommandType.Text;
    cmd.CommandText = "INSERT Region (RegionID, RegionDescription) VALUES (5, 'NorthWestern')";
    cmd.Connection = sqlConnection1;

    sqlConnection1.Open();
    cmd.ExecuteNonQuery();
    sqlConnection1.Close();

    What I am looking for is a method to insert a row whose data is provided by a Dataset instead of constant values like:

    VALUES (5, 'NorthWestern')

    In other words to read from an in memory Dataset Table and insert into a DB Table

    Perhaps there is a way to use a row collection as argument of the "Insert"

    Any ideas ?

    Thanks very much


    HBB
    Tuesday, September 6, 2011 11:35 AM
  • What you would do is create a .CommandText with parameters in it, then loop the .Rows collection of your DataTable to set the parameters in your .CommandText to values extracted from the current Row.  You would then .ExecuteNonQuery for each Row of the DataTable.  So your code may look something like this ...

    DataTable dt = new DataTable();
                    
    // code to populate DataTable
                    
    SqlConnection cnnSql = new SqlConnection();
    
    SqlCommand cmd = new SqlCommand();
    cmd.CommandType = System.Data.CommandType.Text;
    cmd.CommandText = "INSERT Region (RegionID, RegionDescription) VALUES (@RegionID, @RegionDescription)";
    cmd.Connection = cnnSql;
    
    cnnSql.Open();
    
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        cmd.Parameters.AddWithValue("@RegionID", int.Parse(dt.Rows[i]["RegionID"].ToString()));
        cmd.Parameters.AddWithValue("@RegionDescription", dt.Rows[i]["ReqionDescription"].ToString());
        cmd.ExecuteNonQuery();
    }
                                    
    cnnSql.Close();
    
    

     

    Hope that helps!!

     


    Brent Spaulding | Access MVP
    • Marked as answer by HBB_BA Wednesday, September 7, 2011 1:24 AM
    Tuesday, September 6, 2011 8:09 PM
  • Hello Brent,

    This is the link I could not find in books or articles. I mean the way of linking the Insert syntax with the @MyName operator that links the source from dt.Rows to the destination in the Insert syntax.

    cmd.CommandText = "INSERT Region (RegionID, RegionDescription) VALUES (@RegionID, @RegionDescription)";

    cmd.Parameters.AddWithValue("@RegionID", int.Parse(dt.Rows[i]["RegionID"].ToString()));
    cmd.Parameters.AddWithValue("@RegionDescription", dt.Rows[i]["ReqionDescription"].ToString());

    Now is very easy.

    Thanks very much and have a nice evening!

     

     

     


    HBB
    Tuesday, September 6, 2011 10:53 PM
  • You're welcome!  Good luck with your project HBB!


    Brent Spaulding | Access MVP
    Wednesday, September 7, 2011 2:12 AM
  • Don't forget to include a Parameters.Clear() in that loop. And, you can make it even more succinct by getting rid of the parsing and .ToString() stuff.

    for (int i = 0; i < dt.Rows.Count; i++)
    {
        cmd.Parameters.Clear();
        cmd.Parameters.AddWithValue("@RegionID", dt.Rows[i]["RegionID"]);
        cmd.Parameters.AddWithValue("@RegionDescription", dt.Rows[i]["ReqionDescription"]);
        cmd.ExecuteNonQuery();
    }
    
    

     


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Friday, September 9, 2011 4:20 PM
  • Or, better yet:

    foreach (DataRow row in dt.Rows)
    {
        cmd.Parameters.Clear();
        cmd.Parameters.AddWithValue("@RegionID", row["RegionID"]);
        cmd.Parameters.AddWithValue("@RegionDescription", row["ReqionDescription"]);
        cmd.ExecuteNonQuery();
    }
    
    
    

     


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Friday, September 9, 2011 4:22 PM
  • Definately good tips!  Thanks for jumping in Bonnie!
    Brent Spaulding | Access MVP
    Friday, September 9, 2011 4:54 PM
  • Always happy to throw in my 2 cents, Brent!  ;0)
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, September 10, 2011 1:23 AM