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