public void CreateTSQL(SampleSQLData sample, IList <object> sqlList, IDictionary <string, string> parameters)
{
string baseSQL = @"DELETE FROM {0} WHERE {1};";
string dataSQL = string.Empty;
foreach (var row in sample.Rows)
{
string fields = string.Empty;
foreach (var key in sample.Keys)
{
Common.Schema schema = sample.Schema.SingleOrDefault(s => s.Column == key);
string value = JSON.DeserializeDynamic(row.ToString())[schema.Column];
if (parameters.ContainsKey(value))
{
value = parameters[value];
}
string parsedValue = "{0}".FormatWith(ParserDataType.ParseSQL((ParserDataType.DataType)Enum.Parse(typeof(ParserDataType.DataType), schema.Type, true), value));
fields += "{0}={1} AND ".FormatWith(schema.Column, parsedValue);
}
dataSQL += baseSQL.FormatWith(sample.Table, fields.Substring(0, fields.LastIndexOf("AND")));
}
if (!string.IsNullOrEmpty(dataSQL))
{
sqlList.Add(dataSQL);
}
}
public void CreateTSQL(SampleSQLData sample, IList <object> sqlList)
{
string baseSQL = @"INSERT INTO {0} ({1}) VALUES ({2});";
string dataSQL = string.Empty;
foreach (var row in sample.Rows)
{
string fields = string.Empty;
string values = string.Empty;
foreach (var schema in sample.Schema)
{
fields += "{0},".FormatWith(schema.Column);
string value = JSON.DeserializeDynamic(row.ToString())[schema.Column];
values += "{0},".FormatWith(ParserDataType.ParseSQL((ParserDataType.DataType)Enum.Parse(typeof(ParserDataType.DataType), schema.Type, true), value));
}
dataSQL += baseSQL.FormatWith(sample.Table, fields.Substring(0, fields.LastIndexOf(',')), values.Substring(0, values.LastIndexOf(',')));
}
if (!string.IsNullOrEmpty(dataSQL))
{
sqlList.Add(dataSQL);
}
}
public IList <object> CreateInsert(IList <object> list)
{
IList <SampleSQLData> convertedList = list.Cast <SampleSQLData>().ToList();
IList <object> insertList = new List <object>();
foreach (SampleSQLData sample in convertedList)
{
foreach (var fk in sample.FK_References)
{
SampleSQLData sampleDataFiltered = convertedList.Where(l => !_tablesLoaded.ContainsKey(fk.Table) && l.Table == fk.Table).SingleOrDefault();
if (sampleDataFiltered != null)
{
TSQLProcessor.Execute(new CreateInsertSQL(), sampleDataFiltered, insertList);
_tablesLoaded.Add(fk.Table, true);
}
}
if (!_tablesLoaded.ContainsKey(sample.Table))
{
TSQLProcessor.Execute(new CreateInsertSQL(), sample, insertList);
_tablesLoaded.Add(sample.Table, true);
}
}
return(insertList);
}
private void CreateInsertCommand(SampleSQLData sample, IList <object> insertList, IList <SampleSQLData> convertedList)
{
foreach (var fk in sample.FK_References)
{
SampleSQLData sampleDataFiltered = convertedList.SingleOrDefault(l => !_tablesLoaded.ContainsKey(fk.Table) && l.Table == fk.Table);
if (sampleDataFiltered != null)
{
CreateInsertCommand(sampleDataFiltered, insertList, convertedList);
}
}
if (!_tablesLoaded.ContainsKey(sample.Table))
{
TSQLProcessor.Execute(new CreateInsertSQL(), sample, insertList);
_tablesLoaded.Add(sample.Table, true);
}
}
public void CreateTSQL(SampleSQLData sample, IList <object> sqlList, IDictionary <string, string> parameters)
{
string baseSQL = @"INSERT INTO {0} ({1}) VALUES ({2});";
string dataSQL = string.Empty;
foreach (var row in sample.Rows)
{
dynamic dynObj = JsonConvert.DeserializeObject(row.ToString());
JObject jObj = (JObject)dynObj;
JToken jToken;
string value = string.Empty;
string fields = string.Empty;
string values = string.Empty;
foreach (var schema in sample.Schema)
{
fields += "{0},".FormatWith(schema.Column);
jObj.TryGetValue(schema.Column, out jToken);
value = jToken.ToString();
if (parameters.ContainsKey(value))
{
value = parameters[value];
}
values += "{0},".FormatWith(ParserDataType.ParseSQL((ParserDataType.DataType)Enum.Parse(typeof(ParserDataType.DataType), schema.Type, true), value));
}
dataSQL += baseSQL.FormatWith(sample.Table, fields.Substring(0, fields.LastIndexOf(',')), values.Substring(0, values.LastIndexOf(',')));
}
if (!string.IsNullOrEmpty(dataSQL))
{
sqlList.Add(dataSQL);
}
}
public void CreateTSQL(SampleSQLData sample, IList <object> sqlList)
{
string baseSQL = @"DELETE FROM {0} WHERE {1};";
string dataSQL = string.Empty;
foreach (var row in sample.Rows)
{
dynamic dynObj = JsonConvert.DeserializeObject(row.ToString());
JObject jObj = (JObject)dynObj;
string value = string.Empty;
string fields = string.Empty;
foreach (var key in sample.Keys)
{
Schema schema = sample.Schema.SingleOrDefault(s => s.Column == key);
if (!jObj.TryGetValue(schema.Column, out JToken jToken))
{
throw new ArgumentException($"Column '{schema.Column}' not found on data rows provided.");
}
value = jToken.ToString();
string parsedValue = "{0}".FormatWith(ParserDataType.ParseSQL((ParserDataType.DataType)Enum.Parse(typeof(ParserDataType.DataType), schema.Type, true), value));
fields += "{0}={1} AND ".FormatWith(schema.Column, parsedValue);
}
dataSQL += baseSQL.FormatWith(sample.Table, fields.Substring(0, fields.LastIndexOf("AND")));
}
if (!string.IsNullOrEmpty(dataSQL))
{
sqlList.Add(dataSQL);
}
}
public static void Execute(IGenerateTSQL sqlCommand, SampleSQLData sample, IList <object> sqlList)
{
sqlCommand.CreateTSQL(sample, sqlList);
}
public static void Execute(IGenerateTSQL sqlCommand, SampleSQLData sample, IList <object> sqlList, IDictionary <string, string> dynamicParameters)
{
sqlCommand.CreateTSQL(sample, sqlList, dynamicParameters);
}
