September 29, 2014

SQL 2005 - Sql server stored procedure examples

1. Introduction

A "Stored Procedure" in Sqlserver is an executable program unit that can be stored in the database as objects. These stored procedures can be executed from client application programs to get good performance. The stored procedure can be called from other scripting or even from some other stored procedures.

The communication between SqlServer Stored procedure and client application happens through parameters and return values. The below picture shows that:

Figure 1: SqlServer Proc. & External App. Communication
Figure 1: SqlServer Proc. & External App. Communication

Client applications optionally sends data to the stored procedure and stored procedures perform processing based on the data sent by the client application. After the stored procedure finished its work it packs data back (Again optional) to its caller through various output techniques like:

  1. Sending data via Output parameter type
  2. Sending data via return statement
  3. Sending data by select statement

We will see all these stuff in this article.

2. Creating & EXEC Stored Procedure in Sql

You can create a stored procedure in the SQL Server Management Studio. Once the procedure is created it will be listed in the Programmability node of the database in which the procedure create statement is executed. To execute the Procedure use the “EXEC” followed by the procedure name.

The structure of the stored procedure is shown in Fig.2. While creating a procedure, the name of the procedure is given first followed by one or more parameters passed to it. The parameter(s) can be optional. After the parameter, the procedure body will be written to perform the required operations. The body can have local variables declared in it and these variables are local to the procedures (i.e) they can be viewed only inside the procedure body.

Figure 2: Basic Skeleton of SQLServer Stored Proc
Figure 2: Basic Skeleton of SQLServer Stored Proc

In the Fig.3 below, a simple stored procedure is shown skipping all the optional parts of the procedure. So to create a procedure, we need a procedure name and at least a single statement as procedure body. Note that it will be good practice to create and execute the procedure with the schema name qualifier. In the below example, the schema name qualifier is dbo.

Figure 3: A Simple Stored Proc. Example
Figure 3: A Simple Stored Proc. Example

The procedure body can have any kind of the SQL statements like creating a table, inserting one or more table row(s), setting the behaviour of the database etc.,. However, the procedure body restricts certain operations in it. Some of the important restrictions are listed below:

  1. The body should not create some other stored procedure
  2. The body should not create a view
  3. The body should not create any triggers.

The below-given video, shows creating and executing a simple stored procedure using SQL Server Management Studio:

Video 1: Creating a Simple Stored Procedure

3. Declaring Variable in Procedure Body

You can declare one or more variable(s) local to the procedure body and these variables are visible only inside the body of the procedure. You can declare variables anywhere in the body of the stored procedure. But, The good practice is declaring the variables in the beginning of stored procedure body as shown in Fig.2. Have a look at the Sample procedure given below:

Figure 4: Declaring Variables in Stored Proc.
Figure 4: Declaring Variables in Stored Proc.

In the above procedure, you can see variables Name1 and Name2 declared in the beginning of the procedure (Marked as 1). Here you can note that multiple variables are declared in a single line and each variable declaration is separated by a comma. Also, note that the variable has prefix @. In the procedure body you can declare variable wherever you want and in the example, the variable @Name3 is declared towards the end of the Procedure body. To assign a value to a declared variable, the "Set Keyword" is used (Marked as 3) and this is shown in above example. Unlike declaring more than one variable in a single line, only one set statement is allowed in a line. The result of executing the above procedure is shown below:

Figure 5: Executing the Procedure SayHello2
Figure 5: Executing the Procedure SayHello2

OK. In the procedure body, How do I assign multiple values in a single statement ? Well. The answer is using the “Select Var=value” pairs. And you can use these pairs by separating it with a comma. The below screen shot shows this:

Figure 6: Multiple Variable Assignments in Single Statement
Figure 6: Multiple Variable Assignments in Single Statement

4. SQL stored procedure parameters

