在c#中使用linq执行存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17289860/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Executing stored procedure using linq in c#
提问by chaudrhy
I made a stored procedure in sql server 2008 which gives me the changes made to a table. I am using Linq to SQL to use this table in C#. my stored procedure is
我在 sql server 2008 中做了一个存储过程，它给了我对表所做的更改。我正在使用 Linq to SQL 在 C# 中使用这个表。我的存储过程是
CREATE PROCEDURE dbo.getlog
-- Add the parameters for the stored procedure here
@p1 int = 0,
@p2 int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @from_lsn binary(10), @to_lsn binary(10)
SET @from_lsn =
sys.fn_cdc_get_min_lsn('dbo_User_Info')
SET @to_lsn = sys.fn_cdc_get_max_lsn()
SELECT ID_number, Name, Age FROM cdc.fn_cdc_get_all_changes_dbo_User_Info
(@from_lsn, @to_lsn, N'all');
END
GO
The above procedure runs fine in sql server. However when i run this statement using linq in C#
上述过程在 sql server 中运行良好。但是，当我在 C# 中使用 linq 运行此语句时
mytestDataContext obj = new mytestDataContext();
var test=obj.ExecuteCommand("dbo.getlog");
foreach( var abc in test)
{}
I get this error
我收到这个错误
Error 1 foreach statement cannot operate on variables of type 'int' because 'int' does not contain a public definition for 'GetEnumerator'
错误 1 foreach 语句无法对“int”类型的变量进行操作，因为“int”不包含“GetEnumerator”的公共定义
采纳答案by Soner G?nül
ExecuteCommandmethod returns Int32and you can't use magical foreachloop using a simple integer.
ExecuteCommand方法返回Int32并且您不能使用foreach使用简单整数的神奇循环。
Return Value
Type: System.Int32
The number of rows modified by the executed command.
I'm not too much familiar with DataContextclass but you can use DataContext.ExecuteQuerywhich returns IEnumerable<TResult>and you can use foreachloop with it.
我对DataContextclass不太熟悉，但您可以使用DataContext.ExecuteQuerywhich 返回IEnumerable<TResult>，也可以使用foreachloop 。
Return Value
Type: System.Collections.Generic.IEnumerable<TResult>
A collection of objects returned by the query.
查询返回的对象集合。
回答by Simon Whitehead
ExecuteCommandreturns an int.. not your results.
ExecuteCommand返回一个int.. 不是你的结果。
See MSDN here: http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.executecommand.aspx
请参阅此处的 MSDN：http: //msdn.microsoft.com/en-us/library/system.data.linq.datacontext.executecommand.aspx
public int ExecuteCommand(
string command,
params Object[] parameters
)
I think you're after ExecuteQuery.
我认为你在追求ExecuteQuery。
回答by DreamChild
I don't know why do you use foreach-statement, but method 'ExecuteCommand' returns int value, and foreach cycle need object that implements IEnumerable
我不知道你为什么使用 foreach 语句，但是方法 'ExecuteCommand' 返回 int 值，并且 foreach 循环需要实现 IEnumerable 的对象
回答by Zarepheth
I may be assuming too much, but if you are doing the C# with a recent version of Visual Studio, rather directly specifying the T-SQL call to run the stored procedure as string of literal text, you can drag and drop the stored procedure onto the LINQ to SQL modelling window. This will add it to the LINQ to SQL data context.
我可能假设太多了，但是如果您使用最新版本的 Visual Studio 执行 C#，而不是直接指定 T-SQL 调用以将存储过程作为文字文本字符串运行，则可以将存储过程拖放到LINQ to SQL 建模窗口。这会将其添加到 LINQ to SQL 数据上下文。
int param1 = 1;
int param2 = 2;
mytestDataContext obj = new mytestDataContext();
var test=obj.getlog(param1, param2);
foreach( var abc in test)
{
/* code inside loop */
}
The same technique can be used for calling user-defined functions.
相同的技术可用于调用用户定义的函数。
Doing this will reduce typing and provide intellisense to help with calling the stored procedures and SQL functions.
这样做将减少输入并提供智能感知来帮助调用存储过程和 SQL 函数。
回答by MrMins
You can use this library: https://github.com/mrmmins/C-StoreProcedureModelBinding
你可以使用这个库：https: //github.com/mrmmins/C-StoreProcedureModelBinding
Returns the values as a List, you only need create a simple class with the names and values types, like:
将值作为列表返回，您只需要创建一个具有名称和值类型的简单类，例如：
var productos = DataReaderT.ReadStoredProceadures<MyCustomModel>(myDbEntityInstance, "dbo.MySPName", _generic);
and MyCumtomModel class is something like:
和 MyCumtomModel 类是这样的：
public int id {get; set;}
public int salary {get; set;}
public string name {get; set;}
public string school {get; set;}
and generic like:
和通用像：
List<Generic> _generic = = new List<Generic>
{
new Generic
{
Key = "@phase", Type = SqlDbType.Int, Value = "207"
}
}
};
And now, your productshas the options like: products.First(), products.Count(), foreachetc.
而现在，你products有一个像的选项：products.First()，products.Count()，foreach等。
