none
How do you copy a DataTable to a new database table? RRS feed

  • Question

  • I've spent hours searching for what must be the simplest task imaginable with ADO.NET but can't seem to find a solution.

    I have a database connection object: Dim oConn As Object

    This can be an SqlConnection, OleDbConnection or OdbcConnection

    I also have an ADO.NET DataTable: Dim oTable As DataTable

    The DataTable is the result of an ad-hoc query and some business logic.

    What's a decent way to save the DataTable to a brand new table in the database using a generic approach that will work for the three types of connections?

    SqlBulkCopy seems to fit the bill nicely but OleDb and Odbc don't seem to have it.

    Thanks.

    Saturday, August 6, 2011 12:01 PM

All replies

  • ADO.NET is designed to execute SQL queries (DML or DDL) and as it's a generic interface it knows nothing about the schema. While you can query the schema it can't change it.

    There are scripting tools in SQL Server Management Studio that can copy tables.


    __________________________________________________________________
    William Vaughn
    Author, Mentor, Trainer, MVP
    Beta V Corporation
    William Vaughn's blog

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    “The Owl Wrangler” a fantasy fiction novel

    Please click the Mark as Answer button if a post solves your problem!

    Saturday, August 6, 2011 7:12 PM
    Moderator
  • I think William explained it well. Out of curiosity, why do you want to store such tables? Messing with database structure isn't something usual.
    Miha Markic [MVP C#] http://blog.rthand.com
    Saturday, August 6, 2011 7:27 PM
  • Thanks for the feedback.

    I'm porting a VB6 / ADODB application to .NET. It is an ad-hoc tool for accountants (from a wide variety of firms) that allows them to connect to a database (usually a local MS Access file but more and more often, copies of production SQL Server, DB2 and Oracle databases) and perform a number of different business analytics functions.

    A number of the functions (statistical sampling, digital analysis with Benford's law , specialized / fuzzy duplicate item detection, gap detection in account and check numbers) can't be done with an SQL query so I have to do them myself. The results end up in a DataTable (it used to be a recordset in the VB6 version) and they need to be saved as a new table back to the database.

    In VB6, I used ADODB to create the new table based on the schema info I could extract from the recordset and then wrote each record of the recordset to the new table.

    I was hoping there was something like DataTable.SaveAsTable("NameOfTheNewTable") as the VB6 code was quite lengthy and performance challeneged.

    As far as .NET knowing nothing about the schema, this is confusing me as I have been using GetSchema extensively to discover information about a database and its tables without having to open the tables. I guess that the GetSchema data is passed unaltered from the provider to my app (GetSchema returns a different set of columns for different providers it seems but they all seem to return TABLE_NAME and TABLE_TYPE) but ADO doesn't use any of this info itself although I would have imagined it would need to know something about the schema in order to be able to manage the DataTables it creates.

    Thanks.

    Saturday, August 6, 2011 9:52 PM
  • I guess most applications don't modify database structure and hence this functionality wasn't included into ado.net. That said creating such functionality in your case shouldn't be that hard.

    Basically you can iterate columns from your datatable and concatenate a proper SQL command, like:

    CREATE TABLE [dbo].[TableName](
    	[FirstColumn] [int],
    	[Secondcolumn] [varchar](255),
        ....
    )
    


    Here is some code for you that might help you

    static void Main(string[] args)
        {
          DataTable table = new DataTable();
          table.Columns.Add("Tubo", typeof(int));
          table.Columns.Add("Bumbar", typeof(string));
    
          var columnSql = from c in table.Columns.Cast<DataColumn>()
                  select string.Format("[{0}] {1}", c.ColumnName, GetSqlType(c.DataType));
          string sql = string.Format("CREATE TABLE {0} ({1})", table.TableName, string.Join(",\n", columnSql));
          Console.WriteLine(sql);
          Console.ReadLine();
          
        }
    
        private static string GetSqlType(Type type)
        {
          if (type == typeof(int))
            return "int";
          else if (type == typeof(string))
            return "nvarchar(max)";
          else
            return "ooops";
        }
    


    You'd use sql for SqlCommand to execute it. Note that you'd have to create different output for different databases if necessary...

    HTH


    Miha Markic [MVP C#] http://blog.rthand.com
    Sunday, August 7, 2011 9:47 AM
  • Thanks. I'm now trying to figure out how to retrieve and specify the max length for a character field as well as the precision and scale for numeric fields in the DDL CREATE TABLE SQL I'm generating. This information isn't available in the DataColumn type. The info is in the Columns collection of the connection's GetSchema. Of course, each provider returns a different set of columns for GetSchema so I'm forced to come up with a generic representation of the columns schema for each of the connection types I need to support (Sql, OleDb, Odbc and eventually Oracle and DB2)

    Is there a straight forward, connection type independent way of determining what the precision and scale are for a given DataColumn?

    The way I'm doing it now requires loading the columns collection via the connection object's GetSchema and then creating an alternate DataTable with a standard set of column names and then populating it with the appropriate values for the Sql, OleDb or Odbc connection being used.

    My app uses a database class / layer that hides the specifics of the underlying connection types. I'm trying to get it to work with as wide a variety of data sources a possible. It knows nothing about data it analyzes ahead of time and depends on the user to select the appropriate fields to use for the desired analytical procedure they want to run. and so it has to rely on meta data from the schema.

    Thanks.

    Sunday, August 7, 2011 11:19 AM
  • probably the easiest way to use sql server import and export wizard. you can opt either .net framework data provider for oracle or oracle provider for oledb. http://msdn.microsoft.com/en-us/library/ms141209.aspx

    Thanks,

    Werewolf,


    Just a newbie for everything.
    Thursday, August 11, 2011 5:54 AM
  • Thanks. I was hoping for a generic way of doing it. I have created separate SaveDataTableAsDatabaseTable methods for each type of connection so that they can take advantage of connection specific features that could improve performance over simply issuing a bunch of Insert Into statements.

     

    Thursday, August 11, 2011 6:19 AM