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")); }