The Right / Ideal Way to Manage SQL Connections and Transactions - C #

Question

I have a C # application that makes many SQL calls through several functions and classes. The database is SQL Server 2008 R2. Currently, each class or function will open its own SQL connection whenever it needs to read / write data to the database:

using (SqlConnection connection = new SqlConnection(connectionString)) 

Does this work correctly with connection management? Also, I am just starting to add transactions, so if I am not mistaken, transactions must be completed in the same connection. Here is an example of some code:

 try { Class1 class1 = new Class1(); class1.data = "somedata"; class1.save(); // new sql connnection Class2 class2 = new Class2(); class2.data = GetSomeData(); // new sql connection class2.save(); // new sql connection } catch (Exception e) { } 

I looked at TransactionScope and SqlTransaction, but I'm not sure if my current connection structure is perfect for this implementation. Any guidance would be greatly appreciated.

Answer 1

"Opening" a new connection for each operation is not resource-intensive, as you might think.

The reason for this is that all ADO.NET providers today use the connection pool internally. This means that every time you .Close() connection, it really just returns to the pool.

Performance degradation occurs when you start attracting multiple transactions in a transaction, as this means that the transaction is being promoted to a distributed transaction. That is, a third-party transaction operator is involved and controls the transaction.

So, the answer is that while your entities do not have dependencies, there is no reason to start with transactions or stop using multiple connections.

When are transactions required?

However, if object A depends on object B, you must use transactions.

Dependencies may also be specified by business rules. Take an example.

You have a bank transfer, when money must be withdrawn from account A and deposited to account B. Without a transaction, money can be withdrawn from account A, but will not be deposited to B (due to an error).

Answer 2

You can enclose both saved calls inside a using clause that starts a transaction.

whenever your system wants to start a connection, it will check the parent transactions, if any.

verification samples at http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

Answer 3

At the very least, create a data access class that handles the persistence of your objects - just pass the object to a class method that knows how to handle it. Distributing persistence code across classes makes sense only if the class is an isolated object, which is most likely not (most objects interact with other objects). You must use the ORM functions available in .NET-SQLMetal (command line) or the LINQ-to-SQL ORM designer in Visual Studio. Then you will not have the problems you are facing right now, and instead focus on what you are really trying to do with your code.

Answer 4

Yes, the correct solution has a using clause in the same block of code that does the actual data marshaling between your objects and the SQL server. The reason is that if an error occurs, you do not experience connection leaks, which can cause other very complex problems to detect problems.

The question is, should you really have a save call for each individual class, or should you use something like a data access provider to process messages with the database server.

If Entity B has a dependency on Entity A, then the value of A must be saved, and the identifier returned before B can correctly save, you have a choice. Either A must have a reference to B and process it, or you pass both objects to the data access class, which processes the correct save. For example, if Entity A was the order header and B was the position. In this situation, A must have a reference to set B. When A.Save() is A.Save() , then it must B.Save() over this collection and call B.Save() .

In any case, it would be fairly easy to implement transactions in the context of a single connection.