In the previous examples, we saw creating a simple stored procedure and executing it. A procedure can accept parameters and the caller of the procedure passes values (But, Not always. We will see that soon) to it. Based on the passed in value, the procedure takes relevant action inside the body of it. For example, let us create a procedure that will accept City and State from the caller and tells how many authors belong to the passed-in City and State. The procedure will query the Authors table of the Pubs database to perform this author count. To have this database, Google it, or download SQL script from the SQL2005 page if this Web site. Below is the example procedure:
Figure 7: Executing Procedure with Parameters

In the above example procedure, the procedure accepts two parameters named @State and @City and this is marked as 1 in the above screen shot. The data type matches the type defined in the table definition for convenience. The procedure body has the internal variable @TotalAuthors and this variable is used to display the number authors. The parameters passed in are used to form “WHERE” clause of the select query that counts the number of authors. This is marked as 2 in the screenshot. Finally, the counted value is printed to the output window through the Print statement and the output is marked as 5.
There is two way to execute the procedure. The first way, marked as 3, shows passing the parameters as comma separated list after procedure name. In our example we are passing the values CA, Berkeley separated by a comma. These values are collected by the procedure parameter variables @State and @City. In this method, the parameter passing order is important. This first technique is what called “Passing Argument by Ordinal Position”. In the second way, marked as 4, after the procedure name, the parameters are directly assigned and in this case, the order is not important. This second technique is known as “Passing Named Arguments”.  This is explained in the below video:

Video 2: Executing procedure with Parameters.

The screen shot below shows creating a stored procedure with default parameters and executing it. The procedure is same as the one created in the previous screenshot and only the parameters as shifted. That is Parameter @City kept first and @State kept next with default value.  The default parameter is marked as 1 in the below screen shot. Look at the Procedure execution (2, 3) and in the first execution (3) we passed both the parameters. In this case, the passed-in parameter ‘UT’ replaces the default value ‘CA’ (Shown as 4).  In the second execution we passed only one argument value ‘Covelo’ for the parameter @City and the parameter @State takes the default value ‘CA’. It is good practice to have all the default parameters towards the end of the parameter list. Otherwise, the execution marked as 2 is not possible and you should go with Passing Named arguments.

Figure 8: SQL Stored Procedure with Default Parameters

5. SQL stored procedure returning data

The three important ways of sending the data to the caller of the stored procedure is listed below:
1) Stored Procedure Returning value
2) Stored Procedure Output parameter
3) Select from stored Procedure
We will look at each technique one by one.

5.1 SQL Stored Procedure Return statement

In this technique, the procedure assigns a value to a local variable and returns that. A procedure can directly return a constant value also. In below example, we created a procedure that returns the total number of author. When you compare this procedure with the previous one, you can see the Print statement is replaced by the return statement.

Figure 9: SQL Stored Procedure with return statement

OK. Now let us see how we can execute the procedure and print the value returned by the procedure. The procedure execution requires declaring a variable and printing that after the procedure execution. Note that instead of print statement you can use Select statement like “Select @RetValue as OutputValue”. The below code snippet shows both the way of checking the procedure return value:

Figure 10: Exec Stored procedure with return statement

5.2 SQL Stored Procedure OUTPUT parameter

The return statement can be used to return a single variable and that we saw in the previous example. Using the Output parameter type a procedure can send one or more variable values to the caller. The output parameter is indicated by the Keyword “Output” while creating the procedure. When a parameter is specified as an output parameter, the procedure body should assign a value to it. Have a look at the Procedure example given below:

Figure 11: Stored Procedure with Output Parameter type

In the above procedure, two output parameters named @TotalAuthors, @TotalNoContract is specified in the parameters list. These variables are assigned values inside the procedure body. When we use output parameters the caller can see the value that is assigned inside the body of the procedure. The below picture shows executing the Procedure and retrieving the values through the output parameter:

Figure 12: Exec stored procedure with output parameter

In the above script, two variables are declared to see the values packed by the stored procedure in the output parameter and this is marked as 1. Then the procedure is executed by supplying the normal parameter values CA and Berkeley. The third and fourth parameters are output parameters and hence the declared variables @OutputVar1, @OutputVar2 are passed to the procedure (Marked as 2). Note that while passing the variables the keyword output is specified here also. Once the procedure is successfully executed the values returned through the output parameters are printed to the messages window and this is marked as 3.

