Last post Nov 12, 2016 09:00 AM by Nilishere
Nov 08, 2016 03:48 PM|aspfun|LINK
I asp.net project, after a few steps one dataset was created.
How to export this dataset as a SQL table into a SQL server?
Since this dataset has about 200 columns it is hard to create a SQL table column by column.
Nov 08, 2016 06:24 PM|PatriceSc|LINK
Not crystal clear. Do you mean that you want to export that to an existing table or that you want to create a new table from this ? Yopu could still build a http://www.w3schools.com/sql/sql_create_table.asp statement
programmatically from the DataSet if you are trying to create a new table.
Also 200 columns seems much. Double check maybe that your data is correctly shaped (or is it not normalized?)
Nov 08, 2016 06:34 PM|Nilishere|LINK
Yes you can do that with sqlserver management objects and Sqlbulkcopy.
First you have a create a table in the required sql server database using sqlserver management objects and then you can use Sqlbulkcopy to copy data to the new created table in the sql server DB.
1. First add the below namespaces in your code
2. Create the connection to the destination server and create the database or you can use an existing one
//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");
3. Create the table and set database as current database
//Set Database to the newly created database
db = server.Databases["TestSMODatabase"];
//Create a new SMO table
Table TestTable = new Table(db, "TestTable");
4. Create the columns in the table, asn it has 200 columns you don't need to write them all instead you can loop through them using datacolumn collection . Here
GetDataType() function has been defined to create the column with the correct datatype.Here SourceTable is the actual datatable which you get from your original dataset ( e.g Dataset.Tables)
//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());
//Create the Destination Table
Check the code for GetDataType() function below
//You can add more conversions here
public DataType GetDataType(string dataType)
DataType DTTemp = null;
DTTemp = DataType.Decimal(2, 18);
DTTemp = DataType.VarChar(50);
DTTemp = DataType.Int;
5. Create a primary key index for the new table
//Create a primary key index
Index index = new Index(TestTable, "ID");
index.IndexKeyType = IndexKeyType.DriPrimaryKey;
Here ID is the primary key for the new created TestTable.
6. Populate the new table with Sqlbulkcopy
//First create a connection string to destination database
connectionString = YourConnectionStringand
Initial Catalog=TestSMODatabase"; // Add Initial Catalog=<your new created Database name> to your connectionstring to point to new database.
//Open a connection with destination database;
using (SqlConnection connection =
//Open bulkcopy connection.
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection))
//Set destination table name
//to table previously created.
bulkcopy.DestinationTableName = "dbo.TestTable";
bulkcopy.WriteToServer(SourceTable); //Write the data to the destination table
// SourceTable is the actual datatable from your dataset i.e SourceTable = Dataset.Tables;
catch (Exception ex)
Please check the below links for reference
Nov 08, 2016 07:19 PM|aspfun|LINK
This is what I need.
Nov 09, 2016 09:01 AM|Eric Du|LINK
I'm very glad to hear your issue have been solved, if you think Nilishere's reply help you solved your isssue, please mark his reply as answer!
Nov 12, 2016 09:00 AM|Nilishere|LINK
Good to know that this solution worked for you, please mark my solution as answer as others will be benefited from it too.