Code :

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;

/// <summary>
/// Summary description for adoMain.cs
/// </summary>
public class adoMain
{
public string strResult;
private static readonly string _connectionString;
private int _sid; public int newsid;
private string _job;
private string _firstName;
private string _lastName;
private string _note;
public string strTime;
public string strDelete;
static adoMain()
{
_connectionString = WebConfigurationManager.ConnectionStrings["adoCnn"].ConnectionString;

}
public int sid
{
get { return _sid; }
set { _sid = value; }
}
public string Job
{
get { return _job; }
set { _job = value; }
}
public string FirstName
{
get { return _firstName; }
set { _firstName = value; }
}
public string LastName
{
get { return _lastName; }
set { _lastName = value; }
}
public string Note
{
get { return _note; }
set { _note = value; }
}
// public List<adoMain> GetAll()
public DataTable GetAll()
{
//List<adoMain> results = new List<adoMain>();
//SqlConnection cnn = new SqlConnection(_connectionString);
//SqlCommand cmd = new SqlCommand("SELECT * FROM Main2", cnn);
// cnn.StatisticsEnabled = true;
/* using (cnn)
{
//cnn.Open();
//SqlDataReader reader = cmd.ExecuteReader();
strResult += "<br/> Connected Loader";

while (reader.Read())
{
adoMain adObj = new adoMain();
// adObj.sid = (String)reader["sid"].ToString();
adObj.sid = (int)reader["sid"];
adObj.Job = (string)reader["Job"];
adObj.FirstName = (string)reader["FirstName"];
adObj.LastName = (string)reader["LastName"];
adObj.Note = (string)reader["Note"];
results.Add(adObj);
}
*/
DataTable dt = new DataTable();
try
{
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Main2", _connectionString);

adapter.Fill(dt);
strResult += "Data Loaded";

}
catch (Exception error)
{
strResult += "There was an error " + error.Message;
}return dt;
}
public void UpDate(int sid, string job, string firstname, string lastname, string note)
{
// Note parameter sid is a string than an integer, incontrast to AddNew or Delete
SqlConnection cnn = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("UPDATE Main2 SET FirstName =@Firstname,Job=@Job,LastName = @LastName, Note=@Note WHERE sid=@sid", cnn);
cmd.Parameters.AddWithValue("@Job", job);
cmd.Parameters.AddWithValue("@FirstName", firstname);
cmd.Parameters.AddWithValue("@LastName", lastname);
cmd.Parameters.AddWithValue("@Note", note);
cmd.Parameters.AddWithValue("@sid", sid);
try
{
using (cnn)
{
cnn.Open();
cmd.ExecuteNonQuery();
}
}
catch (Exception error)
{
strResult += " Could not Update " + error.Message.ToString();
}
finally
{
strResult = "Row Update Suceessfull :" + cnn.State.ToString(); ;
}
}
// methods to delete and add new
public void Delete(int sid)
{
SqlConnection cnn = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("DELETE FROM Main2 WHERE sid=@sid", cnn);
cmd.Parameters.AddWithValue("@sid", sid);
using (cnn)
{
cnn.Open();
cmd.ExecuteNonQuery();
}
strResult += "Row Delete Suceessfull";
}
public void AddNew(int sid, string job, string firstname, string lastname, string note)
{
SqlConnection cnn = new SqlConnection(_connectionString);
string strInput = "INSERT INTO Main2([sid],[Job],[FirstName],[LastName],[Note])VALUES (" + (sid + ",'" + job + "','" + firstname + "','" + lastname + "','" + note + "')");

SqlCommand cmd = new SqlCommand(strInput, cnn);
// Avoid Concatenation from SQL Injection attack

try
{
using (cnn)
{
cnn.Open();
cmd.ExecuteNonQuery();
strResult = "<br/>Records inserted ";
newsid = sid;
}

}
catch (Exception error)
{
strResult += " Could not insert " + error.Message.ToString();
}
finally
{
cnn.Close();
//strResult += "<br/>Row Additon Suceessfull :" + cnn.State.ToString();

}
}

}

 

Code Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" Debug="true"%>

<!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.AdoConnect4: Default Page DataTable</title>

