How to make these methods parameterized?

Question

I have method in my business layer like this.

    public Boolean saveParty(Party ptObj)
    {
        string query1 = "EXEC insertToParty'" + ptObj.PTRegID + "','" + ptObj.PTName.Replace("'", "''") + "','" + ptObj.PTSymARR + "','" + ptObj.PTSymName + "','" + ptObj.elecRepCol + "','" + ptObj.PTSec + "','" + ptObj.phPri + "','" + ptObj.phSec + "','" + ptObj.bsAddress + "','" + ptObj.secAddress + "','" + ptObj.addedUser + "','" + ptObj.addedDate + "','" + ptObj.PTstatus + "'";
        return (new DataAccessLayer().executeNonQueries(query1));
    }

In my data access layer I have created execute non queries like this.

public Boolean executeNonQueries(string query02)
    {
        Boolean flag = false;
        SqlConnection con = null;
        SqlCommand com = null;
        try
        {
            con = new SqlConnection(DBConnect.makeConnection());
            con.Open();
            com = new SqlCommand(query02, con);
            com.ExecuteNonQuery();
            flag = true;
        }
        catch (Exception ex)
        {
            flag = false;
            throw ex;
        }
        finally
        {
            com.Dispose();
            con.Close();
        }
        return flag;
    }

In my DataBase connect layer I have implemented like this.

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

I have these methods in three separate classes. What I want to know is how to change these methods to use parameterized queries as I'm using string concatenation here without changing the layer architecture. how can I achieve that?

Answer 1

Your business layer has no business forming the SQL string. It should concern itself with the domain, and leave persistence to the data access layer. If possible, move that responsibility entirely to the data access layer.

If the realities in your project prevent you from placing responsibilities where they belong, you can consider having the business layer pass down the SQL statement with parameter placeholders, plus a dictionary that contains entries for each parameter name and parameter value.

In the business layer

public Boolean saveParty(Party ptObj)
{
    string query1 = "EXEC insertToParty @Id, @Name, etc";

    Dictionary<string, object) p = new Dictionary<string, object>();
    p.Add("@Id", ptObj.PTReqID);
    p.Add("@Name", ptObj.PTName);
    // etc.

    return (new DataAccessLayer().executeNonQueries(query1));
}

In the data access layer, add the parameters from the dictionary, e.g.

public Boolean executeNonQueries(string query02, Dictionary<string, object> parameters)
{
    // Your existing code to setup connection
    foreach (var param in dictionaryWithParametersAndValues)
    {
        com.AddWithValue(param.Key, param.Value);
    }
    com.ExecuteNonQuery(); 
    // Rest of your existing code
}