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


public class ArtistDB
{
    static SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);

    public static int insertItem(string desc)
    {
        try
        {
            SqlCommand command = new SqlCommand("insert into Item values (@desc)");
            command.Parameters.AddWithValue("@desc", desc);
            command.Connection = connection;
            connection.Open();
            if (command.ExecuteNonQuery() > 0)
            {
                command.CommandText = "Select @@identity";
                return Convert.ToInt32(command.ExecuteScalar());
            }
        }
        finally
        {
            connection.Close();
        }
        return -1;
    }

    public static int insertArtist(Artist a)
    {
        try
        {
            SqlCommand command = new SqlCommand("insertArtist");
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@id", a.Id);
            command.Parameters.AddWithValue("@dob", a.Dob);
            command.Parameters.AddWithValue("@name", a.Name);
            command.Parameters.AddWithValue("@profile", a.Profile);
            command.Parameters.AddWithValue("@imagefile", a.ImageFile);
            command.Connection = connection;
            connection.Open();
            return command.ExecuteNonQuery();
        }
        finally
        {
            connection.Close();
        }
    }

    public static int insertArtist1(Artist a)
    {
        try
        {
            SqlCommand command = new SqlCommand("insert into artist values (@id, @dob, @name, @profile, @imagefile)");
            command.Parameters.AddWithValue("@id", a.Id);
            command.Parameters.AddWithValue("@dob", a.Dob);
            command.Parameters.AddWithValue("@name", a.Name);
            command.Parameters.AddWithValue("@profile", a.Profile);
            command.Parameters.AddWithValue("@imagefile", a.ImageFile);
            command.Connection = connection;
            connection.Open();
            return command.ExecuteNonQuery();
        }
        finally
        {
            connection.Close();
        }
    }



    public static List<Artist> getArtistByName(string name)
    {
        List<Artist> artistList = new List<Artist>();
        try
        {
            SqlCommand command = new SqlCommand("Select * from Artist where name like @name");
            command.Parameters.AddWithValue("@name", "%" + name + "%");
            command.Connection = connection;
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                Artist a = new Artist(reader["Id"].ToString(), reader["Name"].ToString(),
                            Convert.ToDateTime(reader["Dob"]), reader["profile"].ToString(), reader["imagefile"].ToString());
                artistList.Add(a);
            }
            reader.Close();
        }
        finally
        {
            connection.Close();
        }
        return artistList;
    }

    public static List<Artist> getAllArtists()
    {

        List<Artist> artistList = new List<Artist>();
        try
        {
            SqlCommand command = new SqlCommand("Select * from Artist");
            command.Connection = connection;
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                Artist a = new Artist(reader["Id"].ToString(), reader["Name"].ToString(),
                            Convert.ToDateTime(reader["Dob"]), reader["profile"].ToString(), reader["imagefile"].ToString());
                artistList.Add(a);
            }
            reader.Close();
        }
        finally
        {
            connection.Close();
        }
        return artistList;
    }
}