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