asp.net web api with Mysql database

Question

I have a background in PHP and bump into a problem while trying out ASP.NET webAPI.

I have a MySQL database and a table that contains some files information. Now i want to make a "SELECT * FROM Files" and see the result in XML.

How can i render the result of the query that is returned?

This is my code at the moment,

The Model:

namespace ProductsApp.Models
{
    public class File
    {
        public int Id {get; set;}
        public string Text {get; set;}
    }
}

The Controller:

public IEnumerable<File> Get()
{
    try
    {
        command = conn.CreateCommand();
        command.CommandText = "SELECT * FROM Files";
        conn.Open();

        MySqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            //How to output the rows ????
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }

    return null; // return the list
}

code is text. posting PICTURES of text is essentially useless. — Marc B, Mar 23 '16 at 20:00
See answer to http://stackoverflow.com/questions/2948255/xml-file-creation-using-xdocument-in-c-sharp. — Kevin, Mar 23 '16 at 20:04

Nkosi · Accepted Answer · 2016-03-27T00:12:56.123

Making some assumptions about your File Table, this is how you get the data

public IEnumerable<File> Get()
{
    List<File> list = new List<File>();
    try
    {
        command = conn.CreateCommand();
        command.CommandText = "SELECT * FROM Files";
        conn.Open();

        using(MySqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                //How to output the rows ????
                int id = (int) reader["Id"];//Assuming column name is 'Id' and value if typeof(int)
                string text = (string) reader["Text"];//Assuming column name is `Text` and typeof(string)
                var file = new File {Id = id, Text = text};
                list.Add(file);
            }
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }

    return list; // return the list
}

As for the xml that is a formatting setting that needs to be allowed on the request.

Given the default setup of WebApi once the client making the call requests text/xml as the content type then the result should be parsed to xml and returned to the client.

If it is you want to force the response to always be xml then you need to make some changes to the response. One way is to set the Content-Type header before returning your result.

Response.Content.Headers.ContentType = MediaTypeHeaderValue.Parse("text/xml;charset=utf-8");

There are other ways you can do this and there are many answers on SO that will be able to show you.

Thank you! Im getting en exception 403 forbidden. Do i need to provide i connection string in the web.config to solve this 403? — Webbie, Mar 29 '16 at 13:42
Status code 403: Forbidden happens if you have authentication enabled for your service on the controller of globally for the api. Check for `[Authorize]` attribute on your controller. If you want to allow non authorized users on the specific action you can use the `[AllowAnonymous]` attribute on the action — Nkosi, Mar 29 '16 at 13:49
I have set [AllowAnonymous] to my method and the Controller class and im getting: **ex.Message = "Unable to connect to any of the specified MySQL hosts."** — Webbie, Mar 29 '16 at 19:42
Check your connection string and make sure it is correct. That is usually what that message means. — Nkosi, Mar 29 '16 at 20:44

score 2 · Answer 2 · answered Dec 09 '18 at 06:35

Webconfig:

     public static MySqlConnection conn()
    {
        string conn_string = "server=localhost;port=3306;database=testmvc;username=root;password=root;";


        MySqlConnection conn = new MySqlConnection(conn_string);

        return conn;
    }

Controller :

    public MySqlConnection con = WebApiConfig.conn();

    public IHttpActionResult GetAllProduct()
    {
        IList<product> pro = null;

        try
        {
            con.Open();
            MySqlCommand cmd = new MySqlCommand("select * from product", con);

            cmd.CommandType = CommandType.Text;

            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);

            pro = ds.Tables[0].AsEnumerable().Select(dataRow => new product { Pname = dataRow.Field<string>("pname"), Pid = dataRow.Field<int>("pid"), Pprice = dataRow.Field<decimal>("pprice") }).ToList();

        }
        finally
        {
            con.Close();
        }


        if (pro.Count == 0)
        {
            return NotFound();
        }

        return Ok(pro);
    }

    public IHttpActionResult PostNewProduct(product pro)
    {
        try
        {
            con.Open();

            MySqlCommand cmd = new MySqlCommand();

            cmd.Connection = con;
            cmd.CommandText = "SELECT MAX(pid) from product";

            cmd.CommandType = CommandType.Text;

            int maxid = Convert.ToInt16(cmd.ExecuteScalar().ToString())+1;


            cmd.CommandText = "insert into product values(" + maxid + ",'" + pro.Pname + "'," + pro.Pprice + ")";

            cmd.ExecuteNonQuery();

        }
        finally
        {
            con.Close();
        }

        return Ok();
    }

    public IHttpActionResult PutOldProduct(product pro)
    {

        string sql = "update product set pname='" + pro.Pname + "',pprice=" + pro.Pprice + " where pid=" + pro.Pid + "";
        try
        {
            con.Open();
            MySqlCommand cmd = new MySqlCommand(sql, con);

            cmd.CommandType = CommandType.Text;

            cmd.ExecuteNonQuery();

        }
        finally
        {
            con.Close();
        }

        return Ok();
    }

    public IHttpActionResult Delete(int id)
    {
        string sql = "delete from product where pid=" + id + "";
        try
        {
            con.Open();
            MySqlCommand cmd = new MySqlCommand(sql, con);

            cmd.CommandType = CommandType.Text;

            cmd.ExecuteNonQuery();

        }
        finally
        {
            con.Close();
        }

        return Ok();
    }

asp.net web api with Mysql database

2 Answers2