using Magazin_ColOovidiu.Other.DataMaps;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Magazin_ColOovidiu.Other
{
    public class DbConnections
    {
        public static string msConString = String.Format("Server={0};Database={1};Integrated Security={2}",
            Properties.ProjectSettings.Default.MsSqlServer,
            Properties.ProjectSettings.Default.MsSqlDbName,
            Properties.ProjectSettings.Default.MsSecurity);

        public static System.Data.DataTable GetDataTable(string query, string tableName) //this is the generic method that gets the table, can be used everywhere
        {
            System.Data.DataTable tbl;
            using (SqlConnection conn = new SqlConnection(msConString))
            {
                SqlDataAdapter sda = new SqlDataAdapter(query, conn);
                DataSet ds = new DataSet(tableName);
                sda.Fill(ds, tableName);
                tbl = ds.Tables[tableName];
            }
            return tbl;
        }

        public static IEnumerable<LoginDataDB> GetDbCredentials() //these are specific, needed to get specific data from db table
        {
            string query = "SELECT * FROM [testDb].[dbo].[Creds]"; //this is the table from the db
            System.Data.DataTable tbl = GetDataTable(query, "Creds"); //must have the same name as the table, in this case Creds
            foreach (DataRow rw in tbl.Rows)
            {
                string username = rw["username"].ToString(); //select the row name
                string password = rw["password"].ToString();
                string cash = rw["cash"].ToString();
                yield return new LoginDataDB(username, password, cash);
            }


        }

        public static IEnumerable<LoginDataDB> GetDbCredentials2() //used to get only the first row ***
        {
            string query = "SELECT * FROM [testDb].[dbo].[Creds]"; //this is the table from the db
            System.Data.DataTable tbl = GetDataTable(query, "Creds");
            DataRow rw = tbl.Rows[0]; //*** that is indicated here

            string username = rw["username"].ToString(); //select the row name
            string password = rw["password"].ToString();
            string cash = rw["cash"].ToString();
            yield return new LoginDataDB(username, password, cash);
        }
    }



}