Simple data export

• Download source - 9.4 KB

Introduction

BImport is a trivial console application that exports data from a set of tables in database 'a' to an identical set of tables in database 'b'.

Background

Once again, my laziness got the better of me. I've been working on an application where it is useful to have live data to work with without actually using the live database. This program allows me to update my development database with the current data housed in live, without needing to fire up SQL Server: I just double-click on the EXE in Explorer and, bam, it's done. Simple.

Using BImport

There are two parts to BImport:

1. Populate the config file so that BImport knows where to go and what to do.

Here is a sample App.config which, upon program compilation, will be renamed as BImport.exe.config, and will live in the same folder as BImport.exe.

XML

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <!-- Require source and target connection strings -->
        <add name="SOURCE" connectionString="Persist Security Info=True;
           User ID={USER_ID};PASSWORD={PASSWORD};Data Source={DATA_SOURCE};
           Initial Catalog={INITIAL_CATALOG};"
           providerName="Sql.Data.SqlClient" />
        <add name="TARGET" connectionString="Persist Security Info=True;
           User Id={USER_ID};Password={PASSWORD};Data Source={DATA_SOURCE};
           Initial Catalog={INITIAL_CATALOG};" 
           providerName="Sql.Data.SqlClient" />
    </connectionStrings>
    <appSettings>
        <!-- Tables that require 'set identity_insert [tablename] on' 
                should set value  to true -->
        <add key="Table1" value="true" />
        <add key="Table2" value="true" />
        <add key="Table3" value="false" />
        <add key="Table4" value="true" />
    </appSettings>
</configuration>

There are two parts to the config file; the first tells BImport what the connection strings are for the source and target databases; the second, the list of tables for which the data should be copied. Note that you can mark each table as needing to use 'set identity_insert [tableName] on', where this is required. The reason for this is so that you can decide which tables to copy: in my case, I don't need to copy any of the reference tables, so why bother?

2. The meat and potatoes of the program are (assuming you have provided the correct detail in the config file):
• Loop through each of the keys in the appSettings section of the config file, and
• Grab the data into a DataTable from the source database.
• Open a connection to the target database.
• If the source DataTable has rows...
• Truncate the target table.
• Create StringBuilders to hold:
• The INSERT statement
• The list of fields
• The parameters
• Finally, for each row of data found, execute an INSERT statement.

Getting the table names:

I wanted a really quick 'n dirty way of grabbing the data from the config file without having to think about multiple values hanging off the same key. You'll need to ensure that a reference to System.Configuration has been added, and it is then as simple as:

foreach (string value in ConfigurationManager.AppSettings)
{
    // Get the name of the table.
    string tableName = value.ToString();
    ...

That's it: if I wanted the actual value, I could do ConfigurationManager.AppSettings[value], which will give me, in this case, the bool that denotes whether or not to use identity_insert.

Now that I have the table names, I can process each one in turn.

Using a helper method, I get the source data into a DataTable, and then only proceed for this table if there are any rows to process.

If there are rows, I then use another helper method to first determine whether or not I should use identity_insert, and then truncate the table prior to inserting the source data.

We can then step through the DataTable columns and begin to build the INSERT statement.

foreach (DataColumn dataColumn in dataTable.Columns)
{
    fields.Append(dataColumn.ColumnName);
    parameters.Append("@" + dataColumn.ColumnName.ToLower
                (CultureInfo.InvariantCulture));
    ...

Then, loop through all of the DataTable rows for each table, adding the values to the parameters, and then executing the query. Voila!

foreach (DataRow dataRow in dataTable.Rows)
{
    SqlCommand command = new SqlCommand(sql.ToString(), connection);

    foreach (DataColumn dataColumn in dataTable.Columns)
    {
        command.Parameters.AddWithValue("@" + 
          dataColumn.ColumnName.ToLower(CultureInfo.InvariantCulture), 
          dataRow[dataColumn.ColumnName]);
    }
    ...

and then:

command.ExecuteNonQuery();

Note that there are many examples of the above code littered across the inter-web, so feel free to implant any that you feel more comfortable with; this, though, appears to be a simple and straightforward way of carrying out the task.

Caution: I have only used this with 10 tables, of which the largest had about 2000 rows, and have not tested it with more tables and larger datasets: you'll need to satisfy yourself that the program can cope with more tables/larger volumes in a timely fashion.

Finally, the number of rows exported will be displayed in the console. That's it. Done. Press any key and use the data.

Conclusion

Whilst this could be done in a variety of ways, this worked well in my situation; perhaps, it could do with being wrapped in a transaction, and could surely benefit from some decent exception handling, but that is for you: for the moment, this works for me, and does its job each and every time.

BImport is a simple application for a simple task, and I have coded it as such: it's taken longer to put the article together!

History

• Version 1.0 - 9^th October, 2008

This is very much a first and last release: this is a trivial application, and carries out one simple task only: if you do see where useful changes could be made, please let me know, and I'll update the article and acknowledge your contribution.

Copyright © 2008, Mark Merrens.