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();
}
}
}
|