Click here to Skip to main content
15,913,669 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
i am create 3 tiar application in asp.net
my ValueObjectLayer is below
C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace ValueObjectLayer
{
   public class clsEmployeeVO
   {
        public int intID { get; set; }
        public string strName { get; set; }
        public string strMobile { get; set; }
        public string strEmail { get; set; }
        public DateTime dtBirthDate { get; set; }
        //public Boolean blStatus { get; set; }
        public string strCreatedBy { get; set; }
        public DateTime dtCreatedDate { get; set; }
        public string strUpdatedBy { get; set; }
        public DateTime dtUpdatedDate { get; set; }
    }
}

and my DataAccessLayer is
C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using ValueObjectLayer;
namespace DataAccessLayer
{
    public class clsEmployeeDAL
    {         
         SqlCommand objCmd;
         int intCount;
         SqlDataReader sqlrd;
         SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["strCon"].ConnectionString);
         public int RegisterEmployeeDAL(clsEmployeeVO objEmployee)
         {
             try
             {
                 if (con.State == ConnectionState.Closed)
                 {
                     con.Open();
                 }
                 objCmd = new SqlCommand();
                 objCmd.CommandText = "sp_insert_update_tblEmployee";
                 objCmd.Connection = con;
                 objCmd.Parameters.Clear();
                 objCmd.Parameters.AddWithValue("@intID", objEmployee.intID);
                 objCmd.Parameters.AddWithValue("@strName", objEmployee.strName);
                 objCmd.Parameters.AddWithValue("@strMobile", objEmployee.strMobile);
                 objCmd.Parameters.AddWithValue("@strEmail", objEmployee.strEmail);
                 objCmd.Parameters.AddWithValue("@dtBirthDate", objEmployee.dtBirthDate);
                 //objCmd.Parameters.AddWithValue("@blStatus", objEmployee.blStatus);
                 objCmd.Parameters.AddWithValue("@strCreatedBy", objEmployee.strCreatedBy);
                 objCmd.Parameters.AddWithValue("@strUpdatedBy", objEmployee.strUpdatedBy);
                 intCount = Convert.ToInt32(objCmd.ExecuteNonQuery());
                 if (con.State == ConnectionState.Open)
                 {
                     con.Close();
                 }
             }
             catch (Exception ex)
             {
                 return intCount = 0;
             }
             return intCount;
         }
    }
}

and my BussinessAccessLayer is
C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataAccessLayer;
using ValueObjectLayer;
namespace BussinessAccessLayer
{
    public class clsEmployeeBAL
    {       
        public int RegisterEmployeeDAL(clsEmployeeVO objEmployee)
        {
            clsEmployeeDAL objDAL = new clsEmployeeDAL();
            return objDAL.RegisterEmployeeDAL(objEmployee);
        }
    }
}

and my store procuder is
SQL
USE [dbDemo]
GO
/****** Object:  StoredProcedure [dbo].[sp_insert_update_tblEmployee]    Script Date: 09-01-2016 10:35:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_insert_update_tblEmployee]
	@intID	int,
	@strName	nvarchar(50),
	@strMobile	nvarchar(50),
	@strEmail	nvarchar(50),
	@dtBirthDate	nvarchar(50),
	--@dtCreatedDate datetime,
	--@dtUpdatedDate datetime,
	@strCreatedBy	nvarchar(50),
	@strUpdatedBy	nvarchar(50),
	@intCount int output
AS
BEGIN TRANSACTION COMPILE_ALL
BEGIN try
	if(not exists (select intID from tblEmployee where intID=@intID))
	begin
	insert into tblEmployee (strName,strMobile,strEmail,dtBirthDate,strCreatedBy,dtCreatedDate,strUpdatedBy,dtUpdatedDate)values
							(@strName,@strMobile,@strEmail,@dtBirthDate,@strCreatedBy,GETDATE(),@strUpdatedBy,GETDATE())
	end
	else
	begin
	update tblEmployee set strName=@strName,strMobile=@strMobile,strEmail=@strEmail,dtBirthDate=@dtBirthDate,
	strUpdatedBy=@strUpdatedBy,dtUpdatedDate=GETDATE() where intID=@intID
	end
	COMMIT TRANSACTION COMPILE_ALL
    set @intCount = 1
END try
BEGIN CATCH
	ROLLBACK TRANSACTION COMPILE_ALL
	set @intCount = 0
	END CATCH

and my save button code is
C#
protected void Button1_Click(object sender, EventArgs e)
   {
       clsEmployeeBAL objBAL = new clsEmployeeBAL();
       clsEmployeeVO objVO = new clsEmployeeVO();
       objVO.intID = 0;
       objVO.strName = txtName.Text;
       objVO.strMobile = txtMobile.Text;
       objVO.strEmail = txtEmail.Text;
       objVO.dtBirthDate = Convert.ToDateTime(txtBirthDate.Text);
       objVO.strCreatedBy = "Admin";
       objVO.strUpdatedBy = "";
       objBAL.RegisterEmployeeDAL(objVO);
       ClearAll();
   }

i got an error "Procedure or function 'sp_insert_update_tblEmployee' expects parameter '@intID', which was not supplied."
Posted

you haven't set command type as stored procedure, do as below
C#
objCmd = new SqlCommand();
objCmd.CommandText = "sp_insert_update_tblEmployee";
objCmd.CommandType = CommandType.StoredProcedure;
 
Share this answer
 
Comments
Amit Bhoi 9-Jan-16 3:48am    
it works but i got an another error "Procedure or function 'sp_insert_update_tblEmployee' expects parameter '@intCount', which was not supplied."
As DamithSL pointed out you should define the command type. Also you seem to be missing one parameter, @intCount is not defined so your code should include something like
C#
objCmd.Parameters.Add("@intCount", SqlDbType.Int).Direction = ParameterDirection.Output;
 
Share this answer
 
Comments
Amit Bhoi 9-Jan-16 3:51am    
Thanx DamithSL and Mika Wendelius

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900