Thursday, November 26, 2015

SQL 2005 - Creating and using Table-Valued Functions

1. Introduction

In the previous article we saw creating a simple scalar- valued function. Now, here in this article we will look at creating the Table-Valued function. Unlike the scalar-valued function a table-valued function returns a table to the caller. There are two kinds of table valued functions that we can write. Those are:
1) In-Line Table-Valued functions
2) Multi-line table valued functions

In this article, I will walk you through creating both the functions. OK, let us start.

2. In-Line Table valued function

The inline table-valued function returns a table in-place of the scalar values. We can call table valued function as in-line when the body has a single select statement. The function “FuncAuthors” is an example for the In-Line table valued function and the function is given in the below screen shot:

In-Line Table Valued Function Example
Fig 1. In-Line table valued function

Execute the above T-SQL to create the Table-Valued function. Once the function is created, it will appear in the Table-Valued function node as shown in the below picture:

SQL Tree
Fig 2. Function in the tree

Note that the function returns a table by Picking the columns au_id, au_lname etc., from the authors table. As this is the only statement we call this function as an in-line table valued function also we can note that the function does not have Begin and End pair as the body contains single statement. The below video shows creating an In-Line table valued function:

Video 1: Creating in-Line table valued function

3. Using the Inline Table-Valued function

Now try executing the function using the way we executed the scalar valued function using the statement below:
-- InCorrect Usage
Select dbo.FuncAuthors();

You will get an error stating the function is not found in the database. This is because, the function that returns table cannot be used the same way we use the scalar valued function.

In the last article we saw the scalar valued functions participating in the select queries. As the scalar-valued function returns a single value, it can participate on the select list, where conditions of the select queries. Whereas, the table valued functions; as it returns a table, can participate in the “from” clause of the select queries. Simply, wherever the table can appear in the SQL Select statement, the function (Table-valued) can be used. In our example, as the function is returning a Table and function is in-line, the author table can be even updated through it. Now look at the below picture which shows correct usage of the table valued function:

Using In-line Table-Valued Function
Fig 3. Using the In-Line Table-Valued Function

The above example shows calling the function in the “from” clause of the T-SQL select statement. In the column list the above example specifies the * indicating all table columns returned by the function needs to be retrieved. We can also specify the columns returned by the function in the select list of the query. The below example shows forming the author full name:

In-Line Table valued function with Selective Column
Fig 4. In-Line Table valued function with Selective Column

Since the function “FuncAuthors” is an In-Line function, we can update the authors table by making use of the table returned by it. In the below example, Author’s last name is changed from ‘White’ to ‘Gray’:

Update through In-Line Table-Valued function
Fig 5. In-Line table valued function in Update

Using the inline scalar valued function is shown in the below.

Video 2: In-Line Table Valued function – Usage

4. Multi-line Table valued function

Multi-line table valued functions are written to handle the complex situation of the achieving the required result. Let us write an example which returns Order Information from the sales database for a given order number. Once the sales order number is given, the function should return the Titles in the given ORDER, how many qty of each title placed in the order and total copies sold so for. Have a look at the function below:

Multi-line table valued function
Fig 6. Multi-line table valued function example
In the above T-SQL, the return table with column and data type is defined as part of the SQL function itself (Marked as 1). We should define the data type in such a way that it should match with the underlying column of the table schema. Next, two variables @storeid and @titleid are declared (Marked as 2) to retrieve the corresponding data from the relevant tables. For example, by using the storied from the sales table, the store name is retrieved from the stores table. The other four more variables (Marked as 3) are used to retrieve the return table data from different tables of the pubs database. These four values are inserted into the return table that you we will see soon.

We know that the function takes the Order number as parameter and this parameter is used to declare the cursor SalesCur (Marked as 4) on the sales table. Then the cursor is opened (marked as 5) to retrieve Title ID, Store ID and Quantity for the given order number from the sales table. There may be one or more rows (Or even zero) of data for the given order number and the fetch statement retrieves the data from the first row and advances the cursor pointer to next valid row. The successful fetch is tested using the SQL Environment “@@FetchStatus” and in our example, this test is used to iterate over the retrieved rows of data.

