Posts
208
Comments
1144
Trackbacks
51
LINQ to SQL - Return Scalar Value from a Stored Procedure

One of the best things about LINQ to SQL is that is really does have solid support for stored procedures.  However, it's not quite as friendly as dynamic LINQ to SQL with scalar values because you can't take advantage of anonymous types.  Instead you must return an actual known type.  Furthermore, this can't be a primitive so you can't return string or bool directly.  You also can't use a class that doesn't have a default (parameterless) constructor so that also rules out nullable types like Nullable<bool>.

Consider a trival stored procedure that looks like this:

   1:  CREATE PROCEDURE [dbo].[GetFirstName]
   2:      @ID int
   3:  AS
   4:  BEGIN
   5:      SELECT FirstName
   6:      FROM   Contacts
   7:      WHERE  ContactID = @id
   8:  END

We just want to return a single scalar string here. So we have to create a dummy class that looks like this:

   1:  public class DummyClass
   2:  {
   3:      public string FirstName { get; set; }
   4:  }

Then our DataContext method can look like this:

   1:  [Function(Name = "dbo.GetFirstName")]
   2:  public ISingleResult<DummyClass> GetFirstName(int id)
   3:  {
   4:      IExecuteResult result = this.ExecuteMethodCall(this, (MethodInfo)MethodInfo.GetCurrentMethod(), id);
   5:      return (ISingleResult<DummyClass>)result.ReturnValue;
   6:  }

And add to that, we'll need an extra consuming method that defensively checks for an empty resultset:

   1:  public static string GetFirstName(int id)
   2:  {
   3:      using (MyDataContext dataContext = new MyDataContext())
   4:      {
   5:          var result = dataContext.GetFirstName(id).FirstOrDefault();
   6:          if (result != null)
   7:          {
   8:              return result.FirstName;
   9:          }
  10:          else
  11:          {
  12:              return null;
  13:          }
  14:      }
  15:  }

So all this works just fine but it seems like a lot of work to go through just to get a simple scalar value from a stored procedure.  Not to mention that I don't want to have to go through this every single time I need a scalar value from a stored procedure in my app.  Visual Studio will essentially generate the DummyClass for me but what if I'm hand-crafting my LINQ to SQL and am not currently using Visual Studio code generation?  I don't want to have to go through that every time.  So what can we do to make this slightly more developer-friendly?

First off, I want to avoid code duplication if at all possible.  Not only if I return multiple scalars at different places in my app but also I'd prefer to avoid the duplication if these scalars are different data types (e.g., sometimes strings, sometimes Boolean, etc.).  What we can do is to create a simple generic class called Scalar that looks like this:

   1:  public class Scalar<T>
   2:  {
   3:      public T Value { get; set; }
   4:  }

This means I can re-write my DataContext method like this:

   1:  [Function(Name = "dbo.GetFirstName")]
   2:  public ISingleResult<Scalar<string>> GetFirstName(int id)
   3:  {
   4:      IExecuteResult result = this.ExecuteMethodCall(this, (MethodInfo)MethodInfo.GetCurrentMethod(), id);
   5:      return (ISingleResult<Scalar<string>>)result.ReturnValue;
   6:  }

So since I'm using generics here, I never have to write another dummy class again - regardless of whether I've got a string or Boolean or whatever.  I can always just re-use Scalar<T>.

Additionally, I had to write a consuming method that checked to see if DummyClass was null and if not return its value; otherwise return null.  Now that I know I'm always going to use the Scalar class for all my scalar queries, I can move this into a re-usable extension method like this:

   1:  public static T GetScalar<T>(this Scalar<T> scalar)
   2:  {
   3:      if (scalar != null)
   4:      {
   5:          return scalar.Value;
   6:      }
   7:      else
   8:      {
   9:          return default(T);
  10:      }
  11:  }

