This chapter is from the book
This chapter is from the book
This chapter is from the book
Programmatic Transactions
In this chapter, the database that was created earlier is reused. Just as a reminder, the database diagram is shown again in Figure 7-2.
)
Figure 7-2 Sample database.
With serviced components, you can do transactions by using attributes. Before looking into these attributes, however, let’s take a look at how transactions are done programmatically so that you can easily compare the techniques.
Using the .NET data provider for SQL Server, you can do transactions programmatically with the SqlTransaction class. A new transaction is created with the method BeginTransaction of the SqlConnection class, which returns an object of type SqlTransaction. Then the SqlTransaction methods Commit and Rollback can be used to commit or to undo the transaction result.
Let’s immediately consider an example, with the class CourseData. A connection string is passed into the constructor of this class, as shown in Listing 7-1. The caller of this class can read the connection string from a configuration file if required. To avoid creating a new SqlCommand with every method call, a new SqlCommand is instantiated in the constructor, and the parameters that are needed to add a new course record are added to the command object.
Listing 7-1 Programmatic Transactions
using System;
using System.Data;
using System.Data.SqlClient;
namespace Samples.Courses.Data
{
public class CourseData
{
private string connectionString;
private SqlCommand insertCourseCommand;
public CourseData(string connectionString)
{
this.connectionString = connectionString;
insertCourseCommand = new SqlCommand();
insertCourseCommand.CommandText = "INSERT INTO " +
"Courses (CourseId, Number, Title, Active) " +
"VALUES(@CourseId, @Number, @Title, @Active)";
insertCourseCommand.Parameters.Add("@CourseId",
SqlDbType.UniqueIdentifier);
insertCourseCommand.Parameters.Add("@Number",
SqlDbType.NChar, 10);
insertCourseCommand.Parameters.Add("@Title",
SqlDbType.NVarChar, 50);
insertCourseCommand.Parameters.Add("@Active",
SqlDbType.Bit);
}
In the method AddCourse that is shown in Listing 7-2, the connection object is instantiated, the connection is associated with the previously created command object, and the connection is opened. Inside a try block, the INSERT statement is executed with the method ExecuteNonQuery. Before that, a new transaction is started using the connection.BeginTransaction method. The returned transaction object is automatically associated with the connection. If the database insert is successfully, the transaction is committed. If an exception is thrown within ExecuteNonQuery because of some error while issuing the database insert, the exception is caught, and a rollback is done in the catch block.
Listing 7-2 AddCourse Method with Programmatic Transactions
public void AddCourse(Course course)
{
int customerId = -1;
SqlConnection connection =
new SqlConnection(connectionString);
insertCourseCommand.Connection = connection;
insertCourseCommand.Parameters["@CourseId"].Value =
course.CourseId;
insertCourseCommand.Parameters["@Number"].Value =
course.Number;
insertCourseCommand.Parameters["@Title"].Value =
course.Title;
insertCourseCommand.Parameters["@Active"].Value =
course.Active;
connection.Open();
try
{
insertCourseCommand.Transaction =
connection.BeginTransaction();
insertCourseCommand.ExecuteNonQuery();
insertCourseCommand.Transaction.Commit();
}
catch
{
insertCourseCommand.Transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
}
In the Test class of the client application (Figure 7-3), the class CourseData is used to create a new course. The connection string passed to the constructor is read from the configuration file using the ConfigurationSettings class.
Listing 7-3 Client Test Application
using System;
using System.Configuration;
using Samples.Courses.Data;
using Samples.Courses.Entities;
class Test
{
static void Main()
{
// read the connection string from the configuration file
string connectionString =
ConfigurationSettings.AppSettings["Database"];
// write a new course to the database
CourseData db = new CourseData(connectionString);
Course c = new Course();
c.Number = "MS-2389";
c.Title = "Programming ADO.NET";
c.Active = true;
db.AddCourse(c);
}
}
Listing 7-4 shows the application configuration file that is used by the client application. In the configuration, the database connection string is defined as a child element of <appSettings>.
Listing 7-4 Application Configuration File to Define the Connection String
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="Database"
value="server=localhost;database=Courses;
trusted_connection=true" />
</appSettings>
</configuration>
As shown in the previous examples, dealing with transactions programmatically is not a hard task. However, how can the implementation be done if multiple SQL commands should be part of a single transaction? If all these commands are closely related, putting them into a single try block is not a big deal. However, a scenario in which a single transaction should cross multiple components is not so straightforward.
If multiple classes should participate in the same transaction (for example, a customer should be added, and an entry to the course map must also be done), dealing with transactions is not that easy anymore. Take a look at how this can be handled.
Figure 7-3 shows a scenario where a single transaction is useful: If one course attendee wants to change her course schedule, she must be removed from one course list and added to another one. Now it should never happen that the course attendee is added to the new course, but not deleted from the previous one, and it should never happen that the attendee is just removed from a course, but never added to the new course. Both adding and removing the attendee from the course must be part of the same transaction.
)
Figure 7-3 Transactions with multiple components.
You can program multiple database actions that are spread across multiple classes, and that should participate in the same transaction, by passing the connection object that is associated with the transaction as an argument to the methods. Inside the methods, the same connection object can be used to participate within the same transaction.
This can get quite complex with many classes and bigger scenarios. You have to decide whether the class should participate in the same transaction, or whether a new transaction should be started. What should happen if an object is called in between that does not use database methods at all? With such a method, it would be necessary to add an additional argument so that the connection object could be passed.
Enterprise Services offers a way to deal with this complexity: automatic transactions.