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