SQL Server Stored procedures and UDF within a LINQ context-Part I

In this two parts article, I will give techniques of how to deal with stored procedures and user defined functions UDF from within a LINQ context rather than ADO.Net. The advantage of calling the stored procedures and UDF via LINQ is, in fact, that LINQ is not concerned whether they are written using Transact SQL or SQL Common language Runtime that allows users to create objects such as stored procedures or user defined functions using C# or VB.Net. In the other hand, LINQ enables to write strongly typed queries. To know how to execute a stored procedure or a given UDF using LINQ and have an overview concerning this issue; I invite you to follow those scenarios.

During this tutorial the NorthWind SQL Server data base is used as a data source. In order to download it you can reach it via this link

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

The NORTHWIND.MDF could be reached in your hard drive C:\SQL Server 2000 Sample Databases\NORTHWND.MDF

Before starting let's make a rapid comparison between the stored procedures and the UDF or user defined functions through this schedule:

Stored procedures	User defined functions
A Stored procedure can't return a table variable in spite of the ability to create one	A Function can return table variables
A Stored procedure can change the server environment	A Function can't change the server environment
A Stored procedure can be used in an XML FOR clause	A Function can't be used in an XML FOR clause
Stored procedures are called independently using EXEC	Functions are called from within SQL statement
Stored procedures may return an integer or nothing	Functions must always return an object
A stored procedure continues to the next instruction when an error occurs except that an error handling code is set	Functions stops execution when an error occurs

In addition to the above schedule, I can say that based on my experience, the stored procedure are more rapidly executed than the user defined functions.

First, create a new windows Console application then add those namespaces into your project System.Data.Linq; and System.Data.Linq.Mapping. If a reference isn't already added to System.Data.Linq then right click on the project icon in the solution explorer and add a reference to it.

Figure 1

Stored procedures and LINQ

Senario1:

Say that you want to update the NorthWind Products table by enabling final users to modify the price related to a given product according to its identifier. In this case, the user has to enter both the product identifier and the price value via an interface dedicated to that.

This is the stored procedure core:

CREATE PROCEDURE dbo.ModifiyPrice

(

@NewPrice MONEY,

@ProdID INT

)

/* SET NOCOUNT ON */

UPDATE Products

SET UnitPrice = @NewPrice

WHERE ProductID = @ProdID;

RETURN

Let's switch to Visual Studio and perform the code that calls the above procedure. First, you derive a class from the DataContext in order to create some customized behaviors those fit our need.

class NorthWind : DataContext

{

public NorthWind()

: base(@"Data Source=.\SQLEXPRESS;AttachDbFilename=" +

@"C:\SQL Server 2000 Sample Databases\NORTHWND.MDF';" +

@"Integrated Security=True;Connect Timeout=30;User Instance=True") { }

.........

}

Then you add a method responsible of mapping the stored procedure into the above class, it is public int SetNewPrice in this case.

class NorthWind : DataContext

