将DataTable转换为C#中的SQL CREATE TABLE + INSERT脚本 [英] Converting a DataTable to an CREATE TABLE + INSERT script for SQL in C#
问题描述
我需要从DataTable中生成一个TSQL脚本以及其中的DATA。
这不是一个插入。除此之外,我还需要创建表(相同的数据结构)
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 SERVER(CREATE TABLE + INSERT)中创建此DataTable结构的TSQL脚本及其中的数据。
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命令。 / a>
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();
}
}
这篇关于将DataTable转换为C#中的SQL CREATE TABLE + INSERT脚本的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!