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: 

 

public partial class ReportDCDataContext //TheSameNameAsDesignerDataContext

{

 

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

    [ResultType(typeof(T_Religion))]

    [ResultType(typeof(Site))]

    public IMultipleResults GetOurExampleShape(

        [Parameter(Name = "shape", DbType = "Int")]

        System.Nullable<int> Shape,

        [Parameter(Name = "ReligionId", DbType = "SmallInt")]

        System.Nullable<short> religionId)

    {

        IExecuteResult result = this.ExecuteMethodCall(this,

            ((MethodInfo)(MethodInfo.GetCurrentMethod())),

            Shape, religionId);

        return ((IMultipleResults)(result.ReturnValue));

    }

 

And the last step. To our BL class (for example, ReportsBL.cs) we add two methods:

 

public static List<T_Religion> GetReligionListBL(short? ReligionID)

{

    ReportDCDataContext dc = new ReportDCDataContext();

    IMultipleResults result =

        dc.GetOurExampleShape(1, ReligionID);

    List<T_Religion> lr = result.GetResult<T_Religion>().ToList();

    return lr;

}

 

public static List<Site> GetSiteListBL()

{

    ReportDCDataContext dc = new ReportDCDataContext();

    IMultipleResults result =

        dc.GetOurExampleShape (2,-999);

    List<Site> ls = result.GetResult<Site>().ToList();

    return ls;

}

 

and now to our asmx file (remember, we have decided, that we use the web service) we add web methods:

 

[WebMethod]

public List<Site> GetReportSite()

{

    return ReportsBL.GetSiteListBL();

}

 

[WebMethod]

public List<T_Religion> GetReportReligion(short? ReligionId)

{

    return ReportsBL.GetReligionListBL(ReligionId);

}

OK! Now we can use our web service and retrieve data (Site and/or Religion) in any application we want. 

Good luck in programming !