SQL Server Stored procedures and UDF within LINQ context-PartII

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.

  1. The Scalar UDF
  2. Inline table valuated UDF
  3. 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!!!


Similar Articles