This tutorial will show you how to create your own Data Access Component that will both read and edit data from a SQL database. C# version.
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!
Download the Full Working Version of this Project written with Visual Studio 2008 and C#.NET Here!
Click Here for this project written in Visual Studio VB.NET 2008!
Looking for more .NET Database Tutorials? Click Here!
There are plenty of built-in tools to work with data in ASP.NET, but we also get the ability to create our own Data Access Components for further customization of data handling and manipulation. In this tutorial, you will learn how to build a Data Access Component (DAC) with the ability to retrieve and update database data.
Before we do anything else, we need 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 writing. We will need to write a method for reading the data, and then a method for updating records. We will start off with the reading:
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 = (int)dr["Id"];
newPerson.Name = (string)dr["Name"];
newPerson.Age = (string)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 adding data to the database, we will need to add another method, which will look something like this:
public void Update(int id, string name, string age)
{
SqlConnection con = new SqlConnection(_connectionString); SqlCommand cmd = new SqlCommand("UPDATE tblPeople SET name=@name,age=@age WHERE id=@id", con); cmd.Parameters.AddWithValue("@id", id); cmd.Parameters.AddWithValue("@name", name); cmd.Parameters.AddWithValue("@age", age); using (con) {
con.Open();
cmd.ExecuteNonQuery(); } |
This method uses SQL statements to update the database record with variables 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.Web.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
/// <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 Update(int id, string name, string age) {
SqlConnection con = new SqlConnection(_connectionString); SqlCommand cmd = new SqlCommand("UPDATE tblPeople SET name=@name,age=@age WHERE id=@id", con); cmd.Parameters.AddWithValue("@id", id); cmd.Parameters.AddWithValue("@name", name); cmd.Parameters.AddWithValue("@age", age); 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 = (int)dr["Id"];
newPerson.Name = (string)dr["Name"];
newPerson.Age = (string)dr["Age"];
results.Add(newPerson); } } return results; } static People() {
_connectionString = WebConfigurationManager.ConnectionStrings["connectionString"].ConnectionString; } |
We are now done with the class; we just need to incorporate 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" AutoGenerateEditButton="true" Width="370px" /> <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" TypeName="People"
SelectMethod="GetAll" UpdateMethod="Update" /> |
We chose Server Intellect for its dedicated servers, for our web hosting. They have managed to handle virtually everything for us, from start to finish. And their customer service is stellar.
Click Here for this project written in Visual Studio VB.NET 2008!
Looking for more .NET Database Tutorials? Click Here!
|