Search using stored procedure

I want to search a DB table and display the records in a gridview below is my stored proc

create procedure search
        @Firstname varchar (50),
        @Lastname varchar (50),
        @Gender varchar (10),
        @Maritalstatus varchar (20),
        @Height varchar (30),
        @Complexion varchar (10),
        @Religion varchar (30),
        @State varchar (30),
        @Mothertongue varchar (30),
        @Education varchar (40),
        @Occupation varchar (40),
        @Annualincome varchar (30),
        @Starsign varchar (30),
        @Rassi varchar (30),
        @Gothram varchar (30),
        @DOB datetime, 
        @Timeofbirth nchar (10),
        @Emailid varchar (50)
as
begin
        select Firstname,Lastname,Gender,Maritalstatus,Height,Complexion,
               Religion,State,Mothertongue,Education,Occupation,AnnualIncome,
               Starsign,Rassi,Gothram,Dob,TimeOfBirth
        From Profile_Master 
        where (Firstname LIKE '%'+@Firstname+'%') and 
              (Emailid LIKE '%'+Emailid+'%')
end

Code in DAL

public void getdata(string fname,string lname,string gender,string maritalstatus,
                     string height,string complexion,string religion,string state,
                     string mothertongue,string education,string occupation,
                     string aincome,string starsign,string rasi,string gothram,
                     DateTime dob,DateTime tob,string  emailid)
    {
        SqlConnection conn = Generic.DBConnection.OpenConnection();
        try
        {
             SqlCommand cmdd = new SqlCommand("search", conn);
            cmdd.CommandType = CommandType.StoredProcedure;
               cmdd.Parameters.AddWithValue("@Firstname", fname);
            cmdd.Parameters.AddWithValue("@Emailid", emailid);
             SqlDataAdapter da = new SqlDataAdapter(cmdd);
            da.Fill(dt);         
            return dt;

        }
        catch (Exception)
        {

            throw;
        }

in B

Rather do this in your DAL:

public System.Data.DataSet spGetUser(string firstname)
    {
        using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionString))
        {
            using (System.Data.SqlClient.SqlCommand command = GetCommand("sp_GetUser", connection))
            {
                command.CommandType = System.Data.CommandType.StoredProcedure;
                System.Data.SqlClient.SqlParameter parameter;

                parameter = new System.Data.SqlClient.SqlParameter("@fname", System.Data.SqlDbType.VarChar);
                parameter.Direction = System.Data.ParameterDirection.Input;
                parameter.Value = firstname;
                command.Parameters.Add(parameter);

                System.Data.DataSet dataSet = new System.Data.DataSet();
                System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(command);
                adapter.Fill(dataSet);
                return dataSet;
            }

}

Using a USING handles the closing of connections implicitly, so you dont have to.

Edit the above block to add more params etc.

StackExchange Code Review Q#14896, answer score: 2