<link href="main.css" rel="stylesheet" type="text/css" />
<style type="text/css">
.style1
{
width: 76px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>


<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
DeleteMethod="Delete" InsertMethod="AddNew" SelectMethod="GetAll"
TypeName="adoMain" UpdateMethod="UpDate" >
<DeleteParameters>
<asp:Parameter Name="sid" Type="Int32" />
</DeleteParameters>
<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>
<InsertParameters>
<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" />
</InsertParameters>
</asp:ObjectDataSource>


<br />
<table >
<tr>
<td>
&nbsp;</td>
<td>
This example uses DataTable and DataSet in an External Class adoMain.cs;
ObjectDataSource1, as the name suggest would bind to the data generated by that&nbsp;
class/objects. </td>
</tr>
<tr>
<td>
&nbsp;</td>
<td>
You can Use this one : Also compare&nbsp;&nbsp;
<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="~/AddData.aspx">Second Page</asp:HyperLink>
&nbsp;where we have identical
</td>
</tr>
<tr>
<td>
&nbsp;</td>
<td>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataSourceID="ObjectDataSource1" onrowdeleted="GridView1_RowDeleted">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
<asp:BoundField DataField="sid" HeaderText="sid" SortExpression="sid" />
<asp:BoundField DataField="Job" HeaderText="Job" SortExpression="Job" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName"
SortExpression="FirstName" />
<asp:BoundField DataField="LastName" HeaderText="LastName"
SortExpression="LastName" />
<asp:BoundField DataField="Note" HeaderText="Note" SortExpression="Note" />
</Columns>
</asp:GridView>
</td>
</tr>
<tr>
<td>
&nbsp;</td>
<td>
&nbsp;</td>
</tr>
<tr>
<td>
&nbsp;</td>
<td style="color: #FF0000">
Below is a SqlDatasoure that binds direct to the database and capable of
updating and deleting Rows in contast to the example mm.AdoConnet3</td>
</tr>
<tr>
<td>
&nbsp;</td>
<td>
&nbsp;</td>
</tr>
<tr>
<td>
&nbsp;</td>
<td>
<asp:Label ID="L1" runat="server" Text="Label"></asp:Label>
</td>
</tr>
</table>
</div>

<table >
<tr>
<td class="style1">
&nbsp;</td>
<td>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:adoCnn2 %>"
DeleteCommand="DELETE FROM [Main2] WHERE [sid] = @original_sid"
InsertCommand="INSERT INTO [Main2] ([sid], [Job], [FirstName], [LastName], [Note]) VALUES (@sid, @Job, @FirstName, @LastName, @Note)"
OldValuesParameterFormatString="original_{0}"
SelectCommand="SELECT * FROM [Main2]"

UpdateCommand="UPDATE [Main2] SET [Job] = @Job, [FirstName] = @FirstName, [LastName] = @LastName, [Note] = @Note WHERE [sid] = @original_sid"
onupdated="SqlDataSource1_Updated" ondeleted="SqlDataSource1_Deleted">
<DeleteParameters>
<asp:Parameter Name="original_sid" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="Job" Type="String" />
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="Note" Type="String" />
<asp:Parameter Name="original_sid" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<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" />
</InsertParameters>
</asp:SqlDataSource>
</td>
</tr>
<tr>
<td class="style1">
&nbsp;</td>
<td>
<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False"
DataKeyNames="sid" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
<asp:BoundField DataField="sid" HeaderText="sid" ReadOnly="True"
SortExpression="sid" />
<asp:BoundField DataField="Job" HeaderText="Job" SortExpression="Job" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName"
SortExpression="FirstName" />
<asp:BoundField DataField="LastName" HeaderText="LastName"
SortExpression="LastName" />
<asp:BoundField DataField="Note" HeaderText="Note" SortExpression="Note" />
</Columns>
</asp:GridView>
</td>
</tr>
<tr>
<td class="style1">
&nbsp;</td>
<td>
&nbsp;</td>
</tr>
</table>
</form>
</body>
</html>
 

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.Collections.Generic;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
L1.Text += "";
adoMain adObj = new adoMain();
L1.Text += adObj.strResult;



}
protected void GridView1_RowDeleted(object sender, GridViewDeletedEventArgs e)
{
if (e.Exception == null)
{
L1.Text += "GridView1 Edited Successfully";
}

}
protected void SqlDataSource1_Updated(object sender, SqlDataSourceStatusEventArgs e)
{
try
{
if (e.AffectedRows > 0)
{
L2.Text += "SQL Update successful";
}
}
catch (Exception error)
{
L2.Text = error.Message.ToString();
}
}
protected void SqlDataSource1_Deleted(object sender, SqlDataSourceStatusEventArgs e)
{
try
{
if (e.AffectedRows > 0)
{
L2.Text += "SQL delete successful";
}
}
catch (Exception error)
{
L2.Text += error.Message.ToString();
}
}
protected void GridView2_RowUpdated(object sender, GridViewUpdatedEventArgs e)
{
try
{

if (e.AffectedRows > 0)
{
L2.Text += " Grid changed ";
}

}
catch(Exception error)
{
L2.Text += " GridView2 Error " +error.Message.ToString();
}
}

}

 

The SqlDataSource did not work

 The sqlDataSource code that worked

