Practice using LinqToSql: Using Linq to automate calls to stored procedures.

The last few months of my work have contributed to the active research of LinqToSql technology. Therefore, I want to talk about my experience, it will be a series of small notes that may help someone quickly and deeper understand the essence of the new concept of data access developed by Microsoft.

Use Linq to automate calls to stored procedures.

The easiest way to use object data access is to create a dbml database model. But there are situations when performance is the key factor and you can get data from SQL Server solely by a stored procedure. In this case, of course, we can use the Visual Studio designer. But, unfortunately, its functions for configuring call parameters of stored procedures are very limited. I was very annoyed that I did not have the opportunity to set the type of the parameter passed by hand.
We take the stored procedure:

1: create procedure sp_getItems (
2:
3: query xml - <query> <id> 12 </ id> <id> 13 </ id> </ query>
four:
5:) as
6:
7: begin
eight:
9: select [Id], [Name], [Description]
ten:
11: from [Item] it
12:
13: inner join ( select tc value ( '.' , Bigint) [requestedid] from query. Nodes ( 'query / id' ) t (c)) as query on query.requestedid = it. [Id];
14:
15: end ;

Dragging it into the diagram in the designer, we get the following prototype:

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

The designer does not allow changing the parameter type from XElement to string.
What are we doing?
We declare the class a successor to the magic DataContext:

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

We announce the result prototype:

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 register directly the Proxy-method for calling 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 it:

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

Output parameters:

In order to get data from the stored procedure, returned through the parameters, change the proxy function

1: [Function (Name = "sp_getItems" )]
2: public ISingleResult <Item> GetItems ([Parameter (DbType = "xml" )] string query, [Parameter (DbType = "int output" )] 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: }
eight:

Thus, it is possible to fully implement access to the database on LinqToSql technology (without using ADO.NET).