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