<asp:SqlDataSource ID="SqlDataSource1" runat="server" DataSourceMode="DataSet"
ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:adoCnn2 %>"
DeleteCommand="DELETE FROM [Main2] WHERE [sid] = @original_sid AND [Job] = @original_Job AND [FirstName] = @original_FirstName AND [LastName] = @original_LastName AND [Note] = @original_Note"
InsertCommand="INSERT INTO [Main2] ([sid], [Job], [FirstName], [LastName], [Note]) VALUES (@sid, @Job, @FirstName, @LastName, @Note)"
OldValuesParameterFormatString="original_{0}"
SelectCommand="SELECT * FROM [Main2]"
UpdateCommand="UPDATE [Main2] SET [Job] = @Job, [FirstName] = @FirstName, [LastName] = @LastName, [Note] = @Note WHERE [sid] = @original_sid AND [Job] = @original_Job AND [FirstName] = @original_FirstName AND [LastName] = @original_LastName AND [Note] = @original_Note">
<DeleteParameters>
<asp:Parameter Name="original_sid" Type="Int32" />
<asp:Parameter Name="original_Job" Type="String" />
<asp:Parameter Name="original_FirstName" Type="String" />
<asp:Parameter Name="original_LastName" Type="String" />
<asp:Parameter Name="original_Note" Type="String" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="Job" Type="String" />
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="Note" Type="String" />
<asp:Parameter Name="original_sid" Type="Int32" />
<asp:Parameter Name="original_Job" Type="String" />
<asp:Parameter Name="original_FirstName" Type="String" />
<asp:Parameter Name="original_LastName" Type="String" />
<asp:Parameter Name="original_Note" Type="String" />
</UpdateParameters>
<InsertParameters>
<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" />
</InsertParameters>
</asp:SqlDataSource>

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:adoCnn2 %>"
DeleteCommand="DELETE FROM [Main2] WHERE [sid] = @original_sid"
InsertCommand="INSERT INTO [Main2] ([sid], [Job], [FirstName], [LastName], [Note]) VALUES (@sid, @Job, @FirstName, @LastName, @Note)"
OldValuesParameterFormatString="original_{0}"
SelectCommand="SELECT * FROM [Main2]"

UpdateCommand="UPDATE [Main2] SET [Job] = @Job, [FirstName] = @FirstName, [LastName] = @LastName, [Note] = @Note WHERE [sid] = @original_sid"
onupdated="SqlDataSource1_Updated" ondeleted="SqlDataSource1_Deleted">
<DeleteParameters>
<asp:Parameter Name="original_sid" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="Job" Type="String" />
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="Note" Type="String" />
<asp:Parameter Name="original_sid" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<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" />
</InsertParameters>
</asp:SqlDataSource>

 

AddData.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AddData.aspx.cs" Inherits="AddData" Debug="true"%>

<!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 id="Head1" runat="server">
<title>mm.AdoConnectd 3: Connected ADO Add New</title>
<link id="Link1" type="text/css" rel="Stylesheet" href="~/main.css" runat="server"/>
</head>
<body>
<form id="form1" runat="server">
<div>
<table style="border: 4px solid #800000; width:100%;">
<tr>
<td >sid</td> <td>
<asp:TextBox ID="TextBox1" runat="server" Width="226px"
></asp:TextBox>
</td>
</tr>
<tr>
<td >Job</td> <td>
<asp:TextBox ID="TextBox2" runat="server" Width="226px"></asp:TextBox>
</td>
</tr><tr>
<td >FirstName</td> <td>
<asp:TextBox ID="TextBox3" runat="server" Width="226px"></asp:TextBox>
</td>
</tr><tr>
<td >LastName</td> <td>
<asp:TextBox ID="TextBox4" runat="server" Width="226px"></asp:TextBox>
</td>
</tr><tr>
<td >Note</td> <td>
<asp:TextBox ID="TextBox5" runat="server" Height="80px" Width="226px"
style="margin-left: 0px"></asp:TextBox>
</td>
</tr>
<tr>
<td >
&nbsp;</td> <td>
<asp:Button ID="Btn_Insert" runat="server" onclick="Button1_Click" Text="Inser Values" PostBackUrl="~/AddData.aspx"
Width="106px" />
<asp:Button ID="Btn_Reset" runat="server" Text="Reset" onclick="Button2_Click"
Width="86px" />
</td>
</tr>
<tr>
<td >Editor Table :</td> <td>&nbsp;Add New Records &nbsp&nbsp&nbsp
&nbsp;&nbsp;&nbsp;
<asp:HyperLink ID="HyperLink2" runat="server" NavigateUrl="~/Default.aspx">|
Home</asp:HyperLink>
</td>
</tr>
<tr>
<td >&nbsp;</td> <td>
<asp:Label ID="L1" runat="server" Text="Label"></asp:Label>
</td>
</tr>
<tr>
<td >&nbsp;</td> <td>
<asp:Label ID="L2" runat="server" Text="Label"></asp:Label>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>

AddData.aspx.cs

using System;
using System.Collections;
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;

public partial class AddData : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
String str1 = TextBox1.Text;
int n1 = System.Convert.ToInt32(str1);
//Int32 n1 = Convert.ToInt32(str);
//Inserting a Record
adoMain newrecord = new adoMain();
newrecord.AddNew(n1, TextBox2.Text, TextBox3.Text, TextBox4.Text, TextBox5.Text);
L1.Text = "Added : " + newrecord.strResult;
L2.Text = "Idenx Id: " + newrecord.newsid.ToString();
str1 = "";
}
protected void Button2_Click(object sender, EventArgs e)
{
//clear Texr Reset
TextBox1.Text = ""; TextBox2.Text = ""; TextBox3.Text = ""; TextBox4.Text = ""; TextBox5.Text = "";

}
}