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; } }
public async Task ExecutePrediction(PredictionParameters parms) { string accessJson = await _fileStorage.ReadFile("connectdefinition", "PPDMDataAccess.json"); _accessDefs = JsonConvert.DeserializeObject <List <DataAccessDef> >(accessJson); ConnectParameters connector = await GetConnector(parms.DataConnector); databaseConnectionString = connector.ConnectionString; _dbConn.OpenConnection(connector); string sourceConnector = GetSource(); if (parms.DataConnector == sourceConnector) { syncPredictions = true; } else { syncPredictions = false; } RuleManagement rules = new RuleManagement(_azureConnectionString); RuleModel rule = await rules.GetRuleAndFunction(parms.DataConnector, parms.PredictionId); manageIndexTable = new ManageIndexTable(_accessDefs, connector.ConnectionString, rule.DataType, rule.FailRule); manageIndexTable.InitQCFlags(false); await MakePredictions(rule, connector); _dbConn.CloseConnection(); manageIndexTable.SaveQCFlags(); }
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; } }
public async Task LoadLASFile(ConnectParameters source, ConnectParameters target, string fileName, string ReferenceTableDefJson) { _logSource = fileName; connectionString = target.ConnectionString; _dataDef = JsonConvert.DeserializeObject <List <DataAccessDef> >(target.DataAccessDefinition); _references = JsonConvert.DeserializeObject <List <ReferenceTable> >(ReferenceTableDefJson); lasAccessJson = JObject.Parse(await fileStorageService.ReadFile("connectdefinition", "LASDataAccess.json")); string lasMappingsStr = await fileStorageService.ReadFile("connectdefinition", "LASDataAccess.json"); lasMappings = JsonConvert.DeserializeObject <LASMappings>(lasMappingsStr); lasSections = new List <LASSections>(); LASSections ls = await GetLASSections(source.Catalog, fileName); lasSections.Add(ls); _dbConn.OpenConnection(target); _dbUserName = _dbConn.GetUsername(); GetVersionInfo(ls.versionInfo); string json = GetHeaderInfo(ls.wellInfo); LoadHeader(json); GetCurveInfo(ls.curveInfo); GetDataInfo(ls.dataInfo); LoadParameterInfo(); LoadLogs(); _dbConn.CloseConnection(); }
public async Task <string> GetIndexData(string sourceName) { string result = ""; ConnectParameters connector = await Common.GetConnectParameters(azureConnectionString, sourceName); if (connector.SourceType == "DataBase") { string jsonConnectDef = await _fileStorage.ReadFile("connectdefinition", "PPDMDataAccess.json"); connector.DataAccessDefinition = jsonConnectDef; } else { Exception error = new Exception($"RuleManagement: data source must be a Database type"); throw error; } _dbConn.OpenConnection(connector); string strProcedure = $"EXEC spGetNumberOfDescendants '/', 1"; string query = ""; DataTable qc = _dbConn.GetDataTable(strProcedure, query); List <DmsIndex> index = ProcessAllChildren(qc); result = JsonConvert.SerializeObject(index, Formatting.Indented); _dbConn.CloseConnection(); return(result); }
public async Task <List <int> > ExecuteQcRule(DataQCParameters qcParms) { try { ConnectParameters connector = await GetConnector(qcParms.DataConnector); RuleModel rule = await GetRuleAndFunctionInfo(qcParms.DataConnector, qcParms.RuleId); string accessJson = await _fileStorage.ReadFile("connectdefinition", "PPDMDataAccess.json"); _accessDefs = JsonConvert.DeserializeObject <List <DataAccessDef> >(accessJson); DbUtilities dbConn = new DbUtilities(); dbConn.OpenConnection(connector); manageQCFlags = new ManageIndexTable(_accessDefs, connector.ConnectionString, rule.DataType); List <int> failedObjects = await QualityCheckDataType(dbConn, rule, connector); dbConn.CloseConnection(); return(failedObjects); } catch (Exception ex) { Exception error = new Exception($"DataQc: Could process rule {qcParms.RuleId}, {ex}"); throw error; } }
public void DeleteTable(string connectString, string table) { DbUtilities dbConn = new DbUtilities(); dbConn.OpenWithConnectionString(connectString); dbConn.DBDelete(table); dbConn.CloseConnection(); }
private DataTable NewDataTable(string dataType) { string select = dataAccess.Select; string query = $" where 0 = 1"; DbUtilities db = new DbUtilities(); db.OpenWithConnectionString(connectionString); DataTable table = db.GetDataTable(select, query); db.CloseConnection(); return(table); }
private void UpdateDatabase(string jsonDataObject, string connectionString, string dataType) { JObject dataObject = JObject.Parse(jsonDataObject); dataObject["ROW_CHANGED_BY"] = Environment.UserName; jsonDataObject = dataObject.ToString(); jsonDataObject = Helpers.Common.SetJsonDataObjectDate(jsonDataObject, "ROW_CHANGED_DATE"); DbUtilities dbConn = new DbUtilities(); dbConn.OpenWithConnectionString(connectionString); dbConn.UpdateDataObject(jsonDataObject, dataType); dbConn.CloseConnection(); }
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 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; } }
public void CloseIndex() { dbConn.InsertUserDefinedTable(myIndex); dbConn.CloseConnection(); sourceAccess.CloseConnection(); }
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}"); }