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.
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:
- How to create a table and or database at runtime that reflects the
DataTables' schema (namely columns and data types).
- 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
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
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:
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:
string connectionString = YourConnectionString;
SqlConnection Connection = new SqlConnection(connectionString);
Server server = new Server(new ServerConnection(Connection));
Database db = new Database(server, "TestSMODatabase");
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:
db = server.Databases["TestSMODatabase"];
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
Column tempC = new Column();
foreach (DataColumn dc in SourceTable.Columns)
tempC = new Column(TestTable, dc.ColumnName);
tempC.DataType = GetDataType(dc.DataType.ToString());
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
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;
DTTemp = DataType.Decimal(2, 18);
DTTemp = DataType.VarChar(50);
DTTemp = DataType.Int;
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:
Index index = new Index(TestTable, "ID");
index.IndexKeyType = IndexKeyType.DriPrimaryKey;
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
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
loop. Instead of processing each
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.
connectionString = YourConnectionStringand
//Open a connection with destination database;
using (SqlConnection connection =
//Open bulkcopy connection.
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection))
//Set destination table name
//to table previously created.
bulkcopy.DestinationTableName = "dbo.TestTable";
catch (Exception ex)
Finally we have a database and table that was generated and filled from run time data based on an in memory
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.
- January 16, 2007: Changed formatting to better utilize page layout as well as revised text and removed extraneous code snippets