ADO.NET Cookbook

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.

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 ...