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