5.3 Select from stored procedure

The Select…From technique is used to return a set of values in the form of a table of data (RecordSet) to the caller of the stored procedure. In the below example the stored procedure accepts a parameter called @AuthID and queries the table Authors by filtering the records returned by using this @AuthId parameter. The Select statement is marked as 1 in the below picture decides what needs to be returned to the caller of the stored procedure. When executing the stored procedure “Author Id” is passed in (Shown as 2). The result of executing the stored procedure is marked as 3. The example procedure here always returns only one record or none. But a stored procedure does not have any restriction to return more than one record. Look at Fig.6 which shows returning data through select involving the computed variables.

Figure 13: Select from stored procedure

6. Conclusion

A stored procedure is powerful compared to a function in the front end doing the same thing. Since the stored procedure gets executed in the server itself, the data exchange in huge volumes between server and client application (for certain computations) can be avoided. In this article, combining the data return techniques (For Example- Procedure returning data through return statement as well as an output parameter) is not shown. But you can learn that yourself.  Bye for now.

September 10, 2014

C# - Dynamically updating the App.Config file

1. Introduction

In the last article, we saw how to use the app.config file, its hierarchy, and priority in picking the application settings. In this article, we will see updating the app.config file at runtime. The article example shows adding, deleting and updating a key entry the app.config file.

Have a look at the below screenshot taken from the app.config file:

The boxed item is one pair of setting; say for an example, an application by providing the key Key_4 can access the setting value value_4. In our article, we are going to see how do we add, delete and update these kinds of settings that come under "appSettings Section" of the configuration file.

2. About the example

The screenshot of the sample application is shown below:

The result of the action performed on the application configuration file (App.config) is communicated through the “Display Area”. To add (Through button Marked as 5) a setting to the application configuration file, the key and value should be entered in the Setting Key, Setting Value text boxes marked as 2 and 3 in the screenshot. To retrieve (Marked as 6) the setting, the key should be specified in the Setting Key text box. The retrieved key is displayed in the display area as well as in the Setting value. To modify (Through button marked as 7) an existing setting, specify the existing key and new modified value in the Setting Key, Setting Value respectively. To delete (Marked as 8) a key specify the key in the Setting Key. Note all these actions are performed at run time.

OK. Let us go through the coding part of this example.

3. Adding New Configuration Entry Dynamically

1) To add a new Application configuration entry, first we should make sure that the entry does not exist in the Application configuration. In the below code, first the application configuration file is opened by making a call to "OpenExeConfiguration() Method". Note that the parameter is passes is the executable file name. Then "KeyValueConfigurationCollection" object is retrieved from the opened configuration file through the "AppSettingsSection". The reference stored in app_settings now represent the entire key, value pair of the entries that present inside the <appSettings>. To get a particular entry in the <appSettings> section, the key name is given to the collection app_settings. In our case, the value entered in the “Setting Key” text box is supplied to the app_settings to retrieve single pair that resides in <AppSettings> and if the key does not exists, a null is returned instead of a valid "KeyValueConfigurationElement" object. Below is the code that makes sure the setting entry exists:

//2.1 Get AppSettings section from the Config
Configuration config = ConfigurationManager.OpenExeConfiguration("ReadConStr.exe");
AppSettingsSection App_Section = (AppSettingsSection)config.GetSection("appSettings");
KeyValueConfigurationCollection app_settings = App_Section.Settings;
KeyValueConfigurationElement element = app_settings[txtKey.Text];
if (element == null)

2) Once we validated that the setting key is new, we allow adding a new setting entry in the application setting file. A new key-value pair element is created and added to the instance of Key-Value configuration collection. Once the collection is updated with the new entry, the application configuration file is saved to store this new entry. Below is the code:

//2.2 Grab Key-Value pair & Make your Change
KeyValueConfigurationElement new_element = new KeyValueConfigurationElement(txtKey.Text, txtValue.Text);

//2.3 Save the new appsection to App.Config
txtDisplayOutput.Text = "Saved";

