none
Dataset(or datatable) Injection into a MySql Database RRS feed

  • Question

  • I need to inject information from a dataset in bulk or one datatable(within the dataset) at a time to a mysql server. I've been able to create the dataset filled with tables, and just in case created a mirror array of datatables, I'm just not sure how to inject this into the database. I've seen quite a few examples on how to download the dataset, update it, then reupload it to the MySql server, yet I haven't been able to create new tables and inject those into a database. I think my query might be off. Any help on this would be appreciated.

                for (int x = 0; x < bulkTable.Length; x++)
                {
                    try
                    {
                        conn = connectDB(); //mysql connection, connects to the database
                        comm = new MySqlCommand("SELECT * FROM stockdb", conn); //mysql command
                        da = new MySqlDataAdapter(comm); //data adapter
                        da.Update(bulkTable[x]); // update command
                    }
                    catch (Exception e)
                    {
                        fm1.AppendTextToConsole("null"+x+" \n");
                    }
                }

    that's the example code of the datatable injection using an array. I'm planning on simply using just a DataSet if I can bulk upload the entire dataset, instead of one table at a time.
    Wednesday, April 9, 2008 10:59 AM

All replies

  • After changing the code to this:

                for (int x = 0; x < bulkTable.Length; x++)
                {
                    try
                    {
                        conn = connectDB();
                        comm = new MySqlCommand(String.Format("SELECT * FROM `{0}|Daily`",bulkTable[x].TableName), conn);
                        da = new MySqlDataAdapter(comm);
                        da.Update(bulkTable[x]);
                    }
                    catch (Exception e)
                    {
                        fm1.AppendTextToConsole(e.ToString());
                    }
                }

    Now I'm just getting this error:

    240System.InvalidOperationException: Update requires a valid InsertCommand when passed DataRow collection with new rows.
       at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
       at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
       at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
       at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
       at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
       at StockProjectAdmin.Admin.bulkInjectionFolder(Object folderPath) in C:\Documents and Settings\sam\My Documents\Visual Studio 2008\Projects\StockProjectAdmin\Admin.cs:line 240System.NullReferenceException: Object reference not set to an instance of an object.
       at StockProjectAdmin.Admin.bulkInjectionFolder(Object folderPath) in C:\Documents and Settings\sam\My Documents\Visual Studio 2008\Projects\StockProjectAdmin\Admin.cs:line 238

    Any ideas?
    Wednesday, April 9, 2008 11:51 AM
  • you need to create a dataset and add all your tables to the new dataset (dataset can have many datatables). then call

     

    da.Update(DataSet);

     

    Wednesday, April 9, 2008 11:58 AM
  •  dagofthedofg wrote:
    After changing the code to this:
    Any ideas?

     

    Yes, in addition to what Bermil said, you should delete your first duplicate post.

    Wednesday, April 9, 2008 12:02 PM
  • And I did that:
                     try
                    {
                        DataSet ds = new DataSet();
                        ds.Tables.AddRange(bulkTable);
                        conn = connectDB();
                        comm = new MySqlCommand(String.Format("INSERT INTO `{0}|Daily` (date, open, high, low, close, volume) VALUES('{0}', '{1}', '{2}', '{3}', '{4}', '{5}')", bulkTable[0].TableName), conn);
                        da = new MySqlDataAdapter(comm);
                        da.Update(ds);
                    }
                    catch (Exception e)
                    {
                        fm1.AppendTextToConsole(e.ToString());
                    }
    And here's what I got:

    System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.
       at System.Text.StringBuilder.AppendFormat(IFormatProvider provider, String format, Object[] args)
       at System.String.Format(IFormatProvider provider, String format, Object[] args)
       at System.String.Format(String format, Object arg0)
       at StockProjectAdmin.Admin.bulkInjectionFolder(Object folderPath) in C:\Documents and Settings\sam\My Documents\Visual Studio 2008\Projects\StockProjectAdmin\Admin.cs:line 238

    And btw, that second post wasn't a duplicate post, it was a revision I made to my code that I updated for. Read the code next time.
    Wednesday, April 9, 2008 12:49 PM