The possible values of Fetch status is shown in the below table:

The FETCH statement was successful.
The FETCH statement failed or the row was beyond the result set.
The row fetched is missing.

On every successful fetch, the fetched data @storedid, @titledid are used to retrieve the store name and title name from the corresponding tables stores, titles respectively (Marked as 6). Now we have @stname, @title and @qty (got from the cursor itself) for the return table. The @TotalSold is retrieved by making use of the aggregate function on Sales table for the given title. Once, all the data in hand, there are inserted to the return table @OrderInfo (Marked as 7) and this table is returned to the caller. Creating this function is explained in the below video:

Video 3: Creating Multi-Line function

5. Using the Multi-Line table valued function

Multi-Line table-valued function can be used the same way normal in-line table valued function (Update not permitted here). Let us see the first example which is shown below:

Multi-Line Table valued function Usage
Fig 7. Multi-Line Table valued function Usage

1) The multi-line table valued function created in the first section is shown in the sql tree under the node Table-valued Functions (Marked as 1).
2) In the above example, the markings 2 and 3 shows four books are placed in the order identified by the order number P3087a.
3) The markings 4 and 5 show how the function is invoked and its returned data. Note that as the function returns the table it is placed in the “from” clause of the select query. We can also see the return data is from the @OrderInfo table returned by the function @GetOrderInfo.

Below is the second example, which shows how the aggregate is returned by the function.

Multi-Line Table valued function Usage
Fig 8. Multi-Line Table valued function Usage


1 and 2
The select query returns the sales data for the order number 722a. And we see only one book title (PS2091) is placed in that order.
3 and 4
The query shows retrieving the sales data for the title_id PS2091. The result shows that there are 4 orders in which the book is ordered. We can also see that the total quantity ordered is 108 (By counting qty column in all four orders).
5 and 6
The query makes use the GetOrderInfo function by passing the Order Number 722a. And you can see the result with TotalSold showing the 108.

The demo of above two examples are given in the below video.

Video 4: Multi-Line function usage

6. Listings

Listing 1:
-- Table-Valued Function Inline
Create Function FuncAuthors()
Returns Table As
                Return Select au_id, au_lname, au_fname, phone,
                                                city, state, zip from authors;
Listing 2:
-- InCorrect Usage
Select dbo.FuncAuthors();
-- Correct Usage
Select * from dbo.FuncAuthors();

-- Correct Usage
Select au_id, au_fname + ' ' + au_lname as AuName
from dbo.FuncAuthors();

--Update through the Function
-- Valid only for inline
Select * from dbo.FuncAuthors()
Where Au_id = '172-32-1176';

Update dbo.FuncAuthors()
Set Au_Lname = 'Gray'
Where Au_id = '172-32-1176';

Select * from dbo.FuncAuthors()
Where Au_id = '172-32-1176';

Update Table Authors
Set Au_Lname = 'White'
Where Au_id = '172-32-1176';

Select * from dbo.FuncAuthors()
Where Au_id = '172-32-1176';

Listing 3:
-- Table-Valued Function Multi-line
Create Function GetOrderInfo(
                @OrderNumber varchar(20))
returns @OrderInfo table(
                storeName varchar(20),
                title varchar(80),
                Qty smallint,
                TotalSold int
                ) as
                --For Data retrival
                declare @storeid char(4);
                declare @titleid varchar(6);

                declare @stname as varchar(20);
                declare @title as varchar(80);
                declare @qty as smallint;
                declare @TotalSold as int;

                -- Cursor to hold all the titles and store id
                declare SalesCur cursor for
                select stor_id, title_id, qty from
                sales where ord_num = @OrderNumber;          

                -- Open cursor and Fetch data
                Open SalesCur;
                Fetch next from SalesCur into @storeid, @titleid, @qty;
                while @@Fetch_status = 0
                                --Collect data for the return table
                                select @stname = stor_name from stores where stor_id = @storeid;
                                select @title = title from titles where title_id = @titleid;
                                Select @TotalSold = sum(qty) from Sales where title_id = @titleid;

                                --Insert data to the return table
                                insert @OrderInfo (storeName, title, Qty, TotalSold)
                                                Select @stname, @title, @qty, @TotalSold;

                                Fetch next from SalesCur into @storeid, @titleid, @qty;

