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