internal FullTableImage GenerateData(DatabaseTable table, bool includeWhenMatchedClause, bool useDefaultValues) { MetaDataAccess metaAccess = new MetaDataAccess(); ConstraintAccess consAccess = new ConstraintAccess(); DataAccess dataAccess = new DataAccess(); FullTableImage full = new FullTableImage(); full.Table = table; full.Constraints = consAccess.GetConstraintForTable(table); full.Schema = metaAccess.GetSchemaForTable(table); foreach (Column col in full.Schema.Columns) { if (col.IsIdentity) { full.Table.HasIdentity = true; full.Table.IdentityColumn = col.ColumnName; } if (!col.IsPartOfPrimaryKey && full.Constraints.Exists(x => x.IsUnique && x.Columns.Exists(c => c.ColumnName == col.ColumnName))) { full.Table.HasUniqueKeyOtherThanPK = true; } if (col.IsPartOfPrimaryKey && col.IsIdentity) { full.Table.HasPKSameAsIdentity = true; } } // We want to include the WHEN MATCHED clause to enable updates of existing rows. full.Table.IncludeWhenMatchedClause = includeWhenMatchedClause; // If the entire table is made up by the primary key then we cannot do any updates to the table. if (full.Constraints.Where(x => x.IsPrimaryKey).Count() > 0) { if (full.Constraints.Where(x => x.IsPrimaryKey).SingleOrDefault().Columns.Count == full.Schema.Columns.Count()) { full.Table.IncludeWhenMatchedClause = false; } if (full.Constraints.Where(x => x.IsUnique).Count() == 1) { if (full.Constraints.Where(x => x.IsUnique).Single().Columns.Count == full.Schema.Columns.Count()) { full.Table.IncludeWhenMatchedClause = false; } } } // Use DEFAULT values instead of the saved data for columns that have DEFAULT specified. full.Table.UseDefaultValues = useDefaultValues; // Use SET IDENTITY INSERT ON if // The identity is the only unique column in the table. // Do NOT use SET IDENTITY INSERT ON if // Identity is not the only column that is unique // PK is not identity // There exists a single unique constraint on the table (other than the identity) // If there are multiple unique constraints on the table then that should result in a Manual Editing Required Exception (or something) int uniqueCount = (from a in full.Constraints where a.IsPrimaryKey == false && a.IsUnique == true select a).Count(); full.Table.UseIdentityInsert = true; if (!full.Table.HasIdentity) { full.Table.UseIdentityInsert = false; } if (uniqueCount == 1 && full.Table.HasUniqueKeyOtherThanPK) { full.Table.UseIdentityInsert = false; } if (full.Table.UseIdentityInsert) { full.Table.UseAsPrimaryKeyColumns = full.Schema.Columns.Where(x => x.IsPartOfPrimaryKey).ToList(); } else { if (uniqueCount > 0) { foreach (var cols in full.Constraints.Where(x => x.IsUnique).Select(constr => constr.Columns)) { foreach (var col in cols) { full.Table.UseAsPrimaryKeyColumns.Add(col); } } } else { full.Table.UseAsPrimaryKeyColumns.AddRange( full.Schema.Columns.Where(c => c.IsPartOfPrimaryKey)); } } if (full.Table.UseIdentityInsert) { full.Table.InsertColumns = full.Schema.Columns; full.Table.UpdateColumns = full.Schema.Columns.Where( c => c.IsPartOfPrimaryKey == false ).ToList(); } else { if (full.Table.HasIdentity) { full.Table.InsertColumns = full.Schema.Columns.Where( c => (full.Table.HasIdentity ^ full.Table.IdentityColumn == c.ColumnName) ).ToList(); full.Table.UpdateColumns = full.Schema.Columns.Where( c => !full.Table.UseAsPrimaryKeyColumns.Contains(c) && (full.Table.HasIdentity ^ full.Table.IdentityColumn == c.ColumnName) ).ToList(); } else { full.Table.InsertColumns = full.Schema.Columns; full.Table.UpdateColumns = full.Schema.Columns.Where(c => c.IsPartOfPrimaryKey != true).ToList(); } } full.Rows = dataAccess.GetDataForTable(table, full.Schema.Columns); return(full); }
static void Main(string[] args) { try { DataGenerator gen = new DataGenerator(); MetaDataAccess mea = new MetaDataAccess(); List <DatabaseTable> tables = new List <DatabaseTable>(); Console.WriteLine("Using this connection:"); Console.WriteLine(System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString); Console.WriteLine(); Console.WriteLine("Enter the name of the table you wish to generate data for, use <schema_name>.<table_name> format. Leave blank to generate for ALL tables: "); string manualInput = Console.ReadLine(); // manualInput = "Common_Cnfg.PermissionInRole"; Console.WriteLine("Include WHEN MATCHED clause to enable updating of existing rows? [Y/N]"); bool includeWhenMatchedClause = Console.ReadKey().KeyChar.ToString().ToLower() == "y" ? true : false; Console.WriteLine(); Console.WriteLine("Use DEFAULT values instead of saved data for datetime columns? [Y/N]"); bool useDefaultValues = Console.ReadKey().KeyChar.ToString().ToLower() == "y" ? true : false; Console.WriteLine(); if (string.IsNullOrEmpty(manualInput)) { tables = mea.GetAllTables(); } else { string schemaName = string.Empty; if (!manualInput.Contains('.')) { schemaName = "dbo"; } else { schemaName = manualInput.Substring(0, manualInput.IndexOf('.')); } string tableName = manualInput.Substring(manualInput.IndexOf('.') + 1); tables.Add(new DatabaseTable { TableName = tableName, SchemaName = schemaName }); } Console.WriteLine("Excluding tables with no data"); Console.WriteLine("Creating scripts"); Console.WriteLine("Rows\t\t Table Name"); int i = 1; foreach (var table in tables) { try { FullTableImage full = gen.GenerateData(table, includeWhenMatchedClause, useDefaultValues); if (full.Rows.Count > 0) { Console.WriteLine("{0}\t\t {1}", full.Rows.Count, table.ToString()); string folderName = System.Configuration.ConfigurationManager.AppSettings["outputFolder"]; string fileName = string.Format("{2}{3}_{0}.{1}.sql", table.SchemaName, table.TableName, folderName, i++ *(1000 - i * 10) + 1000000); gen.SerializeAndTransform(full, fileName); } } catch (Exception) { Console.WriteLine("Error while processing " + table.FullName); throw; } } } catch (Exception ex) { Console.WriteLine(ex.ToString()); Console.ReadKey(); } }