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.
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:
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:
We declare a prototype of the result:
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 (Dragging it in the designer onto the diagram, we get the following prototype:
2:
3: query xml -12 thirteen
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;
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 ItemAnd we prescribe directly the proxy method to call the procedure:
2: {
3: public long Id {get; set; }
4: public string Name {get; set; }
5: public string Description {get; set; }
6:}
7:
1: [Function (Name = "sp_getItems")]How to use it:
2: public ISingleResult- GetItems ([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:
1: using (CatalogReader context = new CatalogReader ())
2: return context.GetItems (""); 12 thirteen
3:
Output parameters:
In order to get data from the stored procedure returned through the parameters, we change the proxy function1: [Function (Name = "sp_getItems")]Thus, it is possible to fully realize access to the database using LinqToSql technology (without using ADO.NET).
2: public ISingleResult- 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- ) result.ReturnValue;
7:}
8: