[ 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;


// 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);

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;

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]
) ON [PRIMARY];
The first command—the DROP statement—is not strictly required and is included here
so that the example does not crash if it has been run previously. It might be more
appropriate in your situation to check if the table already exists in the database and if it
does, abort execution since your table might contain important data. If that is the case,
return the results of the EXISTS query to the calling application and use that to control
whether the new table is created.
The second DDL command uses the CREATE TABLE statement to create the table in
the database. The code iterates over the collection of the columns in the DataTable
schema to retrieve the name and the maximum length of the column and whether the