Linq和SQL存储过程调用问题 [英] Linq and SQL stored procedure calling problem

问题描述

亲爱的团队，
我有一个存储过程...在SQL的存储过程代码下面.....我用Google搜索，但没有任何法定结果，我试图解决自己，但无法解决，最后我无奈地问了代码项目团队. ....此存储过程无需使用linq即可正常工作.我给出了如下代码小段

Dear Team,
I have a stored procedure...below the code of stored procedure in SQL.....I have googled but not any statisfied result and I tried to solve itself but could not solve and finaly i m helpless to ask the code project team.....this stored procedure is working fine without using linq.I have given small snippet of my code as follows

ALTER PROCEDURE [dbo].[SP_Get_Next_Id] 
	(
		@Table_Name varchar(40),
		@Column_Name varchar(40),  --From which column to get the MaxId
		@SQL_Ret int output
	)
AS
Declare @SQL_MAX nVarchar(100)
DECLARE @ParmDefinition NVARCHAR(500)

BEGIN
	SET NOCOUNT ON
	Begin
		set @SQL_MAX='Select @SQL_MAX_OUT=isnull(Max('+@Column_Name+'),0)+1 From '+@Table_Name
		
		SET @ParmDefinition = N'@SQL_MAX_OUT varchar(30) OUTPUT'

		exec sp_executesql @SQL_MAX,@ParmDefinition,@SQL_MAX_OUT=@SQL_Ret OUTPUT
	End
END

------- LINQ映射----

-------LINQ MAPPING----

using System.Data;
using System.Configuration;
using System.Linq;
using System.Data.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.Linq.Mapping;
using System.Reflection;
public class DB_DataContext:DataContext
{
    public DB_DataContext()
        : base(@"Data source=.\SQLEXPRESS;AttachDbFileName=|Datadirectory|\Data\Fleet_CMMS.MDF;
                             Integrated Security=True;User Instance=True")
    {}
    

    [Function(Name = "dbo.SP_Get_Next_Id")]
    public ISingleResult<int> SP_Get_Next_Id([Parameter(DbType = "Varchar(40)")] string TableName,
               [Parameter(DbType = "Varchar(40)")] string Column_Name,
                [Parameter(DbType = "int")] ref int SQL_Ret)
    {
        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), TableName, Column_Name, SQL_Ret);
        return (ISingleResult<int>)(result.ReturnValue);
    }
}

----呼叫代码----

----CALLING CODE----

int return_variable=0;
DB_DataContext Db_dc = new DB_DataContext();
Db_dc.SP_Get_Next_Id("CMMS_DEPOT_MASTER", "Depot_ID", ref return_variable);

它给出了错误

IT GIVES ERROR

