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:
- How to create a table and or database at runtime that reflects the
DataTable
s' 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 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:
string connectionString = YourConnectionString;
SqlConnection Connection = new SqlConnection(connectionString);
Server server = new Server(new ServerConnection(Connection));
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:
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 DataTable
):
Column tempC = new Column();
foreach (DataColumn dc in SourceTable.Columns)
{
tempC = new Column(TestTable, dc.ColumnName);
tempC.DataType = GetDataType(dc.DataType.ToString());
TestTable.Columns.Add(tempC);
}
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:
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.
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