ADO.NET 3.5 Cookbook, 2nd Edition

11.10. Creating a Database Table from a DataTable Schema

Problem

You need to create a table in a database from an existing DataTable schema.

Solution

Use the CreateDdlFromDataTable() method shown in this solution.

The sample code contains creates a DataTable containing the schema from the Sales.SalesOrder table in the AdventureWorks sample database. The method CreateDdlFromDataTable() in the sample code is called which generates the T-SQL DDL to create the table from the DataTable schema. The NetType2SqlType() method is called by the CreateDdlFromDataTable() to map .NET data types to SQL Server types when building the DDL statement.

The C# code in Program.cs in the project CreateTableDdlFromDataTable is shown in Example 11-10.

Example 11-10. File: Program.cs for CreateTableDdlFromDataTable solution

using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Xml;

namespace CreateTableDdlFromDataTable
{
    class Program
    {
        static void Main(string[] args)
        {
            string schemaName = "Sales";
            string tableName = "SalesOrderHeader";

            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            // Fill a DataTable with the schema of the table. string sqlText = "SELECT * FROM " + schemaName + "." + tableName; SqlDataAdapter da = new SqlDataAdapter(sqlText, sqlConnectString); DataTable dt = new DataTable( ); da.FillSchema(dt, SchemaType.Source); // Create the DDL that creates the table from the DataTable string sqlDdl = CreateDdlFromDataTable(schemaName, ...