So basically, all I've had to do is create that trivial Scalar<T> class and that 1 extension method which consisted of 1 IF statement and now anytime I need a scalar value from a stored procedure in my app, it becomes as trivial as writing my data context method with the re-usable Scalar<T> and this:

   1:  public static string GetFirstName(int id)
   2:  {
   3:      using (MyDataContext dataContext = new MyDataContext())
   4:      {
   5:          return dataContext.GetFirstName(id).FirstOrDefault().GetScalar();
   6:      }
   7:  }

Now, the one catch - the one thing to notice here is that the property name in Scalar<T> is "Value".  That means that I'd have to change my stored procedure to alias my scalar data like this:

   1:  SELECT FirstName as Value

Although this is a downside, I believe it's well worth it in order to realize all the other benefits of quick implementation and avoiding duplicate and unnecessary code.

posted on Tuesday, July 8, 2008 10:55 PM Print
Comments
Gravatar
# re: LINQ to SQL - Return Scalar Value from a Stored Procedure
Oskar Austegard
7/30/2008 1:39 PM
Nice workaround, but it still smells. Seems to me you're solving the wrong problem, a better approach would be to extend the DataContext to allow executing the stored proc as a scalar in the first place. How you do that would be another thing, though...
Gravatar
# re: LINQ to SQL - Return Scalar Value from a Stored Procedure
Steve
7/30/2008 10:47 PM
Yeah, I'm totally with you. It is a "workaround" - not the ideal solution. Seems to be a limitation of LINQ with stored procedures though. Unless you are returning a scalar value as the return value of the stored procedure itself (which isn't really feasible for data types like Boolean, DateTime, etc.) then there really isn't a *great* answer that I know of.
Gravatar
# re: LINQ to SQL - Return Scalar Value from a Stored Procedure
Doug Shontz, Jr.
8/8/2008 3:24 PM
Maybe it's just because I'm a DBA and I came to LINQ from that end, but there is a much simpler way. I define an output parameter in the stored proc, and then use its reference inside of the code. Using your example:

Procedure :

CREATE PROCEDURE [dbo].[GetFirstName]
(@ID int, @FirstName varchar(50) output)
AS
BEGIN

SET NOCOUNT ON;

SELECT @FirstName = ISNULL(FirstName,'')
FROM Contacts
WHERE ContactID = @ID


Data Context : I admit I don't know how to do that. Being a 12-year DBA, I'm just learning C#. I just use the DBML designer and drag the proc to the panel. It will create a ref string in the .cs file.

Calling Code:

public static string GetFirstName(int id)
{
using (MyDataContext dataContext = new MyDataContext())
{
string FirstName;
var query = dataContext.GetFirstName(id, ref FirstName);
return FirstName;
}
}

This has worked for me with every type of scalar that I have tried so far. Hope this helps. I learned this from the following source : http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx

Gravatar
# re: LINQ to SQL - Return Scalar Value from a Stored Procedure
John W
10/14/2008 9:05 AM
Steve, interesting article for applying generics--thanks.

Doug, thanks for sharing a simplified solution to returning a scalar. That really helped me.
Gravatar
# re: LINQ to SQL - Return Scalar Value from a Stored Procedure
Quango
2/11/2009 9:34 AM
Why not code the SPROC as a function not a SPROC? It's much simpler to return a scalar value from a SPROC - more logical too.
Gravatar
# re: LINQ to SQL - Return Scalar Value from a Stored Procedure
JOHN PARLATO
4/9/2009 3:45 PM
this.ExecuteMethodCall(this, (MethodInfo)MethodInfo.GetCurrentMethod(), id);

the code above is not being recognized by my intellesence... I have linq.data included; but I don't get the ExecuteMethodCall on my this? What am I missing here?
Gravatar
# re: LINQ to SQL - Return Scalar Value from a Stored Procedure
Steve
4/9/2009 5:11 PM
@John - I'd have to see all the code to be able to answer that. My suspicion is that you're not inheriting from DataContext.
Gravatar
# re: LINQ to SQL - Return Scalar Value from a Stored Procedure
Sam
10/19/2009 4:13 PM
Thanks a lot! After lots of headaches I found this post! Hard to find this article though! Shame on google.

