T4 template for rapid development

Developer
May 19, 2013 at 10:36 PM
I have spent some time playing with fluent data and so far I am loving it :). I have noticed that if you are working with bottom to top apprach means you created your database first and then you are writing your domain objects. Then this maybe boring work provided that you have lot of tables. Also most of the time you will be doing simple insert, update or load again and again. In order to speed up the things and place these common operations at one place I am working on a T4 template. I followed Table Data Gateway Pattern explained by Martin Fowler. This means there will an entity class and a gateway class for each table in database. The gateway class will contain all common CURD operations as static methods. Gateway class will support select single by primary key, select all entities, select by foreign key, insert, update and delete. Also all load multiple methods will support sorting and paging and will be able to bind with object data source easily.

I hope once finished this T4 template will provide the ability to jump start BBL with one click. I am going to post the generated code for one entity from my sample project here.
 #region Product

    /// <summary>
    /// Product entity class
    /// </summary>
    public partial class Product    
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
        public string Sku { get; set; }
        public string Description { get; set; }
        public int ManufacturerId { get; set; }
        public DateTime CreatedOn { get; set; }
        public DateTime ModifiedOn { get; set; }
    }
    
    /// <summary>
    /// Product gateway class
    /// </summary>
    public partial class ProductGateway
    {
        private static IDbContext Context()
        {
            return new DbContext().ConnectionStringName("MyDb",
                    new SqlServerProvider());
        }

        public static Product Select(int id)
        {
            using(var context = Context())
            {
                return context.Sql(" SELECT * FROM Product WHERE Id = @id ")
                    .Parameter("id", id)
                    .QuerySingle<Product>();
            }
        }

        public static List<Product> SelectAll()
        {
            return SelectAll(string.Empty);
        }

        public static List<Product> SelectAll(string sortExpression)
        {
            return SelectAll(0, 0, sortExpression);
        }

        public static List<Product> SelectAll(int startRowIndex, int maximumRows, string sortExpression)
        {
            using (var context = Context())
            {
                var select = context.Select<Product>(" * ")
                    .From(" Product ");

                if (maximumRows > 0)
                {
                    if (startRowIndex == 0) 
                        startRowIndex = 1;

                    select.Paging(startRowIndex, maximumRows);
                }

                if (!string.IsNullOrEmpty(sortExpression))
                    select.OrderBy(sortExpression);

                return select.QueryMany();
            }
        }

        public static int CountAll()
        {
            using (var context = Context())
            {
                return context.Sql(" SELECT COUNT(*) FROM Product ")
                    .QuerySingle<int>();
            }
        }

        
        public static List<Product> SelectByManufacturer(int manufacturerId)
        {
            return SelectByManufacturer(manufacturerId, string.Empty);
        }

        public static List<Product> SelectByManufacturer(int manufacturerId, string sortExpression)
        {
            return SelectByManufacturer(manufacturerId, 0, 0, sortExpression);
        }

        public static List<Product> SelectByManufacturer(int manufacturerId, int startRowIndex, int maximumRows, string sortExpression)
        {
            using (var context = Context())
            {
                var select = context.Select<Product>(" * ")
                    .From(" Product ")
                    .Where(" ManufacturerId = @manufacturerid ")
                    .Parameter("manufacturerid", manufacturerId);

                if (maximumRows > 0)
                {
                    if (startRowIndex == 0) 
                        startRowIndex = 1;

                    select.Paging(startRowIndex, maximumRows);
                }

                if (!string.IsNullOrEmpty(sortExpression))
                    select.OrderBy(sortExpression);

                return select.QueryMany();
            }
        }

        public static int CountByManufacturer(int manufacturerId)
        {
            using (var context = Context())
            {
                return context.Sql(" SELECT COUNT(*) FROM Product WHERE ManufacturerId = @manufacturerid")
                    .Parameter("manufacturerid", manufacturerId)
                    .QuerySingle<int>();
            }
        }
        
        public static bool Insert(Product product) 
        {
            using (var context = Context())
            {
                int id = context.Insert<Product>("Product", product)
                    .AutoMap(x => x.Id)
                    .ExecuteReturnLastId<int>();

                product.Id = id;
                return id > 0;
            }
        }
        public static bool Update(Product product)
        {
            using (var context = Context())
            {
                return context.Update<Product>("Product", product)
                    .AutoMap(x => x.Id)
                    .Execute() > 0;
            }
        }

        public static bool Delete(Product product) 
        {
            return Delete(product.Id);
        }

        public static bool Delete(int id)
        {
            using (var context = Context())
            {
                return context.Sql(" DELETE FROM Product WHERE Id = @id ")
                    .Parameter("id", id)
                    .Execute() > 0;
            }
        }
    }
    
    #endregion
