Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
BRH 12
#DOTNET 10
#LINQ 6
LINQ 5
#ASP.NET 4
#DATAACCESS 4
ASP.NET 3
DOTNET 3
.NET 2
WCF 2

Archive · View All
July 2010 5
August 2010 4
June 2011 3
October 2010 1

How to get multiple result set of procedure using LINQ to SQL

Jul 17 2010 10:32PM by IBhadelia   

There always be case where one procedure returns more then one result set. Getting those data in DataSet is lazy way of coding, best way to do that is using DataReader. DataReader having method call NextResult which allows us to read next result set if any.

First we will see how we were doing this in ADO.NET using DataReader and then we will see how we can achieve this using LINQ to SQL.

SqlCommand cmd = new SqlCommand(); //Your command object
IDataReader idr = cmd.ExecuteReader();

if(idr.NextResult())
{
    while (idr.Read())
    {
        //Read the data reader
    }
}

Now let see how we can read multiple result set using LINQ. In general scenario when you drag and drop procedure in dbml its always returns ISingleResult and only consider first result, based of first result it will create data contracts.

Lets create procedure retuning multiple results.

CREATE PROCEDURE GetCustAndEmp
AS
BEGIN
	SELECT TOP 10 * FROM Customers
	SELECT TOP 10 * FROM Employees
END

When you drag and drop this procedure you can see function in designer.cs file which look like following

image

You can see it generates ISignleResult result and having only single entity which is GetCustAndEmpResult, and when we see the definition of GetCustAndEmpResult class we can see that it only consider customer results and exclude employee result.

image

To get  multiple results we have to modify methods and have to add some more decorations. First will remove GetCustAndEmp procedure from dbml by just deleting that form method panel as we are going to create that method manually. The method should be in same namespace and with same class, If we put it in designer.cs then each time we refresh the model we have to write our modified method again. We can use Partial Class to avoid this condition, partial class will be across file but same scope. You can read more here on Partial Class.

So will create new file having same class name and added partial keyword, this you can do by pressing F7 on our dbml.

image

We will now place our method with some modification to work with multiple results.

partial class NorthwindModelDataContext
{
    [FunctionAttribute(Name = "dbo.GetCustAndEmp")]
    [ResultType(typeof(Employee))]
    [ResultType(typeof(Customer))]
    public IMultipleResults GetCustAndEmp()
    {
        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
        return (IMultipleResults)(result.ReturnValue);
    }
}

Few modification to this, we have added two decorations to specify type of result that method is expecting. We have added Employee and Customer as ResultType as our procedure is returning these two result sets, this will going to use by MethodInfo in order to return IMultipleResults. In addition to this we have to change return type, it was before ISingleResult now we have to make it IMultipleResults. Now our method is ready to return multiple results. Let’s see how we can get the results form IMultipleResults.

using (NorthwindModelDataContext context = new NorthwindModelDataContext())
{
    var result = context.GetCustAndEmp();
    List<Customer> customers = result.GetResult<Customer>().ToList();
    List<Employee> employees = result.GetResult<Employee>().ToList();

    Console.WriteLine("Customers : {0}, Employees : {1}", customers.Count, employees.Count);
}

And here is the output

image

If you see Class which are in ResultType you can find there are few decorations on property and some events are defined and lots of code. In our case we are having already exists class which is generated by dbml. What if you want to return some transfer object? Yes you have to create new class with those decoration and events!! Opps not good correct?

We have alternative which is nice and we don’t have to bother about all these decorations and properties. Well this is hack not proper solutions. As we know dbml is generating class for first result found in procedure, so we can generate dbml by keeping our required result first until we have all classes with us. We have to keep these classes separate so when you remove method each time it will not get destroy. At the end this process you will have bunch of new classes which you have to add in newly created file. I said file file not class, that means it should reside out of partial class not inside. We will see that quickly

Our changed procedure

CREATE PROCEDURE GetCustAndEmp
AS
BEGIN
	SELECT TOP 10 ContactName, CompanyName FROM Customers
	SELECT TOP 10 FirstName, LastName, Title FROM Employees
END

You can see now it’s returning transfer object not whole table. When we refresh dbml it will add our procedure it will add class having ContactName and CompanyName, will copy that class change name and add into class separate.

image

Now we will change the sequence of our SQL statements in procedure

CREATE PROCEDURE GetCustAndEmp
AS
BEGIN
	SELECT TOP 10 FirstName, LastName, Title FROM Employees
	SELECT TOP 10 ContactName, CompanyName FROM Customers
END

Will do same process to generate our own class.

image

Now we are all set to write our method which returns IMultipleResults. Let see the change on GetCustAndEmp method.

[FunctionAttribute(Name = "dbo.GetCustAndEmp")]
[ResultType(typeof(EmployeeDTO))]
[ResultType(typeof(CompanyDTO))]
public IMultipleResults GetCustAndEmp()
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
    return (IMultipleResults)(result.ReturnValue);
}

Calling of this will be same as older one.

using (NorthwindModelDataContext context = new NorthwindModelDataContext())
{
    var result = context.GetCustAndEmp();
    List<CompanyDTO> customers = result.GetResult<CompanyDTO>().ToList();
    List<EmployeeDTO> employees = result.GetResult<EmployeeDTO>().ToList();

    Console.WriteLine("Customers : {0}, Employees : {1}", customers.Count, employees.Count);
}

Tags: #DOTNET, IMultipleResults, LINQ, #DATAACCESS, #LINQ, BRH,


IBhadelia
163 · 1% · 299
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]