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> CreateDelete(IList <object> list, IDictionary <string, string> dynamicParameters) { IList <SampleMongoData> convertedList = list.Cast <SampleMongoData>().ToList(); IList <object> deleteList = new List <object>(); foreach (var sample in convertedList) { IDictionary <string, IList <BsonElement> > data = new Dictionary <string, IList <BsonElement> >(); IList <BsonElement> elementList = new List <BsonElement>(); foreach (var row in sample.Rows) { 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 (dynamicParameters.ContainsKey(value)) { value = dynamicParameters[value]; } elementList.Add(new BsonElement(schema.Column, ParserDataType.ParseMongo((ParserDataType.DataType)Enum.Parse(typeof(ParserDataType.DataType), schema.Type, true), value))); } } data.Add(sample.Collection, elementList); deleteList.Add(data); } return(deleteList); }
public IList <object> CreateInsert(IList <object> list, IDictionary <string, string> dynamicParameters) { IList <SampleMongoData> convertedList = list.Cast <SampleMongoData>().ToList(); IList <object> insertList = new List <object>(); foreach (var sample in convertedList) { if (!_collectionsLoaded.ContainsKey(sample.Collection)) { IDictionary <string, IList <BsonDocument> > data = new Dictionary <string, IList <BsonDocument> >(); IList <BsonDocument> docList = new List <BsonDocument>(); foreach (var row in sample.Rows) { BsonDocument document = new BsonDocument(); foreach (var schema in sample.Schema) { string value = string.Empty; if (schema.Type.ToLower() == ParserDataType.DataType.Array.ToString().ToLower() || schema.Type.ToLower() == ParserDataType.DataType.Document.ToString().ToLower()) { value = JSON.DeserializeDynamic(row.ToString())[schema.Column].ToString(); string key = value.Replace("\"", ""); if (dynamicParameters.ContainsKey(key)) { value = dynamicParameters[key]; } } else { value = JSON.DeserializeDynamic(row.ToString())[schema.Column]; if (dynamicParameters.ContainsKey(value)) { value = dynamicParameters[value]; } } document.Add(new BsonElement(schema.Column, ParserDataType.ParseMongo((ParserDataType.DataType)Enum.Parse(typeof(ParserDataType.DataType), schema.Type, true), value))); } docList.Add(document); } data.Add(sample.Collection, docList); insertList.Add(data); _collectionsLoaded.Add(sample.Collection, true); } } return(insertList); }
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 IList <object> CreateDelete(IList <object> list) { IList <SampleMongoData> convertedList = list.Cast <SampleMongoData>().ToList(); IList <object> deleteList = new List <object>(); foreach (var sample in convertedList) { IDictionary <string, IList <BsonElement> > data = new Dictionary <string, IList <BsonElement> >(); IList <BsonElement> elementList = new List <BsonElement>(); foreach (var row in sample.Rows) { dynamic dynObj = JsonConvert.DeserializeObject(row.ToString()); JObject jObj = (JObject)dynObj; string value = string.Empty; foreach (var key in sample.Keys) { Schema schema = sample.Schema.SingleOrDefault(s => s.Column == key); jObj.TryGetValue(schema.Column, out JToken jToken); value = jToken.ToString(); elementList.Add(new BsonElement(schema.Column, ParserDataType.ParseMongo((ParserDataType.DataType)Enum.Parse(typeof(ParserDataType.DataType), schema.Type, true), value))); } } if (string.IsNullOrEmpty(sample.Collection)) { throw new ArgumentException("Missing 'Collection' field from schema specification", "Collection"); } data.Add(sample.Collection, elementList); deleteList.Add(data); } return(deleteList); }
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 IList <object> CreateInsert(IList <object> list) { IList <SampleMongoData> convertedList = list.Cast <SampleMongoData>().ToList(); IList <object> insertList = new List <object>(); foreach (var sample in convertedList) { if (!_collectionsLoaded.ContainsKey(sample.Collection)) { IDictionary <string, IList <BsonDocument> > data = new Dictionary <string, IList <BsonDocument> >(); IList <BsonDocument> docList = new List <BsonDocument>(); int rowCount = 0; foreach (var row in sample.Rows) { dynamic dynObj = JsonConvert.DeserializeObject(row.ToString()); JObject jObj = (JObject)dynObj; BsonDocument document = new BsonDocument(); foreach (var schema in sample.Schema) { string value = string.Empty; if (schema.Type.ToLower() == ParserDataType.DataType.Array.ToString().ToLower() || schema.Type.ToLower() == ParserDataType.DataType.Document.ToString().ToLower()) { if (!jObj.TryGetValue(schema.Column, out JToken jToken)) { throw new SchemaException($"Column '{schema.Column}' not found in row {rowCount.ToString()}") { StatusCode = HttpStatusCode.BadRequest } } ; value = jToken.ToString(); } else { if (!jObj.TryGetValue(schema.Column, out JToken jToken)) { throw new SchemaException($"Column '{schema.Column}' not found in row {rowCount.ToString()}") { StatusCode = HttpStatusCode.BadRequest } } ; value = jToken.ToString(); } document.Add(new BsonElement(schema.Column, ParserDataType.ParseMongo((ParserDataType.DataType)Enum.Parse(typeof(ParserDataType.DataType), schema.Type, true), value))); } rowCount++; docList.Add(document); } if (string.IsNullOrEmpty(sample.Collection)) { throw new ArgumentException("Missing 'Collection' field from schema specification", "Collection"); } data.Add(sample.Collection, docList); insertList.Add(data); _collectionsLoaded.Add(sample.Collection, true); } } return(insertList); }