Home .NET Practicing LinqToSql: Using Linq to automate stored procedurecalls.

Practicing LinqToSql: Using Linq to automate stored procedurecalls.

by admin

The last few months of my work contributed to active study of LinqToSql technology. That’s why I want to tell you about my experience, it will be a series of small notes, which will probably help someone to understand the essence of the newconcept of data access, developed by Microsoft Company.

Using Linq to automate stored procedure calls.

The easiest way to take advantage of object access to data is to createa dbml databasemodel. But there are situations where performance is key and getting data fromSQL Server can only be done by a stored procedure. In thiscase, of course, we can use the Visual Studio designer. But, unfortunately, its functions of configuring call parameters of stored procedures are very limited. I wasvery annoyed by the fact that I could not setthe type of passed parameter manually.Taking a stored procedure :

1: create procedure sp_getItems(
2:
3: query xml — <query><id>12</id><id> 13</id> </query>
4:
5: ) as
6:
7: begin
8:
9: select [Id], [Name], [Description]
10:
11: from [Item] it
12:
13: inner join ( select t.c. value ( ‘.’ , bigint) [requestedid] from query nodes( ‘query/id’ ) t(c)) as query on query.requestedid = it.[id];
14:
15: end ;

By dragging it in the designer to the diagram, we getthisprototype :

1: public System.Data.Linq.ISingleResultsp_getItems(System.Xml.Linq. XElement query){… }

The designer does not allow to change the parameter type from XElement to string.
What do we do?
Declare a classas a descendant of the magic DataContext:

1: public class CatalogReader: DataContext
2: {
3: public CatalogReader(): base (System.Configuration. ConfigurationManager ConnectionStrings[ "ConnectionString" ConnectionString)
4: {}
5: }
6:

Declare prototype result :

1: class Item
2: {
3: public long Id { get ; set ; }
4: public string Name { get ; set ; }
5: public string Description { get ; set ; }
6: }
7:

And we directly write the Proxy method to call the procedure :

1: [Function(Name= "sp_getItems" )]
2: public ISingleResult<Item> GetItems([Parameter(DbType= «xml» )] string query)
3: {
4: var result = base ExecuteMethodCall( this , (System.Reflection.MethodInfo)System.Reflection.MethodInfo.GetCurrentMethod(), query);
5: return (ISingleResult<Item> )result.ReturnValue;
6: }
7:

How to use this :

1: using (CatalogReader context = new CatalogReader())
2: return context.GetItems( "<query> <id> 12</id> <id> 13</id> </query> " );
3:

Output parameters :

To retrieve data from a stored procedure returned via parameters, we change the proxy function

1: [Function(Name= «sp_getItems» )]
2: public ISingleResult<Item> GetItems([Parameter(DbType= «xml» )] string query, [Parameter(DbType= «intoutput» )] ref int count)
3: {
4: var result = base ExecuteMethodCall( this , (System.Reflection.MethodInfo)System.Reflection.MethodInfo.GetCurrentMethod(), query);
5: count = ( int )result.GetParameterValue(1);
6: return (ISingleResult<Item> )result.ReturnValue;
7: }
8:

This way you can fully implement LinqToSql database access (without using ADO.NET).

You may also like