Html Conventions in ASP.net MVC

Just today, Jimmy wrote a great post on using HTML conventions in ASP.net MVC using FubuMvc’s core HTML conventions. Go ahead a read it. I’ll wait here.

The problem with this is that it heavily relies on StructureMap (FWIW I love SM, but its overkill here), and doesn’t quite fit into the way ASP.net MVC does things. It also requires a lot of setup. I found this out almost 2 years ago now, and so SchoStack.Web was born. Over the coming posts I will go through what’s included in the library, but here is how you quickly get up and running with the default conventions (these represent the defaults that render lists, dropdowns, checkbox’s which are all ASP.net MVC compliant).

  1. Create new mvc project.
  2. install-package schostack.web
  3. Replace System.Web.Html namespaces in Views/web.config with the following
    <add namespace=”SchoStack.Web.Html” />
    <add namespace=”SchoStack.Web.Html.Form” />
  4. In Application_Start add:
    HtmlConventionFactory.Add(new DefaultHtmlConventions());
    HtmlConventionFactory.Add(new DataAnnotationHtmlConventions());
    HtmlConventionFactory.Add(new DataAnnotationValidationHtmlConventions());
  5. Thats it.

The following are the three main helpers used to generate all HTML based on your conventions.

@Html.Input(x=>x.Name)
@Html.Display(x=>x.Name)
@Html.Label(x=>x.Name)

All the usual MVC helpers are available as well like ValidationSummary and ValidationMessage.

I’ll try to cover the following things in upcoming posts, but if you have any questions or issues you can ask/raise them on github.

1. Creating your own conventions
2. Closer look at out of the box conventions
3. Validation (Server and Client validation using either FluentValidation and Data Annotations driven off the input model, not the viewmodel)
4. Foreach Loop Helpers
5. Url Generation and Routing

Adam

Posted in .NET | Tagged , , , | 2 Comments

NPoco 2.0

Today I finally published NPoco 2.0. It has been 6 months in the making and 13 pre-release versions. Thankyou to all who have raised bugs and used it in anger.

Most of the documentation has been published to the github wiki, however if you find something that is missing please let me know and I will endeavour to fix that.

There should be very limited breaking changes in 2.0, but if you have always just passed the connection string to the Database object then nothing will probably change. You can also specify the database type now, by passing ‘DatabaseType.SqlServer2012’ for example into the Database constructor. This will change the way the paging works in this case.

One of the latest features that has made it into 2.0 is the ability to use LINQ to specify simple where clauses, ordering and paging when fetching a single object. For example:

Database.FetchBy<User>(sql => sql.Where(x => x.Name == "Bob")
                                 .OrderBy(x => x.UserId)
                                 .Limit(10, 10))

There is also a FetchWhere<> which only takes a where expression.

Thanks again to all the contributors and if you have any issues be sure to raise them on github.

Adam

Posted in .NET | Tagged , | 7 Comments

NPoco, PostgreSQL and DateTimeOffset

Setting up PostgreSQL to run with NPoco is very simple. Firstly you need to install the Npgsql driver from NuGet. From the Package Manager Console type:

Install-Package Npgsql

Once this has been installed, we need to make a change to the web.config.

  <system.data>
    <DbProviderFactories>
      <add name="Npgsql Data Provider" 
           invariant="Npgsql" 
           description="Data Provider for PostgreSQL" 
           type="Npgsql.NpgsqlFactory, Npgsql" />
    </DbProviderFactories>
  </system.data>

Now we are setup to create our connection string. Here is a sample one.

<add name="Conn" 
    connectionString="Server=localhost;Database=db;User Id=user1;
Password=user1;Use Extended Types=true"
providerName="Npgsql"/>

The important part here is that you use the “User Extended Types=true”. This will return “TimeStampTz” types using Npgsql’s own type “NpgsqlTimeStampTZ” instead of a plain DateTime. Note: Npgsql does not automatically convert “timestamptz” types to DateTimeOffset, but using NPoco’s built in Mapper we can do this ourselves.

if (DestType == typeof(DateTimeOffset) || DestType == typeof(DateTimeOffset?)
    || DestType == typeof(DateTime) || DestType == typeof(DateTime?))
{
    return x =>
    {
        if (x is NpgsqlTimeStampTZ)
        {
            if (DestType == typeof(DateTime))
                return (DateTime)((NpgsqlTimeStampTZ)x);
            if (DestType == typeof(DateTime?))
                return (DateTime?)((NpgsqlTimeStampTZ)x);
            if (DestType == typeof(DateTimeOffset))
                return (DateTimeOffset)((NpgsqlTimeStampTZ)x);
            if (DestType == typeof(DateTimeOffset?))
                return (DateTimeOffset?)((NpgsqlTimeStampTZ)x);
        }
        if (x is NpgsqlTimeStamp)
        {
            if (DestType == typeof(DateTime))
                return (DateTime)((NpgsqlTimeStamp)x);
            if (DestType == typeof(DateTime?))
                return (DateTime?)((NpgsqlTimeStamp)x);
        }
        if (x is NpgsqlDate)
        {
            if (DestType == typeof(DateTime))
                return (DateTime)((NpgsqlDate)x);
            if (DestType == typeof(DateTime?))
                return (DateTime?)((NpgsqlDate)x);
        }

        return x;
    };
}

