Write A DataTable with variable columns into a database table

Question

This could have been much easier if I used SqlConnection. But now I have an OleDbConnection and I would like to copy an entire DataTable into a database table. The DataTable can have different columns every time. So I would like only the matching columns to be inserted.

How can I achieve this without iterating through my DataTable columns all the time and building an INSERT query according to that?

having a structure that keep changing you wont be able to do it without some sort of iteration — Franck, Jan 20 '15 at 14:35
@Franck true. but how can such thing exist with `SqlBulkCopy` but if I use `OleDb` I have to go through things brick by brick? — disasterkid, Jan 20 '15 at 14:38
@SeanLange the connection string to the database has a `provider` value that is not supported by `SqlConnection`. — disasterkid, Jan 20 '15 at 14:44
Well if you are connecting to sql server can you change the application so you have a connection string that doesn't force you to use an outdated technology. Or maybe add a second connection string to the config file? — Sean Lange, Jan 20 '15 at 14:46
@SeanLange I wouldn't call `OleDb` outdated necessarily. I am willing to provide support for other types of databases in the future. I would rather keep a generic solution. And adding a separate connection string and save it somewhere else for rainy days is not something I would like to have on my program. — disasterkid, Jan 20 '15 at 14:53
OLEDB has been deprecated for some time now. sql 2012 is the last version that supports it. Not sure what you mean about saving a connection somewhere for rainy days but I understand about having 2 connection strings but they would be right next to each other in the config file. — Sean Lange, Jan 20 '15 at 14:57
@SeanLange I did not know about that. Thanks. But is there a solution for my problem for now? — disasterkid, Jan 20 '15 at 15:05

score 4 · Answer 1 · edited May 23 '17 at 11:43

Pedram, sure you can. Go ahead and use GetOleDbSchemaTable() to get the details.

This is the way you proceed,

DataSet output = new DataSet();
using (OleDbConnection conn = new OleDbConnection(strConn))
{
    conn.Open();

    DataTable schemaTable = conn.GetOleDbSchemaTable(
        OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

    // e.g. Rows in SchemaTable are Sheets in Xlsx file.
    foreach (DataRow schemaRow in schemaTable.Rows)
    {
        //... 
        string sheet = schemaRow["TABLE_NAME"].ToString();
        var select = "SELECT * FROM [" + sheet + "]";
        OleDbCommand cmd = new OleDbCommand(select, conn);
        cmd.CommandType = CommandType.Text;
        DataTable outputTable = new DataTable(sheet);
        output.Tables.Add(outputTable);
        new OleDbDataAdapter(cmd).Fill(outputTable);
    }
}

Later on when you got your DataSet you can get your DataTables out of it using myDataSet.Tables, however they come in a DataTableCollection type which only support Foreach and not the IEnumerable() then you could do LINQ queries on it without the need for iteration. .NET developers haven't decided to implement this due to multiple reasons you can read extensively over here.

However there is a very easy workaround and that's Casting it. So if you don't want to iterate over the collection and cherry pick the entries you want go ahead and use the following syntax in Method Syntax:

var foo = dataSet.Tables.Cast<DataTable>().Where(
            t => t.TableName == "The Table I want");

or in the Query syntax LINQ form as,

var bar = from DataTable t in dataSet.Tables
            where t.TableName == "The table I want" 
            select t;

Hope I understood you right though. I spent a fair bit of time figuring this out myself since I was curious.

Update

Bulk Copy

If you want to copy your whole table over OleDbConnection to implement something like SqlBulkCopy does in SqlConnection you'll be looking to use IRowsetFastLoad. You didn't mention if you're using Ado.Net or just the pure OleDb. If it's the first option and you're using C# unfortunately IRowsetFastLoad is not implemented in this platform and only on C++. In this situation you are looking at iterating and grinding your teeth.

On the other hand if SqlBulkCopy is an option, I am pretty sure you're already all over it. If not, have a quick look at the following article. It clears lots of things up.

Using SqlBulkCopy to efficiently copy table from Access to SQL Server

I had a quick Googling and came across solutions such as using adapter.Update(), however I haven't used it this way before and won't be able to comment on it without testing.

DbDataAdapter.Update Method (DataTable)

Probably it worth having a look. Sorry, for the half way answer though. I had to go through your comments to fully understand what you want to accomplish.

Hey Mehrad! The code snippet you provided looks very elegant. However, I already the source `DataTable` and the name of the destination table. What I would like to do is to INSERT all the rows in `DataTable` into the destination table. Do you think there is somewhere in your code in which you have provided the answer that I overlooked? — disasterkid, Jan 21 '15 at 10:01
I think a solution like http://stackoverflow.com/questions/9075159/how-to-insert-a-data-table-into-sql-server-database-table will somehow do that job. Don't you think? — disasterkid, Jan 21 '15 at 10:10
I'd say the suggested link is also an SQL solution which could use the `SqlBulkCopy` instead if you went that way. I also updated my answer. Please have a look and let us know how you're doing. — Mehrad, Jan 21 '15 at 11:07
But `SqlBulkCopy` works only with an `SqlConnection` and not an `OleDbConnection`. — disasterkid, Jan 21 '15 at 11:08
I am aware of that, and was talking about the suggestion two comments above on another SO question which was suggesting a SQL based answer for a similar question. — Mehrad, Jan 21 '15 at 11:10

Write A DataTable with variable columns into a database table

1 Answers1

Bulk Copy