Create Table In SQL Based On .NET Datatable
Here’s a quick code to generate a “Create Table” query based on your Datatable object. The code is written on C# and it generates a create query for SQL table. I had found this code somewhere on the StackOverflow site and it has proven to be extremely helpful.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
public static string GenerateCreateTableQueryFromDataTable(string tableName, DataTable table) { var 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)"; } |