Creating a SQL Server table from a C# datatable
public static string CreateTABLE(string tableName, DataTable table)
{
string sqlsc;
sqlsc = "CREATE TABLE " + tableName + "(";
for (int i = 0; i < table.Columns.Count; i++)
{
sqlsc += "\n [" + table.Columns[i].ColumnName + "] ";
string columnType = table.Columns[i].DataType.ToString();
switch (columnType)
{
case "System.Int32":
sqlsc += " int ";
break;
case "System.Int64":
sqlsc += " bigint ";
break;
case "System.Int16":
sqlsc += " smallint";
break;
case "System.Byte":
sqlsc += " tinyint";
break;
case "System.Decimal":
sqlsc += " decimal ";
break;
case "System.DateTime":
sqlsc += " datetime ";
break;
case "System.String":
default:
sqlsc += string.Format(" nvarchar({0}) ", table.Columns[i].MaxLength == -1 ? "max" : table.Columns[i].MaxLength.ToString());
break;
}
if (table.Columns[i].AutoIncrement)
sqlsc += " IDENTITY(" + table.Columns[i].AutoIncrementSeed.ToString() + "," + table.Columns[i].AutoIncrementStep.ToString() + ") ";
if (!table.Columns[i].AllowDBNull)
sqlsc += " NOT NULL ";
sqlsc += ",";
}
return sqlsc.Substring(0,sqlsc.Length-1) + "\n)";
}
It's a little bit unusual in SQL to create tables out of a client supplied definition of a Datatable object. Tables are carefully crafted entities in SQL, with deploy time placement consideration of choosing the proper disk, with indexing consideration at design time and with all the issues involved in properly modeling a database.
Better you'd explain what you're trying to achieve so we understand what advice to give.
As a side note, in SQL 2008 there is a very easy way to create a table out of a client defined Datatable: pass the DataTable as a Table value parameter, then issue a SELECT * INTO <tablename> FROM @tvp, this will effectively transfer the definition of the Datatable and its content data into a real table in SQL.
I know this question is rather old, but I just had something very similar that I needed to write. I took what I did and altered the examples provided by both Amin and rasputino and created an example that will output just the SQL. I added a few features and avoided concatenation to help improve upon a process that is inherently a poor performer.
/// <summary>
/// Inspects a DataTable and return a SQL string that can be used to CREATE a TABLE in SQL Server.
/// </summary>
/// <param name="table">System.Data.DataTable object to be inspected for building the SQL CREATE TABLE statement.</param>
/// <returns>String of SQL</returns>
public static string GetCreateTableSql(DataTable table)
{
StringBuilder sql = new StringBuilder();
StringBuilder alterSql = new StringBuilder();
sql.AppendFormat("CREATE TABLE [{0}] (", table.TableName);
for (int i = 0; i < table.Columns.Count; i++)
{
bool isNumeric = false;
bool usesColumnDefault = true;
sql.AppendFormat("\n\t[{0}]", table.Columns[i].ColumnName);
switch (table.Columns[i].DataType.ToString().ToUpper())
{
case "SYSTEM.INT16":
sql.Append(" smallint");
isNumeric = true;
break;
case "SYSTEM.INT32":
sql.Append(" int");
isNumeric = true;
break;
case "SYSTEM.INT64":
sql.Append(" bigint");
isNumeric = true;
break;
case "SYSTEM.DATETIME":
sql.Append(" datetime");
usesColumnDefault = false;
break;
case "SYSTEM.STRING":
sql.AppendFormat(" nvarchar({0})", table.Columns[i].MaxLength);
break;
case "SYSTEM.SINGLE":
sql.Append(" single");
isNumeric = true;
break;
case "SYSTEM.DOUBLE":
sql.Append(" double");
isNumeric = true;
break;
case "SYSTEM.DECIMAL":
sql.AppendFormat(" decimal(18, 6)");
isNumeric = true;
break;
default:
sql.AppendFormat(" nvarchar({0})", table.Columns[i].MaxLength);
break;
}
if (table.Columns[i].AutoIncrement)
{
sql.AppendFormat(" IDENTITY({0},{1})",
table.Columns[i].AutoIncrementSeed,
table.Columns[i].AutoIncrementStep);
}
else
{
// DataColumns will add a blank DefaultValue for any AutoIncrement column.
// We only want to create an ALTER statement for those columns that are not set to AutoIncrement.
if (table.Columns[i].DefaultValue != null)
{
if (usesColumnDefault)
{
if (isNumeric)
{
alterSql.AppendFormat("\nALTER TABLE {0} ADD CONSTRAINT [DF_{0}_{1}] DEFAULT ({2}) FOR [{1}];",
table.TableName,
table.Columns[i].ColumnName,
table.Columns[i].DefaultValue);
}
else
{
alterSql.AppendFormat("\nALTER TABLE {0} ADD CONSTRAINT [DF_{0}_{1}] DEFAULT ('{2}') FOR [{1}];",
table.TableName,
table.Columns[i].ColumnName,
table.Columns[i].DefaultValue);
}
}
else
{
// Default values on Date columns, e.g., "DateTime.Now" will not translate to SQL.
// This inspects the caption for a simple XML string to see if there is a SQL compliant default value, e.g., "GETDATE()".
try
{
System.Xml.XmlDocument xml = new System.Xml.XmlDocument();
xml.LoadXml(table.Columns[i].Caption);
alterSql.AppendFormat("\nALTER TABLE {0} ADD CONSTRAINT [DF_{0}_{1}] DEFAULT ({2}) FOR [{1}];",
table.TableName,
table.Columns[i].ColumnName,
xml.GetElementsByTagName("defaultValue")[0].InnerText);
}
catch
{
// Handle
}
}
}
}
if (!table.Columns[i].AllowDBNull)
{
sql.Append(" NOT NULL");
}
sql.Append(",");
}
if (table.PrimaryKey.Length > 0)
{
StringBuilder primaryKeySql = new StringBuilder();
primaryKeySql.AppendFormat("\n\tCONSTRAINT PK_{0} PRIMARY KEY (", table.TableName);
for (int i = 0; i < table.PrimaryKey.Length; i++)
{
primaryKeySql.AppendFormat("{0},", table.PrimaryKey[i].ColumnName);
}
primaryKeySql.Remove(primaryKeySql.Length - 1, 1);
primaryKeySql.Append(")");
sql.Append(primaryKeySql);
}
else
{
sql.Remove(sql.Length - 1, 1);
}
sql.AppendFormat("\n);\n{0}", alterSql.ToString());
return sql.ToString();
}
Here's a simple test to use this method and get the SQL:
DataTable table = new DataTable("Users");
table.Columns.Add(new DataColumn()
{
ColumnName = "UserId",
DataType = System.Type.GetType("System.Int32"),
AutoIncrement = true,
AllowDBNull = false,
AutoIncrementSeed = 1,
AutoIncrementStep = 1
});
table.Columns.Add(new DataColumn()
{
ColumnName = "UserName",
DataType = System.Type.GetType("System.String"),
AllowDBNull = true,
DefaultValue = String.Empty,
MaxLength = 50
});
table.Columns.Add(new DataColumn()
{
ColumnName = "LastUpdate",
DataType = System.Type.GetType("System.DateTime"),
AllowDBNull = false,
DefaultValue = DateTime.Now,
Caption = "<defaultValue>GETDATE()</defaultValue>"
});
table.PrimaryKey = new DataColumn[] { table.Columns[0] };
string sql = DataHelper.GetCreateTableSql(table);
Console.WriteLine(sql);
And finally, the output:
CREATE TABLE [Users] (
[UserId] int IDENTITY(0,1) NOT NULL,
[UserName] nvarchar(50),
[LastUpdate] datetime NOT NULL,
CONSTRAINT PK_Users PRIMARY KEY (UserId)
);
ALTER TABLE Users ADD CONSTRAINT [DF_Users_UserName] DEFAULT ('') FOR [UserName];
ALTER TABLE Users ADD CONSTRAINT [DF_Users_LastUpdate] DEFAULT (GETDATE()) FOR[LastUpdate];
I agree with the original answer that states that data management isn't something that should be done haphazardly. It does indeed require a lot of thought to keep the DB running smoothly and allowing for maintainability in the future. But, there are times when a coding solution is necessary and I'm hoping this might help someone out.