BL

 public static int SaveDetails(SampleBO objSave)
        {
            SampleDA objDA = new SampleDA();
            return objDA.Save(objSave);
           // return SampleDA.Save(objSave);
        }

        public static DataSet GetDetails()
        {
            SampleDA objDA = new SampleDA();
            return objDA.GetDetails();
        }

        public static int Delete(int id)
        {
            SampleDA objDA = new SampleDA();
            return objDA.Delete(id);
        }

DA

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["AppDBCon"].ToString());
       
        public int Save(SampleBO objSave)
        {
            SqlCommand objCmd = new SqlCommand();
            objCmd.CommandType = CommandType.StoredProcedure;
            objCmd.CommandText = "sp_Save";
            try
            {
                objCmd.Parameters.AddWithValue("P_Id",objSave.ID == 0? (object)DBNull.Value:objSave.ID);
                objCmd.Parameters.AddWithValue("P_Name", objSave.Name);
                objCmd.Parameters.AddWithValue("P_Address", objSave.Address);
                objCmd.Parameters.AddWithValue("P_Phone", objSave.Phone);
                objCmd.Parameters.AddWithValue("P_Remarks", objSave.Remarks);
                SqlParameter outParms = new SqlParameter("@P_RetVal",SqlDbType.Int);
                outParms.Direction = ParameterDirection.Output;
                objCmd.Parameters.Add(outParms);
                objCmd.Connection = con;
                con.Open();
                objCmd.ExecuteNonQuery();
                con.Close();
                return Convert.ToInt32(outParms.Value);
            }
            catch(Exception ex)
            {
                return -1;
            }
            
        }

        public DataSet GetDetails()
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_GetDetails";
            try
            {
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
                adp.Fill(ds);
            }
            catch(Exception ex)
            {
                
            }
            return ds;
        }

        public int Delete(int id)
        {
            SqlCommand objCmd = new SqlCommand();
            objCmd.CommandType = CommandType.StoredProcedure;
            objCmd.CommandText = "spDelete";
            try
            {
                objCmd.Parameters.AddWithValue("P_Id", id == 0 ? (object)DBNull.Value : id);
                SqlParameter outParms = new SqlParameter("@P_RetVal", SqlDbType.Int);
                outParms.Direction = ParameterDirection.Output;
                objCmd.Parameters.Add(outParms);
                objCmd.Connection = con;
                con.Open();
                objCmd.ExecuteNonQuery();
                con.Close();
                return Convert.ToInt32(outParms.Value);
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

spDelete

USE [DineShore]
GO
/****** Object:  StoredProcedure [dbo].[spDesigantion_Delete]    Script Date: 04/19/2013 11:18:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* ==========================================
-- Purpose        : To Delete Designation Details
-- Module        : DineShore Admin
-- Created By    : Sumesh
-- Created On    : 10- Feb - 2012 02:21 PM
-- Execute        : DECLARE @P_Ret_Value INT
                  EXEC [spDepartment_Delete] 5, @P_Ret_Value OUTPUT
                  SELECT @P_Ret_Value
-- Return Values: 1 : Deleted Successfully
                  0 : Record Not found/ Already Deleted
                 -1 : If Any Sql error occur
  ==========================================*/

ALTER PROC [dbo].[spDesigantion_Delete]
             (@P_Id  INT
             ,@P_Ret_Value INT OUTPUT)
AS
SET NOCOUNT ON
BEGIN TRY
    BEGIN TRANSACTION
            -- Check id exists
            IF EXISTS(SELECT [ID] FROM [Designations_Mst]
                    WHERE [ID] =@P_Id )
                BEGIN
                -- ID Exists, then delete the details
                DELETE FROM [Designations_Mst]
                        WHERE [ID] =@P_Id
                -- Delete Success , Set Return value as 1
                SET @P_Ret_Value = 1
                END
            ELSE
                -- if id not exists, then return 0, details already deleted
                SET @P_Ret_Value = 0
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- if any excepetion occur set return value as -1
    SET @P_Ret_Value = -1
    ROLLBACK TRANSACTION
END CATCH

spGet

USE [DineShore]
GO
/****** Object:  StoredProcedure [dbo].[spDesignation_GetDetails]    Script Date: 04/19/2013 11:17:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* ==========================================
-- Purpose        : To Select Department Details
-- Module        : DineShore Admin
-- Created By    : Sumesh
-- Created On    : 10- Feb - 2012 09:00 AM
-- Execute        : [spDesignation_GetDetails] null,1,null,2, 2,2,'Code',NULL
-- Conditions   : IF pass  @P_OrganizationID then return all details that under passing OrgID
                   @P_User For Check user have Permission for that record, Check That user Entered or not
ID            Status            Remarks
NULL         NULL            Return All Designation Details
NULL         True            Return All Designation Details with Active Status
NULL         False            Return All Designation Details with InActive Status
ID             True            Return Designation Details with ID and Active Status
ID             False            Return Designation Details with ID and InActive Status
ID             NULL            Return Designation Detail by ID
-- Returns        : Designation Details with data table
  ==========================================*/
ALTER PROC [dbo].[spDesignation_GetDetails]
        (@P_ID                INT = NULL
        ,@P_User            INT
        ,@P_Status            BIT = NULL
        ,@P_OrganizationID    INT = NULL
        ,@P_PageSize        INT = NULL
        ,@P_Skip_Value        INT = NULL
        ,@P_SearchColumn    VARCHAR(100) = NULL
        ,@P_SearchValue        VARCHAR(250) = NULL)   
AS
    BEGIN
        IF(@P_PageSize IS NULL)
            BEGIN
                    SELECT D.[Id]
                        ,D.[Code]
                        ,D.[Name]
                        ,D.[Remarks]
                        ,D.[OrganizationID]
                        ,O.Name AS [OrganizationName]
                        ,D.[CreatedBy]
                        ,D.[CreatedDt]
                        ,D.[Status]
                        ,Case When D.[CreatedBy] in (@P_User) Then 1
                        Else 0 End AS [Mode]
                        FROM [Designations_Mst] AS D
                        INNER JOIN [Organization_Mst] AS O ON   D.[OrganizationID] = O.ID
                        WHERE D.ID =ISNULL(@P_ID,D.ID)  AND  D.[Status] =ISNULL(@P_Status,D.[Status])
                              AND D.[OrganizationID] = ISNULL (@P_OrganizationID,D.[OrganizationID]) ORDER BY D.[Name]
            END
        ELSE
            BEGIN
                    DECLARE @sql NVARCHAR(MAX);
                    DECLARE @Wheresql NVARCHAR(MAX);
                    SET @sql= 'SELECT TOP '+ CONVERT(VARCHAR(25),@P_PageSize) +  ' D.[Id]
                        ,D.[Code]
                        ,D.[Name]
                        ,D.[Remarks]
                        ,D.[OrganizationID]
                        ,O.Name AS [OrganizationName]
                        ,D.[CreatedBy]
                        ,D.[CreatedDt]
                        ,D.[Status]
                        ,CASE WHEN D.[CreatedBy] in ('+ CONVERT(VARCHAR(25),@P_User)+') THEN 1
                         ELSE 0 END AS [Mode]
                        FROM [Designations_Mst] AS D
                        INNER JOIN [Organization_Mst] AS O ON   D.[OrganizationID] = O.ID 
                        WHERE D.[ID] NOT IN (SELECT TOP '+ CONVERT(VARCHAR(25),@P_Skip_Value) +  ' DS.[Id] FROM [Designations_Mst] AS DS ORDER BY DS.[ID] DESC )'
                        IF (@P_SearchColumn = 'OrganizationID')
                        BEGIN
                         
                          SET @Wheresql =' AND D.[OrganizationID] IN (Select O.[ID] from  [Organization_Mst] AS O WHERE O.[Name]  LIKE '''+ '%' + ISNULL(@P_SearchValue, '%') + '%' + '''' +')'
                        END
                        ELSE
                        BEGIN
                            SET @Wheresql =' AND '+ ISNULL (' D.'+@P_SearchColumn, 'D.[Code]')+  ' LIKE '''+ '%' + ISNULL(@P_SearchValue, '%') + '%' + ''''
                        END
                SET @sql=@sql+@Wheresql+ ' ORDER BY D.[ID] DESC'
        EXEC sp_executesql @sql

            END
    END

App config

<?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <add name="AppDbCon" connectionString="Data Source=Prathap;Initial Catalog=zzzz;User ID=sa;Password=sql2005"/>
    <add name="AppDbConPwd" connectionString="Zd1gehJhs11kyDOFCqy/9w=="/>
    <!--SQL@2005-->
    <!--<add name="AppDbConPwd"
             connectionString="X6ZijF0oogg=" />-->
    <!--oylmbra-->
  </connectionStrings>
</configuration>

sp_Save

/* ==========================================
-- Purpose        : To Save Designation Details
-- Module        : POS Back end
-- Created By    : Sumesh
-- Created On    : 10- Feb - 2012 10:42 AM
-- Execute        : DECLARE @P_Ret_Value INT
                  EXEC [spDesignation_Save]   null, 'ad1111','b',1, 1,1,'12-12-2011',@P_Ret_Value OUTPUT
                  SELECT @P_Ret_Value
-- Return        : > 0: Save Success, -2 : Code Already Exists, -1 : Sql Error  0 - Record Not Found
  ==========================================*/

ALTER PROC [dbo].[spDesignation_Save]
                       
           ( @P_ID                        INT = NULL
            ,@P_Code                    VARCHAR(100)
            ,@P_Name                    VARCHAR(250)
            ,@P_OrganizationID            INT
            ,@P_Remarks                    VARCHAR(500)
            ,@P_Status                    BIT    = NULL
            ,@P_CreatedBy                VARCHAR(50)
            ,@P_CreatedDt                DATETIME = NULL
            ,@P_Ret_Value                INT OUTPUT
            )
AS
SET NOCOUNT ON
-- Create temporary table
DECLARE @Temp_Table TABLE
(
    [Id]    INT
)
BEGIN TRY
    -- if @P_Id = null, then insert / new entry mode
    IF(@P_ID IS NULL)
    BEGIN
        -- New Code Already Exists , if exists return -2
        IF EXISTS(SELECT  Code FROM [Designations_Mst] WHERE  Code=@P_Code AND [OrganizationID]=@P_OrganizationID)
        BEGIN
        SET @P_Ret_Value=-2;
        RETURN
        END
        ---
            BEGIN TRANSACTION
                INSERT INTO [Designations_Mst]
                        ([Code]
                        ,[Name]
                        ,[OrganizationID]
                        ,[Remarks]
                        ,[CreatedBy]
                        ,[CreatedDt]
                        ,[Status])
                -- Add Id to temporary table
                OUTPUT INSERTED.ID INTO @Temp_Table
                VALUES
                        (@P_Code       
                        ,@P_Name   
                        ,@P_OrganizationID
                        ,@P_Remarks  
                        ,@P_CreatedBy       
                        ,ISNULL(@P_CreatedDt, GETDATE())
                        ,ISNULL(@P_Status, 1))   
            -- Assign ID as return value , if save success
            SELECT @P_Ret_Value = ID FROM @Temp_Table
        COMMIT TRANSACTION
        END
    ELSE
        -- if id != null, then the action is update
        BEGIN
            -- New Code Already Exists For Update, if exists , set return value=-2
            IF EXISTS(SELECT Code FROM [Designations_Mst] WHERE Code=@P_Code AND [OrganizationID]=@P_OrganizationID AND ID!=@P_Id )
            BEGIN
            SET @P_Ret_Value=-2;
            return
            END
            BEGIN TRANSACTION
                -- Update [Counter_Mst] details by id
                UPDATE  [Designations_Mst]
                         SET
                         [Code]                    =@P_Code
                        ,[Name]                    =@P_Name
                        ,[OrganizationID]        =@P_OrganizationID
                        ,[Remarks]                =@P_Remarks
                        ,[CreatedBy]            =@P_CreatedBy
                        ,[CreatedDt]            =ISNULL(@P_CreatedDt, GETDATE())   
                        WHERE ID=@P_Id
                -- @@ROWCOUNT Returns the number of rows affected by the last statement. 
                -- If the number of rows > 0, then set return value as 0 - No updation occur
                IF @@ROWCOUNT > 0
                    SET @P_Ret_Value=@P_Id
                ELSE
                    SET @P_Ret_Value = 0
             COMMIT TRANSACTION
           END
END TRY
BEGIN CATCH
        -- If Any exception occur - set return values as -1
        SET @P_Ret_Value = -1
        ROLLBACK TRANSACTION
END CATCH

DBServices

 public class DbServices
    {
        #region Methods
        /// <summary>
        /// Fo Handle Db Services
        /// </summary>
        /// <CreatedBy>Sumesh.N</CreatedBy>
        /// <CreatedOn>19-Jan-2012 03:00 PM</CreatedOn>
        SqlConnection objConn = new SqlConnection(ConfigurationManager.ConnectionStrings[DbServiceConst.DB_Connection].ConnectionString);//+ EncryptionDecryption.DecryptString( ConfigurationManager.ConnectionStrings[DbServiceConst.DB_ConnectionPwd].ConnectionString,true));
        /// <summary>
        /// Method to create Connection
        /// </summary>
        /// <returns></returns>
        /// <remarks></remarks>
        private SqlConnection GetConnection()
        {
            if (objConn.State == ConnectionState.Closed)
            {
                objConn.Open();
            }
            return objConn;
        }
        /// <summary>
        /// Mehtod to Close sql connection
        /// </summary>
        /// <returns></returns>
        /// <remarks></remarks>
        public bool CloseConnection()
        {
            if (objConn.State != ConnectionState.Closed)
            {
                objConn.Close();
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        ///  Method to Execute procedure
        /// </summary>
        /// <param name="objCmd"></param>
        /// <returns></returns>
        /// <remarks></remarks>
        public int ExecuteProcedure(SqlCommand objCmd)
        {
            objCmd.Connection = GetConnection();
            int i = objCmd.ExecuteNonQuery();
            CloseConnection();
            return i;
        }
        /// <summary>
        /// Method to Execute Scalar
        /// </summary>
        /// <param name="objCmd"></param>
        /// <returns></returns>
        public object ExecuteScalar(SqlCommand objCmd)
        {
            objCmd.Connection = GetConnection();
            object value = objCmd.ExecuteScalar();
            CloseConnection();
            return value;
        }
        /// <summary>
        /// Method to Execute Reader
        /// </summary>
        /// <param name="objCmd"></param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(SqlCommand objCmd)
        {
            objCmd.Connection = GetConnection();
            SqlDataReader rdr = null;
            rdr = objCmd.ExecuteReader();
            return rdr;
        }
        #endregion
    }