Difference between stored procedure and function

Difference between stored procedure and function

1) Procedure can return zero or n values whereas function can return one value which is mandatory.
2) Procedures can have input, output parameters for it whereas functions can have only input parameters.
3) Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
4) Functions can be called from procedure whereas procedures cannot be called from function.
5) Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
6) We can go for transaction management in procedure whereas we can't go in function.
7) Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.

Ajax Cascading DropDownList With Database Example in GridView

There are several cases when you have two or three dropdowns in gridview and want second one (and third one) to be populated based on selection of first or second dropdownlist.

I've used Ajax cascading dropdownlist in EditItemTemaplete of GridView for updation of records in grid by fetching data from database to populate dropdowns,I've also implemented ajax auto complete extender textbox in it to edit name field.

Make sure you have created ajax enabled website and installed ajax toolkit and ajax web extensions properly.

using System.Collections.Generic;

using System.Configuration;

using System.Data.SqlClient;

using AjaxControlToolkit;

using System.Collections.Specialized;

/// <summary>

/// Summary description for CascadingDropDown

/// </summary>

[WebService(Namespace = "http://tempuri.org/")]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

[System.Web.Script.Services.ScriptService()]

public class CascadingDropDown : System.Web.Services.WebService

{

//Create global Connection string

string strConnection = ConfigurationManager.ConnectionStrings

["dbConnectionString"].ConnectionString;

public CascadingDropDown () {

//Uncomment the following line if using designed components 

//InitializeComponent(); 

}

/// <summary>

/// WebMethod to populate country Dropdown

/// </summary>

/// <param name="knownCategoryValues"></param>

/// <param name="category"></param>

/// <returns>countrynames</returns>

[WebMethod]

public CascadingDropDownNameValue[] GetCountries

(string knownCategoryValues, string category)

{

//Create sql connection and sql command

SqlConnection con = new SqlConnection(strConnection);

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;

cmd.CommandType = System.Data.CommandType.Text;

cmd.CommandText = "Select * from Country";

//Create dataadapter and fill the dataset

SqlDataAdapter dAdapter = new SqlDataAdapter();

dAdapter.SelectCommand = cmd;

con.Open();

DataSet objDs = new DataSet();

dAdapter.Fill(objDs);

con.Close();

//create list and add items in it 

//by looping through dataset table

List<CascadingDropDownNameValue> countryNames

= new List<CascadingDropDownNameValue>();

foreach (DataRow dRow in objDs.Tables[0].Rows)

{

string countryID = dRow["CountryID"].ToString();

string countryName = dRow["CountryName"].ToString();

countryNames.Add(new CascadingDropDownNameValue

(countryName, countryID));

}

return countryNames.ToArray();

}

[WebMethod]

public CascadingDropDownNameValue[] GetCities

(string knownCategoryValues, string category)

{

int countryID;

//this stringdictionary contains has table with key value

//pair of cooountry and countryID

StringDictionary countryValues =

AjaxControlToolkit.CascadingDropDown.

ParseKnownCategoryValuesString(knownCategoryValues);

countryID = Convert.ToInt32(countryValues["Country"]);

SqlConnection con = new SqlConnection(strConnection);

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;

cmd.CommandType = System.Data.CommandType.Text;

cmd.Parameters.AddWithValue("@CountryID", countryID);

cmd.CommandText =

"Select * from City where CountryID = @CountryID";

SqlDataAdapter dAdapter = new SqlDataAdapter();

dAdapter.SelectCommand = cmd;

con.Open();

DataSet objDs = new DataSet();

dAdapter.Fill(objDs);

con.Close();

List<CascadingDropDownNameValue> cityNames =

new List<CascadingDropDownNameValue>();

foreach (DataRow dRow in objDs.Tables[0].Rows)

{

string cityID = dRow["CityID"].ToString();

string cityName = dRow["CityName"].ToString();

cityNames.Add(new CascadingDropDownNameValue

(cityName, cityID));

}

return cityNames.ToArray();

}

}

The complete html source is like this

<%@ Page Language="C#" EnableEventValidation="false"

AutoEventWireup="true" CodeFile="Default.aspx.cs"

Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"

"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head id="Head1" runat="server">

<title>Untitled Page</title>

</head>

<body>

<form id="form1" runat="server">

<asp:ScriptManager ID="ScriptManager1" runat="server">

<Services>

<asp:ServiceReference Path="AutoComplete.asmx" />

<asp:ServiceReference Path="CascadingDropDown.asmx" />

</Services>

</asp:ScriptManager>

<asp:UpdatePanel ID="UpdatePanel1" runat="server">

<ContentTemplate>

<asp:GridView ID="GridView1" runat="server"

AutoGenerateColumns="False"

DataSourceID="SqlDataSource1"

OnRowUpdating="GridView1_RowUpdating">