3) Apart from this, remaining code is written to specify the result of the Action (Adding a Config. Entry). Below is entire code for adding a configuration entry:

//Sample 02: Adding application Settings to Config File
private void btnAdd_Click(object sender, EventArgs e)
    //2.1 Get AppSettings section from the Config
    Configuration config = ConfigurationManager.OpenExeConfiguration("ReadConStr.exe");
    AppSettingsSection App_Section = (AppSettingsSection)config.GetSection("appSettings");
    KeyValueConfigurationCollection app_settings = App_Section.Settings;
    KeyValueConfigurationElement element = app_settings[txtKey.Text];
    if (element == null)
        //2.2 Grab Key-Value pair & Make your Change
        KeyValueConfigurationElement new_element = new KeyValueConfigurationElement(txtKey.Text, txtValue.Text);

        //2.3 Save the new appsection to App.Config
        txtDisplayOutput.Text = "Saved";
        txtDisplayOutput.Text = "Key Already Exists";

    //2.4 Clean up
    txtKey.Text = "";
    txtValue.Text = "";

4. Retrieving the Configuration Entry without re-starting

When an application setting is added dynamically it can be retrieved dynamically. The code below retrieves the newly added application entry without re-launching the application again. Here we check that the key entry already exists in the application configuration file and if so, we display the corresponding value in the Display Area.

//Sample 03: Read Application settings
private void btnAppSettings_Click(object sender, EventArgs e)
    //3.1 Get AppSettings section from the Config
    Configuration config = ConfigurationManager.OpenExeConfiguration("ReadConStr.exe");
    AppSettingsSection App_Section = (AppSettingsSection)config.GetSection("appSettings");
    KeyValueConfigurationCollection app_settings = App_Section.Settings;

    //3.2 Retrieve Specific element by Key and Display it
    KeyValueConfigurationElement element = app_settings[txtKey.Text];
    if (element != null)
        txtDisplayOutput.Text = element.Value;
        txtValue.Text = element.Value;
        txtDisplayOutput.Text = "Setting Not found";

5. Modifying the application configuration Entry

To modify the key-value pair in the application configuration, grab the key value pair, remove it and add the new one. In the below code we search for the specific key, grab the configuration entry and drop it. Then, a new entry with the same key name and modified value is pushed into the application configuration collection. As the code is similar to what you learned already, I am skipping the further explanation here. Below is the complete code for modifying the configuration entry:

//Sample 04: Modify Application Settings
private void btnModify_Click(object sender, EventArgs e)
    //4.1 Get AppSettings section from the Config
    Configuration config = ConfigurationManager.OpenExeConfiguration("ReadConStr.exe");
    AppSettingsSection App_Section = (AppSettingsSection)config.GetSection("appSettings");
    KeyValueConfigurationCollection app_settings = App_Section.Settings;
    KeyValueConfigurationElement element = app_settings[txtKey.Text];

    //4.2 Grab Key-Value pair & Make your Change
    if (element != null)
        element.Value = txtValue.Text;

        //4.3 Save the new appsection to App.Config
        txtDisplayOutput.Text = "Setting Modified";
        txtDisplayOutput.Text = "Setting Not Found";

    //4.4 Clean up
    txtKey.Text = "";
    txtValue.Text = "";

6. Deleting the application configuration Entry

By this time, you may be aware how to do this. Yes, similar to previous section code except after dropping the configuration section we save the configuration file.

//Sample 05: Delete Application Settings
private void btnDelete_Click(object sender, EventArgs e)
    //5.1 Get AppSettings section from the Config
    Configuration config = ConfigurationManager.OpenExeConfiguration("ReadConStr.exe");
    AppSettingsSection App_Section = (AppSettingsSection)config.GetSection("appSettings");
    KeyValueConfigurationCollection app_settings = App_Section.Settings;
    KeyValueConfigurationElement element = app_settings[txtKey.Text];

    //5.2 Remove the setting by Key
    if (element != null)
        txtDisplayOutput.Text = "Setting Removed";
        txtDisplayOutput.Text = "Setting Not Found";

    //5.4 Clean up
    txtKey.Text = "";
    txtValue.Text = "";

