public void CreateEntrys(string connection, string outputPath, string database) { TargetDir = outputPath; Manager = new DbAccessLayer(DbAccessType.MsSql, connection); bool checkDatabase; try { checkDatabase = Manager.CheckDatabase(); } catch (Exception) { checkDatabase = false; } if (!checkDatabase) { throw new Exception("Database not accessible. Maybe wrong Connection or no Selected Database?"); } var databaseName = string.IsNullOrEmpty(Manager.Database.DatabaseName) ? database : Manager.Database.DatabaseName; if (string.IsNullOrEmpty(databaseName)) { throw new Exception("Database not exists. Maybe wrong Connection or no Selected Database?"); } Console.WriteLine("Connection OK ... Reading Server Version ..."); SqlVersion = Manager.RunPrimetivSelect <string>("SELECT SERVERPROPERTY('productversion')").FirstOrDefault(); Console.WriteLine("Server version is {0}", SqlVersion); Console.WriteLine("Reading Tables from {0} ...", databaseName); Tables = Manager.Select <TableInformations>().Select(s => new TableInfoModel(s, databaseName, Manager)).ToList(); Views = Manager.Select <ViewInformation>().Select(s => new TableInfoModel(s, databaseName, Manager)).ToList(); StoredProcs = Manager.Select <StoredProcedureInformation>().Select(s => new StoredPrcInfoModel(s)).ToList(); Console.WriteLine( "Found {0} Tables, {1} Views, {2} Procedures ... select a Table to see Options or start an Action", Tables.Count(), Views.Count(), StoredProcs.Count()); Enums = new List <Dictionary <int, string> >(); RenderMenu(); }
public TableInfoModel(ITableInformations info, string database, DbAccessLayer db) { CreateSelectFactory = true; Info = info; Database = database; ColumnInfos = db.Select <ColumnInfo>(new object[] { Info.TableName, database }).Select(s => new ColumInfoModel(s)).ToList(); var firstOrDefault = db.RunPrimetivSelect(typeof(string), "SELECT COLUMN_NAME " + "FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc " + "JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu " + "ON tc.CONSTRAINT_NAME = ccu.Constraint_name " + "WHERE tc.CONSTRAINT_TYPE = 'Primary Key' " + "AND tc.TABLE_CATALOG = @database " + "AND tc.TABLE_NAME = @tableName", new List <IQueryParameter>() { new QueryParameter("tableName", info.TableName), new QueryParameter("database", Database) }).FirstOrDefault() as string; var columInfoModel = ColumnInfos.FirstOrDefault(s => s.ColumnInfo.ColumnName == firstOrDefault); if (columInfoModel != null) { columInfoModel.PrimaryKey = true; } var forgeinKeyDeclarations = db.Select <ForgeinKeyInfoModel>(new object[] { info.TableName, database }); foreach (var item in ColumnInfos) { var fod = forgeinKeyDeclarations.FirstOrDefault(s => s.SourceColumn == item.ColumnInfo.ColumnName); if (fod != null) { item.ForgeinKeyDeclarations = fod; } } }
public virtual void LoadPage(DbAccessLayer dbAccess) { RaiseNewPageLoading(); SyncHelper(CurrentPageItems.Clear); if (pk == null) { pk = typeof(T).GetPK(dbAccess.Config); } IDbCommand finalAppendCommand; if (CommandQuery != null) { TotalItemCount = new ElementProducer <T>(CommandQuery).CountInt().FirstOrDefault(); MaxPage = (long)Math.Ceiling((decimal)TotalItemCount / PageSize); RaiseNewPageLoading(); var elements = new ElementProducer <T>(CommandQuery).QueryD("LIMIT @PagedRows, @PageSize", new { PagedRows = (CurrentPage - 1) * PageSize, PageSize }).ToArray(); foreach (var item in elements) { var item1 = item; SyncHelper(() => CurrentPageItems.Add(item1)); } RaiseNewPageLoaded(); } else { if (AppendedComands.Any()) { if (BaseQuery == null) { BaseQuery = dbAccess.CreateSelect <T>(); } finalAppendCommand = AppendedComands.Aggregate(BaseQuery, (current, comand) => dbAccess.Database.MergeTextToParameters(current, comand, false, 1, true, false)); } else { if (BaseQuery == null) { BaseQuery = dbAccess.CreateSelect <T>(); } finalAppendCommand = BaseQuery; } var selectMaxCommand = dbAccess .Query() .QueryText("WITH CTE AS") .InBracket(query => query.QueryCommand(finalAppendCommand)) .QueryText("SELECT COUNT(1) FROM CTE") .ContainerObject .Compile(); ////var selectMaxCommand = DbAccessLayerHelper.CreateCommand(s, "SELECT COUNT( * ) AS NR FROM " + TargetType.GetTableName()); //if (finalAppendCommand != null) // selectMaxCommand = DbAccessLayer.ConcatCommands(s, selectMaxCommand, finalAppendCommand); var maxItems = dbAccess.RunPrimetivSelect(typeof(long), selectMaxCommand).FirstOrDefault(); if (maxItems != null) { long parsedCount; long.TryParse(maxItems.ToString(), out parsedCount); TotalItemCount = parsedCount; MaxPage = (long)Math.Ceiling((decimal)parsedCount / PageSize); } //Check select strategy //IF version is or higher then 11.0.2100.60 we can use OFFSET and FETCH //esle we need to do it the old way RaiseNewPageLoading(); IDbCommand command; command = dbAccess .Query() .WithCte("CTE", cte => new SelectQuery <T>(cte.QueryCommand(finalAppendCommand))) .QueryText("SELECT * FROM") .QueryText("CTE") .QueryD("LIMIT @PagedRows, @PageSize;", new { PagedRows = (CurrentPage - 1) * PageSize, PageSize }) .ContainerObject .Compile(); var selectWhere = dbAccess.SelectNative(typeof(T), command, true).Cast <T>().ToArray(); foreach (var item in selectWhere) { var item1 = item; SyncHelper(() => CurrentPageItems.Add(item1)); } RaiseNewPageLoaded(); } }
/// <summary> /// Loads the PageSize into CurrentPageItems /// </summary> /// <param name="dbAccess"></param> void IDataPager.LoadPage(DbAccessLayer dbAccess) { T[] selectWhere = null; IDbCommand finalAppendCommand; if (string.IsNullOrEmpty(SqlVersion)) { #pragma warning disable 618 SqlVersion = dbAccess.RunPrimetivSelect <string>("SELECT SERVERPROPERTY('productversion')") .FirstOrDefault(); #pragma warning restore 618 } SyncHelper(CurrentPageItems.Clear); if (pk == null) { pk = typeof(T).GetPK(dbAccess.Config); } if (CommandQuery != null) { TotalItemCount = new ElementProducer <T>(CommandQuery).CountInt().FirstOrDefault(); MaxPage = (long)Math.Ceiling((decimal)TotalItemCount / PageSize); RaiseNewPageLoading(); var elements = new ElementProducer <T>(CommandQuery).QueryD("OFFSET @PagedRows ROWS FETCH NEXT @PageSize ROWS ONLY", new { PagedRows = (CurrentPage - 1) * PageSize, PageSize }).ToArray(); foreach (var item in elements) { var item1 = item; SyncHelper(() => CurrentPageItems.Add(item1)); } RaiseNewPageLoaded(); } else { if (AppendedComands.Any()) { if (BaseQuery == null) { BaseQuery = dbAccess.CreateSelect <T>(); } finalAppendCommand = AppendedComands.Aggregate(BaseQuery, (current, comand) => dbAccess.Database.MergeTextToParameters(current, comand, false, 1, true, false)); } else { if (BaseQuery == null) { BaseQuery = dbAccess.CreateSelect <T>(); } finalAppendCommand = BaseQuery; } var selectMaxCommand = dbAccess .Query() .QueryText("WITH CTE AS") .InBracket(query => query.QueryCommand(finalAppendCommand)) .QueryText("SELECT COUNT(1) FROM CTE") .ContainerObject .Compile(); ////var selectMaxCommand = DbAccessLayerHelper.CreateCommand(s, "SELECT COUNT( * ) AS NR FROM " + TargetType.GetTableName()); //if (finalAppendCommand != null) // selectMaxCommand = DbAccessLayer.ConcatCommands(s, selectMaxCommand, finalAppendCommand); var maxItems = dbAccess.RunPrimetivSelect(typeof(long), selectMaxCommand).FirstOrDefault(); if (maxItems != null) { long parsedCount; long.TryParse(maxItems.ToString(), out parsedCount); TotalItemCount = parsedCount; MaxPage = (long)Math.Ceiling((decimal)parsedCount / PageSize); } //Check select strategy //IF version is or higher then 11.0.2100.60 we can use OFFSET and FETCH //esle we need to do it the old way RaiseNewPageLoading(); IDbCommand command; if (CheckVersionForFetch()) { command = dbAccess .Query() .WithCte("CTE", cte => new SelectQuery <T>(cte.QueryCommand(finalAppendCommand))) .QueryText("SELECT * FROM") .QueryText("CTE") .QueryText("ORDER BY") .QueryD(pk) .QueryD("ASC OFFSET @PagedRows ROWS FETCH NEXT @PageSize ROWS ONLY", new { PagedRows = (CurrentPage - 1) * PageSize, PageSize }) .ContainerObject .Compile(); } else { // ReSharper disable ConvertToLambdaExpression var selectQuery = dbAccess.Query() .WithCte("BASECTE", baseCte => { if (BaseQuery != null) { return(baseCte.Select.Table <T>()); } return(new SelectQuery <T>(baseCte.QueryCommand(finalAppendCommand))); }) .WithCte("CTE", cte => { return(new SelectQuery <T>(cte.QueryText("SELECT * FROM (") .Select.Table <T>() .RowNumberOrder("@pk") .WithParamerters(new { Pk = pk }) .QueryText("AS RowNr") .QueryText(", BASECTE.* FROM BASECTE") .QueryText(")") .As("TBL") .Where .Column("RowNr") .Is .Between(page => { return page.QueryText("@PagedRows * @PageSize + 1") .WithParamerters(new { PagedRows = CurrentPage - 1, PageSize }); }, maxPage => { return maxPage .InBracket(calc => { return calc.QueryText("@PagedRows + 1"); }) .QueryText("* @PageSize"); } ))); }, true) .QueryText("SELECT * FROM CTE"); command = selectQuery.ContainerObject.Compile(); } selectWhere = dbAccess.SelectNative(typeof(T), command, true).Cast <T>().ToArray(); foreach (var item in selectWhere) { var item1 = item; SyncHelper(() => CurrentPageItems.Add(item1)); } RaiseNewPageLoaded(); } }
void IDataPager.LoadPage(DbAccessLayer dbAccess) { T[] selectWhere = null; IDbCommand finalAppendCommand; if (AppendedComands.Any()) { if (BaseQuery == null) { BaseQuery = dbAccess.CreateSelect <T>(); } finalAppendCommand = AppendedComands.Aggregate(BaseQuery, (current, comand) => dbAccess.Database.MergeTextToParameters(current, comand, false, 1, true, false)); } else { if (BaseQuery == null) { BaseQuery = dbAccess.CreateSelect <T>(); } finalAppendCommand = BaseQuery; } SyncHelper(CurrentPageItems.Clear); var pk = TargetType.GetPK(dbAccess.Config); var selectMaxCommand = dbAccess .Query() .WithCte("CTE", cte => new SelectQuery <T>(cte.QueryCommand(finalAppendCommand))) .QueryText("SELECT COUNT(*) FROM CTE") .ContainerObject .Compile(); var maxItems = dbAccess.RunPrimetivSelect(typeof(long), selectMaxCommand).FirstOrDefault(); if (maxItems != null) { long parsedCount; long.TryParse(maxItems.ToString(), out parsedCount); TotalItemCount = parsedCount; MaxPage = (long)Math.Ceiling((decimal)parsedCount / PageSize); } RaiseNewPageLoading(); IDbCommand command; command = dbAccess.Query() .WithCte("CTE", cte => new SelectQuery <T>(cte.QueryCommand(finalAppendCommand))) .QueryText("SELECT * FROM CTE") .QueryD("ASC LIMIT @PageSize OFFSET @PagedRows", new { PagedRows = (CurrentPage - 1) * PageSize, PageSize }) .ContainerObject .Compile(); selectWhere = dbAccess.SelectNative(TargetType, command, true).Cast <T>().ToArray(); foreach (T item in selectWhere) { var item1 = item; SyncHelper(() => CurrentPageItems.Add(item1)); } if (CurrentPage > MaxPage) { CurrentPage = MaxPage; } RaiseNewPageLoaded(); }
public async Task CreateEntrysAsync(string connection, string outputPath, string database) { Status = "Try to connect"; //Data Source=(LocalDb)\ProjectsV12;Integrated Security=True;Database=TestDB; IsEnumeratingDatabase = true; TargetDir = outputPath; Manager = new DbAccessLayer(DbAccessType.MsSql, connection); Manager.Async = false; Manager.ThreadSave = true; DbConfig.EnableGlobalThreadSafety = true; try { Connected = Manager.CheckDatabase(); } catch (Exception) { IsEnumeratingDatabase = false; Connected = false; } if (!Connected) { IsEnumeratingDatabase = false; Status = "Database not accessible. Maybe wrong Connection or no Selected Database?"; return; } var databaseName = string.IsNullOrEmpty(Manager.Database.DatabaseName) ? database : Manager.Database.DatabaseName; if (string.IsNullOrEmpty(databaseName)) { IsEnumeratingDatabase = false; Status = "Database not exists. Maybe wrong Connection or no Selected Database?"; Connected = false; return; } Status = "Connection OK ... Reading Server Version ..."; SqlVersion = Manager.RunPrimetivSelect <string>("SELECT SERVERPROPERTY('productversion')").FirstOrDefault(); Status = "Reading Tables"; var counter = 2; var createTables = SimpleWork(() => { var tables = new DbAccessLayer(DbAccessType.MsSql, connection).Select <TableInformations>() .Select( s => new TableInfoModel(s, databaseName, new DbAccessLayer(DbAccessType.MsSql, connection))) .Select(s => new TableInfoViewModel(s, this)) .ToList(); foreach (var source in tables) { if (Tables.All(f => f.Info.TableName != source.Info.TableName)) { Tables.Add(source); } } }); var createViews = SimpleWork(() => { var views = new DbAccessLayer(DbAccessType.MsSql, connection) .Select <ViewInformation>() .Select(s => new TableInfoModel(s, databaseName, new DbAccessLayer(DbAccessType.MsSql, connection))) .Select(s => new TableInfoViewModel(s, this)) .ToList(); foreach (var source in views) { if (Views.All(f => f.Info.TableName != source.Info.TableName)) { Views.Add(source); } } }); await createTables; await createViews; SelectedTable = Tables.FirstOrDefault(); IsEnumeratingDatabase = false; Status = "Done"; }