ARTICLE
Simple data access component with Connection, Command and DataReader Classes
Connection , command and DataReader classes provide you with the fundamental methods of working with database data.
Download
Files:
You can build a simple data access
component with Connection, command and DataReader classes.
SqlConnection object is used to represent connection to data source.
SqlCommand object is used to represent a SELECT command of SQL Server.
DataReader is used to represent execution of result command.
In this article , EmployeeClass component ,include ALL() method that returns
every records from Employee table.
App_code/Employee.cs
using
System;
using
System.Collections.Generic;
using
System.Linq;
using
System.Web;
using
System.Web.Configuration;
using
System.Data.SqlClient;
using
System.Data;
public class EmployeeClass
{
private static readonly string
Connection_String;
private int
Emp_id;
private string
Emp_Fname;
private string
Emp_lname;
private string
Emp_country;
public int id
{
get { return
Emp_id; }
set { Emp_id = value;
}
}
public string
FirstName
{
set { Emp_Fname = value;
}
get { return
Emp_Fname; }
}
public string
LastName
{
set { Emp_lname = value;
}
get { return
Emp_lname; }
}
public string
Country
{
set { Emp_country =
value; }
get { return
Emp_country; }
}
public List<EmployeeClass>
All()
{
List<EmployeeClass>
result = new List<EmployeeClass>();
SqlConnection con =
new SqlConnection(Connection_String);
SqlCommand cmd = new SqlCommand("SELECT
id,Emp_fname,Emp_lname,Emp_country,Emp_salary FROM Employee",con);
using(con)
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
EmployeeClass emp =
new EmployeeClass();
emp.id = (int)reader["id"];
emp.FirstName = (string)reader["Emp_fname"].ToString();
emp.LastName = (string)reader["Emp_lname"].ToString();
emp.Country = (string)reader["Emp_country"].ToString();
result.Add(emp);
}
}
return result;
}
static EmployeeClass()
{
Connection_String = WebConfigurationManager.ConnectionStrings["MasterDatabase"].ConnectionString;
}
}
Main.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Main.aspx.cs" Inherits="Main"
<!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></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="ObjectDataSource1">
<Columns>
<asp:BoundField DataField="id" HeaderText="id" SortExpression="id" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
<asp:BoundField DataField="Country" HeaderText="Country" SortExpression="Country" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$
ConnectionStrings:MasterDatabase %>" SelectCommand="SELECT *
FROM [Employee]"></asp:SqlDataSource>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="All"
TypeName="EmployeeClass"></asp:ObjectDataSource>
</div>
</form>
</body>
</html>
Output