{

//The constuctor

public NorthWind()

: base(@"Data Source=.\SQLEXPRESS;AttachDbFilename=" +

@"C:\SQL Server 2000 Sample Databases\NORTHWND.MDF';" +

@"Integrated Security=True;Connect Timeout=30;User Instance=True") { }

//This attribute provides data about the stored procedure that will be used

//through reflection mechnism within the SetNewPrice method

[Function(Name = "ModifiyPrice")]

//The method that maps the stored procedure must return an object

//We use an int as a returned type because this is only an update action

public int SetNewPrice([Parameter(Name="@NewPrice",DbType="int")]int Price,[Parameter(Name="@ProdID",DbType="int")]int identifier)

{

/*The ExecuteMethodCall recieves as parameters, a datacontext, a MethodInfo

therefore System.Reflection namespace has to be included and finally an object

table that contained the input parapeters entred by the user

and it returns an IExecuteResult object*/

IExecuteResult oResult = this.ExecuteMethodCall(this, MethodInfo.GetCurrentMethod() as MethodInfo,new object[2]{Price,identifier});

//The IExecuteResult.ReturnValue is a type of object

//therefore it must be transtyped to integer type

return (int)oResult.ReturnValue;

}

DataContext.ExecuteMethodCall() is used in this case and it returns an IExecuteResult object that returns the executed query result by itself through the oResult.ReturnValue object. It is only a unique update operation to perform in this case, thus, the returned value is not so important in this context rather than the update operation itself. Another important think that has to be said before you begin write the code. In fact, the System.Reflection namespace has to be included as we use MethodInfo in this context. The MethodInfo invokes the method that maps the stored procedure through reflection.

Now, let's write a code that consumes the services of this above class

static void Main(string[] args)

{

//Initilise a NorthWind object

NorthWind oNorthWind = new NorthWind();

//The product identifier

Console.WriteLine("Enter the product identifier");

string id = Console.ReadLine();

//The new price

Console.WriteLine("Enter the product new price");

string np = Console.ReadLine();

//Execute the product update operation through the bellow method

int oResult = oNorthWind.SetNewPrice(Convert.ToInt32(id), Convert.ToInt32(np));

Console.WriteLine(string.Format("The product {0} price is updated to {1}",id,np));

Console.ReadLine();

}

Let's run the application and observe,

Figure 2

Open the products table and the result will be

Figure 3

Senario2:

Always with the same table, I mean Products one. Imagine that the price follow this mechanism

Figure 4

Where C is the periodic growth price ratio multiplied by a constant that depends on whether the price is greater or less than 20, the final price will be the initial one multiplied by the C coefficient.

And this is the new stored procedure core:

ALTER PROCEDURE dbo.ModifiyPrice

(

@NewPrice MONEY,

@ProdID INT

)

/* SET NOCOUNT ON */

IF @NewPrice> 20

BEGIN

UPDATE Products

SET UnitPrice = UnitPrice*(@NewPrice/UnitPrice)*1.1

WHERE ProductID =@ProdID

END

ELSE IF @NewPrice < 20

BEGIN

UPDATE Products

SET UnitPrice = UnitPrice*(@NewPrice/UnitPrice)*1.5

WHERE ProductID =@ProdID

END

In this case, the stored procedure will be mapped according to its multiple results shape. And the code will be changed to fit the new circumstances:

class NorthWind : DataContext

{

//The constuctor

public NorthWind()

: base(@"Data Source=.\SQLEXPRESS;AttachDbFilename=" +

@"C:\SQL Server 2000 Sample Databases\NORTHWND.MDF';" +

@"Integrated Security=True;Connect Timeout=30;User Instance=True") { }

//This attribute provides data about the stored procedure that will be used

//through reflection mechnism within the SetNewPrice method

[Function(Name = "dbo.ModifiyPrice")]

//If There is nothing to return set the generic type to int

//As the stored procedure returns and integer 0 after execution

[ResultType(typeof(int))]

//The method that maps the stored procedure must return an object

//We use an int as a returned type because this is only an update action

public IMultipleResults SetNewPrice([Parameter(Name="@ProdID",DbType="int")]int identifier,

[Parameter(Name = "@NewPrice", DbType = "int")]int newprice)

{

/*The ExecuteMethodCall recieves as parameters, a datacontext, a MethodInfo

therefore System.Reflection namespace has to be included and finally an object

table that contained the input parapeters entred by the user

and it returns an IExecuteResult object*/

IExecuteResult oResult = this.ExecuteMethodCall(this, MethodInfo.GetCurrentMethod() as MethodInfo,new object[2]{identifier,newprice});

//The IExecuteResult.ReturnValue is a type of object

//therefore it must be transtyped to integer type

return (IMultipleResults)oResult.ReturnValue;

}

}

As you remark, the returned type is changed from int in the first scenario to IMultipleResults in the current one because each block of the stored procedure returns a different result. Hence, you will deal with a multiple queries context. The IMultipleResults interface is leveraged for such case.

In order to make use of the above code, let's implement the main method as follow:

static void Main(string[] args)

{

//Instanciate the NorthWind data context

NorthWind oNorthWind = new NorthWind();

//Enter the product identifier

Console.WriteLine("Enter the product identifier number");

string id = Console.ReadLine();

//Enter the price of the accoring product

Console.WriteLine("Enter the new price");

string price = Console.ReadLine();

try

{

//The first block is executed

if (Convert.ToInt32(price) > 20)

{

/* The IMultipleResults represents the queries result, the returned object is not our primary concern, the principal think is to execute the update*/

IMultipleResults result1 = oNorthWind.SetNewPrice(Convert.ToInt32(id), Convert.ToInt32(price));

Console.WriteLine("The first block of the stored procedure is used");

Console.WriteLine("Update operation is complete");

Console.Read();

}

//The second bolck is executed

else

{

IMultipleResults result2 = oNorthWind.SetNewPrice(Convert.ToInt32(id), Convert.ToInt32(price));

Console.WriteLine("The second block of the stored procedure is used");

Console.WriteLine("Update operation is complete");

Console.Read();

}

catch (Exception caught)

{

Console.WriteLine(caught.Message);

Console.Read();

}

As you remark, the code is cracked into two main blocks, the first one deals with the situation where the entered price is more than 20 and the second block deals with the situation where the price is less than or equal to 20. For each situation, an IMultipleResults object is returned. Normally, you make use of the result1.GetResult<typeof(returned object)>() and result2.GetResult<typeof(returned object)>() that returns a generic IEnumerable object that enables us to enumerate table objects through a foreach loop, but in this situation there is not important returned objects, I mean the main task is to update the data base through the employed stored procedure and no more. In this case, a simple integer is returned after executing the update operation. Thus, you have to precise that the type of the result is integer through adorning the IMultipleResults SetNewPrice() method that returns results by the both attributes [ResultType(typeof(int))] and [ResultType(typeof(int))], those last ones are very necessary even there are not returned objects; if you ignore them you'll receive an Invalid operation exception at the run time.

Finally, try to run the application and observe:

Open the products table

Figure 5

As you remark, the initial price is 20. Try to modify the price as follow:

Figure 6

The new price will be leveraged according to the first alternative as the price is more than 20 and the result will be as below after reopening the Products table.

Figure 7

Now, set back the unit price to its initial value, I mean, the unit price of the product 1 to 20. Then run the application and try to update the same product using a price value less than or equal to 20.

Figure 8

The result will be leveraged according to the second alternative

Figure 9

Finally, recall that LINQ provides more than one mechanism to call a stored procedure. In part II of this article you'll discover how to call an UDF and obtain its result(s) using LINQ.

Good Dotneting!!!