This tutorial will show you how we can create our own class to retrieve data from a database, using the ObjectDataSource control, in C#.
Download the Full Working Version of this Project written with Visual Studio.NET 2008 Here!
Looking for the VB.NET Version? Click here!
Looking for more .NET Database Tutorials? Click Here!
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!
ASP.NET is full of controls and classes to make interacting with our data easier. However, ASP.NET also gives us the ability to create our own classes to interact and manipulate our data.
In this tutorial, you will learn how to create your own Data Access Component. We will simply be displaying data from our database, but to do this, we will write our own class that will retrieve the data from a SQL database.
We will start by creating our SQL database - we will have one table with three columns - id, name and city. Once we have our database structure, we can add some sample data, and then we can start work on our web application.
To display the data, we will be using a DataGrid and an ObjectDataSource to manage our data source. We can always display the data on a button click to see how it performs the request. The following is what the ASPX page will look like:
<form id="form1" runat="server">
<asp:Button ID="butGetData" runat="server" Text="Get Data"
onclick="butGetData_Click" /><br /><br /> <asp:DataGrid ID="DataGrid1" runat="server" /> <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" TypeName="People"
SelectMethod="GetAll" /> </form> |
Notice that we have TypeName and SelectMethod attributes on the ObjectDataSource. These refer to our Class, which we can now write. It will look something like this:
private static readonly string _connectionString;
private string _name;
private string _city;
public string Name
{
get {
return _name; } set {
_name = value; }
public string City
{
get {
return _city; } set {
_city = value; }
public List<People> GetAll()
{
List<People> results = new List<People>(); SqlConnection conn = new SqlConnection(_connectionString); SqlCommand cmd = new SqlCommand("SELECT name,city FROM tblPeople", conn); using (conn) {
conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) {
People newPeople = new People();
newPeople.Name = (string)reader["Name"];
newPeople.City = (string)reader["City"];
results.Add(newPeople); } } return results;
static People()
{
_connectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; } |
Need help with Windows Dedicated Hosting? Try Server Intellect. I'm a happy customer!
For each column we wish to interact with, we create a Get and a Set, which allows us to both retrieve data and also set data. Our GetAll method is simply retrieving all data that is in the database and storing in a List, which we then return to the ObjectDataSource.
The entire Class will look something like this:
using System;
using System.Data;
using System.Web.Configuration;
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.Collections.Generic;
using System.Data.SqlClient;
/// <summary>
/// Summary description for People
/// </summary>
public class People
{
private static readonly string _connectionString; private string _name; private string _city; public string Name {
get {
return _name; } set {
_name = value; } } public string City {
get {
return _city; } set {
_city = value; } } public List<People> GetAll() {
List<People> results = new List<People>(); SqlConnection conn = new SqlConnection(_connectionString); SqlCommand cmd = new SqlCommand("SELECT name,city FROM tblPeople", conn); using (conn) {
conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) {
People newPeople = new People();
newPeople.Name = (string)reader["Name"];
newPeople.City = (string)reader["City"];
results.Add(newPeople); } } return results; } static People() {
_connectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; } |
Finally, we can add logic to the button on the back-end of our ASPX page:
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e) { } protected void butGetData_Click(object sender, EventArgs e) {
DataGrid1.DataSourceID = "ObjectDataSource1";
DataGrid1.DataBind(); } |
If we now run this web application, we will be able to retrieve our data from the database with the click of a button.
Looking for the VB.NET Version? Click here!
Looking for more .NET Database Tutorials? Click Here!
We moved our web sites to Server Intellect and have found them to be incredibly professional. Their setup is very easy and we were up and running in no time.
|