Here we check if the Destination Type is “DateTime” or “DateTimeOffset” and convert the Npgsql type into the CLR type. To wire this Mapper up we do the following:

var db = new Database("Conn") { Mapper = new Mapper() };

Usually I will create a static method that will create my Database so that I don’t have to write this every time. eg.

public class DbHelper
{
    public static IDatabase GetDb()
    {
        return new Database("Conn") { Mapper = new Mapper() };
    }
}

Now I can call DbHelper.GetDb() everywhere I need my database and the mapper will be plugged in appropriately.

As you can see, it is very easy to get up and running with NPoco and PostgreSQL. Please leave a comment if you have any questions.

Adam

Posted in .NET | Tagged , , , | Leave a comment

NPoco and MiniProfiler

Now that you have NPoco installed, being able to see exactly what queries were run and how they performed can make a huge difference in the overall performance of your site.

MiniProfiler is an awesome project by Sam Saffron that can easily profile your site and SQL queries. It is very easy to install and there is heaps of documentation at its home page.

To install MiniProfiler type Install-Package MiniProfiler in the Package Manager Console.

So how do you wire it up to NPoco? That’s also very easy.

public class TheDatabase : Database
{
    public TheDatabase(string connectionStringName) 
        : base(connectionStringName) {}

    public override IDbConnection OnConnectionOpened(IDbConnection conn)
    {
        return new ProfiledDbConnection((DbConnection) conn, MiniProfiler.Current);
    }
}

Create a class that inherits from Database and override the OnConnectionOpened returning a new ProfiledDbConnection. Now instantiate your new class instead of Database when running queries.

As long as you have the @MiniProfiler.RenderIncludes() in your layout and MiniProfiler is started each request as per the installation instructions then your queries should be displayed in the top corner.

Let me know how you get on, and thank Sam for his awesome project. 
Adam

Posted in .NET | Tagged , , | Leave a comment

Introducing NPoco

NPoco is the new library from my PetaPoco branch. If you don’t know what PetaPoco is, it is a MicroORM that enables quick and fast database access using raw sql. Why did I create this library? Well there was becoming too much divergence and it was always a pain that I couldn’t obtain my branch through NuGet.

Currently I’m working on V2 which is available now on NuGet if you use the –Pre console switch or by selecting “Include Prerelease” in Visual Studio. This does not include the T4 templates. I do take pull requests if you like this feature of PetaPoco and want it in NPoco.

There is also a fair bit of documentation about NPoco located at the Wiki. It includes basic documentation of how to use the library as well as the features only in NPoco. Some of these include.

  1. Query onto an existing object
  2. One-to-Many query helpers
  3. Mapping to Nested Objects query helpers
  4. Dictionary<string, object> and object[] queries for dynamic results
  5. Change tracking for updates
  6. Composite Primary Key support
  7. Queries that returns Multiple Result Sets
  8. Fluent Mappings including Conventional based mappings
  9. Version column support
  10. IDatabase interface
  11. Sql Templating

I’m still finalising all the documentation for these features, so if documentation doesn’t exist for one of the let me know and I will prioritise that feature.

The code is also available at Github, so go have a look, give it a try, send me a pull request and let me know how you get on.

Adam

** I did try to get my changes for PetaPoco merged back in to the main project, however I feel this will be better for my changes going forward.

Posted in .NET | Tagged | 3 Comments

PetaPoco – Convention Based Fluent Mapping

After using the Fluent Mapping’s I blogged about a few months ago, I started to see patterns occurring. My table names were always the pluralized type and the primary key was the Type name concatenated with “Id”. With all this repetition, I decided that conventional mappings were needed and that they should be overridable for the edge case.

Here is the simplest example.

protected void Application_Start()
{
    FluentMappingConfiguration.Scan(x =>
    {
        x.Assembly(typeof(MvcApplication).Assembly);
    });
}

This just tells the FluentMapping to scan the assembly with the type MvcApplication in it and any class it finds, define a mapping so that the object will can be persisted to PetaPoco. Now by itself this is pretty pointless because by default PetaPoco already does this for you on the fly. It will use the same defaults PetaPoco currently invokes which is

  1. TableName = Type name
  2. PrimaryKey = “ID”
  3. PrimaryKeyAutoIncrement = on

You would also not want add every type in your project either as most of them will not be used for database.

Ok, so we don’t want all the types, but we want to filter them by their namespace. All my DB models are in Models/Db, which has the namespace MvcApplication.Models.Db so we will use this as the filter.

protected void Application_Start()
{
    FluentMappingConfiguration.Scan(x =>
    {
        x.Assembly(typeof(MvcApplication).Assembly);
        x.IncludeTypes(type => type.Namespace == "MvcApplication.Models.Db");
    });
}

The IncludeTypes method takes in a lambda expression with the Type as the argument and returns a bool. This method can also be used to exclude types.

