Explanation:
I was searching for a solution so I can save my dynamically created DataTable to my Database. I could not find any direct method in .NET. Then I came to this solution. I guess it will be very helpfull for many guys.
I have built a class that can create an SQL Server table from either a DataTable, or the Schema of a IDataReader (r.GetSchemaTable()). It is somewhat optimised to my purposes, but it is easily modified to your own.
public class SqlTableCreator
{
#region Instance Variables
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; }
}
#endregion
#region Constructor
public SqlTableCreator() { }
public SqlTableCreator(SqlConnection connection) : this(connection, null) { }
public SqlTableCreator(SqlConnection connection, SqlTransaction transaction) {
_connection = connection;
_transaction = transaction;
}
#endregion
#region Instance Methods
public object Create(DataTable schema) {
return Create(schema, null);
}
public object Create(DataTable schema, int numKeys) {
int[] primaryKeys = new int[numKeys];
for (int i = 0; i < numKeys; i++) {
primaryKeys[i] = i;
}
return Create(schema, primaryKeys);
}
public object Create(DataTable schema, int[] primaryKeys) {
string sql = GetCreateSQL(_tableName, schema, primaryKeys);
SqlCommand cmd;
if (_transaction != null && _transaction.Connection != null)
cmd = new SqlCommand(sql, _connection, _transaction);
else
cmd = new SqlCommand(sql, _connection);
return cmd.ExecuteNonQuery();
}
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();
}
#endregion
#region Static Methods
public static string GetCreateSQL(string tableName, DataTable schema, int[] primaryKeys) {
string sql = "CREATE TABLE " + tableName + " (\n";
// columns
foreach (DataRow column in schema.Rows) {
if (!(schema.Columns.Contains("IsHidden") && (bool)column["IsHidden"]))
sql += column["ColumnName"].ToString() + " " + SQLGetType(column) + ",\n";
}
sql = sql.TrimEnd(new char[] { ',', '\n' }) + "\n";
// primary keys
string pk = "CONSTRAINT PK_" + tableName + " PRIMARY KEY CLUSTERED (";
bool hasKeys = (primaryKeys != null && primaryKeys.Length > 0);
if (hasKeys) {
// user defined keys
foreach (int key in primaryKeys) {
pk += schema.Rows[key]["ColumnName"].ToString() + ", ";
}
}
else {
// check schema for keys
string keys = string.Join(", ", GetPrimaryKeys(schema));
pk += keys;
hasKeys = keys.Length > 0;
}
pk = pk.TrimEnd(new char[] { ',', ' ', '\n' }) + ")\n";
if (hasKeys) sql += pk;
sql += ")";
return sql;
}
public static string GetCreateFromDataTableSQL(string tableName, DataTable table) {
string sql = "CREATE TABLE [" + tableName + "] (\n";
// columns
foreach (DataColumn column in table.Columns) {
sql += "[" + column.ColumnName + "] " + SQLGetType(column) + ",\n";
}
sql = sql.TrimEnd(new char[] { ',', '\n' }) + "\n";
// primary keys
if (table.PrimaryKey.Length > 0) {
sql += "CONSTRAINT [PK_" + tableName + "] PRIMARY KEY CLUSTERED (";
foreach (DataColumn column in table.PrimaryKey) {
sql += "[" + column.ColumnName + "],";
}
sql = sql.TrimEnd(new char[] { ',' }) + "))\n";
}
return sql;
}
public static string[] GetPrimaryKeys(DataTable schema) {
List<string> keys = new List<string>();
foreach (DataRow column in schema.Rows) {
if (schema.Columns.Contains("IsKey") && (bool)column["IsKey"])
keys.Add(column["ColumnName"].ToString());
}
return keys.ToArray();
}
// 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 "VARCHAR(" + ((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 Exception(type.ToString() + " not implemented.");
}
}
// Overload based on row from schema table
public static string SQLGetType(DataRow schemaRow) {
return SQLGetType(schemaRow["DataType"],
int.Parse(schemaRow["ColumnSize"].ToString()),
int.Parse(schemaRow["NumericPrecision"].ToString()),
int.Parse(schemaRow["NumericScale"].ToString()));
}
// Overload based on DataColumn from DataTable type
public static string SQLGetType(DataColumn column) {
return SQLGetType(column.DataType, column.MaxLength, 10, 2);
}
#endregion
}
|