How to refactoring database connection method with data from API - c#

Issue

I try to change my database connection method with data from API and want to remove connection to database.

This is my database connection and I want to replace data from database with data from API.

public IEnumerable<AlertLevel> DataBaseConnection(int mapCode)
    {
        string ConnectionString = "server=192.168.1.1;uid=user;port=3333;pwd=password;database=dbName;";
        MySqlConnection Conn = new MySqlConnection(ConnectionString);
        var listAlert = new List<AlertLevel>();

        try
        {
            Conn.Open();

            //replace(2) with mapCode
            string query = "CALL Get_Alert_levels_Station(" + mapCode + ");";
            MySqlCommand myCommand = new MySqlCommand(query, Conn);
            MySqlDataReader myReader;

            myReader = myCommand.ExecuteReader();

            try
            {
                while (myReader.Read())
                {
                    var currentData = new AlertLevel()
                    {

                        dateForecast = myReader.GetDateTime(0),
                        levelForecast = myReader.GetInt32(1)

                    };

                    listAlert.Add(currentData);
                }
            }
            finally
            {
                myReader.Close();
                Conn.Close();
            }
        }

        catch (Exception ex)
        {
            Console.WriteLine("Database Connection", "Not Connected ..." + Environment.NewLine + ex.ToString(), "OK");
        }

        return listAlert;
    }

This is my methods with API data:

string GenerateRequestUri(string endpoint)
    {
        string requestUri = endpoint;
        requestUri += $"?id=16";

        return requestUri;
    }

    string GenerateRequestUriStations(string endpoint)
    {
        string requestUri = endpoint;
        requestUri += $"stations";

        return requestUri;
    }


    public WaterBindingData GetData()
    {
        var reusult = _restServiceData.GetWaterDataForecast(GenerateRequestUriStations(Constants.EndPoint), GenerateRequestUri(Constants.EndPoint));

        
        foreach (var item in reusult.WaterData.Ardaforecast[0].Items)
        {
            item.DateTimeForecast.ToString();
            item.AlertLevelForecast.ToString();
        }

        return reusult;

    }

I want to put inside in DataBaseConnection method my API logic and want to put item.DateTimeForecast.ToString(); and item.AlertLevelForecast.ToString(); in AlertLevel and also I don't know how to put dynamic variable in GenerateRequestUri(string endpoint): requestUri += $"?id=16"; 16 Have to be mapCode

Solution

I assume you want to do something like this

string GenerateRequestUri(string endpoint, mapCode)
{
    string requestUri = endpoint;
    requestUri += $"?id={mapCode}";

    return requestUri;
}

public IEnumerable<AlertLevel> GetDataFromAPI(int mapCode)
{
   var listAlert = new List<AlertLevel>();

   var reusult = _restServiceData.GetWaterDataForecast(GenerateRequestUriStations(Constants.EndPoint), GenerateRequestUri(Constants.EndPoint), mapCode);

        
        foreach (var item in reusult.WaterData.Ardaforecast[0].Items)
        {
            var currentData = new AlertLevel()
                    {
                        dateForecast = item.DateTimeForecast.ToString(),
                        levelForecast = item.AlertLevelForecast.ToString()
                    };

                    listAlert.Add(currentData);
        }

        return listAlert;

    }

Answered By - Jason