A couple of times now, I've needed to save in-memory ADO.NET DataTables straight to SQL Server as new tables. Its handy sometimes, if you're importing data from other sources. Nick Tompson had posted some code on the social.msdn forum to do this, and here I've stripped out the features I didn't need and adapted it to use StringBuilders:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
// Save a DataTable to a SQLServer Table
// Based on Nick Tompsons code from
// http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/4929a0a8-0137-45f6-86e8-d11e220048c3/
// Removed 'make from Schema' options to simplify, and used StringBuilders.
public class SqlTableCreator
{
private SqlConnection \_connection;
public SqlConnection Connection
{
get { return \_connection; }
set { \_connection = value; }
}
private SqlTransaction \_transaction;
public SqlTransaction Transaction
{
get { return \_transaction; }
set { \_transaction = value; }
}
private string \_tableName;
public string DestinationTableName
{
get { return \_tableName; }
set { \_tableName = value; }
}
public SqlTableCreator(SqlConnection connection) : this(connection, null) { }
public SqlTableCreator(SqlConnection connection, SqlTransaction transaction)
{
\_connection = connection;
\_transaction = transaction;
}
public object CreateFromDataTable(DataTable table)
{
string sql = GetCreateFromDataTableSQL(\_tableName, table);
SqlCommand cmd;
if (\_transaction != null && \_transaction.Connection != null)
cmd = new SqlCommand(sql, \_connection, \_transaction);
else
cmd = new SqlCommand(sql, \_connection);
return cmd.ExecuteNonQuery();
}
public static string GetCreateFromDataTableSQL(string tableName, DataTable table)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("CREATE TABLE \[{0}\] (", tableName);
// columns
bool first = true;
foreach (DataColumn column in table.Columns)
{
if (first)
first = false;
else
sb.Append(",");
sb.AppendFormat("\\n\[{0}\] {1}", column.ColumnName, SQLGetType(column));
}
// primary keys
if (table.PrimaryKey.Length > 0)
{
sb.AppendFormat(" CONSTRAINT \[PK\_{0}\] PRIMARY KEY CLUSTERED (", tableName);
bool first2 = true;
foreach (DataColumn column in table.PrimaryKey)
{
if (first2)
first2 = false;
else
sb.Append(",");
sb.AppendFormat("\[{0}\]", column.ColumnName);
}
sb.Append(")\\n");
}
sb.Append(")\\n");
return sb.ToString();
}
// Return T-SQL data type definition, based on schema definition for a column
public static string SQLGetType(object type, int columnSize, int numericPrecision, int numericScale)
{
switch (type.ToString())
{
case "System.String":
return String.Format("VARCHAR({0})", ((columnSize == -1) ? 255 : columnSize));
case "System.Decimal":
if (numericScale > 0)
return "REAL";
else if (numericPrecision > 10)
return "BIGINT";
else
return "INT";
case "System.Double":
case "System.Single":
return "REAL";
case "System.Int64":
return "BIGINT";
case "System.Int16":
case "System.Int32":
return "INT";
case "System.DateTime":
return "DATETIME";
default:
throw new ApplicationException(String.Format("{0} not implemented.",type.ToString()));
}
}
// Overload based on DataColumn from DataTable type
public static string SQLGetType(DataColumn column)
{
return SQLGetType(column.DataType, column.MaxLength, 10, 2);
}
}