private void BuildInsertWithUDTProcedure(DbUtilities dbConn, string dataType, DataAccessDef accessDef) { string sqlCommand = $"DROP PROCEDURE IF EXISTS spInsert{dataType}; "; sqlCommand = sqlCommand + $"DROP TYPE IF EXISTS[dbo].[UDT{dataType}];"; dbConn.SQLExecute(sqlCommand); sqlCommand = ""; string sql = accessDef.Select; string table = Common.GetTable(sql); ColumnProperties attributeProperties = CommonDbUtilities.GetColumnSchema(dbConn, sql); string[] tableAttributes = Common.GetAttributes(sql); tableAttributes = tableAttributes.Where(w => w != "Id").ToArray(); CreateUserDefinedTypes(dbConn, attributeProperties, sql, dataType); string comma = ""; string attributes = ""; foreach (var word in tableAttributes) { string attribute = word.Trim(); attributes = attributes + comma + "[" + attribute + "]"; comma = ","; } sqlCommand = sqlCommand + $"CREATE PROCEDURE [dbo].[spInsert{dataType}] @rules UDT{dataType} readonly " + " AS BEGIN " + $" INSERT INTO dbo.{table}({attributes}) " + $" SELECT {attributes} FROM @rules;" + " END"; dbConn.SQLExecute(sqlCommand); }
private async Task InsertRule(RuleModel rule, string connectionString) { List <RuleModel> rules = new List <RuleModel>(); string userName = CommonDbUtilities.GetUsername(connectionString); rule.ModifiedBy = userName; rule.CreatedBy = userName; rule.RuleKey = GetRuleKey(rule, connectionString); rule.CreatedDate = DateTime.Now; rule.ModifiedDate = DateTime.Now; rules.Add(rule); await _ruleData.InsertRules(rules, connectionString); }
private void UpdateRule(RuleModel rule, string connectionString) { string userName = CommonDbUtilities.GetUsername(connectionString); rule.ModifiedBy = userName; string json = JsonConvert.SerializeObject(rule, Formatting.Indented); json = Common.SetJsonDataObjectDate(json, "ModifiedDate"); using (IDbConnection cnn = new SqlConnection(connectionString)) { var p = new DynamicParameters(); p.Add("@json", json); string sql = "dbo.spUpdateRules"; int recordsAffected = cnn.Execute(sql, p, commandType: CommandType.StoredProcedure); } }
private void BuildUpdateProcedure(DbUtilities dbConn, string dataType, DataAccessDef accessDef) { string comma; string attributes; string sqlCommand = $"DROP PROCEDURE IF EXISTS spUpdate{dataType} "; dbConn.SQLExecute(sqlCommand); sqlCommand = ""; string sql = accessDef.Select; string[] keys = accessDef.Keys.Split(','); string table = Common.GetTable(sql); ColumnProperties attributeProperties = CommonDbUtilities.GetColumnSchema(dbConn, sql); string[] tableAttributes = Helpers.Common.GetAttributes(sql); sqlCommand = sqlCommand + $"CREATE PROCEDURE spUpdate{dataType} "; sqlCommand = sqlCommand + "@json NVARCHAR(max) "; sqlCommand = sqlCommand + "AS "; sqlCommand = sqlCommand + "BEGIN "; sqlCommand = sqlCommand + $"SELECT "; comma = " "; attributes = ""; foreach (var word in tableAttributes) { string attribute = word.Trim(); attributes = attributes + comma + attribute; comma = ","; } sqlCommand = sqlCommand + attributes; sqlCommand = sqlCommand + " INTO #TempJson "; sqlCommand = sqlCommand + $" FROM OPENJSON(@json) "; comma = ""; attributes = " WITH ("; foreach (var word in tableAttributes) { string attribute = word.Trim(); string dataProperty = attributeProperties[attribute]; attributes = attributes + comma + attribute + " " + dataProperty + " '$." + attribute + "'"; comma = ","; } sqlCommand = sqlCommand + attributes; sqlCommand = sqlCommand + ") AS jsonValues "; sqlCommand = sqlCommand + $" UPDATE A "; sqlCommand = sqlCommand + $" SET "; comma = " "; attributes = ""; foreach (var word in tableAttributes) { if (word != "Id") { string attribute = word.Trim(); attributes = attributes + comma + "A." + attribute + " = " + "B." + attribute; comma = ","; } } sqlCommand = sqlCommand + attributes; sqlCommand = sqlCommand + $" FROM "; sqlCommand = sqlCommand + $" {table} AS A "; sqlCommand = sqlCommand + " INNER JOIN #TempJson AS B ON "; comma = " "; attributes = ""; foreach (string key in keys) { attributes = attributes + comma + "A." + key.Trim() + " = " + "B." + key.Trim(); comma = " AND "; } sqlCommand = sqlCommand + attributes; sqlCommand = sqlCommand + " END"; dbConn.SQLExecute(sqlCommand); }
private void BuildInsertProcedure(DbUtilities dbConn, string dataType, DataAccessDef accessDef) { string comma; string attributes; string sqlCommand = $"DROP PROCEDURE IF EXISTS spInsert{dataType}; "; sqlCommand = sqlCommand + $"DROP TYPE IF EXISTS[dbo].[UDT{dataType}];"; dbConn.SQLExecute(sqlCommand); sqlCommand = ""; string sql = accessDef.Select; string[] keys = accessDef.Keys.Split(','); string table = Common.GetTable(sql); ColumnProperties attributeProperties = CommonDbUtilities.GetColumnSchema(dbConn, sql); string[] tableAttributes = Common.GetAttributes(sql); tableAttributes = tableAttributes.Where(w => w != "Id").ToArray(); sqlCommand = sqlCommand + $"CREATE PROCEDURE spInsert{dataType} "; sqlCommand = sqlCommand + " @json NVARCHAR(max) "; sqlCommand = sqlCommand + " AS "; sqlCommand = sqlCommand + " BEGIN "; sqlCommand = sqlCommand + $" INSERT INTO {table }"; attributes = " ("; comma = ""; foreach (var word in tableAttributes) { string attribute = word.Trim(); attributes = attributes + comma + "[" + attribute + "]"; comma = ","; } attributes = attributes + ")"; sqlCommand = sqlCommand + attributes; sqlCommand = sqlCommand + $" SELECT"; comma = " "; attributes = ""; foreach (var word in tableAttributes) { string attribute = word.Trim(); attributes = attributes + comma + attribute; comma = ","; } sqlCommand = sqlCommand + attributes; sqlCommand = sqlCommand + $" FROM OPENJSON(@json)"; comma = ""; attributes = " WITH ("; foreach (var word in tableAttributes) { string attribute = word.Trim(); string dataProperty = attributeProperties[attribute]; attributes = attributes + comma + attribute + " " + dataProperty + " '$." + attribute + "'"; comma = ","; } sqlCommand = sqlCommand + attributes; sqlCommand = sqlCommand + ") AS jsonValues "; sqlCommand = sqlCommand + " END"; dbConn.SQLExecute(sqlCommand); }
private string GetHeaderInfo(string wellInfo) { LASHeaderMappings headMap = new LASHeaderMappings(); DataAccessDef dataType = _dataDef.First(x => x.DataType == "WellBore"); string input = null; Dictionary <string, string> header = new Dictionary <string, string>(); string[] attributes = Common.GetAttributes(dataType.Select); ColumnProperties attributeProperties = CommonDbUtilities.GetColumnSchema(_dbConn, dataType.Select); foreach (string attribute in attributes) { header.Add(attribute.Trim(), ""); } header["ASSIGNED_FIELD"] = "UNKNOWN"; header["OPERATOR"] = "UNKNOWN"; header["DEPTH_DATUM"] = "UNKNOWN"; header["CURRENT_STATUS"] = "UNKNOWN"; header["ROW_CREATED_BY"] = _dbUserName; header["ROW_CHANGED_BY"] = _dbUserName; header.Add("API", ""); StringReader sr = new StringReader(wellInfo); List <LASLine> headerMnems = new List <LASLine>(); while ((input = sr.ReadLine()) != null) { LASLine line = DecodeLASLine(input); if (!string.IsNullOrEmpty(line.Mnem)) { headerMnems.Add(line); } } foreach (var line in headerMnems) { WellMapping mapping = lasMappings.WellMappings.FirstOrDefault(s => s.LASMnem == line.Mnem); if (mapping != null) { string value = mapping.DBMnem; header[value] = line.Data; if (value == "NULL") { _nullRepresentation = line.Data; } } } foreach (var alternativeKey in lasMappings.AlernativeKeys) { if (!string.IsNullOrEmpty(header["UWI"])) { break; } string[] keys = alternativeKey.Key.Split(','); string seperator = ""; foreach (var key in keys) { LASLine line = headerMnems.FirstOrDefault(s => s.Mnem == key.Trim()); if (line != null) { if (!string.IsNullOrEmpty(line.Data)) { header["UWI"] = seperator + line.Data; seperator = "-"; } } } } foreach (string item in attributes) { string attribute = item.Trim(); string attributeType = attributeProperties[attribute]; if (attributeType.Contains("nvarchar")) { int?length = Regex.Match(attributeType, @"\d+").Value.GetIntFromString(); if (length != null) { header[attribute] = header[attribute].Truncate(length.GetValueOrDefault()); } } } string json = JsonConvert.SerializeObject(header, Formatting.Indented); _uwi = header["UWI"]; return(json); }
public async Task LoadCSVFile(ConnectParameters source, ConnectParameters target, string fileName) { DateTime timeStart = DateTime.Now; connectionString = target.ConnectionString; string accessJson = await fileStorageService.ReadFile("connectdefinition", "PPDMDataAccess.json"); _dataDef = JsonConvert.DeserializeObject <List <DataAccessDef> >(accessJson); string referenceJson = await fileStorageService.ReadFile("connectdefinition", "PPDMReferenceTables.json"); _references = JsonConvert.DeserializeObject <List <ReferenceTable> >(referenceJson); string csvJson = await fileStorageService.ReadFile("connectdefinition", "CSVDataAccess.json"); _csvDef = JsonConvert.DeserializeObject <List <CSVAccessDef> >(csvJson); //Console.WriteLine("start reading csv file"); string csvText = await fileStorageService.ReadFile(source.Catalog, fileName); DateTime timeEnd = DateTime.Now; TimeSpan diff = timeEnd - timeStart; //Console.WriteLine($"Time span, read all definitions files: {diff}"); string dataType = source.DataType.Remove(source.DataType.Length - 1, 1); dataAccess = _dataDef.First(x => x.DataType == dataType); CSVAccessDef csvAccess = _csvDef.First(x => x.DataType == dataType); Dictionary <string, int> attributes = csvAccess.Mappings.ToDictionary(); Dictionary <string, string> constants = csvAccess.Constants.ToStringDictionary(); Dictionary <string, string> columnTypes = dt.GetColumnTypes(); DbUtilities dbConn = new DbUtilities(); dbConn.OpenWithConnectionString(connectionString); string dataTypeSql = dataAccess.Select; attributeProperties = CommonDbUtilities.GetColumnSchema(dbConn, dataTypeSql); dbConn.CloseConnection(); //Console.WriteLine("Start parsing csv file"); using (TextReader csvStream = new StringReader(csvText)) { var conf = new CsvConfiguration(CultureInfo.InvariantCulture) { //BadDataFound = null }; using (var csv = new CsvReader(csvStream, conf)) { csv.Read(); csv.ReadHeader(); string[] headerRow = csv.HeaderRecord; var attributeMappings = new Dictionary <string, string>(); foreach (var item in attributes) { int colNumber = item.Value; attributeMappings.Add(headerRow[colNumber], item.Key); } List <dynamic> csvRecords = csv.GetRecords <dynamic>().ToList(); timeEnd = DateTime.Now; diff = timeEnd - timeStart; //Console.WriteLine($"Time span, parsed cvs file into dynamic objects: {diff}"); foreach (var row in csvRecords) { DynamicObject newCsvRecord = new DynamicObject(); foreach (var item in row) { if (attributeMappings.ContainsKey(item.Key)) { string dbAttribute = attributeMappings[item.Key]; string value = item.Value; string dataProperty = attributeProperties[dbAttribute]; if (dataProperty.Contains("varchar")) { string numberString = Regex.Match(dataProperty, @"\d+").Value; int maxCharacters = Int32.Parse(numberString); if (value.Length > maxCharacters) { value = value.Substring(0, maxCharacters); } } newCsvRecord.AddProperty(dbAttribute, value); } } FixKey(newCsvRecord); } timeEnd = DateTime.Now; diff = timeEnd - timeStart; //Console.WriteLine($"Time span, fixed dynamic objects: {diff}"); dt = DynamicToDT(newCsvRecords); timeEnd = DateTime.Now; diff = timeEnd - timeStart; //Console.WriteLine($"Time span, transfer from csv to datatable: {diff}"); InsertTableToDatabase(attributes, dataType, target, constants); } } timeEnd = DateTime.Now; diff = timeEnd - timeStart; //Console.WriteLine($"Time span, completion: {diff}"); }