<Columns>

<asp:CommandField ShowEditButton="True" />

<asp:TemplateField HeaderText="ID" SortExpression="ID">

<ItemTemplate>

<asp:Label ID="lblID" runat="server"

Text='<%#Eval("ID") %>'>

</asp:Label>

</ItemTemplate>

<EditItemTemplate>

<asp:Label ID="lblID" runat="server"

Text='<%#Bind("ID") %>'>

</asp:Label>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Name"

SortExpression="Name">

<ItemTemplate>

<asp:Label ID = "lblName" runat="server"

Text='<%#Eval("Name") %>'>

</asp:Label>

</ItemTemplate>

<EditItemTemplate>

<asp:TextBox ID="txtName" runat="server"

Text='<%#Bind("Name") %>' >

</asp:TextBox>

<ajaxToolkit:AutoCompleteExtender

runat="server"

ID="autoComplete1"

TargetControlID="txtName"

ServicePath="AutoComplete.asmx"

ServiceMethod="GetCompletionList"

MinimumPrefixLength="1"

CompletionInterval="10"

EnableCaching="true"

CompletionSetCount="12" />

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Country"

SortExpression="Country">

<ItemTemplate>

<asp:Label ID="lblCountry" runat="server"

Text='<%#Eval("Country") %>'>

</asp:Label>

</ItemTemplate>

<EditItemTemplate>

<asp:DropDownList ID="ddlCountry" runat="server">

</asp:DropDownList>

<ajaxToolkit:CascadingDropDown

ID="CascadingDropDown1"

runat="server"

Category="Country"

TargetControlID="ddlCountry"

PromptText="-Select Country-"

LoadingText="Loading Countries.."

ServicePath="CascadingDropDown.asmx"

ServiceMethod="GetCountries">

</ajaxToolkit:CascadingDropDown>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="City"

SortExpression="City">

<ItemTemplate>

<asp:Label ID="lblCity" runat="server"

Text='<%#Eval("City") %>'>

</asp:Label>

</ItemTemplate>

<EditItemTemplate>

<asp:DropDownList ID="ddlCity" runat="server"

OnSelectedIndexChanged="ddlCity_SelectedIndexChanged">

</asp:DropDownList><br />

<ajaxToolkit:CascadingDropDown

ID="CascadingDropDown2"

runat="server"

Category="City"

TargetControlID="ddlCity"

ParentControlID="ddlCountry"

PromptText="-Select City-"

LoadingText="Loading Cities.."

ServicePath="CascadingDropDown.asmx"

ServiceMethod="GetCities">

</ajaxToolkit:CascadingDropDown>

</EditItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</ContentTemplate>

</asp:UpdatePanel>

<div>

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

ConnectionString="<%$ ConnectionStrings:dbConnectionString %>"

SelectCommand="SELECT [ID], [Name], [City],[Country]

FROM [Location]"

UpdateCommand="Update Location set [Name] = @Name,

[City] = @City,[Country] = @Country

where ID = @ID">

<UpdateParameters>

<asp:Parameter Name="Name" />

<asp:Parameter Name="ID" />

<asp:Parameter Name="Country"/>

<asp:Parameter Name="City"/>

</UpdateParameters>

</asp:SqlDataSource>

</div>

</form> 

</body>

</html>

Finally write this code in code behind of aspx page to update record

protected void GridView1_RowUpdating

(object sender, GridViewUpdateEventArgs e)

{

//Find dropdown to get selected Item text  

DropDownList ddlGridCountry = (DropDownList)

GridView1.Rows[e.RowIndex].FindControl("ddlCountry");

string strCountry =

ddlGridCountry.SelectedItem.Text.ToString();

DropDownList ddlGridCity = (DropDownList)

GridView1.Rows[e.RowIndex].FindControl("ddlCity");

string strCity =

ddlGridCity.SelectedItem.Text.ToString();

SqlDataSource1.UpdateParameters.Clear();

SqlDataSource1.UpdateParameters.Add

("Country", strCountry);

SqlDataSource1.UpdateParameters.Add("City", strCity);

}

Hope this will helps you.

Populating Casading Dropdownlists in asp.net

In This post we are going to  Populating cascading drop down list from database.


here is the sample code in our aspx page:

<table>

    <tr>

        <td>First</td>

        <td><asp:DropDownList ID="drpCountry" runat="server"  AutoPostBack="true"

                onselectedindexchanged=" drpCountry _SelectedIndexChanged"></asp:DropDownList></td>

    </tr>

    <tr>

        <td>Secord</td>

        <td><asp:DropDownList ID="drpState" runat="server" AutoPostBack="true"

                onselectedindexchanged=" drpState _SelectedIndexChanged"> 

            </asp:DropDownList></td>

    </tr>

    <tr>

        <td>Thrid</td>

        <td><asp:DropDownList ID="drpCity" runat="server"></asp:DropDownList></td>

    </tr>

