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.

NetType2SqlType( )

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] ) ON [PRIMARY];

The first commandthe DROP statementis 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 column is an identity column or allows null values. A method is called to map the .NET data types of the DataTable to SQL Server data types. This method does not work perfectly because there is not a one-to-one mapping between .NET data types and SQL Server data types. Make the mapping decisions based on the requirements of your application. The mapping method also adds the field length for the DDL column description for string-type columns. For more information about mapping SQL Server data types to .NET Framework data types, see Recipe 2.8.

The third DDL command creates the primary key constraint on the newly constructed table. While single-column primary keys can easily be added to the CREATE TABLE command, the easiest way to handle compound keys is by using an ALTER TABLE statement with an ADD CONSTRAINT statement and PRIMARY KEY argument. Iterate over the collection of columns exposed by the PrimaryKey property of the table to add the columns to the command.

If you have a number of tables in a DataSet that you want to create in a database, you can iterate through the collection of DataRelation objects for the DataSet and use the ALTER TABLE statement with the ADD CONSTRAINT command and a FOREIGN KEY argument to add the table relations to the database.

For more information about DDL syntax, see Microsoft SQL Server Books Online.