在C＃中将DataTable转换为SQL的INSERT脚本 [英] Converting a DataTable to an INSERT script for SQL in C#

问题描述

大家好，

我的.CSV文件包含以下内容：

1732，Juan Perez，435.00,11-05-2002

554，Pedro Gomez，12342.30,06-02-2004

112，Ramiro Politti，0.00,01-02-2000

924，Pablo Ramirez，3321.30,24 -11-2002

我已经设法读取并将所有记录插入到C＃中的DataTable中，现在我需要为此表生成一个TSQL INSERT脚本以及其中的DATA。

有什么建议吗？

解决方案

这就是你需要做的事情;



你可以一次一行地进入数据库。



主要是这种方法只是打开与访问数据库的连接，通过gridview中的每一行datasource（如果你动态创建数据源，你可能需要在开始时重新创建表而不是从gridview中提取它），然后通过SQL中的insert命令将每一行推入表中。



检索数据

< pre lang =C ++> DataTable dtTable = Gridview1.datasource;

创建连接字符串

 string connString =; 
数据源= myServerAddress;初始目录= myDataBase;用户ID = myUsername;密码= myPassword; 

打开连接

 SqlConnection cnx =  new  SqlConnection（connString）; 

读取数据表中的每一行重复方法

每个iRow作为数据行中的dtTable



指定SQL字符串

  string  strSQL =   INSERT INTO 
 TestTable（Col1，Col2）VALUES（ + iRow.item（  Col1）+  ， + iRow.item（  Col1）+   ）; 

'创建命令对象

 SqlCommand cmd =  new  SqlCommand（）; 
 cmd.CommandText = strSQL; 
 cmd.Connection = cnx; 

'执行插入

 尝试 
 {
 cnx.Open（）; 
 cmd.ExecuteNonQuery（）; 
} 

'关闭数据库连接

cnx .Close（）



或者你可以做一个BULK Insert。使用 SQLBulkCopy类 [ ^ ]

如果您将所有记录提取到数据表中，则直接将此数据表传递到存储过程，并从存储过程通过插入选择查询将所有行插入一个镜头。



请让我知道你是否想要这个解决方案。



记住这个只适用于sql server 2008以上。



但是



如果您有CSV文件，请使用批量复制。



，行分隔符为\ n和field seperator as，



剩余是你的要求。

我有相同的查询如何在脚本中转换选择和插入查询并保存在动态表中ob单击事件

Hi guys,
I have a .CSV file with this content:
1732,Juan Perez,435.00,11-05-2002
554,Pedro Gomez,12342.30,06-02-2004
112,Ramiro Politti,0.00,01-02-2000
924,Pablo Ramirez,3321.30,24-11-2002
I have managed to read and insert all the records into a DataTable in C#, now I need to generate a TSQL INSERT script for this table along with the DATA in it.
any suggestions?

解决方案

SO here is what you need to do;

You can do it one row at a time in to the database.

Mainly this method is just opening a connection to the access db, going through each row in the gridview datasource (if you create the datasource on the fly you might need to recreate the table at the beginning instead of pulling it from the gridview) and then pushing each row in to the table via an insert command in SQL.

Retrieve data

DataTable dtTable = Gridview1.datasource;

Create a connection string

string connString ="";
Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

Open a connection

SqlConnection cnx = new SqlConnection(connString);

To read each row in datatable a reiteration method
For Each iRow as Datarow in dtTable

Specify the SQL string

string strSQL = "INSERT INTO
TestTable(Col1,Col2) VALUES (" + iRow.item("Col1") + "," + iRow.item("Col1") + ")";

'Create a command object

SqlCommand cmd = new SqlCommand();
            cmd.CommandText = strSQL;
            cmd.Connection = cnx;

'Execute Insert

try
            {
                cnx.Open();
                cmd.ExecuteNonQuery();
            }

'Close the db connection
cnx.Close()

Or instead you can do a BULK Insert. By using SQLBulkCopy Class[^]

If you fetched all records into datatable the directly pass this datatable into a stored procedure and from stored procedure insert all rows into one shot by insert select query.

Please let me know if you want this solution.

Remeber this works only in sql server 2008 onwards.

but

If you have CSV file use bulk copy.

with line seperator as \n and field seperator as ,

Remaining is upto your requirement.

i have same query how to convert select and insert query in script and save in dynamic table ob single click event

这篇关于在C＃中将DataTable转换为SQL的INSERT脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！