private void LoadLogs()
{
DataAccessDef dataType = _dataDef.First(x => x.DataType == "Log");
int logCount = _logNames.Count();
GetIndexValues();
for (int k = 1; k < logCount; k++)
{
Dictionary <string, string> logHeader = new Dictionary <string, string>();
string[] attributes = Common.GetAttributes(dataType.Select);
foreach (string attribute in attributes)
{
logHeader.Add(attribute.Trim(), "");
}
logHeader["NULL_REPRESENTATION"] = _nullRepresentation;
logHeader["VALUE_COUNT"] = "99999.0";
logHeader["MAX_INDEX"] = "99999.0";
logHeader["MIN_INDEX"] = "99999.0";
logHeader["UWI"] = _uwi;
string logName = Common.FixAposInStrings(_logNames[k]);
logHeader["CURVE_ID"] = logName;
string json = JsonConvert.SerializeObject(logHeader, Formatting.Indented);
LoadLogHeader(json, logName, k);
}
}
private void LoadParameterInfo()
{
DataAccessDef dataType = _dataDef.FirstOrDefault(x => x.DataType == "LogParameter");
if (dataType != null)
{
LASSections ls = lasSections[0];
string input = null;
if (!string.IsNullOrEmpty(ls.parameterInfo))
{
if (!LogParmExist())
{
DataRow newRow;
DataTable dtNew = new DataTable();
string logParmtable = Common.GetTable(dataType.Select);
string sqlQuery = $"select * from {logParmtable} where 0 = 1";
SqlDataAdapter logParmAdapter = new SqlDataAdapter(sqlQuery, connectionString);
logParmAdapter.Fill(dtNew);
int seqNo = 0;
StringReader sr = new StringReader(ls.parameterInfo);
while ((input = sr.ReadLine()) != null)
{
LASLine line = DecodeLASLine(input);
if (!string.IsNullOrEmpty(line.Mnem))
{
seqNo++;
newRow = dtNew.NewRow();
newRow["UWI"] = _uwi;
newRow["WELL_LOG_ID"] = _logSource;
newRow["WELL_LOG_SOURCE"] = "Source";
newRow["PARAMETER_SEQ_NO"] = seqNo;
newRow["PARAMETER_TEXT_VALUE"] = line.Data.Trim();
newRow["REPORTED_DESC"] = line.Description.Trim();
newRow["REPORTED_MNEMONIC"] = line.Mnem.Trim();
newRow["ROW_CREATED_BY"] = _dbUserName;
newRow["ROW_CHANGED_BY"] = _dbUserName;
newRow["ROW_CREATED_DATE"] = DateTime.Now.ToString("yyyy-MM-dd");
newRow["ROW_CHANGED_DATE"] = DateTime.Now.ToString("yyyy-MM-dd");
dtNew.Rows.Add(newRow);
}
}
if (dtNew.Rows.Count > 0)
{
using (SqlConnection destinationConnection = new SqlConnection(connectionString))
{
destinationConnection.Open();
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(destinationConnection.ConnectionString))
{
bulkCopy.BatchSize = 500;
bulkCopy.DestinationTableName = logParmtable;
bulkCopy.WriteToServer(dtNew);
}
}
}
}
}
}
}
public static string GetSelectString(this List <DataAccessDef> dataAccessDefs, string dataType)
{
DataAccessDef dataDef = dataAccessDefs.First(x => x.DataType == dataType);
string select = dataDef.Select;
return(select);
}
private void LoadHeader(string json)
{
List <ReferenceTable> dataTypeRefs = _references.Where(x => x.DataType == "WellBore").ToList();
foreach (ReferenceTable reference in dataTypeRefs)
{
json = CheckHeaderForeignKeys(json, reference);
}
DataAccessDef dataType = _dataDef.First(x => x.DataType == "WellBore");
string tmpUwi = Common.FixAposInStrings(_uwi);
string select = dataType.Select;
string query = $" where UWI = '{tmpUwi}'";
DataTable dt = _dbConn.GetDataTable(select, query);
if (dt.Rows.Count == 0)
{
json = Common.SetJsonDataObjectDate(json, "ROW_CREATED_DATE");
json = Common.SetJsonDataObjectDate(json, "ROW_CHANGED_DATE");
JObject jo = JObject.Parse(json);
foreach (JProperty property in jo.Properties())
{
string strValue = property.Value.ToString();
if (string.IsNullOrEmpty(strValue))
{
property.Value = null;
}
}
json = jo.ToString();
_dbConn.InsertDataObject(json, "WellBore");
}
}
public static string GetKeysString(this List <DataAccessDef> dataAccessDefs, string dataType)
{
DataAccessDef dataDef = dataAccessDefs.First(x => x.DataType == dataType);
string keys = dataDef.Keys;
return(keys);
}
private void GetCurveInfo(string curveInfo)
{
DataAccessDef dataType = _dataDef.First(x => x.DataType == "Log");
Dictionary <string, string> log = new Dictionary <string, string>();
string[] attributes = Common.GetAttributes(dataType.Select);
foreach (string attribute in attributes)
{
log.Add(attribute.Trim(), "");
}
string input = null;
StringReader sr = new StringReader(curveInfo);
while ((input = sr.ReadLine()) != null)
{
LASLine line = DecodeLASLine(input);
if (!string.IsNullOrEmpty(line.Mnem))
{
log["CURVE_ID"] = line.Mnem.Trim();
_logNames.Add(line.Mnem.Trim());
log["UWI"] = _uwi;
var json = JsonConvert.SerializeObject(log, Formatting.Indented);
}
}
}
public static string Consistency(QcRuleSetup qcSetup, DataTable dt, List <DataAccessDef> accessDefs, IIndexDBAccess indexData)
{
string returnStatus = "Passed";
RuleModel rule = JsonConvert.DeserializeObject <RuleModel>(qcSetup.RuleObject);
JObject dataObject = JObject.Parse(qcSetup.DataObject);
JToken value = dataObject.GetValue(rule.DataAttribute);
string strValue = value.ToString();
if (Common.CompletenessCheck(strValue) == "Passed")
{
string dataType = rule.DataType;
IndexModel index = Task.Run(() => indexData.GetIndex(qcSetup.IndexId, qcSetup.DataConnector)).GetAwaiter().GetResult();
if (index != null)
{
string query = " where " + index.DataKey;
DataAccessDef dataAccessDef = accessDefs.First(x => x.DataType == dataType);
string sql = dataAccessDef.Select + query;
IADODataAccess consistencyConn = new ADODataAccess();
DataTable ct = consistencyConn.GetDataTable(sql, qcSetup.ConsistencyConnectorString);
Dictionary <string, string> columnTypes = ct.GetColumnTypes();
if (ct.Rows.Count > 0)
{
string strRefValue = ct.Rows[0][rule.DataAttribute].ToString();
string valueType = columnTypes[rule.DataAttribute];
if (Common.CompletenessCheck(strRefValue) == "Passed")
{
returnStatus = RuleMethodUtilities.ConsistencyCheck(strValue, strRefValue, valueType);
}
}
}
}
return(returnStatus);
}
private DataTable GetParentDataTable(string dataType)
{
DataTable pt = new DataTable();
dataAccess = _dataDef.First(x => x.DataType == dataType);
string select = dataAccess.Select;
string table = Common.GetTable(select);
List <ReferenceTable> dataTypeRefs = _references.Where(x => x.DataType == tableDataType).ToList();
foreach (ReferenceTable referance in dataTypeRefs)
{
if (referance.Table == table)
{
string columnName = referance.ReferenceAttribute;
List <string> distinctIds = dt.AsEnumerable().Select(row => row.Field <string>(columnName)).Distinct().ToList();
pt = NewDataTable(dataType);
int keyAttributeLength = GetAttributeLength(referance.Table, referance.KeyAttribute);
foreach (string id in distinctIds)
{
if (!string.IsNullOrEmpty(id))
{
string newId = id;
if (newId.Length > keyAttributeLength)
{
newId = newId.Substring(0, keyAttributeLength);
if (newId.Substring(keyAttributeLength - 1, 1) == "'")
{
newId = newId.Substring(0, keyAttributeLength - 1);
}
DataRow[] sl = dt.Select($"{columnName} = '{id}'");
foreach (DataRow r in sl)
{
r[columnName] = newId;
}
}
string tmpId = Common.FixAposInStrings(newId);
string key = $"{referance.KeyAttribute} = '{tmpId}'";
DataRow[] rows = pt.Select(key);
if (rows.Length == 0)
{
DataRow newRow = pt.NewRow();
newRow[referance.KeyAttribute] = newId;
newRow[referance.ValueAttribute] = id;
if (!string.IsNullOrEmpty(referance.FixedKey))
{
string[] fixedKey = referance.FixedKey.Split('=');
newRow[fixedKey[0]] = fixedKey[1];
}
pt.Rows.Add(newRow);
}
}
}
}
}
return(pt);
}
public DataAccessDef GetDataAccessDefinition()
{
DataAccessDef dataAccessDef = new DataAccessDef();
dataAccessDef.DataType = "Index";
dataAccessDef.Select = getSql;
dataAccessDef.Keys = "INDEXID";
return(dataAccessDef);
}
public async Task <DataTable> GetLASLogHeaders(ConnectParameters source, ConnectParameters target)
{
DataAccessDef dataType = _dataDef.First(x => x.DataType == "Log");
string select = dataType.Select;
string query = $" where 0 = 1";
_dbConn.OpenConnection(target);
DataTable dt = _dbConn.GetDataTable(select, query);
List <string> files = new List <string>();
files = await GetLASFileNames(source.Catalog);
foreach (LASSections ls in lasSections)
{
GetVersionInfo(ls.versionInfo);
string json = GetHeaderInfo(ls.wellInfo);
_logNames = new List <string>();
GetCurveInfo(ls.curveInfo);
int logCount = _logNames.Count();
GetIndexValues();
for (int k = 1; k < logCount; k++)
{
Dictionary <string, string> logHeader = new Dictionary <string, string>();
string[] attributes = Common.GetAttributes(dataType.Select);
foreach (string attribute in attributes)
{
logHeader.Add(attribute.Trim(), "");
}
logHeader["NULL_REPRESENTATION"] = _nullRepresentation;
logHeader["VALUE_COUNT"] = "-99999.0";
logHeader["MAX_INDEX"] = "-99999.0";
logHeader["MIN_INDEX"] = "-99999.0";
logHeader["UWI"] = _uwi;
logHeader["ROW_CREATED_BY"] = _dbConn.GetUsername();
logHeader["ROW_CHANGED_BY"] = _dbConn.GetUsername();
string logName = Common.FixAposInStrings(_logNames[k]);
logHeader["CURVE_ID"] = logName;
json = JsonConvert.SerializeObject(logHeader, Formatting.Indented);
DataRow row = dt.NewRow();
JObject jo = JObject.Parse(json);
foreach (JProperty property in jo.Properties())
{
string strValue = property.Value.ToString();
if (!string.IsNullOrEmpty(strValue))
{
if (row.Table.Columns.Contains(property.Name))
{
row[property.Name] = property.Value;
}
}
}
dt.Rows.Add(row);
}
}
return(dt);
}
private void 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);
foreach (string attribute in attributes)
{
header.Add(attribute.Trim(), "");
}
header["FINAL_TD"] = "99999.0";
header["SURFACE_LATITUDE"] = "99999.0";
header["SURFACE_LONGITUDE"] = "99999.0";
header["DEPTH_DATUM_ELEV"] = "99999.0";
header["GROUND_ELEV"] = "99999.0";
header["ASSIGNED_FIELD"] = "UNKNOWN";
header["OPERATOR"] = "UNKNOWN";
header["DEPTH_DATUM"] = "UNKNOWN";
header["CURRENT_STATUS"] = "UNKNOWN";
header.Add("API", "");
StringReader sr = new StringReader(wellInfo);
while ((input = sr.ReadLine()) != null)
{
LASLine line = DecodeLASLine(input);
if (!string.IsNullOrEmpty(line.Mnem))
{
try
{
string key = headMap[line.Mnem];
header[key] = line.Data;
if (key == "NULL")
{
_nullRepresentation = line.Data;
}
}
catch (Exception)
{
}
}
}
if (string.IsNullOrEmpty(header["UWI"]))
{
header["UWI"] = header["API"];
}
if (string.IsNullOrEmpty(header["UWI"]))
{
header["UWI"] = header["LEASE_NAME"] + "-" + header["WELL_NAME"];
}
string json = JsonConvert.SerializeObject(header, Formatting.Indented);
_uwi = header["UWI"];
LoadHeader(json);
}
private List <string> GetLogList()
{
List <string> curves = new List <string>();
DataAccessDef dataType = _dataDef.First(x => x.DataType == "Log");
string select = dataType.Select;
string tmpUwi = Common.FixAposInStrings(_uwi);
string query = $" where UWI = '{tmpUwi}'";
DataTable dt = _dbConn.GetDataTable(select, query);
curves = dt.AsEnumerable().Select(p => p.Field <string>("CURVE_ID")).Distinct().ToList();
return(curves);
}
private void DeleteInDatabase(ConnectParameters connector, IndexModel indexItem)
{
string dataType = indexItem.DataType;
string dataKey = indexItem.DataKey;
List <DataAccessDef> accessDefs = JsonConvert.DeserializeObject <List <DataAccessDef> >(connector.DataAccessDefinition);
DataAccessDef accessDef = accessDefs.First(x => x.DataType == dataType);
string select = accessDef.Select;
string dataTable = Common.GetTable(select);
string dataQuery = "where " + dataKey;
DbUtilities dbConn = new DbUtilities();
dbConn.OpenWithConnectionString(connector.ConnectionString);
dbConn.DBDelete(dataTable, dataQuery);
dbConn.CloseConnection();
}
private void LoadLogHeader(string json, string logName, int pointer)
{
DataAccessDef dataType = _dataDef.First(x => x.DataType == "Log");
string select = dataType.Select;
string query = $" where UWI = '{_uwi}' and CURVE_ID = '{logName}'";
DataTable dt = _dbConn.GetDataTable(select, query);
if (dt.Rows.Count == 0)
{
json = Common.SetJsonDataObjectDate(json, "ROW_CREATED_DATE");
json = Common.SetJsonDataObjectDate(json, "ROW_CHANGED_DATE");
_dbConn.InsertDataObject(json, "Log");
LoadLogCurve(pointer, logName);
}
}
private void InitDuplicateKeys(string dataType)
{
dataAccess = _dataDef.First(x => x.DataType == dataType);
string[] keys = dataAccess.Keys.Split(',').Select(key => key.Trim()).ToArray();
foreach (DataRow row in dt.Rows)
{
string duplicateKey = "";
foreach (string key in keys)
{
string value = row[key].ToString().ToUpper();
duplicateKey = duplicateKey + value;
}
duplicates.Add(duplicateKey.GetSHA256Hash(), "");
}
}
private void BuildGetProcedure(DbUtilities dbConn, string dataType, DataAccessDef accessDef)
{
string sqlCommand = $"DROP PROCEDURE IF EXISTS spGet{dataType} ";
dbConn.SQLExecute(sqlCommand);
sqlCommand = "";
string sql = accessDef.Select;
sqlCommand = sqlCommand + $"CREATE PROCEDURE spGet{dataType} ";
sqlCommand = sqlCommand + " AS ";
sqlCommand = sqlCommand + " BEGIN ";
sqlCommand = sqlCommand + sql;
sqlCommand = sqlCommand + " END";
dbConn.SQLExecute(sqlCommand);
}
public async Task <DataTable> GetLASWellHeaders(ConnectParameters source, ConnectParameters target)
{
string accessJson = await fileStorageService.ReadFile("connectdefinition", "PPDMDataAccess.json");
_dataDef = JsonConvert.DeserializeObject <List <DataAccessDef> >(accessJson);
//lasAccessJson = JObject.Parse(await fileStorageService.ReadFile("connectdefinition", "LASDataAccess.json"));
string lasMappingsStr = await fileStorageService.ReadFile("connectdefinition", "LASDataAccess.json");
lasMappings = JsonConvert.DeserializeObject <LASMappings>(lasMappingsStr);
List <string> files = new List <string>();
files = await GetLASFileNames(source.Catalog);
DataAccessDef dataType = _dataDef.First(x => x.DataType == "WellBore");
string select = dataType.Select;
string query = $" where 0 = 1";
_dbConn.OpenConnection(target);
DataTable dt = _dbConn.GetDataTable(select, query);
foreach (string file in files)
{
LASSections ls = await GetLASSections(source.Catalog, file);
lasSections.Add(ls);
GetVersionInfo(ls.versionInfo);
string json = GetHeaderInfo(ls.wellInfo);
DataRow row = dt.NewRow();
JObject jo = JObject.Parse(json);
foreach (JProperty property in jo.Properties())
{
string strValue = property.Value.ToString();
if (!string.IsNullOrEmpty(strValue))
{
if (row.Table.Columns.Contains(property.Name))
{
row[property.Name] = property.Value;
}
}
}
dt.Rows.Add(row);
}
return(dt);
}
private bool LogParmExist()
{
bool exist = false;
DataAccessDef dataType = _dataDef.First(x => x.DataType == "LogParameter");
string select = dataType.Select;
string tmpUwi = Common.FixAposInStrings(_uwi);
string query = $" where UWI = '{tmpUwi}' and WELL_LOG_ID = '{_logSource}'";
DataTable dt = _dbConn.GetDataTable(select, query);
if (dt.Rows.Count > 0)
{
exist = true;
}
return(exist);
}
private void LoadHeader(string json)
{
foreach (ReferenceTable reference in _references)
{
json = CheckHeaderForeignKeys(json, reference);
}
DataAccessDef dataType = _dataDef.First(x => x.DataType == "WellBore");
string select = dataType.Select;
string query = $" where UWI = '{_uwi}'";
DataTable dt = _dbConn.GetDataTable(select, query);
if (dt.Rows.Count == 0)
{
json = Common.SetJsonDataObjectDate(json, "ROW_CREATED_DATE");
json = Common.SetJsonDataObjectDate(json, "ROW_CHANGED_DATE");
_dbConn.InsertDataObject(json, "WellBore");
}
}
private void BuildGetProcedureWithId(DbUtilities dbConn, string dataType, DataAccessDef accessDef)
{
string sqlCommand = $"DROP PROCEDURE IF EXISTS spGetWithId{dataType} ";
dbConn.SQLExecute(sqlCommand);
sqlCommand = "";
string sql = accessDef.Select;
string query = " WHERE ID = @id ";
sqlCommand = sqlCommand + $"CREATE PROCEDURE spGetWithId{dataType} ";
sqlCommand = sqlCommand + " @id INT ";
sqlCommand = sqlCommand + " AS ";
sqlCommand = sqlCommand + " BEGIN ";
sqlCommand = sqlCommand + sql + query;
sqlCommand = sqlCommand + " END";
dbConn.SQLExecute(sqlCommand);
}
private void BuildGetProcedureWithQcString(DbUtilities dbConn, DataAccessDef accessDef)
{
string sqlCommand = $"DROP PROCEDURE IF EXISTS spGetWithQcStringIndex ";
dbConn.SQLExecute(sqlCommand);
sqlCommand = "";
string sql = accessDef.Select;
sqlCommand = sqlCommand + $"CREATE PROCEDURE spGetWithQcStringIndex ";
sqlCommand = sqlCommand + " @qcstring VARCHAR(10) ";
sqlCommand = sqlCommand + " AS ";
sqlCommand = sqlCommand + " BEGIN ";
sqlCommand = sqlCommand + " declare @query as varchar(240) ";
sqlCommand = sqlCommand + " set @query = '%' + @qcstring + ';%'";
sqlCommand = sqlCommand + sql;
sqlCommand = sqlCommand + " WHERE QC_STRING like @query";
sqlCommand = sqlCommand + " END";
dbConn.SQLExecute(sqlCommand);
}
private void DeleteParent(string qcStr, IndexModel idxResults)
{
string condition = $"INDEXID={idxResults.IndexId}";
var rows = indexTable.Select(condition);
rows[0]["JSONDATAOBJECT"] = "";
rows[0]["QC_STRING"] = qcStr;
indexTable.AcceptChanges();
if (syncPredictions)
{
string dataType = idxResults.DataType;
string dataKey = idxResults.DataKey;
DataAccessDef ruleAccessDef = _accessDefs.First(x => x.DataType == dataType);
string select = ruleAccessDef.Select;
string dataTable = GetTable(select);
string dataQuery = "where " + dataKey;
_dbConn.DBDelete(dataTable, dataQuery);
}
}
private async Task CreateUpdateStoredProcedure(DbUtilities dbConn)
{
List <DataAccessDef> accessDefs = await GetDataAccessDefinitions();
var dataTypes = accessDefs.Select(s => s.DataType).Where(s => s != "Index").ToList();
foreach (string dataType in dataTypes)
{
DataAccessDef accessDef = accessDefs.First(x => x.DataType == dataType);
BuildUpdateProcedure(dbConn, dataType, accessDef);
}
RuleManagement rm = new RuleManagement();
string type = "Rules";
DataAccessDef ruleDef = rm.GetDataAccessDefinition(type);
BuildUpdateProcedure(dbConn, type, ruleDef);
type = "Functions";
DataAccessDef functionDef = rm.GetDataAccessDefinition(type);
BuildUpdateProcedure(dbConn, type, functionDef);
}
private async Task CreateGetStoredProcedure(DbUtilities dbConn)
{
RuleManagement rm = new RuleManagement();
string type = "Rules";
DataAccessDef ruleDef = rm.GetDataAccessDefinition(type);
BuildGetProcedure(dbConn, type, ruleDef);
BuildGetProcedureWithId(dbConn, type, ruleDef);
type = "Functions";
DataAccessDef functionDef = rm.GetDataAccessDefinition(type);
BuildGetProcedure(dbConn, type, functionDef);
BuildGetProcedureWithId(dbConn, type, functionDef);
IndexAccess ia = new IndexAccess();
type = "Index";
DataAccessDef indexDef = ia.GetDataAccessDefinition();
BuildGetProcedure(dbConn, type, indexDef);
BuildGetProcedureWithQcString(dbConn, indexDef);
}
private void InsertMissingObjectToIndex(PredictionResult result)
{
IndexFileData indexdata = GetIndexFileData(result.DataType);
if (indexdata.DataName != null)
{
JObject dataObject = JObject.Parse(result.DataObject);
string dataName = dataObject[indexdata.NameAttribute].ToString();
string dataType = result.DataType;
DataAccessDef dataAccessDef = _accessDefs.First(x => x.DataType == dataType);
string dataKey = GetDataKey(dataObject, dataAccessDef.Keys);
int parentId = result.IndexId;
string jsonData = result.DataObject;
double latitude = -99999.0;
double longitude = -99999.0;
int nodeId = GeIndextNode(dataType, parentId);
if (nodeId > 0)
{
_dbConn.InsertIndex(nodeId, dataName, dataType, dataKey, jsonData, latitude, longitude);
}
}
}
private void LoadLogCurve(int pointer, string logName)
{
string comma = "";
DataAccessDef dataType = _dataDef.First(x => x.DataType == "LogCurve");
int indexCount = _indexValues.Count();
int logCount = _logNames.Count();
string jsonArray = @"[";
for (int i = 0; i < indexCount; i++)
{
jsonArray = jsonArray + comma;
Dictionary <string, string> logCurve = new Dictionary <string, string>();
string[] attributes = Common.GetAttributes(dataType.Select);
foreach (string attribute in attributes)
{
logCurve.Add(attribute.Trim(), "");
}
logCurve["UWI"] = _uwi;
logCurve["CURVE_ID"] = logName;
logCurve["SAMPLE_ID"] = i.ToString();
logCurve["INDEX_VALUE"] = _indexValues[i].ToString();
logCurve["MEASURED_VALUE"] = _curveValues[pointer + (i * logCount)].ToString();
string json = JsonConvert.SerializeObject(logCurve, Formatting.Indented);
json = Common.SetJsonDataObjectDate(json, "ROW_CREATED_DATE");
json = Common.SetJsonDataObjectDate(json, "ROW_CHANGED_DATE");
jsonArray = jsonArray + json;
comma = ",";
}
jsonArray = jsonArray + @"]";
string select = dataType.Select;
string query = $" where UWI = '{_uwi}' and CURVE_ID = '{logName}'";
DataTable dt = _dbConn.GetDataTable(select, query);
if (dt.Rows.Count == 0)
{
_dbConn.InsertDataObject(jsonArray, "LogCurve");
}
}
public DataAccessDef GetDataAccessDefinition(string dataType)
{
DataAccessDef dataAccessDef = new DataAccessDef();
if (dataType == "Rules")
{
dataAccessDef.DataType = "Rules";
dataAccessDef.Select = getSql;
dataAccessDef.Keys = "Id";
}
else if (dataType == "Functions")
{
dataAccessDef.DataType = "Functions";
dataAccessDef.Select = functionSql;
dataAccessDef.Keys = "Id";
}
else
{
throw new InvalidOperationException("Not a valif data type");
}
return(dataAccessDef);
}
public async Task <string> GetAttributeInfo(string sourceName, string dataType)
{
string json = "";
ConnectParameters connector = await Common.GetConnectParameters(azureConnectionString, sourceName);
string accessJson = await _fileStorage.ReadFile("connectdefinition", "PPDMDataAccess.json");
List <DataAccessDef> accessDefs = JsonConvert.DeserializeObject <List <DataAccessDef> >(accessJson);
DataAccessDef dataAccess = accessDefs.First(x => x.DataType == dataType);
string sql = dataAccess.Select;
string table = Common.GetTable(sql);
string query = $" where 0 = 1";
DbUtilities dbConn = new DbUtilities();
dbConn.OpenWithConnectionString(connector.ConnectionString);
DataTable dt = dbConn.GetDataTable(sql, query);
AttributeInfo attributeInfo = new AttributeInfo();
attributeInfo.DataAttributes = dt.GetColumnTypes();
json = JsonConvert.SerializeObject(attributeInfo);
return(json);
}
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);
}
