Function Vs Stored Procedure

Difference between Function and Stored Procedure


  • Compiled and executed at Run time.
  • Can not affect the Database state. i.e Can not perform insert,delete and update operation.
  • Can return 1 value which is mandatory.
  • Can have Input parameters only.
  • Function can not call Stored Procedure .
  • Exception can not be handled by using try/catch block.
  • Can not go for Transaction Management.
  • Functions can be embeded in Select statement.

Stored Procedure

  • Stored in Parsed and compiled format in database.
  • Can Affect the database state by using DML commands.
  • Can return 0 or n value .
  • Can have Input and output parameters.
  • Stored Procedure can call Function .
  • Exception can be handled by using try/catch block.
  • Can go for Transaction management.
  • It can not be embeded in Select statement.
Posted in SQL | Tagged , , , , | Leave a comment

Populate days dropdownlist for selected month and year using javascript

In this article, i have described how we can populate number of days in day dropdownlist using javascript for the selected month and year. When we change the the month and year, the values in the days drop down will be updated accordingly. I have also taken care of number of days in February for the leap year.

File : leapyear.aspx.cs
Firstly, let us populate the default values for these three dropdowns (day, month, year) as you can see below.

protected void Page_Load(object sender, EventArgs e)
for (int i = 1; i <= 31; i++)
ddlDay.Items.Add(new ListItem(i.ToString()));

