| [ Team LiB ] |
|
Recipe 10.15 Creating a Table in the Database from a DataTable SchemaProblemYou need to create a table in a database from an existing DataTable schema. SolutionUse the CreateTableFromSchema( ) method shown in this solution. The sample code contains one event handler and two methods:
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;
}
DiscussionThe 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 schemaif 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 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 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. |
| [ Team LiB ] |
|