Linq and SQL stored procedure calling problem

Question

1.44/5 (2 votes)

See more:

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

SQL

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 MAPPING----

C#

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----

C#

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]SHOUTING removed - OriginalGriff[/edit]

Posted 8-Oct-11 20:26pm

sukhen dass

Updated 8-Oct-11 21:41pm

OriginalGriff

v6

Add a Solution

Comments

OriginalGriff 9-Oct-11 3:41am

DON'T SHOUT. Using all capitals is considered shouting on the internet, and rude (using all lower case is considered childish). Use proper capitalisation if you want to be taken seriously.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Bala Selvanayagam · Accepted Answer · 2011-10-08T22:10:00

Solution 1

Change the line

int return_variable=0;

to

int? return_variable = 0;

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

Posted 8-Oct-11 22:10pm

Bala Selvanayagam

Comments

sukhen dass 10-Oct-11 8:25am

Hi,bala thank u to give your precious time...I tried as per your solution but the same error msg and I changed in variable declaration "int? return_variable = 0;" and in the defined function "ref int? SQL_Ret" parameters as per the requirement to define the nullable
and error is at this line
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), TableName, Column_Name, SQL_Ret);

sukhen dass 12-Oct-11 5:28am

Bala pls,reply

Bala Selvanayagam 12-Oct-11 7:24am

I will have an another look today and come back to you

Bala Selvanayagam 12-Oct-11 10:50am

Replace your function by

[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.SP_Get_Next_Id")]
public int SP_Get_Next_Id([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="Int")] ref System.Nullable<int> sQL_Ret)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), table_Name, column_Name, sQL_Ret);
sQL_Ret = ((System.Nullable<int>)(result.GetParameterValue(2)));
return ((int)(result.ReturnValue));
}

and then int? return_variable = 0;

let me know the results

sukhen dass 13-Oct-11 5:39am

Hi,Bala thanks for your solution its working....But how to return string value rather than integer is it possible

Bala Selvanayagam 13-Oct-11 10:01am

METHOD
[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.SP_Get_Next_Id1")]
public int 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 = ((string)(result.GetParameterValue(2)));
return ((int)(result.ReturnValue));
}

CALLING..

DataClasses1DataContext db = new DataClasses1DataContext();
string a="";
db.SP_Get_Next_Id1("employees", "id", ref a);

Please note that i have used a different name fo the SP this time for the string return type...

SP_Get_Next_Id1

If you are happy with the answer please vote and accept the answer.

Many Thanks
Bala

sukhen dass 14-Oct-11 6:12am

---I TRIED THIS----
[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));
}

-----CALLING---------
String strReturn_Var = "";
Db_dc.SP_Get_Next_Id1("CMMS_DEPOT_MASTER", "Depot_ID", ref strReturn_Var);

---ERROR----
I tried but it is giving this error msg 'System.String' is not a valid return type for a mapped stored procedure method.

Bala Selvanayagam 14-Oct-11 7:22am

did you change your stored procedure to dbo.SP_Get_Next_Id1 ( note there is a number 1 at the end) and change the return type in the stored proceudre ?

sukhen dass 14-Oct-11 8:20am

I have changed it to....
ALTER 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

Bala Selvanayagam 14-Oct-11 9:26am

Just out of interested,

How do you do the stored procedure mapping in C#? There is an easyway to do that and infact ssytem will create all for you...looks you are missing something... could you tell me step by step as to how you map a stored procedure in C# front end ?

also what is the .NET frame work you are using ? based on your feed back i will try to come up with a simple step by step guide to do this easily ?

Bala Selvanayagam 15-Oct-11 5:04am

What i meant is not the source code,

Do you write the classes and mapping manually in c# or do you drag and drop the stored procedure in the *dbml to generate the code for you ?

sukhen dass 15-Oct-11 6:47am

mapping manually

Bala Selvanayagam 15-Oct-11 8:17am

You do not needs to manually...

1.Create a new windows application new project

2.Right click on the project solution->Add->New item and add a "Linq to Sql" class and by default the name will be something like this "DataClasses1.dbml"

.once added, you will be in the "DataClasses1.dbml" view and to the left hand side of this view you can see two tabs ( Tool box & Server explorer)...and click on the server explorer

3.There you can add a connection to the database server and once the connection is established you can expand the connection node and see the stord procedure you have created in the database...Drag the stored procedure you are interested from the server view and drop it in the *.dbml..

If you have noticed the *.dbml will have two sides...you make sureyou drop the stored procedure to the second side.

now you are ready to call your SP....and all the necessary codes are auto created

sukhen dass 15-Oct-11 10:20am

Bala..I can use this method but the problem is multiple will user use the different database but all tables and everything will we same.So,i dont think it would be better the design time environment so,I m prefering This method....What is your suggestion about this???

Bala Selvanayagam 17-Oct-11 4:17am

What do you mean by differenet database when it comes to multiple users...? Are you saying that you are going to use with different databases like Oracle, MySQl and Access ?

Or do you mean that you do not want the connection to be hard coded since when it comes to live environment the connection string to sql server could be different ?

sukhen dass 17-Oct-11 6:26am

I am developing this application for multiple Clients of Different Organizations.Databse will be same in SQL and location would be one that is our server only but because each organization will have their own organization data but database structure would be common for all and they will access their own database only.So,For different organization its connection string would be different.

Bala Selvanayagam 17-Oct-11 10:46am

It will not be a problem, you can still use automated way and change the connection string at run time for the clients indidually. I think this is the best way rather than creating the classes and mapping manually.

1.When you use the automated way, the system creates a file named "App.config" automatically and that is where the connection string is stored. When you compile the version for thje deplaoyment say for instance will say that the name of your *.exe is MyApp.exe. When you deploy this MyApp.exe you needs to have an XML file called MyApp.exe.config in the same deployment folder with the same content of App.config for your EXE to work and then you can open the MyApp.exe.config in a note pad a change the connection string to suite the deployment environment.

2.Other optiion is you can pass a connectin string as a parameter as shown below to over right the automated one

DataClasses1DataContext db = new DataClasses1DataContext("Data Source=SOLAP;Initial Catalog=Model;User ID=sa;Password=admin1990");
string out1="";
db.SP_Get_Next_Id1("employees", "id", ref out1);
MessageBox.Show(out1);
MessageBox.Show("OK");

sukhen dass 18-Oct-11 2:04am

Thank U Bala u helped me lot and thanks for your precious time.....its working....I will get back to u later if any problem comes....

Bala Selvanayagam 18-Oct-11 4:41am

Glad i was able to help

Cheers

sukhen dass · Accepted Answer · 2011-10-14T22:21:00

-----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.