string[] strMonth = { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" };
for (int y = 0; y = DateTime.Now.Year – 20; y–)

ddlDay.Items.Insert(0, (new ListItem(“Day”, “Day”)));
ddlMonth.Items.Insert(0, (new ListItem(“Month”, “Month”)));
ddlYear.Items.Insert(0, (new ListItem(“Year”, “Year”)));

File : leapyear.aspx

<asp:DropDownList ID=”ddlMonth” runat=”server” onchange=”BindDay()”>
<asp:DropDownList ID=”ddlYear” runat=”server” onchange=”BindDay()”>
<asp:DropDownList ID=”ddlDay” runat=”server”>

And Include the below javascript at the end of the page.

<script type=”text/javascript”>
var ddlDay = document.getElementById(‘<%= ddlDay.ClientID %>’);
var ddlMonth = document.getElementById(‘<%= ddlMonth.ClientID %>’);
var ddlYear = document.getElementById(‘<%= ddlYear.ClientID %>’);
var dayCount = 0;
var leapYear = 0;

function BindDay() {
if (ddlMonth && ddlYear && ddlMonth.value != ‘Month’ && ddlYear.value !=’Year’) {
return false;

function DaysInMonth(month) {
switch (month) {
case ‘2’: switch (leapYear) {
case 1: dayCount = 29; break;
default: dayCount = 28; break;
case ‘4’: dayCount = 30; break;
case ‘6’: dayCount = 30; break;
case ‘9’: dayCount = 30; break;
case ’11’: dayCount = 30; break;
default: dayCount = 31; break;

function IsLeapYear() {
var year = ddlYear.value;
leapYear = 0;
if ((((parseInt(year) % 4 == 0) && (parseInt(year) % 100 != 0)) || (parseInt(year) % 400 == 0)))
leapYear = 1;

function BindDayDropdown() {
var myOption = “”;
for (var d = 1; d <= dayCount; d++) {
var dy = d;

if (parseInt(dy) < 10) {
dy = “0” + dy;

myOption = document.createElement(“OPTION”);
myOption.value = dy;
myOption.text = dy;

function ClearDayDropdown() {
for (var l = ddlDay.options.length – 1; l >= 0; l–) {
var selOption = document.createElement(“OPTION”);
selOption.value = “Day”;
selOption.text = “Day”;


Posted in ASP.NET, C#, Javascript and Jquery | Tagged , , , , , , , | Leave a comment

JQuery Ajax Example

Following is a code sample to make a ajax call using jquery.

In default.aspx page
//Include Jquery library
<input type=”button” id=”fetchData” onclick=”return getData();” />
<script language=”javascript” type=”text/javascript”>
function getData() {

var request = $.ajax({
url: “my-ajax-page.aspx”,
type: “GET”,
dataType: “html”

request.done(function (msg) {
}); (jqXHR, textStatus) {
alert(“Request failed: ” + textStatus);

return false;


public partial class myajaxpage: System.Web.UI.Page
protected void Page_Load(object sender, EventArgs e)

Posted in Javascript and Jquery | Tagged , , , , | Leave a comment

validate email id, input only numbers, alphabets or alphanumeric characters

Refer to jquery code below to validate numeric, alphabets, alphanumeric and validate email id fields.

Function below allows users to enter only numeric values in textbox. Regular expression is used to check if the input character is numeric or not. If it is not numeric character then the entered character will be ignored. We can just place an alert message for giving notification to user.

// Numbers only
$(function () {
$(#txtNumber).keyup(function () {
if (this.value.match(/[^0-9]/g)) {
this.value = this.value.replace(/[^0-9]/g, ”);

Similarly, function below allows to enter only alphabets.

// Alphabets only
$(‘#txtAlphabet’).keyup(function () {
if (this.value.match(/[^a-zA-Z]/g)) {
this.value = this.value.replace(/[^a-zA-Z]/g, ”);

// Alphaneumeric characters only
$(#txtAlphaNeumeric).keyup(function () {
if (this.value.match(/[^a-zA-Z0-9]/g)) {
this.value = this.value.replace(/[^a-zA-Z0-9]/g, ”);
alert(“Enter only alphanumeric characters.”)

If we consider a textbox with an ID of txtEmail, following lines of code will apply client side validation on the textbox for email address, i.e the entered value in the textbox should be a valid email address, otherwise an alert message will be displayed.

Validate e-mail address
var txtEmail = document.getElementById(‘<%= txtEmail.ClientID %>’);
if (txtEmail .value != ”) {
var EmailRegEx = /^([a-zA-Z0-9_\.\-\+])+\@(([a-zA-Z0-9\-])+\.)+([a-zA-Z0-9]{2,4})+$/;
if (!EmailRegEx.test(txtEmail .value)) {
alert(“Please enter valid email id!”)
txtEmail .focus()
return false;

Posted in C#, Javascript and Jquery | Tagged , , , , , , , , , , , | Leave a comment

Split DataTable into multiple tables

Consider a DataTable with a column name salary, following code splits the DataTable on the basis of values in salary column, i.e all the rows in DataTable with same salary will come into one DataTable.

DataTable dt= new DataTable();
dt = objEmployee.getDetails(); // Get Data from Database
if (dt.Rows.Count > 0)
List<DataTable> result = dt.AsEnumerable().GroupBy(row => row.Field<int>(“salary”)).Select(g => g.CopyToDataTable()).ToList();
int noOfTables = result.Count;

DataTable dtCommonSalary= new DataTable();
for (int loop= 0; loop < noOfTables; loop++)
dtCommonSalary= result[loop] as DataTable;
// DisplayResultBysalary definition
DisplayResultBysalary(DataTable commonSalaryEmployee)
// display result using commonSalaryEmployee DataTable

Posted in ASP.NET, C# | Tagged , , , , , , | Leave a comment

Events and ASP.Net

ASP.Net is an event-driven programming and responds to events through its predefined event handlers. When events are raised it must be handled. In ASP.NET, objects may raise events and may have assigned event handlers. For example, a button may raise the Click event and may have an OnClick method that handles the event. Besides these, there are other events in ASP.Net which fire when the page is requested. In this article, I will discuss the flow of these events.

“I have a Page which has a MasterPage and a UserControl in it. Can you tell me the flow, how the Init and Load events will be fired?”, one of my colleague asked me one day. Though i replied him, i was confused. So lets clear this confusion.

ASP.Net web page consists of
1. Page
2. MasterPage
3. UserControl.

Page is the parent control and MasterPage and UserControl reside within the page, so we can consider its child.
So when I say, if any particular event fire from Parent To Child, you need to understand that, event of Page, then MasterPage(if the page has) and finally UserControl event is fired.

Otherway round, when I say Child to Parent, then event of UserControl, followed by MasterPage and then Page event is fired.

INIT event is fired as “Child to Parent”.
So when I say this, If a page has UserControl, then UserControl’s Init event will be fired first, then only Page_Init event will be fired.
If a Page has MasterPage and UserControl in it, then the flow of INIT event would be UserControl, MasterPage INIT event and Page_Init. Since MasterPage and UserConttrol are within Page they are child of the page.

Consider a Page which has a UserControl within it. In this case Page_Load will be raised first followed by UserControl_Load Event.
If we consider a Page, which has MasterPage and UserControl within it, then the sequence of Load Event would be Page_Load, MasterPage_Load and finally UserControl_Load so basically we can say its Parent to Child event flow.

Except INIT event all other events in ASP.Net Page Life Cycle are fired from Parent To Child.

Events fired during POSTBACK
There are two flavors of postback events. The first is a changed event. This event fires when some piece of data is changed between postbacks. An example is the DropDownLists SelectedIndexChanged event, or the TextBox’s TextChanged event. Server controls that provide changed events must implement the IPostBackDataHandler interface. The other flavor of postback events is the raised event. These are events that are raised by the server control for whatever reason the control sees fit. For example, the Button Web control raises the Click event when it is clicked, and the Calendar control raises the VisibleMonthChanged event when the user moves to another month. Controls that fire raised events must implement the IPostBackEventHandler interface.

Posted in ASP.NET, C# | Tagged , , , , , | Leave a comment

Microsoft Enterprise Library for Database

What is EnterPrise Library?

Microsoft Enterprise Library is a collection of reusable application blocks designed to assist software developers with common enterprise development challenges. It includes:

  • Data Access Application Block
  • Cryptography Application Block
  • Caching Application Block
  • Exception Handling Application Block
  • Logging Application Block
  • Policy Injection Application Block
  • Security Application Block
  • Validation Application Block
  • Unity Application Block

Enterprise Library for Data Access Application Block

Scope of this article is to describe how a user can use enterprise library for data access layer.

Prerequisite required:
1.Basic knowledge of ASP.NET
2.Basic knowledge of ADO.NET
3.Basic knowledge of database

Steps to follow

Download Enterprise Library from Microsoft website and install it. After installation you will get a location where multiple “dll” files will be available. We just need to get reference of these “dll”s in order to use them in our application. After installation you will see below “dll”s are available for reference –

  • Microsoft.ApplicationBlocks.Data.dll
  • Microsoft.Practices.EnterpriseLibrary.Common.dll
  • Microsoft.Practices.EnterpriseLibrary.Data.dll
  • Microsoft.Practices.ObjectBuilder2.dll
  • Microsoft.Practices.Unity.dll

Among these libraries, we only need 2 references to get database object with configuration connection string, these are –

  • Microsoft.Practices.EnterpriseLibrary.Common.dll
  • Microsoft.Practices.EnterpriseLibrary.Data.dll


  • Create an ASP.NET application
  • Add configuration section in web.config
  • <configSections>
    <section name=”dataConfiguration” type=”Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35″/>

  • Add connection string section in web.config
  • <connectionStrings>
    <add name=”ConnectionStringName” connectionString=”Data Source=.;Initial Catalog=Northwind;User ID=sa;Password=password” providerName=”System.Data.SqlClient”/>

  • Add data configuration for default database in web.config
  • <dataConfiguration defaultDatabase=”ConnectionStringName”/>

This all are configuration setting to use Enterprise Library for database connection. Now we will see how to create a database object with this connection string.

Creating Database Objects

  • Add reference to your page where you want to create database object.
    1. using Microsoft.Practices.EnterpriseLibrary.Data;
    2. using System.Data.Common;
  • Add these lines of code to get database object
    1. Database objDb = DatabaseFactory.CreateDatabase(); OR
    2. Database objDb = DatabaseFactory.CreateDatabase(“ConnectionStringName”);

Constructor without parameter will get default database connection string whereas with parameter will search respective connection string name and created database object over given connection string.

Code To get Data from Database

Although you are done with database object, still we will move ahead with some sample code to get data from database.

DataSet dsSalesReports = new DataSet();
DbCommand dbCommand = objDb.GetStoredProcCommand(“Employee Sales by Country”);

// Adding stored procedure parameters

objDb.AddInParameter(dbCommand, “@Beginning_Date”, DbType.Date, DateTime.Now.AddYears(-25));
objDb.AddInParameter(dbCommand, “@Ending_Date”, DbType.Date, DateTime.Now);

// Loading dataset based on parameters
objDb.LoadDataSet(dbCommand, dsSalesReports, “SalesReports”);

// Binding gridview with resultset
GridViewSalesReport.DataSource = dsSalesReports;

That’s all for database connection with Enterprise Library.

Posted in ASP.NET, SQL | Tagged , , , , , | Leave a comment

Implement ICallbackEventHandler Example

In this article, I have tried to explain an alternate way of achieving the AJAX functionality using ICallBackEventHandler. Many of the times we must have used core AJAX to partially update the page content. Using UpdatePanel can be another approach to partially update the page content. Here is a very simple example to populate a dropdown list using ICallbackEventHandler.

The page needs to implement ICallbackEventHandler interface and implement its methods.

  • public string GetCallbackResult()
  • public void RaiseCallbackEvent(string eventArgument)

In the following example I have used [Northwind] database and Microsoft EnterPrise Library for database Connectivity.You can use alternate ways to connect to Database if you are not having knowledge of Microsoft EnterPrise Library.

The page contains two dropdown List, ddlCategory and ddlSubcategory. ddlCategory gets populated on Page_load and ddlSubcategory populates on index change event of the ddlCategory Dropdown.

Just have a look into the below example to learn how you can implement AJAX functionality using ICallbackEventHandler.

Default.aspx Page

<%@ Page Language=”C#” AutoEventWireup=”true” CodeFile=”Default.aspx.cs” Inherits=”_Default” %>
<head runat=”server”>
<title>Implement ICallbackEventHandler</title>

<script language=”javascript” type=”text/javascript”>

function getSubCat(obj)
CallServer(obj.options[obj.selectedIndex].value, “”);

function ReceiveServerData(retValue)
var i=0;
var j=0;
var theDropDown=document.getElementById(“ddlSubcategory”);
var numberOfOptions = theDropDown.options.length ;
for (i=0; i<numberOfOptions; i++)
//Note: Always remove(0) and NOT remove(i)

var mydata=retValue.split(‘,’);

function AddItem(Text,Value)
// Create an Option object
var opt = document.createElement(“option”);
// Add an Option object to Drop Down/List Box
// Assign text and value to Option object
opt.text = Text;
opt.value = Value;


<form id=”form1″ runat=”server”>
<asp:DropDownList ID=”ddlCategory” runat=”server” onchange=”getSubCat(this);”>
<br />
<SPAN>Sub Category</Span>
<asp:DropDownList ID=”ddlSubcategory” runat=”server”>

Default.aspx.cs Page

using System;
using System.Data;
using System.Web.Security;
using System.Web.UI;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Text;

public partial class _Default : System.Web.UI.Page, ICallbackEventHandler
    string returnValue = string.Empty;

    protected void Page_Load(object sender, EventArgs e)
     if (!IsPostBack)
      String cbReference = string.Empty;
      String callbackScript=string.Empty;
      cbReference =Page.ClientScript.GetCallbackEventReference(this, "arg", "ReceiveServerData", "context");
      callbackScript = "function CallServer(arg, context)" + "{" + cbReference + ";}";
      Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "CallServer", callbackScript, true);

    private void BindCategory()
        Database objDb = DatabaseFactory.CreateDatabase();
        IDataReader reader = null;

            reader = objDb.ExecuteReader(CommandType.Text, 
                     "select RegionID,RegionDescription from region");
            ddlCategory.DataSource = reader;
            ddlCategory.DataValueField = reader.GetName(0).ToString();
            ddlCategory.DataTextField = reader.GetName(1).ToString();
        catch(Exception ex)

            if (reader != null)

    #region ICallbackEventHandler Members

    public string GetCallbackResult()
        return returnValue; 
       // throw new NotImplementedException();

    public void RaiseCallbackEvent(string eventArgument)
        Database objDb = DatabaseFactory.CreateDatabase();
        IDataReader reader = null;
        StringBuilder sb = new StringBuilder();

            reader = objDb.ExecuteReader(CommandType.Text,
                    "select territoryID, territoryDescription from 
                    Territories where RegionID=" + eventArgument);
            while (reader.Read())
            sb.Remove(sb.Length - 1, 1);
            returnValue = sb.ToString();
        catch (Exception)
            if (reader != null)
       // throw new NotImplementedException();
Posted in ASP.NET, C# | Tagged , , , , | 1 Comment