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
}

Solution 1

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.

Solution 2

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