Data Caching using LINQ
In a client server environment, it is very important to reduce the number of trips between the database and application. In order to achieve this is to get all the data in one trip and reuse the data for various other purposes.
One way is to cache the data and make it available for other transactions. There are multiple ways of caching data while programming within asp.net.
One way I employed for a high speed automotive data loader in asp.net is with the help of LINQ.
Although the technique is very simple, the results are tremendous in terms of speed of execution.
The need in my current project was to lookup descriptions for various codes that come from various tables. I created a stored procedure in SQL Server 2008 that returns data with IDs and Descriptions from these tables. Example of the procedure is below.
CREATE PROCEDURE [VehicleCodesLookUpSelect]
(@ExternalSource varchar(50) )
as
select * from Makes
select * from ModelCodes
select * from ModelLineCodes
Select * from TrimCodes
Select * from ColorCodes
Select * from OptionCodes
select * from dealercodemapping where ExternalSource = @ExternalSource
Then in the Function that I wrote I used LINQ on dataset that stores the data from the above procedure. I had to loop thru the vehicle feed file and while doing so had the need to lookup descriptions for various codes. Within the inner loop, I have something like below for a model code
Dim Modelquery = From Model In tblModels.AsEnumerable() _
Where Model.Field(Of String)("ModelCode") = dr("WENMDC").ToString And _
Model.Field(Of String)("MakeCode") = Makequery(0).MakeCode _
Select New With { _
.ModelCode = Model.Field(Of String)("ModelCode"), _
.ModelCodeDescription = Model.Field(Of String)("ModelCodedescription") _
}
In the above code tblModels is a data table that is derived from the dataset. Once I find a suitable match for the model I use it matching record as follows.
If Modelquery.Count > 0 Then
cmd.Parameters.AddWithValue("@Model", Modelquery(0).ModelCodeDescription).SqlDbType = SqlDbType.VarChar
Else
cmd.Parameters.AddWithValue("@Model", "N/A").SqlDbType = SqlDbType.VarChar
End If
I achieved about 60% gain while running this process on a remote database server.
I hope this article helps you understand the one of the uses of LINQ in available in .NET framework 3.5
Ananth
IntelliFusion Technologies