Make the method parameterized

Question

I have designed my application in a layered approach. I have a BusinessOP layer for each interface and a common data access layer. In my Data access layer I have Data Reader method like this.|

     public SqlDataReader executeQuerys(string query01)
    {
        SqlConnection con = null;
        SqlCommand com = null;
        try
        {
            con = new SqlConnection(DBConnect.makeConnection());
            con.Open();
            com = new SqlCommand(query01, con);
            return com.ExecuteReader(CommandBehavior.CloseConnection);
        }

        catch
        {
            com.Dispose();
            con.Close();
            throw;
        }

This is the code for my DBConnection layer.

     public static string makeConnection()
    {
        string con = ConfigurationManager.ConnectionStrings["MyDB.Properties.Settings.ConString"].ToString();
        return con;
    }

In my business layer I have methods like this each calling a specific stored procedure.

     public SqlDataReader getLGDivID(string divName)
    {
        string query = "EXEC getLGDivID'" + divName + "'";
        return new DataAccessLayer().executeQuerys(query);
    }

As my business operation layer is unsecure, I want to have it with parameterized query in here I'm using string concatenation to pass parameters. Can anyone hint me how to modify it?

Answer 1

You can change your function a little bit:

public SqlDataReader executeQuerys(string query01, string paramName, string value)
{
    SqlConnection con = null;
    SqlCommand com = null;
    try
    {
        con = new SqlConnection(DBConnect.makeConnection());
        con.Open();
        com = new SqlCommand(query01, con);
        com.Parameters.AddWithValue(paramName, value);
        com.Dispose();
        con.Close();
    }
    catch
    {
        com.Dispose();
        con.Close();
        throw;
    }
   return com.ExecuteReader(CommandBehavior.CloseConnection);
}

then to use it:

public SqlDataReader getLGDivID(string divName)
{
    string query = "EXEC getLGDivID @divName";
    return new DataAccessLayer().executeQuerys(query, "@divName", divName);
}

---

EDIT:

As @silvermind pointed out, you should dispose your connection properly. The way you have it now it will dispose connection only when you catch an exception.

This is bad, make use of IDisposable, for example:

public SqlDataReader executeQuerys(string query01, string paramName, string value)
{
    using (SqlConnection con = new SqlConnection(DBConnect.makeConnection()))
    {
        try
        {
            con.Open();
            com = new SqlCommand(query01, con);
            com.Parameters.AddWithValue(paramName, value);
        }
        catch(SqlException ex)
        {
            //Handle the exceptio
            //no need to dispose connection manually
            //using statement will take care of that
        }
    }
    return com.ExecuteReader(CommandBehavior.CloseConnection);
}