private string GenerateInsertScript(string countryName)
{
countryRepo.Setup(repo => repo.GetRandom()).Returns(new Country {
Name = countryName
});
countryColumnValueStrategy.Setup(vs => vs.GetValue(It.IsAny <CountryColumnInfo>(), It.IsAny <object>()))
.Returns(countryName);
var strategy = new SqlServerDataGenStrategy(columnStrategies.Object, countryRepo.Object);
var tableInfo = new TableInfo
{
Name = "SomeTable",
Columns = new List <ColumnInfo>
{
new CountryColumnInfo {
IsNullable = false, SystemColumnType = SystemColumnType.String
}
}
};
var options = new InsertScriptGenerationOptions {
RowCount = 1
};
Task <string> scriptTask = strategy.GenerateInsertScriptAsync(tableInfo, options);
scriptTask.Wait();
return(scriptTask.Result);
}
private static void GenerateScript(IDataGenStrategy strategy, TableInfo tableInfo,
InsertScriptGenerationOptions generationOptions)
{
Stopwatch stopwatch = Stopwatch.StartNew();
Task <string> task = strategy.GenerateInsertScriptAsync(tableInfo, generationOptions);
task.Wait();
stopwatch.Stop();
Console.WriteLine(task.Result);
Console.WriteLine("Elapsed time: {0}", stopwatch.Elapsed);
Console.WriteLine();
}
public async Task TestGenerateInsertScript()
{
// Arrange
const string tableName = "dbo.Test01";
tableToDrop = tableName;
string createTableScript =
$"create table {tableName} (" +
"Id int identity(1,1) NOT NULL, " +
"[Name] [varchar](128) NOT NULL," +
"IntColumn int null" +
") ON [PRIMARY]";
InsertScriptGenerationOptions generationOptions = new InsertScriptGenerationOptions {
RowCount = 8
};
IDataGenStrategy strategy = CreateSqlServerDataGenStrategy();
// Act
TableInfo tableInfo = await strategy.GetTableInfoAsync(createTableScript);
string insertScript = await strategy.GenerateInsertScriptAsync(tableInfo, generationOptions);
// Assert
Log(createTableScript);
Log(insertScript);
Assert.IsTrue(tableInfo.Columns.Count > 0);
string createInsertScript = createTableScript + Environment.NewLine + insertScript;
ExecuteNonQuery(createInsertScript);
using (SqlCommand cmd = new SqlCommand($"select * from {tableName}", Connection))
{
cmd.CommandType = CommandType.Text;
using (SqlDataReader reader = cmd.ExecuteReader())
{
const int colId = 0;
const int colName = 1;
Assert.AreEqual(3, reader.FieldCount);
Assert.AreEqual("int", reader.GetDataTypeName(colId));
Assert.AreEqual("varchar", reader.GetDataTypeName(colName));
int rowCount = 0;
while (reader.Read())
{
rowCount++;
Assert.IsFalse(reader.IsDBNull(colId));
Assert.AreEqual(rowCount, reader.GetInt32(colId));
Assert.IsFalse(reader.IsDBNull(colName));
}
Assert.AreEqual(generationOptions.RowCount, rowCount);
}
}
}
protected string GenerateInsertScript(TableInfo tableInfo, InsertScriptGenerationOptions generationOptions)
{
if (tableInfo?.Columns == null)
{
return(null);
}
var columns = tableInfo.Columns
.Where(c => !c.IsComputed && !c.IsIdentity)
.ToList();
Dictionary <ColumnInfo, object> contexts = new Dictionary <ColumnInfo, object>();
foreach (ColumnInfo columnInfo in columns)
{
if (columnInfo is AutoincColumnInfo)
{
contexts.Add(columnInfo, new AutoincContext());
}
}
string columnNames = string.Join(", ", columns.Select(c => c.Name));
StringBuilder sb = new StringBuilder();
sb.Append($"insert into {tableInfo.Name} ({columnNames}) values ");
for (int i = 0; i < generationOptions.RowCount; i++)
{
if (i > 0)
{
sb.Append(",");
}
sb.AppendLine();
Country country = CountryRepo.GetRandom();
StringBuilder sbValues = new StringBuilder();
foreach (ColumnInfo columnInfo in columns)
{
if (sbValues.Length != 0)
{
sbValues.Append(", ");
}
object context = contexts.ContainsKey(columnInfo) ? contexts[columnInfo] : country;
string value = GetColumnValue(columnInfo, context);
if (!string.IsNullOrWhiteSpace(value))
{
sbValues.Append(value);
}
}
sb.Append($"({sbValues})");
}
return(sb.ToString());
}
public async Task <HttpResponseMessage> GetGeneratedData(HttpRequestMessage request, [FromBody] TableInfoViewModel vm)
{
IDataGenStrategy dataGenStrategy = DataGenStrategies[vm.SqlType];
var generationOptions = new InsertScriptGenerationOptions {
RowCount = vm.EntityCount
};
string generatedData = await dataGenStrategy.GenerateInsertScriptAsync(vm.TableInfo, generationOptions);
GeneratedDataViewModel generatedDataViewModel = new GeneratedDataViewModel
{
GeneratedData = generatedData
};
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, generatedDataViewModel);
return(response);
}
static void Main(string[] args)
{
/*RunAsync().Wait();
* Console.ReadLine();*/
Container.Initialize();
using (var scope = Container.InternalContainer.BeginLifetimeScope())
{
IDataGenStrategy strategy = scope.ResolveKeyed <IDataGenStrategy>(SqlType.SqlServer);
Task <TableInfo> tableInfoTask = strategy.GetTableInfoAsync(
"create table dbo.Test01 (" +
"Id int identity(1,1) not null, " +
//"[FirstName] [varchar](128) not null, " +
//"[LastName] [varchar](128) not null, " +
"Country varchar(128) not null, " +
"City varchar(128) not null, " +
"RegionName varchar(128) not null, " +
//"AddressLine1 varchar(128) not null, " +
//"IntColumn int not null" +
") on [primary]");
tableInfoTask.Wait();
TableInfo tableInfo = tableInfoTask.Result;
Console.WriteLine("Table name = {0}", tableInfo.Name);
Console.WriteLine("-----");
Console.WriteLine("Columns:");
foreach (ColumnInfo columnInfo in tableInfo.Columns)
{
Console.WriteLine(
"Name = {0}; Precision = {1}; Scale = {2}; IsNullable = {3}; IsIdentity = {4}; IsComputed = {5}",
columnInfo.Name, columnInfo.Precision, columnInfo.Scale,
columnInfo.IsNullable, columnInfo.IsIdentity, columnInfo.IsComputed);
Console.WriteLine();
}
Console.WriteLine("--------------------------------------");
AppDomain.CurrentDomain.SetData("DataDirectory", @"..\..\..\APaers.DataGen.Api\App_Data");
InsertScriptGenerationOptions generationOptions = new InsertScriptGenerationOptions {
RowCount = 3
};
GenerateScript(strategy, tableInfo, generationOptions);
GenerateScript(strategy, tableInfo, generationOptions);
}
Console.WriteLine("Press any key to continue...");
Console.ReadKey();
}
public async Task <IHttpActionResult> GetGeneratedData([FromBody] TableInfoViewModel vm)
{
IDataGenStrategy dataGenStrategy = DataGenStrategies[vm.SqlType];
var generationOptions = new InsertScriptGenerationOptions {
RowCount = vm.EntityCount
};
var tableInfo = new TableInfo {
Name = vm.Name, Columns = vm.Columns
};
string generatedData = await dataGenStrategy.GenerateInsertScriptAsync(tableInfo, generationOptions);
GeneratedDataViewModel generatedDataViewModel = new GeneratedDataViewModel
{
GeneratedData = generatedData
};
return(Ok(generatedDataViewModel));
}
public async Task TestGenerateInsertScript_DifferentColumnTypes_DefaultFormats()
{
// Arrange
const string tableName = "dbo.Test01";
tableToDrop = tableName;
string createTableScript =
$"create table {tableName} (" +
"Id int identity(1,1) not null, " +
"SomeUniqueId uniqueidentifier not null, " +
"SomeGuid varchar(64) not null, " +
"Country varchar(128) not null, " +
"RegionName nvarchar(128) not null, " +
"City nvarchar(128) not null, " +
"AddressLine1 varchar(256) not null, " +
"AddressLine2 varchar(256) not null, " +
"LatitudeLongitude varchar(32) not null, " +
"SomeTextColumn varchar(128) not null, " +
"PersonFullName varchar(16) not null, " +
"Email varchar(64) not null, " +
"Telephone varchar(16) not null, " +
"PassportNo varchar(16) not null, " +
"PostalCode varchar(12) not null, " +
"WholeValue int not null, " +
"DecimalValue decimal not null, " +
"FloatValue float not null, " +
"BoolValue bit not null, " +
"DateTimeValue datetime not null, " +
") on [primary];";
InsertScriptGenerationOptions generationOptions = new InsertScriptGenerationOptions {
RowCount = 10
};
IDataGenStrategy strategy = CreateSqlServerDataGenStrategy();
// Act
TableInfo tableInfo = await strategy.GetTableInfoAsync(createTableScript);
string insertScript = await strategy.GenerateInsertScriptAsync(tableInfo, generationOptions);
// Assert
Log(createTableScript);
Log(insertScript);
Assert.IsTrue(tableInfo.Columns.Count > 0);
string createInsertScript = createTableScript + Environment.NewLine + insertScript;
ExecuteNonQuery(createInsertScript);
using (SqlCommand cmd = new SqlCommand($"select * from {tableName}", Connection))
{
cmd.CommandType = CommandType.Text;
using (SqlDataReader reader = cmd.ExecuteReader())
{
int col = 0;
int colId = col++;
int colGuid = col++;
int colGuidString = col++;
int colCountry = col++;
int colRegion = col++;
int colCity = col++;
int colAddressLine1 = col++;
int colAddressLine2 = col++;
int colLatitudeLongitude = col++;
int colRandomText = col++;
int colFullName = col++;
int colEmail = col++;
int colPhone = col++;
int colPassport = col++;
int colPostalCode = col++;
int colInt = col++;
int colDecimal = col++;
int colFloat = col++;
int colBoolean = col++;
int colDateTime = col++;
Assert.AreEqual(col, reader.FieldCount);
Assert.AreEqual("int", reader.GetDataTypeName(colId));
Assert.AreEqual("uniqueidentifier", reader.GetDataTypeName(colGuid));
Assert.AreEqual("varchar", reader.GetDataTypeName(colGuidString));
Assert.AreEqual("varchar", reader.GetDataTypeName(colCountry));
Assert.AreEqual("nvarchar", reader.GetDataTypeName(colRegion));
Assert.AreEqual("nvarchar", reader.GetDataTypeName(colCity));
Assert.AreEqual("varchar", reader.GetDataTypeName(colAddressLine1));
Assert.AreEqual("varchar", reader.GetDataTypeName(colAddressLine2));
Assert.AreEqual("varchar", reader.GetDataTypeName(colLatitudeLongitude));
Assert.AreEqual("varchar", reader.GetDataTypeName(colRandomText));
Assert.AreEqual("varchar", reader.GetDataTypeName(colFullName));
Assert.AreEqual("varchar", reader.GetDataTypeName(colEmail));
Assert.AreEqual("varchar", reader.GetDataTypeName(colPhone));
Assert.AreEqual("varchar", reader.GetDataTypeName(colPassport));
Assert.AreEqual("varchar", reader.GetDataTypeName(colPostalCode));
Assert.AreEqual("int", reader.GetDataTypeName(colInt));
Assert.AreEqual("decimal", reader.GetDataTypeName(colDecimal));
Assert.AreEqual("float", reader.GetDataTypeName(colFloat));
Assert.AreEqual("bit", reader.GetDataTypeName(colBoolean));
Assert.AreEqual("datetime", reader.GetDataTypeName(colDateTime));
int rowCount = 0;
while (reader.Read())
{
for (int i = 0; i < col; i++)
{
Assert.IsFalse(reader.IsDBNull(i));
}
rowCount++;
Assert.AreEqual(rowCount, reader.GetInt32(colId));
Guid guid = reader.GetGuid(colGuid);
Assert.AreNotEqual(Guid.Empty, guid);
string guidString = reader.GetString(colGuidString);
Guid guidFromString = Guid.Parse(guidString);
Assert.AreNotEqual(Guid.Empty, guidFromString);
Assert.AreNotEqual(guid, guidFromString);
string country = reader.GetString(colCountry);
Assert.IsTrue(Countries.ContainsKey(country), $"Country not found: {country}");
string region = reader.GetString(colRegion);
Assert.IsTrue(Regions.ContainsKey(region), $"Region not found: {region}. (Country: {country})");
string city = reader.GetString(colCity);
Assert.IsTrue(Cities.ContainsKey(city), $"City not found: {city}. (Country: {country}, Region: {region})");
string addressLine1 = reader.GetString(colAddressLine1);
Assert.IsFalse(string.IsNullOrWhiteSpace(addressLine1));
string addressLine2 = reader.GetString(colAddressLine2);
Assert.IsFalse(string.IsNullOrWhiteSpace(addressLine2));
string latitudeLongitude = reader.GetString(colLatitudeLongitude);
Assert.IsFalse(string.IsNullOrWhiteSpace(latitudeLongitude));
string randomText = reader.GetString(colRandomText);
Assert.IsFalse(string.IsNullOrWhiteSpace(randomText));
string fullName = reader.GetString(colFullName);
Assert.AreEqual(1, fullName.Count(c => c == ' '), $"Generated Full name: {fullName}");
string email = reader.GetString(colEmail);
Assert.IsTrue(IsValidEmail(email), $"Generated Email: {email}");
string phone = reader.GetString(colPhone);
Regex regex = new Regex(@"[\d\s]+");
Assert.IsTrue(regex.IsMatch(phone), $"Generated Phone: {phone}");
string passport = reader.GetString(colPassport);
regex = new Regex(@"[\d\s]+");
Assert.IsTrue(regex.IsMatch(passport), $"Generated Passport: {passport}");
string postalCode = reader.GetString(colPostalCode);
regex = new Regex(@"[\d]+");
Assert.IsTrue(regex.IsMatch(postalCode), $"Generated Postal code: {postalCode}");
int intValue = reader.GetInt32(colInt);
Assert.IsTrue(intValue >= 0);
decimal decimalNumber = reader.GetDecimal(colDecimal);
Assert.IsTrue(decimalNumber >= 0);
double floatNumber = reader.GetDouble(colFloat);
Assert.IsTrue(floatNumber >= 0);
bool boolValue = reader.GetBoolean(colBoolean);
Assert.IsTrue(boolValue || !boolValue);
DateTime dateTime = reader.GetDateTime(colDateTime);
Assert.IsTrue(dateTime > DateTime.Now.AddYears(-10));
Assert.IsTrue(dateTime < DateTime.Now.AddYears(10));
}
Assert.AreEqual(generationOptions.RowCount, rowCount);
}
}
}
public async Task <string> GenerateInsertScriptAsync(TableInfo tableInfo, InsertScriptGenerationOptions generationOptions)
{
return(await Task.Run(() => GenerateInsertScript(tableInfo, generationOptions)));
}
