Friday, July 31, 2009

Using partial class, when working with LINQ to SQL Stored Procedures

Working with LINQ to SQL we often use stored procedures, written by ourselves or some other developers. In this article I will explain how you can combine using of your own classes to DataContext (dbml file) and partial DataContext class, when working with LINQ to SQL Stored Procedure.

A stored procedure could be simple enough (for our understanding), but not so simple for auto-generated code (may be it's better to say, that auto-generated code just does not generate a return type, which we want to see, or just does not see/recognize, that our stored procedure return any type). For example, we have very simple table (fig. 1)

01.JPG

Fig. 1.

and some stored procedure, that we use for getting one or all records from this table:

ALTER PROCEDURE dbo.usp_Site

@Site smallint = -999

AS

select

SiteNum,

SiteName,

LastUpdate

from dbo.NC_Site

where

SiteNum =

case

when @Site = -999 then

SiteNum

else (@Site)

end

Now, if we drag and drop this stored procedure from the Server Explorer onto our class NC_Site (fig. 1) of our LINQ designer, we get auto-generated method like that:

[Function(Name="dbo.usp_Site")]

public ISingleResult<NC_Site> usp_Site([Parameter(Name="Site",

DbType="SmallInt")] System.Nullable<short> site)

{

IExecuteResult result = this.ExecuteMethodCall(this,

((MethodInfo)(MethodInfo.GetCurrentMethod())), site);

return ((ISingleResult<NC_Site>)(result.ReturnValue));

}

and our stored procedure returns strong type NC_Site (fig. 2):


02.JPG

Fig. 2.

All is very fine and we very easy can use our method in our project, etc.

But, in our life we often use very complication stored procedures (for some reports, etc.), which contain many different tables and many different cunnings and ways to get needed information. Let's very little change our stored procedure, that in our traditional programming life (without LINQ) will not affect use of procedure:

ALTER PROCEDURE dbo.usp_Site

@Site smallint = -999

AS

select

SiteNum,

SiteName,

LastUpdate

into #t --change

from dbo.NC_Site

where

SiteNum =

case

when @Site = -999 then

SiteNum

else (@Site)

end

select * from #t --change

drop table #t --change

First of all, now we cannot drop stored procedure onto our class NC_Site (fig. 3):


03.JPG

Fig. 3.

We just drop it on the designer. Now, our auto-generated method looks so:

[Function(Name="dbo.usp_Site")]

public int usp_Site([Parameter(Name="Site",

DbType="SmallInt")] System.Nullable<short> site)

{

IExecuteResult result = this.ExecuteMethodCall(this,

((MethodInfo)(MethodInfo.GetCurrentMethod())), site);

return ((int)(result.ReturnValue));

}

and return type is just (None) (fig. 4):


04.JPG

Fig. 4.

In this case we will be helped by our own classes, partial classes and (if there is need) the IMultipleResults Interface.

OK! The first step is creating our own classes (or class), that correspond to all possible output results of our stored procedure. Then, we create partial class (with the name of the DataContext designer), add method, that we need (it can be method with multiple results) and, at last, we create all methods, that help to
retrieve data (methods of our business logic, web services, etc. ).

Let's assume, that we have some stored procedure and web service, that help us to retrieve data. The stored procedure allows to get two query results (it depends on input parameter @shape); and, of course, we have a little complicated a situation with the temporary tables #t1 and #t2 :

ALTER PROCEDURE [dbo].[usp_OurExample]

(

@shape int = 1,

@ReligionId smallint = -999
)

AS

BEGIN

SET NOCOUNT ON

if (@shape = 1)

begin

select *

into #t1 from dbo.T_Religion

where

ReligionId =

(

case

when @ReligionId = -999 then ReligionId

else @ReligionId

end

);

select * from #t1;

drop table #t1;

end

else if (@shape = 2)

begin

select

SiteId,

SiteName

into #t2 from dbo.T_Site;

select * from #t2;

drop table #t2;

end

END;

As we can see, the first result is "select * " and, therefore there is no need to create special class: it is just T_Religion class, which corresponds (reflects) to the table T_Religion and it can be created by "drag/drop" operation. For the second result we create our own class Site (again, we can create very complicated class with the properties/fields, corresponding to different tables with different joins):


05.JPG

Fig. 5.

Now we can create partial class for our .desinger.cs. For example, if we have dbml file and this file has name ReportDC.dbml, we create a partial class with the name ReportDCDataContext. Then we add a method with a multiple result:


See full detail: http://www.c-sharpcorner.com/UploadFile/LivMic/LinqPartialStoredP07302009025236AM/LinqPartialStoredP.aspx

No comments: