Search using stored procedure

Question

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 BAL

public DataTable search(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)
 {
 ProfileMasterDAL dal=new ProfileMasterDAL();
 try
 {
 returndal.getdata(fname,lname,gender,maritalstatus,
 height,complexion,religion, state,
 mothertongue, education, occupation, aincome,
 starsign, rasi,gothram, dob, tob, emailid);
 }
 catch (Exception ex)
 {
 throw ex;
 }
 }

in my UI

 public DataTable bind(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)
 {
 ProfileMasterBLL bll=new ProfileMasterBLL();
 try
 {
 DataTable dt = bll.search(fname, lname, gender, maritalstatus, 
 height, complexion, religion, state,
 mothertongue, education, occupation, aincome, 
 starsign, rasi, gothram, dob,
 tob, emailid);
 GridView1.DataSource = dt;
 GridView1.DataBind();
 return dt;
 }
 catch (Exception ex)
 {
 throw ex;
 }
 }

i want to search with two textboxes how to assign the textbox values??Is there any better way to code than this?Thanks.

EDIT

protected void Button1_Click(object sender, EventArgs e)
 {
 ProfileMasterBLL bll=new ProfileMasterBLL();
 string fname = TextBox1.Text;
 string email = TextBox2.Text;
 bll.FirstName = fname;
 bll.EmailID = email;
 try
 {
 DataTable dt=new DataTable();
 //what to code here?

• StuartLC: Well, one thing is that Connection, Command and DataTable aren't being disposed. You might find that codereview.stackexchange is better suited toward style and best practice type questions.
• Ben Robinson: I would for a start make your BL class inherit from you DAL class and make your methods virtual, that way at least if you are not doing anything useful in your BL class you don't have to bother instantiating you DAL class then calling the search method. IT is a good general pattern for n-tier and if you want to do BL in you BL layer you just overide the method and add your logic on top of what the DAL layer is doing.
• Filip De Vos: Using the construction LIKE '%'+@Firstname+'%' is a guaranteed table scan, hence super slow. And it opens up the floodgates on SQL-Injection attacks. If you want to pattern match you are better off creating full text indexes and using those.

Answer 1

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.

Answer 2

if you want assign your textbox

void Btn_Click(Object sender, EventArgs e)
{
 string fname = YourTextBoxName.Text.Trim();
 string emailid =YourTextBoxEmail.Text.Trim(); 
 //Call bll.Search
 //Bind your grid
}

You can use optinal parameters, just modify your format of Search method

public DataTable Search(fname = "", lname ="" , gender = "", maritalstatus, ......)
{
}

• Chandra sekhar: I want to call the values in a button click as i am searching..how to assign the values?
• Candie: I updated answer Chandra
• Chandra sekhar: If i call the search method i have to pass the 18 arguments as i have declared them...
• Candie: you ca use optional parameters

Answer 3

There are couple of things you could improve

1. Connection, Command, DataTable object should be disposed.

2. Instead of sending too many parameter in the bll.search() method, you should create entity class that will accept all the params that you are passing in to method. Set all params and pass that entity class object to method. Code would be more readable.

3. (Firstname LIKE '%'+@Firstname+'%') and (Emailid LIKE '%'+Emailid+'%') will this code work as expected if you pass empty or null value?

4. In DAL code, There is no need for throwing the exception, you can log your exception in database and return null value from exception block and apply null check where you are getting data from database.

5. In your UI code, Before binding the grid with data, better to initialize it with NULL value and then bind it. Good for practice.

• Chandra sekhar: In finally i have to dipose the objects? How to create a entity class? that accept params?
• Manish: Create a class that will have get/set properties for your parameters that you are passing in bind method. Pass the object of that entity class object in to your bind method and process the data. let me know if you have any confusion.
• Chandra sekhar: You mean in the DAL or can i create new class file and assign them?
• Manish: Not in DAL. Class will be created in BAL.
• Chandra sekhar: oops yeah in BAL can i use a new class file for this?