Works great, except the compiler won't allow extension methods on generic non-static classes:

'extension methods must be defined in a non-generic static class'
Gravatar
# re: LINQ to SQL - Return Scalar Value from a Stored Procedure
Lee
12/7/2009 10:58 AM
@Doug - THANK YOU!!! I too come from the DB side of this game and this comment solved my day old problem!!! Thanks!!
Gravatar
# re: LINQ to SQL - Return Scalar Value from a Stored Procedure
Neevan
2/19/2010 4:48 AM
my .dbml is behaving very strangely. In my dbml designer.cs file i have procedure similar to below 1



[Function(Name="dbo.proc_sel_ReportDetails")]{




public ISingleResult<proc_sel_ReportDetailsResult> proc_sel_ReportDetails([Parameter(Name="Report_ID", DbType="Int")] System.Nullable<int> report_ID, [Parameter(Name="Source_ID", DbType="Int")] System.Nullable<int> source_ID, [Parameter(Name="Server", DbType="VarChar(50)")] string server)IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), report_ID, source_ID, server);return ((ISingleResult<proc_sel_ReportDetailsResult>)(result.ReturnValue));}

Some times it fetches records from the DB. sometimes it returns record with null values.(that proc returns records when i execute the same in DB).

What is causing the isssue. Please help me out.

your help is highly appreciated.

Gravatar
# re: LINQ to SQL - Return Scalar Value from a Stored Procedure
Steve
2/19/2010 2:32 PM
@Neevan - that is strange. Have you debugged your L2S code by attaching to the Log property of the data context? This will show you all the SQL that is emitted from L2S while it's running and you can run it directly against SQL. That is what I would start with if I were you.
Gravatar
# re: LINQ to SQL - Return Scalar Value from a Stored Procedure
Nick Olsen
7/28/2010 6:01 PM
If you are trying to return a scalar why wouldn't you just use a function? Those can be added the the dbml as well and they return a scalar value. Any reason to use the method you suggested over a function?
Gravatar
# re: LINQ to SQL - Return Scalar Value from a Stored Procedure
Steve
8/6/2010 5:20 PM
@Nick - Using a function is a good solution too (probably a *better* solution). I think when I posted this (2 years ago) I was working with a database where the stored procedure had *already* been created this way and I was integrating LINQ to SQL with it.
Gravatar
# re: LINQ to SQL - Return Scalar Value from a Stored Procedure
nima dilmaghani
12/18/2010 12:04 PM
You may need to use sored procedures. For example, if you are doing inserts, updates, or deletes, functions will not work. And you may want to return the number of records affected.
Gravatar
# re: LINQ to SQL - Return Scalar Value from a Stored Procedure
cant get sql column in text box
6/30/2012 5:44 AM
ALTER PROCEDURE [dbo].[Transaction]
(

@Patient nvarchar(50),
@E_TO nvarchar(50),
@R_type int,
@User_id uniqueidentifier,
@ReportType nvarchar(50),
@Patient_no int,
@Patient_ID_NO numeric(18,0),
@idcount int output

)
AS
BEGIN

declare @tempid int
set @tempid = 0;
declare @idcnt int
select @idcnt =isnull( max(idcount),0) from Transactions where year(R_date)=year(getdate())
if (@idcnt =0)
set @tempid=1
else
set @tempid = @idcnt +1


INSERT INTO dbo.Transactions (Patient,E_TO,R_date,R_from,User_id,report_type,Patient_no,Patient_ID_NO,idcount)values (@Patient,@E_TO,getdate(),@R_type,@User_id,@ReportType,@Patient_no,@Patient_ID_NO,@tempid)
s
SELECT idcount

