3
\$\begingroup\$

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?
\$\endgroup\$
  • \$\begingroup\$ 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. \$\endgroup\$ – StuartLC Aug 21 '12 at 12:49
  • \$\begingroup\$ 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. \$\endgroup\$ – Ben Robinson Aug 21 '12 at 12:56
  • \$\begingroup\$ 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. \$\endgroup\$ – Filip De Vos Aug 21 '12 at 12:57
2
\$\begingroup\$

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.

| improve this answer | |
\$\endgroup\$
1
\$\begingroup\$

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.

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ In finally i have to dipose the objects? How to create a entity class? that accept params? \$\endgroup\$ – Chandra sekhar Aug 21 '12 at 13:11
  • \$\begingroup\$ 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. \$\endgroup\$ – Manish Aug 21 '12 at 13:15
  • \$\begingroup\$ You mean in the DAL or can i create new class file and assign them? \$\endgroup\$ – Chandra sekhar Aug 21 '12 at 13:16
  • \$\begingroup\$ Not in DAL. Class will be created in BAL. \$\endgroup\$ – Manish Aug 21 '12 at 13:25
  • \$\begingroup\$ oops yeah in BAL can i use a new class file for this? \$\endgroup\$ – Chandra sekhar Aug 21 '12 at 13:28
0
\$\begingroup\$

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, ......)
{

}
| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ I want to call the values in a button click as i am searching..how to assign the values? \$\endgroup\$ – Chandra sekhar Aug 21 '12 at 12:58
  • \$\begingroup\$ I updated answer Chandra \$\endgroup\$ – Candie Aug 21 '12 at 13:00
  • \$\begingroup\$ If i call the search method i have to pass the 18 arguments as i have declared them... \$\endgroup\$ – Chandra sekhar Aug 21 '12 at 13:03
  • \$\begingroup\$ you ca use optional parameters \$\endgroup\$ – Candie Aug 21 '12 at 13:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy