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.
  • 4690
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

  display-output-in-vb.net.gif

Categories

More Articles

© 2019 DotNetHeaven. All rights reserved.