internal static async Task <StockDetails> AStockDetailsdbAsync(int id) { var sDetails = new StockDetails(); try { string stm = SISDBQueryCollection.qGetAStockDetails; using (SQLiteConnection conn = SISDBContext.GetdbConnection()) { using (SQLiteCommand cmd = new SQLiteCommand(stm, conn)) { cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@varStockID", id); using (var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SingleRow)) { while (await reader.ReadAsync()) { sDetails.Load((SQLiteDataReader)reader); } } } return(sDetails); } } catch (SQLiteException ex) { System.Diagnostics.Debug.WriteLine("SQLiteException:" + ex.Message); throw ex; } catch (Exception ex) { throw ex; } }
internal static async Task <Stock> AStocksdbAsync(int id) { var aStock = new Stock(); try { string stm = SISDBQueryCollection.qGetAStock; using (SQLiteConnection conn = SISDBContext.GetdbConnection()) { using (SQLiteCommand cmd = new SQLiteCommand(stm, conn)) { cmd.Parameters.Clear(); SQLiteParameter p1 = new SQLiteParameter("varStockID", System.Data.DbType.Int16); p1.Value = id; cmd.Parameters.Add(p1); using (var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SingleRow)) { while (await reader.ReadAsync()) { aStock.Load((SQLiteDataReader)reader); } } } return(aStock); } } catch (SQLiteException ex) { System.Diagnostics.Debug.WriteLine("SQLiteException:" + ex.Message); throw ex; } catch (Exception ex) { throw ex; } }
internal static async Task <List <Stock> > AllStocksdbAsync() { var stocks = new List <Stock>(); try { string stm = SISDBQueryCollection.qGetAllStocks; using (SQLiteConnection conn = SISDBContext.GetdbConnection()) { using (SQLiteCommand cmd = new SQLiteCommand(stm, conn)) { cmd.Parameters.Clear(); using (var reader = await cmd.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { var s = new Stock(); s.Load((SQLiteDataReader)reader); stocks.Add(s); } } } return(stocks); } } catch (SQLiteException ex) { System.Diagnostics.Debug.WriteLine("SQLiteException:" + ex.Message); throw ex; } catch (Exception ex) { throw ex; } }
internal static async Task <List <object> > GetStocksCodedbAsync(string IsActive, LoadFrom from) { var stocks = new List <Stock>(); var vReturn = new List <object>(); try { string stm = string.Empty; switch (from) { case LoadFrom.Rediff: stm = SISDBQueryCollection.qGetRediffCode; break; case LoadFrom.Yahoo: stm = SISDBQueryCollection.qGetYahooCode; break; } using (SQLiteConnection conn = SISDBContext.GetdbConnection()) { using (SQLiteCommand cmd = new SQLiteCommand(stm, conn)) { cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@StatusType", IsActive); using (var reader = await cmd.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { //var o = new JsonResult(); vReturn.Add(new JsonResult(new { SID = reader[0], SName = reader[1].ToString(), Code = reader[2].ToString() })); } } } return(vReturn); } } catch (SQLiteException ex) { System.Diagnostics.Debug.WriteLine("SQLiteException:" + ex.Message); throw ex; } catch (Exception ex) { throw ex; } }
public DataTable GetStocks() { DataSet ds = new DataSet(); DataTable dtStock; ds.Reset(); using (SQLiteConnection conn = SISDBContext.GetdbConnection()) { string stm = "SELECT * FROM TStockName";// LIMIT 5"; using (SQLiteCommand cmd = new SQLiteCommand(stm, conn)) { SQLiteDataAdapter daStocks = new SQLiteDataAdapter(cmd); daStocks.Fill(ds); dtStock = ds.Tables[0]; } } //var myStocks = ds.Tables[0].AsEnumerable().Select(dataRow => new Stock { StockName = dataRow.Field<string>("StockName") }).ToList(); return(dtStock); }
internal static async Task <List <Stock> > StocksdbAsync(string status = "Active", string category = "") { var stocks = new List <Stock>(); try { string stm = SISDBQueryCollection.qGetAllStocks; using (SQLiteConnection conn = SISDBContext.GetdbConnection()) { using (SQLiteCommand cmd = new SQLiteCommand(stm, conn)) { cmd.Parameters.Clear(); using (var reader = await cmd.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { var s = new Stock(); s.Load((SQLiteDataReader)reader); stocks.Add(s); } } } } if (string.IsNullOrEmpty(category)) { return(stocks.Where(e => e.Status.Contains(status, StringComparison.OrdinalIgnoreCase)).ToList()); } else { return(stocks.Where(e => e.Status.Contains(status, StringComparison.OrdinalIgnoreCase) && e.Category.Contains(category, StringComparison.OrdinalIgnoreCase)).ToList()); } } catch (SQLiteException ex) { System.Diagnostics.Debug.WriteLine("SQLiteException:" + ex.Message); throw ex; } catch (Exception ex) { throw ex; } }
internal static async Task <bool> StockPriceBulkUpdatedbAsync(List <StockPrice> sp) { var sSql = SISDBQueryCollection.qInsertLatestStockPrice; foreach (var s in sp) { //Check the validity of data before adding to db if (s.Price > 0 && ((s.ValueOn - DateTime.Now).TotalDays < 3 /*Configurable*/)) { var values = ($"({s.StockID}, {s.Price}, '{s.ValueOn.ToString("yyyy-MM-dd HH:mm:ss")}'),"); sSql = sSql + values; } } sSql = sSql.TrimEnd(','); try { using (SQLiteConnection conn = SISDBContext.GetdbConnection()) { using (SQLiteCommand cmd = conn.CreateCommand()) { cmd.CommandText = sSql; if (conn.State != ConnectionState.Open) { conn.Open(); } await cmd.ExecuteNonQueryAsync(); // int lastID = Convert.ToInt32(cmd.ExecuteScalar()); } } return(true); } catch (SQLiteException ex) { System.Diagnostics.Debug.WriteLine("SQLiteException:" + ex.Message); throw ex; } catch (Exception ex) { throw ex; } }