Listing 4:
-- Executing the Function
-- Remember there is no validation
Select * from sales where ord_num = 'P3087a';
Select * from dbo.GetOrderInfo('P3087a');
Select * from sales where ord_num = '722a';
Select * from sales where title_id = 'PS2091';
Select * from dbo.GetOrderInfo('722a');

Thursday, November 05, 2015

SQL 2005 - Creating and using Scalar-Valued function in SQL Server

1. Introduction

Stored Procedure and function in SQL server are the compiled unit of SQL Program which performs a specific task. Unlike a stored procedure, a user defined function in SQL server can participate on the SQL Select Queries. In this article, I will show creating and using the “Scalar-Valued Function” (UDF) in the SQL Server.

The Examples in this article are created using Microsoft supplied Titles table of Pubs database. You can download the SQL Script that creates the Pubs and NorthWnd database from the SQL 2005 Section of this site. Before we begin, here is the content of the Titles table that you can refer while going through the article:

Content of Titles Table
Fig 1. Content of Titles Table

2. Simple Scalar-Valued Function

Just like a stored procedure the SQL Server function also performs a specific task and returns value to the caller. Inside the body of the function, the return value to the caller is decided. Once the function is created, it can be used in the same way how one can use the in-build functions of SQL Server.

The below picture shows set of T-SQL for creating a user defined function called TotalBooks. This function counts number of titles from the Pubs.Title table and returns that information to the caller.

Simple Scalar-Valued Function Example
Fig 2. Simple Scalar-Valued Function Example

Just like a stored procedure, the keyword “Create Function” is used to create the function (Marked as 1) with the name (Marked as 4) you like. Since the function returns a value to the caller, the keyword “Returns” is used to specify type of data returned by the function. In our Example, the function TotalBooks returns an integer data type (Marked as 2).

In the body of the function, the duties of the function and what it should return to the caller are decided. The body of the function goes in between the “Begin & End” pair. In our Example, the variable BookCount (Marked as 5) is declared as integer. Total number of the title is computed by using a Count aggregate function and the count is assigned to the internal member (Marked as 6) called @BookCount. Then this book count is returned (Marked as 3 and 7) to the caller of the function. Notice the use of “Returns” (Marked as 2) and “Return” (Marked as 3) where we usually make mistake. Once the function is created, you can verify it by expanding the “Scalar-Valued Function” node under the “Programmability” node of the Database as shown in the below picture.

User Function in Database Tree
Fig 3. User Function in Database Tree

While executing the function, the function name should be qualified with the schema name (In our case it is the default dbo schema). Also do not forget to place the parenthesis after the function name. The function can be executed as follows:

Executing the Scalar-Valued Function
Fig 4. Executing the Scalar-Valued Function

The below given video shows creating and executing the Function which is  discussed above.
Video 1: Simple Scalar-Valued Function

3. Scalar-Valued Function with Parameters and Default Parameter

In the previous section, we had created a simple "Scalar-Valued function". A Scalar-Valued function can take parameter also. In this section we will create a function that returns a value which is the total of all titles that belongs to a given book section. To do this, the function will expect a “Book Section” as parameter from the caller. Have a look at the below example:

Scalar-Valued function with Parameter
Fig 5. Scalar-Valued function with Parameter

Here, we are creating a function called TotalPrice which accepts a parameter called “Type” (Marked as 1). This parameter evaluates to NULL when the caller does not supply value to it. Note that this parameter type should match with the datatype of “Type” column in the Title table as we may supply the parameter value from table column also. Since the Total Price is retrieved from the Price column of the Title table, the return type is given as Money (Marked as 1) again matching with the corresponding table column type. The “Null on Null Input” states that, the function returns immediately when one or more null parameter is encountered in the parameter list (Marked as 2).

