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