# Mixing Command and Transaction Script Patterns

Patterns of Enterprise Applications Architecture by Martin Fowler defines Transaction Script pattern that is one of domain logic patterns. In his book Martin Fowler also gives a hint how to use Command Pattern by Gang of Four with Transaction Script. In this posting I will show you some real-life code that makes use of both patterns.

### My Jira importer

I have simple Jira importer that imports new tasks and work logs to my main system. As the importer is small service with some operations defined I had no point to build it up using some bigger architectural model. I took Transaction Script and Command Pattern together and built my importer based on these.

The main command flow of my Jira importer is simple:

• import new or updated projects,
• import new or updated tasks,
• import new or updated work logs.

I’m using Jira web services and simple Entity Framework based data model to import data to my main database.

### Commands

Two have better control over import actions and to later execute them by using command like arguments I used Command Pattern to make it easier to run import commands in row.

Here is my simple base command class:

public abstract class BaseCommand{    private static string _jiraToken;     protected string JiraToken    {        get        {            if(string.IsNullOrEmpty(_jiraToken))                using (var client = new JiraSoapServiceService())                {                    _jiraToken = client.login("XXX", "XXX");                }             return _jiraToken;        }    }     protected SqlConnection GetConnection()    {        var connStr = ConfigurationManager.ConnectionStrings["MyLOB"].ConnectionString;        var conn = new SqlConnection(connStr);        conn.Open();         return conn;    }     public abstract void Execute();}

And here is the main program that uses commands:

public void Run(){    var commands = new List<BaseCommand>();    commands.Add(new ImportStatusesCommand());    commands.Add(new ImportIssueTypesCommand());    commands.Add(new ImportProjectsCommand());    commands.Add(new ImportTasksCommand());    commands.Add(new ImportWorkLogsCommand());     foreach (var command in commands)    {        command.Execute();    }}

It’s easy to add new commands here and later use command line arguments to control what actually is imported when importer is run.

### Transaction Scripts

My commands are internally implemented as transaction scripts – they just do directly some simple operations like inserting and updating data. To keep application code readable and small I am using some stored procedures where I can hide some more data related logic if needed. Here is the project importer class:

public class ImportProjectsCommand : BaseCommand{     public override void Execute()    {        var projects = new RemoteProject[]{};         using(var client = new JiraSoapServiceService())        {            projects = client.getProjectsNoSchemes(JiraToken);        }         foreach (var project in projects)        {            SaveOrUpdateProject(project);        }    }     private void SaveOrUpdateProject(RemoteProject jiraProject)    {        using(var conn = GetConnection())        {            var command = conn.CreateCommand();            command.CommandText = "SELECT COUNT(*) FROM Project WHERE Id=@Id";            command.Parameters.AddWithValue("@Id", jiraProject.id);            var count = (int)command.ExecuteScalar();             command = conn.CreateCommand();            command.CommandType = CommandType.StoredProcedure;             if (count > 0)            {                command.CommandText = "UPDATE_PROJECT";            }            else            {                command.CommandText = "INSERT_PROJECT";            }             command.Parameters.AddWithValue("@Id", jiraProject.id);            command.Parameters.AddWithValue("@Key", jiraProject.key);            command.Parameters.AddWithValue("@Name", jiraProject.name);            command.ExecuteNonQuery();        }    }}

Although you can see way different code in Patterns of Enterprise Applications Architecture by Martin Fowler the idea remains the same – the code in command just makes one or more similar transactions and doesn’t care about nothing more.

### Conclusion

It’s possible to mix patterns from different categories to get something done the way that you don’t get stuck later. The idea I found from Fowler’s book was applicable also in some other small applications I have written. These applications are years old now and as they are still small applications they still use mix on Transaction Script and Command Pattern. If your application is small and you think it will stay small you can use the ideas presented in this blog post.