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
– Franck, Commented Jan 20, 2015 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
– disasterkid, Commented Jan 20, 2015 at 14:38
@SeanLange the connection string to the database has a provider value that is not supported by SqlConnection. — disasterkid
– disasterkid, Commented Jan 20, 2015 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
– Sean Lange, Commented Jan 20, 2015 at 14:46

Community · Accepted Answer · 2017-05-23 11:43:30Z

4

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.

edited May 23, 2017 at 11:43

CommunityBot

11 silver badge

answered Jan 20, 2015 at 23:07

Mehrad

4,2336 gold badges46 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

disasterkid Over a year ago

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 Over a year ago

I think a solution like stackoverflow.com/questions/9075159/… will somehow do that job. Don't you think?

disasterkid Over a year ago

Sorry, I am not sure about that. Should do some tests.

Mehrad Over a year ago

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.

disasterkid Over a year ago

But SqlBulkCopy works only with an SqlConnection and not an OleDbConnection.

|

Collectives™ on Stack Overflow

Write A DataTable with variable columns into a database table

1 Answer 1

Bulk Copy

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Bulk Copy

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related