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