Pattern for using DataSets with Repositories


This post is precipitated by a question someone had about the following error when loading a table adapter insert query with too many values. The error is:
 
"Cannot evaluate expression because a thread is stopped at a point where
garbage collection is impossible, possibly because the code is optimized."
 
The questions deals with an insert into a table with 70+ fields. I am going to hit that in the next section and then move to a reusable pattern with DataSets and Repositories. The pattern is designed to allow one to drag and drop the data definitions and then use code generation for the Repositories to access the individual tables.
 

Groundwork

Before going further, I think I should state that 70 fields in a database table is a lot. I am not stating that it is "too many", as I do not know what information is collected. The question detailed an orders table, so I am wondering if the orders table looks like this:

OrderId
Item1
Item1Quantity
Item2
Item2Quantity
Item3
Item3Quantity
Item4
Item4Quantity

If this is the case, some normalization is in order rather than have a 70 field orders table. The poster also queried in another query about nullable Booleans, which suggest a misunderstanding of the use of bits, so I would guess normalization is needed in this database. That alone would solve the problem.

 

Data Pattern with DataSets

The following pattern is something I recently worked on with an application I will likely pass on to others due to some financial constraints in my current company. The group is an offshore group that appears to have precious little .NET experience, so a data access pattern that can largely use drag and drop is a good thing. The background is largely unimportant, but it gives perspective to why I would use DataSets and Table Adapters in this project over custom objects, et al.

So, the initial work is a single DataSet with all tables in the database. Yes, this might consume a few more cycles than selective tabling, but it is ultra simple and one can easily rework the DataSet if there is a schema change.
 
The design goals of the data layer were simple.
 
  1. Separation of concerns was paramount – This meant creating a business layer that, initially, is a pass through only.
  2. Had to be able to change connection at runtime – This is an issue with the default implementation of Table Adapters, but is easily overcome
  3. Had to be able to generate the main bits for the Repository to avoid boilerplate coding

DataSet

There is really nothing special about the DataSet, other than I have added a query to each table to get by the primary key (FillById). The steps for this are simple:

  1. Drag table on DataSet
  2. Right click and choose New Query
  3. Keep the default of SQL Query
  4. Keep the default of select
  5. On the query window add the following {fieldName} = @id, where {fieldName} is the primary key
  6. On the next window, change the name to FillById and click Finish

I do the same for each of the foreign keys. A lot of work? When developing the first one, sure. Beyond that, I can code gen off the database. The only downside of code gen, right now, is I code gen outside of the adapter and have to paste in the bits. Since the database gives me the info I need about the fields, this is simple work. I will hit this a bit more when I talk about code gen.

Repository

The repository is completely code gened. I am currently using the following Template:

using System.Data.SqlClient;
using Microtrak.UnitWarehouse.Data.DataSets;
using Microtrak.UnitWarehouse.Data.DataSets.UnitWarehouseDSTableAdapters;
using Microtrak.UnitWarehouse.Data.Interfaces;

namespace Microtrak.UnitWarehouse.Data.Respositories
{
    public class {token}Repository : IDataRepository
    {
        #region Declarations

        private readonly string _connectionString;

        #endregion //Declarations

        #region Constructors

        public {token}Repository(string connectionString)
        {
            _connectionString = connectionString;
        }

        #endregion //Constructors

        #region Get Routines

        public UnitWarehouseDS.{token}DataTable GetAll()
        {
            var table = new UnitWarehouseDS.{token}DataTable();
            {token}TableAdapter adapter = GetAdapter(_connectionString);
            adapter.Fill(table);

            return table;
        }

        public UnitWarehouseDS.{token}Row GetByID(int id)
        {
            var table = new UnitWarehouseDS.{token}DataTable();
            {token}TableAdapter adapter = GetAdapter(_connectionString);
			adapter.FillById(table, id);

            if (table.Rows.Count == 0)
                return null;

            return (UnitWarehouseDS.{token}Row) table.Rows[0];
        }

        #endregion //Get Routines

        #region Save Routines

        public UnitWarehouseDS.{token}Row Insert(UnitWarehouseDS.{token}Row row)
        {
            {token}TableAdapter adapter = GetAdapter(_connectionString);
            int rowsAffected = {insert};

            if (rowsAffected > 0)
            {
                var table = new UnitWarehouseDS.{token}DataTable();
                //CREATE LINE TO GET ROW

                if (table.Rows.Count > 0)
                    return (UnitWarehouseDS.{token}Row) table.Rows[0];
            }

            return null;
        }

