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;
using APNSoft.WebControls;

public partial class DataGrid_RowInsertUpdateDelete : System.Web.UI.Page
{

    protected void Page_Load(object sender, EventArgs e)
    {

        //Apply server-side events
        myDataGrid.InsertCommand += new APNSoft.WebControls.DataGridEventHandler(myDataGrid_InsertCommand);
        myDataGrid.UpdateCommand += new APNSoft.WebControls.DataGridEventHandler(myDataGrid_UpdateCommand);
        myDataGrid.DeleteCommand += new APNSoft.WebControls.DataGridEventHandler(myDataGrid_DeleteCommand);

        //Define SQL query
        string SqlQuery = @"SELECT CustomerID, CompanyName, ContactName, Address, PostalCode " + 
            "FROM Customers ORDER BY CustomerID";

        //Get DataTable (MS Access Database)
        DataTable myDataSource = DataBase.GetDataTableOleDb(SqlQuery, "~/DataGrid/DataBases/Nwind.mdb");

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

        //Set the data source
        myDataGrid.KeyFieldName = "CustomerID";
        myDataGrid.DataSource = myDataSource;
        myDataGrid.DataBind();

        //Set properties for the UpdateDelete column
        myDataGrid.Columns["UpdateDelete"].HeaderText = "";
        myDataGrid.Columns["UpdateDelete"].Template = "~/DataGrid/Templates/UpdateDelete.html";
        myDataGrid.Columns["UpdateDelete"].Sortable = false;
        myDataGrid.Columns["UpdateDelete"].Width = 83;

    }


    //Server-side procedure for Insert
    private void myDataGrid_InsertCommand(object sender, DataGridEventArgs e)
    {
        //Declare database objects
        OleDbConnection conn = new OleDbConnection();
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + 
            Server.MapPath("~/DataGrid/DataBases/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 myDataGrid_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("~/DataGrid/DataBases/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 myDataGrid_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("~/DataGrid/DataBases/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();
        }
    }
}