Other part of the function is similar to what we have written in the previous section. However, you can see how the parameter passed-in is used inside the body of the function. The parameter passed is used in the where-clause of the select query formed inside the Function body (Marked as 3). Here, the total price of book section specified by the parameter is retrieved and assigned to the local variable @PriceTotal. This variable is then returned to the caller of the function.

4. Passing Parameters to Scalar-Valued Function

In the above example, you may think leaving empty in the parameter list will supply the default value of null. But, that is not right in case of SQL Server 2005. Have a look at the Query and the Error message below:

Select Dbo.TotalPrice();

Msg 313, Level 16, State 2, Line 1
An insufficient number of arguments were supplied for the procedure or function Dbo.TotalPrice.

The correct way of supplying the default parameter is shown below:

Select Dbo.TotalPrice(Default);


The above query returns null as it encounters a null parameter. Look at the example and section marked as 2. Other parameter passing techniques are explained through a picture shown below:

Fig 6. Executing Scalar-Valued function with Parameters

In the First highlighted portion of the picture, you can see the function called two times by the caller by supplying the char constants two times (mod_cook and business). The function returns the Total price value for the sections mod_cook and business.

The second highlighted section shows passing the parameter from the table itself. Here, the Type column of the Title table is passed as the parameter to the function.

The third highlighted section shows the result of executing the query. The query returns 6 rows (As we ask for distinct of Type). Hence the function is invoked 6 times and each time supplying the value of type column, which retrieved from the table "Title".

Video 2: Scalar-Valued function with Parameter

Script Listing

Listing 1.1

--Simple Function
Create Function TotalBooks()
returns int as
     declare @BookCount as int;
     Select @BookCount = Count(Title_id)
     from Titles;
     return @BookCount;

Listing 1.2

--Function with Parameter
Create Function TotalPrice(@Type char(12)= null)
returns money
with returns null on null input as
     declare @PriceTotal money;
     Select @PriceTotal=SUM(Price)
     From Titles
     Where Type=@Type;
     return @PriceTotal;

Wednesday, October 28, 2015

[ ADO.Net ] - Retrieve Table Schema Information using CommandBehavior.SchemaOnly

1. Introduction

When you are working on sometimes you need to get the table schema information like Column name and data type of that column. In this example article, I will show getting the table schema information. Have a look at the below screenshot: Retrieve Table Schema
Fig1. Table Schema for Pubs.Discounts

Here, we are going to display the table schema marked as 2 for the table “discounts” which marked as 1.

2. About the example

The screenshot of the sample is shown below: Retrieve Table Schema
Fig 2. Example Application Screenshot

The item marked as 1 is a multiline textbox control used to display the table schema shown in section 1 of this article. The “Get Table Schema” button (Marked as 2) once clicked displays the schema information of the discount table in the multi-line textbox. Before using the sample, you should configure the connection string to the Pubs database. Setting the connection string for the “NorthWnd“ database is shown in the below video. The same procedure can be followed to set the connection string for the Pubs database (Name the connection string as PUBSDB) as the table DISCOUNTS resides in it.

Video 1: Forming the connection string

3. Code behind the form

1) First, the required namespace is used in the .cs file. Code is below:

 //Sample 01: For accessing the required functionality
using System.Data.SqlClient;

2) Next, connection to the Pubs database is established by making use of the connection string “PubsDB” formed in the previous section. The SqlConnection object is created using the connection string which is stored as the application property. Once the connection object is created, the Open method on the object is called to establish the connection to the Pubs database. Below is the code:

//Sample 2.1: Open Connection to SQL
SqlConnection Con = new SqlConnection(Properties.Settings.Default.PubsDB);

When you are typing the above code, the intelli-sense displays the Connection string name when it is configured as the application property. The below screenshot shows the that: Retrieve Table Schema
Fig 3. Intellisence for ConnectionString property

3) After the successful connection, a SqlCommand object is created on the DISCOUNTS table of the PUBS database. Then, ExecuteReader method on the Cmd object is called by passing the parameter CommandBehavior.SchemaOnly. This informs the command object that we need only the schema information on the underlying command object in our case it is schema information of Discounts table. The code is given below:

