Tutorial 8: Using Stored Procedures

Description
Table of Contents
Summary

LINQ to SQL automatically produces SQL statements to CRUD operations. Sometimes it may be preferable to use stored procedures to access data in order to  improve application performance, guarantee security and inject some business logic.

Note

A customized DataContext object must be implemented in order to have access to protected methods DataContext::ExecuteCommand / ExecutemethodCall / ExecuteQuery for stored procedures or custom SQL statements execution.

Operations
Using stored procedure for CUD operations overriding Linq to SQL dynamic SQL

To override the (generated) data context methods for a specific entitiy:
1) Implement a stored procedure with required parameters mapped to the entity’s class properties;
2) Override partial data context methods ::InsertENTITYNAME / DeleteENTITYNAME / UpdateENTITYNAME parameterized with the entity instance;
2.1) Use the ::ExecuteCommand() to call the entity stored procedure on the database

Remarks

In case of the UpdateENTITYNAME method, the number of rows affected must be returned, therefore the spUpdateCustomer() stored procedure must add the RETURN @@ROWCOUNT instruction at the end of the stored procedure code.

How to do it
public partial class TrainingDataContext
{
	// Overrided method for insertion of Customer entities
	public void InsertCustomer(Customer pCustomer)
	{
		this.ExecuteCommand(
					"exec spInsertCustomer @Firstname={0}, @Lastname={1}",
					pCustomer.FirstName, pCustomer.LastName);
	}

	// Overrided method for update of Customer entities
	public void DeleteCustomer(Customer pCustomer)
	{
		this.ExecuteCommand("exec spDeleteCustomer @id={0}", pCustomer.ID);
	}

	// Overrided method for delete of Customer entities
	public void UpdateCustomer(Customer pNewCustomer)
	{
		int iRowsAffected = this.spUpdateCustomer(
								pNewCustomer.ID, pNewCustomer.FirstName, pNewCustomer.LastName);

		if (iRowsAffected < 1) throw new ChangeConflictException();
	}
}
Executing custom SQL queries

1) Implement a custom method with required parameters and entity type to be used as result type;
2) Use ::ExecuteQuery() method to execute SQL query that maps result set to the output entity type;

Remarks

The class used as result type does not have to be created within the Linq To SQL ORM Designer.

How to do it
public partial class TrainingDataContext
{
	public IEnumerable<Customer> GetCustomerByCountry(string pCountry)
	{
		return ExecuteQuery<Customer>(@"Select * from customers where country = '{0}'", pcountry);
	}
}
Map client method to a User Defined Function (UDF) to use on queries

The database function is mapped to a class method:
1) Decorate method with the FunctionAttribute attribute
1.1) Each related method parameter is decorated with ParameterAttribute attribute.

Remarks

This function can be used on inline queries.

How to do it
public partial class TrainingDataContext
{
	[Function(Name = "dbo.udf_ReverseCustomerName", IsComposable = true)]
	[return: Parameter(DbType = "VarChar(100)")]
	public string ReverseCustomerName([Parameter(Name = "string",
		DbType = "VarChar(100)")] string @string)
	{
		return ((string)(this.ExecuteMethodCall(this,
			((MethodInfo)(MethodInfo.GetCurrentMethod())), @string).ReturnValue));
	}
	...
}
CREATE FUNCTION udf_ReverseCustomerName(@string varchar(100))
RETURNS varchar(100)
AS
BEGIN
    DECLARE @customerName varchar(100)
    -- Implementation left as exercise for users.
    RETURN @customerName
END
Class method mapped to a user-defined function on the database
How to use it
var query =
    from c in dc.Customers
    select new {c.ContactName, Title =
        dc.ReverseCustomerName(c.ContactTitle)};
SELECT [t0].[ContactName],
    dbo.ReverseCustomerName([t0].[ContactTitle]) AS [Title]
FROM [Customers] AS [t0]
Using mapped user-defined function on in-line queries
Map client method to a table-valued User Defined Function (UDF)

The database table-valued user-defined function is mapped to a class method:
1) Decorate method with the FunctionAttribute attribute
1.1) Each related method parameter is decorated with ParameterAttribute attribute.

Remarks

This function can be used on inline queries as any nornal data table of the data context object.

How to do it
public partial class TrainingDataContext
{
	[Function(Name="dbo.udf_ProductsCostingMoreThan", IsComposable=true)]
	public IQueryable<ProductsCostingMoreThanResult> ProductsCostingMoreThan([Parameter(DbType="Money")] System.Nullable<decimal> cost)
	{
		return this.CreateMethodCallQuery<ProductsCostingMoreThanResult>(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), cost);
	}
	...
}
CREATE FUNCTION ProductsCostingMoreThan(@cost money)
RETURNS TABLE
AS
RETURN
    SELECT ProductID, UnitPrice
    FROM Products
    WHERE UnitPrice > @cost
