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