Ado-connection

This an example of Ado. Connect model ( 3rd document of the Series 1, 2 and 3)

• Adding  record

  adoMain newrecord = new adoMain();
  newrecord.AddNew(n1, TextBox2.Text, TextBox3.Text, TextBox4.Text, TextBox5.Text);

• Update
• Delete Record

Step1: Create New Website http://manas6/aspnet.35/mm.AdoConnect3/Default.aspx

Add one Default.aspx

Add a Class adoMain.cs

Step 2: Edit Web.config

<connectionStrings>
<add name="adoCnn" connectionString="Data Source=MANAS6\SQLEXPRESS;Initial Catalog=adoConnect1;Integrated Security=True" providerName="System.Data.SqlClient;"/>
</connectionStrings>

Then addd/Edit code in adoMain.cs

using System;
using System.Data;
using System.Web.Configuration;
using System.Data.SqlClient;
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()
{
List<adoMain> results = new List<adoMain>();
SqlConnection cnn = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("SELECT * FROM Main", cnn);
cnn.StatisticsEnabled = true;
using (cnn)
{
cnn.Open();
SqlDataReader reader = cmd.ExecuteReader();
strResult += "<br/> Connected and DataReader 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);
}
}

IDictionary currentStatistics = cnn.RetrieveStatistics();
long bytesReceived = (long)currentStatistics["BytesReceived"];
long bytesSent = (long)currentStatistics["BytesSent"];
strResult += "Data Loaded";
return results;
}
public void UpDate(string 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 Main SET FirstName =@Firstname,Job=@Job,LastName = @LastName, Note=@Note WHERE sid=@sid", cnn);
cmd.Parameters.AddWithValue("@sid", sid);
cmd.Parameters.AddWithValue("@Job", job);
cmd.Parameters.AddWithValue("@FirstName", firstname);
cmd.Parameters.AddWithValue("@LastName", lastname);
cmd.Parameters.AddWithValue("@Note", note);

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(); ;
}
}
public void Delete(int sid)
{
SqlConnection cnn = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("DELETE FROM Main 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 Main([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();
}
}
}

