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