Creating a Table in the Database from a DataTable Schema

[ 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.
Nội dung trích xuất từ tài liệu:
Creating a Table in the Database from a DataTable Schema[ 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: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 constantsusing 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(maxLengt ...