5-14 EF使用存储过程以及LIST泛型对象转换为Datatable

原文链接:http://www.cnblogs.com/ckblogs/p/3728330.html

今天使用EF根据数据库生成模型后,发现之前编写的存储过程并没有返回一个实体类,研究后发现写成了

ALTER proc [dbo].[usp_tree]
@id varchar(max)
AS
declare @str nvarchar(1000);
set @str='with my1 as (select FId,Parent,Name,Code as TagName,leaf from FactoryTree where Parent=('''+@id+''')  union all select FactoryTree.FId,FactoryTree.Parent,FactoryTree.Name,FactoryTree.Code as TagName,FactoryTree.leaf from my1,FactoryTree where my1.FId=FactoryTree.Parent and my1.leaf=0  and FactoryTree.leaf=0)  select * from my1'
print @str
exec (@str)

 

这样的写法在EF中默认返回INT类型,而我需要获取多表查询的结果返回一个新的实体类,对其进行操作,所以有新写了一个存储过程

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[EF_Tree] @id varchar(max) AS BEGIN

with my1 as (select FId,Parent,Name,Code as TagName,leaf from FactoryTree where Parent=(''+@id+'')  union all select FactoryTree.FId,FactoryTree.Parent,FactoryTree.Name,FactoryTree.Code as TagName,FactoryTree.leaf from my1,FactoryTree where my1.FId=FactoryTree.Parent and my1.leaf=0  and FactoryTree.leaf=0)  select * from my1

END

只是去掉了@str就能返回一个实体类,之前使用ADO.NET并没有这种情况。

 

话说EF还真是好用,改了数据库只要更新下模型就可以了,很HAPPY。

EF调用存储过程

var Result = (from p in db.EF_Tree(Fid)
                         select p).ToList();

因为Result是实体类,而在项目中要用到DataTable转化JSON(没办法,公司项目里都是用的ADO.NET,我也懒得造*)

下一步,就是将Result转换为DataTable

 /// <summary>         

/// 将集合类转换成DataTable         

/// </summary>         

/// <param name="list">集合</param>         

/// <returns></returns>         

public static DataTable MyListToDataTable(IList list)        

{            

DataTable result = new DataTable();            

if (list.Count > 0)            

{               

  PropertyInfo[] propertys = list[0].GetType().GetProperties();                

foreach (PropertyInfo pi in propertys)      

           {              

       result.Columns.Add(pi.Name);       

          }

                for (int i = 0; i < list.Count; i++)      

           {                   

  ArrayList tempList = new ArrayList();           

          foreach (PropertyInfo pi in propertys)       

              {                      

   object obj = pi.GetValue(list[i], null);    

                     tempList.Add(obj);               

      }                  

   object[] array = tempList.ToArray();        

             result.LoadDataRow(array, true);           

      }       

      }          

   return result;        

}

不知道为什么网上的例子里还要在result.Columns.Add(pi.Name,pi.propertyType);      

就一直报错DataSet不支持System.nullable<>

后来研究了一下删了pi.propertyType就OK了。

 

 

 

 

转载于:https://www.cnblogs.com/ckblogs/p/3728330.html

上一篇:Design Pattern : Composite Pattern


下一篇:Leaf for Mac (rss阅读器)