Search

Categories

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Send mail to the author(s) E-mail

# Thursday, 06 October 2011

Whilst getting my head around Rob Conery’s Massive ORM, I needed to figure out what the dynamic keyword is in C#4:

class Program {
        static void Main(string[] args) {
            Calculator calc = new Calculator();
            int sum = calc.Add(1, 2);
            Console.WriteLine(sum);
            Console.ReadLine();
        }
    }

    public class Calculator {
        public int Add(int a, int b) {
            return a+b;
        }
    }

creation of an object, invokation of a method, and the collection of a return value

Var

Can represent any type that can be determined at compile time.

class Program {
        static void Main(string[] args) {
            //int
            var i = 5;
            //string
            var x = "hello";
            //int[]
            var y = new[] { 0, 1, 2, 3 };
            //anonymous type
            var anon = new { Name = "Dave", Age = 34 };
            //List<int>
            var list = new List<int>();
            //Calculator
            var calc = new Calculator();
            int sum = calc.Add(1, 2);
            Console.WriteLine(sum);
            Console.ReadLine();
        }
    }

    public class Calculator {
        public int Add(int a, int b) {
            return a+b;
        }
    }

 

Dynamic

at runtime you get the type

class Program {
        static void Main(string[] args) {
            //int
            dynamic i = 5;
            //string
            dynamic x = "hello";
            //int[]
            dynamic y = new[] { 0, 2, 3, 4 };
            //anonymous type
            dynamic anon = new {Name="Dave", Age=34};

            Console.WriteLine(i + x);

            dynamic calc = new Calculator();
            //don't get intellisense when press .
            int sum = calc.Add(1, 2);
            Console.WriteLine(sum);
            Console.ReadLine();
        }
    }

    public class Calculator {
        public int Add(int a, int b) {
            return a+b;
        }
    }

 

ExpandoObject and TryInvokeMember

An object whose members can be dynamically added and removed at runtime.

image

can’t do anything with the expandoObject though!

image

Much better.  Saying to the compiler – “Playing with a different set of rules”.

Why doesn’t Product need to by Dynamic?

           dynamic tbl = new DynamicModel("Northwind", "Products", "ProductID");
            //tbl.Single returns a dynamic of type ExpandoOnject, not an ExpandoObject.
            //acutally an IEnumrable<dynamic>.FirstOfDefault()
            var product = tbl.Single();

We don’t need product to be dynamic here as tbl.Single returns a dynamic of type Expando.

tbl has to be dynamic otherwise the TryInvokeMember wont work, as there is no method called Single.

| | # 

While working on the TricksController of my project, I came across .All(), but then .FindBy which needs dynamics.  So now is the time to have a good look at massive and understand:

  • what is going on under the hood of massive
  • what can I strip out to make even simple

First up, is to do the simplest possible thing ie console app.

class Program {
        static void Main(string[] args) {
            Products _tbl = new Products();
            IEnumerable<dynamic> products = _tbl.All();

            foreach (var item in products) {
                Console.WriteLine(item.ProductName);
            }

            Console.WriteLine("number of products is: " + products.Count());
            Console.ReadLine();
        }
    }

    public class Products : DynamicModel {
        public Products() : base("Northwind", "Products", "ProductID") {
        }
    }

This shows all 77products (checked against the db).

namespace Massive3 {
    public class DynamicModel : DynamicObject {
        DbProviderFactory _factory;
        string _connectionString;
        public string TableName { get; set; }
        public string PrimaryKeyField { get; set; }

        public DynamicModel(string connectionStringName, string tableName, string primaryKeyField) {
            TableName = tableName;
            PrimaryKeyField = primaryKeyField;
            string _providerName = "System.Data.SqlClient";
            _factory = DbProviderFactories.GetFactory(_providerName);
            _connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
        }

        public IEnumerable<dynamic> All(string where = "", string orderBy = "", int limit = 0, string columns = "*") {
            string sql = BuildSelect(where, orderBy, limit);
            return Query(string.Format(sql, columns, TableName));
        }

        private static string BuildSelect(string where, string orderBy, int limit) {
            string sql = limit > 0 ? "SELECT TOP " + limit + " {0} FROM {1} " : "SELECT {0} FROM {1} ";
            if (!string.IsNullOrEmpty(where))
                sql += where.Trim().StartsWith("where", StringComparison.OrdinalIgnoreCase) ? where : "WHERE " + where;
            if (!String.IsNullOrEmpty(orderBy))
                sql += orderBy.Trim().StartsWith("order by", StringComparison.OrdinalIgnoreCase) ? orderBy : " ORDER BY " + orderBy;
            return sql;
        }

        public IEnumerable<dynamic> Query(string sql) {
            using (var conn = OpenConnection()) {
                var rdr = CreateCommand(sql, conn).ExecuteReader();
                while (rdr.Read()) {
                    yield return rdr.RecordToExpando(); ;
                }
            }
        }

        public DbConnection OpenConnection() {
            var result = _factory.CreateConnection();
            result.ConnectionString = _connectionString;
            result.Open();
            return result;
        }

        DbCommand CreateCommand(string sql, DbConnection conn) {
            var result = _factory.CreateCommand();
            result.Connection = conn;
            result.CommandText = sql;
            return result;
        }
    }

    public static class ObjectExtensions {
        public static dynamic RecordToExpando(this IDataReader rdr) {
            dynamic e = new ExpandoObject();
            var d = e as IDictionary<string, object>;
            for (int i = 0; i < rdr.FieldCount; i++)
                d.Add(rdr.GetName(i), DBNull.Value.Equals(rdr[i]) ? null : rdr[i]);
            return e;
        }
    }
}

Refactored to a very simple tool.. only All() and Query() work.

