Thursday, April 21, 2011

Save Successfully confirm message using store procedure(sp) Input OutPut

Insert,Update,Delete Store Procedure(SP) execution Successfully done or any Error that Can using output variable in sp.
Here I have Given Example For Insert Sp in Three Tier Architecture.


Insert Store Procedure
ALTER PROCEDURE dbo.sp_InserttblEmp @Name varchar(50), @depid int, @Salary int, @status int output AS insert into tblemp VALUES(@Name,@depid,@Salary); select @status=@@ERROR RETURN @status
LogicLayer.cs
public Boolean insert_tblemp(LogicLayer objLogicLayer)
    {
        int status;
        string Sp = "sp_InserttblEmp";
        objCmd = new SqlCommand(Sp,objCn);
        objCmd.CommandType = CommandType.StoredProcedure;
        objCmd.Parameters.Add("@Name",objLogicLayer.Name);
        objCmd.Parameters.Add("@depid", objLogicLayer.Sid);
        objCmd.Parameters.Add("@Salary", objLogicLayer.Salary);
        objCmd.Parameters.Add("@status", SqlDbType.Int).Value=1;
        objCmd.Parameters["@status"].Direction = ParameterDirection.InputOutput;
        objCn.Open();
        objCmd.ExecuteNonQuery();
        status = (int)objCmd.Parameters["@status"].Value;
        objCn.Close();
        if (status == 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
DataLayer.cs
public class public class LogicLayer
{
{
DataLayer objLogicLayer=new LogicLayer ();
public int Sid { set; get; }
public string Name { set; get; }
public int Salary { set; get; }
public Boolean insert_tblemp(DataLayer objDataLayer)
{
return objLogicLayer.insert_tblemp(objDataLayer);
}
}
InsertForm.ASPX
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="insert.aspx.cs" Inherits="insert" %>

<!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">
    
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table>
    <tr>
    <td>
    Name
    </td>
    <td>
        <asp:TextBox ID="txtName" runat="server"></asp:TextBox>
    </td>
    </tr>
    <tr>
    <td>
    depid
    </td>
    <td>
        <asp:TextBox ID="txtdepid" runat="server"></asp:TextBox>
    </td>
    </tr>
    <tr>
    <td>
    Salary
    </td>
    <td>
        <asp:TextBox ID="txtSalary" runat="server"></asp:TextBox>
    </td>
    </tr>
    <tr>
    <td>
   &nbsp;
        <asp:Label ID="lblMsg" runat="server" ForeColor="Red" Text="Label"></asp:Label>
    </td>
    <td>
        <asp:Button ID="btnSave" runat="server" Text="Save" onclick="btnSave_Click" />
    </td>
    </tr>
    </table>
    </div>
    </form>
</body>
</html>
InsertForm.ASPX.cs
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class insert : System.Web.UI.Page
{
    LogicLayer objLogicLayer = new LogicLayer();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        { 
        
        }
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        bool status;
        objLogicLayer.Sid = Convert.ToInt32(txtdepid.Text);
        objLogicLayer.Name = txtName.Text;
        objLogicLayer.Salary = Convert.ToInt32(txtSalary.Text);
        status = objLogicLayer.insert_tblemp(objLogicLayer);
        if (status == true)
        {
            lblMsg.Text = "Save Successfully";
        }
        else
        {
            lblMsg.Text = "Error";
        }
    }
}
ASPdotNET-Example