        public UnitWarehouseDS.{token}Row Update(UnitWarehouseDS.{token}Row row)
        {
            {token}TableAdapter adapter = GetAdapter(_connectionString);
            int rowsAffected = adapter.Update(row);

            if (rowsAffected > 0)
                return row;

            return null;
        }

        public UnitWarehouseDS.{token}Row Save(UnitWarehouseDS.{token}Row row)
        {
            //Check row. If present then update, else save
            UnitWarehouseDS.{token}Row testRow = GetByID(row.{token}Id);

            if (testRow == null)
                return Insert(row);

            return Update(row);
        }

        #endregion //Save Routines

        #region Private Routines

        private static {token}TableAdapter GetAdapter(string connectionString)
        {
            var adapter = new {token}TableAdapter
                              {
                                  Connection = new SqlConnection(connectionString)
                              };
            return adapter;
        }

        #endregion //Private Routines
    }
}

The curly braces are things I am replacing, like {token}, which really should be {tableName}. I started with a single token and then realized the Insert method on the TableAdapter sometimes fails with nullable fields. Thus the Insert I have moved to is no longer using the Insert(row) method, but instead using a custom query. Here is an example of the generated code:

        public UnitWarehouseDS.OneOffUnitRow Insert(UnitWarehouseDS.OneOffUnitRow row)
        {
            OneOffUnitTableAdapter adapter = GetAdapter(_connectionString);
            int rowsAffected = adapter.Insert(row.UnitId,
                                              row.OneOffReasonId,
                                              (row.IsNotesNull() ? null : row.Notes),
                                              row.CreatedDate,
                                              row.CreatedUserId,
                                              (row.IsUpdatedDateNull() ? (DateTime?) null : row.UpdatedDate),
                                              (row.IsUpdatedUserIdNull() ? (int?) null : row.UpdatedUserId));

            if (rowsAffected > 0)
            {
                var table = new UnitWarehouseDS.OneOffUnitDataTable();
                adapter.FillByUnitIdDesc(table, row.UnitId);

                if (table.Rows.Count > 0)
                    return (UnitWarehouseDS.OneOffUnitRow) table.Rows[0];
            }

            return null;
        }

I have not placed this in the code gen yet. Instead I am pasting the results of the table with sp_help into Excel and using the following formula:

=IF(G2="no", CONCATENATE("row.",A2,","),CONCATENATE("(row.Is",A2,"Null() ? (",B2,IF(B2="int","?",""),") null : row.",A2,"),"))

This will either produce something like:

row.UnitId,

or

(row.IsUpdatedDateNull() ? (DateTime?) null : row.UpdatedDate),

There are still a few tweaks I have to do to ensure the above statement is perfectly produced, but I will put it into my code generator.

Business Layer

My business layer, at this time, is very thin. For example, if I am merely returning results to paint in a form, the call is like this:

        public static {DataSetName}.{TableName} GetAllOneOffReasons(string connectionString)
        {
            var repository = new {TableName}Repository(connectionString);
            return repository.GetAll();
        }

 See how beautfully this one can be code generated?

Now, you might ask "why have a business layer when it is only pass through?" Good question. I could go directly to the database rather than use this facade/pass through class. And in a case where I had a full object model laid out, I probably would, as the Repository provides a bit of abstraction without the need of a facade for the data access. It is done here as it is quite easy to generate the facade. There is a UI logic layer on top of this layer, which provides code that is difficult to boilerplate and then the UI, which is designed to be extremely thin (separation of concerns).

An aside: The thin UI is something I regularly opt for, as I never know where an app might lead. This particular application is a Windows Forms application (not my forte, but I am working through it). But it could morph into a WPF application without a huge amount of work, as long as I keep logic out of the form and keep the form code to code that either a) displays data or b) gathers data from the form and passes it off. There are a couple of things that need to be refactored to truly separate UI from logic (perhaps I should have started with WPF?), but it is easy enough to refactor.

Code Generator

For the code generator, I first aimed at SMO (SQL Management Objects). I then experienced a couple of problems. Even though I overcame them, I figured they might plague the offshore team if I released the code to them and opted to use some SQL Server "trickery" I know.

To set up the code gen, I created two DataSets. I only strongly type these to simplify use (I only wanted to spend half an hour max on the initial code gen). The DataSets are sp_Help and Individual_sp_Help. The first is used to get the tables from the database. It also gets other objects, but the following gets rid of that:

            DataView userTable = new DataView(ds.Object);
            userTable.RowFilter = "Object_type = ‘user table’";

