none
ADO.NET Command Parameters in Connection Class RRS feed

  • Question

  • Hello Prof;

    New in Programming Field and ;

    I'm Using A Class "Connection" to connect to Sqlserver database 

    namespace employeesdb { class Connection { public static SqlConnection GetConnection() { SqlConnection conn = new SqlConnection(); conn.ConnectionString = @"myconnstring"; return conn; } public static DataSet ExecuteDataSet(string sql) { SqlConnection con = GetConnection(); SqlCommand cmd = new SqlCommand(sql, con);

    List<SqlParameter> @params = new List<SqlParameter>(); // this line
                @params.ForEach(x => cmd.Parameters.Add(x)); // and This Line

    // Here I want To Add Something Concern cmd Parameter for calling it with function DataSet ds = new DataSet(); SqlDataAdapter adapt = new SqlDataAdapter(cmd); try { adapt.Fill(ds); } catch { con.Close(); } return ds; } } }


    I call my Function like That

     private void XtraForm1_Load(object sender, EventArgs e)
            {
                DataSet dsa = Connection.ExecuteDataSet("Select * from tblusers");
    
    
    // need To Add Parameters Here Can I DO That ????
                dataGridView1.DataSource= dsa.Tables[0];
    }


    • Edited by Ahmed Aboelez Monday, December 28, 2015 5:29 PM just Changed My Code
    • Moved by Kristin Xie Tuesday, December 29, 2015 5:53 AM move to better forum
    Monday, December 28, 2015 5:10 PM

Answers

  • Hi Ahmed,

    Here would be my recommendations for adding to your current code.

    Update to your Connection class:

    // Pass SqlParamater array with predefined values from XtraForm1_Load
    public static DataSet ExecuteDataSet(string sql, SqlParameter[] parameters)
    {
        SqlConnection con = GetConnection();
        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddRange(parameters);
        DataSet ds = new DataSet();
        SqlDataAdapter adapt = new SqlDataAdapter(cmd);
        try
        {
            adapt.Fill(ds);
        }
        catch
        {
            con.Close();
        }
        return ds;
    }

    And to your grid load method:

    private void XtraForm1_Load(object sender, EventArgs e)
    {
        SqlParameter[] parameters = new SqlParameter[]
        {
            new SqlParameter("@parameter1", "string1"),
            new SqlParameter("@parameter2", 222),
        };
        DataSet dsa = Connection.ExecuteDataSet("Select * from tblusers where Name = @parameter1 AND UserId = @parameter2 ", parameters);
    
        dataGridView1.DataSource = dsa.Tables[0];
    }

    I would recommend not creating the predefined values within the ExecuteDataSet method of your connection class so that you can re-use it in other areas of your application if needed, and it wouldn't be coupled with that specific grid.

    As Kevininstructor mentioned, be careful how much you load into a single grid with hopes of filtering as it could become "resource inefficient", but with today's machines you can still get away with a lot.

    Best of luck to you.




    • Edited by DPCodesalot Tuesday, December 29, 2015 2:36 AM
    • Proposed as answer by BonnieBMVP Tuesday, December 29, 2015 6:01 AM
    • Marked as answer by Ahmed Aboelez Wednesday, December 30, 2015 1:51 AM
    Tuesday, December 29, 2015 2:34 AM

All replies

  • Hi,

    I didn't get your question but just wanted to give you suggestion for general practice you can use SQL Helper class.

    http://www.aspsnippets.com/Articles/Using-SqlHelper-class-in-ASPNet-Tutorial-with-examples-in-C-and-VBNet.aspx


    Rajat Jaiswal

    Monday, December 28, 2015 5:22 PM
  • I Have Edit My Question Again Please Look At Code Again 

    I Just Need To Add List Of Parameters With Command ;
    Monday, December 28, 2015 5:30 PM
  • Hello,

    It really makes sense to setup methods dedicated to retrieving specific data e.g. a method to return customers where country equals a specific country, have a specific field list e.g. SELECT CompanyName, Identifier, ContactName WHERE Country = @Country where @Country would be a string value passed into the method and use in a pre-defined parameter that we set the value coming in to the parameter and then return a DataTable as a DataSet is overkill here looking at your current question. 

    Same idea for CRUDE operations.

    Thinking data to return, it is always smart to not return all rows and columns unless there is a business need for it and even then best to list the fields in the select statement to return a DataTable unless there are relationships that need setting up from child tables.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter or Facebook via my MSDN profile but will not answer coding question on either.

    Monday, December 28, 2015 11:18 PM
  • Hi Ahmed,

    Here would be my recommendations for adding to your current code.

    Update to your Connection class:

    // Pass SqlParamater array with predefined values from XtraForm1_Load
    public static DataSet ExecuteDataSet(string sql, SqlParameter[] parameters)
    {
        SqlConnection con = GetConnection();
        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddRange(parameters);
        DataSet ds = new DataSet();
        SqlDataAdapter adapt = new SqlDataAdapter(cmd);
        try
        {
            adapt.Fill(ds);
        }
        catch
        {
            con.Close();
        }
        return ds;
    }

    And to your grid load method:

    private void XtraForm1_Load(object sender, EventArgs e)
    {
        SqlParameter[] parameters = new SqlParameter[]
        {
            new SqlParameter("@parameter1", "string1"),
            new SqlParameter("@parameter2", 222),
        };
        DataSet dsa = Connection.ExecuteDataSet("Select * from tblusers where Name = @parameter1 AND UserId = @parameter2 ", parameters);
    
        dataGridView1.DataSource = dsa.Tables[0];
    }

    I would recommend not creating the predefined values within the ExecuteDataSet method of your connection class so that you can re-use it in other areas of your application if needed, and it wouldn't be coupled with that specific grid.

    As Kevininstructor mentioned, be careful how much you load into a single grid with hopes of filtering as it could become "resource inefficient", but with today's machines you can still get away with a lot.

    Best of luck to you.




    • Edited by DPCodesalot Tuesday, December 29, 2015 2:36 AM
    • Proposed as answer by BonnieBMVP Tuesday, December 29, 2015 6:01 AM
    • Marked as answer by Ahmed Aboelez Wednesday, December 30, 2015 1:51 AM
    Tuesday, December 29, 2015 2:34 AM