在C#转换一个DataTable到CREATE TABLE + INSERT脚本SQL [英] Converting a DataTable to an CREATE TABLE + INSERT script for SQL in C#
问题描述
我需要在它与数据一起生成一个DataTable一个TSQL脚本。
它不是一个单一的插入。除了这一点,我需要太创建表(数据表相同的结构)
I need to generate a TSQL script from a DataTable along with the DATA in it. It's not a single insert. Beside that, I need the create the table too (same datatable structure)
所以:我有填充数据的DataTable。我想,在SQL服务器会在它这个数据表与数据一起结构的TSQL脚本(CREATE TABLE + INSERT)
so: I have a DataTable filled with data. I want the TSQL script that creates the structure of this DataTable along with the data in it in SQL SERVER (CREATE TABLE + INSERT)
感谢您提前
并特别感谢约翰·桑德斯帮助我纠正这个问题。
Thank you in advance And Special thanks to John Saunders for helping me to correct the question.
推荐答案
首先,我会用这个对象的建立自己的CREATE TABLE命令。
First, I would use this object to build your CREATE TABLE command.
然后我会用SQL BulkCopy,而不是单独的INSERT语句。
Then I would use SQL BulkCopy rather than separate INSERT statements.
public static void BulkInsertDataTable(string connectionString, string tableName, DataTable table)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlBulkCopy bulkCopy =
new SqlBulkCopy
(
connection,
SqlBulkCopyOptions.TableLock |
SqlBulkCopyOptions.FireTriggers |
SqlBulkCopyOptions.UseInternalTransaction,
null
);
bulkCopy.DestinationTableName = tableName;
connection.Open();
bulkCopy.WriteToServer(table);
connection.Close();
}
}
这篇关于在C#转换一个DataTable到CREATE TABLE + INSERT脚本SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!