从LINQ访问动态创建的存储过程 [英] Accessing dynamically created stored procedure from LINQ

问题描述

我正在MS SQL存储过程中处理数据.使用存储过程参数(例如:"location1，location2，location3，")动态创建枢转的列，因此未知要生成的列数.输出应该看起来像(位置是从存储过程参数中获取的):

I'm pivoting data in MS SQL stored procedure. Columns which are pivoted are dynamically created using stored procedure parameter (for exampe: "location1,location2,location3,") so number of columns which will be generated is not known. Output should look like (where locations are taken from stored procedure parameter):

OrderTime |位置1 |位置2 | 位置3

OrderTime | Location1 | Location2 | Location3

是否有可能在LINQ to SQL中使用它?当我将此过程拖到dbml文件时，它表明该过程返回int类型.

Any chance that this can be used in LINQ to SQL? When I dragged this procedure to dbml file it shows that this procedure returns int type.

我从log_sales表中使用的列是:

• 位置(我正在旋转的不同位置)
• 收费(金额)
• OrderTime

存储过程:

CREATE PROCEDURE [dbo].[proc_StatsDay] @columns NVARCHAR(64) AS

DECLARE @SQL_PVT1 NVARCHAR(512), @SQL_PVT2 NVARCHAR(512), @SQL_FULL NVARCHAR(4000);

SET @SQL_PVT1 =  'SELECT OrderTime, ' + LEFT(@columns,LEN(@columns)-1) +'
FROM (SELECT ES.Location, CONVERT(varchar(10), ES.OrderTime, 120),ES.Charge
        FROM dbo.log_sales ES
        ) AS D (Location,OrderTime,Charge)
        PIVOT (SUM (D.Charge) FOR D.Location IN
            (';
SET @SQL_PVT2 = ') )AS PVT
ORDER BY OrderTime DESC';

SET @SQL_FULL = @SQL_PVT1 + LEFT(@columns,LEN(@columns)-1) + 
@SQL_PVT2;       

EXEC sp_executesql @SQL_FULL, N'@columns NVARCHAR(64)',@columns = @columns

在dbml designer.cs文件中，我的存储过程的代码部分:

In dbml designer.cs file my stored procedure part of code:

[Function(Name="dbo.proc_StatsDay")]
public int proc_EasyDay([Parameter(DbType="NVarChar(64)")] string columns)
{
    IExecuteResult result = this.ExecuteMethodCall(this,((MethodInfo)MethodInfo.GetCurrentMethod())), columns);
    return ((int)(result.ReturnValue));
}

推荐答案

假设真正可怕的动态需求，您可以使用

Assuming truly dire dynamic need, you could use DataContext.ExecuteQuery

只需编写一个将覆盖结果空间的类型(属性名称必须与查询中的列名称匹配):

Just whip up a type that will cover the result space (the property names must match the column names in the query):

public class DynamicResult
{
  public DateTime OrderDate {get;set;}
  public decimal? Location1 {get;set;}
  public decimal? Location2 {get;set;}
//..
  public decimal? Location100 {get;set;}
}

然后打电话

IEnumerable<DynamicResult> result =
  myDataContext.ExecuteQuery<DynamicResult>(commandString, param1);

这篇关于从LINQ访问动态创建的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！