How to call the StoredProcedure with Linq that has Output parameters

Goldytech
Posted by Goldytech under LINQ category on | Views : 6026
This is storedprocdure code
CREATE PROCEDURE [dbo].[InsertCustomer]
(

@LastName varchar(50),
@FirstName varchar(50),
@Address varchar(50),
@City varchar(50),
@State char(2),
@ZIP varchar(10),
@CustomerID int OUTPUT,
@Modified timestamp OUTPUT
)
AS
SET NOCOUNT ON;

-- Inserts a Customer row
INSERT INTO Customer
( LastName, FirstName, Address, City, State, ZIP )
VALUES
( @LastName, @FirstName, @Address, @City, @State, @ZIP ) ;

IF @@ROWCOUNT>0 AND @@ERROR=0 -- Checks if the last statement produced an error
-- Selects the primay key value and the generated timestamp which is put back into the dataset
SELECT @CustomerID = CustomerID,
@Modified = Modified
FROM Customer
WHERE (CustomerID = SCOPE_IDENTITY());
RETURN


Now this is the LinqCode which you can call via LinqtoSQL context object


System.Nullable<int> CustomerId = 0;
System.Data.Linq.Binary lastChanged = null;
try
{
using (OMSDbDataContext ctx = new OMSDbDataContext())
{
ctx.InsertCustomer(
TxtLastName.Text.ToString(),
TxtFirstName.Text.ToString(),
TxtAddress.Text.ToString(),
TxtCity.Text.ToString(),
TxtState.Text.ToString(),
TxtZip.Text.ToString(),
ref CustomerId,
ref lastChanged);
}

}
catch (Exception ex)
{

throw new Exception(ex.Message);
}

finally
{
this.ClientScript.RegisterStartupScript(this.GetType(), "Success", "alert('Record Inserted with CustomerId = " + CustomerId + "');", true);
}

Comments or Responses

Posted by: Raja on: 8/17/2009 Level:Starter | Status: [Member]
Where is the stored procedure name in the LINQ code and what is OMSDbDataContext?
Posted by: Goldytech on: 8/17/2009 Level:Bronze | Status: [Member]
Hello Raja,
The stored proc name is InsertCustomer

and OMSDbDataContext is the datacontext class of .dbml file, which is LINQ to SQL classes.

Hope this helps

Login to post response