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?");
}
WinConsole.WriteLine("Connection OK ... Reading Server Version ...");
SqlVersion = Manager.RunSelect <string>(Manager.Database.CreateCommand("SELECT SERVERPROPERTY('productversion')")).FirstOrDefault();
WinConsole.WriteLine("Server version is {0}", SqlVersion);
WinConsole.WriteLine("Reading Tables from {0} ...", databaseName);
Tables = Manager.Select <TableInformations>()
.ToArray()
.AsParallel()
.Select(s => new TableInfoModel(s, databaseName, new DbAccessLayer(DbAccessType.MsSql, connection)))
.ToList();
Views = Manager.Select <ViewInformation>()
.ToArray()
.AsParallel()
.Select(s => new TableInfoModel(s, databaseName, new DbAccessLayer(DbAccessType.MsSql, connection)))
.ToList();
StoredProcs = Manager.Select <StoredProcedureInformation>()
.Select(s => new StoredPrcInfoModel(s))
.ToList();
WinConsole.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 void SharedTransactionCounter()
{
var dbOne = new DefaultDatabaseAccess(new ThreadConnectionController());
dbOne.Attach(new MsSql(DbAccess.Database.ConnectionString));
var rootAccess = new DbAccessLayer(dbOne, DbAccess.Config);
Assert.That(rootAccess.Database.ConnectionController.InstanceCounter, Is.EqualTo(0));
Assert.That(rootAccess.Database.ConnectionController.LockRoot, Is.Not.Null);
Assert.That(rootAccess.Database.ConnectionController.Transaction, Is.Null);
var dbTwo = new DefaultDatabaseAccess(new ThreadConnectionController());
dbTwo.Attach(new MsSql(DbAccess.Database.ConnectionString));
var nestedAccess = new DbAccessLayer(dbTwo, DbAccess.Config);
Assert.That(nestedAccess.Database.ConnectionController.InstanceCounter, Is.EqualTo(0));
Assert.That(nestedAccess.Database.ConnectionController.LockRoot, Is.EqualTo(dbOne.ConnectionController.LockRoot));
Assert.That(nestedAccess.Database.ConnectionController.Transaction, Is.Null);
Assert.That(DbAccess.Database.ConnectionController, Is.Not.EqualTo(dbOne.ConnectionController));
Assert.That(DbAccess.Database.ConnectionController.LockRoot, Is.Not.EqualTo(dbOne.ConnectionController.LockRoot));
Assert.That(rootAccess.Database, Is.Not.EqualTo(nestedAccess.Database));
Assert.That(rootAccess.Database.ConnectionController, Is.Not.EqualTo(nestedAccess.Database.ConnectionController));
if (Type.HasFlag(DbAccessType.MsSql))
{
DbAccess.ExecuteGenericCommand("ALTER DATABASE " + DbAccess.Database.DatabaseName +
" SET ALLOW_SNAPSHOT_ISOLATION ON");
}
DbAccess.Database.RunInTransaction((de) =>
{
Assert.That(DbAccess.Database.ConnectionController.InstanceCounter, Is.EqualTo(1));
Assert.That(DbAccess.Select <Users>().Length, Is.EqualTo(0));
}, IsolationLevel.Snapshot);
rootAccess.Database.RunInTransaction(d =>
{
Assert.That(rootAccess.Database.ConnectionController.InstanceCounter, Is.EqualTo(nestedAccess.Database.ConnectionController.InstanceCounter));
Assert.That(rootAccess.Database.ConnectionController.Transaction, Is.EqualTo(nestedAccess.Database.ConnectionController.Transaction));
Assert.That(rootAccess.Database.ConnectionController.InstanceCounter, Is.EqualTo(1));
DataMigrationHelper.AddUsersFast(10, rootAccess);
Assert.That(rootAccess.Database.ConnectionController.InstanceCounter, Is.EqualTo(1));
//the nested database runs with the same TransactionCounter as the RootAccess so it should use
//the same transaction and connection
nestedAccess.Database.RunInTransaction((de) =>
{
Assert.That(rootAccess.Database.ConnectionController.InstanceCounter, Is.EqualTo(2));
Assert.That(nestedAccess.Select <Users>().Length, Is.EqualTo(10));
DataMigrationHelper.AddUsersFast(10, nestedAccess);
Assert.That(rootAccess.Database.ConnectionController.InstanceCounter, Is.EqualTo(2));
});
Assert.That(nestedAccess.Select <Users>().Length, Is.EqualTo(20));
DbAccess.Database.RunInTransaction((de) =>
{
Assert.That(DbAccess.Database.ConnectionController.InstanceCounter, Is.EqualTo(1));
Assert.That(DbAccess.Select <Users>().Length, Is.EqualTo(0));
}, IsolationLevel.Snapshot);
}, IsolationLevel.Snapshot);
DbAccess.Database.RunInTransaction((de) =>
{
Assert.That(DbAccess.Database.ConnectionController.InstanceCounter, Is.EqualTo(1));
Assert.That(DbAccess.Select <Users>().Length, Is.EqualTo(20));
}, IsolationLevel.Snapshot);
}
/// <summary>
/// Creates a DbCollection for the specifiy type
/// To Limit the output create a new Type and then define the statement
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static DbCollection <T> CreateDbCollection <T>(this DbAccessLayer layer)
where T : class,
INotifyPropertyChanged
{
return(new DbCollection <T>(layer.Select <T>()));
}
private void RenderTableMenu(ITableInfoModel selectedTable)
{
WinConsole.WriteLine("Actions:");
WinConsole.WriteLine(@"\ForModel [\c] [ColumnName] [[NewName] | [\d]] ");
WinConsole.WriteLine(@" Adds a ForModelAttribute to a Property or class with \c for class. deletes it with \d");
WinConsole.WriteLine("Example:");
WinConsole.WriteLine(@" \ForModelAttribute \c NewTableName");
WinConsole.WriteLine(" Adding a ForModelAttribute Attribute to the generated class");
WinConsole.WriteLine(@" \ForModelAttribute ID_Column NewName");
WinConsole.WriteLine(
" Adding a ForModelAttribute Attribute to the generated Property with the value NewName");
WinConsole.WriteLine();
WinConsole.WriteLine(@"\Exclude [true | false] [ColumnName]");
WinConsole.WriteLine(" Exclude this table from the Process");
WinConsole.WriteLine(@"\InsertIgnore true | false] [ColumnName]");
WinConsole.WriteLine(" Exclude this column from inserts");
WinConsole.WriteLine(@"\Enum [true | false] [ColumnName]");
WinConsole.WriteLine(" Marks this Column as an ENUM field on the Database. Experimental");
WinConsole.WriteLine(@"\Fallack [true | false]]");
WinConsole.WriteLine(" Should create a LoadNotImplimentedDynamic Property for Not Loaded fields");
WinConsole.WriteLine(@"\Createloader [true | false]]");
WinConsole.WriteLine(" Should create a Dataloader that loads the Properties from the IDataRecord");
WinConsole.WriteLine(@"\CreateSelect [true | false]]");
WinConsole.WriteLine(" Should create a Attribute with a Select Statement");
WinConsole.WriteLine(@"\stats");
WinConsole.WriteLine(" Shows all avalible data from this table");
WinConsole.WriteLine(@"\back");
WinConsole.WriteLine(" Go back to Main Menu");
var readLine = Program.AutoConsole.GetNextOption();
if (string.IsNullOrEmpty(readLine))
{
RenderMenu();
return;
}
var parts = readLine.Split(' ').ToArray();
if (parts.Any())
{
switch (parts[0].ToLower())
{
case @"\formodel":
if (parts.Length == 3)
{
var deleteOrNewName = parts[2];
if (parts[1] == @"\c")
{
if (deleteOrNewName == @"\d")
{
selectedTable.NewTableName = string.Empty;
}
else
{
selectedTable.NewTableName = parts[2];
}
WinConsole.WriteLine("Renamed from {0} to {1}", selectedTable.Info.TableName, selectedTable.NewTableName);
}
else
{
var oldName = parts[1];
var columnToRename = selectedTable.ColumnInfos.FirstOrDefault(s => s.ColumnInfo.ColumnName == oldName);
if (deleteOrNewName != @"\d")
{
if (columnToRename != null)
{
columnToRename.NewColumnName = deleteOrNewName;
WinConsole.WriteLine("Renamed from {0} to {1}", oldName, deleteOrNewName);
}
else
{
WinConsole.WriteLine("There is no Column that is named like {0}", deleteOrNewName);
}
}
else
{
if (columnToRename != null)
{
columnToRename.NewColumnName = string.Empty;
WinConsole.WriteLine("Removed the Renaming from {0} to {1}", deleteOrNewName, parts[1]);
}
else
{
WinConsole.WriteLine("There is no Column that is named like {0}", deleteOrNewName);
}
}
}
}
else
{
WinConsole.WriteLine("Unvalid Input expected was [ColumnName] [NewName] ");
WinConsole.WriteLine();
}
break;
case @"\insertignore":
if (parts.Length == 3)
{
bool result;
bool.TryParse(parts[1], out result);
var column = selectedTable.ColumnInfos.FirstOrDefault(s => s.ColumnInfo.ColumnName == parts[2]);
if (column == null)
{
WinConsole.WriteLine("Unvalid Input expected was [ColumnName] ");
WinConsole.WriteLine();
break;
}
column.InsertIgnore = result;
}
else
{
WinConsole.WriteLine("Unvalid Input expected was true | false ");
WinConsole.WriteLine();
}
break;
case @"\enum":
if (parts.Length == 3)
{
bool result;
bool.TryParse(parts[1], out result);
var column = selectedTable.ColumnInfos.FirstOrDefault(s => s.ColumnInfo.ColumnName == parts[2]);
if (column == null)
{
WinConsole.WriteLine("Unvalid Input expected was [ColumnName] ");
WinConsole.WriteLine();
break;
}
if (result == false)
{
column.EnumDeclaration = null;
break;
}
if (column.ForgeinKeyDeclarations == null)
{
WinConsole.WriteLine("Declare the Enum:");
WinConsole.WriteLine("Format: [Number] [Description]");
WinConsole.WriteLine("Example: '1 Valid'");
WinConsole.WriteLine("Type ok to submit");
WinConsole.WriteLine("Type cancel to revert");
column.EnumDeclaration = new EnumDeclarationModel();
WinConsole.WriteLine("Name of Enum:");
column.EnumDeclaration.Name = Program.AutoConsole.GetNextOption();
while (true)
{
var inp = Program.AutoConsole.GetNextOption();
if (inp.ToLower() == "ok")
{
break;
}
if (inp.ToLower() == "cancel")
{
column.EnumDeclaration = null;
}
var option = inp.Split(' ');
if (option.Length == 2)
{
var enumNumber = option[0];
int enumNumberResult;
if (int.TryParse(enumNumber, out enumNumberResult))
{
column.EnumDeclaration.Values.Add(enumNumberResult, option[1]);
WinConsole.WriteLine("Added Enum member {0} = {1}", option[1], enumNumberResult);
}
else
{
WinConsole.WriteLine("Invalid Enum number Supplyed");
}
}
else
{
WinConsole.WriteLine("Invalid Enum member Supplyed");
}
}
}
else
{
WinConsole.WriteLine("Enum is ForgeinKey.");
WinConsole.WriteLine("Read data from Database to autogenerate Enum");
WinConsole.WriteLine("Reading table: '{0}'", column.ForgeinKeyDeclarations.TableName);
var tableContent =
Manager.Select <Any>(new object[] { column.ForgeinKeyDeclarations.TableName });
if (!tableContent.Any())
{
WinConsole.WriteLine("The Enum table '{0}' does not contain any data", column.ForgeinKeyDeclarations.TableName);
break;
}
if (tableContent.First().PropertyBag.Count > 2)
{
WinConsole.WriteLine("The Enum table '{0}' contains more then 2 columns", column.ForgeinKeyDeclarations.TableName);
break;
}
if (!tableContent.Any(s => s.PropertyBag.Any(f => f.Value is int)))
{
WinConsole.WriteLine("The Enum table '{0}' does not contains exactly one column of type int",
column.ForgeinKeyDeclarations.TableName);
break;
}
if (!tableContent.Any(s => s.PropertyBag.Any(f => f.Value is string)))
{
WinConsole.WriteLine("The Enum table '{0}' does not contains exactly one column of type int",
column.ForgeinKeyDeclarations.TableName);
break;
}
column.EnumDeclaration = new EnumDeclarationModel();
column.EnumDeclaration.Name = column.ForgeinKeyDeclarations.TableName + "LookupValues";
foreach (var item in tableContent)
{
var pk = (int)item.PropertyBag.FirstOrDefault(s => s.Value is int).Value;
var value = (string)item.PropertyBag.FirstOrDefault(s => s.Value is string).Value;
column.EnumDeclaration.Values.Add(pk, value);
WinConsole.WriteLine("Adding Enum member '{0}' = '{1}'", value, pk);
}
}
}
else
{
WinConsole.WriteLine("Unvalid Input expected was true | false ");
WinConsole.WriteLine();
}
break;
case @"\exclude":
if (parts.Length == 2)
{
bool result;
bool.TryParse(parts[1], out result);
selectedTable.Exclude = result;
}
else
{
if (parts.Length == 3)
{
bool result;
bool.TryParse(parts[1], out result);
var column = selectedTable.ColumnInfos.FirstOrDefault(s => s.ColumnInfo.ColumnName == parts[2]);
if (column == null)
{
WinConsole.WriteLine("Unvalid Input expected was [ColumnName] ");
WinConsole.WriteLine();
break;
}
column.Exclude = result;
}
else
{
WinConsole.WriteLine("Unvalid Input expected was true | false ");
WinConsole.WriteLine();
}
}
break;
case @"\fallack":
if (parts.Length == 2)
{
bool result;
bool.TryParse(parts[1], out result);
selectedTable.CreateFallbackProperty = result;
}
else
{
WinConsole.WriteLine("Unvalid Input expected was true | false ");
WinConsole.WriteLine();
}
break;
case @"\createloader":
if (parts.Length == 2)
{
bool result;
bool.TryParse(parts[1], out result);
selectedTable.CreateDataRecordLoader = result;
}
else
{
WinConsole.WriteLine("Unvalid Input expected was true | false ");
WinConsole.WriteLine();
}
break;
case @"\createSelect":
if (parts.Length == 2)
{
bool result;
bool.TryParse(parts[1], out result);
selectedTable.CreateSelectFactory = result;
}
else
{
WinConsole.WriteLine("Unvalid Input expected was true | false ");
WinConsole.WriteLine();
}
break;
case @"\stats":
WinConsole.WriteLine("Name = {0}", selectedTable.Info.TableName);
WinConsole.WriteLine("Cs class Name = {0}", selectedTable.GetClassName());
WinConsole.WriteLine("Exclude = {0}", selectedTable.Exclude);
WinConsole.WriteLine("Create Fallback Property = {0}", selectedTable.CreateFallbackProperty);
WinConsole.WriteLine("\t Create Select Factory = {0}", selectedTable.CreateSelectFactory);
WinConsole.WriteLine("\t Create Dataloader = {0}", selectedTable.CreateDataRecordLoader);
WinConsole.WriteLine("Columns");
foreach (var columnInfo in selectedTable.ColumnInfos)
{
WinConsole.WriteLine("--------------------------------------------------------");
WinConsole.WriteLine("\t Name = {0}", columnInfo.ColumnInfo.ColumnName);
WinConsole.WriteLine("\t Is Primary Key = {0}", columnInfo.PrimaryKey);
WinConsole.WriteLine("\t Cs Property Name = {0}", columnInfo.GetPropertyName());
WinConsole.WriteLine("\t Position From Top = {0}", columnInfo.ColumnInfo.PositionFromTop);
WinConsole.WriteLine("\t Nullable = {0}", columnInfo.ColumnInfo.Nullable);
WinConsole.WriteLine("\t Cs Type = {0}", columnInfo.ColumnInfo.TargetType.Name);
WinConsole.WriteLine("\t forgeinKey Type = {0}", columnInfo.ForgeinKeyDeclarations);
WinConsole.WriteLine("\t Is Enum Type = {0}", columnInfo.EnumDeclaration != null);
}
break;
case @"\back":
RenderMenu();
return;
default:
break;
}
}
RenderTableMenu(selectedTable);
}
public FooTest()
{
var access = new DbAccessLayer(DbAccessType.MsSql,
"Data Source=(localdb)\\Projects;Initial Catalog=Northwind;Integrated Security=True;");
var select = access.Select <Foo>("SomeName");
}
public TableInformations[] GetTables()
{
return(_db.Select <TableInformations>());
}