I'm working on an ASP.NET MVC project; my goal is to prepare a report from a table so, the first time I've wrote Linq code but it was too slow.
我正在开发一个ASP.NET MVC项目;我的目标是从表中准备一份报告,这是我第一次写Linq代码但速度太慢了。
And after that I've written a SQL query it was so fast and I want to use stored procedure for getting report data from my table. In fact my project is so easy: it gets two dates - start date and end date - and displays result in a table.
之后我编写了一个SQL查询,它的速度非常快,我想使用存储过程从我的表中获取报表数据。事实上,我的项目非常简单:它有两个日期 - 开始日期和结束日期 - 并在表格中显示结果。
I want to write my stored procedure to get two parameters - start date and end date - from C# code and then return output in a variable in C#.
我想编写我的存储过程以从C#代码获取两个参数 - 开始日期和结束日期,然后在C#中的变量中返回输出。
The first question: how to convert my SQL query to a stored procedure with two parameters, start Date and End date?
第一个问题:如何将我的SQL查询转换为具有两个参数的存储过程,开始日期和结束日期?
The second question: how to return output result in C#?
第二个问题:如何在C#中返回输出结果?
SELECT
CAST(date_rec_slash AS DATETIME), COUNT(code_marz) AS total,
CASE
WHEN code_marz = 1 THEN 'a'
WHEN code_marz = 2 THEN 'b'
WHEN code_marz = 3 THEN 'c'
WHEN code_marz = 4 THEN 'd'
WHEN code_marz = 5 THEN 'e'
END
FROM
dbo.tbl_bar
WHERE
CAST(date_rec_slash AS DATETIME) BETWEEN '2017/12/01' AND '2017/12/31'
GROUP BY
CAST(date_rec_slash AS DATETIME), code_marz
ORDER BY
CAST(date_rec_slash AS DATETIME) ASC;
C#:
C#:
var spResults = db.Database.SqlQuery<tbl_bar>("Report");
2 个解决方案
#1
3
Declare your store procedure using this syntax:
使用以下语法声明您的商店过程:
USE yourDataBaseNAme
GO
CREATE PROCEDURE [dbo].yourStoreProcedureName
@startDate nvarchar(30),
@endDate nvarchar(30)
AS
SELECT Cast(date_rec_slash as datetime) AS 'date_rec_slash', count(code_marz) as total,
CASE
WHEN code_marz = 1 THEN 'a'
WHEN code_marz = 2 THEN 'b'
WHEN code_marz = 3 THEN 'c'
WHEN code_marz = 4 THEN 'd'
WHEN code_marz = 5 THEN 'e'
END AS 'code_marz'
FROM dbo.tbl_bar
WHERE Cast(date_rec_slash as datetime) between @startDate
AND @endDate
GROUP BY Cast(date_rec_slash as datetime), code_marz
ORDER BY Cast(date_rec_slash as datetime) ASC;
GO
Call this store procedure in EF:
在EF中调用此存储过程:
db.Database.SqlQuery<yourObjectNameToCAST>("yourStoreProcedureName");
Call store procedure with parameter in EF:
使用EF中的参数调用存储过程:
SqlParameter startDate= new SqlParameter("@startDate", "Value");
SqlParameter endDate= new SqlParameter("@endDate", "Value");
db.Database.SqlQuery<yourObjectNameToCAST>("exec yourStoreProcedureName @startDate, @endDate", startDate, endDate).ToList();
your Object to Cast:
你的对象:
public class yourObjectNameToCAST
{
public datetime date_rec_slash { get; set; }
public int total { get; set; }
public string code_marz { get; set; }
}
#2
1
You can declare your stored procedures using
您可以使用声明存储过程
CREATE PROCEDURE [dbo].YourStoredProcedure
@Start DATETIME,
@END DATETIME
AS
Then you can get rid of the code needed to cast from string
然后你可以摆脱从字符串转换所需的代码
In order to get the results mapped as a c# object, you need to use SqlQuery
or FromSql
depending on the version of Entity Framework that you are using
为了将结果映射为c#对象,您需要使用SqlQuery或FromSql,具体取决于您使用的Entity Framework的版本
Entity Framework 6
实体框架6
var result = dbContext.Bar.SqlQuery("EXEC YourStoredProcedure").ToList();
To pass a parameter, you woild do something like
要传递参数,你可以做类似的事情
var result = dbContext.Bar.SqlQuery("EXEC YourStoredProcedure @SomeParameter",
new SqlParameter("@SomeParameter", TheParameterValue)).ToList();
And for Entity Framework Core 2
对于Entity Framework Core 2
var result = dbContext.Bar
.FromSql("EXEC YourStoredProcedure")
.ToList();
Where Bar
is your C# object declared as a property with type DbSet<Bar>
in your dbContext class
其中Bar是您的C#对象,在dbContext类中声明为类型为DbSet
Based on your output you should create a C# object similar to
根据您的输出,您应该创建一个类似于的C#对象
public class Bar
{
public datetime DateRecSlash { get; set; }
public int CodeMarz { get; set; }
}
#1
3
Declare your store procedure using this syntax:
使用以下语法声明您的商店过程:
USE yourDataBaseNAme
GO
CREATE PROCEDURE [dbo].yourStoreProcedureName
@startDate nvarchar(30),
@endDate nvarchar(30)
AS
SELECT Cast(date_rec_slash as datetime) AS 'date_rec_slash', count(code_marz) as total,
CASE
WHEN code_marz = 1 THEN 'a'
WHEN code_marz = 2 THEN 'b'
WHEN code_marz = 3 THEN 'c'
WHEN code_marz = 4 THEN 'd'
WHEN code_marz = 5 THEN 'e'
END AS 'code_marz'
FROM dbo.tbl_bar
WHERE Cast(date_rec_slash as datetime) between @startDate
AND @endDate
GROUP BY Cast(date_rec_slash as datetime), code_marz
ORDER BY Cast(date_rec_slash as datetime) ASC;
GO
Call this store procedure in EF:
在EF中调用此存储过程:
db.Database.SqlQuery<yourObjectNameToCAST>("yourStoreProcedureName");
Call store procedure with parameter in EF:
使用EF中的参数调用存储过程:
SqlParameter startDate= new SqlParameter("@startDate", "Value");
SqlParameter endDate= new SqlParameter("@endDate", "Value");
db.Database.SqlQuery<yourObjectNameToCAST>("exec yourStoreProcedureName @startDate, @endDate", startDate, endDate).ToList();
your Object to Cast:
你的对象:
public class yourObjectNameToCAST
{
public datetime date_rec_slash { get; set; }
public int total { get; set; }
public string code_marz { get; set; }
}
#2
1
You can declare your stored procedures using
您可以使用声明存储过程
CREATE PROCEDURE [dbo].YourStoredProcedure
@Start DATETIME,
@END DATETIME
AS
Then you can get rid of the code needed to cast from string
然后你可以摆脱从字符串转换所需的代码
In order to get the results mapped as a c# object, you need to use SqlQuery
or FromSql
depending on the version of Entity Framework that you are using
为了将结果映射为c#对象,您需要使用SqlQuery或FromSql,具体取决于您使用的Entity Framework的版本
Entity Framework 6
实体框架6
var result = dbContext.Bar.SqlQuery("EXEC YourStoredProcedure").ToList();
To pass a parameter, you woild do something like
要传递参数,你可以做类似的事情
var result = dbContext.Bar.SqlQuery("EXEC YourStoredProcedure @SomeParameter",
new SqlParameter("@SomeParameter", TheParameterValue)).ToList();
And for Entity Framework Core 2
对于Entity Framework Core 2
var result = dbContext.Bar
.FromSql("EXEC YourStoredProcedure")
.ToList();
Where Bar
is your C# object declared as a property with type DbSet<Bar>
in your dbContext class
其中Bar是您的C#对象,在dbContext类中声明为类型为DbSet
Based on your output you should create a C# object similar to
根据您的输出,您应该创建一个类似于的C#对象
public class Bar
{
public datetime DateRecSlash { get; set; }
public int CodeMarz { get; set; }
}