Sunday, 20 October 2013

What is Function in PL/SQL and How to use it

What is a Function in PL/SQL?
A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.
General Syntax to create a function is
CREATE [OR REPLACE] FUNCTION function_name [parameters]
RETURN return_datatype; 
IS 
Declaration_section 
BEGIN 
Execution_section
Return return_variable; 
EXCEPTION 
exception section 
Return return_variable; 
END;
1) Return Type: The header section defines the return type of the function. The return datatype can be any of the oracle datatype like varchar, number etc.
2) The execution and exception section both should return a value which is of the datatype defined in the header section.
For example, let’s create a frunction called ''employer_details_func' similar to the one created in stored proc

1> CREATE OR REPLACE FUNCTION employer_details_func
2>    RETURN VARCHAR(20);
3> IS
5>    emp_name VARCHAR(20);
6> BEGIN
7>       SELECT first_name INTO emp_name
8>       FROM emp_tbl WHERE empID = '100';
9>       RETURN emp_name;
10> END;

In the example we are retrieving the ‘first_name’ of employee with empID 100 to variable ‘emp_name’.
The return type of the function is VARCHAR which is declared in line no 2.
 
The function returns the 'emp_name' which is of type VARCHAR as the return value in line no 9.

How to execute a PL/SQL Function?
A function can be executed in the following ways.
1) Since a function returns a value we can assign it to a variable.
employee_name :=  employer_details_func;

If ‘employee_name’ is of datatype varchar we can store the name of the employee by assigning the return type of the function to it.
2) As a part of a SELECT statement
SELECT employer_details_func FROM dual;
3) In a PL/SQL Statements like,
dbms_output.put_line(employer_details_func);

This line displays the value returned by the function.


Posted by at No comments:
Labels:

Saturday, 19 October 2013

CHECK USERNAME AVAILABILITY IN ASP.NET USING AJAX PAGEMETHODS


Designer Source Code :

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="CS.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
<script type = "text/javascript">
function ShowAvailability() {
    PageMethods.CheckUserName(document.getElementById("<%=txtUserName.ClientID%>").value, OnSuccess);
}
function OnSuccess(response) {
    var mesg = document.getElementById("mesg");
    switch (response) {
        case "true":
            mesg.style.color = "green";
            mesg.innerHTML = "Available";
            break;
        case "false":
            mesg.style.color = "red";
            mesg.innerHTML = "Not Available";
            break;
        case "error":
            mesg.style.color = "red";
            mesg.innerHTML = "Error occured";
            break;                    
    }
}
function OnChange(txt) {
    document.getElementById("mesg").innerHTML = "";
}
</script>
</head>
<body style = "font-family:Arial; font-size:10pt">
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server" EnablePageMethods = "true">
</asp:ScriptManager>
<div>
    UserName :
    <asp:TextBox ID="txtUserName" runat="server"
        onkeyup = "OnChange(this)"></asp:TextBox>
    <input id="btnCheck" type="button" value="Show Availability"
        onclick = "ShowAvailability()" />
    <br />
    <span id = "mesg"></span>
</div>
</form>
</body>
</html>

 Code Behind :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

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

    }

    [System.Web.Services.WebMethod]
    public static string CheckUserName(string userName)
    {
        string returnValue = string.Empty;
        try
        {
            string consString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            SqlConnection conn = new SqlConnection(consString);
            SqlCommand cmd = new SqlCommand("spx_CheckUserAvailability", conn);           
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@UserName", userName.Trim());
            conn.Open();
            returnValue = cmd.ExecuteScalar().ToString();
            conn.Close(); 
        }
        catch
        {
            returnValue = "error";
        }
        return returnValue;
    }
}


Stored Procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE spx_CheckUserAvailability
      @UserName VARCHAR(50)
AS
BEGIN
      SET NOCOUNT ON;
      IF NOT EXISTS
            (SELECT * FROM Users
             WHERE UserName = @UserName
            )
            SELECT 'true'
      ELSE
            SELECT 'false'
END
GO


Download Source Code


Posted by at No comments:

Wednesday, 16 October 2013

Move Selected Gridview Rows to Another Gridview in Asp.net

