This tutorial will show you how to use C# and AJAX to create a Data Access Component that will display data from a SQL database and also allow you to delete records from the database.
In addition to creating our own Data Access Component to read data from our SQL database, we can also add a method to delete records from the database. In this tutorial, you will learn how to create a Data Access Component that will both read and display data from a SQL database and that also has the ability to delete records as well.
In this example, we will be using a SQL database with one table that has three columns - id, name and age. We will write a Data Access Component to access this data and use a GridView to display it. We will also add the functionality to delete records from the database. Finally, we will be adding AJAX to increase the usability of the system.
Once we have our database set up, we will begin to write the class. To read data from the database, we will need something like this:
private static readonly string _connectionString;
private int _id;
private string _name;
private string _age;
public int Id
{
get {
return _id; } set {
_id = value; }
public string Name
{
get {
return _name; } set {
_name = value; }
public string Age
{
get {
return _age; } set {
_age = value; }
public List<People> GetAll()
{
List<People> results = new List<People>(); SqlConnection con = new SqlConnection(_connectionString); SqlCommand cmd = new SqlCommand("SELECT id,name,age FROM tblPeople", con); using (con) {
con.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) {
People newPerson = new People();
newPerson.Id = System.Convert.ToInt32((int)(dr["Id"]));
newPerson.Name = System.Convert.ToString(dr["Name"]);
newPerson.Age = System.Convert.ToString(dr["Age"]);
results.Add(newPerson); } } return results;
static People()
{
_connectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; } |
Try Server Intellect for Windows Server Hosting. Quality and Quantity!
At present, the above code is just for retrieving the data from the database. We use a List collection to gather all records from the database, and then return to the object that calls the method. In this case, we will use the ObjectDataSource to call the method, but that's a little later in the ASPX code. Because we want to add the functionality of deleting data from the database, we will need to add another method, which will look something like this:
public void Delete(int id)
{
SqlConnection con = new SqlConnection(_connectionString); SqlCommand cmd = new SqlCommand("DELETE tblPeople WHERE Id=@Id", con); cmd.Parameters.AddWithValue("@Id", id); using (con) {
con.Open();
cmd.ExecuteNonQuery(); } |
This method uses SQL statements to delete database records with the id variable passed to it from whatever calls it. In this example, the ObjectDataSource will be calling it, which we will get to a little later.
So the entire code-behind for the class looks something like this:
using System;
using System.Data;
using System.Configuration;
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.Collections.Generic;
using System.Data.SqlClient;
using System.Web.Configuration;
/// <summary>
/// Summary description for People
/// </summary>
public class People
{
private static readonly string _connectionString; private int _id; private string _name; private string _age; public int Id {
get {
return _id; } set {
_id = value; } } public string Name {
get {
return _name; } set {
_name = value; } } public string Age {
get {
return _age; } set {
_age = value; } } public void Delete(int id) {
SqlConnection con = new SqlConnection(_connectionString); SqlCommand cmd = new SqlCommand("DELETE tblPeople WHERE Id=@Id", con); cmd.Parameters.AddWithValue("@Id", id); using (con) {
con.Open();
cmd.ExecuteNonQuery(); } } public List<People> GetAll() {
List<People> results = new List<People>(); SqlConnection con = new SqlConnection(_connectionString); SqlCommand cmd = new SqlCommand("SELECT id,name,age FROM tblPeople", con); using (con) {
con.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) {
People newPerson = new People();
newPerson.Id = System.Convert.ToInt32((int)(dr["Id"]));
newPerson.Name = System.Convert.ToString(dr["Name"]);
newPerson.Age = System.Convert.ToString(dr["Age"]);
results.Add(newPerson); } } return results; } static People() {
_connectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; } |
Server Intellect offers Windows Hosting Dedicated Servers at affordable prices. I'm very pleased!
This concludes our writing of the class. Now we just need to implement it into the ASPX page.
To do this, all we will do is include two controls: GridView and ObjectDataSource. By specifying a few attributes of these controls, we will unlock the functionality of the class we just created. The ASPX page will look something like this:
<form id="form1" runat="server">
<asp:GridView ID="GridView1" runat="server" DataSourceID="ObjectDataSource1"
DataKeyNames="id" AutoGenerateDeleteButton="true" Width="370px" /> <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" TypeName="People"
SelectMethod="GetAll" DeleteMethod="Delete" /> |
We migrated our web sites to Server Intellect over one weekend and the setup was so smooth that we were up and running right away. They assisted us with everything we needed to do for all of our applications. With Server Intellect's help, we were able to avoid any headaches!
As a final touch, we will implement AJAX to create a desktop feel to the application. By adding a ScriptManager and an UpdatePanel control, we will hijack the postback and reload just the GridView and DataSource. The ASPX page will now look something like this:
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server" />
<asp:UpdatePanel ID="UpdateManager1" runat="server">
<ContentTemplate>
<asp:GridView ID="GridView1" runat="server" DataSourceID="ObjectDataSource1"
DataKeyNames="id" AutoGenerateDeleteButton="true" Width="370px" /> <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" TypeName="People"
SelectMethod="GetAll" DeleteMethod="Delete" />
</ContentTemplate>
</asp:UpdatePanel>
</form> |
Looking for the Visual Studio.NET 2008 VB.NET version? Click here!
Looking for more .NET Tutorials? Click Here!