CODEWITHSUNDEEP
c#sqlsql-serverlinqstored-procedures
yoshi0423
yoshi0423

Reputation: 245

LINQ to SQL - Mapping Stored Procedure with Multiple Results and using in webservice

In my current project, I am utilizing LINQ to pull results from a stored procedure with one dataset.

However, what I'm hoping to do is utilize LINQ to call a procedure with multiple datasets. So it will only be one call to SQL, with 3 results that I can work with, rather than calling 3 different procedures to get all my data.

I stumbled upon this http://www.codeproject.com/Tips/315667/LINQ-to-SQL-Mapping-Stored-Procedure-with-Multiple however, from my understanding, this would return one dataset based on which one I'm calling. My goal is reduce the number of database calls.

Here is how I currently do it. Not sure if it's possible to continue using this model to get the results I'm looking for, or if I have to utilize LINQ in a different way.

I utilize Visual Studio 2010 currently.

I have a .dbml file that links to a SQL database. I drag and drop Procedures from Server Explorer into the .dbml file. After that, I do not touch the .dmbl file. I let .NET handle it all.

I have a web service like this which will utilize that procedure to return that data.

    //TEST: mutliple dataset return
    [WebMethod]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public List<ps_UTY_TEST_MultipleDataSetResult> Return_multi_dataset()
    {
        InnoUtilityDataContext db = new InnoUtilityDataContext();
        List<ps_UTY_TEST_MultipleDataSetResult> d = new  List<ps_UTY_TEST_MultipleDataSetResult>();
        d = db.ps_UTY_TEST_MultipleDataSet().ToList();
        return d;
    }

This works great when returning a single datasets, but I want to return multiple dataset from a procedure such as this.

ALTER PROC [dbo].[ps_UTY_TEST_MultipleDataSet]
AS
   SELECT uUsername 
   FROM Common.dbo.tbluser 
   WHERE uUserName = 'username1'

   SELECT uFname 
   FROM Common.dbo.tbluser 
   WHERE uUserName = 'username2'

Is this possible?

Upvotes: 1

Views: 2862

Answers (1)

Vishal Patel
Vishal Patel

Reputation: 973

Below URL will help to you...

LINQ Stored Procedure return multiple dataset

Upvotes: 2

Related Questions