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, this will be a series of small notes that will probably help someone quickly and deeper understand the essence of the new concept of data access developed by Microsoft.

    Using Linq to automate stored procedure calls.


    The easiest way to use object data access is to create a dbml database model. But there are situations when performance is a key factor and you can get data from SQL Server using an exclusively stored procedure. In this case, of course, we can use the designer of Visual Studio. But, unfortunately, its functions for configuring call parameters for stored procedures are very limited. I was very annoyed that I did not have the opportunity to put down the type of the transmitted parameter with my hands.
    We take the stored procedure:
      1: create procedure sp_getItems (
      2: 
      3:  query xml -12thirteen
      4: 
      5:) as
      6: 
      7: begin
      8: 
      9: select [Id], [Name], [Description]
     10: 
     11: from [Item] it
     12: 
     13: inner join (select tcvalue ('.', bigint) [requestedid] from query .nodes ('query / id') t (c)) as query on query.requestedid = it. [id];
     14: 
     15: end;
    Dragging it in the designer onto the diagram, 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 inheritor of the magic DataContext:
      1: public class CatalogReader: DataContext
      2: {
      3: public CatalogReader (): base (System.Configuration.ConfigurationManager.ConnectionStrings ["ConnectionString"]. ConnectionString)
      4: {}
      5:}
      6: 


    We declare a prototype of the 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 prescribe directly the proxy method to call the procedure:
      1: [Function (Name = "sp_getItems")]
      2: public ISingleResultGetItems ([Parameter (DbType = "xml")] string query)
      3: {
      4: var result = base.ExecuteMethodCall (this, (System.Reflection.MethodInfo) System.Reflection.MethodInfo.GetCurrentMethod (), query);
      5: return (ISingleResult) result.ReturnValue;
      6:}
      7: 
    How to use it:
      1: using (CatalogReader context = new CatalogReader ())
      2: return context.GetItems ("12thirteen");
      3: 

    Output parameters:

    In order to get data from the stored procedure returned through the parameters, we change the proxy function
      1: [Function (Name = "sp_getItems")]
      2: public ISingleResultGetItems ([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) result.ReturnValue;
      7:}
      8: 
    Thus, it is possible to fully realize access to the database using LinqToSql technology (without using ADO.NET).

    Also popular now: