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