S@thish Web World

3-tier Architecture Sample Code:

DataAccess Layer :

DAL/Datalayer.cs

using System;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

///

/// Summary description for dataaccesslayer
///

namespace datalayer.DAL

{

///

/// Summary description for parameter
///

public class DBConnection

{

private IDbCommand cmd = new SqlCommand();

private string strConnectionString = "";

private bool handleErrors = false;

private string strLastError = "";

public DBConnection()

{

strConnectionString = System.Configuration.ConfigurationManager.AppSettings["dbstring"].ToString();

SqlConnection cnn = new SqlConnection();

cnn.ConnectionString = strConnectionString;

cmd.Connection = cnn;

cmd.CommandType = CommandType.StoredProcedure;

}

public int ExecuteNonQuery()

{

int i = -1;

try

{

this.Open();

i = cmd.ExecuteNonQuery();

this.Close();

}

catch (Exception ex)

{

if (handleErrors)

strLastError = ex.Message;

else

throw;

}

catch

{

throw;

}

return i;

}

public int ExecuteNonQuery(string commandtext)

{

int i = -1;

try

{

cmd.CommandText = commandtext;

i = this.ExecuteNonQuery();

}

catch (Exception ex)

{

if (handleErrors)

strLastError = ex.Message;

else

throw;

}

catch

{

throw;

}

return i;

}

public DataSet ExecuteDataSet()

{

SqlDataAdapter da = null;

DataSet ds = null;

try

{

da = new SqlDataAdapter();

da.SelectCommand = (SqlCommand)cmd;

ds = new DataSet();

da.Fill(ds);

}

catch (Exception ex)

{

if (handleErrors)

strLastError = ex.Message;

else

throw;

}

catch

{

throw;

}

return ds;

}

public DataSet ExecuteDataSet(string commandtext)

{

DataSet ds = null;

try

{

cmd.CommandText = commandtext;

ds = this.ExecuteDataSet();

}

catch (Exception ex)

{

if (handleErrors)

strLastError = ex.Message;

else

throw;

}

catch

{

throw;

}

return ds;

}

public string CommandText

{

get

{

return cmd.CommandText;

}

set

{

cmd.CommandText = value;

cmd.Parameters.Clear();

}

public IDataParameterCollection Parameters

{

get

{

return cmd.Parameters;

}

public void AddParameter(string paramname, object paramvalue)

{

SqlParameter param = new SqlParameter(paramname, paramvalue);

cmd.Parameters.Add(param);

}

public void AddParameter(IDataParameter param)

{

cmd.Parameters.Add(param);

}

public string ConnectionString

{

get

{

return strConnectionString;

}

set

{

strConnectionString = value;

}

private void Open()

{

cmd.Connection.Open();

}

private void Close()

{

cmd.Connection.Close();

}

public bool HandleExceptions

{

get

{

return handleErrors;

}

set

{

handleErrors = value;

}

public string LastError

{

get

{

return strLastError;

}

public void Dispose()

{

cmd.Dispose();

}

Businees Layer :

BOL/WC_Agency.cs

using System;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using datalayer;

using datalayer.DAL;

///

/// Summary AFName for Company.cs
/// ************************************************************
/// *    Project Name       :       *
/// *    Created on         :   Monday, Jan 30,2012         *
/// *    Page Name          :   WC_Agency.cs                    *
/// *    Parent Page        :   Status Coverded pages          *
/// *    Procedure name     :   sp_TBL_WC_Agency_MASTER               *
/// *    Front end          :   Visual Studio 2010             *
/// *    Back end           :   Sql server 2008                *
/// ************************************************************
///

namespace WC_Agency.BOL

{

public class WC_Agency_frm

{

private int _Action;

public int Action

{

get { return _Action; }

set { _Action = value; }

}

private int _Active;

public int Active

{

get { return _Active; }

set { _Active = value; }

}

private int _PID;

public int PID

{

get { return _PID; }

set { _PID = value; }

}

private int _Wid;

public int Wid

{

get { return _Wid; }

set { _Wid = value; }

}

private string _AgencyName;

public string AgencyName

{

get { return _AgencyName; }

set { _AgencyName = value; }

}

private string _Address;

public string Address

{

get { return _Address; }

set { _Address = value; }

}

private string _City;

public string City

{

get { return _City; }

set { _City = value; }

}

private string _State;

public string State

{

get { return _State; }

set { _State = value; }

}

private string _Zip;

public string Zip

{

get { return _Zip; }

set { _Zip = value; }

}

private string _ProducerName;

public string ProducerName

{

get { return _ProducerName; }

set { _ProducerName = value; }

}

private string _CSPName;

public string CSPName

{

get { return _CSPName; }

set { _CSPName = value; }

}

private string _OfficePhone;

public string OfficePhone

{

get { return _OfficePhone; }

set { _OfficePhone = value; }

}

private string _MobilePhone;

public string MobilePhone

{

get { return _MobilePhone; }

set { _MobilePhone = value; }

}

private string _Fax;

public string Fax

{

get { return _Fax; }

set { _Fax = value; }

}

private string _Email;

public string Email

{

get { return _Email; }

set { _Email = value; }

}

private string _Code;

public string Code

{

get { return _Code; }

set { _Code = value; }

}

private string _SubCode;

public string SubCode

{

get { return _SubCode; }

set { _SubCode = value; }

}

private string _AgencyCustomerID;

public string AgencyCustomerID

{

get { return _AgencyCustomerID; }

set { _AgencyCustomerID = value; }

}

public static int Add_Update_delete_WC_Agency(WC_Agency_frm af)

{

DBConnection dc = new DBConnection();

dc.Parameters.Add(new SqlParameter("@Action", af.Action));

dc.Parameters.Add(new SqlParameter("@Wid", af.Wid));

dc.Parameters.Add(new SqlParameter("@pid", af.PID));

dc.Parameters.Add(new SqlParameter("@AgencyName", af.AgencyName));

dc.Parameters.Add(new SqlParameter("@Address", af.Address));

dc.Parameters.Add(new SqlParameter("@City", af.City));

dc.Parameters.Add(new SqlParameter("@Zip", af.Zip));

dc.Parameters.Add(new SqlParameter("@State", af.State));

dc.Parameters.Add(new SqlParameter("@ProducerName", af.ProducerName));

dc.Parameters.Add(new SqlParameter("@CSPName", af.CSPName));

dc.Parameters.Add(new SqlParameter("@OfficePhone", af.OfficePhone));

dc.Parameters.Add(new SqlParameter("@MobilePhone", af.MobilePhone));

dc.Parameters.Add(new SqlParameter("@Fax", af.Fax));

dc.Parameters.Add(new SqlParameter("@Email", af.Email));

dc.Parameters.Add(new SqlParameter("@Code", af.Code));

dc.Parameters.Add(new SqlParameter("@SubCode", af.SubCode));

dc.Parameters.Add(new SqlParameter("@AgencyCustomerID", af.AgencyCustomerID));

dc.Parameters.Add(new SqlParameter("@Active", af.Active));

return dc.ExecuteNonQuery("SP_TBL_WC_AGENCY");

}

public DataSet Select_WC_Agency(WC_Agency_frm af)

{

DBConnection dc = new DBConnection();

DataSet ds = new DataSet();

dc.Parameters.Add(new SqlParameter("@Action", af.Action));

dc.Parameters.Add(new SqlParameter("@Wid", af.Wid));

dc.Parameters.Add(new SqlParameter("@pid", af.PID));

dc.Parameters.Add(new SqlParameter("@AgencyName", af.AgencyName));

dc.Parameters.Add(new SqlParameter("@Address", af.Address));

dc.Parameters.Add(new SqlParameter("@City", af.City));

dc.Parameters.Add(new SqlParameter("@Zip", af.Zip));

dc.Parameters.Add(new SqlParameter("@State", af.State));

dc.Parameters.Add(new SqlParameter("@ProducerName", af.ProducerName));

dc.Parameters.Add(new SqlParameter("@CSPName", af.CSPName));

dc.Parameters.Add(new SqlParameter("@OfficePhone", af.OfficePhone));

dc.Parameters.Add(new SqlParameter("@MobilePhone", af.MobilePhone));

dc.Parameters.Add(new SqlParameter("@Fax", af.Fax));

dc.Parameters.Add(new SqlParameter("@Email", af.Email));

dc.Parameters.Add(new SqlParameter("@Code", af.Code));

dc.Parameters.Add(new SqlParameter("@SubCode", af.SubCode));

dc.Parameters.Add(new SqlParameter("@AgencyCustomerID", af.AgencyCustomerID));

dc.Parameters.Add(new SqlParameter("@Active", af.Active));

return ds = dc.ExecuteDataSet("SP_TBL_WC_AGENCY");

}

public static int Add_Update_delete_WC_Agency_MASTER(WC_Agency_frm af)

{

DBConnection dc = new DBConnection();

dc.Parameters.Add(new SqlParameter("@Action", af.Action));

dc.Parameters.Add(new SqlParameter("@Wid", af.Wid));

dc.Parameters.Add(new SqlParameter("@AgencyName", af.AgencyName));

dc.Parameters.Add(new SqlParameter("@Address", af.Address));

dc.Parameters.Add(new SqlParameter("@City", af.City));

dc.Parameters.Add(new SqlParameter("@Zip", af.Zip));

dc.Parameters.Add(new SqlParameter("@State", af.State));

dc.Parameters.Add(new SqlParameter("@ProducerName", af.ProducerName));

dc.Parameters.Add(new SqlParameter("@CSPName", af.CSPName));

dc.Parameters.Add(new SqlParameter("@OfficePhone", af.OfficePhone));

dc.Parameters.Add(new SqlParameter("@MobilePhone", af.MobilePhone));

dc.Parameters.Add(new SqlParameter("@Fax", af.Fax));

dc.Parameters.Add(new SqlParameter("@Email", af.Email));

dc.Parameters.Add(new SqlParameter("@Code", af.Code));

dc.Parameters.Add(new SqlParameter("@SubCode", af.SubCode));

dc.Parameters.Add(new SqlParameter("@AgencyCustomerID", af.AgencyCustomerID));

dc.Parameters.Add(new SqlParameter("@Active", af.Active));

return dc.ExecuteNonQuery("SP_TBL_WC_AGENCY_MASTER");

}

public DataSet Select_WC_Agency_MASTER(WC_Agency_frm af)

{

DBConnection dc = new DBConnection();

DataSet ds = new DataSet();

dc.Parameters.Add(new SqlParameter("@Action", af.Action));

dc.Parameters.Add(new SqlParameter("@Wid", af.Wid));

dc.Parameters.Add(new SqlParameter("@AgencyName", af.AgencyName));

dc.Parameters.Add(new SqlParameter("@Address", af.Address));

dc.Parameters.Add(new SqlParameter("@City", af.City));

dc.Parameters.Add(new SqlParameter("@Zip", af.Zip));

dc.Parameters.Add(new SqlParameter("@State", af.State));

dc.Parameters.Add(new SqlParameter("@ProducerName", af.ProducerName));

dc.Parameters.Add(new SqlParameter("@CSPName", af.CSPName));

dc.Parameters.Add(new SqlParameter("@OfficePhone", af.OfficePhone));

dc.Parameters.Add(new SqlParameter("@MobilePhone", af.MobilePhone));

dc.Parameters.Add(new SqlParameter("@Fax", af.Fax));

dc.Parameters.Add(new SqlParameter("@Email", af.Email));

dc.Parameters.Add(new SqlParameter("@Code", af.Code));

dc.Parameters.Add(new SqlParameter("@SubCode", af.SubCode));

dc.Parameters.Add(new SqlParameter("@AgencyCustomerID", af.AgencyCustomerID));

dc.Parameters.Add(new SqlParameter("@Active", af.Active));

return ds = dc.ExecuteDataSet("SP_TBL_WC_AGENCY_MASTER");

}

Presentation Layer :

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 Common.BOL;
using System.Data;
using User_Details.BOL;
using datalayer.DAL;
using User_Roles.BOL;
using Authentication.BOL;
using System.Configuration;
using WCRates.BOL;
using States.BOL;
using Carrier.BOL;
using WC_Agency.BOL;

public partial class Controls_AddWCAgency : System.Web.UI.UserControl
{
    WC_Agency_frm wcf = new WC_Agency_frm();

    protected void Page_Load(object sender, EventArgs e)
    {
        if (Request.QueryString["Rateid"] != null)
        {
            int Rateid = int.Parse(Request.QueryString["Rateid"].ToString());
            if (!IsPostBack)
            {
                Get_WCAgencyDetails(Rateid);
            }
        }
    }
    protected void btnWCRatesAdd_Click(object sender, EventArgs e)
    {
        AddWCAgency();
    }
    protected void btnWCRatesUpdate_Click(object sender, EventArgs e)
    {
        UpdateWCAgency();
    }

    public void Get_WCAgencyDetails(int Rateid)
    {
        btnWCRatesAdd.Visible = false;
        btnWCRatesUpdate.Visible = true;
        int Active = 1;

        DataSet ds_typrCorp = new DataSet();

        wcf.Wid = Rateid;
        wcf.Action = 5;
        wcf.Active = Convert.ToInt32(Active);
        ds_typrCorp = wcf.Select_WC_Agency_MASTER(wcf);

        if (ds_typrCorp.Tables[0].Rows.Count > 0)
        {
            txtAgencyName.Text = ds_typrCorp.Tables[0].Rows[0]["AgencyName"].ToString();
            txtAddress.Text = ds_typrCorp.Tables[0].Rows[0]["Address"].ToString();
            txtCity.Text = ds_typrCorp.Tables[0].Rows[0]["City"].ToString();
            txtState.Text = ds_typrCorp.Tables[0].Rows[0]["State"].ToString();
            txtZip.Text = ds_typrCorp.Tables[0].Rows[0]["Zip"].ToString();
            txtProducerName.Text = ds_typrCorp.Tables[0].Rows[0]["ProducerName"].ToString();
            txtCSPName.Text = ds_typrCorp.Tables[0].Rows[0]["CSPName"].ToString();
            txtOffPhone.Text = ds_typrCorp.Tables[0].Rows[0]["OfficePhone"].ToString();
            txtMobPhone.Text = ds_typrCorp.Tables[0].Rows[0]["MobilePhone"].ToString();
            txtFax.Text = ds_typrCorp.Tables[0].Rows[0]["Fax"].ToString();
            txtEmail.Text = ds_typrCorp.Tables[0].Rows[0]["Email"].ToString();
            txtCode.Text = ds_typrCorp.Tables[0].Rows[0]["Code"].ToString();
            txtSubCode.Text = ds_typrCorp.Tables[0].Rows[0]["SubCode"].ToString();
            txtAgeCustID.Text = ds_typrCorp.Tables[0].Rows[0]["AgencyCustomerID"].ToString();

            txtAgencyName.Focus();
        }

    }

    public void AddWCAgency()
    {
        string AgencyName = txtAgencyName.Text.Trim().ToString();
        string Address = txtAddress.Text.Trim().ToString();
        string City = txtCity.Text.Trim().ToString();
        string State = txtState.Text.Trim().ToString();
        string Zip = txtZip.Text.Trim().ToString();
        string ProducerName = txtProducerName.Text.Trim().ToString();
        string CSPName = txtCSPName.Text.Trim().ToString();
        string OffPhone = txtOffPhone.Text.Trim().ToString();
        string MobPhone = txtMobPhone.Text.Trim().ToString();
        string Fax = txtFax.Text.Trim().ToString();
        string Email = txtEmail.Text.Trim().ToString();
        string Code = txtCode.Text.Trim().ToString();
        string SubCode = txtSubCode.Text.Trim().ToString();
        string AgeCustID = txtAgeCustID.Text.Trim().ToString();

        wcf.Action = 1;
        wcf.Active = 1;
        wcf.AgencyName = AgencyName;
        wcf.Address = Address;
        wcf.City = City;
        wcf.State = State;
        wcf.Zip = Zip;
        wcf.ProducerName = ProducerName;
        wcf.CSPName = CSPName;
        wcf.OfficePhone = OffPhone;
        wcf.MobilePhone = MobPhone;
        wcf.Fax = Fax;
        wcf.Email = Email;
        wcf.Code = Code;
        wcf.SubCode = SubCode;
        wcf.AgencyCustomerID = AgeCustID;

        WC_Agency_frm.Add_Update_delete_WC_Agency_MASTER(wcf);

        lblStatus.Visible = true;
        lblStatus.Text = "Added Successfully";

        ClearControls();

    }

    public void UpdateWCAgency()
    {

        string AgencyName = txtAgencyName.Text.Trim().ToString();
        string Address = txtAddress.Text.Trim().ToString();
        string City = txtCity.Text.Trim().ToString();
        string State = txtState.Text.Trim().ToString();
        string Zip = txtZip.Text.Trim().ToString();
        string ProducerName = txtProducerName.Text.Trim().ToString();
        string CSPName = txtCSPName.Text.Trim().ToString();
        string OffPhone = txtOffPhone.Text.Trim().ToString();
        string MobPhone = txtMobPhone.Text.Trim().ToString();
        string Fax = txtFax.Text.Trim().ToString();
        string Email = txtEmail.Text.Trim().ToString();
        string Code = txtCode.Text.Trim().ToString();
        string SubCode = txtSubCode.Text.Trim().ToString();
        string AgeCustID = txtAgeCustID.Text.Trim().ToString();

        wcf.Action = 2;
        wcf.Active = 1;
        wcf.Wid = int.Parse(Request.QueryString["Rateid"].ToString());
        wcf.AgencyName = AgencyName;
        wcf.Address = Address;
        wcf.City = City;
        wcf.State = State;
        wcf.Zip = Zip;
        wcf.ProducerName = ProducerName;
        wcf.CSPName = CSPName;
        wcf.OfficePhone = OffPhone;
        wcf.MobilePhone = MobPhone;
        wcf.Fax = Fax;
        wcf.Email = Email;
        wcf.Code = Code;
        wcf.SubCode = SubCode;
        wcf.AgencyCustomerID = AgeCustID;

        WC_Agency_frm.Add_Update_delete_WC_Agency_MASTER(wcf);

        lblStatus.Visible = true;
        lblStatus.Text = "Updated Successfully";

        ClearControls();

    }

    public void ClearControls()
    {
        txtAgencyName.Text = "";
        txtAddress.Text = "";
        txtCity.Text = "";
        txtState.Text = "";
        txtZip.Text = "";
        txtProducerName.Text = "";
        txtCSPName.Text = "";
        txtOffPhone.Text = "";
        txtMobPhone.Text = "";
        txtFax.Text = "";
        txtEmail.Text = "";
        txtCode.Text = "";
        txtSubCode.Text = "";
        txtAgeCustID.Text = "";

    }



}

Database Table and Stored Procedure

CREATE TABLE [dbo].[TBL_WC_AGENCY_MASTER](
    [Wid] [int] IDENTITY(1,1) NOT NULL,
    [AgencyName] [nvarchar](200) NULL,
    [Address] [nvarchar](200) NULL,
    [City] [nvarchar](200) NULL,
    [State] [nvarchar](200) NULL,
    [Zip] [nvarchar](200) NULL,
    [ProducerName] [nvarchar](200) NULL,
    [CSPName] [nvarchar](200) NULL,
    [OfficePhone] [nvarchar](200) NULL,
    [MobilePhone] [nvarchar](200) NULL,
    [Fax] [nvarchar](200) NULL,
    [Email] [nvarchar](200) NULL,
    [Code] [nvarchar](200) NULL,
    [SubCode] [nvarchar](200) NULL,
    [AgencyCustomerID] [nvarchar](200) NULL,
    [Active] [int] NULL,
CONSTRAINT [PK_TBL_WC_AGENCY_MASTER] PRIMARY KEY CLUSTERED
(
    [Wid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

-----------------------------------------------------------------------------

Create procedure [dbo].[SP_TBL_WC_AGENCY_MASTER](
@Action int,
@Wid int,
@AgencyName nvarchar(200)=NULL,
@Address nvarchar(200)=NULL,
@City nvarchar(200)=NULL,
@State nvarchar(200)= NULL,
@Zip nvarchar(200)= NULL,
@ProducerName nvarchar(200)=null,
@CSPName nvarchar(200)=null,
@OfficePhone nvarchar(200)=null,
@MobilePhone nvarchar(200)=null,
@Fax nvarchar(200)=null,
@Email nvarchar(200)=null,
@Code nvarchar(200)=null,
@SubCode nvarchar(200)=null,
@AgencyCustomerID nvarchar(200)=null,
@Active int
)
as
begin

IF (@ACTION = 1)
   BEGIN
   INSERT INTO TBL_WC_AGENCY_MASTER VALUES(@AgencyName,@Address,@City,@State,@Zip,@ProducerName,@CSPName,@OfficePhone,@MobilePhone,@Fax,@Email,@Code,@SubCode,@AgencyCustomerID,@Active)
   END
IF(@ACTION = 2)
   BEGIN
   UPDATE TBL_WC_AGENCY_MASTER set AgencyName=@AgencyName,Address=@Address,City=@City,State=@State,Zip=@Zip,ProducerName=@ProducerName,CSPName=@CSPName,OfficePhone=@OfficePhone,MobilePhone=@MobilePhone,Fax=@Fax,Email=@Email,Code=@Code,SubCode=@SubCode,AgencyCustomerID=@AgencyCustomerID where Wid=@Wid
   END
IF(@ACTION = 3)
   BEGIN
   UPDATE TBL_WC_AGENCY_MASTER set Active=0 where Wid=@Wid
   END
if(@ACTION = 4)
   BEGIN
   SELECT * FROM TBL_WC_AGENCY_MASTER WHERE ACTIVE=1
   END

   if(@ACTION = 5)
   BEGIN
   SELECT * FROM TBL_WC_AGENCY_MASTER WHERE ACTIVE=1 and Wid=@Wid
   END

    if(@ACTION = 6)
   BEGIN
   SELECT * FROM TBL_WC_AGENCY_MASTER WHERE ACTIVE=1 and AgencyName=@AgencyName
   END



END

S@thish Web World

Pages

Monday, May 14, 2012

No comments:

Post a Comment

INDIAN SPOT BULLION RATE

Live International Market Rates

MCX Live Rates

NCDEX Levels