The video, given below show how the Example application works:

Source Code : Download

July 21, 2014

C# - App.config file in C-Sharp windows application

1. Introduction

Let us say you are developing an Internet modem device monitor utility application. The duty of the application will be monitoring for Upload and download activity of the modem. When there is heavy download say 500KB per second of download for past 10 Minutes, the device should intimate that to a user. The intimation can be done either by blinking a Red LED modem device or by making continues beep or by making the entire desktop with red transparent overlay (System got RED by download J). But, which option the utility should choose from? Now I hear you are saying keep it as an "Application Setting".

In old days, Legacy windows desktop software uses section based Initialization configuration files ("INI Files") and application reads and writes from it. The settings are placed on sections which are nothing but grouping the settings under a certain category. Now in dot net days, the INI files are replaced as application CONFIG files. Application settings are placed in this XML format application configurations files. In this article, I will explain how configuration files can be accessed by the application with an example.

2. Configuration File Hierarchy

The configuration file has a hierarchy and the application will search for required settings based on this hierarchy. Have a look at the below picture:

The first thing to be noted is that Web Application looks for settings in the web.Config” whereas windows desktop application will look for the settings in the “App.Config” file. Let us say a web server machine Johns-Kitten is hosting three websites and all three websites are from the same publishers.  Now the settings common to all three websites goes to “Root.Web.Config” and settings specific to one website or solution goes to the "web.config file".

The settings, which are common to both web application and desktop application, can go in “Machine.config File". The settings defined in the bottom most overrides the settings given in the above level. For example, the setting given in the web.config overrides the settings given in the “Machine.Config” file (i.e.) if the same setting is defined in both the machine.config and web.config, web.config setting takes the precedence. In this article, we are going to explore how do we use the App.Config file in desktop c# windows applications.

3. About the Example

Have a look at the below application screen shot:

Clicking the “Application Setting” button marked in the picture (as 4) will retrieve the application settings from the App.config file and displays that in the Display Output (Marked as 1).  A key will specify the setting that needs to be retrieved and that key will be entered in the textbox marked as 3. Clicking the button “Get Connection string” (Marked as 2) will retrieve the connection string defined in the app.config file and displays that in the display output. Specifying the name of the connection string will retrieve the connection string.  In our sample, connection string name defined in the App.config file is referred by the checkboxes marked as 5 in the screenshot.

4. Create the Project

Create a Windows C# application called ReadConStr. Once the exe project is created we can store all the application settings for this ReadConStr.exe into a configuration file called App.Config and read the settings from it through the “system.configuration” assembly. To add this assembly to the ReadConStr project:

  1. Open the Solution Explorer
  2. Right Click the Reference node
  3. Click on the “Add Reference…” menu item from the context menu.

This will display an “Add Reference” dialog. You can pick the System.Configuration assembly from this dialog as shown in the below screenshot. Once you pick the System.Configuration from the add reference dialog, the reference is added to the project and you can examine that by expanding the reference node.

After adding the reference, add the using statement to make use of the application configuration file:

//Sample 01: Required Config Entries
using System.Configuration;

5. Add Appsettings and ConnectionString

OK. Our application is ready to read configuration settings and where is the configuration file? We should add this configuration file to the application.  To add the application configuration file to the project follow the steps listed below:

1) Go to “Solution Explorer”
2) Right click the project name “ReadConStr” and chose “Add=>New Item…”

3) In the “Add New Item” dialog, select the option, “Application Configuration File” and leave the default name “App.Config” as it is. And click on the “Add” button. This will add the application configuration file to your project.

Once the configuration file (App.config) is added to the project, we can place our application wide project settings into this configuration file. To add the application settings, open the app.config file from the solution explorer and add the following code:

The application keeps all the settings inside the AppSettings tags. The setting goes with a "key-value pairs". For example, the application will ask for the setting by providing the key say “Key_4” and gets the corresponding setting value say, Value_5.  Like application settings, Connection string occupies a separate section as shown below:

