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:
- 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.
="1.0"="utf-8"
<configuration>
<connectionStrings>
<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>
<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?
- 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)
{
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 - 9th 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.