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