LINQ - Result Value get from Stored Procedure in Linq

Asked By kshama parashar on 05-Aug-11 02:41 AM
hello to all

I have a Stored Procedure of insert data in which i m checking the duplicasy of emailid if email id is already exist then show message like

Email Id Already Exist.

Otherwise insert data and show message like

Registration Successfully.

Here is my SP:

Create procedure [dbo].[insertuser]
@id int,
@uname nvarchar(max),
@pas nvarchar(max),
@name nvarchar(max),
@ponnumber nvarchar(max),
@add nvarchar(max),
@emailid nvarchar(max),
@regtime datetime
as
declare @ss as int
declare @msg as nvarchar(50)
set @ss=(select count(*) from ulogin where emailid=@emailid)

if @ss>0
begin
set @msg=('Email Address Already Exists.')
select @msg as sts
end
else
begin
insert into ulogin values(@id,@uname,@pas,@name,@ponnumber,@add,@emailid,@regtime)
set @msg=('Registration Sucessfully.')
select @msg as sts
end
return

I m using linq
now problem is that i m not able to retrieve the message in c#.

My c# code is here

  var aa= dt.insertuser(dd.id, txtuname.Text, txtpas.Text, txtname.Text, txtcnum.Text, txtadd.Text, txtemail.Text,DateTime.Now.Date).ToList();
     ScriptManager.RegisterStartupScript(this, typeof(Page), "1", "alert('"+aa.ToString()+"')", true);

i want message in var aa.
Help me.
Web Star replied to kshama parashar on 05-Aug-11 02:45 AM
you get return value form datacontext as follows

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:  }
TSN ... replied to kshama parashar on 05-Aug-11 02:49 AM
hi..

look at this sample..

When you write LINQ query expressions like this the LINQ to SQL ORM will execute the necessary dynamic SQL for you to retrieve Product objects that matches your query.

As you'll learn in this post, you can also optionally map SPROCs in the database to your LINQ to SQL DataContext class, which allows you to alternatively retrieve the same Product objects by calling a stored procedure instead:

 

This ability to use both dynamic SQL and SPROCs with a clean data model layer is pretty powerful, and provides a great deal of flexibility when working on projects.

The Steps to Map and Call a SPROC using LINQ to SQL

In my Part 2: Defining our Data Model Classes tutorial I discussed how to use the LINQ to SQL ORM designer to create a LINQ to SQL class model like below:

Notice above how there are two panes on the LINQ to SQL ORM designer surface.  The left pane enables us to define data model classes that map to our database.  The right method pane allows us to optionally map SPROCs (and UDFs) to our LINQ to SQL DataContext object, which we can then use in-place of dynamic SQL to populate the data model objects.

How to Map a SPROC to a LINQ to SQL DataContext

To map SPROCs to our DataContext class, let's first go to the VS 2008 Server Explorer window and look at the SPROCs within our database:

We can double click any of the SPROCs above to open and edit them.  For example, below is the "CustOrderHist" SPROC in Northwind:

To map the above SPROC to our LINQ to SQL DataContext, we can drag/drop it from the Server Explorer onto our LINQ to SQL ORM designer.  This will automatically create a new method on our LINQ to SQL DataContext class like below:

By default the method name created on the DataContext class will be the same as the SPROC name, and the return type of the method will be an automatically created type that follows the "[SprocName]Result" naming pattern. For example: the SPROC above would return a sequence of "CustOrderHistResult" objects.  We could optionally change the name of the method by selecting it in the designer and then use the property grid to rename it.

How to Call our Newly Mapped SPROC

Once we've done the steps above to map a SPROC onto our DataContext class, it is easy to use it to programmatically retrieve data.  All we need to-do is call the new method we mapped on our DataContext class to get back a sequence of strongly typed results from the SPROC:

Calling the SPROC in VB:

Calling the Sproc in C#:

In addition to programming looping over the result like in the code samples above, I could also obviously bind the results to any UI control to display them.  For example, the below code databinds the result of our SPROC to a <asp:gridview> control:

Which then displays the product history of our customer on a page like so:

TSN ... replied to kshama parashar on 05-Aug-11 03:09 AM
hi.....


check out the third case...
Case 1: With Output Parameter
CREATE PROCEDURE [dbo].[GetEmployeeCount]
    @OutVal DateTime OUTPUT
AS
BEGIN
    SELECT @OutVal = GetDate()
END
You need to write code which will look like,
using (TestDBDataContext db = new TestDBDataContext())
{
  //Need a Nullable type here
  //and you need to have some value to it
  DateTime? dt = null;
  var q = db.GetEmployeeCount(ref dt);

  Console.WriteLine(dt);         
}
Case 2: With Return (only for Integers)
CREATE PROCEDURE [dbo].[GetEmployeeCountRet]     
AS
BEGIN
    DECLARE @Ret INT
    SELECT @Ret = COUNT(*) FROM Emp
    RETURN @Ret
END
Your code may look like,
using (TestDBDataContext db = new TestDBDataContext())
{
  //For Stored Procedure with Return value (for Integer)
  //returns Int
  var q = db.GetEmployeeCountRet();

  Console.WriteLine(q);         
}
You cannot simply say
SELECT COUNT(*) FROM Emp and capture the value in a variable. Because in LINQ to SQL a Stored Procedure either returns ISingleResult  or IMultipleResults, so capturing single value becomes very tricky.
So when you have to do it go for Scalar-Valued function
Case 3: Using Scalar-Values Functions
ALTER FUNCTION [dbo].[fn_GetEmployeeCount]()
RETURNS int
AS
BEGIN
    DECLARE @ResultVar int
   
    SELECT @ResultVar = Count(*) FROM Emp
   
    RETURN @ResultVar
END
You code,
using (TestDBDataContext db = new TestDBDataContext())
{
  var q = db.fn_GetEmployeeCount();
  Console.WriteLine(q);         
}
harsh shah replied to kshama parashar on 05-Aug-11 03:14 AM
HI,

Try Below code

Here is my SP:

Create procedure [dbo].[insertuser]
@id int,
@uname nvarchar(max),
@pas nvarchar(max),
@name nvarchar(max),
@ponnumber nvarchar(max),
@add nvarchar(max),
@emailid nvarchar(max),
@regtime datetime,
@Msg   varchar(200) OUTPUT 
as



if ((select count(*) from ulogin where emailid=@emailid)>0)
begin
set @msg=('Email Address Already Exists.')
select @msg
end
else
begin
insert into ulogin values(@id,@uname,@pas,@name,@ponnumber,@add,@emailid,@regtime)
set @msg=('Registration Sucessfully.')
select @msg as sts
end
return

I m using linq
now problem is that i m not able to retrieve the message in c#.

My c# code is here

string Status="";
  var aa= dt.insertuser(dd.id, txtuname.Text, txtpas.Text, txtname.Text, txtcnum.Text, txtadd.Text, txtemail.Text,DateTime.Now.Date,ref Status).ToList();
   ScriptManager.RegisterStartupScript(this, typeof(Page), "1", "alert('"+aa.ToString()+"')", true);


inform me

Regards,

Harsh Shah