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)
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):
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):
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):
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):
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 !