</table>

Now in Code behind page bind first dropdownlist on Page_Load event.

protected void Page_Load(object sender, EventArgs e)

 {

   if (!Page.IsPostBack)

   {

     // bind the first dropdown here.

   }

 }

protected void  drpCountry _SelectedIndexChanged (object sender, EventArgs e)

{

            SqlCommand cmd = new SqlCommand(select * from state where       country_ID='"+drpCountry.SelectedValue.ToString()+"', con);

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            DataSet ds = new DataSet();

            da.Fill(ds);

            if (ds.Tables[0].Rows.Count > 0)

            {

              drpState .Items.Clear();

                ListItem li = new ListItem();

                li.Text = "---Select---";

                li.Value = "0";

                drpState .Items.Add(li);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)

                    {

                        ListItem li1 = new ListItem();

                        li1.Text = ds.Tables[0].Rows[i]["State_Name"].ToString();

                        li1.Value = ds.Tables[0].Rows[i][" State_ID "].ToString();

                        drpState.Items.Add(li1);

                    }

            }

}

protected void   drpState _SelectedIndexChanged  (object sender, EventArgs e)
{
            SqlCommand cmd = new SqlCommand(select * from City where       country_ID='"+ drpState .SelectedValue.ToString()+"', con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            if (ds.Tables[0].Rows.Count > 0)
            {
               drpCity  .Items.Clear();
                ListItem li = new ListItem();
                li.Text = "---Select---";
                li.Value = "0";
                drpCity  .Items.Add(li);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        ListItem li1 = new ListItem();
                        li1.Text = ds.Tables[0].Rows[i]["City_Name"].ToString();
                        li1.Value = ds.Tables[0].Rows[i][" City_ID "].ToString();
                        drpCity .Items.Add(li1);
                    }
            }
}

Binding Dropdownlist with dataset values in ASP.NET

    Suppose you have a dropdown list in you aspx page.
   
   <asp:DropDownList ID="drpbranch" runat="server" Width="350px"></asp:DropDownList>


   Now we bind this dropdownlist with dataset values.
   Suppose we have a table in our SQL database names Branch having fileds BR_CD and BR_NM.


   Now. in aspx.cs page.


   private void FillDropDownList()
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString);


        SqlCommand cmd = new SqlCommand("Select * from Branch", con);


        SqlDataAdapter da = new SqlDataAdapter(cmd);


        DataSet ds = new DataSet();
        da.Fill(ds);
       
         drpbranch .DataTextField = ds.Tables[0].Columns[" BR_NM "].ToString();
         drpbranch .DataValueField = ds.Tables[0].Columns[" BR_CD "].ToString();


         drpbranch .DataSource = ds.Tables[0];
         drpbranch .DataBind();
    }


   This post might help you.


  Thanks

Introduction of Abstract classes and Abstract methods of c#

Introduction of Abstract classes and Abstract methods of c#

              abstract  keyword is used to declare abstract classes and abstract methods in c#.

       Abstract class

Ø      The concept of Abstract Class and Abstract method is used under the concept of Inheritance. The abstract class can only be a base class of other classes.

Ø      Declaring class as abstract simply means this class cannot be instantiated , In simple words its object cannot be created.  As the instance cannot be created of this abstract class but can inherit another class and by creating the instance of this derived class you can access the method of the abstract class.

Ø        Abstract class plays a role when in lot of cases we don't want to create object of specific classes but still we want to take some base functionalities of that base class for its derived classes.

Ø       Abstract classes may or may not contain any concrete and even abstract methods.

Ø      Abstract classes are templates for future specific classes.

.

       Abstract method

Ø      Abstract methods are only declared in abstract classes.

Ø      Abstract method or abstract property just indicates that the method or property does not contain any implementation.

Ø      Because of no actual implementation in declaration of abstract method , it does not  contain any method body and simply ends with a semicolon without opening and closing curly braces.

Ø      Abstract methods and properties must be implemented in derived classes of abstract class.

Ø      Abstract method is implicitly a virtual method.

Ø       If you create a abstract method then you must override this method in the subclass otherwise it shows error in the program.

               Program to understand the concept of Abstract class.

public abstract class Organization

{

    //...Class implementation

    public abstract void Salary(int x, int y)

    {

        //this method mustn't be implemented here.

        //If we do implement it, the result is a Syntax Error.

    }

}

public abstract Department1 : Organization

{

    //...Class implementation

    //...you do not have to implement the the method Salary(int x, int y)

}

public class Designation : Department1

{

    //here we should provide an implemetation for Salary(int x, int y)

    public override void Salary(int x, int y)

    {

        //must do some Calculation here

    }

}