.net entity framework 存储过程 能直接返回 datatable吗
因为需要实现的是一个报表功能
列是按照 用户 在界面上选择的 列 来自动生成的
所以每次出来的数据部固定 无法使用 实体类型来 映射
设计器并没有提供对返回值不是实体（比如无返回值和标量返回值）的存储过程的自动生成
求大神给点方案
设计器并没有提供对返回值不是实体（比如无返回值和标量返回值）的存储过程的自动生成。
.net framework 4.0
存储过程如下：
ALTER Proc BroadBand_GetReports
(
@CableID VarChar(200),
@WorkStationID INT,
@StartTime datetime,
@EndTime datetime
)
as
--BEGIN REPORT
--1. select data
select r.ID,
r.CableID,
r.SubCabelID,
r.ProductCode,
r.WorkStationID,
r.DataElementID,
r.DataElementValue,
r.CreateUser,
r.ChangeUser,
r.ChangeDate,
r.CreateDate,
r.ShiftID,
r.Category,
r.Property1,
r.Property2,
r.Property3,
r.Property4,
r.Property5,
w.WorkStationName + e.DataElementName AS element
into #tempnpi
from RoutingWorkstationDataElement r,
DataElement e,
WorkStation w
where r.DataElementID=e.DataElementID and r.WorkStationID = w.ID
and r.changeDate between '2012-07-10' AND '2012-07-12'
--2. summary multi times to only one
DECLARE @DataElementID INT, @DataElementValue Varchar(500)
DECLARE Element_Cursor CURSOR FOR
SELECT CableID, WorkStationID, DataElementID, DataElementValue from #tempnpi
OPEN Element_Cursor
FETCH NEXT FROM Element_Cursor INTO @CableID, @WorkStationID, @DataElementID, @DataElementValue
WHILE @@FETCH_STATUS = 0
BEGIN
update #tempnpi
set DataElementValue = DataElementValue + '; ' + (select DataElementValue from #tempnpi where CableID=@CableID and workstationid=@WorkStationID
and DataElementID = @DataElementID )
where CableID=@CableID and workstationid=@WorkStationID and DataElementID = @DataElementID
and DataElementValue = ''
FETCH NEXT FROM Element_Cursor INTO @CableID, @WorkStationID, @DataElementID, @DataElementValue
END
CLOSE Element_Cursor
DEALLOCATE Element_Cursor
--3. pivot
DECLARE @Eachfield NvarChar(200)
DECLARE @WorkStationIDNoMean INT
DECLARE @Fields NvarChar(MAX)
SET @Fields = ''
DECLARE FILEDS_CURSOR CURSOR FOR
SELECT DISTINCT workstationid, Element From #tempnpi Order by Workstationid
OPEN FILEDS_CURSOR
FETCH NEXT FROM FILEDS_CURSOR INTO @WorkStationIDNoMean,@Eachfield
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Fields = @Fields + '[' + @Eachfield + '],'
FETCH NEXT FROM FILEDS_CURSOR INTO @WorkStationIDNoMean,@Eachfield
END
CLOSE FILEDS_CURSOR
DEALLOCATE FILEDS_CURSOR
DECLARE @SQL NvarChar(MAX)
SET @SQL =
'SELECT ProductCode,CableID , '+
SubString(@Fields,1, Len(@Fields)-1)+
'FROM
(SELECT ProductCode,CableID, ELEMENT, DataElementValue
FROM #tempnpi ) AS SourceTable
PIVOT
(
MAX(DataElementValue)
FOR ELEMENT IN ('+SubString(@Fields,1, Len(@Fields)-1)+
')) AS PivotTable'
EXEC (@SQL)
drop table #tempnpi
public DataTable GetReportsBySomething(string id)
{
DataTable dtList = new DataTable();
dtList.Columns.Add(new DataColumn("员工", typeof(string)));
dtList.Columns.Add(new DataColumn("使用时间", typeof(string)));
var work = GetWorkList(p=>条件);
foreach (var w in work)
{
DataRow drRow = dtList.NewRow();
drRow[0] = GetNameById(w.Key);
drRow[1] = w.TotalTime;
dtList.Rows.Add(drRow);
}
return dtList;
}
