/// <summary> /// Gets a list of all the files stored in the database /// </summary> public static async Task<List<string>> GetAllFileNamessAsync() { string connectionString = defaultConnectionString; using (var connection = new OleDbConnection(connectionString)) { try { await connection.OpenAsync(); string query = "SELECT Name FROM Files;"; using (var command = new OleDbCommand(query, connection)) { var reader = await command.ExecuteReaderAsync(); var list = new List<string>(); while (await reader.ReadAsync()) list.Add(reader.GetString(0)); return list; } } catch (Exception ex) { TextEditor.WPFMessageBoxException(ex); return null; } } }
public async Task<IList<ServiceZipDetailsSet>> GetServiceZips(string company, string filename, string month, int year) { var serviceZips = new List<ServiceZipDetailsSet>(); var conn = new OleDbConnection(string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"", filename)); await conn.OpenAsync(); var cmd = new OleDbCommand(); cmd.Connection = conn; cmd.CommandText = "SELECT * FROM [Лист1$]"; var reader = await cmd.ExecuteReaderAsync(); while (reader.Read()) { int q = 0; if (reader["Quantity"] != DBNull.Value) int.TryParse(reader["Quantity"].ToString(), out q); if (reader["BlankNumber"].ToString().Trim() == "tr") { //here } serviceZips.Add(new ServiceZipDetailsSet { WorkDate = Convert.ToDateTime(reader["Date"]), BlankNumber = reader["BlankNumber"].ToString().Trim(), ZipName = reader["Work"].ToString().Trim(), StoreNumber = Convert.ToInt16(reader["StoreNumber"]), ZipPrice = Convert.ToDecimal(reader["Price"]), Company = company, ServiceMonth = month, ServiceYear = year, ZipQuantity = q }); } reader.Close(); conn.Close(); return serviceZips; }
public override async Task<RopDocument[]> FillDate() { List<RopData> data=new List<RopData>(); var dd = new DownloadData(); var dataBytes = await dd.Data(rdBase.PathDocument); var path = Path.Combine(Path.GetTempPath(), Path.GetTempFileName() + ".xls"); File.WriteAllBytes(path,dataBytes); //var path = "D:\\a.xls"; var dt = new DataTable(); using (var m = new OleDbConnection()) { m.ConnectionString = ExcelHelpers.BuildExcelConnectionString(path, true); m.Open(); var query = @"Select * From [Sheet1$]"; using (var cmd = new OleDbCommand(query, m)) { using (var dr = cmd.ExecuteReaderAsync().Result) { dt.Load(dr); } } } foreach (DataRow row in dt.Rows) { int nr; var arr = row.ItemArray; //if (arr[0] == null || string.IsNullOrWhiteSpace(arr[0].ToString())) if (string.IsNullOrWhiteSpace(arr[0]?.ToString())) continue; if(!int.TryParse(arr[0].ToString(),out nr)) continue; var numeJudet = arr[1].ToString().Trim().ToLower(); var judet = judetFinder.Find(numeJudet); var rd=new RopData(); rd.Judet = judet; rd.Valoare = int.Parse(arr[2].ToString()); rd.Oras = null; data.Add(rd); } rdBase.Data = data.ToArray(); return new [] { rdBase }; }
/// <summary> /// Method to Get All the Records from Excel /// </summary> /// <returns></returns> public async Task<ObservableCollection<Employee>> GetDataFormExcelAsync() { ObservableCollection<Employee> Employees = new ObservableCollection<Employee>(); await Conn.OpenAsync(); Cmd = new OleDbCommand(); Cmd.Connection = Conn; Cmd.CommandText = "Select * from [Sheet1$]"; var Reader = await Cmd.ExecuteReaderAsync(); while (Reader.Read()) { Employees.Add(new Employee() { EmpNo = Convert.ToInt32(Reader["EmpNo"]), EmpName = Reader["EmpName"].ToString(), DeptName = Reader["DeptName"].ToString(), Salary = Convert.ToInt32(Reader["Salary"]) }); } Reader.Close(); Conn.Close(); return Employees; }
public override async Task<RopDocument[]> FillDate() { var dataAlegatori = new List<RopData>(); var dataAlegatoriPrezenti = new List<RopData>(); var dd = new DownloadData(); var dataBytes = await dd.Data(rdBase.PathDocument); var path = Path.Combine(Path.GetTempPath(), Path.GetTempFileName() + ".xls"); File.WriteAllBytes(path, dataBytes); //var path = "D:\\a.xls"; var dt = new DataTable(); using (var m = new OleDbConnection()) { m.ConnectionString = ExcelHelpers.BuildExcelConnectionString(path, true); m.Open(); var query = @"Select * From [SIAEP2014_STAT_Statistica pe lo$]"; using (var cmd = new OleDbCommand(query, m)) { using (var dr = cmd.ExecuteReaderAsync().Result) { dt.Load(dr); } } } foreach (DataRow row in dt.Rows) { var arr = row.ItemArray; //if (arr[0] == null || string.IsNullOrWhiteSpace(arr[0].ToString())) if (string.IsNullOrWhiteSpace(arr[0]?.ToString())) continue; var numeJudet = arr[1].ToString().Trim().ToLower(); if(numeJudet == "străinătate") continue; var judet = judetFinder.Find(numeJudet); var valoare = int.Parse(arr[3].ToString()); var rd = new RopData(); rd.Judet = judet; rd.Valoare = valoare; rd.Oras = null; dataAlegatori.Add(rd); rd = new RopData(); rd.Judet = judet; rd.Valoare = int.Parse(arr[6].ToString()); rd.Oras = null; dataAlegatoriPrezenti.Add(rd); } var newRD = new RopDocument(rdBase); newRD.Name += "Nr Alegatori Liste"; newRD.ID = "BA671A2B-27E8-408D-BABD-59B52661789D"; newRD.Data = dataAlegatori .GroupBy(it => it.Judet).Select(group => new RopData() { Judet = group.Key, Valoare = group.Sum(it => it.Valoare) } ).ToArray(); var newRDUrne = new RopDocument(rdBase); newRDUrne.Name += "Nr Alegatori Prezentati La Urne"; newRDUrne.ID = "BBB0ECA2-34BE-4177-B3A2-82BC6B41311E"; newRDUrne.Data = dataAlegatoriPrezenti .GroupBy(it => it.Judet).Select(group => new RopData() { Judet = group.Key, Valoare = group.Sum(it => it.Valoare) } ).ToArray(); return new[] { newRD,newRDUrne }; }
public async Task<IRow[]> Query(IReadOnlyDictionary<string, string> columns) { if (null == columns) { throw new ArgumentNullException(CommaDelimitedFileAdapter.ArgumentNameColumns); } string query = string.Format( CultureInfo.InvariantCulture, CommaDelimitedFileAdapter.QueryTemplate, this.fileName); if (columns.Any()) { IReadOnlyCollection<string> filters = columns .Select( (KeyValuePair<string, string> item) => string.Format( CultureInfo.InvariantCulture, CommaDelimitedFileAdapter.FilterTemplate, item.Key, item.Value)) .ToArray(); string filter = string.Join(CommaDelimitedFileAdapter.DelimiterFilter, filters); query = string.Concat(query, CommaDelimitedFileAdapter.WhereClausePrefix, filter); } OleDbCommand commandQuery = null; try { commandQuery = new OleDbCommand(query, connection); DbDataReader reader = null; try { reader = await commandQuery.ExecuteReaderAsync(); IList<IRow> rows = new List<IRow>(); while (reader.Read()) { string rowKey = (string)reader[0]; Dictionary<string, string> rowColumns = new Dictionary<string, string>(this.headers.Count - 1); for (int indexColumn = 1; indexColumn < this.headers.Count; indexColumn++) { string columnValue = reader[indexColumn] as string; if (string.IsNullOrWhiteSpace(columnValue)) { continue; } string columnHeader = this.headers.ElementAt(indexColumn); rowColumns.Add(columnHeader, columnValue); } IRow row = new Row(rowKey, rowColumns); rows.Add(row); } IRow[] results = rows.ToArray(); return results; } finally { if (reader != null) { reader.Close(); reader = null; } } } finally { if (commandQuery != null) { commandQuery.Dispose(); commandQuery = null; } } }
/// <summary> /// Checks database for existence of a file name /// </summary> /// <param name="name"></param> /// <param name="connection"></param> /// <returns></returns> private static async Task<bool> FileExists(string name, OleDbConnection connection) { string query = "SELECT Name FROM Files WHERE name = @name;"; using (var command = new System.Data.OleDb.OleDbCommand(query, connection)) { command.Parameters.AddWithValue("@name", name); var reader = await command.ExecuteReaderAsync(); // First run of ReadAsync() will return true if there are records matching its query return await reader.ReadAsync(); } }
public override async Task<RopDocument[]> FillDate() { var dataCasatoriti = new List<RopData>(); var dd = new DownloadData(); var dataBytes = await dd.Data(rdBase.PathDocument); var path = Path.Combine(Path.GetTempPath(), Path.GetTempFileName() + ".xls"); File.WriteAllBytes(path, dataBytes); //var path = "D:\\a.xls"; var dt = new DataTable(); using (var m = new OleDbConnection()) { m.ConnectionString = ExcelHelpers.BuildExcel2007ConnectionString(path, true); m.Open(); var query = @"Select * From [Anul2015$]"; using (var cmd = new OleDbCommand(query, m)) { using (var dr = cmd.ExecuteReaderAsync().Result) { dt.Load(dr); } } } foreach (DataRow row in dt.Rows) { var arr = row.ItemArray; //if (arr[0] == null || string.IsNullOrWhiteSpace(arr[0].ToString())) if (string.IsNullOrWhiteSpace(arr[0]?.ToString())) continue; var numeJudet = arr[1].ToString().Trim().ToLower(); var judet = judetFinder.Find(numeJudet); Func<object, int> retVal = (obj) => { int i; if (obj == null) return 0; if (int.TryParse(obj.ToString(), out i)) return i; return 0; }; int valoare = retVal(arr[15]); var rd = new RopData(); rd.Judet = judet; rd.Valoare = valoare; rd.Oras = null; dataCasatoriti.Add(rd); } rdBase.Data = dataCasatoriti .GroupBy(it => it.Judet).Select(group => new RopData() { Judet = group.Key, Valoare = group.Sum(it => it.Valoare) } ).ToArray(); return new[] {rdBase}; }