In the above case, the database connection strings are enclosed between ConnectionStrings tags. The application specifies the “name” and retrieves the connection string and/or provider details. In the above example, by knowing the name “XYZ1”, the application can get connection string “XYZ2” and provider name “XYZ3”. If you are not expertise in the connection string, Watch the below video to place connection string in your App.Config file:

6. Reading AppSettings from App.Config

The event handler for the “Application Setting” button is given below:

//Sample 02: Read Application settings
private void btnAppSettings_Click(object sender, EventArgs e)
    string setting = ConfigurationManager.AppSettings[txtKey.Text];
    if (setting == null)
        setting = "Setting not found";
    txtDisplayOutput.Text = setting;

The "ConfigurationManager Class" has the collections AppSettings, ConnectionStrings. In this event handler, we refer the AppSettings map and retrieve the required setting value by supplying the key. The key will be specified text box noted as 3 in the application screenshot. The retrieved value is shown in the result display area.

7. Reading ConnectionString from App.Config

Now have a look at the code below, which reads the connection string setting from the App.Config file:

//Sample 03: Read Connection String
private void btnConString_Click(object sender, EventArgs e)
    //3.1 Declarations
    ConnectionStringSettings setting_constr = null;
    string con_str = "";

    //3.2 Get Connetion String for Pubs and/or NorthWnd
    if (chkPubs.Checked == true)
        setting_constr = ConfigurationManager.ConnectionStrings["Pubs"];
        con_str = setting_constr.ConnectionString + Environment.NewLine;
    if (chkNWnd.Checked == true)
        setting_constr = ConfigurationManager.ConnectionStrings["NWind"];
        con_str = con_str + setting_constr.ConnectionString;
    if (con_str == "")
        con_str = "Specify either Pubs or NWnd";

    //3.3 Display the output
    txtDisplayOutput.Text = con_str;

The above code runs the when the “Get Connection String” button is clicked in the form. Here also we refer the ConnectionStrings map from the ConfigurationManager object to get the corresponding "ConnectionStringSettings" object. This connection string map requires the name as the key. Look at our sample connection strings in the app.config; XYZ1 and abc1 are the names of the connection strings. The actual connection string is then retrieved from this ConnectionStringSettings object. In our example, we assign the ConnectionStringSetting object into setting_constr when the corresponding checkbox is checked. The “ConnectionString” property from this setting_constr will be referred to get actual connection string text. Once we have connection string setting that will be displayed in the display output.

8. Changing the fore color of display area

Now let us assign the fore color for the output display area. This setting shows a real-time example of how the configuration file will be used to change application behavior without re-compiling it. Have a look at the below code which reads the fore color setting from the configuration file and sets the multi-line textbox accordingly:

//Sample 4: Application Background Color
private void frmAppConfig_Load(object sender, EventArgs e)
    //4.1: Read setting
    string ColorSetting = ConfigurationManager.AppSettings["FontColor"];
    Color ForeColor;

    //4.2 Have a relevent Color
    if (ColorSetting == "Red")
        ForeColor = Color.Red;
    else if (ColorSetting == "Blue")
        ForeColor = Color.Blue;
        ForeColor = Color.Green;

    //4.3 Assign it to the Text Display
    txtDisplayOutput.ForeColor = ForeColor;

The above code is written in the form load event handler. The code is similar to what you saw in section 6, but here we use the application setting to change the behavior of the textbox. Or simply, the text display color of the textbox will get changed based on the color setting given in the application configuration file.

9. Running the Application – Video

The below-given video shows the how the example works with the application configuration file. This video also shows changing the behavior of the textbox just by changing the configuration file.

Video: Running the Application 

Source Code: Download

June 13, 2014

ASP.Net - CustomValidator with client validation function script

1. Introduction

Validation controls are useful to perform the validation on the web forms. The framework provides validation controls like RequiredFieldValidator, RangeValidator and CompareValidator etc. Sometimes, these validation controls are not adequate to perform the special validations. In those situations, we will go for the CustomValidator control. This article will walk you through the usage of the CustomValidator control and shows how will you link the scripting function as the validation function.