Designer Source Code

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Transfer selected gridview rows to another gridview in Asp.net</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="gvDetails" AutoGenerateColumns="false" CellPadding="5" runat="server">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:CheckBox ID="chkSelect" runat="server" AutoPostBack="true"OnCheckedChanged="chkSelect_CheckChanged" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField HeaderText="UserId" DataField="UserId" />
<asp:BoundField HeaderText="UserName" DataField="UserName" />
<asp:BoundField HeaderText="Education" DataField="Education" />
<asp:BoundField HeaderText="Location" DataField="Location" />
</Columns>
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
</asp:GridView>
<br />
<b>Second Gridview Data</b>
<asp:GridView ID="gvTranferRows" AutoGenerateColumns="false" CellPadding="5" runat="server"EmptyDataText="No Records Found">
<Columns>
<asp:BoundField HeaderText="UserId" DataField="UserId" />
<asp:BoundField HeaderText="UserName" DataField="UserName" />
<asp:BoundField HeaderText="Education" DataField="Education" />
<asp:BoundField HeaderText="Location" DataField="Location" />
</Columns>
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
</asp:GridView>
</div>
</form>
</body>
</html>

 
Code Behind


using System;
using System.Data;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindGridview();
            BindSecondGrid();
        }
    }
    protected void BindGridview()
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("UserId", typeof(Int32));
        dt.Columns.Add("UserName", typeof(string));
        dt.Columns.Add("Education", typeof(string));
        dt.Columns.Add("Location", typeof(string));
        DataRow dtrow = dt.NewRow();    // Create New Row
        dtrow["UserId"] = 1;            //Bind Data to Columns
        dtrow["UserName"] = "Jitendra Gangwar";
        dtrow["Education"] = "MCA";
        dtrow["Location"] = "Ghaziabad";
        dt.Rows.Add(dtrow);
        dtrow = dt.NewRow();               // Create New Row
        dtrow["UserId"] = 2;               //Bind Data to Columns
        dtrow["UserName"] = "Dhirendra";
        dtrow["Education"] = "MBA";
        dtrow["Location"] = "Delhi";
        dt.Rows.Add(dtrow);
        dtrow = dt.NewRow();              // Create New Row
        dtrow["UserId"] = 3;              //Bind Data to Columns
        dtrow["UserName"] = "Pankaj";
        dtrow["Education"] = "B.Tech";
        dtrow["Location"] = "Delhi";
        dt.Rows.Add(dtrow);
        gvDetails.DataSource = dt;
        gvDetails.DataBind();

    }
   protected void chkSelect_CheckChanged(object sender, EventArgs e)
   {
       GetSelectedRows();
       BindSecondGrid();
   }
   protected void BindSecondGrid()
    {
        DataTable dt = (DataTable)ViewState["GetRecords"];
        gvTranferRows.DataSource = dt;
        gvTranferRows.DataBind();
    }
    private void GetSelectedRows()
    {
        DataTable dt;
        if (ViewState["GetRecords"] != null)
            dt = (DataTable)ViewState["GetRecords"];
        else
            dt = CreateTable();
        for (int i = 0; i < gvDetails.Rows.Count; i++)
        {
                CheckBox chk = (CheckBox)gvDetails.Rows[i].Cells[0].FindControl("chkSelect");
                if (chk.Checked)
                {
                    dt = AddGridRow(gvDetails.Rows[i], dt);
                }
                else
                {
                    dt = RemoveRow(gvDetails.Rows[i], dt);
                }
        }
        ViewState["GetRecords"] = dt;
    }
    private DataTable CreateTable()
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("UserId");
        dt.Columns.Add("UserName");
        dt.Columns.Add("Education");
        dt.Columns.Add("Location");
        dt.AcceptChanges();
        return dt;
    }
    private DataTable AddGridRow(GridViewRow gvRow, DataTable dt)
    {
        DataRow[] dr = dt.Select("UserId = '" + gvRow.Cells[1].Text + "'");
        if (dr.Length <= 0)
        {
            dt.Rows.Add();
            int rowscount = dt.Rows.Count - 1;
            dt.Rows[rowscount]["UserId"] = gvRow.Cells[1].Text;
            dt.Rows[rowscount]["UserName"] = gvRow.Cells[2].Text;
            dt.Rows[rowscount]["Education"] = gvRow.Cells[3].Text;
            dt.Rows[rowscount]["Location"] = gvRow.Cells[4].Text;
            dt.AcceptChanges();
       }
        return dt;
    }
    private DataTable RemoveRow(GridViewRow gvRow, DataTable dt)
    {
        DataRow[] dr = dt.Select("UserId = '" + gvRow.Cells[1].Text + "'");
        if (dr.Length > 0)
        {
            dt.Rows.Remove(dr[0]);
            dt.AcceptChanges();
        }
        return dt;
    }
}

Download Source Code


Posted by at 3 comments:
Subscribe to: Posts (Atom)