The logic is simple. Use SP_help to get the object names (the token) and then process information about the table. The basic data gatethering is like so:

        private Individual_SP_help GetSP_help(string tableName)
        {
            string connectionString = GetConnectionString();
            string sql = "exec sp_help ‘" + tableName + "’";
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand command = new SqlCommand(sql, conn);

            SqlDataAdapter da = new SqlDataAdapter(command);
            Individual_SP_help ds = new Individual_SP_help();

            da.TableMappings.Add("Table", "Table");
            da.TableMappings.Add("Table1", "Column");
            da.TableMappings.Add("Table2", "Identity");
            da.TableMappings.Add("Table3", "RowGuid");
            da.TableMappings.Add("Table4", "Location");
            da.TableMappings.Add("Table5", "Index");
            da.TableMappings.Add("Table6", "Constraint");

            conn.Open();
            da.Fill(ds);

            conn.Dispose();

            //ds.WriteXmlSchema("C:\Individual_SP_help.xsd");

            return ds;
        }

I then do a check to ensure the table does not start with sys (as sysdiagrams appears in many of my databases) and process each field. The most complex logic is the creation of the INSERT statement. It is then a simple matter of replacing the tokens in the template file (shown in the Repository section above).

Aside again: If one wants to know how to intially create a DataSet without the table adapters, the following line is how you do it.

ds.WriteXmlSchema("C:\Individual_SP_help.xsd");

If you take the above routine and change out the STD for DataSet, like so, you can generate your STD:

        private DataSet GetSP_help(string tableName)
        {
            string connectionString = GetConnectionString();
            string sql = "exec sp_help ‘" + tableName + "’";
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand command = new SqlCommand(sql, conn);

            SqlDataAdapter da = new SqlDataAdapter(command);
            DataSet ds = new DataSet();

            da.TableMappings.Add("Table", "Table");
            da.TableMappings.Add("Table1", "Column");
            da.TableMappings.Add("Table2", "Identity");
            da.TableMappings.Add("Table3", "RowGuid");
            da.TableMappings.Add("Table4", "Location");
            da.TableMappings.Add("Table5", "Index");
            da.TableMappings.Add("Table6", "Constraint");

            conn.Open();
            da.Fill(ds);

            conn.Dispose();

            //ds.WriteXmlSchema("C:\Individual_SP_help.xsd");

            return ds;
        }

This is an easy way to set up a DataSet when you do not want the autogenerated table adapters. As the dataset here is from a system sproc, I felt drag and drop was not my best option (esp. considering the result sets are different depending on what you are querying).

Summary

This particular project provided many things for me that I can pass on to a team that does not have an intimate understanding of .NET. First, the method of setting up the Data "objects" is pure drag and drop. Second, the pattern is so easily duplicated it can be generated. In fact, every single level of the application can be generated except for some UI logic.

Since I am a strong advocate of TDD, I am sure some will ask why there are no tests. The answer is two-fold. First, I did have tests on the initial pattern, when I was developing it from scratch. Once I had a pattern for a Repository that worked, I created the code generator. I then added tests on one of the generated classes and found that the pattern worked. From then on, creating additional tests would have been time taken I did not have (although I could have code gened them as well Wink). The second reason is I did not see the value in generating tests for a successful generated pattern. This was largely precipitated by the fact this is not a wide release product. I guess I can also add that the offshore team is ignorant of TDD, so it would be a waste to pass this on.

The primary purpose of posting this is opening another avenue for people using DataSets. If you like this, feel free to refine it. If not, move on. The archtiecure here is not Google scale, so I would not opt for this methodology if you need an application that can handle tens of thousands of requests in a second. The inherent nature of DataSets are table = table in dataset. This means you can end up with many database calls to fill things in. There are ways around this, of course, but that is a topic for another day.

The main value in this particular set up is the fact you can code gen almost all of your code. This means you can spend time on the UI and business logic. There is a bit of constraint as a trade off, but it should be fine for most applications. As it stands, it takes me less than 2 minutes to wire in a new table to my UI. Once I set up code gen for the FillById and code gen the facade, it will be less than half that time. This leaves me to focus on User Experience (form layout?) and ensuring I have proper validation logic. As the application is rather complex, this is a Godsend.

Peace and Grace,
Greg

Advertisements

2 Responses to Pattern for using DataSets with Repositories

  1. Kelly says:

    Excellent article Greg. I do wonder about the data architecture when tables get to this size but it does happen.

  2. Gregory says:

    Kelly:
     
    I am tracking with you all the way on this. 70 fields in a database table is excessive. The solution here was just a simple way to use the drag and drop bits, but still have control over the layers. I am not sure I would use this in anything that ever needed to scale very large, although the table adapters are fast. I have another pattern for lots of related data.
     
    Greg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: