using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer;
//Set destination connection string string connectionString = YourConnectionString;
SqlConnection Connection = new SqlConnection(connectionString);
//SMO Server object setup with SQLConnection. Server server = new Server(new ServerConnection(Connection));
//Create a new SMO Database giving server object and database name Database db = new Database(server, "TestSMODatabase");
db.Create();
//Set Database to the newly created database db = server.Databases["TestSMODatabase"];
//Create a new SMO table Table TestTable = new Table(db, "TestTable");
//SMO Column object referring to destination table. Column tempC = new Column();
//Add the column names and types from the datatable into the new table //Using the columns name and type property foreach (DataColumn dc in SourceTable.Columns)
{
//Create columns from datatable column schema tempC = new Column(TestTable, dc.ColumnName);
tempC.DataType = GetDataType(dc.DataType.ToString());
TestTable.Columns.Add(tempC);
}
//Create the Destination Table TestTable.Create();
public DataType GetDataType(string dataType)
{
DataType DTTemp = null;
switch (dataType)
{
case ("System.Decimal"):
DTTemp = DataType.Decimal(2, 18);
break;
case ("System.String"):
DTTemp = DataType.VarChar(50);
break;
case ("System.Int32"):
DTTemp = DataType.Int;
break;
}
return DTTemp;
}
//Create a primary key index Index index = new Index(TestTable, "ID");
index.IndexKeyType = IndexKeyType.DriPrimaryKey;
index.IndexedColumns.Add(new IndexedColumn(index,"ID"));
TestTable.Indexes.Add(index);
Populating the New Table Using SQL Bulk Copy
//First create a connection string to destination database string connectionString;
connectionString = YourConnectionStringand
Initial Catalog=TestSMODatabase"; //Open a connection with destination database; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); //Open bulkcopy connection. using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection)) { //Set destination table name //to table previously created. bulkcopy.DestinationTableName = "dbo.TestTable"; try { bulkcopy.WriteToServer(SourceTable); } catch (Exception ex) { Console.WriteLine(ex.Message); } connection.Close(); } }
kamalchhabra
Participant
1110 Points
272 Posts
Re: Datatable to sqldatabase table
Aug 05, 2011 09:54 AM|LINK
first imports these name space
Populating the New Table Using SQL Bulk Copy
for help see following linkhttp://www.codeproject.com/KB/database/DataTableToDataBase.aspx