[ Team LiB ]
Recipe 10.15 Creating a Table in the Database from a DataTable Schema
Problem
You need to create a table in a database from an existing DataTable schema.
Solution
Use the CreateTableFromSchema( ) method shown in this solution.
The sample code contains one event handler and two methods:
Button.Click
Creates a DataTable containing the schema from the Orders table in the Northwind
sample database. The method CreateTableFromSchema( ) in the sample code is
called to create a table in the database from this schema.
CreateTableFromSchema( )
This method creates a schema in the database for the schema of the DataTable
argument. The method builds a DDL statement from the schema information and
executes it against the data source specified by the connection string argument to
create the table.
N
etType2SqlType( )
This method is called by the CreateTableFromSchemaMethod( ) to map .NET data
types to SQL Server types when building the DDL statement.
The C# code is shown in Example 10-15.
Example 10-15. File: CreateDatabaseTableFromDataTableSchemaForm.cs
// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Text;
using System.Data;
using System.Data.SqlClient;
// . . .
private void goButton_Click(object sender, System.EventArgs e)
{
// Fill a table with the Orders table schema.
String sqlText = "SELECT * FROM [Orders]";
SqlDataAdapter da = new SqlDataAdapter(sqlText,
ConfigurationSettings.AppSettings["Sql_ConnectString"]);
DataTable dt = new DataTable("Orders");
da.FillSchema(dt, SchemaType.Source);
CreateTableFromSchema(dt,
ConfigurationSettings.AppSettings["Sql_ConnectString"]);
MessageBox.Show("Table " + TABLENAME + " created.",
"Create DataTable from schema.",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void CreateTableFromSchema(DataTable dt, String connectionString)
{
// Drop the new table if it is already there.
StringBuilder sqlCmd = new StringBuilder(
"if exists (SELECT * FROM dbo.sysobjects WHERE id = " +
"object_id('[" + TABLENAME + "]') " +
"AND OBJECTPROPERTY(id, 'IsUserTable') = 1)" +
Environment.NewLine +
"DROP TABLE " + TABLENAME + ";" + Environment.NewLine +
Environment.NewLine);
// Start building a command string to create the table.
sqlCmd.Append("CREATE TABLE [" + TABLENAME + "] (" +
Environment.NewLine);
// Iterate over the column collection in the source table.
foreach(DataColumn col in dt.Columns)
{
// Add the column.
sqlCmd.Append("[" + col.ColumnName + "] ");
// Map the source column type to a SQL Server type.
sqlCmd.Append(NetType2SqlType(col.DataType.ToString( ),
col.MaxLength) + " ");
// Add identity information.
if(col.AutoIncrement)
sqlCmd.Append("IDENTITY ");
// Add AllowNull information.
sqlCmd.Append((col.AllowDBNull ? "" : "NOT ") + "NULL," +
Environment.NewLine);
}
sqlCmd.Remove(sqlCmd.Length - (Environment.NewLine.Length + 1), 1);
sqlCmd.Append(") ON [PRIMARY];" + Environment.NewLine +
Environment.NewLine);
// Add the primary key to the table, if it exists.
if(dt.PrimaryKey != null)
{
sqlCmd.Append("ALTER TABLE " + TABLENAME +
" WITH NOCHECK ADD " + Environment.NewLine);
sqlCmd.Append("CONSTRAINT [PK_" + TABLENAME +
"] PRIMARY KEY CLUSTERED (" + Environment.NewLine);
// Add the columns to the primary key.
foreach(DataColumn col in dt.PrimaryKey)
{
sqlCmd.Append("[" + col.ColumnName + "]," +
Environment.NewLine);
}
sqlCmd.Remove(sqlCmd.Length -
(Environment.NewLine.Length + 1), 1);
sqlCmd.Append(") ON [PRIMARY];" + Environment.NewLine +
Environment.NewLine);
}
sqlTextBox.Text = sqlCmd.ToString( );
// Create and execute the command to create the new table.
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(sqlCmd.ToString( ), conn);
conn.Open( );
cmd.ExecuteNonQuery( );
conn.Close( );
}
private String NetType2SqlType(String netType, int maxLength)
{
String sqlType = "";
// Map the .NET type to the data source type.
// This is not perfect because mappings are not always one-to-one.
switch(netType)
{
case "System.Boolean":
sqlType = "[bit]";
break;
case "System.Byte":
sqlType = "[tinyint]";
break;
case "System.Int16":
sqlType = "[smallint]";
break;
case "System.Int32":
sqlType = "[int]";
break;
case "System.Int64":
sqlType = "[bigint]";
break;
case "System.Byte[]":
sqlType = "[binary]";
break;
case "System.Char[]":
sqlType = "[nchar] (" + maxLength + ")";
break;
case "System.String":
if(maxLength == 0x3FFFFFFF)
sqlType = "[ntext]";
else
sqlType = "[nvarchar] (" + maxLength + ")";
break;
case "System.Single":
sqlType = "[real]";
break;
case "System.Double":
sqlType = "[float]";
break;
case "System.Decimal":
sqlType = "[decimal]";
break;
case "System.DateTime":
sqlType = "[datetime]";
break;
case "System.Guid":
sqlType = "[uniqueidentifier]";
break;
case "System.Object":
sqlType = "[sql_variant]";
break;
}
return sqlType;
}
Discussion
The solution dynamically constructs a Data Definition Language (DDL) statement to
create a table in a SQL Server database from the schema of a DataTable. The complete
statement that is generated is shown in Example 10-16.
Example 10-16. DDL generated to create database table from DataTable schema
if exists
(SELECT * FROM dbo.sysobjects WHERE id = object_id('[TBL1015]') AND
OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE TBL1015;
CREATE TABLE [TBL1015] (
[OrderID] [int] IDENTITY NOT NULL,
[CustomerID] [nvarchar] (5) NULL,
[EmployeeID] [int] NULL,
[OrderDate] [datetime] NULL,
[RequiredDate] [datetime] NULL,
[ShippedDate] [datetime] NULL,
[ShipVia] [int] NULL,
[Freight] [decimal] NULL,
[ShipName] [nvarchar] (40) NULL,
[ShipAddress] [nvarchar] (60) NULL,
[ShipCity] [nvarchar] (15) NULL,
[ShipRegion] [nvarchar] (15) NULL,
[ShipPostalCode] [nvarchar] (10) NULL,
[ShipCountry] [nvarchar] (15) NULL
) ON [PRIMARY];
ALTER TABLE TBL1015 WITH NOCHECK ADD
CONSTRAINT [PK_TBL1015] PRIMARY KEY CLUSTERED (
[OrderID]