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?

Never rethrow an exception with `throw ex;` instead "release it" by just calling `throw;`. Otherwise you lose the stack trace that came before you re-threw it. — juharr, Oct 16 '14 at 17:47
As long as you only pass a string you are stuck. Personally I would get rid of that generic and vulnerable method executeNonQueries. If you need to keep it I would create an overload that receives a generic list of parameters that can be added to the command before you execute it. The biggest issue in general here is that your layers don't have anywhere near the proper level of separation. You are still doing sql in the business layer. You Party object should have a Save method instead of passing it a Party object. The save method should call the save logic in the data layer. — Sean Lange, Oct 16 '14 at 17:50
You might want to look into using Dapper. It gracefully handles the parameter mapping to and from entity objects and gets rid of the boiler plate ADO.Net code you have in your `executeNonQuery` method. — juharr, Oct 16 '14 at 17:51
+1 for [Dapper](https://github.com/StackExchange/dapper-dot-net). Lately it was the best time spent on learning something new — Steve, Oct 16 '14 at 17:59
@chathwind Here's that exact [question](http://stackoverflow.com/questions/6241246/any-good-samples-for-getting-started-with-dapper) on SO with an answer by Marc Gravell, one of creators of Dapper. — juharr, Oct 16 '14 at 18:11

Eric J. · Accepted Answer · 2014-10-16T18:33:24.810

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
}

edited Oct 16 '14 at 18:33

answered Oct 16 '14 at 17:48

Eric J.

147,927
63
340
553

can a dictionary string hold property types like byte array byte[]. ? – ChathurawinD Oct 16 '14 at 17:52
Yes, `Dictionary`. – Eric J. Oct 16 '14 at 17:53
@ EricJ I'm bit confused. How can I pass the parameters in the business layer? and how can I receive that in DataAccess layer using that dictionary. – ChathurawinD Oct 16 '14 at 17:59
passing in the parameters via dictionaries isn't necessarily the best approach... there are ways around that; but yes, it would work – Marc Gravell Oct 16 '14 at 17:59
@MarcGravell: Yes, like EF or Dapper. The current layering is not optimal. Do you have other thoughts on a good band-aide, in case that's all the OP can do? – Eric J. Oct 16 '14 at 18:04
@Eric "dapper" is the first tool in my first aid kit at the moment; it would well enough here passing down the domain object "as is" to hold the parameters – Marc Gravell Oct 16 '14 at 18:09
@EricJ Thanks for the code.If you can add how to pass to dictionary in business layer it would be great. Not complete code just show how to add two parameters.Dapper is something new to me, can you provide any code example for that too, if you don't mind. – ChathurawinD Oct 16 '14 at 18:11
@chathwind: I have only dabbled in Dapper, but Marc is the primary / most active author on the Dapper project https://github.com/StackExchange/dapper-dot-net – Eric J. Oct 16 '14 at 18:13
@EricJ. If you can add how to pass to dictionary in business layer it would be great. Not complete code just show how to add two parameters. – ChathurawinD Oct 16 '14 at 18:15
@chathwind: Added an example. – Eric J. Oct 16 '14 at 18:33

How to make these methods parameterized?

1 Answers1