LINQ - int Function in Linq To Sql - Asked By kshama parashar on 06-Aug-11 03:31 AM

Hello To All

I have a stored procedure to findout maxid by passing table name.
now problem is that when i m trying to using this SP by linq to sql not able to get the resultvalue .
because function return int value.
So how can i find result value.
My code is here

default.aspx.cs page

var idd = dt.maxid1("bankdetail");

DataClasses.designer.cs page

[Function(Name="dbo.maxid1")]
   public int maxid1([Parameter(DbType="NVarChar(MAX)")] string tblname)
   {
       IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), tblname);
       return ((int)(result.ReturnValue));
   }

My SP Is here

Create proc [dbo].[maxid1]
@tblname nvarchar(max)
as
declare @SQL nvarchar(max)
set @sql =('select isnull(max(id),0)+1 as id from '+ @tblname)
exec(@sql)
return

and Output is like this

Reena Jain replied to kshama parashar on 06-Aug-11 03:39 AM

Hi,

To return the value from stored procedure you need to use select keyword. so modify your stored procedure like this

Create proc [dbo].[maxid1]

@tblname nvarchar(max)

as

declare @SQL nvarchar(max)

set @sql =('select isnull(max(id),0)+1 as id from '+ @tblname)

select @sql

try this and let me know

kshama parashar replied to Reena Jain on 06-Aug-11 03:43 AM

if i replace exec to select

my output is like

kshama parashar replied to Reena Jain on 06-Aug-11 03:46 AM

if i replace exec to select

my output is like

Reena Jain replied to kshama parashar on 06-Aug-11 03:55 AM

Hi,

yes this is because you are using single quotes in around select query, so its treating as string. Just remove the single quotes to solve the problem.
try this stored procedure

Create proc [dbo].[maxid1]

@tblname nvarchar(max)

as

declare @SQL nvarchar(max)

select @sql=(isnull(max(id),0)+1) from + @tblname

try this and let me know

Reena Jain replied to kshama parashar on 06-Aug-11 03:56 AM

Hi,

you can get last Inserted Auto Value using below query

SELECT IDENT_CURRENT(‘tablename’)

It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

thanks

C# Code to get last Inserted AutoIncrement Value

protected void Button1_Click(object sender, EventArgs e)

{

SqlConnection con = new SqlConnection("Connection String");

con.Open();

SqlCommand comm = new SqlCommand("select IDENT_CURRENT('table1')", con);

int currentAutoIncrementValue = Convert.ToInt32(comm.ExecuteScalar());

con.Close();

/* Do Anything With value */

}

Hope this will help you