ASP.NET Web API Connecting to MySQL Database

Question

I am new when it comes to C# and .Net applications however im trying to build a Web API that pulls data from a MySql database and displays it in JSON format when you reach that endpoint.

I am using MySql.Data.MySqlClient for this and intend on using Newtonsoft.JSON to serialise the result format in JSON formatting.

Code:

    [Route("api/[controller]")]
    [ApiController]
    public class TestController : ControllerBase
    {
        MySqlConnection conn = new MySqlConnection(@"server=localhost;port=3306;database=mysql;username=root;password=[PASSWORD];");

        [HttpGet]
        public string Get()
        {
            MySqlCommand query = conn.CreateCommand();
            query.CommandText = "SELECT * FROM engine_cost LIMIT 100";

            try
            {
                conn.Open();
            }
            catch (MySqlException ex)
            {
                throw ex;
            }

            MySqlDataReader fetch_query = query.ExecuteReader();

            while (fetch_query.Read())
            {
                return fetch_query.ToString(); // Result: "MySql.Data.MySqlClient.MySqlDataReader"
            }

            return "yay"; // Return JSON with Database values
        }
    }

I know the code is a bit messy but im really unsure on how to tackle this.

Thanks in advance

See this answer : https://stackoverflow.com/a/16166658/493521 — Matt Evans, Jun 02 '21 at 12:26
@MattEvans Thanks, still going through it. Finding this to be quite overwhelming — yush, Jun 02 '21 at 12:34

score 0 · Accepted Answer · answered Jun 02 '21 at 12:59

This is my solution:

    [Route("api/[controller]")]
    [ApiController]
    public class TestController : ControllerBase
    {
        MySqlConnection conn = new MySqlConnection("server=localhost;port=3306;database=mysql;username=root;password=[PASSWORD];");

        [HttpGet]
        public string Get()
        {
            string sqlCmd = "SELECT * FROM engine_cost LIMIT 100";

            MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM engine_cost LIMIT 100;", conn);

            da.SelectCommand.CommandType = CommandType.Text;

            DataTable dt = new DataTable();

            da.Fill(dt);

            if (dt.Rows.Count > 0)
            {
                return JsonConvert.SerializeObject(dt);
            }
            else
            {
                return "no data found";
            }
        }
    }

Why use `return JsonConvert.SerializeObject(dt);`? You could do `return dt;`, then ASP will create an `ObjectResult` and the default formatter for an object result is JSON so it will format `dt` as a JSON in the response. — Jossean Yamil, Jun 05 '21 at 02:51

ASP.NET Web API Connecting to MySQL Database

1 Answers1