Open sql connection in business service

Question

Do you see it as tight coupling that my business service class opens a SqlConnection ?

Actually a business service should not be aware of the concrete dataprovider?!

public class UnitService:

 public void DeleteUnit(Unit unit)
            {
                using (SqlConnection con = new SqlConnection());
                using (TransactionScope trans = new TransactionScope())
                {
                    con.Open();

                    _unitDataProvider.Delete(unit,con);
                    _employeeDataProvider.UpdateEmployees(con);

                    trans.Complete();
                }             
            }

What's the point of opening the `con` if it is another object, `unitDataProvider`, responsible for actual deletion and the connection doesn't seem to be passed down to this another object? — Wiktor Zychla, Jan 26 '12 at 21:14
ah sorry its pseoudo code, of course both dataprovider methods accept the connection as parameter to execute their sqlcommands on it :) — Pascal, Jan 26 '12 at 21:23

score 3 · Accepted Answer · answered Jan 26 '12 at 21:18

Your qustion is very subject to opinion...

I love to abstract code and uncouple everywhere possible. The question, as always, is that of time and requirements.

For a small simple project that does not require extensive Unit Testing within the Business Tier, your coupling although does not necessarily follow best practice may be exactly what the customer/end-user requires, and may allow you to provide the software in a more timely fashion.

For a larger/more complex/etc project it may be best to abstract the persistance layer.

It is simply not feasible to follow best practices, best design patterns, and best coding principles for every line of code you write. I've found the authors of such books quite often mention the patterns as potentially surplus to requirements and should simply be used as a tool, when needed.

Hope that helps?

Pankaj · Answer 2 · 2012-01-26T21:19:57.870

Do you see it as tight coupling that my business service class opens a SqlConnection ?

Yes. If you have some calculation to do that job you can do in Business layer before reaching to presentation layer.

One more thing I would like to suggest is to use "Using" statements for the IDisposable Objects in case of SQLConnection class

I meant It should be like below.

using (System.Data.SqlClient.SqlConnection con = new SqlConnection("YourConnection string")) {
    con.Open();
    SqlCommand cmd = new SqlCommand();
    string expression = "Parameter value";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "Your Stored Procedure";
    cmd.Parameters.Add("Your Parameter Name", SqlDbType.VarChar).Value = expression;
    cmd.Connection = con;
    using (IDataReader dr = cmd.ExecuteReader()) {
        if (dr.Read()) {
        }
    }
}

ah darn pseudo code :P I used a using for the connection stuff too in my real code just see the using trans... — Pascal, Jan 26 '12 at 21:30

Open sql connection in business service

2 Answers2