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(); } }
I will suggest you to user DataAdapter with Insert command it will automatically insert all the rows when you will call update on it to submit changes to the database automatically.
Here is a decent example of same
public static SqlDataAdapter CreateCustomerAdapter(SqlConnection conn)
{
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd;
// Create the SelectCommand.
cmd = new SqlCommand("SELECT * FROM Customers " +
"WHERE Country = @Country AND City = @City", conn);
cmd.Parameters.Add("@Country", SqlDbType.NVarChar, 15);
cmd.Parameters.Add("@City", SqlDbType.NVarChar, 15);
da.SelectCommand = cmd;
// Create the InsertCommand.
cmd = new SqlCommand("INSERT INTO Customers (CustomerID, CompanyName) " +
"VALUES (@CustomerID, @CompanyName)", conn);
cmd.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
cmd.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");
da.InsertCommand = cmd;
return da;
}
once data adapter is defined you can add rows to your data table and call da.Update
chandumatta
Member
51 Points
47 Posts
Datatable to sqldatabase table
Aug 05, 2011 09:32 AM|LINK
Hi every one,
i have a datatable with four columns and plenty of rows. now i want to store this data into sqldatabase table of same four columns.
how can i do this?
Please help me out
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
tarun n june...
Contributor
4054 Points
902 Posts
Re: Datatable to sqldatabase table
Aug 05, 2011 12:34 PM|LINK
I will suggest you to user DataAdapter with Insert command it will automatically insert all the rows when you will call update on it to submit changes to the database automatically.
Here is a decent example of same
public static SqlDataAdapter CreateCustomerAdapter(SqlConnection conn) { SqlDataAdapter da = new SqlDataAdapter(); SqlCommand cmd; // Create the SelectCommand. cmd = new SqlCommand("SELECT * FROM Customers " + "WHERE Country = @Country AND City = @City", conn); cmd.Parameters.Add("@Country", SqlDbType.NVarChar, 15); cmd.Parameters.Add("@City", SqlDbType.NVarChar, 15); da.SelectCommand = cmd; // Create the InsertCommand. cmd = new SqlCommand("INSERT INTO Customers (CustomerID, CompanyName) " + "VALUES (@CustomerID, @CompanyName)", conn); cmd.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID"); cmd.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName"); da.InsertCommand = cmd; return da; }Cheers!
Tarun Juneja
http://meghainfotech.wordpress.com/
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Datatable to sqldatabase table
Aug 09, 2011 02:06 AM|LINK
Hello tarun n juneja：）
You can offer an English version if the OP is from English-speaking country：http://support.microsoft.com/kb/308507/en-us
Hello Op：）
You can use "tarun n juneja"'s SqlCommandBuilder and then create new DataRow, and in the end, just call adapter.Update("dt") to finish inserting rows.
However to be simple, I'd like to recommand you using SqlCommandBuilder for a single table with primary key assigned.
Thx again.
tarun n june...
Contributor
4054 Points
902 Posts
Re: Datatable to sqldatabase table
Aug 09, 2011 07:32 AM|LINK
cool never knew this.
thanks Decker.
Cheers!
Tarun Juneja
http://meghainfotech.wordpress.com/
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Datatable to sqldatabase table
Aug 09, 2011 08:30 AM|LINK
Nothing special, It's my pleasure to help others.
So do you, so do all the contributors!
Hehe……