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 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
}

user_10763639 · Answer

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 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("pname"), Pid = dataRow.Field("pid"), Pprice = dataRow.Field("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 Answers