Simple data access component with Connection, Command and DataReader Classes in VB.NET
Connection , command and DataReader classes provide you with the fundamental methods of working with database data.
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