System.InvalidOperationException: The type 'System.Int32' must declare a default (parameterless) constructor in order to be constructed during mapping. at System.Data.Linq.SqlClient.Translator.BuildProjectionInternal(SqlExpression item, MetaType rowType, IEnumerable`1 members, Boolean allowDeferred, SqlLink link, Expression source) at System.Data.Linq.SqlClient.Translator.BuildProjection(SqlExpression item, MetaType rowType, Boolean allowDeferred, SqlLink link, Expression source) at System.Data.Linq.SqlClient.QueryConverter.TranslateStoredProcedureCall(MethodCallExpression mce, MetaFunction function) at System.Data.Linq.SqlClient.QueryConverter.VisitMappedFunctionCall(MethodCallExpression mc) at System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc) at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node) at System.Data.Linq.SqlClient.QueryConverter.ConvertOuter(Expression node) at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(Expression query, SqlNodeAnnotations annotations) at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) at System.Data.Linq.DataContext.ExecuteMethodCall(Object instance, MethodInfo methodInfo, Object[] parameters) at DB_DataContext.SP_Get_Next_Id(String TableName, String Column_Name, Int32& SQL_Ret) in d:\sukhen\Projects\ASP.NET\Fleet_CMMS\App_Code\DB_DataContext.cs:line 34 at Forms_frmDepot.btnSave_Click(Object sender, EventArgs e) in d:\sukhen\Projects\ASP.NET\Fleet_CMMS\Forms\frmDepot.aspx.cs:line 68

[edit]已删除呼喊声-OriginalGriff [/edit]

[edit]SHOUTING removed - OriginalGriff[/edit]

推荐答案

更改行

int return_variable=0;

到

int? return_variable = 0;

这应该可以解决问题，如果不能解决，请告诉我们您何时收到此错误消息?

This should resolve the issue, if not please let us know at which point you are getting this error message ?

----- SQL Server 2005中的step1 -----

创建过程[dbo].[SP_Get_Next_Id1]
(
@Table_Name varchar(40)，
@Column_Name varchar(40)，-从哪一列获取MaxId
@SQL_Ret VARCHAR(40)输出
)
AS
声明@SQL_MAX nVarchar(100)
DECLARE @ParmDefinition NVARCHAR(500)
--DECLARE @Return_Value int
开始
-添加了SET NOCOUNT ON以防止产生额外的结果集
-干扰SELECT语句.
设置NOCOUNT ON
开始
--print``Seelct Max(''+ @ Column_Name +'')+ 1来自''+ @ Table_Name
设置@ SQL_MAX =''选择@ SQL_MAX_OUT = isnull(Max(''+ @ Column_Name +'')，0)+1来自''+ @ Table_Name
--set @ SQL_Ret = EXEC(@SQL_MAX)
SET @ParmDefinition = N''@ SQL_MAX_OUT varchar(40)输出''
exec sp_executesql @ SQL_MAX，@ ParmDefinition，@ SQL_MAX_OUT = @ SQL_Ret输出
结束
END

VS2008和框架3.5中的----- step2 ----

使用系统；
使用System.Data;
使用System.Configuration;
使用System.Linq;
使用System.Data.Linq;
使用System.Web;
使用System.Web.Security;
使用System.Web.UI;
使用System.Web.UI.HtmlControls;
使用System.Web.UI.WebControls;
使用System.Web.UI.WebControls.WebParts;
使用System.Xml.Linq;
使用System.Data.Linq.Mapping;
使用System.Reflection;
使用System.Windows.Forms;
使用System.Data.Linq.Mapping;

公共类DB_DataContext:DataContext
{
公共DB_DataContext()
:base(@数据源=.\ SQLEXPRESS; AttachDbFileName = |数据目录| \ Data \ Fleet_CMMS.MDF;
集成安全性= True；用户实例= True)
{}

[global :: System.Data.Linq.Mapping.FunctionAttribute(Name ="dbo.SP_Get_Next_Id1")]
公共字符串SP_Get_Next_Id1([global :: System.Data.Linq.Mapping.ParameterAttribute(Name ="Table_Name"，DbType ="VarChar(40)")]字符串table_Name，[global :: System.Data.Linq.Mapping.ParameterAttribute (Name ="Column_Name"，DbType ="VarChar(40)")]字符串column_Name，[global :: System.Data.Linq.Mapping.ParameterAttribute(Name ="SQL_Ret"，DbType ="VarChar(40)")]引用字符串SQL_Ret)
{
IExecuteResult结果= this.ExecuteMethodCall(this，(((MethodInfo)(MethodInfo.GetCurrentMethod()))，table_Name，column_Name，SQL_Ret);
SQL_Ret = Convert.ToString((result.GetParameterValue(2)));
return((string)(result.ReturnValue));
}
}


---- Step3 -----呼叫----
DB_DataContext Db_dc =新的DB_DataContext();
字符串strReturn_Var =";
Db_dc.SP_Get_Next_Id1("CMMS_DEPOT_MASTER"，仓库ID"，参考strReturn_Var);
//在数据库中，Depot_Id是一个自动编号和整数列，但我也使用varchar列对其进行了测试，但错误消息相同.

-----step1 in sql server 2005-----

CREATE PROCEDURE [dbo].[SP_Get_Next_Id1]
(
@Table_Name varchar(40),
@Column_Name varchar(40), --From which column to get the MaxId
@SQL_Ret VARCHAR(40) output
)
AS
Declare @SQL_MAX nVarchar(100)
DECLARE @ParmDefinition NVARCHAR(500)
--DECLARE @Return_Value int
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
Begin
--print ''Seelct Max(''+@Column_Name+'')+1 From ''+@Table_Name
set @SQL_MAX=''Select @SQL_MAX_OUT=isnull(Max(''+@Column_Name+''),0)+1 From ''+@Table_Name
--set @SQL_Ret=EXEC(@SQL_MAX)
SET @ParmDefinition = N''@SQL_MAX_OUT varchar(40) OUTPUT''
exec sp_executesql @SQL_MAX,@ParmDefinition,@SQL_MAX_OUT=@SQL_Ret OUTPUT
End
END

-----step2 in VS2008 and framework 3.5 ----

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Data.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.Linq.Mapping;
using System.Reflection;
using System.Windows.Forms;
using System.Data.Linq.Mapping;

public class DB_DataContext:DataContext
{
public DB_DataContext()
: base(@"Data source=.\SQLEXPRESS;AttachDbFileName=|Datadirectory|\Data\Fleet_CMMS.MDF;
Integrated Security=True;User Instance=True")
{}

[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.SP_Get_Next_Id1")]
public string SP_Get_Next_Id1([global::System.Data.Linq.Mapping.ParameterAttribute(Name="Table_Name", DbType="VarChar(40)")] string table_Name, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="Column_Name", DbType="VarChar(40)")] string column_Name, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="SQL_Ret", DbType="VarChar(40)")] ref string SQL_Ret)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), table_Name, column_Name, SQL_Ret);
SQL_Ret = Convert.ToString((result.GetParameterValue(2)));
return ((string)(result.ReturnValue));
}
}


----step3-----calling----
DB_DataContext Db_dc = new DB_DataContext();
String strReturn_Var = "";
Db_dc.SP_Get_Next_Id1("CMMS_DEPOT_MASTER", "Depot_ID", ref strReturn_Var);
//in the database Depot_Id is an autonumber and integer column but i test it with varchar columns as well but same error msg.

这篇关于Linq和SQL存储过程调用问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！