public Wine Insert(Wine wine)
        {
            MySqlConnection conn   = new MySqlConnection(connectionString);
            Wine            result = null;

            try {
                conn.Open();
                string       sql = @"insert into wine(winename, grapes, country, region, year, description, picture)
                               values(@name, @grapes, @country, @region, @year, @description, @picture)";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("name", wine.name);
                cmd.Parameters.AddWithValue("grapes", wine.grapes);
                cmd.Parameters.AddWithValue("country", wine.country);
                cmd.Parameters.AddWithValue("region", wine.region);
                cmd.Parameters.AddWithValue("year", wine.year);
                cmd.Parameters.AddWithValue("description", wine.description);
                cmd.Parameters.AddWithValue("picture", wine.picture);

                cmd.ExecuteNonQuery();
                wine.id = cmd.LastInsertedId;
                result  = wine;
            } catch (Exception) { } finally {
                conn.Close();
            }
            return(wine);
        }
        public List <Wine> GetWines()
        {
            List <Wine>     wines = new List <Wine>();
            Wine            item;
            MySqlConnection conn = new MySqlConnection(connectionString);

            try {
                conn.Open();

                string          sql    = "select * from wine";
                MySqlCommand    cmd    = new MySqlCommand(sql, conn);
                MySqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    item             = new Wine();
                    item.id          = reader.GetInt64(0);
                    item.name        = reader.GetString(1);
                    item.grapes      = reader.GetString(2);
                    item.country     = reader.GetString(3);
                    item.region      = reader.GetString(4);
                    item.year        = reader.GetString(5);
                    item.description = reader.GetString(6);
                    item.picture     = reader.IsDBNull(7) ? null : reader.GetString(7);

                    wines.Add(item);
                }
                reader.Close();
            } catch (Exception) {
            } finally {
                conn.Close();
            }

            return(wines);
        }
        public Wine GetWine(long id)
        {
            Wine            item = null;
            MySqlConnection conn = new MySqlConnection(connectionString);

            try {
                conn.Open();

                string       sql = "select * from wine where id=@id";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("id", id);
                MySqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    item             = new Wine();
                    item.id          = reader.GetInt64(0);
                    item.name        = reader.GetString(1);
                    item.grapes      = reader.GetString(2);
                    item.country     = reader.GetString(3);
                    item.region      = reader.GetString(4);
                    item.year        = reader.GetString(5);
                    item.description = reader.GetString(6);
                    item.picture     = reader.IsDBNull(7) ? null : reader.GetString(7);
                    break;
                }
                reader.Close();
            } catch (Exception) {
            } finally {
                conn.Close();
            }

            return(item);
        }
        public List<Wine> GetWines()
        {
            List<Wine> wines = new List<Wine>();
            Wine item;
            MySqlConnection conn = new MySqlConnection(connectionString);
            try {
                conn.Open();

                string sql = "select * from wine";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                MySqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read()) {
                    item = new Wine();
                    item.id = reader.GetInt64(0);
                    item.name = reader.GetString(1);
                    item.grapes = reader.GetString(2);
                    item.country = reader.GetString(3);
                    item.region = reader.GetString(4);
                    item.year = reader.GetString(5);
                    item.description = reader.GetString(6);
                    item.picture = reader.IsDBNull(7) ? null : reader.GetString(7);

                    wines.Add(item);
                }
                reader.Close();
            } catch (Exception) {

            } finally {
                conn.Close();
            }

            return wines;
        }
        public int Update(Wine wine)
        {
            MySqlConnection conn         = new MySqlConnection(connectionString);
            int             rowsAffected = -1;

            try {
                conn.Open();
                string       sql = @"update wine
                               set
                               winename = @name, 
                               grapes = @grapes, 
                               country = @country, 
                               region = @region, 
                               year = @year, 
                               description = @description, 
                               picture = @picture
                               where id=@id";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("id", wine.id);
                cmd.Parameters.AddWithValue("name", wine.name);
                cmd.Parameters.AddWithValue("grapes", wine.grapes);
                cmd.Parameters.AddWithValue("country", wine.country);
                cmd.Parameters.AddWithValue("region", wine.region);
                cmd.Parameters.AddWithValue("year", wine.year);
                cmd.Parameters.AddWithValue("description", wine.description);
                cmd.Parameters.AddWithValue("picture", wine.picture);

                rowsAffected = cmd.ExecuteNonQuery();
            } catch (Exception) { } finally {
                conn.Close();
            }
            return(rowsAffected);
        }
        public Wine GetWine(long id)
        {
            Wine item = null;
            MySqlConnection conn = new MySqlConnection(connectionString);
            try {
                conn.Open();

                string sql = "select * from wine where id=@id";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("id", id);
                MySqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read()) {
                    item = new Wine();
                    item.id = reader.GetInt64(0);
                    item.name = reader.GetString(1);
                    item.grapes = reader.GetString(2);
                    item.country = reader.GetString(3);
                    item.region = reader.GetString(4);
                    item.year = reader.GetString(5);
                    item.description = reader.GetString(6);
                    item.picture = reader.IsDBNull(7) ? null : reader.GetString(7);
                    break;
                }
                reader.Close();
            } catch (Exception) {

            } finally {
                conn.Close();
            }

            return item;
        }
        public JsonResult Insert(Wine wine)
        {
            Response.AppendHeader("Access-Control-Allow-Origin", "*");
            WineDa da = new WineDa();

            Wine result = da.Insert(wine);

            return Json(result);
        }
        public void InsertWine()
        {
            Wine wine = new Wine();
            wine.name = "unit test wine";
            wine.grapes = "23";
            wine.country = "Bolivia";
            wine.region = "Sucre";
            wine.year = "2013";
            wine.description = "No description";
            wine.picture = "wine1.png";

            WineDa da = new WineDa();
            Wine result = da.Insert(wine);
            Assert.IsTrue(result != null);
        }
        public void UpdateWine()
        {
            Wine wine = new Wine();
            wine.id = 4;
            wine.name = "unit test wine";
            wine.grapes = "23";
            wine.country = "Bolivia";
            wine.region = "Bermejo";
            wine.year = "2013";
            wine.description = "No description";
            wine.picture = "wine2.png";

            WineDa da = new WineDa();
            int rowsAffected = da.Update(wine);
            Assert.IsTrue(rowsAffected >= 0);
        }
        public JsonResult Update(long wineId, Wine wine)
        {
            Response.AppendHeader("Access-Control-Allow-Origin", "*");
            WineDa da = new WineDa();
            wine.id = wineId;
            int rows = da.Update(wine);

            int status;
            string message;
            if (rows > 0) {
                status = 200;
                message = "updated";
            } else {
                status = 400;
                message = "error";
            }

            return Json(new { Status = status, Message = message });
        }
        public int Update(Wine wine)
        {
            MySqlConnection conn = new MySqlConnection(connectionString);
            int rowsAffected = -1;
            try {
                conn.Open();
                string sql = @"update wine
                               set
                               winename = @name,
                               grapes = @grapes,
                               country = @country,
                               region = @region,
                               year = @year,
                               description = @description,
                               picture = @picture
                               where id=@id";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("id", wine.id);
                cmd.Parameters.AddWithValue("name", wine.name);
                cmd.Parameters.AddWithValue("grapes", wine.grapes);
                cmd.Parameters.AddWithValue("country", wine.country);
                cmd.Parameters.AddWithValue("region", wine.region);
                cmd.Parameters.AddWithValue("year", wine.year);
                cmd.Parameters.AddWithValue("description", wine.description);
                cmd.Parameters.AddWithValue("picture", wine.picture);

                rowsAffected = cmd.ExecuteNonQuery();
            } catch (Exception) { } finally {
                conn.Close();
            }
            return rowsAffected;
        }
        public Wine Insert(Wine wine)
        {
            MySqlConnection conn = new MySqlConnection(connectionString);
            Wine result = null;
            try {
                conn.Open();
                string sql = @"insert into wine(winename, grapes, country, region, year, description, picture)
                               values(@name, @grapes, @country, @region, @year, @description, @picture)";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("name", wine.name);
                cmd.Parameters.AddWithValue("grapes", wine.grapes);
                cmd.Parameters.AddWithValue("country", wine.country);
                cmd.Parameters.AddWithValue("region", wine.region);
                cmd.Parameters.AddWithValue("year", wine.year);
                cmd.Parameters.AddWithValue("description", wine.description);
                cmd.Parameters.AddWithValue("picture", wine.picture);

                cmd.ExecuteNonQuery();
                wine.id = cmd.LastInsertedId;
                result = wine;
            } catch (Exception) { } finally {
                conn.Close();
            }
            return wine;
        }