2. About the Example

The below screenshot shows the example we are going to create:

Fig 1. CustomValidator Example

The Bonus percentage textbox is the control we are going to validate. The control marked as 2 is the custom validation control. Thos control validates the data entered in the percentage and throws the error when bonus percentage is more than 30. OK, let us move further.

3. CustomValidator control

With CustomValidator control, we can do validation on the Client Side alone or Sever side alone or in both places.  The important properties that need to be set for making use of the client-side scripting are shown in the below picture (Marked as 1).  The property ControlToValidate is set to identify the UI element we want to validate. In our example, we are going to validate bonus textbox (txtBonus). The property ClientValidationFunction is used to specify the client-side scripting function that performs the validation. The error message that needs to be displayed in the custom validation control is set by the ErrorMessage property. This is marked as 2 in the below picture.

Fig.2: ClientValidationFunction Property

Once we link the control to be validated with the Scripting function name, we start validating the value represented by that server-side Form U control. To better understand this have a look at the below depiction:

The CustomValidator acts as a bridge between the Server side control that needs to be validated and Client side scripting function that does the validation. The value represented by the control is passed as the parameter to the scripting function. Whereas the scripting function makes use the Value and applies scripting power to do custom validation. The scripting function, responds back to the CustomValidator by setting the IsValid property to either true or false. When the value is false, the CustomerValidator reacts by displaying the Error string set the ErrorMessage property.

Fig.3 : CustomValidator & Client Scripting 

4. The Scripting Function

Below is the scripting function that performs the validation. The value entered in the bonus text box is referred here like args.Value.  Once the value is collected in the local variant variable intBonus, it is validated to make sure that bonus percent should not exceed 30%.

<script type="text/javascript">
function CheckBonus(src, args)
 var intBonus = args.Value;
 if (intBonus > 30)
  args.IsValid = false;
  args.IsValid = true;

The below video explains Making use of the CustomValidator control:

Video 1: Configuring the CustomValidator

Video 2: Running the sample ASP.Net Page

Source Code: Download

March 28, 2014

SQL 2005 - Creating a view and updating db through the view

1. Introduction

A View is nothing but a select query with a name given to it or we can simply say a view is a "Named Query". Ok. Why we need a view? There will be lot of answer for this. Some of the important stuff is given below:

  1. A View can bring data from multiple tables by using adequate joins and while bringing so, it may require complex filters and calculated data to form the required result set. In user point of view, all these complexities are hidden and data is queried from a single table.
  2. Some time for security purposes, access to the table and its internal details like table schema and relationships are not given to the database user. All they have is, access to a view without the knowledge of what tables are actually exists in the database.
  3. Using the view, you can restrict an user to update only some portions of the records.

2. Sql view Example

Have a look at the below picture which shows a Sql view definition:

Creating a View
Fig.1 - Creating a view

So, to create a view, just form a select query then use the create view statement on the top (shown in the above picture). Many people use the prefix “v” before the view name to denote that the database object is a view. Once the statement is executed, we can see a new database view created for us.

In the above example, we created a view from the table Titles, which is actually a Microsoft supplied sample table available in the Pubs Database. Once a view is created, data can be queried from it like how we query the data from any existing table. Below is the example that shows retrieving the data from the view:

Selecting Data From a View
Fig.2 - Selecting Data From a View

The Sql script is below:

-- Make sure Microsoft Pubs
-- database is avaiable in your DB server
Use pubs;
Create view vTitles as
      Title, type, Royalty, 
      (ytd_sales/12) as Monthly_Avg,
      (price * 0.05) as discount_price
from titles;

Select top 5 Title, Monthly_Avg from vTitles;
Drop view vTitles;

3. SQL View – Join Two Table

Sometimes it will be very useful to create a view when data is taken from two or more different tables. Later the data can be queried from the view as it is queried from a single table. Have a look at the below example:

SQL View with Three Tables Joint together
Fig.3 - SQL View with Three Tables Joint together

