using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

using APNSoft.WebControls;

public class WebForm1 : System.Web.UI.Page
{

    //DataGrid declaration
    protected APNSoftDataGrid dg1; 


    private void Page_Load(object sender, System.EventArgs e)
    {
        //Set the DataGrid properties
        dg1.ID = "dg1";
        dg1.BasePage = base.Page;
        dg1.SkinFolder = "~/Skins/APNSoft/";
        dg1.KeyFieldName = "CustomerID";
        
        dg1.ClientSideOnContextMenuRow = "OnContextMenu";
        dg1.ContextMenuHeader.Template = "~/Templates/CntMnuHeaders.xml";
        dg1.ContextMenuRow.Template = "~/Templates/CntMnuRows.xml";
        dg1.AddContextMenu("MenuForBERGS", "~/Templates/CntMnuRowsAdd.xml");

        dg1.EditForm.Path="~/Templates/EditForm.ascx";
        dg1.EditForm.ShadowDepth=3;
        dg1.EditForm.ShadowColor=System.Drawing.ColorTranslator.FromHtml("#C0C0C0");
        dg1.EditForm.ShadowOpacity=35;
        dg1.EditForm.PositionInsert=APNSoft.WebControls.GridEditForm.PositionValues.CenterOfGrid;
        dg1.EditForm.PositionUpdate=APNSoft.WebControls.GridEditForm.PositionValues.CenterOfGrid;

        dg1.ConfirmTextDelete = "Are you sure?";


        //Apply server-side events
        dg1.InsertCommand += 
            new DataGridEventHandler(dg1_InsertCommand);
        dg1.UpdateCommand += 
            new DataGridEventHandler(dg1_UpdateCommand);
        dg1.DeleteCommand += 
            new DataGridEventHandler(dg1_DeleteCommand);

        //Define SQL query
        string SQL = @"SELECT CustomerID, CompanyName, ContactName, 
                Address FROM Customers ORDER BY CustomerID";
        
        //Create the DataTable object
        DataTable myDataTable = GetDataSource(SQL);

        //Add column for UpdateDelete
        DataColumn UpdateDelete = new DataColumn();
        UpdateDelete.ColumnName = "UpdateDelete";
        UpdateDelete.DataType = System.Type.GetType("System.String");
        UpdateDelete.DefaultValue = "";
        myDataTable.Columns.Add(UpdateDelete);

        //Set the data source
        dg1.DataSource = myDataTable;
        dg1.DataBind();

        //Set properties for the UpdateDelete column
        dg1.Columns["UpdateDelete"].HeaderText = "";
        dg1.Columns["UpdateDelete"].Template = "~/Templates/UpdateDelete.htm";
        dg1.Columns["UpdateDelete"].Sortable = false;
    }


    //Server-side procedure for Insert
    private void dg1_InsertCommand(object sender, DataGridEventArgs e)
    {
        //Declare database objects
        OleDbConnection conn = new OleDbConnection();
        conn.ConnectionString = 
            "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" +
            Server.MapPath("~/DataBase/Nwind.mdb");

        //Create SQL Query
        string SQL = @"INSERT INTO Customers 
        (CustomerID, CompanyName, ContactName, Address)
        VALUES
        (@CustomerID, @CompanyName, @ContactName, @Address)";

        //Create command
        OleDbCommand cmd = new OleDbCommand(SQL,conn);
            
        //Add parameters
        cmd.Parameters.Add("@CustomerID", OleDbType.VarChar).Value = e.EditFormFields["txtCustomerID"].ToString();
        cmd.Parameters.Add("@CompanyName", OleDbType.VarChar).Value = e.EditFormFields["txtCompanyName"].ToString();
        cmd.Parameters.Add("@ContactName", OleDbType.VarChar).Value = e.EditFormFields["txtContactName"].ToString();
        cmd.Parameters.Add("@Address", OleDbType.VarChar).Value = e.EditFormFields["txtAddress"].ToString();

        //Execute the query
        conn.Open();
        try
        {
            cmd.ExecuteNonQuery();
        }
        finally
        {
            cmd.Dispose();
            conn.Close();
        }        

        //Should be specified to select created record
        e.GridRowKeyValue = e.EditFormFields["txtCustomerID"].ToString();
    }



    //Server-side procedure for Update
    private void dg1_UpdateCommand(object sender, DataGridEventArgs e)
    {
        //Get the row
        GridRow myGridRow = e.GridRow;
        if(myGridRow == null)return;

        //Declare db objects
        OleDbConnection conn = new OleDbConnection();
        conn.ConnectionString = 
            "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" +
            Server.MapPath("~/DataBase/Nwind.mdb");

        //Create SQL Query
        string SQL = @"UPDATE Customers SET 
            CompanyName=@CompanyName, ContactName=@ContactName, Address=@Address
            WHERE CustomerID=@CustomerID";
            
        //Create command
        OleDbCommand cmd = new OleDbCommand(SQL,conn);
            
        //Add parameters
        cmd.Parameters.Add("@CompanyName", OleDbType.VarChar).Value = e.EditFormFields["txtCompanyName"].ToString();
        cmd.Parameters.Add("@ContactName", OleDbType.VarChar).Value = e.EditFormFields["txtContactName"].ToString();
        cmd.Parameters.Add("@Address", OleDbType.VarChar).Value = e.EditFormFields["txtAddress"].ToString();
        cmd.Parameters.Add("@CustomerID", OleDbType.VarChar).Value = e.EditFormFields["txtCustomerID"].ToString();

        //Execute the query
        conn.Open();
        try
        {
            cmd.ExecuteNonQuery();
        }
        finally
        {
            cmd.Dispose();
            conn.Close();
        }        
    }



    //Server-side procedure for Delete
    private void dg1_DeleteCommand(object sender, DataGridEventArgs e)
    {
        //Get the row
        GridRow myGridRow = e.GridRow;
        if(myGridRow == null)return;
            
        //Declare database objects
        OleDbConnection conn = new OleDbConnection();
        conn.ConnectionString = 
            "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" +
            Server.MapPath("~/DataBase/Nwind.mdb");

        //Create SQL Query
        string SQL = @"DELETE * FROM Customers WHERE CustomerID = @CustomerID";
            
        //Create command
        OleDbCommand cmd = new OleDbCommand(SQL,conn);
            
        //Add parameters
        cmd.Parameters.Add("@CustomerID", OleDbType.VarChar).Value = myGridRow.Cells["CustomerID"].Value;

        //Execute query
        conn.Open();
        try
        {
            cmd.ExecuteNonQuery();
        }
        finally
        {
            cmd.Dispose();
            conn.Close();
        }
    }




    //Returns a DataTable from MS Access database
    private DataTable GetDataSource(string query)
    {
        OleDbConnection conn = new OleDbConnection();
        OleDbDataAdapter MyOleDbDataAdapter = new OleDbDataAdapter();
        DataTable myDataTable = new DataTable();
        
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="+
            System.Web.HttpContext.Current.Server.MapPath("~/DataBase/Nwind.mdb");
        
        MyOleDbDataAdapter.SelectCommand = new OleDbCommand(query,conn);
        conn.Open();

        try
        {MyOleDbDataAdapter.Fill(myDataTable);}
        finally
        {conn.Close();}

        return myDataTable;
    }



    #region Web Form Designer generated code
    override protected void OnInit(EventArgs e)
    {
        InitializeComponent();
        base.OnInit(e);
    }
    
    private void InitializeComponent()
    {    
        this.Load += new System.EventHandler(this.Page_Load);
    }
    #endregion

}