//Sample 2.2: Create Command Object and get schema reader
SqlCommand Cmd = new SqlCommand("Select * from Discounts", Con);
SqlDataReader Schema_Reader = Cmd.ExecuteReader(CommandBehavior.SchemaOnly);

4) Now we have the Schema_Reader of SqlDataReader which we can to iterate through to get the column information of the DISCOUNTS table. Inside the iteration loop, the schema information is retrieved and displayed in the multiline textbox. The code for this is given below:

//Sample 2.3: Iterate through the Reader and get the Schema information
int Total_Column = Schema_Reader.FieldCount;
for (int i = 0; i < Total_Column; i++)
    string schema_inormation = string.Format("Column Name: {0}, Column Type: {1}",
        Schema_Reader.GetName(i), Schema_Reader.GetDataTypeName(i));
    txtSchemaInfo.AppendText(schema_inormation + Environment.NewLine);

//Sample 2.4 : Close all the objects

Source code : Download

Wednesday, October 14, 2015

[ C# ] - Creating your own Debugger Visualizer

1. Introduction

"Debugger Visualizers" are visual studio IDE’s debugging component. These components represent the variables and class objects in meaningful form so that it can be easily visualised. Say; for example, you have class ABC to store the “Passport Size” photo of a person and using the visualizer you can see the photo of the person while debugging the object of ABC. The below video shows the existing visualizer for the strings:

Video 1: Visualizer for String

2. About the Example

The Example is divided into two parts. One is a Class Library project called VisualStack which can be opened through visualStack.sln. The another project is a Stack & Queue Example downloaded from this website (Check the C# Section). The class library project is the one we used to create the “Custom Debugger Visualizer” for the Stack Object.  Once the project is ready and visualizer is deployed, you can invoke the visualizer for any Stack object as your visual studio IDE understands how to visualise the Stack object through the deployed class library project.

Have a look at the below screenshot:

Custom Debugger Visualizer
Fig 1 - About the Debugger Visualizer Example

The Item marked as 1 show five elements pushed into the Stack object of the Dot Net Framework (2.0). Item marked as two shows the Visual studio debugger showing the Visualizer written by us. If you write multiple visualizers for the same Stack object, all are listed here and you can select the default one by placing the check mark. Clicking the lens icon invokes the default visualizer. The item marked as three shows the simple stack visualizer which lists all the items in the stack. Left is the Top of the Stack and right is the bottom of the stack.

3. Create Class Library Project

Follow the below steps to Setup the Project (or) Watch the video in this Section End.

1) Create a class library project and this project will be deployed to Visual Studio 2005 IDE so that IDE understands the visualizer exists for System.Collections.Stack. Below screen shot shows the Project creation steps:

Custom Debugger Visualizer
Fig 2 - Creating Class Library Project

2) Once the Project is created, Add assembly reference to the project, so that we can make use the Visual Studio debugger visualizer. The screen shot below shows adding the required reference - Microsoft.VisualStudio.DebuggerVisualizers. 

Custom Debugger Visualizer
Fig 3 - Adding Project Reference

3) After the reference is added to the class library project, add a form to the Project and name the file as StackView.cs. This is shown in the below screenshot:

Custom Debugger Visualizer
Fig 4 - Adding form to the Project

Video 2: Starting the Project

4. The DebuggerVisualizer Attribute

In the previous step, we had created a class library project. Now we will set the DebuggerVisualizer attribute to our class library and note that the DebuggerVisualizer attribute will be set at the assembly level. First, we will start using the required namespaces as shown in the below code:

//Sample 01: Add Reference, then use the below
using Microsoft.VisualStudio.DebuggerVisualizers;
using System.Diagnostics;
using System.Collections;

The namespace Microsoft.VisualStudio.DebuggerVisualizers is used to access the DialogDebuggerVisualizer which we will see soon. The “System.Diagnostics” namespace is used to have access to DebuggerVisualizer Attribute". Finally in order to use the Stack, System.Collection namespace is used by this class library project.