In the above example, first, the view created in the previous example is dropped. Then a view is created on a select query, which takes data from three different tables. The two inner joins are shown in blue colour and the data taken from the all three tables are marked in Red colour. Note that how the aliases T, Au, TA are used in the FROM clause and how they referred in the select clause of the query. Now, have a look at the below example, which queries data from the view:

Querying data from the view
Fig.4 - Querying data from the view

Surprisingly, the aliases are gone out. And when client uses the query from this view they are not aware of the fact that the data is actually queried from three different tables through table joins in the background. This is how a view hides the complexity behind it in the user perspective.

The SQL Script is below:

Drop View vTitles;

Create view vTitles as
      T.Title, Type, T.Royalty,
      (T.ytd_sales / 12) as Monthly_SalesAvg,
      (T.Price * 0.05) as Discount_price,
      Au.au_fname + ', ' + Au.au_lname as AuthorName,
      TA.RoyaltyPer as RoyaltyPercent
From Titles T
      Inner Join TitleAuthor TA
      On T.title_id = TA.title_id
      Inner Join Authors Au
      on TA.au_id = Au.au_id;

Select AuthorName, Title,
            Monthly_SalesAvg, RoyaltyPercent
From vTitles
Order by AuthorName;

4. Updating through SQL View

The data in the database can be updated using the view also. But, updating the data through SQL view has some limitations. Consider the previous example again by looking at the below picture:

Fig.5 - Joins and Calculated Columns

In the picture above, the marker 1 denotes the calculated or computed columns. That means the data taken from the data table is computed through arithmetic or data manipulation operation. For Example, the Discount_price column is computed by multiplying value 0.05 with the database column Price.  Similarly, the columns Monthly_SalesAvg, AuthorName also computed like this. The above picture also shows that data is taken from three different tables and you can see that in the From Clause which is marked as 2, 3 and 4.

The SQL View has some limitation while updating the data. The limitations are:

  1. Updating the computed columns are not possible.
  2. Updating the data that belongs to only one table can be possible. It is not possible to update data that comes from two different tables.

Now let us look at some examples.

1) The below example tries to update the columns Type and Royalty through the view vTitles. As the columns are coming from the same table called “Titles”, this update is allowed.

A Successful Update through view
Fig.6 - A Successful Update through view

2) In the below example, the update through the view “vTitles” is failed because the AuthorName column is a computed column. Have a look at the view definition and you can see that AuthorName column is formed by combining the two columns au_fname and au_lname from the table Authors. The Error “Msg 4406, Level 16, State 1” specifies here that the update is not successful as it tries to update a computed column.

Update through view Failed because of derived column
Fig.7 - Update through view Failed because of derived column

3) The Final Example is shown below. In this example we are trying to update the column from two different tables. The Column “type” is from the table “Titles” and the column “RoyaltyPercent” is from the table “TitleAuthor”.  As this update does not obey the rule of “update should be in a Single table”, the update operation got failed. This is shown as error in the message “Msg 4406, Level 16, State 1” marked as 2 in the below picture.

Updating columns from different table
Fig. 8 - Updating columns from different table 

5. SQL view with check option

Before we talk about the usage of check option, have a look at the below picture:

Fig. 9 - A view with Filtered Rows

The select portion of the vAuthors returns two rows because of the existence the where condition. So, through this view you can update only two rows. OK, what if somebody updates the view changing the state. For Example something like the below one:

Update vAuthors set state = 'OH';

These kinds of updates succeed and at the same time, only two rows gets affected as that is the whole content of the view. What happens when somebody queries something from the view? The query does not return anything as the view definition does not return any data. Look at the update statement again keeping in mind that the view is created for the state of UT and view becomes empty after the update as there is no state of OH.

To avoid the above said situation, one can create a "View with the check option". The below example shows creating the view with check option:

Creating view with Check Option
Fig. 10 - Creating view with Check Option

If you try with the update again, the below shown error will be reported as the check option guards the view:
The check option restricts the update as it affects the view content
Fig.11 - The check option restricts the update as it affects the view content

[ No Downloads for this Article ]

Like this site? Tell it to your Friend :)