Coordinator
May 25, 2013 at 7:38 AM
This is great work. Please keep us updated about the progress and when the T4 template is done.

Some feedback:
1) To allow for better unit-testability it would be better to have the methods as instances instead of statics, then the Gateway classes can be injected using constructor injection.
2) Any particular reason why you choose the Table Gateway Pattern over the Repository pattern (http://martinfowler.com/eaaCatalog/repository.html)?
Developer
May 25, 2013 at 9:16 PM
Edited May 25, 2013 at 11:29 PM
1) To allow for better unit-testability it would be better to have the methods as instances instead of statics, then the Gateway classes can be injected using constructor injection.
Just to keep it simple and save a line to instantiate since I wasn't considering the UNIT testing of gateways.
2) Any particular reason why you choose the Table Gateway Pattern over the Repository pattern
Yes simplicity. I think repository patterns makes more sense when you are considering to mock and UNIT test the objects. I was trying to create very simple and straight BLL that having minimum learning curve and can be used by any one with basic programming skills. I think repository pattern will result three code items per table entity class, repository interface and then repository implementation class?
Developer
Jun 15, 2013 at 12:20 AM
I am done with the template and I think it can be shared now. What is the best way to share the template? I am thinking to share the template undername "fluency". It is design time template which requires three parameters. The preferred approch is to use it along with texttransform.exe by registering one new external tool in visual studio. This way you don't need to include the template to project source code. Three required parameters will be

ns => Namespace
cs => Connection String
csn => Connection String Name

Alternativly the tempalte can be included to source code and can be executed from within Visual Studio. This wil need you to make some changes to template by removing the parameters by hardcoding the namespace, connection string and connection string name. I think this will be more suitable when some one will be looking to extend or update the template.
Coordinator
Jun 23, 2013 at 9:54 PM
Great. I have created a Contributions folder at the project root in TFS and added you as a contributor/developer. You can create a folder below it with the name of your contribution and then just put the code there, and then we can create some documentation for it on a separate page with a link from the main documentation page.
Developer
Jun 24, 2013 at 10:45 PM
Edited Jun 24, 2013 at 10:46 PM
kindblad wrote:
Great. I have created a Contributions folder at the project root in TFS and added you as a contributor/developer. You can create a folder below it with the name of your contribution and then just put the code there, and then we can create some documentation for it on a separate page with a link from the main documentation page.
Thanks! I have created the folder Fluency under contributions and made checkin. I have also made updates to WIKI where I created a new page about how to use the Fluency Template and also linked it to home page under Contributions list right above the Getting started section. The new page can be found here Fluency. It would be great if some one can follow the instructions I left there just to confirm that guidelines I posted there are understandable and functional.
Coordinator
Jun 30, 2013 at 5:27 AM
Great work. I will test it in 2 weeks when I'm back from my vacation.
Coordinator
Sep 21, 2013 at 6:06 AM
I'm currently using this in a project and it works great. Saves me a lot of time since Fluency generates all the code that I need.

Great work!


Lars-Erik