最近在项目中使用了Linq,想把Linq的查询结果直接转换成DataTable对象,通过查找发现Linq有一个CopyToDataTable<T>的泛型方法,该方法只能在T是DataRow的情况下使用,发现了这个方法以后就直接在项目中使用了,但是在使用的过程中发现,如果Linq的查询结果不包含任何DataRow对象的时候,使用CopyToDataTable()方法会报错,代码如下:
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5 using System.Threading.Tasks;
6 using System.Configuration;
7 using System.Data;
8 using System.Data.SqlClient;
9
10 namespace CopyToDataTableDemo
11 {
12 class Program
13 {
14 static void Main(string[] args)
15 {
16 string strConn = ConfigurationManager.ConnectionStrings["AppConnection"].ConnectionString;
17 using (SqlConnection conn = new SqlConnection(strConn))
18 {
19 string strSQL = "SELECT * FROM Product";
20 SqlCommand cmd = new SqlCommand(strSQL, conn);
21 SqlDataAdapter adapter = new SqlDataAdapter(cmd);
22 conn.Open();
23 try
24 {
25 DataTable dt = new DataTable();
26 adapter.Fill(dt);
27 //CopyToDataTable()
28 DataTable dtTemp = dt.AsEnumerable().Where<DataRow>(p =>
29 {
30 return p["ProductId"].ToString().Trim().Equals("4");
31 }).CopyToDataTable();
32
33 }
34 catch (Exception ex)
35 {
36
37 }
38 finally
39 {
40 conn.Close();
41 }
42 }
43 }
44 }
45 }
报错信息如下:
该错误信息说明如果Linq的查询结果不包含任何DataRow对象的时候,使用该方法会报错,那么怎么将Linq的查询结果转换成DataTable使用呢?
继续查询Linq的方法,发现Linq还有一个ToList()的方法,使用该方法可以解决Linq查询结果不包含任何DataRow对象时报错的问题,代码修改如下:
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Threading.Tasks; 6 using System.Configuration; 7 using System.Data; 8 using System.Data.SqlClient; 9 10 namespace CopyToDataTableDemo 11 { 12 class Program 13 { 14 static void Main(string[] args) 15 { 16 string strConn = ConfigurationManager.ConnectionStrings["AppConnection"].ConnectionString; 17 using (SqlConnection conn = new SqlConnection(strConn)) 18 { 19 string strSQL = "SELECT * FROM Product"; 20 SqlCommand cmd = new SqlCommand(strSQL, conn); 21 SqlDataAdapter adapter = new SqlDataAdapter(cmd); 22 conn.Open(); 23 try 24 { 25 DataTable dt = new DataTable(); 26 adapter.Fill(dt); 27 //CopyToDataTable() 28 // 当LINQ的查询结果不包含任何DataRow对象的时候会报错 29 //DataTable dtTemp = dt.AsEnumerable().Where<DataRow>(p => 30 //{ 31 // return p["ProductId"].ToString().Trim().Equals("4"); 32 //}).CopyToDataTable(); 33 34 //ToList() 35 List<DataRow> list = dt.AsEnumerable().Where<DataRow>(p => 36 { 37 return p["ProductId"].ToString().Trim().Equals("4"); 38 }).ToList(); 39 if (list.Count > 0) 40 { 41 DataTable dtTemp = dt.Clone(); 42 // 循环遍历list转换成DataTable 43 list.ForEach(p => 44 { 45 dtTemp.Rows.Add(p.ItemArray); 46 }); 47 } 48 49 } 50 catch (Exception ex) 51 { 52 53 } 54 finally 55 { 56 conn.Close(); 57 } 58 } 59 } 60 } 61 }
// Bind the System.Windows.Forms.DataGridView object // to the System.Windows.Forms.BindingSource object. dataGridView.DataSource = bindingSource;
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
// Query the SalesOrderHeader table for orders placed
// after August 8, 2001.
IEnumerable<DataRow> query =
from order in orders.AsEnumerable()
where order.Field<DateTime>("OrderDate") > new DateTime(2001, 8, 1)
select order;
// Create a table from the query.
DataTable boundTable = query.CopyToDataTable<DataRow>();
// Bind the table to a System.Windows.Forms.BindingSource object,
// which acts as a proxy for a System.Windows.Forms.DataGridView object.
bindingSource.DataSource = boundTable;