In the first article, SQL Server Stored procedures and UDF within LINQ context-Part I, I exposed 2 scenarios of how to deal with stored procedures using LINQ, in this second part, I will accomplish the task by demonstrating how to call a user defined function UDF using the same way adopted in the first part. I mean by exposing real live scenarios.
Before starting the scenarios, let's make a quick review concerning the user defined functions UDF. There are three kind of UDF.
The classification is done according to the nature of the returned object.
- The Scalar UDF
- Inline table valuated UDF
- Table valuated UDF
The scalar UDF returns a unique simple object; I mean other than a table or a similar composed object. The inline table valuated UDF returns a result set of a given select query. Finally, the table valuated UDF returns tables object
Senario1
Imagine the situation where you have to get the category of a given product on giving its identifier. In this case, you implement the UDF as bellow:
CREATE FUNCTION dbo.SelectCaterotyID
(
@ProdID int
)
RETURNS int
AS
BEGIN
DECLARE @CategoryID int
SELECT @CategoryID = CategoryID FROM Products WHERE ProductID = @ProdID
RETURN @CategoryID
END
This above UDF receives the product Identifier and returns an integer that represents the category identifier. The code that enables to call this UDF is designed as follow:
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 the name of the used function*/
[Function(Name = "SelectCategoryID")]
/* This attribute provides information about the returned
object*/
[return: Parameter(Name = "@CategoryID", DbType = "int")]
public int GetTheCategoryID([Parameter(Name = "@ProdID", DbType = "int")]int identifier)
{
IExecuteResult oResult = this.ExecuteMethodCall(this, MethodInfo.GetCurrentMethod() as MethodInfo,new object[1]{identifier});
return (int)oResult.ReturnValue;
}
}
The NorthWind class contains the GetTheCategoryID method witch returns the result as an integer. But as you remark, there is a new used attribute when comparing with the stored procedure case. It is the new [return: Parameter(Name = "@CategoryID", DbType = "int")] this one provides data about the returned object; I mean the related name and type of the returned object. Now, let's implement the code that consumes the NorthWind services.
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();
//Execute the product update operation through the bellow method
int oResult = oNorthWind.SetNewPrice(Convert.ToInt32(id));
Console.WriteLine(string.Format("The corresponding cathegory id is {0}",oResult));
Console.ReadLine();
}
Run the application and observe:
Figure 1
Senario2:
Imagine a scenario where a given user wants to get the name of a given product by giving its identifier. The returned value is a single element but its type is other than integer. It is a string object this once. Many people use stored procedure to perform such task, but I prefer always to make use of a scalar UDF because it is done for this kind of use cases. The scalar UDF core is designed as follow:
CREATE FUNCTION dbo.SelectProductName
(
@ProdID INT
)
RETURNS NVARCHAR(40)
AS
BEGIN
DECLARE @ProductName NVARCHAR(40)
SELECT @ProductName = ProductName FROM Products WHERE ProductID = @ProdID
RETURN @ProductName
END
The UDF receives an integer that represents the productID and returns a string variable that represents the related product name. The code that enables to call this UDF is designed as follow:
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") { }
/* The IsComposable element indicates whether the mapped object
is a stored procedure or a function*/
[Function(Name = "dbo.SelectProductName", IsComposable = true)]
public string SelectProductName([Parameter(Name = "ProdID", DbType = "Int")] int prodID)
{
//The oResult contains the returned result in the ReturnValue object
IExecuteResult oResult = this.ExecuteMethodCall(this,
MethodInfo.GetCurrentMethod() as MethodInfo,
prodID);
return oResult.ReturnValue as string;
}
}
The SelectProductName method returns a string this once. The IsComposable = true element indicates that the mapped object is an UDF rather than a stored procedure. It is another way to indicate the nature of the mapped object other than used in the first scenario, I mean [return: Parameter(Name = "@CategoryID", DbType = "int")]. The purpose here is to discover the different possible implementations concerning the UDF. Another detail concerning the used parameters, is that it is possible to precede the given parameter by the @ such as "@parameterName" like "@ProdID" parameter witch is used within the scope of the UDF to represent the product identifier, however, it is possible to use it without @ too. Both alternatives work without any problems and the "ProdID" is recognized as @ProdID used in the UDF. Now, let's implement the code that consumes the NorthWind class services.
static void Main(string[] args)
{
//The NorthWind object instance
NorthWind oNorthWind = new NorthWind();
//The product identifier
Console.WriteLine("Enter the product identifier");
string id = Console.ReadLine();
//the id value Convertion to integer
int cTointID = Convert.ToInt32(id);
//the returned result is stocked in result variable
string result = oNorthWind.SelectProductName(cTointID);
//Display the result
Console.WriteLine(string.Format("The product {0} name is {1}",
cTointID,
result));
Console.Read();
}
Run the application and observe:
Figure2
Senario3:
What if the user wants to gather information about a given product from the Products table? For example, the user enters the productID and receives a view composed by, namely the product name, the quantity by unit and the unit price. All data are grouped form Products table. The new UDF core will be.
CREATE FUNCTION ProductInfo
(
@ProdID INT
)
RETURNS TABLE
AS
RETURN SELECT Products.ProductName,
Products.QuantityPerUnit,
Products.UnitPrice
FROM Products
WHERE Products.ProductID = @ProdID
As you remark, this is an inline UDF. This last one returns a table or exactly a view according to the product identifier entered by the user.
First think to do, is to create an entity class that represents the inline generated table:
[Table(Name = "Products")]
class Product
{
[Column(Name = "ProductID", IsPrimaryKey = true)]
public int ProductID;
[Column(Name = "ProductName")]
public string Name;
[Column(Name = "QuantityPerUnit")]
public string UnitQuantity;
[Column(Name = "UnitPrice")]
public int Price;
}
This above entity class will be the generic type used later to enumerate data of the selected object. In the other hand, this is the class responsible for connecting to database and retrieving data.
class NorthWind : DataContext
{
//The constuctor
public NorthWind()
: base(@"Data Source=.\SQLEXPRESS;AttachDbFilename=" +
@"C:\CallSPUDF\CallSPUDF\NORTHWND.MDF;Integrated" +
@" Security=True") { }
[Function(Name = "ProductInfo", IsComposable = true)]
public IQueryable<Product> GetProductData([Parameter(Name = "ProdID",
DbType = "Int")]int idenifier)
{
IExecuteResult oResult = this.ExecuteMethodCall(this,
MethodInfo.GetCurrentMethod() as MethodInfo,
new object[1] { idenifier });
return (IQueryable<Product>)oResult.ReturnValue;
}
}
As you can remark, the returned type is a generic IQueryable object because the output is a table and not a scalar.
Finally, let's implement the main method to enable consuming the above class services.
static void Main(string[] args)
{
NorthWind oNorthWind = new NorthWind();
Console.WriteLine("Enter the identifier");
string id = Console.ReadLine();
IQueryable<Product> Query = oNorthWind.GetProductData(Convert.ToInt32(id));
string data="";
foreach (Product p in Query)
{
data = string.Format("Product name {0}, product price {1} and product unit quantity {2}",
p.Name,
p.Price,
p.UnitQuantity);
Console.WriteLine(data);
Console.WriteLine();
}
Console.Read();
}
Now, fire up the application and observe. That's it.
In a part I, I enumerate cases according to witches I demonstrate methods of how to call a stored procedure and querying data depending on situations and use cases, and in the second part, same think is done but UDF user defined functions are the subject this once.
Good Dotneting!!!