在select语句中使用datatable [英] use datatable in select statement

问题描述

da.InsertCommand = new SqlCommand(@"insert into Customers (nameCustomer)
select DISTINCT nameCustomer from dt where NOT EXISTS (select nameCustomer from Customers where Customers.nameCustomer=dt.nameCustomer)", cn);

                cn.Open();
                da.InsertCommand.ExecuteNonQuery();
                cn.Close();

客户是桌子在sql server数据库中

dt是从excel表填充的数据表



当我运行项目时出现此错误无效的对象名称'dt' 。

所以如何在select语句中使用datatable。

Customers is table in sql server database
dt is datatable filled from excel sheet

when i run the project this error appear "Invalid object name 'dt'."
so how can i use datatable in select statement.

推荐答案

你不能以这种方式混合数据源。您的SQL服务器没有关于应用程序级别上存储的任何数据的信息。

您要么逐个解析可数据行（请参阅： http://www.dotnetperls.com/datatable-foreach [ ^ ]），或者您可以创建一个存储过程，接受您的数据表作为表类型参数并进行插入或使用批量复制。请参阅此处的asnwers： http：/ /stackoverflow.com/questions/10405373/insert-entire-datatable-into-database-at-once-instead-of-row-by-row [ ^ ]

You can't mix data sources this way. Your SQL server has no information about any data stored on your application level.
You either parse the datable rows one by one (see: http://www.dotnetperls.com/datatable-foreach[^]), or you can create a stored procedure that accepts your datatable as table typed parameter and does the insertion, or use bulkcopy. See the asnwers here: http://stackoverflow.com/questions/10405373/insert-entire-datatable-into-database-at-once-instead-of-row-by-row[^]

不行，这是行不通的，因为 dt 是C＃代码中的DataTable，并且SQL很可能在完全不同的情况下运行机。因此，将c＃对象的名称作为命令字符串的一部分传递永远不会工作...



相反，请查看使用SqlBulkCopy：SqlBulkCopy.WriteToServer Method（DataTable） [ ^ ]

No, that won't work because dt is a DataTable in your C# code, and the SQL is very likely to to running on a totaly diffferent machine. So passing the name of your c# object as part of the command string is never goign to work...

Instead, look at using SqlBulkCopy: SqlBulkCopy.WriteToServer Method (DataTable)[^]

这篇关于在select语句中使用datatable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！