In case you need to insert data or corrupted data (as happened once use this sql strip to reload data into the Main table

INSERT INTO Main([sid],[Job],[FirstName],[LastName],[Note])VALUES (1001, "Manager", "James", "Smith", "higher ed");
INSERT INTO Main([sid],[Job],[FirstName],[LastName],[Note])VALUES (1002, "President", "Peter", "Hudson", "10 years exp");
INSERT INTO Main([sid],[Job],[FirstName],[LastName],[Note])VALUES (1003, "Tech Level3", "David", "Parker", "IT-Assembly");
INSERT INTO Main([sid],[Job],[FirstName],[LastName],[Note])VALUES (1004, "Tech", "Sue", "Jackson", "IT-Maintain"
INSERT INTO Main([sid],[Job],[FirstName],[LastName],[Note])VALUES (1005, "Manager-Senior", "John", "Doe", "Note");
INSERT INTO Main([sid],[Job],[FirstName],[LastName],[Note])VALUES (1006, "Video Tech", "Emanul", "Frisco", "Video Grapher");
 

Step 4:  Add One ObjectSourceControl and GridView Control to Default page

Pick the class you added to your web site

Choose load Method and (do not select any other method) click Finish.

Now Set GridView DataSource reference to this data control.

Code main.css

form
{
width:500px; 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:350px;
}

 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.adoConnect 3: Home Default</title>
<link id="Link1" type="text/css" rel="Stylesheet" href="~/main.css" runat="server"/>
</head>
<body>
<form id="form1" runat="server">
<div id="div2">
This Home page&nbsp;
<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="~/AddData.aspx">Add Data</asp:HyperLink>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<asp:HyperLink ID="HyperLink2" runat="server" NavigateUrl="~/EditDelete.aspx">Edit/Delete</asp:HyperLink>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataSourceID="ObjectDataSource1" BorderColor="#FFCC99"
BorderStyle="Groove" BorderWidth="4px" Width="503px">
<Columns>
<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>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
SelectMethod="GetAll" TypeName="adoMain"></asp:ObjectDataSource>
</div>
</form>
</body>
</html>
 

 

RunTime View of Default.aspx

Step 5: Now add two more web pages

Step 6: code EditDelete.aspx : In this web page, update is controlled by Object DataSource and deleting a row is managed with custom (minimum) codes.

Configure ObjectDataSource

 

Code EditDelete.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="EditDelete.aspx.cs" Inherits="_EditDelete" 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 Update delete</title>
<link type="text/css" rel="Stylesheet" href="~/main.css" runat="server"/>

<style type="text/css">
#Reset1
{
width: 141px;
}
</style>
</head>
<body>
<form id="form1" runat="server">

<div id="div2">

<table >
<tr>
<td colspan="3" >
<asp:ObjectDataSource ID="adoObj" TypeName="adoMain" SelectMethod="GetAll"
runat="server" DeleteMethod="delete" UpdateMethod="UpDate" >
<DeleteParameters>
<asp:Parameter Name="sid" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="sid" Type="String" />
<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>

To Add Data&nbsp;
<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="~/AddData.aspx">Add New Data</asp:HyperLink>
&nbsp;&nbsp;&nbsp;
<asp:HyperLink ID="HyperLink2" runat="server" NavigateUrl="~/Default.aspx">Home</asp:HyperLink>

</td>
</tr>
<tr>
<td colspan="3">
<asp:Label ID="L1" runat="server" /> <br /> <asp:Label ID="L2" Text="Label" runat="server" />

<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False" DataSourceID="adoObj"
Width="500px" BorderColor="#6699FF" BorderStyle="Groove" BorderWidth="4px" >
<Columns>
<asp:CommandField 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 colspan="3">

To View the changes please go to Home page or Click on Refresh</td>
</tr>
<tr>
<td >
Enter ID to Delete &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<asp:TextBox ID="TextBox6" runat="server"
CssClass="txttiny"></asp:TextBox>

</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr>
<td colspan="3" align="center">
<asp:Button ID="Button3" runat="server" onclick="Button3_Click" Text="Delete " Width="187px" />
<asp:Button ID="Refresh" runat="server" Text="ReFresh" Width="187px" onclick="Refresh_Click" PostBackUrl="~/Default.aspx" />
</td>
</tr>
<tr>
<td colspan="3" align="center">
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
</td></tr></table></div>
</form>
</body>
</html>

Code EditDelete.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;

public partial class _EditDelete : System.Web.UI.Page
{
// private static int n1;
protected void Page_Load(object sender, EventArgs e)
{
adoMain cObj = new adoMain();
L1.Text = cObj.strResult;
Label1.Text = cObj.strDelete;
}

protected void Button3_Click(object sender, EventArgs e)
{
//deleting Record
try
{
adoMain delrecord = new adoMain();
String str = TextBox6.Text;
int n2 = System.Convert.ToInt32(str);
delrecord.Delete(n2);
TextBox6.Text = "";
}
catch (Exception error)
{
Label1.Text = error.Message.ToString();
}
}

protected void Refresh_Click(object sender, EventArgs e)
{
adoMain refresh = new adoMain();
Label1.Text = refresh.strDelete;

}
}


 

Step 6: Coding AddData.aspx

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

<!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.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
<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="~/EditDelete.aspx">Viw Grid View</asp:HyperLink>
&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>

 

Code 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 = "";

}
}

Step 7: Runtime Analysis: We are now ready to test

Below is the Table we are going to access from web site.

This our Home or opening page, to look at the data

Step 8: Updating the data, you may click Edit/Delete from Home page to view this page. Let us Edit a field.

Change  Tech Level 34 to Level3

Now Click on Update to commit the changes.

The result is visible at once.

Are we thrilled? Not exactly, update is loose on checking on the data integrity, since it is a field? Further, sid is a Primary Key!

string strInput = "INSERT INTO Main([sid],[Job],[FirstName],[LastName],[Note])VALUES (" + ( sid + ",'"+ job + "','" + firstname + "','" + lastname + "','" + note + "')");
 

Step 9: Test : Adding  a row.

Results are confirmed, with a loop back string from adoMain.cs

Confirmation from the database

Step 10: Deleting A row

The box will clear up, as you submit the request

Delete is confirmed