FROM Transactions
WHERE idcount = @idcount
END
Gravatar
# re: LINQ to SQL - cant get the idcount in text box ,..
abdul
6/30/2012 5:47 AM
i want to display idcount in textbox..i am unable to find the solution
ALTER PROCEDURE [dbo].[Transaction]
(

@Patient nvarchar(50),
@E_TO nvarchar(50),
@R_type int,
@User_id uniqueidentifier,
@ReportType nvarchar(50),
@Patient_no int,
@Patient_ID_NO numeric(18,0),
@idcount int output

)
AS
BEGIN

declare @tempid int
set @tempid = 0;
declare @idcnt int
select @idcnt =isnull( max(idcount),0) from Transactions where year(R_date)=year(getdate())
if (@idcnt =0)
set @tempid=1
else
set @tempid = @idcnt +1


INSERT INTO dbo.Transactions (Patient,E_TO,R_date,R_from,User_id,report_type,Patient_no,Patient_ID_NO,idcount)values (@Patient,@E_TO,getdate(),@R_type,@User_id,@ReportType,@Patient_no,@Patient_ID_NO,@tempid)
SELECT idcount

FROM Transactions
WHERE idcount = @idcount
end
Gravatar
# re: LINQ to SQL - Return Scalar Value from a Stored Procedure
Nhat Nguyen
7/3/2013 12:39 PM
I have store
alter proc [dbo].[BKGetInvoiceNew]
(
@Control int,
@checkno nvarchar(20),
@RVC nvarchar(10),
@FormDay nvarchar(15),
@ToDay nvarchar(15)
)
AS
BEGIN
declare @sql nvarchar(2000)

set @sql ='SELECT CheckNo,TableID,TotalBase,SubTotal,TaxTotal,Discount,STaxTotal,CloseBy,NGuest,CloseTime,PONumber,PaymentMode,PaymentInfo,NChild,TableNo
from SMILE_POS.dbo.Invoice where 1=1'

if @checkno <>''
set @sql = @sql + 'and Checkno ='+@checkno+''

if @RVC <>''
set @sql =@sql + ' and RVC ='+@RVC+''

if @FormDay <>'' and @ToDay<>''
set @sql = @sql+' Convert(DateTime,Cast(IMonth as varchar) + ''/'' + Cast(IDay as varchar) + ''/'' + Cast(IYear as varchar),101) between ''' + cast(@FormDay as varchar) +''' And ''' + cast(@ToDay as varchar)+ ''
--PRINT @sql

EXECUTE (@sql)


end
How to call this store for code LIN Q
Gravatar
# re: LINQ to SQL - Return Scalar Value from a Stored Procedure
Nhat Nguyen
7/3/2013 12:49 PM
I have Store SQL :
alter proc [dbo].[BKGetInvoiceNew]
(
@Control int,
@checkno nvarchar(20),
@RVC nvarchar(10),
@FormDay nvarchar(15),
@ToDay nvarchar(15)
)
AS
BEGIN
declare @sql nvarchar(2000)

set @sql =’SELECT CheckNo,TableID,TotalBase,SubTotal,TaxTotal,Discount,STaxTotal,CloseBy,NGuest,CloseTime,PONumber,PaymentMode,PaymentInfo,NChild,TableNo
from SMILE_POS.dbo.Invoice where 1=1′

if @checkno ”
set @sql = @sql + ‘and Checkno =’+@checkno+”

if @RVC ”
set @sql =@sql + ‘ and RVC =’+@RVC+”

if @FormDay ” and @ToDay”
set @sql = @sql+’ Convert(DateTime,Cast(IMonth as varchar) + ”/” + Cast(IDay as varchar) + ”/” + Cast(IYear as varchar),101) between ”’ + cast(@FormDay as varchar) +”’ And ”’ + cast(@ToDay as varchar)+ ”
–PRINT @sql

EXECUTE (@sql)

end

I can not call this store SQL in winform, can you help me call this store? Thanks

Post Comment

Title *
Name *
Email
Comment *  
Verification

View Steve Michelotti's profile on LinkedIn

profile for Steve Michelotti at Stack Overflow, Q&A for professional and enthusiast programmers




Google My Blog

Tag Cloud