CSharp Gridview Selection Style – ADO.Net Database

<%@ Page Language="C#" AutoEventWireup="true" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"><html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
        <asp:ObjectDataSource ID="sourceEmployees"
                              runat="server"
                              SelectMethod="GetEmployees"
                              TypeName="EmployeeDB"/>
        <asp:GridView ID="GridView1"
                      runat="server"
                      CellPadding="4"
                      DataSourceID="sourceEmployees"
                      Font-Names="Verdana"
                      Font-Size="Small"
                      ForeColor="Black"
                      GridLines="None"
                      AutoGenerateColumns="False"
                      DataKeyNames="EmployeeID">
            <footerStyle BackColor="White" Font-Bold="True" ForeColor="White" />
            <rowStyle BackColor="Pink" ForeColor="Black" />
            <pagerStyle BackColor="Red" ForeColor="Black" HorizontalAlign="Center" />
            <selectedRowStyle BackColor="Red" Font-Bold="True" ForeColor="Navy" />
            <headerStyle BackColor="White" Font-Bold="True" ForeColor="White" />
            <alternatingRowStyle BackColor="White" />
            <columns>
                <asp:CommandField ShowSelectButton="True"
                                  ButtonType="Image"/>
                <asp:CommandField ButtonType="Button"  ShowSelectButton="True"  />
                <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
                <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" SortExpression="EmployeeID" />
                <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
                <asp:BoundField DataField="TitleOfCourtesy"
                                HeaderText="TitleOfCourtesy"
                                SortExpression="TitleOfCourtesy" />
            </columns>
        </asp:GridView>
    </form>
</body>
</html>
File: EmployeeDB.csusing System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Configuration;
using System.Collections;public class EmployeeDetails
{
  private int employeeID;
  private string firstName;
  private string lastName;
  private string titleOfCourtesy;  public int EmployeeID
  {
    get {return employeeID;}
    set {employeeID = value;}
  }
  public string FirstName
  {
    get {return firstName;}
    set {firstName = value;}
  }
  public string LastName
  {
    get {return lastName;}
    set {lastName = value;}
  }
  public string TitleOfCourtesy
  {
    get {return titleOfCourtesy;}
    set {titleOfCourtesy = value;}
  }  public EmployeeDetails(int employeeID, string firstName, string lastName,
    string titleOfCourtesy)
  {
    this.employeeID = employeeID;
    this.firstName = firstName;
    this.lastName = lastName;
    this.titleOfCourtesy = titleOfCourtesy;
  }  public EmployeeDetails(){}
}
public class EmployeeDB
{
  private string connectionString;  public EmployeeDB()
  {
    connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
  }
  public EmployeeDB(string connectionString)
  {
    this.connectionString = connectionString;
  }  public int InsertEmployee(EmployeeDetails emp)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("InsertEmployee", con);
    cmd.CommandType = CommandType.StoredProcedure;    cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
    cmd.Parameters["@FirstName"].Value = emp.FirstName;
    cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
    cmd.Parameters["@LastName"].Value = emp.LastName;
    cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
    cmd.Parameters["@TitleOfCourtesy"].Value = emp.TitleOfCourtesy;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Direction = ParameterDirection.Output;
 
    con.Open();
    cmd.ExecuteNonQuery();
    return (int)cmd.Parameters["@EmployeeID"].Value;
  }  public void UpdateEmployee(EmployeeDetails emp)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("UpdateEmployee", con);
    cmd.CommandType = CommandType.StoredProcedure;    cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
    cmd.Parameters["@FirstName"].Value = emp.FirstName;
    cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
    cmd.Parameters["@LastName"].Value = emp.LastName;
    cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
    cmd.Parameters["@TitleOfCourtesy"].Value = emp.TitleOfCourtesy;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = emp.EmployeeID;    con.Open();
    cmd.ExecuteNonQuery();
  }  public void UpdateEmployee(int EmployeeID, string firstName, string lastName, string titleOfCourtesy)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("UpdateEmployee", con);
    cmd.CommandType = CommandType.StoredProcedure;    cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
    cmd.Parameters["@FirstName"].Value = firstName;
    cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
    cmd.Parameters["@LastName"].Value = lastName;
    cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
    cmd.Parameters["@TitleOfCourtesy"].Value = titleOfCourtesy;
    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = EmployeeID;    con.Open();
    cmd.ExecuteNonQuery();
  }  public void DeleteEmployee(int employeeID)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("DeleteEmployee", con);
    cmd.CommandType = CommandType.StoredProcedure;    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = employeeID;
 
    con.Open();
    cmd.ExecuteNonQuery();
  }  public EmployeeDetails GetEmployee(int employeeID)
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("GetEmployee", con);
    cmd.CommandType = CommandType.StoredProcedure;    cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
    cmd.Parameters["@EmployeeID"].Value = employeeID;
 
    con.Open();
    SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
 
    reader.Read();
    EmployeeDetails emp = new EmployeeDetails(
        (int)reader["EmployeeID"], (string)reader["FirstName"],
        (string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
    reader.Close();
    return emp;
  }  public EmployeeDetails[] GetEmployees()
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("GetAllEmployees", con);
    cmd.CommandType = CommandType.StoredProcedure;
 
    ArrayList employees = new ArrayList();    con.Open();
    SqlDataReader reader = cmd.ExecuteReader();    while (reader.Read())
    {
       EmployeeDetails emp = new EmployeeDetails(
          (int)reader["EmployeeID"], (string)reader["FirstName"],
          (string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
      employees.Add(emp);
    }
    reader.Close();
 
    return (EmployeeDetails[])employees.ToArray(typeof(EmployeeDetails));
  }
 
  public int CountEmployees()
  {
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("CountEmployees", con);
    cmd.CommandType = CommandType.StoredProcedure;
 
        con.Open();
    return (int)cmd.ExecuteScalar();
  }
}File: Web.config<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
  <appSettings/>
  <connectionStrings>
    <add name="Northwind" connectionString="Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"/>
  </connectionStrings>
 
</configuration>

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.