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 CreateStoredProcedures(ConnectParameters connector)
{
try
{
string sql = await ReadDatabaseFile("StoredProcedures.sql");
string[] commandText = sql.Split(new string[] { String.Format("{0}GO{0}", Environment.NewLine) }, StringSplitOptions.RemoveEmptyEntries);
DbUtilities dbConn = new DbUtilities();
dbConn.OpenConnection(connector);
for (int x = 0; x < commandText.Length; x++)
{
if (commandText[x].Trim().Length > 0)
{
dbConn.SQLExecute(commandText[x]);
}
}
await CreateGetStoredProcedure(dbConn);
await CreateInsertStoredProcedure(dbConn);
await CreateUpdateStoredProcedure(dbConn);
dbConn.CloseConnection();
}
catch (Exception ex)
{
Exception error = new Exception("Create DMS Model Error: ", ex);
throw error;
}
}
private async Task CreatePPDMModel(DataModelParameters dmParameters, ConnectParameters connector)
{
try
{
string sql = await _fileStorage.ReadFile(dmParameters.FileShare, dmParameters.FileName);
if (string.IsNullOrEmpty(sql))
{
Exception error = new Exception($"Empty data from {dmParameters.FileName}");
throw error;
}
else
{
DbUtilities dbConn = new DbUtilities();
dbConn.OpenConnection(connector);
dbConn.SQLExecute(sql);
dbConn.CloseConnection();
}
}
catch (Exception ex)
{
Exception error = new Exception("Create PPDM Model Error: ", ex);
throw error;
}
}
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);
}
private void CreateSqlSources(DbUtilities dbConn)
{
string sql = "";
try
{
sql = @"CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******'";
dbConn.SQLExecute(sql);
}
catch (Exception ex)
{
//logger.LogInformation("Problems creating master key, it may already exist, {ex}");
}
sql = "Select * from sys.external_data_sources ";
string query = " where name = 'PDOAzureBlob'";
DataTable dt = dbConn.GetDataTable(sql, query);
if (dt.Rows.Count > 0)
{
sql = "DROP EXTERNAL DATA SOURCE PDOAzureBlob ";
dbConn.SQLExecute(sql);
}
try
{
//sql = $"DROP DATABASE SCOPED CREDENTIAL {_credentials}";
//dbConn.SQLExecute(sql);
}
catch (Exception ex)
{
//logger.LogInformation($"Problems deleting credentials, it may not exist, {ex}");
}
try
{
//sql = $"CREATE DATABASE SCOPED CREDENTIAL {_credentials} WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = '{_secret}'";
//dbConn.SQLExecute(sql);
//sql = $"CREATE EXTERNAL DATA SOURCE PDOAzureBlob WITH(TYPE = BLOB_STORAGE, LOCATION = '{_blobStorage}', CREDENTIAL = {_credentials})";
//dbConn.SQLExecute(sql);
}
catch (Exception ex)
{
//logger.LogInformation($"Problems crreating external data source, {ex}");
}
}
private async Task CreateDMSModel(DataModelParameters dmParameters, ConnectParameters connector)
{
try
{
string sql = await ReadDatabaseFile("DataScienceManagement.sql");
string sqlFunctions = await ReadDatabaseFile("InternalRuleFunctions.sql");
DbUtilities dbConn = new DbUtilities();
dbConn.OpenConnection(connector);
dbConn.SQLExecute(sql);
dbConn.SQLExecute(sqlFunctions);
//CreateSqlSources(dbConn);
dbConn.CloseConnection();
string fileName = "WellBore.json";
string taxonomy = await ReadDatabaseFile(fileName);
await _fileStorage.SaveFile(dmParameters.FileShare, fileName, taxonomy);
fileName = "PPDMDataAccess.json";
string definition = await ReadDatabaseFile(fileName);
await _fileStorage.SaveFile("connectdefinition", fileName, definition);
fileName = "LASDataAccess.json";
definition = await ReadDatabaseFile(fileName);
await _fileStorage.SaveFile("connectdefinition", fileName, definition);
fileName = "CSVDataAccess.json";
definition = await ReadDatabaseFile(fileName);
await _fileStorage.SaveFile("connectdefinition", fileName, definition);
fileName = "PPDMReferenceTables.json";
definition = await ReadDatabaseFile(fileName);
await _fileStorage.SaveFile("connectdefinition", fileName, definition);
}
catch (Exception ex)
{
Exception error = new Exception("Create DMS Model Error: ", ex);
throw error;
}
}
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 async Task CreateFunctions(ConnectParameters connector)
{
string sql = await ReadDatabaseFile("Functions.sql");
string[] commandText = sql.Split(new string[] { String.Format("{0}GO{0}", Environment.NewLine) }, StringSplitOptions.RemoveEmptyEntries);
DbUtilities dbConn = new DbUtilities();
dbConn.OpenConnection(connector);
for (int x = 0; x < commandText.Length; x++)
{
if (commandText[x].Trim().Length > 0)
{
dbConn.SQLExecute(commandText[x]);
}
}
}
private void CreateUserDefinedTypes(DbUtilities dbConn, ColumnProperties attributeProperties, string sql, string dataType)
{
string[] tableAttributes = Common.GetAttributes(sql);
string comma = "";
string attributes = "";
foreach (var word in tableAttributes)
{
string attribute = word.Trim();
string dataProperty = attributeProperties[attribute];
attributes = attributes + comma + attribute + " " + dataProperty;
comma = ",";
}
string sqlCommand = $"CREATE TYPE [dbo].[UDT{dataType}] AS TABLE ( ";
sqlCommand = sqlCommand + attributes + ")";
dbConn.SQLExecute(sqlCommand);
}
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);
}
public static PredictionResult PredictFormationOrder(QcRuleSetup qcSetup, DbUtilities dbConn)
{
List <StratUnits> inv = new List <StratUnits>();
PredictionResult result = new PredictionResult
{
Status = "Failed"
};
string formation;
string tempTable = "#MinMaxAllFormationPick";
DataTable idx = new DataTable();
try
{
string select = "select * from #MinMaxAllFormationPick";
string query = "";
idx = dbConn.GetDataTable(select, query);
}
catch (Exception ex)
{
if (ex.InnerException.Message.Contains("Invalid object name"))
{
string select = $"EXEC spGetMinMaxAllFormationPick";
string query = "";
idx = dbConn.GetDataTable(select, query);
string SQLCreateTempTable = Common.GetCreateSQLFromDataTable(tempTable, idx);
dbConn.SQLExecute(SQLCreateTempTable);
dbConn.BulkCopy(idx, tempTable);
}
else
{
throw;
}
}
JObject dataObject = JObject.Parse(qcSetup.DataObject);
formation = dataObject["STRAT_UNIT_ID"].ToString();
string tmpFormation = Common.FixAposInStrings(formation);
string condition = $"STRAT_UNIT_ID = '{tmpFormation}'";
var rows = idx.Select(condition);
if (rows.Length == 1)
{
RuleModel rule = JsonConvert.DeserializeObject <RuleModel>(qcSetup.RuleObject);
int age = Convert.ToInt32(rows[0]["AGE"]);
dataObject[rule.DataAttribute] = age;
string remark = dataObject["REMARK"] + $";{rule.DataAttribute} has been predicted by QCEngine;";
dataObject["REMARK"] = remark;
result.DataObject = dataObject.ToString();
result.DataType = rule.DataType;
result.SaveType = "Update";
result.IndexId = qcSetup.IndexId;
result.Status = "Passed";
}
else if (rows.Length > 1)
{
throw new Exception("PredictFormationOrder: Multiple occurences of formation not allowed");
}
return(result);
}
