在商店程序中传递参数问题 [英] passing arguments problem in store procedure

问题描述

我使用linq的三层架构：



i使用这样的一个商店程序：

i use three tier architecture with linq:

i use one store procedure like this:

USE [BrandMutual]
GO
/****** Object:  StoredProcedure [dbo].[SP_Product_Get]    Script Date: 03/02/2013 14:46:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_Product_Get]
(
			 @id int=0,
                   @Action nvarchar(MAX)='''',
                  @category nvarchar(50)=''''
		
)
As
SET NOCOUNT ON
if(@id=0)
Begin
         Select * From tbl_admin_product_add
		
                     
End


if(@id>0 AND  @Action=''S1'' )

Begin
 Select * From tbl_admin_product_add
 where 
  id= @id
  
  E
if(@id>0 AND  @Action=''S2'' )
Begin
 Select * From tbl_admin_product_add
 where 
  category = @category 
  
  End


SET NOCOUNT OFF

和函数在BLL像这样：

LinqConnectionDataContext li = new LinqConnectionDataContext（）;

And a function At BLL Like This:
LinqConnectionDataContext li = new LinqConnectionDataContext();

public IEnumerable<tbl_admin_product_add> GetProductDetails()
   {
 int id=0;

       var v = li.SP_Product_Get(id);

       return (from c in v
               select new tbl_admin_product_add()
               {
                   id = c.id,
                   category = c.category,

               }
              ).ToList();


   }

因此它会生成错误：方法没有重载' 'SP_Product_Get''需要1个参数



如果我通过三个参数那么它运作良好，







所以我想要，根据我的条件，我传递任何参数，存储过程aromatically取其他参数NULL和不生成错误

so it generate Error That: No overload for method ''SP_Product_Get'' takes 1 arguments

And If I pass Three arguments then it work well,



so i want that, according to my condition, i pass whatever parameters, the store procedure aromatically take other arguments NULL and Not Generate errorr

推荐答案

LINQ To SQL ，将始终将存储过程定义转换为命名参数方法签名。因此，您必须传递默认值。



无论如何，你可以避免这种情况。您只需将Named参数签名更改为可选方法签名。当您拖放存储过程时，框架将在上下文的designer.cs文件中生成方法签名。所以，只需去那里并为可选参数分配默认值。请参阅命名和可选参数（C＃编程指南） [ ^ ]了解更多信息。



通常，不建议修改系统生成的代码。所以，你可以编写自己的自定义上下文类。请参阅下面有关如何生成自定义上下文类的文章，通过它您可以解决问题。

使用LINQ to SQL的存储过程可选参数 [ ^ ]

或

使用LINQ to SQL的存储过程可选参数 [ ^ ]



希望它有所帮助。

Hi,

LINQ To SQL, will always convert the stored procedure definition into a Named Parameter method signature. So, you must pass the default values.

Anyway, you can avoid this. you just have change the Named parameter signature to optional method signature. when you drag and drop the stored procedure, the framework will generate the method signature in the designer.cs file of the context. so, just go there and assign the default values for the optional parameters. refer Named and Optional Arguments (C# Programming Guide)[^] for more.

Normally, it is not advisable to modify the system generated code. so, better you can write your own custom context class. refer below articles on how to generate custom context class, through which you solve your problem.
Stored Procedure Optional Parameters using LINQ to SQL[^]
or
Stored Procedure Optional Parameters using LINQ to SQL[^]

hope it helps.

这篇关于在商店程序中传递参数问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！