public List<Table> GetSchemaInformation(string connectionInfo) { List<Table> tables = new List<Table>(); OleDbConnection con = new OleDbConnection(connectionInfo); con.Open(); // Get all tables in the Database DataTable tableData = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); foreach (DataRow row in tableData.Rows) { Table t = new Table(); t.Schema = row[1].ToString(); t.Name = row[2].ToString(); // Get all columns in the Table DataTable columns = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, row[2], null }); foreach (DataRow row1 in columns.Rows) { Column c = new Column(); c.Name = row1[3].ToString(); c.DataType = (OleDbType)row1["DATA_TYPE"]; c.IsNullable = (bool)row1["IS_NULLABLE"]; if (row1["CHARACTER_MAXIMUM_LENGTH"] != null && !String.IsNullOrEmpty(row1["CHARACTER_MAXIMUM_LENGTH"].ToString())) c.MaxLength = int.Parse(row1["CHARACTER_MAXIMUM_LENGTH"].ToString()); t.Columns.Add(c); } // Get all the Primary Key Columns for the Table DataTable primaryKeys = con.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new object[] { null, row[1], row[2] }); foreach (DataRow row2 in primaryKeys.Rows) { t.Columns.Where(x => x.Name == row2[3].ToString()).First().IsPrimaryKey = true; //t.Columns.Where(x => x.Name == row2[3].ToString()).First().PrimaryKeys = GetPrimaryKeysForTable(connectionInfo, t); } t.RecordCount = GetRecordCount(connectionInfo, t); tables.Add(t); } // Get all the Forign keys and table contraints foreach (Table t in tables) { DataTable forignKeys = con.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, new object[] { null, t.Schema, t.Name }); foreach (DataRow row in forignKeys.Rows) { var table = tables.Where(x => x.Name == row[8].ToString()).First(); // TODO: I think this needs schema + table name table.Columns.Where(x => x.Name == row[9].ToString()).First().IsForignKey = true; } DataTable constraints = con.GetOleDbSchemaTable(OleDbSchemaGuid.Check_Constraints_By_Table, new object[] { null, t.Schema, t.Name }); foreach (DataRow row in constraints.Rows) { var table = tables.Where(x => x.Name == row["TABLE_NAME"].ToString()).First(); // TODO: I think this needs schema + table name List<string> columnNames = GetTableNamesFromSql(row["CHECK_CLAUSE"].ToString()); foreach (string column in columnNames) { Column c = table.Columns.Where(x => x.Name.Equals(column, StringComparison.OrdinalIgnoreCase)).FirstOrDefault(); if (c != null) c.IsPartOfConstraint = true; } } } con.Close(); return tables; }
public HashSet<string> GetDataForColumn(ConnectionString connectionString, Table table, Column column) { return _databaseProvider.GetDataForColumn(connectionString.GetConnectionString(), table, column); }
public HashSet<string> GetDataForColumn(string connectionInfo, Table table, Column column) { HashSet<string> data = new HashSet<string>(); OleDbConnection con = new OleDbConnection(connectionInfo); OleDbCommand command = con.CreateCommand(); command.CommandText = string.Format("SELECT [{0}] FROM [{1}]", column.Name, table.FullTableName); command.CommandTimeout = 0; try { con.Open(); OleDbDataReader reader = command.ExecuteReader(); while (reader.Read()) { data.Add(reader[0].ToString().Trim().ToUpper()); } } finally { con.Close(); } return data; }
public Column GenerateDataForColumn(Column column, object[] data, HashSet<string> existingColumnData) { if (column.GeneratorType != null && column.GeneratorType != SystemConstants.DefaultGuid) { List<IGeneratorInfo> infos = ObjectLocator.GetAllInstances<IGeneratorInfo>(); List<IGeneratorBuilder> builders = ObjectLocator.GetAllInstances<IGeneratorBuilder>(); Type generatorType = infos.Where(x => x.Id == column.GeneratorType.Value).First().Type; IGeneratorBuilder builder = builders.Where(x => x.GeneratorId == column.GeneratorType.Value).First(); var generator = ObjectLocator.GetInstance(generatorType); List<string> generatedData = null; if (generator != null) generatedData = builder.BuildGenerator(generator, data, existingColumnData); #region Old Swtich Based Code //switch (column.GeneratorType) //{ // case GeneratorTypes.AccountNumber: // generatedData = ObjectLocator.GetInstance<IAccountNumberGenerator>().GenerateAccountNumbers((double)data[0]); // break; // case GeneratorTypes.Address: // generatedData = ObjectLocator.GetInstance<IAddressGenerator>().GenerateAddressLine1s((double)data[0]); // break; // case GeneratorTypes.Character: // bool charBool = false; // try // { // charBool = (bool)data[1]; // } // catch { } // var characters = ObjectLocator.GetInstance<ICharacterGenerator>().GenerateRandomCharacters((double)data[0], charBool); // generatedData = new List<string>(); // foreach (var c in characters) // { // generatedData.Add(c.ToString()); // } // break; // case GeneratorTypes.City: // generatedData = ObjectLocator.GetInstance<ICityGenerator>().GenerateCityNames((double)data[0]); // break; // case GeneratorTypes.CompanyName: // generatedData = ObjectLocator.GetInstance<ICompanyNameGenerator>().GenerateCompanyNames((double)data[0], existingColumnData); // break; // case GeneratorTypes.Country: // generatedData = ObjectLocator.GetInstance<ICountryGenerator>().GenerateCountryNames((double)data[0]); // break; // case GeneratorTypes.Date: // var dates = ObjectLocator.GetInstance<IDateGenerator>().GenerateDate((double)data[0], (DateTime)data[1], (DateTime)data[2]); // generatedData = new List<string>(); // foreach (var d in dates) // { // generatedData.Add(d.ToString()); // } // break; // case GeneratorTypes.EmailAddress: // generatedData = ObjectLocator.GetInstance<IEmailAddressGenerator>().GenerateEmailAddresses((double)data[0], existingColumnData); // break; // case GeneratorTypes.FirstName: // generatedData = ObjectLocator.GetInstance<IFirstNameGenerator>().GenerateFirstNames((double)data[0]); // break; // case GeneratorTypes.LastName: // generatedData = ObjectLocator.GetInstance<ILastNameGenerator>().GenerateLastNames((double)data[0]); // break; // case GeneratorTypes.Login: // generatedData = ObjectLocator.GetInstance<ILoginNameGenerator>().GenerateLoginNames((double)data[0]); // break; // case GeneratorTypes.Number: // var numbers = ObjectLocator.GetInstance<INumberGenerator>().GenerateRandomNumbers(double.Parse(data[0].ToString()), int.Parse(data[1].ToString()), int.Parse(data[2].ToString())); // generatedData = new List<string>(); // foreach (var n in numbers) // { // generatedData.Add(n.ToString()); // } // break; // case GeneratorTypes.PhoneNumber: // bool phoneBool = false; // try // { // phoneBool = (bool)data[1]; // } // catch { } // generatedData = ObjectLocator.GetInstance<IPhoneNumberGenerator>().GeneratePhoneNumbers((double)data[0], phoneBool); // break; // case GeneratorTypes.SSN: // generatedData = ObjectLocator.GetInstance<ISsnGenerator>().GenerateSocialSecurityNumbers((double)data[0]); // break; // case GeneratorTypes.State: // generatedData = ObjectLocator.GetInstance<IStateGenerator>().GenerateStateNames((double)data[0]); // break; // case GeneratorTypes.String: // bool stringBool1 = false; // try // { // stringBool1 = (bool)data[3]; // } // catch { } // bool stringBool2 = false; // try // { // stringBool2 = (bool)data[4]; // } // catch { } // generatedData = ObjectLocator.GetInstance<IStringGenerator>().GenerateRandomStrings(double.Parse(data[0].ToString()), int.Parse(data[1].ToString()), int.Parse(data[2].ToString()), stringBool1, stringBool2, existingColumnData); // break; // case GeneratorTypes.Url: // bool data2 = false; // try // { // data2 = (bool)data[1]; // } // catch { } // generatedData = ObjectLocator.GetInstance<IUrlGenerator>().GenerateUrls((double)data[0], data2, existingColumnData); // break; // case GeneratorTypes.ZipCode: // bool data1 = false; // try // { // data1 = (bool)data[1]; // } // catch { } // generatedData = ObjectLocator.GetInstance<IZipCodeGenerator>().GenerateZipCodes((double)data[0], data1); // break; // case GeneratorTypes.Clear: // generatedData = new List<string>(); // break; // case GeneratorTypes.FullName: // bool fullNameBool1 = false; // try // { // fullNameBool1 = (bool)data[1]; // } // catch { } // bool fullNameBool2 = false; // try // { // fullNameBool2 = (bool)data[2]; // } // catch { } // generatedData = ObjectLocator.GetInstance<IFullNameGenerator>().GenerateFullNames((double)data[0], fullNameBool1, fullNameBool2); // break; // case GeneratorTypes.Symbol: // generatedData = ObjectLocator.GetInstance<ISymbolGenerator>().GenerateSymbols((double)data[0], existingColumnData); // break; // case GeneratorTypes.SecurityName: // generatedData = ObjectLocator.GetInstance<ISecurityNameGenerator>().GenerateSecurityNames((double)data[0], existingColumnData); // break; // case GeneratorTypes.Hex: // generatedData = ObjectLocator.GetInstance<IHexGenerator>().GenerateHexNumbers((double)data[0]); // break; // case GeneratorTypes.Guid: // generatedData = ObjectLocator.GetInstance<IGuidGenerator>().GenerateGuids((double)data[0]); // break; // case GeneratorTypes.Token: // generatedData = ObjectLocator.GetInstance<ITokenReplacementGenerator>().GenerateReplacedStrings((double)data[0], (string)data[1], existingColumnData); // break; // default: // throw new InvalidOperationException(string.Format("Cannot generate data for type {0}", column.GeneratorType)); //} #endregion Old Swtich Based Code column.Data = generatedData; } return column; }