CODEWITHSUNDEEP
c#asp.netsql-server
user2924710
user2924710

Reputation: 11

Data Access Layer

I have created DataAccessLayer.cs file which helps me to make connection withe the database for insert records into the database and i have created store procedure.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

public class DataAccessLayer
{
    SqlConnection con = new SqlConnection("Connection String");
    SqlCommand cmd;
public DataAccessLayer()
{
    //
    // TODO: Add constructor logic here
    //
}

public int ExecuteProcedure(String procname, SqlParameter[]param)
{
    cmd = new SqlCommand();
    cmd.CommandText = procname;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Connection = con;

    foreach (SqlParameter obj in param)
    {
        cmd.Parameters.Add(obj);
    }
        con.Open();
        int i = cmd.ExecuteNonQuery();
        con.Close();
        return i;
}
}

Code for inserting record into data base using store procedure

SqlParameter[] param = new SqlParameter[]
    {
        new SqlParameter("@Name",txtFirstName.Text),
        new SqlParameter("@DOB",txtDOB.Text),
        new SqlParameter("@Address",txtAddress.Text),
        new SqlParameter("@CN",ddlCountry.SelectedValue),
        new SqlParameter("@SN",ddlState.SelectedValue),
        new SqlParameter("CNN",ddlCity.SelectedValue),
        new SqlParameter("@Mobile",txtMobile.Text),
        new SqlParameter("@Email",txtEmail.Text),
        new SqlParameter("@Pincode",txtPincode.Text),
        new SqlParameter("@returnval",SqlDbType.Int)
    };
    int i = DAL.ExecuteProcedure("AddContactInfo", param);
    if (i == 1)
    {
        lblError.Text = "Sucess";
    }
    else 
    {
        lblError.Text = "Not Sucess";
    }

Store Procedure for inserting records

ALTER proc [dbo].[AddContactInfo]
(
@Name varchar(50),
@DOB datetime,
@Address Varchar(max),
@Mobile varchar(15),
@Email varchar(50),
@CN int,
@SN int,
@CNN int,
@Pincode int,
@returnval int output
)
As
Begin
if exists(select Id from Contact_Info where Email=@Email)
begin
set @returnval=-2;
return @returnval;
end
else
begin
insert into          Contact_Info(Name,DOB,Address,CountryName,StateName,CityName,Pincode,Mobile,Email,CreatedOn    )
values(@Name,@DOB,@Address,@CN,@SN,@CNN,@Pincode,@Mobile,@Email,GETDATE())

if @@ERROR<>0
begin
    set @returnval=-3;
    return @returnval;
end
else
begin
    set @returnval=1;
    return @returnval;
end
end
End

Upvotes: 1

Views: 1270

Answers (1)

CodeCaster
CodeCaster

Reputation: 151594

But i am getting error which is "Procedure or function 'AddContactInfo' expects parameter '@returnval', which was not supplied."

You'll have to set your new SqlParameter("@returnval", SqlDbType.Int) to be an output parameter:

foreach (SqlParameter obj in param)
{
    if (cmd.ParameterName == "@returnval")
    {
        cmd.Direction = ParameterDirection.Output
    }

    cmd.Parameters.Add(obj);
}

Of course, this expects all SP's to have the same @returnval when appropriate. It's better to set it when creating the list of parameters.

Upvotes: 2

Related Questions