public async Task ReturnsJson([CombinatorialValues("stream", "writer", "mapper", "command")] string client,
[CombinatorialValues(1, 50, 10000)] string top,
[CombinatorialValues("auto", "path")] string mode1,
[CombinatorialValues(",include_null_values", ",root('test')", ",root")] string mode2,
[CombinatorialValues(true, false)] bool useCommand,
[CombinatorialValues(true, false)] bool useAsync,
[CombinatorialValues("1", null)] string defaultValue,
[CombinatorialValues("[/]", "{\"a\":/}", "/")] string wrapper,
[CombinatorialValues("", "test")] string sessionContext1,
[CombinatorialValues("TEST", null)] string sessionContext2
)
{
// Arrange
bool isExceptionThrown = false;
var pipe = new Belgrade.SqlClient.SqlDb.QueryPipe(Util.Settings.MasterConnectionString)
.AddContextVariable("v1", () => sessionContext1)
.AddContextVariable("v2", () => sessionContext2)
.OnError(ex => { isExceptionThrown = true; Console.WriteLine(ex); });
var mapper = new Belgrade.SqlClient.SqlDb.QueryMapper(Util.Settings.MasterConnectionString)
.AddContextVariable("v1", () => sessionContext1)
.AddContextVariable("v2", () => sessionContext2)
.OnError(ex => { isExceptionThrown = true; Console.WriteLine(ex); });
var command = new Belgrade.SqlClient.SqlDb.Command(Util.Settings.MasterConnectionString)
.AddContextVariable("v1", () => sessionContext1)
.AddContextVariable("v2", () => sessionContext2)
.OnError(ex => { isExceptionThrown = true; Console.WriteLine(ex); });
var sql = @"select top " + top + @" v1 = cast(SESSION_CONTEXT(N'v1') as varchar(500)),
v2 = cast(SESSION_CONTEXT(N'v2') as varchar(500)),
o.object_id tid, o.name t_name, o.schema_id, o.type t, o.type_desc, o.create_date cd, o.modify_date md,
c.*
from sys.objects o, sys.columns c for json " + mode1 + mode2;
string json = "INVALID JSON";
var pair = wrapper.Split('/');
string prefix = pair[0];
string suffix = pair[1];
Task t = null;
// Action
if (client == "stream")
{
using (MemoryStream ms = new MemoryStream())
{
if (useCommand)
{
t = pipe.Sql(new SqlCommand(sql)).Stream(ms);
}
else
{
t = pipe.Sql(sql).Stream(ms);
}
if (useAsync)
{
await t;
}
else
{
t.Wait();
}
ms.Position = 0;
json = new StreamReader(ms).ReadToEnd();
}
}
else if (client == "writer")
{
using (var sw = new StringWriter())
{
if (useCommand)
{
t = pipe.Sql(new SqlCommand(sql)).Stream(sw, new Options()
{
Prefix = prefix, DefaultOutput = defaultValue, Suffix = suffix
});
}
else
{
t = pipe.Sql(sql).Stream(sw, new Options()
{
Prefix = prefix, DefaultOutput = defaultValue, Suffix = suffix
});
}
if (useAsync)
{
await t;
}
else
{
t.Wait();
}
json = sw.ToString();
}
}
else if (client == "command")
{
using (MemoryStream ms = new MemoryStream())
{
if (useCommand)
{
t = command.Sql(new SqlCommand(sql)).Stream(ms, "");
}
else
{
t = command.Sql(sql).Stream(ms, "");
}
if (useAsync)
{
await t;
}
else
{
t.Wait();
}
ms.Position = 0;
json = new StreamReader(ms).ReadToEnd();
}
}
else
{
if (!useAsync)
{
return;
}
if (useCommand)
{
json = await mapper.GetString(new SqlCommand(sql));
}
else
{
json = await mapper.GetString(sql);
}
}
// Assert
if (isExceptionThrown)
{
return;
}
AssertEx.IsValidJson(json);
if (json.StartsWith("["))
{
string v1 = null;
var b = JArray.Parse(json).SelectTokens("..v1");
var f = b.First();
v1 = (string)f;
Assert.Equal(sessionContext1, v1);
}
else
{
string v1 = null;
var a = JObject.Parse(json);
var b = a.SelectTokens("..v1");
var f = b.First();
v1 = (string)f;
Assert.Equal(sessionContext1, v1);
}
}
public async Task RunAsync(int iterations)
{
using (var connection = GetOpenConnection())
{
#pragma warning disable IDE0017 // Simplify object initialization
#pragma warning disable RCS1121 // Use [] instead of calling 'First'.
var tests = new Tests();
// Entity Framework Core
Try(() =>
{
var entityContext = new EFCoreContext(ConnectionString);
tests.Add(id => entityContext.Posts.First(p => p.Id == id), "Entity Framework Core");
var entityContext2 = new EFCoreContext(ConnectionString);
tests.Add(id => entityContext2.Posts.FromSqlRaw("select * from Posts where Id = {0}", id).First(), "Entity Framework Core: FromSql");
var entityContext3 = new EFCoreContext(ConnectionString);
tests.Add(id => entityContext3.Posts.AsNoTracking().First(p => p.Id == id), "Entity Framework Core: No Tracking");
}, "Entity Framework Core");
// Dapper
Try(() =>
{
var mapperConnection = GetOpenConnection();
tests.Add(id => mapperConnection.Query <Post>("select * from Posts where Id = @Id", new { Id = id }, buffered: true).First(), "Dapper: Query (buffered)");
tests.Add(id => mapperConnection.Query <Post>("select * from Posts where Id = @Id", new { Id = id }, buffered: false).First(), "Dapper: Query (non-buffered)");
tests.Add(id => mapperConnection.QueryFirstOrDefault <Post>("select * from Posts where Id = @Id", new { Id = id }), "Dapper: QueryFirstOrDefault");
var mapperConnection2 = GetOpenConnection();
tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }, buffered: true).First(), "Dapper: Dynamic Query (buffered)");
tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }, buffered: false).First(), "Dapper: Dynamic Query (non-buffered)");
tests.Add(id => mapperConnection2.QueryFirstOrDefault("select * from Posts where Id = @Id", new { Id = id }), "Dapper: Dynamic QueryFirstOrDefault");
// dapper.contrib
var mapperConnection3 = GetOpenConnection();
tests.Add(id => mapperConnection3.Get <Post>(id), "Dapper.Contrib");
}, "Dapper");
// Massive
Try(() =>
{
var massiveModel = new DynamicModel(ConnectionString);
var massiveConnection = GetOpenConnection();
tests.Add(id => massiveModel.Query("select * from Posts where Id = @0", massiveConnection, id).First(), "Massive: Dynamic ORM Query");
}, "Massive");
// PetaPoco
Try(() =>
{
// PetaPoco test with all default options
var petapoco = new PetaPoco.Database(ConnectionString, "System.Data.SqlClient");
petapoco.OpenSharedConnection();
tests.Add(id => petapoco.Fetch <Post>("SELECT * from Posts where Id=@0", id).First(), "PetaPoco: Normal");
// PetaPoco with some "smart" functionality disabled
var petapocoFast = new PetaPoco.Database(ConnectionString, "System.Data.SqlClient");
petapocoFast.OpenSharedConnection();
petapocoFast.EnableAutoSelect = false;
petapocoFast.EnableNamedParams = false;
petapocoFast.ForceDateTimesToUtc = false;
tests.Add(id => petapocoFast.Fetch <Post>("SELECT * from Posts where Id=@0", id).First(), "PetaPoco: Fast");
}, "PetaPoco");
// NHibernate
Try(() =>
{
var nhSession1 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession1.CreateSQLQuery("select * from Posts where Id = :id")
.SetInt32("id", id)
.List(), "NHibernate: SQL");
var nhSession2 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession2.CreateQuery("from Post as p where p.Id = :id")
.SetInt32("id", id)
.List(), "NHibernate: HQL");
var nhSession3 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession3.CreateCriteria <Post>()
.Add(Restrictions.IdEq(id))
.List(), "NHibernate: Criteria");
var nhSession4 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession4
.Query <Post>()
.First(p => p.Id == id), "NHibernate: LINQ");
var nhSession5 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession5.Get <Post>(id), "NHibernate: Session.Get");
}, "NHibernate");
// Belgrade
Try(() =>
{
var query = new Belgrade.SqlClient.SqlDb.QueryMapper(ConnectionString);
tests.AsyncAdd(id => query.Sql("SELECT TOP 1 * FROM Posts WHERE Id = @Id").Param("Id", id).Map(
reader =>
{
var post = new Post();
post.Id = reader.GetInt32(0);
post.Text = reader.GetString(1);
post.CreationDate = reader.GetDateTime(2);
post.LastChangeDate = reader.GetDateTime(3);
post.Counter1 = reader.IsDBNull(4) ? null : (int?)reader.GetInt32(4);
post.Counter2 = reader.IsDBNull(5) ? null : (int?)reader.GetInt32(5);
post.Counter3 = reader.IsDBNull(6) ? null : (int?)reader.GetInt32(6);
post.Counter4 = reader.IsDBNull(7) ? null : (int?)reader.GetInt32(7);
post.Counter5 = reader.IsDBNull(8) ? null : (int?)reader.GetInt32(8);
post.Counter6 = reader.IsDBNull(9) ? null : (int?)reader.GetInt32(9);
post.Counter7 = reader.IsDBNull(10) ? null : (int?)reader.GetInt32(10);
post.Counter8 = reader.IsDBNull(11) ? null : (int?)reader.GetInt32(11);
post.Counter9 = reader.IsDBNull(12) ? null : (int?)reader.GetInt32(12);
}), "Belgrade Sql Client");
}, "Belgrade Sql Client");
//ServiceStack's OrmLite:
Try(() =>
{
var dbFactory = new OrmLiteConnectionFactory(ConnectionString, SqlServerDialect.Provider);
var db = dbFactory.Open();
tests.Add(id => db.SingleById <Post>(id), "ServiceStack.OrmLite: SingleById");
}, "ServiceStack.OrmLite");
// Hand Coded
Try(() =>
{
var postCommand = new SqlCommand()
{
Connection = connection,
CommandText = @"select Id, [Text], [CreationDate], LastChangeDate,
Counter1,Counter2,Counter3,Counter4,Counter5,Counter6,Counter7,Counter8,Counter9 from Posts where Id = @Id"
};
var idParam = postCommand.Parameters.Add("@Id", SqlDbType.Int);
tests.Add(id =>
{
idParam.Value = id;
using (var reader = postCommand.ExecuteReader())
{
reader.Read();
var post = new Post();
post.Id = reader.GetInt32(0);
post.Text = reader.GetNullableString(1);
post.CreationDate = reader.GetDateTime(2);
post.LastChangeDate = reader.GetDateTime(3);
post.Counter1 = reader.GetNullableValue <int>(4);
post.Counter2 = reader.GetNullableValue <int>(5);
post.Counter3 = reader.GetNullableValue <int>(6);
post.Counter4 = reader.GetNullableValue <int>(7);
post.Counter5 = reader.GetNullableValue <int>(8);
post.Counter6 = reader.GetNullableValue <int>(9);
post.Counter7 = reader.GetNullableValue <int>(10);
post.Counter8 = reader.GetNullableValue <int>(11);
post.Counter9 = reader.GetNullableValue <int>(12);
}
}, "Hand Coded");
var table = new DataTable
{
Columns =
{
{ "Id", typeof(int) },
{ "Text", typeof(string) },
{ "CreationDate", typeof(DateTime) },
{ "LastChangeDate", typeof(DateTime) },
{ "Counter1", typeof(int) },
{ "Counter2", typeof(int) },
{ "Counter3", typeof(int) },
{ "Counter4", typeof(int) },
{ "Counter5", typeof(int) },
{ "Counter6", typeof(int) },
{ "Counter7", typeof(int) },
{ "Counter8", typeof(int) },
{ "Counter9", typeof(int) },
}
};
tests.Add(id =>
{
idParam.Value = id;
object[] values = new object[13];
using (var reader = postCommand.ExecuteReader())
{
reader.Read();
reader.GetValues(values);
table.Rows.Add(values);
}
}, "DataTable via IDataReader.GetValues");
}, "Hand Coded");
// DevExpress.XPO
Try(() =>
{
IDataLayer dataLayer = XpoDefault.GetDataLayer(connection, DevExpress.Xpo.DB.AutoCreateOption.SchemaAlreadyExists);
dataLayer.Dictionary.GetDataStoreSchema(typeof(Xpo.Post));
UnitOfWork session = new UnitOfWork(dataLayer, dataLayer);
session.IdentityMapBehavior = IdentityMapBehavior.Strong;
session.TypesManager.EnsureIsTypedObjectValid();
tests.Add(id => session.Query <Xpo.Post>().First(p => p.Id == id), "DevExpress.XPO: Query<T>");
tests.Add(id => session.GetObjectByKey <Xpo.Post>(id, true), "DevExpress.XPO: GetObjectByKey<T>");
tests.Add(id =>
{
CriteriaOperator findCriteria = new BinaryOperator()
{
OperatorType = BinaryOperatorType.Equal,
LeftOperand = new OperandProperty("Id"),
RightOperand = new ConstantValue(id)
};
session.FindObject <Xpo.Post>(findCriteria);
}, "DevExpress.XPO: FindObject<T>");
}, "DevExpress.XPO");
// Entity Framework
Try(() =>
{
var entityContext = new EFContext(connection);
tests.Add(id => entityContext.Posts.First(p => p.Id == id), "Entity Framework");
var entityContext2 = new EFContext(connection);
tests.Add(id => entityContext2.Database.SqlQuery <Post>("select * from Posts where Id = {0}", id).First(), "Entity Framework: SqlQuery");
var entityContext3 = new EFContext(connection);
tests.Add(id => entityContext3.Posts.AsNoTracking().First(p => p.Id == id), "Entity Framework: No Tracking");
}, "Entity Framework");
#if NET4X
// Linq2SQL
Try(() =>
{
var l2scontext1 = GetL2SContext(connection);
tests.Add(id => l2scontext1.Posts.First(p => p.Id == id), "Linq2Sql: Normal");
var l2scontext2 = GetL2SContext(connection);
var compiledGetPost = CompiledQuery.Compile((Linq2Sql.DataClassesDataContext ctx, int id) => ctx.Posts.First(p => p.Id == id));
tests.Add(id => compiledGetPost(l2scontext2, id), "Linq2Sql: Compiled");
var l2scontext3 = GetL2SContext(connection);
tests.Add(id => l2scontext3.ExecuteQuery <Post>("select * from Posts where Id = {0}", id).First(), "Linq2Sql: ExecuteQuery");
}, "LINQ-to-SQL");
// Dashing
Try(() =>
{
var config = new DashingConfiguration();
var database = new SqlDatabase(config, ConnectionString);
var session = database.BeginTransactionLessSession(GetOpenConnection());
tests.Add(id => session.Get <Dashing.Post>(id), "Dashing Get");
}, "Dashing");
//Susanoo
Try(() =>
{
var susanooDb = new DatabaseManager(connection);
var susanooPreDefinedCommand =
CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <Post>()
.Realize();
var susanooDynamicPreDefinedCommand =
CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <dynamic>()
.Realize();
tests.Add(Id =>
CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <Post>()
.Realize()
.Execute(susanooDb, new { Id }).First(), "Susanoo: Mapping Cache Retrieval");
tests.Add(Id =>
CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <dynamic>()
.Realize()
.Execute(susanooDb, new { Id }).First(), "Susanoo: Dynamic Mapping Cache Retrieval");
tests.Add(Id => susanooDynamicPreDefinedCommand
.Execute(susanooDb, new { Id }).First(), "Susanoo: Dynamic Mapping Static");
tests.Add(Id => susanooPreDefinedCommand
.Execute(susanooDb, new { Id }).First(), "Susanoo: Mapping Static");
}, "Susanoo");
#endif
// Subsonic isn't maintained anymore - doesn't import correctly
//Try(() =>
// {
// // Subsonic ActiveRecord
// tests.Add(id => 3SubSonic.Post.SingleOrDefault(x => x.Id == id), "SubSonic ActiveRecord.SingleOrDefault");
// // Subsonic coding horror
// SubSonic.tempdbDB db = new SubSonic.tempdbDB();
// tests.Add(id => new SubSonic.Query.CodingHorror(db.Provider, "select * from Posts where Id = @0", id).ExecuteTypedList<Post>(), "SubSonic Coding Horror");
//}, "Subsonic");
//// BLToolkit - doesn't import correctly in the new .csproj world
//var db1 = new DbManager(GetOpenConnection());
//tests.Add(id => db1.SetCommand("select * from Posts where Id = @id", db1.Parameter("id", id)).ExecuteList<Post>(), "BLToolkit");
Console.WriteLine();
Console.WriteLine("Running...");
await tests.RunAsync(iterations).ConfigureAwait(false);
#pragma warning restore RCS1121 // Use [] instead of calling 'First'.
#pragma warning restore IDE0017 // Simplify object initialization
}
}
public void Run(int iterations)
{
using (var connection = TestSuite.GetOpenConnection())
{
var tests = new Tests();
#if LINQ2SQL
Try(() =>
{
var l2scontext1 = GetL2SContext(connection);
tests.Add(id => l2scontext1.Posts.First(p => p.Id == id), "Linq 2 SQL");
var l2scontext2 = GetL2SContext(connection);
var compiledGetPost = CompiledQuery.Compile((Linq2Sql.DataClassesDataContext ctx, int id) => ctx.Posts.First(p => p.Id == id));
tests.Add(id => compiledGetPost(l2scontext2, id), "Linq 2 SQL Compiled");
var l2scontext3 = GetL2SContext(connection);
tests.Add(id => l2scontext3.ExecuteQuery <Post>("select * from Posts where Id = {0}", id).First(), "Linq 2 SQL ExecuteQuery");
}, "LINQ-to-SQL");
#endif
#if ENTITY_FRAMEWORK
Try(() =>
{
var entityContext = new EFContext(connection);
tests.Add(id => entityContext.Posts.First(p => p.Id == id), "Entity framework");
var entityContext2 = new EFContext(connection);
tests.Add(id => entityContext2.Database.SqlQuery <Post>("select * from Posts where Id = {0}", id).First(), "Entity framework SqlQuery");
//var entityContext3 = new EFContext(connection);
//tests.Add(id => entityFrameworkCompiled(entityContext3, id), "Entity framework CompiledQuery");
//var entityContext4 = new EFContext(connection);
//tests.Add(id => entityContext4.Posts.Where("it.Id = @id", new System.Data.Objects.ObjectParameter("id", id)).First(), "Entity framework ESQL");
var entityContext5 = new EFContext(connection);
tests.Add(id => entityContext5.Posts.AsNoTracking().First(p => p.Id == id), "Entity framework No Tracking");
}, "Entity Framework");
#endif
Try(() =>
{
var mapperConnection = TestSuite.GetOpenConnection();
tests.Add(id => mapperConnection.Query <Post>("select * from Posts where Id = @Id", new { Id = id }, buffered: true).First(), "Mapper Query (buffered)");
tests.Add(id => mapperConnection.Query <Post>("select * from Posts where Id = @Id", new { Id = id }, buffered: false).First(), "Mapper Query (non-buffered)");
tests.Add(id => mapperConnection.QueryFirstOrDefault <Post>("select * from Posts where Id = @Id", new { Id = id }), "Mapper QueryFirstOrDefault");
var mapperConnection2 = TestSuite.GetOpenConnection();
tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }, buffered: true).First(), "Dynamic Mapper Query (buffered)");
tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }, buffered: false).First(), "Dynamic Mapper Query (non-buffered)");
tests.Add(id => mapperConnection2.QueryFirstOrDefault("select * from Posts where Id = @Id", new { Id = id }), "Dynamic Mapper QueryQueryFirstOrDefault");
// dapper.contrib
var mapperConnection3 = TestSuite.GetOpenConnection();
tests.Add(id => mapperConnection3.Get <Post>(id), "Dapper.Contrib");
}, "Dapper");
#if MASSIVE
Try(() =>
{
// massive
var massiveModel = new DynamicModel(TestSuite.ConnectionString);
var massiveConnection = TestSuite.GetOpenConnection();
tests.Add(id => massiveModel.Query("select * from Posts where Id = @0", massiveConnection, id).First(), "Dynamic Massive ORM Query");
}, "Massive");
#endif
#if PETAPOCO
Try(() =>
{
// PetaPoco test with all default options
var petapoco = new PetaPoco.Database(TestSuite.ConnectionString, "System.Data.SqlClient");
petapoco.OpenSharedConnection();
tests.Add(id => petapoco.Fetch <Post>("SELECT * from Posts where Id=@0", id).First(), "PetaPoco (Normal)");
// PetaPoco with some "smart" functionality disabled
var petapocoFast = new PetaPoco.Database(TestSuite.ConnectionString, "System.Data.SqlClient");
petapocoFast.OpenSharedConnection();
petapocoFast.EnableAutoSelect = false;
petapocoFast.EnableNamedParams = false;
petapocoFast.ForceDateTimesToUtc = false;
tests.Add(id => petapocoFast.Fetch <Post>("SELECT * from Posts where Id=@0", id).First(), "PetaPoco (Fast)");
}, "PetaPoco");
#endif
#if SUBSONIC
Try(() =>
{
// Subsonic ActiveRecord
tests.Add(id => SubSonic.Post.SingleOrDefault(x => x.Id == id), "SubSonic ActiveRecord.SingleOrDefault");
// Subsonic coding horror
SubSonic.tempdbDB db = new SubSonic.tempdbDB();
tests.Add(id => new SubSonic.Query.CodingHorror(db.Provider, "select * from Posts where Id = @0", id).ExecuteTypedList <Post>(), "SubSonic Coding Horror");
}, "Subsonic");
#endif
// NHibernate
#if NHIBERNATE
Try(() => {
var nhSession1 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession1.CreateSQLQuery(@"select * from Posts where Id = :id")
.SetInt32("id", id)
.List(), "NHibernate SQL");
var nhSession2 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession2.CreateQuery(@"from Post as p where p.Id = :id")
.SetInt32("id", id)
.List(), "NHibernate HQL");
var nhSession3 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession3.CreateCriteria <Post>()
.Add(Restrictions.IdEq(id))
.List(), "NHibernate Criteria");
var nhSession4 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession4
.Query <Post>()
.First(p => p.Id == id), "NHibernate LINQ");
var nhSession5 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession5.Get <Post>(id), "NHibernate Session.Get");
}, "NHibernate");
#endif
#if BLTOOLKIT
// bltoolkit
var db1 = new DbManager(TestSuite.GetOpenConnection());
tests.Add(id => db1.SetCommand("select * from Posts where Id = @id", db1.Parameter("id", id)).ExecuteList <Post>(), "BLToolkit");
#endif
#if SIMPLEDATA
// Simple.Data
Try(() =>
{
var sdb = Simple.Data.Database.OpenConnection(TestSuite.ConnectionString);
tests.Add(id => sdb.Posts.FindById(id).FirstOrDefault(), "Simple.Data");
}, "Simple.Data");
#endif
#if BELGRADE
Try(() =>
{
var query = new Belgrade.SqlClient.SqlDb.QueryMapper(TestSuite.GetOpenConnection());
tests.Add(id => query.ExecuteReader("SELECT TOP 1 * FROM Posts WHERE Id = " + id,
reader =>
{
var post = new Post();
post.Id = reader.GetInt32(0);
post.Text = reader.GetString(1);
post.CreationDate = reader.GetDateTime(2);
post.LastChangeDate = reader.GetDateTime(3);
post.Counter1 = reader.IsDBNull(4) ? null : (int?)reader.GetInt32(4);
post.Counter2 = reader.IsDBNull(5) ? null : (int?)reader.GetInt32(5);
post.Counter3 = reader.IsDBNull(6) ? null : (int?)reader.GetInt32(6);
post.Counter4 = reader.IsDBNull(7) ? null : (int?)reader.GetInt32(7);
post.Counter5 = reader.IsDBNull(8) ? null : (int?)reader.GetInt32(8);
post.Counter6 = reader.IsDBNull(9) ? null : (int?)reader.GetInt32(9);
post.Counter7 = reader.IsDBNull(10) ? null : (int?)reader.GetInt32(10);
post.Counter8 = reader.IsDBNull(11) ? null : (int?)reader.GetInt32(11);
post.Counter9 = reader.IsDBNull(12) ? null : (int?)reader.GetInt32(12);
}), "Belgrade Sql Client");
}, "Belgrade Sql Client");
#endif
#if SUSANOO
//Susanoo
var susanooDb = new DatabaseManager("Smackdown.Properties.Settings.tempdbConnectionString");
var susanooDb2 = new DatabaseManager("Smackdown.Properties.Settings.tempdbConnectionString");
var susanooPreDefinedCommand =
CommandManager.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <Post>()
.Realize("PostById");
var susanooDynamicPreDefinedCommand =
CommandManager.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <dynamic>()
.Realize("DynamicById");
tests.Add(Id =>
CommandManager.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <Post>()
.Realize("PostById")
.Execute(susanooDb, new { Id }).First(), "Susanoo Mapping Cache Retrieval");
tests.Add(Id =>
CommandManager.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <dynamic>()
.Realize("DynamicById")
.Execute(susanooDb, new { Id }).First(), "Susanoo Dynamic Mapping Cache Retrieval");
tests.Add(Id =>
susanooDynamicPreDefinedCommand
.Execute(susanooDb, new { Id }).First(), "Susanoo Dynamic Mapping Static");
tests.Add(Id =>
susanooPreDefinedCommand
.Execute(susanooDb, new { Id }).First(), "Susanoo Mapping Static");
#endif
#if SOMA
// Soma
// DISABLED: assembly fail loading FSharp.PowerPack, Version=2.0.0.0
// var somadb = new Soma.Core.Db(new SomaConfig());
// tests.Add(id => somadb.Find<Post>(id), "Soma");
#endif
#if ORMLITE
//ServiceStack's OrmLite:
// DISABLED: can't find QueryById
//OrmLiteConfig.DialectProvider = SqlServerOrmLiteDialectProvider.Instance; //Using SQL Server
//IDbCommand ormLiteCmd = TestSuite.GetOpenConnection().CreateCommand();
// tests.Add(id => ormLiteCmd.QueryById<Post>(id), "OrmLite QueryById");
#endif
// HAND CODED
var postCommand = new SqlCommand();
postCommand.Connection = connection;
postCommand.CommandText = @"select Id, [Text], [CreationDate], LastChangeDate,
Counter1,Counter2,Counter3,Counter4,Counter5,Counter6,Counter7,Counter8,Counter9 from Posts where Id = @Id";
var idParam = postCommand.Parameters.Add("@Id", System.Data.SqlDbType.Int);
tests.Add(id =>
{
idParam.Value = id;
using (var reader = postCommand.ExecuteReader())
{
reader.Read();
var post = new Post();
post.Id = reader.GetInt32(0);
post.Text = reader.GetNullableString(1);
post.CreationDate = reader.GetDateTime(2);
post.LastChangeDate = reader.GetDateTime(3);
post.Counter1 = reader.GetNullableValue <int>(4);
post.Counter2 = reader.GetNullableValue <int>(5);
post.Counter3 = reader.GetNullableValue <int>(6);
post.Counter4 = reader.GetNullableValue <int>(7);
post.Counter5 = reader.GetNullableValue <int>(8);
post.Counter6 = reader.GetNullableValue <int>(9);
post.Counter7 = reader.GetNullableValue <int>(10);
post.Counter8 = reader.GetNullableValue <int>(11);
post.Counter9 = reader.GetNullableValue <int>(12);
}
}, "hand coded");
#if !COREFX
DataTable table = new DataTable
{
Columns =
{
{ "Id", typeof(int) },
{ "Text", typeof(string) },
{ "CreationDate", typeof(DateTime) },
{ "LastChangeDate", typeof(DateTime) },
{ "Counter1", typeof(int) },
{ "Counter2", typeof(int) },
{ "Counter3", typeof(int) },
{ "Counter4", typeof(int) },
{ "Counter5", typeof(int) },
{ "Counter6", typeof(int) },
{ "Counter7", typeof(int) },
{ "Counter8", typeof(int) },
{ "Counter9", typeof(int) },
}
};
tests.Add(id =>
{
idParam.Value = id;
object[] values = new object[13];
using (var reader = postCommand.ExecuteReader())
{
reader.Read();
reader.GetValues(values);
table.Rows.Add(values);
}
}, "DataTable via IDataReader.GetValues");
#endif
Console.WriteLine();
Console.WriteLine("Running...");
tests.Run(iterations);
}
}
/// <summary>
/// Created IActionResult object that contains the processed response result.
/// </summary>
/// <param name="target">Connection string to the target database where results will be fetched.</param>
/// <returns>ContentResult with the data processed by request.</returns>
public virtual async Task <IActionResult> GetResult(string target)
{
var mapper = new Belgrade.SqlClient.SqlDb.QueryMapper(target);
return(await GetResult(mapper));
}
public async Task RunAsync(int iterations)
{
using (var connection = GetOpenConnection())
{
var tests = new Tests();
// Linq2SQL
Try(() =>
{
var l2scontext1 = GetL2SContext(connection);
tests.Add(id => l2scontext1.Posts.First(p => p.Id == id), "Linq2Sql: Normal");
var l2scontext2 = GetL2SContext(connection);
var compiledGetPost = CompiledQuery.Compile((Linq2Sql.DataClassesDataContext ctx, int id) => ctx.Posts.First(p => p.Id == id));
tests.Add(id => compiledGetPost(l2scontext2, id), "Linq2Sql: Compiled");
var l2scontext3 = GetL2SContext(connection);
tests.Add(id => l2scontext3.ExecuteQuery <Post>("select * from Posts where Id = {0}", id).First(), "Linq2Sql: ExecuteQuery");
}, "LINQ-to-SQL");
// Entity Framework
Try(() =>
{
var entityContext = new EFContext(connection);
tests.Add(id => entityContext.Posts.First(p => p.Id == id), "Entity Framework");
var entityContext2 = new EFContext(connection);
tests.Add(id => entityContext2.Database.SqlQuery <Post>("select * from Posts where Id = {0}", id).First(), "Entity Framework: SqlQuery");
//var entityContext3 = new EFContext(connection);
//tests.Add(id => entityFrameworkCompiled(entityContext3, id), "Entity Framework CompiledQuery");
//var entityContext4 = new EFContext(connection);
//tests.Add(id => entityContext4.Posts.Where("it.Id = @id", new System.Data.Objects.ObjectParameter("id", id)).First(), "Entity Framework ESQL");
var entityContext5 = new EFContext(connection);
tests.Add(id => entityContext5.Posts.AsNoTracking().First(p => p.Id == id), "Entity Framework: No Tracking");
}, "Entity Framework");
// Dapper
Try(() =>
{
var mapperConnection = GetOpenConnection();
tests.Add(id => mapperConnection.Query <Post>("select * from Posts where Id = @Id", new { Id = id }, buffered: true).First(), "Dapper: Query (buffered)");
tests.Add(id => mapperConnection.Query <Post>("select * from Posts where Id = @Id", new { Id = id }, buffered: false).First(), "Dapper: Query (non-buffered)");
tests.Add(id => mapperConnection.QueryFirstOrDefault <Post>("select * from Posts where Id = @Id", new { Id = id }), "Dapper: QueryFirstOrDefault");
var mapperConnection2 = GetOpenConnection();
tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }, buffered: true).First(), "Dapper: Dynamic Query (buffered)");
tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }, buffered: false).First(), "Dapper: Dynamic Query (non-buffered)");
tests.Add(id => mapperConnection2.QueryFirstOrDefault("select * from Posts where Id = @Id", new { Id = id }), "Dapper: Dynamic QueryFirstOrDefault");
// dapper.contrib
var mapperConnection3 = GetOpenConnection();
tests.Add(id => mapperConnection3.Get <Post>(id), "Dapper.Contrib");
}, "Dapper");
// Massive
Try(() =>
{
var massiveModel = new DynamicModel(ConnectionString);
var massiveConnection = GetOpenConnection();
tests.Add(id => massiveModel.Query("select * from Posts where Id = @0", massiveConnection, id).First(), "Massive: Dynamic ORM Query");
}, "Massive");
// PetaPoco
Try(() =>
{
// PetaPoco test with all default options
var petapoco = new PetaPoco.Database(ConnectionString, "System.Data.SqlClient");
petapoco.OpenSharedConnection();
tests.Add(id => petapoco.Fetch <Post>("SELECT * from Posts where Id=@0", id).First(), "PetaPoco: Normal");
// PetaPoco with some "smart" functionality disabled
var petapocoFast = new PetaPoco.Database(ConnectionString, "System.Data.SqlClient");
petapocoFast.OpenSharedConnection();
petapocoFast.EnableAutoSelect = false;
petapocoFast.EnableNamedParams = false;
petapocoFast.ForceDateTimesToUtc = false;
tests.Add(id => petapocoFast.Fetch <Post>("SELECT * from Posts where Id=@0", id).First(), "PetaPoco: Fast");
}, "PetaPoco");
// NHibernate
Try(() =>
{
var nhSession1 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession1.CreateSQLQuery(@"select * from Posts where Id = :id")
.SetInt32("id", id)
.List(), "NHibernate: SQL");
var nhSession2 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession2.CreateQuery(@"from Post as p where p.Id = :id")
.SetInt32("id", id)
.List(), "NHibernate: HQL");
var nhSession3 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession3.CreateCriteria <Post>()
.Add(Restrictions.IdEq(id))
.List(), "NHibernate: Criteria");
var nhSession4 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession4
.Query <Post>()
.First(p => p.Id == id), "NHibernate: LINQ");
var nhSession5 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession5.Get <Post>(id), "NHibernate: Session.Get");
}, "NHibernate");
// Simple.Data
Try(() =>
{
var sdb = Simple.Data.Database.OpenConnection(ConnectionString);
tests.Add(id => sdb.Posts.FindById(id).FirstOrDefault(), "Simple.Data");
}, "Simple.Data");
// Belgrade
Try(() =>
{
var query = new Belgrade.SqlClient.SqlDb.QueryMapper(ConnectionString);
tests.AddAsync(id => query.ExecuteReader("SELECT TOP 1 * FROM Posts WHERE Id = " + id,
reader =>
{
var post = new Post();
post.Id = reader.GetInt32(0);
post.Text = reader.GetString(1);
post.CreationDate = reader.GetDateTime(2);
post.LastChangeDate = reader.GetDateTime(3);
post.Counter1 = reader.IsDBNull(4) ? null : (int?)reader.GetInt32(4);
post.Counter2 = reader.IsDBNull(5) ? null : (int?)reader.GetInt32(5);
post.Counter3 = reader.IsDBNull(6) ? null : (int?)reader.GetInt32(6);
post.Counter4 = reader.IsDBNull(7) ? null : (int?)reader.GetInt32(7);
post.Counter5 = reader.IsDBNull(8) ? null : (int?)reader.GetInt32(8);
post.Counter6 = reader.IsDBNull(9) ? null : (int?)reader.GetInt32(9);
post.Counter7 = reader.IsDBNull(10) ? null : (int?)reader.GetInt32(10);
post.Counter8 = reader.IsDBNull(11) ? null : (int?)reader.GetInt32(11);
post.Counter9 = reader.IsDBNull(12) ? null : (int?)reader.GetInt32(12);
}), "Belgrade Sql Client");
}, "Belgrade Sql Client");
//Susanoo
var susanooDb = new DatabaseManager(connection);
var susanooPreDefinedCommand =
CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <Post>()
.Realize();
var susanooDynamicPreDefinedCommand =
CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <dynamic>()
.Realize();
tests.Add(Id =>
CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <Post>()
.Realize()
.Execute(susanooDb, new { Id }).First(), "Susanoo: Mapping Cache Retrieval");
tests.Add(Id =>
CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <dynamic>()
.Realize()
.Execute(susanooDb, new { Id }).First(), "Susanoo: Dynamic Mapping Cache Retrieval");
tests.Add(Id => susanooDynamicPreDefinedCommand
.Execute(susanooDb, new { Id }).First(), "Susanoo: Dynamic Mapping Static");
tests.Add(Id => susanooPreDefinedCommand
.Execute(susanooDb, new { Id }).First(), "Susanoo: Mapping Static");
//ServiceStack's OrmLite:
Try(() =>
{
var dbFactory = new OrmLiteConnectionFactory(ConnectionString, SqlServerDialect.Provider);
var db = dbFactory.Open();
tests.Add(id => db.SingleById <Post>(id), "ServiceStack.OrmLite: SingleById");
}, "ServiceStack.OrmLite");
// Hand Coded
var postCommand = new SqlCommand()
{
Connection = connection,
CommandText = @"select Id, [Text], [CreationDate], LastChangeDate,
Counter1,Counter2,Counter3,Counter4,Counter5,Counter6,Counter7,Counter8,Counter9 from Posts where Id = @Id"
};
var idParam = postCommand.Parameters.Add("@Id", SqlDbType.Int);
tests.Add(id =>
{
idParam.Value = id;
using (var reader = postCommand.ExecuteReader())
{
reader.Read();
var post = new Post();
post.Id = reader.GetInt32(0);
post.Text = reader.GetNullableString(1);
post.CreationDate = reader.GetDateTime(2);
post.LastChangeDate = reader.GetDateTime(3);
post.Counter1 = reader.GetNullableValue <int>(4);
post.Counter2 = reader.GetNullableValue <int>(5);
post.Counter3 = reader.GetNullableValue <int>(6);
post.Counter4 = reader.GetNullableValue <int>(7);
post.Counter5 = reader.GetNullableValue <int>(8);
post.Counter6 = reader.GetNullableValue <int>(9);
post.Counter7 = reader.GetNullableValue <int>(10);
post.Counter8 = reader.GetNullableValue <int>(11);
post.Counter9 = reader.GetNullableValue <int>(12);
}
}, "Hand Coded");
// Subsonic isn't maintained anymore - doesn't import correctly
//Try(() =>
// {
// // Subsonic ActiveRecord
// tests.Add(id => 3SubSonic.Post.SingleOrDefault(x => x.Id == id), "SubSonic ActiveRecord.SingleOrDefault");
// // Subsonic coding horror
// SubSonic.tempdbDB db = new SubSonic.tempdbDB();
// tests.Add(id => new SubSonic.Query.CodingHorror(db.Provider, "select * from Posts where Id = @0", id).ExecuteTypedList<Post>(), "SubSonic Coding Horror");
//}, "Subsonic");
//// BLToolkit - doesn't import correctly in the new .csproj world
//var db1 = new DbManager(GetOpenConnection());
//tests.Add(id => db1.SetCommand("select * from Posts where Id = @id", db1.Parameter("id", id)).ExecuteList<Post>(), "BLToolkit");
#if !COREFX
var table = new DataTable
{
Columns =
{
{ "Id", typeof(int) },
{ "Text", typeof(string) },
{ "CreationDate", typeof(DateTime) },
{ "LastChangeDate", typeof(DateTime) },
{ "Counter1", typeof(int) },
{ "Counter2", typeof(int) },
{ "Counter3", typeof(int) },
{ "Counter4", typeof(int) },
{ "Counter5", typeof(int) },
{ "Counter6", typeof(int) },
{ "Counter7", typeof(int) },
{ "Counter8", typeof(int) },
{ "Counter9", typeof(int) },
}
};
tests.Add(id =>
{
idParam.Value = id;
object[] values = new object[13];
using (var reader = postCommand.ExecuteReader())
{
reader.Read();
reader.GetValues(values);
table.Rows.Add(values);
}
}, "DataTable via IDataReader.GetValues");
#endif
Console.WriteLine();
Console.WriteLine("Running...");
await tests.RunAsync(iterations).ConfigureAwait(false);
}
}
