Copy Data from a DataTable to a SQLServer Database using SQLServer Management Objects and SqlBulkCopy

Introduction

This article will focus on transferring an in memory DataTable to SQL Server database. ADO.NET provides numerous well documented methods for creating DataTables with persistence back to their source databases. But in some circumstances, the source database is just that a source. The question remains: "How do you persist your in memory DataTables to a database if you are not intending to update the data to the source database but to a different database? How can this new database and its tables be created to reflect the in-memory DataTable and once that is created, what method can be used to move the data to that table?

Through this article, I intend to demonstrate one way to approach the issue of persisting a DataTable to an unrelated database. I strive to show within this article that through the implementation of SqlServer Management Objects and SQLBulkCopy one possible solution.

Overview

ADO.NET DataTables provide a disconnecting means of working with database information without the cost of an open connection. With a DataTable loaded with source data, the ADO.NET library provides a lot of functionality in working with this data. It also allows you to transform DataTables by providing methods to add columns, compute values and reformat the layout of the table to name a few.

The problem arises though when the DataTable needs to be saved into a new database and its schema is not determined until runtime. While the DataTable acts like a table within a database, there is no direct relation. Because of this, the DataTable cannot be easily placed or transferred directly into a database with intact schema and data. This leads to two basic problems:

1. How to create a table and or database at runtime that reflects the DataTables' schema (namely columns and data types).
2. Once the tables are created, how to best copy the data to the newly created table.

NOTE: This solution only works with .NET 2.0 and Microsoft SQL Server 2005 (not sure of 2000) as the destination database. The source data can come from anywhere as long as it has been put into a datatable.

Set Up

The basic setup that is necessary for this to work is the retrieval of source data into a dataset. This source data can come from any source that can be read by a DataReader or DataAdapter, all that matters is that there is a source DataTable to transfer to the database. For this example, the dataset contains a three column DataTable with the types consisting of int,string and decimal datatypes. While I do not show any specific DataTable data in this article, the data types do pose an interesting problem, but more on that later. As well, I have set up a SqlServer Express 2005 server for my destination database.

Working with SMO

Creating a dynamic database and tables with run-time required changes to the schema poses an interesting problem that does not initially have a clear solution. Traditional methods for handling this would have involved some form of SQL expression and a lot of code. Fortunately, the destination database is a SqlServer 2005 database, which .NET provides an implementation library for dynamically managing the database via SqlServer Management Objects (SMO). I will admit I still do not know a lot about SMO, but they are perfect for solving the issue of creating dynamically changing run-time database environments. I am aware that SMOs can be used to create stored procedures and handle other SQL Server Management issues and that they replace DTO. The great thing about them for the purpose of this article is that they do not require any SQL code implementation.

To get started, the basic imports are as follows:

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer;

I include them here as they are not in the System namespace and can involve some hunting to track them down. Creating a database with SMO is a fairly straight forward process:

//Set destination connection string
string connectionString = YourConnectionString;
SqlConnection Connection = new SqlConnection(connectionString);

//SMO Server object setup with SQLConnection.
Server server = new Server(new ServerConnection(Connection));

//Create a new SMO Database giving server object and database name
Database db = new Database(server, "TestSMODatabase");
db.Create();

This creates a connection string with the server information. Server represents the SMO server object that the new database "TestSMODatabase" will be created in. The create command creates the physical database on the server. Now the SQL Server contains a database into which a table needs to be added:

//Set Database to the newly created database
db = server.Databases["TestSMODatabase"];

//Create a new SMO table
Table TestTable = new Table(db, "TestTable");

At this point, the database server only contains the database as the create method is required to initiate the creation process. Also, the table does not currently contain any schema information. As mentioned above, the column name and type need to be derived from the dataset. This involves first creating an SMO column and then getting the column names and data types ("TestTable" serves as the in-memory DataTable):

//SMO Column object referring to destination table.
Column tempC = new Column();

//Add the column names and types from the datatable into the new table
//Using the columns name and type property
foreach (DataColumn dc in SourceTable.Columns)
{
	//Create columns from datatable column schema
	tempC = new Column(TestTable, dc.ColumnName);
	tempC.DataType = GetDataType(dc.DataType.ToString());

	TestTable.Columns.Add(tempC);
}
//Create the Destination Table
TestTable.Create();

Column has two parameters, the SMO table object and the column name derived from the dataset. The catch here involves getting the appropriate data types as the DataTable's types belong to the System namespace and destination is of type SMO. The GetDataType method converts the types needed by the SMO Column. Currently this only contains a few conversions, but it should provide a good glimpse at the necessary conversion process:

public DataType GetDataType(string dataType)
{
DataType DTTemp = null;

switch (dataType)
{
	case ("System.Decimal"):
		DTTemp = DataType.Decimal(2, 18);
		break;
	case ("System.String"):
		DTTemp = DataType.VarChar(50);
		break;
	case ("System.Int32"):
		DTTemp = DataType.Int;
		break;
}
return DTTemp;
}

The last step in the creation process involves setting up a PrimaryKey column. I discovered this step is optional and is really only necessary for performing relational functions on the table. Even though I did not need it, the code looks like this and goes before the create() method in the above code:

//Create a primary key index
Index index = new Index(TestTable, "ID");
index.IndexKeyType = IndexKeyType.DriPrimaryKey;

index.IndexedColumns.Add(new IndexedColumn(index,"ID"));

TestTable.Indexes.Add(index);

In this case, the ID column that I got from my DataTable provides the primary key.

Now there is a new database and table on the SQL Server with columns and data types matching the source DataTable.

Populating the New Table Using SQL Bulk Copy

The final step involves copying the data into the new database table. The easiest way to do this would involve a foreach or loop. Instead of processing each DataRow separately, SqlBulkCopy could be used instead. SqlBulkCopy involves passing the whole set in one big lump and can be more efficient using less resources. The process is very quick and easy and requires a connection with the database as well as the table name created earlier. One caveat to this method is that it does not seem to recognize the using statement as an open connection so you have to open the connection manually.

//First create a connection string to destination database
string connectionString;
connectionString = YourConnectionStringand
	Initial Catalog=TestSMODatabase";

//Open a connection with destination database;
using (SqlConnection connection = 
       new SqlConnection(connectionString))
{
   connection.Open();

   //Open bulkcopy connection.
   using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection))
   {
   	//Set destination table name
	//to table previously created.
	bulkcopy.DestinationTableName = "dbo.TestTable";

	try
	{
	   bulkcopy.WriteToServer(SourceTable);
	}
	catch (Exception ex)
	{
	   Console.WriteLine(ex.Message);
	}
	
	connection.Close();
   }
}

Finally we have a database and table that was generated and filled from run time data based on an in memory DataTable.

Conclusions

I found the research involved with this to be extremely fun and rewarding. When I started the research for this article, I originally thought this would be fairly straight forward and somewhat simple, but I now realize that without SMO, creating the database and table from run time data would have been even harder. This article has shown a method for dynamically creating SQL Server databases and tables from in memory datatable information. I believe this will prove extremely useful and can be extended even further with relational tables.

History

• January 16, 2007: Changed formatting to better utilize page layout as well as revised text and removed extraneous code snippets