So with all our DB models now being mapped using defaults, lets change them. Say that all our tables have the prefix “t_” on them because our DB admin likes hungarian notation. He also wants the primary key to auto increment (identity column) and the column name to end with “Id”. Ok mister Db man….you’re the boss.

protected void Application_Start()
{
    FluentMappingConfiguration.Scan(x =>
    {
        x.Assembly(typeof(MvcApplication).Assembly);
        x.IncludeTypes(type => type.Namespace == "MvcApplication.Models.Db");
        x.TablesNamed(type => "t_" + type);
        x.PrimaryKeysNamed(type => type + "Id");
        x.PrimaryKeysAutoIncremented(type => true);
    });
}

Too easy.

Next we can configure how columns are mapped. By default the column name will map to the property name, but it doesn’t have to. Our Db man says all columns should have the “d_” prefix if the column is a DateTime property, and the rest should remain the same as the property name.

protected void Application_Start()
{
    FluentMappingConfiguration.Scan(x =>
    {
        x.Assembly(typeof(MvcApplication).Assembly);
        x.IncludeTypes(type => type.Namespace == "MvcApplication.Models.Db");
        x.TablesNamed(type => "t_" + type);
        x.PrimaryKeysNamed(type => type + "Id");
        x.PrimaryKeysAutoIncremented(type => true);
        x.Columns.Named(prop =>
            prop.PropertyType == typeof(DateTime) ? "d_" + prop.Name : prop.Name);
    });
}

I think you get the idea. You can also Ignore properties, set a Version columns and set a Result column.

There are also two extension methods that set some smart defaults. These are:

  1. Tables are the pluralized version of the Type name
  2. PrimaryKeys are the Type name concatenated with “Id”
  3. All complex properties (classes) are ignored

These can be set simply like this.

protected void Application_Start()
{
    FluentMappingConfiguration.Scan(x =>
    {
        x.Assembly(typeof(MvcApplication).Assembly);
        x.IncludeTypes(type => type.Namespace == "MvcApplication.Models.Db");
        x.WithSmartConventions();
    });
}

You can create your own extension methods as well. They are extremely easy. Check out the code for the WithSmartConventions to see how easy it is.

There is one last thing. Conventions are great, but if you can’t override them then they’re pretty useless, because there is always an exception to the rule. Mr Db man comes to me and says that there is one table which is used by 5 systems and does not play by our conventions we have setup. The Type name is Abc, but the table name needs to be “Abcies” not “Abcs”, and the primary key is “MyAbcId”. He also doesn’t want to map the Name column.

protected void Application_Start()
{
    FluentMappingConfiguration.Scan(x =>
    {
        x.Assembly(typeof(MvcApplication).Assembly);
        x.IncludeTypes(type => type.Namespace == "MvcApplication.Models.Db");
        x.TablesNamed(type => "t_" + type);
        x.PrimaryKeysNamed(type => type + "Id");
        x.PrimaryKeysAutoIncremented(type => true);
        x.Columns.Named(prop =>
            prop.PropertyType == typeof(DateTime) ? "d_" + prop.Name : prop.Name);

        x.OverrideMappingsWith(new MappingOverrides());
    });
}

public class MappingOverrides : Mappings
{
    public MappingOverrides()
    {
        For<Abc>().TableName("Abcies")
                    .PrimaryKey("MyAbcId")
                    .Columns(x => x.Column(y=>y.Name).Ignore());
    }
}

Any mappings defined in our mapping overrides will be overlayed on top of our conventions. We covered 99% of our mappings with these conventions but still needed to manually configure one table.

I hope you can see how powerful this can be, especially when you’re got more than 100 tables.

This is currently only available on my branch, which can be found https://github.com/schotime/PetaPoco and downloaded Schotime-PetaPoco-4.0.3.11.zip.

Let me know how you get on or if you have any issues.

Adam

Posted in .NET | Tagged , , , | 3 Comments

PetaPoco – Multiple Result Sets

The other day I got enough time to put the ability to fetch multiple result sets into PetaPoco. Its relatively simple to use and works like this.

Given these two classes with corresponding tables:

    public class Table1
    {
        public string Name { get; set; }
    }
    public class Table2
    {
        public string Col1 { get; set; }
        public int Col2 { get; set; }  
    }

Both results can be retrieved in one call to the database like so:

    var result = db.FetchMultiple<Table1, Table2>(
        "select name from table1;" +                                      "select col1,col2 from table2");

    var table1list = result.Item1;  //List<Table1>
    var table2list = result.Item2;  //List<Table2>

where result here is a Tuple<List<Table1>, List<Table2>>.

Here the semi-colon represents the separation of the two queries. This is the MS SQL server syntax so please check your database specific syntax to use multiple result sets.

If you don’t like returning a Tuple from this query you can provide your own callback to the FetchMultiple method. The callback for the above query would be of type. Func<List<Table1>, List<Table2>, ReturnClass>

The feature is currently only available in my master branch where you can find the download package here https://github.com/schotime/PetaPoco/downloads or directly https://github.com/downloads/schotime/PetaPoco/Schotime-PetaPoco-4.0.3.5.zip.

Hope you find it useful.

Posted in .NET | Tagged , | 1 Comment