Class method mapped to a table-valued user-defined function on the database
How to use it
var query = from p in dc.ProductsCostingMoreThan(80.50m)
		join s in dc.Products on p.ProductID equals s.ProductID
		select new { p.ProductID, s.UnitPrice };
SELECT p2.ProductName, p1.UnitPrice
FROM dbo.ProductsCostingMoreThan(80.50)
AS p1 INNER JOIN Products AS p2 ON p1.ProductID = p2.ProductID
Using mapped table-mapped user-defined function on in-line queries
Map client query method to a Stored Procedure (scalar value as result)

The database stored procedure is mapped to a class method:
1) Implement or generate method that will encapsulate the call to the stored procedure in the database;
2) Decorate method with the FunctionAttribute attribute
2.1) Each related method parameter is decorated with ParameterAttribute attribute.

Remarks

The automatic generation with SQLMetal tool, option “/spprocs”, can generate methods with the same name as the stored procedures.

How to do it
public partial class TrainingDataContext
{
	[Function(Name="dbo.sp_CountCustomer")]
	public int spCountCustomer() {
			IExecuteResults result =
						ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
			return ((int)(result.ReturnValue));
	}
}
CREATE procedure sp_CountCustomer
AS
    declare @count int
    SET @count = (SELECT count(ID) FROM Customers)
    RETURN @count
Class method mapped to a stored procedure on the database
Map client query method to a Stored Procedure with output parameters

The database stored procedure is mapped to a class method:
1) Implement or generate method that will encapsulate the call to the stored procedure in the database;
2) Decorate method with the FunctionAttribute attribute
2.1) Each related method parameter is decorated with ParameterAttribute attribute
2.2) Each output parameter of the stored procedure is treated as a reference method parameter

How to do it
public partial class TrainingDataContext
{
	[Function(Name = "dbo.spGetTotalSalaryAmountPerYear")]
	public ISingleResult<spGetTotalSalaryAmountPerYearResult> GetTotalSalaryAmountPerYear(
			[Parameter(DbType = "Int")] System.Nullable<int> pYear,
			[Parameter(DbType = "Money")]
			ref System.Nullable<decimal> pAmount)
	{
		IExecuteResult result = this.ExecuteMethodCall(
										this,
										((MethodInfo)(MethodInfo.GetCurrentMethod())),
										pYear,
										pAmount);
		pAmount = ((System.Nullable<decimal>) (result.GetParameterValue(1)));

		return ((ISingleResult<spGetTotalSalaryAmountPerYearResult>) (result.ReturnValue));
	}
}
CREATE PROCEDURE spGetTotalSalaryAmountPerYear
    @year int,
    @amount money output
AS
    SET @amount = (SELECT sum(SalaryYear)
                   FROM Salary
                   WHERE year=@year)
    SELECT @amount
Class method mapped to a stored procedure on the database
How to use it
	TrainingDataContext dc = new TrainingDataContext();

	decimal? lvTotal = 0;
	int lvYear = 2004;

	people.GetTotalSalaryAmountPerYear(lvYear, ref lvTotal);

	Console.WriteLine(lvTotal.ToString());
Using mapped stored procedure with output parameters
Map client query method to a Stored Procedure (table as result)

The database stored procedure is mapped to a class method:
1) Implement or generate method that will encapsulate the call to the stored procedure in the database;
1.1) Use an entity class type as output result of the method
2) Decorate method with the FunctionAttribute attribute
2.1) Each related method parameter is decorated with ParameterAttribute attribute.

How to do it
public partial class TrainingDataContext
{
	[FUNCTION(Name = "dbo.spGetCustomersForCompany")]
	public IEnumerable<Customer> GetCustomersForCompany(
				[Parameter(Name = "@company")] string pCompany)
	{
		IQueryResults<Role> result =
				ExecuteMethodCall<Customer>(
								this,
								((MethodInfo)(MethodInfo.GetCurrentMethod())),
								pCompany);

		return ((IEnumerable<Customer>)(result));
	}
}
CREATE PROCEDURE spGetCustomersForCompany
   @company varchar(50)
AS
   SELECT ID, FirstName, LastName
   FROM Customers
   WHERE company LIKE @company
Class method mapped to a stored procedure on the database
How to use it
TrainingDataContext dc = new TrainingDataContext();

foreach(Customer c in dc.GetCustomerForCompany("M%"))
{
   Console.WriteLine("Customer ID / name: {0} {1},{2}", c.ID.ToString(), c.FirstName, c.LastName);
}
Using mapped stored procedure with table result set
Map client query method to a Stored Procedure (multiple table results)

The database stored procedure is mapped to a class method:
1) Implement or generate method that will encapsulate the call to the stored procedure in the database;
2) Decorate method with the FunctionAttribute attribute
2.1) Each related method parameter is decorated with ParameterAttribute attribute.
kk