This tutorial will show you how to create a Data Access Component that will both read and delete data from a SQL database. C# Version.
If you're ever in the market for some great Windows web hosting, try Server Intellect. We have been very pleased with their services and most importantly, technical support.
Click Here for this project written in Visual Studio VB.NET 2008!
Looking for more .NET Database Tutorials? Click Here!
In addition to the built-in controls ASP.NET provides us in managing data sources and interacting with data, we are able to also create our own Data Access Components, for further customization for manipulation of data. In this tutorial, we will be looking at how we go about building a Data Access Component (DAC) with the ability to retrieve and delete database data.
Before anything else, we need to create a database. In this example, we will be working with a SQL database with one table, which has three columns - id, name and age. Once we have set up our database, we will add some sample records to work with. If you already have a database you wish to work with, add it to your project in Solution Explorer.
The first thing we want to do is to create our class. This will handle all the interaction with our database - reading and deleting. We will need to write a method for reading the data and a method for deleting data. We will start off with the reading method:
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; } |
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(); } |
Yes, it is possible to find a good web host. Sometimes it takes a while. After trying several, we went with Server Intellect and have been very happy. They are the most professional, customer service friendly and technically knowledgeable host we've found so far.
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; } |
The class is now fully-functional. Now we just need to construct our ASPX page to use the class.
To do this, we are going to 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 are using Server Intellect and have found that by far, they are the most friendly, responsive, and knowledgeable support team we've ever dealt with!
Click Here for this project written in Visual Studio VB.NET 2008!
Looking for more .NET Database Tutorials? Click Here!
|