using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public class PaintingDB
{
    static SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);
    public static List<Painting> getAllPaintings()
    {

        List<Painting> paintingList = new List<Painting>();
        try
        {
            SqlCommand command = new SqlCommand("select P.id, A.id as Aid, P.Title, P.Price, P.imagefile, A.Name from Painting P, Artist A where P.artistId=A.id");
            command.Connection = connection;
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Painting p = new Painting();
                p.Id = Convert.ToInt32(reader["Id"]);
                p.Title = reader["title"].ToString();
                p.ImageFile = reader["imagefile"].ToString();
                p.Price = Convert.ToDecimal(reader["price"]);
                Artist a = new Artist();
                a.Id = reader["Aid"].ToString();
                a.Name = reader["name"].ToString();
                p.Artist = a;
                paintingList.Add(p);
            }
            reader.Close();
        }
        finally
        {
            connection.Close();
        }
        return paintingList;
    }

    public static int addPainting(Painting painting)
    {
        try
        {
            SqlCommand command = new SqlCommand("insert into Painting (categoryId, title, artistId, price, imageFile, status) values (@categoryId, @title, @artistId, @price, @imageFile, @status )");
            command.Parameters.AddWithValue("@categoryId", painting.Category.Name);
            command.Parameters.AddWithValue("@title", painting.Title);
            command.Parameters.AddWithValue("@artistId", painting.Artist.Id);
            command.Parameters.AddWithValue("@price", painting.Price);
            command.Parameters.AddWithValue("@imageFile", painting.ImageFile);
            command.Parameters.AddWithValue("@status", painting.Status);
            command.Connection = connection;
            connection.Open();
            if (command.ExecuteNonQuery() > 0)
            {
                command.CommandText = "Select @@identity";
                return Convert.ToInt32(command.ExecuteScalar());
            }
        }
        finally
        {
            connection.Close();
        }
        return -1;
    }
}