private IEnumerable <dynamic> Execute(Tuple <string, object[]> arguments)
{
var dynamicModel = new DynamicModel(_connectionString);
var results = dynamicModel.Query(arguments.Item1, arguments.Item2);
return(results);
}
public void WhenIFollowLinkInValidationEmail()
{
var database = new DynamicModel("DefaultConnection", "UserProfile", "Id").SetConnectionString(ConnectionString);
var confirmationToken = database.Query("SELECT ConfirmationToken FROM webpages_Membership").First().ConfirmationToken;
var emailAddress = ScenarioContext.Current.Get <UserProfile>().EmailAddress;
var url = "/Account/CompleteRegistration?confirmationToken=" + confirmationToken + "&email=" + emailAddress;
Console.WriteLine(url);
_browser.Visit(url);
}
public RecordHierarchy GetRecordHierarchy(Entity entity)
{
var index = 0;
var hierarchy = GetEntityHierarchy(null, entity, ref index);
var sql = GenerateHierarchySql(hierarchy);
var model = new DynamicModel(AdminInitialise.ConnectionStringName);
var records = model.Query(sql, entity.Key.Value.Raw).ToList();
var recordHierarchy = GetHierarchyRecords(records, hierarchy);
return(recordHierarchy);
}
//------ Sub Grid------------
public JsonResult SubGridData(string id)
{
string s = @"
SELECT MEDIA_STATISTICS.*
FROM MEDIA_CAT_DIGITAL_PLCMNT INNER JOIN
MEDIA_STATISTICS ON MEDIA_CAT_DIGITAL_PLCMNT.PLACEMENT_CODE
= MEDIA_STATISTICS.PLACEMENT_CODE
and OID_MEDIA_CAT_DIGITAL_PLCMNT = ";
s += id.ToString();
IEnumerable <dynamic> results = tbl.Query(s);
DynamicModel myview = new View_Placements_Publications();
jgridDisplayHelper sh = new jgridDisplayHelper(ShapeSubGridRow);
JsonResult myJasonResults = sh.GetJson(results, 0, 0, 0);
return(myJasonResults);
}
public JsonResult GridUser(int page, int rows, string search, string sidx, string sord)
{
var data = new List <UserManagerModel>();
var tbl = new DynamicModel("ConnectionString", "aspnet_CustomProfile", "UserId");
dynamic iduser = tbl.Query("SELECT m.ApplicationId, m.UserId, m.Email, cp.Name " +
"FROM aspnet_Membership as m LEFT JOIN aspnet_CustomProfile as cp " +
"ON m.UserId = cp.UserId " +
"inner join aspnet_Applications as a " +
"on a.ApplicationName = @0 and a.ApplicationId = m.ApplicationId " +
"order by cp.Name", Portal.UniqueID);
var table = new DynamicModel("ConnectionString", "aspnet_Roles", "RoleId");
dynamic roles = table.Query("SELECT r.RoleName, ur.UserId " +
"FROM aspnet_Roles r, aspnet_UsersInRoles ur " +
"WHERE r.RoleId = ur.RoleId");
var iRoles = (IEnumerable <dynamic>)roles;
var i = 1;
foreach (var user in iduser)
{
var m = new UserManagerModel();
m.id = i;
m.UserId = user.UserId;
m.UserName = user.Name;
m.UserEmail = user.Email;
var userrolid = Guid.Parse(user.UserId.ToString());
try
{
var roleName = iRoles.Where(r => r.UserId == userrolid);
m.UserRol = roleName.Single().RoleName;
}
catch
{
m.UserRol = "";
}
m.Edit = Builddir(m.UserEmail);
data.Add(m);
i++;
}
if (!string.IsNullOrEmpty(search))
{
return(Search(search, page, rows, data));
}
var result = GetRowsFromList(data.AsQueryable(), rows, page);
return(result);
}
public RecordHierarchy GetRecordHierarchy(Entity entity)
{
_log.InfoFormat("Getting record hierarchy for entity record ({0}#{1})", entity.Name, entity.JoinedKeyWithValue);
var index = 0;
var hierarchy = GetEntityHierarchy(null, entity, ref index);
var sql = GenerateHierarchySql(hierarchy);
_log.DebugFormat("Sql hierarchy: \r\n {0}", sql);
var model = new DynamicModel(Admin.ConnectionStringName);
var records = model.Query(sql, entity.Key.Select(x => x.Value.Raw).ToArray()).ToList();
var recordHierarchy = GetHierarchyRecords(records, hierarchy);
return(recordHierarchy);
}
public RecordHierarchy GetRecordHierarchy(
EntityRecord entityRecord,
IList <PropertyDeleteOption> deleteOptions = null)
{
_log.InfoFormat(
"Getting record hierarchy for entity record ({0}#{1})",
entityRecord.Entity.Name,
entityRecord.JoinedKeysWithNames);
var hierarchy = GetEntityHierarchy(entityRecord.Entity, deleteOptions);
var sql = GenerateHierarchySql(hierarchy);
_log.Debug($"Sql hierarchy: \r\n {sql}");
var model = new DynamicModel(_admin.ConnectionStringName);
var records = model.Query(sql, entityRecord.Keys.Select(x => x.Raw).ToArray()).ToList();
var recordHierarchy = GetHierarchyRecords(records, hierarchy);
return(recordHierarchy);
}
public RecordHierarchy GetRecordHierarchy(Entity entity)
{
var index = 0;
var hierarchy = GetEntityHierarchy(null, entity, ref index);
var sql = GenerateHierarchySQL(hierarchy);
var model = new DynamicModel(AdminInitialise.ConnectionString);
var records = model.Query(sql, entity.Key.Value);
var recordHierarchy = GetHierarchyRecords(records, hierarchy);
return(recordHierarchy);
// TODO: test it
// SELECT [t0].*, [t1].*, [t2].*
//FROM [Categories] AS [t0]
//LEFT OUTER JOIN [Products] AS [t1] ON [t1].[CategoryID] = [t0].[CategoryID]
//LEFT OUTER JOIN [Suppliers] AS [t2] ON [t2].[SupplierID] = [t1].[SupplierID]
//WHERE [t0].CategoryID = 9
//ORDER BY [t0].[CategoryID], [t1].[ProductID], [t2].[SupplierID]
}
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), "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);
}
}
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.FromSql("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 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.AsyncAdd(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);
}
}
/// <summary>Runs.</summary>
/// <param name="iterations">The iterations.</param>
public void Run(int iterations)
{
var tests = new Tests();
var l2scontext1 = GetL2SContext();
tests.Add(id => l2scontext1.Posts.First(p => p.Id == id), "Linq 2 SQL");
var l2scontext2 = GetL2SContext();
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();
tests.Add(id => l2scontext3.ExecuteQuery <Post>("select * from Posts where Id = {0}", id).ToList(), "Linq 2 SQL ExecuteQuery");
//Comment out EF to suppress exception
//var entityContext = new EntityFramework.tempdbEntities1();
//entityContext.Connection.Open();
//tests.Add(id => entityContext.Posts.First(p => p.Id == id), "Entity framework");
//var entityContext2 = new EntityFramework.tempdbEntities1();
//entityContext2.Connection.Open();
//tests.Add(id => entityContext.ExecuteStoreQuery<Post>("select * from Posts where Id = {0}", id).ToList(), "Entity framework ExecuteStoreQuery");
var mapperConnection = Program.GetOpenConnection();
tests.Add(id => mapperConnection.Query <Post>("select * from Posts where Id = @Id", new { Id = id }).ToList(), "Mapper Query");
//var mapperConnection2 = Program.GetOpenConnection();
//tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }).ToList(), "Dynamic Mapper Query");
var massiveModel = new DynamicModel(Program.connectionString);
var massiveConnection = Program.GetOpenConnection();
tests.Add(id => massiveModel.Query("select * from Posts where Id = @0", massiveConnection, id).ToList(), "Dynamic Massive ORM Query");
//NServiceKit.OrmLite Provider:
OrmLiteConfig.DialectProvider = SqlServerOrmLiteDialectProvider.Instance; //Using SQL Server
IDbConnection ormLiteConn = Program.GetOpenConnection();
tests.Add(id => ormLiteConn.Select <Post>("select * from Posts where Id = {0}", id), "OrmLite Query");
// HAND CODED
var connection = Program.GetOpenConnection();
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");
tests.Run(iterations);
}
public JsonResult GridUser(int page, int rows, string search, string sortorder, string sortdatafield)
{
if (Session["sKeywords"] != null && !string.IsNullOrEmpty(Session["sKeywords"].ToString()) && string.IsNullOrEmpty(search))
{
search = Session["sKeywords"].ToString();
}
else if (!string.IsNullOrEmpty(search))
{
Session["sKeywords"] = search;
}
var start = (page * rows) - rows + 1;
var end = start + rows - 1;
string orderBY = "";
if (!string.IsNullOrEmpty(sortorder) && !string.IsNullOrEmpty(sortdatafield))
{
orderBY = " Order By " + (sortdatafield.ToUpper() == "ROLE" ? "UserRol" : sortdatafield) + " " + sortorder + " ";
}
var queryUsers = "WITH YourCTE AS " +
"(" +
"SELECT ROW_NUMBER() OVER(ORDER BY cp.Name) AS RowIndx,au.UserName, m.ApplicationId, m.UserId, m.Email, cp.Name, (SELECT ',' + ar.RoleName FROM aspnet_Roles ar where ar.RoleId in (select RoleId from[dbo].[aspnet_UsersInRoles] ur where ur.UserId = m.UserId) FOR XML PATH('')) as UserRol " +
" FROM aspnet_Membership as m LEFT JOIN aspnet_CustomProfile as cp ON m.UserId = cp.UserId " +
" inner join aspnet_users au on au.UserId = m.UserId " +
" inner join aspnet_Applications as a " +
" on a.ApplicationName = '" + Portal.UniqueID + "' and a.ApplicationId = m.ApplicationId " +
" " + getSearchCondition(search) + " " +
" ) " +
" Select *, (SELECT MAX(RowIndx) FROM YourCTE) AS 'TotalRows' from YourCTE where RowIndx between " + start + " and " + end + orderBY;
//ErrorHandler.Publish(LogLevel.Info, "User Query - " + queryUsers);
var tbl = new DynamicModel("ConnectionString", "aspnet_CustomProfile", "UserId");
dynamic searchedUsers = tbl.Query(queryUsers);
var dataFormted = new List <UserFormatedModel>();
foreach (var m in searchedUsers)
{
var usr = new UserFormatedModel()
{
UserName = m.UserName,
Email = m.Email,
Role = m.UserRol,
Edit = General.GetString("EDIT_USER"),
UserId = m.UserId,
EditId = Builddir(m.Email),
Delete = General.GetString("DELETE_USER"),
TotalRows = m.TotalRows
};
if (!string.IsNullOrEmpty(usr.Role))
{
usr.Role = usr.Role.TrimStart(',').TrimEnd(',');
}
dataFormted.Add(usr);
}
var totalRecords = dataFormted[0].TotalRows;
var totalPages = (int)Math.Ceiling(totalRecords / (float)rows);
List <Result> rusts = new List <Result>();
var jsonData = new Result
{
total = totalPages,
page = page,
currentPage = page,
records = totalRecords,
rows = dataFormted.AsQueryable()
};
rusts.Add(jsonData);
return(Json(rusts, JsonRequestBehavior.AllowGet));
/*
* var data = new List<UserManagerModel>();
*
* dynamic iduser = tbl.Query("SELECT m.ApplicationId, m.UserId, m.Email, cp.Name " +
* "FROM aspnet_Membership as m LEFT JOIN aspnet_CustomProfile as cp " +
* "ON m.UserId = cp.UserId " +
* "inner join aspnet_Applications as a " +
* "on a.ApplicationName = @0 and a.ApplicationId = m.ApplicationId " +
* "order by cp.Name", Portal.UniqueID);
* var table = new DynamicModel("ConnectionString", "aspnet_Roles", "RoleId");
* dynamic roles = table.Query("SELECT r.RoleName, ur.UserId " +
* "FROM aspnet_Roles r, aspnet_UsersInRoles ur " +
* "WHERE r.RoleId = ur.RoleId");
* var iRoles = (IEnumerable<dynamic>)roles;
* var i = 1;
* foreach (var user in iduser)
* {
* var m = new UserManagerModel();
* m.id = i;
* m.UserId = user.UserId;
* m.UserName = user.Name;
* m.UserEmail = user.Email;
* var userrolid = Guid.Parse(user.UserId.ToString());
*
* try
* {
* var roleName = iRoles.Where(r => r.UserId == userrolid);
* m.UserRol = roleName.Single().RoleName;
*
* }
* catch
* {
* m.UserRol = "";
* }
* m.Edit = Builddir(m.UserEmail);
* data.Add(m);
* i++;
* }
* if (!string.IsNullOrEmpty(search))
* {
* return Search(search, page, rows, data);
* }
* var result = GetRowsFromList(data.AsQueryable(), rows, page);
* return result;
*/
}
public void Run(int iterations)
{
var tests = new Tests();
var l2scontext1 = GetL2SContext();
tests.Add(id => l2scontext1.Posts.First(p => p.Id == id), "Linq 2 SQL");
var l2scontext2 = GetL2SContext();
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();
tests.Add(id => l2scontext3.ExecuteQuery <Post>("select * from Posts where Id = {0}", id).ToList(), "Linq 2 SQL ExecuteQuery");
var entityContext = new EntityFramework.tempdbEntities1();
entityContext.Connection.Open();
tests.Add(id => entityContext.Posts.First(p => p.Id == id), "Entity framework");
var entityContext2 = new EntityFramework.tempdbEntities1();
entityContext2.Connection.Open();
tests.Add(id => entityContext2.ExecuteStoreQuery <Post>("select * from Posts where Id = {0}", id).ToList(), "Entity framework ExecuteStoreQuery");
var entityContext3 = new EntityFramework.tempdbEntities1();
entityContext3.Connection.Open();
tests.Add(id => entityFrameworkCompiled(entityContext3, id), "Entity framework CompiledQuery");
var entityContext4 = new EntityFramework.tempdbEntities1();
entityContext4.Connection.Open();
tests.Add(id => entityContext4.Posts.Where("it.Id = @id", new System.Data.Objects.ObjectParameter("id", id)).First(), "Entity framework ESQL");
var entityContext5 = new EntityFramework.tempdbEntities1();
entityContext5.Connection.Open();
entityContext5.Posts.MergeOption = System.Data.Objects.MergeOption.NoTracking;
tests.Add(id => entityContext.Posts.First(p => p.Id == id), "Entity framework No Tracking");
var mapperConnection = Program.GetOpenConnection();
tests.Add(id => mapperConnection.Query <Post>("select * from Posts where Id = @Id", new { Id = id }).ToList(), "Mapper Query");
var mapperConnection2 = Program.GetOpenConnection();
tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }).ToList(), "Dynamic Mapper Query");
var massiveModel = new DynamicModel(Program.connectionString);
var massiveConnection = Program.GetOpenConnection();
tests.Add(id => massiveModel.Query("select * from Posts where Id = @0", massiveConnection, id).ToList(), "Dynamic Massive ORM Query");
// PetaPoco test with all default options
var petapoco = new PetaPoco.Database(Program.connectionString, "System.Data.SqlClient");
petapoco.OpenSharedConnection();
tests.Add(id => petapoco.Fetch <Post>("SELECT * from Posts where Id=@0", id), "PetaPoco (Normal)");
// PetaPoco with some "smart" functionality disabled
var petapocoFast = new PetaPoco.Database(Program.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), "PetaPoco (Fast)");
// 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");
// NHibernate
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>()
.Where(p => p.Id == id).ToList(), "NHibernate LINQ");
// bltoolkit
var db1 = new DbManager(Program.GetOpenConnection());
tests.Add(id => db1.SetCommand("select * from Posts where Id = @id", db1.Parameter("id", id)).ExecuteList <Post>(), "BLToolkit");
// Simple.Data
var sdb = Simple.Data.Database.OpenConnection(Program.connectionString);
tests.Add(id => sdb.Posts.FindById(id), "Simple.Data");
//ServiceStack.OrmLite Provider:
/*
* Unhandled Exception: System.FormatException: Input string was not in a correct f
* ormat.
* at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffe
* r& number, NumberFormatInfo info, Boolean parseDecimal)
*/
// OrmLiteConfig.DialectProvider = SqlServerOrmLiteDialectProvider.Instance; //Using SQL Server
// IDbCommand ormLiteCmd = Program.GetOpenConnection().CreateCommand();
// tests.Add(id => ormLiteCmd.Select<Post>("select * from Posts where Id = {0}", id), "ServiceStack.OrmLite SQL Query");
// HAND CODED
var connection = Program.GetOpenConnection();
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");
tests.Run(iterations);
}
public void Run(int iterations)
{
using (var connection = Program.GetOpenConnection())
{
var tests = new Tests();
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");
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");
var mapperConnection = Program.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)");
var mapperConnection2 = Program.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)");
// dapper.contrib
var mapperConnection3 = Program.GetOpenConnection();
tests.Add(id => mapperConnection2.Get <Post>(id), "Dapper.Cotrib");
var massiveModel = new DynamicModel(Program.ConnectionString);
var massiveConnection = Program.GetOpenConnection();
tests.Add(id => massiveModel.Query("select * from Posts where Id = @0", massiveConnection, id).First(), "Dynamic Massive ORM Query");
// PetaPoco test with all default options
var petapoco = new PetaPoco.Database(Program.ConnectionString, "System.Data.SqlClient");
petapoco.OpenSharedConnection();
tests.Add(id => petapoco.Fetch <Post>("SELECT * from Posts where Id=@0", id), "PetaPoco (Normal)");
// PetaPoco with some "smart" functionality disabled
var petapocoFast = new PetaPoco.Database(Program.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), "PetaPoco (Fast)");
// 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");
// NHibernate
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>()
.Where(p => p.Id == id).First(), "NHibernate LINQ");
var nhSession5 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession5.Get <Post>(id), "NHibernate Session.Get");
// bltoolkit
var db1 = new DbManager(Program.GetOpenConnection());
tests.Add(id => db1.SetCommand("select * from Posts where Id = @id", db1.Parameter("id", id)).ExecuteList <Post>(), "BLToolkit");
// Simple.Data
var sdb = Simple.Data.Database.OpenConnection(Program.ConnectionString);
tests.Add(id => sdb.Posts.FindById(id), "Simple.Data");
// Soma
var somadb = new Soma.Core.Db(new SomaConfig());
tests.Add(id => somadb.Find <Post>(id), "Soma");
//ServiceStack's OrmLite:
OrmLiteConfig.DialectProvider = SqlServerOrmLiteDialectProvider.Instance; //Using SQL Server
IDbCommand ormLiteCmd = Program.GetOpenConnection().CreateCommand();
tests.Add(id => ormLiteCmd.QueryById <Post>(id), "OrmLite QueryById");
// 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");
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");
tests.Run(iterations);
}
}
public dynamic QueryDynamic()
{
Step();
return(_model.Query("select * from Posts where Id = @0", _connection, i).First());
}
public static IEnumerable <dynamic> GetBins()
{
return(_db.Query("SELECT * FROM bins"));
}