using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Data.OleDb;

public partial class FillingDataSets : System.Web.UI.Page
{
   OleDbConnection _conn = null;

   protected void Page_Load(object sender, EventArgs e)
   {
      if (!IsPostBack)
      {
       LoadAllArtWorks();
       panWork.Visible = false;
      }
   }

   private void LoadAllArtWorks()
   {
      DataSet ds = new DataSet();

      OleDbConnection conn = GetConnection();

      string sql = GetSql();
      OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);

      try
      {
         adapter.Fill(ds);

         lboxWork.DataSource = ds.Tables[0].DefaultView;
         lboxWork.DataBind();
      }
      catch (Exception ex)
      {
         labMsg.Text = "Error occurred accessing the database";
         labMsg.Text += "<br/>" + ex.Message;;
      }

   }

   private string GetSql()
   {
      return "SELECT ArtWorkID,ArtistID,Title,YearOfWork,Price,ImageFileName FROM ArtWorks";
   }

   private OleDbConnection GetConnection()
   {
      if (_conn == null)
      {
         // specifies database connection. Change this if needed
         string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
         connString += Server.MapPath("~/App_Data/ModernEyeCatalog.mdb") + ";";
         
         // get the connection to the database
         _conn = new OleDbConnection(connString);
      }
      return _conn;
   }

      /// <summary>
   /// Handles the selection of the art work
   /// </summary>
   protected void lboxWork_SelectedIndexChanged(object sender, EventArgs e)
   {
      panWork.Visible = true;

      // retrieve the art work id selected by user
      string sId = lboxWork.SelectedValue;

      DataSet ds = new DataSet();
      OleDbConnection conn = GetConnection();

      string sql = GetSql();
      sql += " WHERE ArtWorkID=@Id";

      // set up parameters to query
      OleDbParameter param = new OleDbParameter("@ID", sId);
      OleDbCommand cmdSelect = new OleDbCommand(sql,conn);
      cmdSelect.Parameters.Add(param);

      OleDbDataAdapter adapter = new OleDbDataAdapter();
      adapter.SelectCommand = cmdSelect;

      try
      {
         adapter.Fill(ds,"Work");

         DataRow dr = ds.Tables["Work"].Rows[0];

         txtId.Text = Convert.ToString(dr["ArtWorkID"]);
         int artistId = (int)dr["ArtistId"];
         MakeArtistList(artistId);

         txtTitle.Text = (string)dr["Title"];
         txtYear.Text = Convert.ToString(dr["YearOfWork"]);
         txtPrice.Text = Convert.ToString(dr["Price"]);
         txtFileName.Text = (string)dr["ImageFileName"];
         
      }
      catch (Exception ex)
      {
         labMsg.Text = "Error occurred accessing the database";
         labMsg.Text += "<br/>" + ex.Message; ;
      }
   }

   private void MakeArtistList(int artistId)
   {
      string sql = "SELECT ArtistId, LastName+', '+FirstName As Name ";
      sql += " FROM Artists ORDER BY LastName";

      DataTable dt = new DataTable();
      OleDbConnection conn = GetConnection();
      OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);

      try
      {
         adapter.Fill(dt);

         drpArtist.DataSource = dt.DefaultView;
         drpArtist.DataBind();
         drpArtist.SelectedValue = artistId.ToString();
      }
      catch (Exception ex)
      {
         labMsg.Text = "Error occurred accessing the database";
         labMsg.Text += "<br/>" + ex.Message; ;
      }
   }
}
