//filtering from the buying rate
//if need to fintered by the selling rate change the sql query to SR in all instances
public static LowestRate lowestUSD()
{
LowestRate USD = new LowestRate();
using (SqlConnection connection = new SqlConnection("Data Source=OBHASHAP\\SQLEXPRESS;Initial Catalog=currency;Persist Security Info=True;Integrated Security=True;User ID=su;Password=su"))
{
connection.Open();
//
// Create new SqlCommand object.
//
using (SqlCommand command = new SqlCommand("select top 1 * from [currency].[dbo].[exchangeRates] where USBR =(select MIN(NULLIF(USBR, 0)) from [currency].[dbo].[exchangeRates])", connection))
{
//
// Invoke ExecuteReader method.
//
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
USD.BuyingRate = reader.GetDouble(9);
USD.SellingRate = reader.GetDouble(10);
USD.BankLogo = reader.GetString(11);
USD.BankName = reader.GetString(2);
// Console.WriteLine(" " + USDBR + " " + logo + " " + bank + " " + ASDBR);
// Console.ReadLine();
}
}
}
return(USD);
}
public static LowestRate lowestEuro()
{
LowestRate ER = new LowestRate();
using (SqlConnection connection = new SqlConnection("Data Source=OBHASHAP\\SQLEXPRESS;Initial Catalog=currency;Trusted_Connection=True;"))
{
connection.Open();
//
// Create new SqlCommand object.
//
using (SqlCommand command = new SqlCommand("select top 1 * from [currency].[dbo].[exchangeRates] where EUBR =(select MIN(NULLIF(EUBR, 0)) from [currency].[dbo].[exchangeRates])", connection))
{
//
// Invoke ExecuteReader method.
//
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
ER.BuyingRate = reader.GetDouble(5);
ER.SellingRate = reader.GetDouble(6);
ER.BankLogo = reader.GetString(11);
ER.BankName = reader.GetString(2);
// Console.WriteLine(" " + USDBR + " " + logo + " " + bank + " " + ASDBR);
// Console.ReadLine();
}
}
}
return(ER);
}
public ActionResult Index()
{
//asigning the lowestRate object in the qureies to the current instance
LowestRate USD = Queries.queries.lowestUSD();
ViewBag.USDBR = USD.BuyingRate;
ViewBag.USDSR = USD.SellingRate;
ViewBag.USDBank = USD.BankName;
ViewBag.USDLogo = USD.BankLogo;
LowestRate AD = Queries.queries.lowestAD();
ViewBag.ADBR = AD.BuyingRate;
ViewBag.ADSR = AD.SellingRate;
ViewBag.ADBank = AD.BankName;
ViewBag.ADLogo = AD.BankLogo;
LowestRate ER = Queries.queries.lowestEuro();
ViewBag.ERBR = ER.BuyingRate;
ViewBag.ERSR = ER.SellingRate;
ViewBag.ERBank = ER.BankName;
ViewBag.ERLogo = ER.BankLogo;
LowestRate NOR = Queries.queries.lowestNOR();
ViewBag.NORBR = NOR.BuyingRate;
ViewBag.NORSR = NOR.SellingRate;
ViewBag.NORBank = NOR.BankName;
ViewBag.NORLogo = NOR.BankLogo;
//all data table
List <tableData> list = new List <tableData>();
//using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDbConn"].ToString()))
//{
// connection.Open();
//Server = OBHASHA\\SQLEXPRESS; Database = currency; User Id = sa; Password = intel@123;
myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDbConn"].ConnectionString);
myConnection.Open();
Debug.WriteLine("Connection " + myConnection.ConnectionString);
using (SqlCommand command = new SqlCommand("SELECT * FROM [currency].[dbo].[exchangeRates]", myConnection))
{
SqlDataReader rdr = command.ExecuteReader();
while (rdr.Read())
{
var data = new tableData();
data.BankName = rdr.GetString(2);
data.ADBR = rdr.GetDouble(3);
data.ADSR = rdr.GetDouble(4);
data.USBR = rdr.GetDouble(9);
data.USSR = rdr.GetDouble(10);
data.EUBR = rdr.GetDouble(5);
data.EUSR = rdr.GetDouble(6);
data.NORBR = rdr.GetDouble(7);
data.NORSR = rdr.GetDouble(8);
data.siteUpdated = rdr.GetString(0);
list.Add(data);
}
}
return(View(list));
}