Once the required namespaces are included, the visualizer attribute is set to the class library project. This attribute is set at assembly level by making use of the “assembly:<attribute>” notation. The code for the attribute is shown below:

//Sample 02: Add the attribute for Custom Visualizer
[ assembly: DebuggerVisualizer(
    Description="Custom Visualizer for Stack")
namespace CustomVisualizer

To have a better understanding of the above attribute, have a look at the below picture:

Custom Debugger Visualizer
Fig 5 - Debugger Visualizer attribute Explained

The attribute shown in the above picture is by-parted and explained as below:
1: The debugger visualizer attribute set at the assembly level
2: The class used as the Custom visualizer. In our case, StackVisualizer class takes that responsibility of visualising the Stack object
4: The class that we want to represent visually. In our case, we want to write custom visualizer for Stack object
3: The bridge between Visualizer and object that needs to be visualised. In our case, the Bridge is between Stack and StackVisualizer
5: Description string that appears in the debug window (Under the lens icon)

5. Visualizer Dialog

In section three we already added a form to the class library project. Open the form and add a text box control to it. Set the multi-line property to true and also provide proper docking and anchor property so that it occupies entire area of the form even when the form is resized. The form is shown below:

Custom Debugger Visualizer
Fig 6 - Adding Visualizer Dialog

The video below shows setting up the form with the multi-line box control in it.

Video 3: Setting up the Visualizer Form

After the form design, access the code for the form and add a public function called SetStack. This function takes the Stack object and displays the content of it in the multi-line textbox control. To iterate through the stack object, "IEnumerable" is extracted from the stack object and it is iterated through the foreach construct. The code for that is shown below:

//Sample 05: Implement the set stack which displays the Stack Content
public void SetStack(Stack stack)
    IEnumerable ItemCol = (IEnumerable)stack;
    foreach (int item in ItemCol)
        txtStackView.Text = txtStackView.Text + "[" + item.ToString() + "] ";

6. Coding the Visualizer

1) Inherit the default class provided by the class library project from DialogDebuggerVisualizer. Now our class has the behavior of the debugger visualizer. The code is given below:

//Sample 04: Inherit from DialogDebuggerVisualizer
public class StackVisualizer: DialogDebuggerVisualizer

2) Next override the “show() method" of the DialogDebuggerVisualizer. The parameter "IDialogVisualizerService" is used to display the dialog that we developed in the previous step. The second parameter "IVisualizerObjectProvider" is used to supply the target object for which we are writing the custom visualizer. In our case, the second parameter supplies the Stack object from the debug session. The function signature is shown below:  

//Sample 05: Override the show method
protected override void Show(IDialogVisualizerService windowService,
    IVisualizerObjectProvider objectProvider)

3) The stack object from the debug session is taken from the objectprovider and stored in a reference called stack. The form created in section 5 of this article is instantiated and stored in the reference frm. The Stack object from debug session is supplied to the visualizer form by calling the “SetStack” method and we know that the SetStack method iterates through the stack content and displays that in the multi-line textbox. Once everything is ready, the form is displayed by calling “ShowDialog() method" of the IDialogVisualizerService. The Code is shown below:  

//Sample 06: Handover Stack from debug session and
// display information the way you want
Stack stack = (Stack) objectProvider.GetObject();
StackView frm = new StackView();

7. Deploying the Custom Visualizer

Once the coding of class library project (i.e.) our custom visualizer project is completed, build the project to create the dll output. This dll should be deployed to the debugger visualizer folder of the Microsoft Visual studio installation location. In the below screenshot the location in which the debugger to be copied is shown. The yellow highlighted dll are already existing dll(s) and blue highlighted one is custom visualizer developed by us. Once the copy is over, restart all the visual studio instance (if already running). After the restart, you can see custom visualizer invoked for "System.Collections.Stack".

Custom Debugger Visualizer
Fig 7 - Deploying custom debugger Visualizer

Video 4: Deploying and using Stack Visualizer

Source Code: Download

Like this site? Tell it to your Firend :)