Constructor - Optional Parameters (C#4)

however the 2nd, 3rd, and 4th parameters calling the base constructor are optional:

class Program {
        static void Main(string[] args) {
            Products _tbl = new Products();
            IEnumerable<dynamic> products = _tbl.All();

            foreach (var item in products) {
                Console.WriteLine(item.ProductName);
            }

            Console.WriteLine("number of products is: " + products.Count());
            Console.ReadLine();
        }
    }

    public class Products : DynamicModel {
        public Products() : base("Northwind") {
        }
    }

So this still works.. how does it know to get the Products table and the appropriate ID?

public DynamicModel(string connectionStringName, string tableName = "", string primaryKeyField = "", string descriptorField = "") {
            //if no tableName passed, use the calling classes name
            TableName = tableName == "" ? this.GetType().Name : tableName;
            //if no primaryKeyField passed, use ID
            PrimaryKeyField = string.IsNullOrEmpty(primaryKeyField) ? "ID" : primaryKeyField;
            string _providerName = "System.Data.SqlClient";
            _factory = DbProviderFactories.GetFactory(_providerName);
            ConnectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
        }

DbProviderFactory is the stuff needed to connect to a specific database.  This is a way of not tying into MSSQL

ALL() – Named Parameters

/// <summary>
        /// Returns all records complying with the passed-in WHERE clause and arguments,
        /// ordered as specified, limited (TOP) by limit.
        /// </summary>
        public virtual IEnumerable<dynamic> All(string where = "", string orderBy = "", int limit = 0, string columns = "*", params object[] args) {
            string sql = BuildSelect(where, orderBy, limit);
            return Query(string.Format(sql, columns, TableName), args);
        }

All is virtual, meaning that I could override it in Products if I wanted to.

All parameters are optional so I could call:

IEnumerable<dynamic> products = _tbl.All(where: "ProductName like '%s%'",
                                                    orderBy: "ProductName",
                                                    limit: 5,
                                                    columns: "ProductName, UnitPrice");

or go directly to the method that All calls ie Query:

IEnumerable<dynamic> products2 = _tbl.Query(@"SELECT * FROM Products");

Query(sql, args)

In order to understand what is going on, am going to strip down Massive to only do what I need:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Dynamic;
using System.Data.Common;
using System.Configuration;
using System.Data;

namespace Massive3 {
    public static class ObjectExtensions {
        public static void AddParams(this DbCommand cmd, params object[] args) {
            foreach (var item in args) {
                AddParam(cmd, item);
            }
        }
        public static void AddParam(this DbCommand cmd, object item) {
            var p = cmd.CreateParameter();
            p.ParameterName = string.Format("@{0}", cmd.Parameters.Count);
            if (item == null) {
                p.Value = DBNull.Value;
            }
            else {
                if (item.GetType() == typeof(Guid)) {
                    p.Value = item.ToString();
                    p.DbType = DbType.String;
                    p.Size = 4000;
                }
                else if (item.GetType() == typeof(ExpandoObject)) {
                    var d = (IDictionary<string, object>)item;
                    p.Value = d.Values.FirstOrDefault();
                }
                else {
                    p.Value = item;
                }
                if (item.GetType() == typeof(string))
                    p.Size = ((string)item).Length > 4000 ? -1 : 4000;
            }
            cmd.Parameters.Add(p);
        }
        public static dynamic RecordToExpando(this IDataReader rdr) {
            dynamic e = new ExpandoObject();
            var d = e as IDictionary<string, object>;
            for (int i = 0; i < rdr.FieldCount; i++)
                d.Add(rdr.GetName(i), DBNull.Value.Equals(rdr[i]) ? null : rdr[i]);
            return e;
        }
    }

    //DynamicObject is .NET
    public class DynamicModel : DynamicObject {
        DbProviderFactory _factory;
        //strange naming.. would expect _connectionString
        string ConnectionString;
        //auto-implemented properties which can be overridden
        public virtual string TableName { get; set; }
        public virtual string PrimaryKeyField { get; set; }
        
        public DynamicModel(string connectionStringName, string tableName = "", string primaryKeyField = "", string descriptorField = "") {
            //if no tableName passed, use the calling classes name
            TableName = tableName == "" ? this.GetType().Name : tableName;
            //if no primaryKeyField passed, use ID
            PrimaryKeyField = string.IsNullOrEmpty(primaryKeyField) ? "ID" : primaryKeyField;
            string _providerName = "System.Data.SqlClient";
            //stuff needed to connect to a database
            _factory = DbProviderFactories.GetFactory(_providerName);
            //connectionStringName has to be passed into this constructor
            ConnectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
        }

        public virtual IEnumerable<dynamic> All(string where = "", string orderBy = "", int limit = 0, string columns = "*", params object[] args) {
            string sql = BuildSelect(where, orderBy, limit);
            return Query(string.Format(sql, columns, TableName), args);
        }

        private static string BuildSelect(string where, string orderBy, int limit) {
            string sql = limit > 0 ? "SELECT TOP " + limit + " {0} FROM {1} " : "SELECT {0} FROM {1} ";
            if (!string.IsNullOrEmpty(where))
                sql += where.Trim().StartsWith("where", StringComparison.OrdinalIgnoreCase) ? where : "WHERE " + where;
            if (!String.IsNullOrEmpty(orderBy))
                sql += orderBy.Trim().StartsWith("order by", StringComparison.OrdinalIgnoreCase) ? orderBy : " ORDER BY " + orderBy;
            return sql;
        }

        public virtual IEnumerable<dynamic> Query(string sql, params object[] args) {
            using (var conn = OpenConnection()) {
                //in my case rdr is a SqlDataReader
                var rdr = CreateCommand(sql, conn, args).ExecuteReader();
                //read results and put into an Expando taking into consideration nulls
                while (rdr.Read()) {
                    yield return rdr.RecordToExpando(); ;
                }
            }
        }

        public virtual DbConnection OpenConnection() {
            var result = _factory.CreateConnection();
            result.ConnectionString = ConnectionString;
            result.Open();
            return result;
        }

        DbCommand CreateCommand(string sql, DbConnection conn, params object[] args) {
            var result = _factory.CreateCommand();
            result.Connection = conn;
            result.CommandText = sql;
            if (args.Length > 0)
                result.AddParams(args);
            return result;
        }
    }
}

Even Simpler

class Program {
        static void Main(string[] args) {
            DynamicModel _tbl = new DynamicModel("Northwind", "Products", "ProductID");
            IEnumerable<dynamic> products = _tbl.Query(@"SELECT * FROM Products").ToList();

            foreach (var item in products) {
                Console.WriteLine(item.ProductName);
            }

            Console.WriteLine("number of products is: " + products.Count());
            Console.ReadLine();
        }
    }

calling Massive directly (as opposed to the Model - Product calling it)

Notice we’re not using dynamic here, so _tbl.Single() won’t work (as need _tbl to be dynamic)

Taken out everything except All().. so above console app will still work.

Simplifying even further – have taken out params.

So this code can do SELECT

  • Specific columns
  • Where
  • OrderBy
  • Top

and return an IEnumerable<dynamic>, then dynamic being an ExpandoObject

Deferred Execution

Because Query returns an IEnumerable<dynamic> it is only executed when iterated.

Code only executes Query method when iterating over products in foreach.

Heart of Massive

Query returns an IEnumerable<dynamic>.. which is a IEnumerable<System.Dynamic.ExpandoObject>

each ExpandoObject in this case has

  • ProductName
  • UnitPrice
  • etc..

TryInvokeMember –Useful Query Tool

Single doesn’t exist in DynamicModel (Massive).  So DynamicObject’s TryInvokeMember is called to kick start the dynamic query stuff.

dynamic tbl = new DynamicModel("Northwind", "Products", "ProductID");
            var product = tbl.Single();

can use:

  • FindBy
  • Last (descending)
  • Single
  • First (ascending)
  • Count
  • Sum
  • Max
  • Min
  • Avg
| | # 
# Monday, 03 October 2011

Works well with Azure and SQL now.

Out of the box MVC3 in latest tools update (as of 4th Oct 2011) we get:

  <connectionStrings>
    <add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"
      providerName="System.Data.SqlClient" />
    <add name="DefaultConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=aspnet_44a5afb3d91849c4b1eb154a09f03a95;Integrated Security=True;MultipleActiveResultSets=True"
      providerName="System.Data.SqlClient" />
  </connectionStrings>

out of the box it needs both connections.

http://www.hanselman.com/blog/IntroducingSystemWebProvidersASPNETUniversalProvidersForSessionMembershipRolesAndUserProfileOnSQLCompactAndSQLAzure.aspx

The AspNetSql*** looks like it is legacy.  If we comment out, then everything can go through the new DefaultProviders, which give easy access to Azure.

Here is my web.config for reference:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <system.diagnostics>
    <trace>
      <listeners>
        <add type="Microsoft.WindowsAzure.Diagnostics.DiagnosticMonitorTraceListener, Microsoft.WindowsAzure.Diagnostics, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" name="AzureDiagnostics">
          <filter type="" />
        </add>
      </listeners>
    </trace>
  </system.diagnostics>
  
  <connectionStrings>
    <!--<add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"
      providerName="System.Data.SqlClient" />-->
    <!--<add name="DefaultConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=aspnet_44a5afb3d91849c4b1eb154a09f03a95;Integrated Security=True;MultipleActiveResultSets=True"
      providerName="System.Data.SqlClient" />-->

    <!-- local -->
    <add name="DefaultConnection" connectionString="Data Source=.\;Initial Catalog=PubTricks_Dev;Integrated Security=True;MultipleActiveResultSets=True"
      providerName="System.Data.SqlClient" />

    <!-- Azure -->
    <!--<add name="DefaultConnection" connectionString="Data Source=xxx.database.windows.net;Initial Catalog=PubTricks;User ID=dave;Password=xxx;Trusted_Connection=False;Encrypt=True;MultipleActiveResultSets=True"
      providerName="System.Data.SqlClient" />-->
    
  </connectionStrings>
  
  
  <appSettings>
    <add key="webpages:Version" value="1.0.0.0" />
    <add key="ClientValidationEnabled" value="true" />
    <add key="UnobtrusiveJavaScriptEnabled" value="true" />
  </appSettings>
  <system.web>
    <compilation debug="true" targetFramework="4.0">
      <assemblies>
        <add assembly="System.Web.Abstractions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
        <add assembly="System.Web.Helpers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
        <add assembly="System.Web.Routing, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
        <add assembly="System.Web.Mvc, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
        <add assembly="System.Web.WebPages, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
      </assemblies>
    </compilation>

    <customErrors mode="Off"></customErrors>
    
    <authentication mode="Forms">
      <forms loginUrl="~/Account/LogOn" timeout="2880" />
    </authentication>
    
    <membership defaultProvider="DefaultMembershipProvider">
      <providers>
        <clear />
        <!--<add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/" />-->
        <add name="DefaultMembershipProvider" type="System.Web.Providers.DefaultMembershipProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/" />
      </providers>
    </membership>
    
    <profile defaultProvider="DefaultProfileProvider">
      <providers>
        <clear />
        <!--<add name="AspNetSqlProfileProvider" type="System.Web.Profile.SqlProfileProvider" connectionStringName="ApplicationServices" applicationName="/" />-->
        <add name="DefaultProfileProvider" type="System.Web.Providers.DefaultProfileProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" applicationName="/" />
      </providers>
    </profile>
    
    <roleManager enabled="true" defaultProvider="DefaultRoleProvider">
      <providers>
        <clear />
        <!--<add connectionStringName="ApplicationServices" applicationName="/"  name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider" />-->
        
        <add applicationName="/" name="AspNetWindowsTokenRoleProvider"  type="System.Web.Security.WindowsTokenRoleProvider" />
        
        <add connectionStringName="DefaultConnection" applicationName="/" name="DefaultRoleProvider" type="System.Web.Providers.DefaultRoleProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
      </providers>
    </roleManager>
    
    <pages>
      <namespaces>
        <add namespace="System.Web.Helpers" />
        <add namespace="System.Web.Mvc" />
        <add namespace="System.Web.Mvc.Ajax" />
        <add namespace="System.Web.Mvc.Html" />
        <add namespace="System.Web.Routing" />
        <add namespace="System.Web.WebPages" />
      </namespaces>
    </pages>
    <sessionState mode="InProc" customProvider="DefaultSessionProvider">
      <providers>
        <add name="DefaultSessionProvider" type="System.Web.Providers.DefaultSessionStateProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" applicationName="/" />
      </providers>
    </sessionState>
  </system.web>
  <system.webServer>
    <validation validateIntegratedModeConfiguration="false" />
    <modules runAllManagedModulesForAllRequests="true" />
  </system.webServer>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-2.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

and web.release.config

<?xml version="1.0"?>
<configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">

  <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=xxx.database.windows.net;Initial Catalog=PubTricks;User ID=dave;Password=xxx;Trusted_Connection=False;Encrypt=True;MultipleActiveResultSets=True"
      xdt:Transform="SetAttributes" xdt:Locator="Match(name)"/>
  </connectionStrings>
  
  <system.web>
    <!-- turn this on in prod - keep commented out for useful errors on live-->
    <!--<compilation xdt:Transform="RemoveAttributes(debug)" />-->

  </system.web>
</configuration>

I use the commented out trick in web.config for Azure, so can easily use the website admin tool to admin the live sites users.

image

| | #