In the example LINK, we used DataTable and UpDate method to change the database row directly, here in this example we are created an additional layer of security with HTTPHanlders, unless the you are ready commit with an event the data change will not affect the physical database.
update web.config
<connectionStrings>
<add name="adoCnn" connectionString="Data Source=MANAS6\SQLEXPRESS;Initial Catalog=adoConnect1;Integrated Security=True"
providerName="System.Data.SqlClient;" />
<add name="adoCnn2" connectionString="Data Source=MANAS6\SQLEXPRESS;Initial Catalog=adoConnect1;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
style sheet
form
{
width:600px; background-color:#FFFFCC; height:auto;
}
#div1
{
position:absolute; background-color: #FFFFCC;z-index:2;
top: 350px; left: 10px;width: 500px; height:auto;
}
#div2
{
position:absolute; background-color: #FFFFCC; z-index:1;
top: 15px; left:10px;width: 550px; height:auto; padding-left:20px;
}
body
{
background-color: Gray;
}
.txtSmall
{
width:100px; height:20px;
}
.txttiny
{
width:50px; height:20px;
}
table
{
width:550px; border-color:Navy; border-width:4px;
}
Add a class in App_Code folder
Run time analysis
However, the changes did on alter any data in the physical database as shown below.
Now click on Commit
It appeared that ROLLOver is not effective once the SQLAdapter updates the (update) and DataTable (bridge over user interface and database) accept changes with AcceptChanges() method; you wo't be able RollBack.
Testing Local RollBack
Edit a row but don't hit commit button,
The data in the designated field did not change. Now click Rollback
Code adoMain.cs
using System;
using System.Data;
using System.Web.Configuration;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Collections;
using System.Collections.Generic;
// HttpContext
using System.Web;
/// <summary>
/// Summary description for adoMain
/// </summary>
public class adoMain
{
public string strResult; private int newsid; public string strCommit;
private string _connectionString = WebConfigurationManager.ConnectionStrings["adoCnn2"].ConnectionString;
private SqlDataAdapter sqlAdapter = new SqlDataAdapter();
public string constructor;
public adoMain()
{
//
// TODO: Add constructor logic here
//
string cmd = "Select sid, Job, FirstName, LastName, Note FROM Main3";
sqlAdapter =new SqlDataAdapter(cmd, _connectionString);
constructor += " Adpater created ";
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(sqlAdapter);
sqlAdapter.UpdateBatchSize = 0;
HttpContext hContext = HttpContext.Current;
if (hContext.Session["myAdoSession"] == null)
{
DataTable dtMain3 = new DataTable();
sqlAdapter.Fill(dtMain3);
dtMain3.PrimaryKey = new DataColumn[]{ dtMain3.Columns["sid"]};
hContext.Session["myAdoSession"] = dtMain3;
}
}
public DataTable GetAll()
{
return (DataTable)HttpContext.Current.Session["myAdoSession"];
}
public void UpDate(int sid, string job, string firstname, string lastname, string note)
{
DataTable dUdate = (DataTable)HttpContext.Current.Session["myAdoSession"];
newsid = sid;
try
{
DataRow rowUpdate = dUdate.Rows.Find(sid);
rowUpdate["Job"] = job;
rowUpdate["FirstName"] = firstname;
rowUpdate["LastName"] = lastname;
rowUpdate["Note"] = note;
strResult = "Update with sid " + newsid;
}
catch(Exception error)
{
strResult = "Theare was eror" + error.Message.ToString();
}
}
public void AcceptChanges()
{
DataTable acceptRow = (DataTable)HttpContext.Current.Session["myAdoSession"];
sqlAdapter.Update(acceptRow);
acceptRow.AcceptChanges();
strCommit = " Data AcceptChanges : Committed";
}
public void RejectChanges()
{
DataTable dTC = (DataTable)HttpContext.Current.Session["myAdoSession"];
dTC.RejectChanges();
strCommit = " Data AcceptChanges : RolledBack";
}
}
Code Default.aspx.cs
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Web.DataAccess;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
adoMain adoobj = new adoMain();
L1.Text = adoobj.constructor;
}
protected void commit_Click(object sender, EventArgs e)
{
adoMain adoCom = new adoMain();
adoCom.AcceptChanges();
GridView1.DataBind();
L1.Text = adoCom.strResult;
L2.Text = adoCom.strCommit;
}
protected void rollback_Click(object sender, EventArgs e)
{
adoMain adoRoll = new adoMain();
adoRoll.RejectChanges();
GridView1.DataBind();
L1.Text = adoRoll.strResult;
L2.Text = adoRoll.strCommit;
}
}
code Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>mm.AdoConnect5 :Default HttpContext</title>
<link href="main.css" rel="stylesheet" type="text/css" />
<style type="text/css">
.style1
{
width: 11px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
SelectMethod="GetAll" TypeName="adoMain" UpdateMethod="UpDate">
<UpdateParameters>
<asp:Parameter Name="sid" Type="Int32" />
<asp:Parameter Name="job" Type="String" />
<asp:Parameter Name="firstname" Type="String" />
<asp:Parameter Name="lastname" Type="String" />
<asp:Parameter Name="note" Type="String" />
</UpdateParameters>
</asp:ObjectDataSource>
<br />
<table >
<tr>
<td class="style1">
</td>
<td colspan="2">
<asp:Label ID="L1" runat="server" Text="Label"></asp:Label><br />
<asp:Label ID="L2" runat="server" Text="Label"></asp:Label>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateEditButton="true"
DataSourceID="ObjectDataSource1">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<%# ((System.Data.DataRowView)Container.DataItem).Row.RowState %>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</td>
</tr>
<tr>
<td class="style1">
</td>
<td colspan="2">
</td>
</tr>
<tr>
<td class="style1">
</td>
<td>
<asp:LinkButton ID="commit" runat="server" onclick="commit_Click">Commit</asp:LinkButton>
</td>
<td>
<asp:LinkButton ID="rollback" runat="server" onclick="rollback_Click">RollBack</asp:LinkButton>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>