701 
Problem
Creating a web application that interacts with a SQL Server database is something that can be super useful for many purposes. It may be for simple tracking of tasks or maybe a full-blown web-based application. In this article, we will work through the steps of creating a basic web application that uses SQL Server to store the data.
Solution
In this tip, we will work through a simple ASP.Net solution to create web pages to work with a SQL Server database.
Setting Up Database Environment
If you have worked through the previous tutorials in this series, your database and SQL login are ready to use. However, if you need to restore your environment, you can use the SQL script in the download for this article.
The script will:
- Create the database if it does not exist.
- Set up the Login and User for the web application.
- Create two tables: Companies and Employees and their relationship.
- Insert data into the companies to be used in the web application.
- Create the CRUD (insert, select, update, delete) stored procedures for both tables.
Building Web Application
I created a Visual Studio Solution with an ASP.Net website where you can work with the HR database that has been created up to this point in this series. You can download the solution in the Next Steps section of this article.
For this tip, I assume that you are proficient with ASP.Net development and have a version of Visual Studio installed on your environment. The solution in this tutorial was created in Microsoft Visual Studio Community 2019.
I am not going into details on the website development but will describe the high-level details. I have a solution developed in .zip format available for download to work through. You can get the link at the end of this tutorial.
Next, let me explain how to set up the Visual Studio solution to get the website developed.
Step 1: Open up Visual Studio and Create an ASP.Net website
Open Visual Studio, select Create a new project, choose ASP.NET Web Application (.Net Framework) template, and click Next.
In the next window, give your Project a name, and a Location where to store it, change the Solution name if you do not want it to be the same as the Project name, and select the Framework. Click Create.
On the next window, select Empty (template), un-tick Configure for HTTPS, and click Create.
Now a project is open in Visual Studio with the basic files in the Solution Explorer, as seen in the image below.
Step 2: Connection String in web.config File
Double-click on the web.config file in the Solution Explorer.
Add the Connection string section. This is the connection information the website will use to connect to the database that is hosted on the SQL Server where you created your database and the Login and User. Make sure to add it between the <configuration> … </configuration> tag in the web.config file.
Change the following in code below to what you have created:
- Datasource
- HRDatabase
- HRDBLogin
- MyPassword – make sure the password is like the one created in the SQL script
Note the connection’s name: HRDBConnection (to be used in the web application).
<connectionStrings>
<add name=”HRDBConnection”
connectionString=”Data Source=Datasource;Initial Catalog=HRDatabase;User ID=HRDBLogin;Password=MyPassword”
providerName=”System.Data.SqlClient” />
</connectionStrings>Be aware of the following common (easy) mistakes:
- Make sure your connection string is between the <connectionStrings> … </connectionStrings>
- Change your Datasource above to the SQL Server instance that you are using for the database
- When you copy the above code to the web.config, check the quotes and make sure they are correct (sometimes it is the MS Word-type quotes)
Step 3: Add Three Web Pages
Create three webforms by right-clicking on the HRProject, Add, Webform
- Default
- Companies
- Employees
Default.aspx
The default page (commonly known as the Home page) will only contain the links to the other two pages, and no changes need to be made to the code-behind page.
Double-click on the Default.aspx page in the Solution Explorer to open the HTML code of the Default.aspx page.
Replace the HTML code with the code below.
Double check that value for “Inherits=” is the same name as your Project.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="HRWebsite.Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>HR Database Home Page</title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script type="text/javascript" src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/js/bootstrap.min.js"></script>
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css" />
</head>
<body>
<form id="form1" runat="server">
<div class="container">
<%-- Webpage Heading --%>
<div class="row">
<div class="col-xs-12">
<h1>HR Database Homepage</h1>
</div>
</div>
<%-- Menu / Message --%>
<div class="navbar-collapse collapse">
<div class="col-sm-4">
<ul class="nav navbar-nav" style="font-weight: bold;">
<li>
<asp:HyperLink ID="hlHome" NavigateUrl="~/Default.aspx" runat="server">Home</asp:HyperLink><br />
</li>
<li>
<asp:HyperLink ID="hlCompanies" NavigateUrl="~/Companies.aspx" runat="server">Companies</asp:HyperLink><br />
</li>
<li>
<asp:HyperLink ID="hlEmployees" NavigateUrl="~/Employees.aspx" runat="server">Employees</asp:HyperLink><br />
</li>
</ul>
</div>
<div class="col-sm-4">
<asp:Label ID="lblMessage" runat="server" Text="" />
</div>
<div class="col-sm-4" style="text-align: right;"></div>
</div>
</div>
</form>
</body>
</html>Companies.aspx
The companies page will have the following features:
- It will display a grid view of the companies
- It will have a link to add a new company
- For each company, you will be able to Update and Delete a company
Double-click on the Companies.aspx page in the Solution Explorer to open the HTML code of the Company’s page.
Replace the HTML code with the code below.
Double check that value for “Inherits=” is the same name as your Project.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Companies.aspx.cs" Inherits="HRWebsite.Companies" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Companies Homepage</title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script type="text/javascript" src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/js/bootstrap.min.js"></script>
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css" />
<script type="text/javascript">
function openCoDetail() {
//alert("Opening modal!");
$('#modCoDetail').modal('show');
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div class="container">
<%-- Webpage Heading --%>
<div class="row">
<div class="col-xs-12">
<h1>Companies Homepage</h1>
</div>
</div>
<%-- Menu / Message / New link --%>
<div class="navbar-collapse collapse">
<div class="col-sm-4">
<ul class="nav navbar-nav" style="font-weight: bold;">
<li>
<asp:HyperLink ID="hlHome" NavigateUrl="~/Default.aspx" runat="server">Home</asp:HyperLink><br />
</li>
<li>
<asp:HyperLink ID="hlCompanies" NavigateUrl="~/Companies.aspx" runat="server">Companies</asp:HyperLink><br />
</li>
<li>
<asp:HyperLink ID="hlEmployees" NavigateUrl="~/Employees.aspx" runat="server">Employees</asp:HyperLink><br />
</li>
</ul>
</div>
<div class="col-sm-4">
<asp:Label ID="lblMessage" runat="server" Text="" />
</div>
<div class="col-sm-4" style="text-align: right;">
<asp:Label ID="Label5" runat="server" Text="[" Font-Size="12px" Visible="true"></asp:Label>
<asp:LinkButton ID="lbNewComp" runat="server" Font-Size="12px" OnClick="lbNewComp_Click">New</asp:LinkButton>
<asp:Label ID="Label6" runat="server" Text="]" Font-Size="12px" Visible="true"></asp:Label>
</div>
</div>
<%-- Gridview --%>
<div class="row" style="margin-top: 20px;">
<div class="col-sm-12">
<asp:GridView ID="gvCompanies" runat="server" AutoGenerateColumns="False" AllowSorting="True"
DataKeyNames="ID"
CssClass="table table-striped table-bordered table-condensed" BorderColor="Silver"
OnRowDeleting="gvCompanies_RowDeleting"
OnRowCommand="gvCompanies_RowCommand"
EmptyDataText="No data for this request!">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<%# Container.DataItemIndex + 1 %>
</ItemTemplate>
<HeaderStyle HorizontalAlign="Left" Width="25px" />
<ItemStyle HorizontalAlign="Left" Font-Bold="true" />
</asp:TemplateField>
<asp:BoundField DataField="CompanyName" HeaderText="Company Name">
<HeaderStyle HorizontalAlign="Left" />
<ItemStyle HorizontalAlign="Left" />
</asp:BoundField>
<asp:BoundField DataField="CompAddress" HeaderText="Company Address">
<HeaderStyle HorizontalAlign="Left" />
<ItemStyle HorizontalAlign="Left" />
</asp:BoundField>
<asp:BoundField DataField="CompContactNo" HeaderText="Contact Number">
<HeaderStyle HorizontalAlign="Left" />
<ItemStyle HorizontalAlign="Left" />
</asp:BoundField>
<%-- Delete Company --%>
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="lbDelCompany" Text="Del" runat="server"
OnClientClick="return confirm('Are you sure you want to delete this company?');" CommandName="Delete" />
</ItemTemplate>
<HeaderStyle HorizontalAlign="Left" />
<ItemStyle HorizontalAlign="Center" Width="50px" />
</asp:TemplateField>
<%-- Update Company --%>
<asp:TemplateField HeaderText="">
<ItemTemplate>
<asp:LinkButton ID="lbUpdCompany" runat="server" CommandArgument='<%# Eval("ID") %>'
CommandName="UpdCompany" Text="Upd" CausesValidation="false"></asp:LinkButton>
</ItemTemplate>
<ItemStyle HorizontalAlign="Center" Width="80px" />
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</div>
</div>
<!-- Modal to Add New or View / Update a Company Details-->
<div class="modal fade" id="modCoDetail" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
<div class="modal-dialog modal-lg" style="width: 600px;">
<div class="modal-content" style="font-size: 11px;">
<div class="modal-header" style="text-align: center;">
<asp:Label ID="lblCompanyNew" runat="server" Text="Add New Company" Font-Size="24px" Font-Bold="true" />
<asp:Label ID="lblCompanyUpd" runat="server" Text="View / Update a Company" Font-Size="24px" Font-Bold="true" />
</div>
<div class="modal-body">
<div class="row">
<div class="col-sm-12">
<%-- Company Details Textboxes --%>
<div class="col-sm-12">
<div class="row" style="margin-top: 20px;">
<div class="col-sm-1"></div>
<div class="col-sm-10">
<asp:TextBox ID="txtCompanyName" runat="server" MaxLength="255" CssClass="form-control input-xs"
ToolTip="Company Name"
AutoCompleteType="Disabled" placeholder="Company Name" />
<asp:Label runat="server" ID="lblCompID" Visible="false" Font-Size="12px" />
</div>
<div class="col-sm-1">
</div>
</div>
<div class="row" style="margin-top: 20px;">
<div class="col-sm-1"></div>
<div class="col-sm-10">
<asp:TextBox ID="txtCompAddress" runat="server" MaxLength="255" CssClass="form-control input-xs"
ToolTip="Company Address"
AutoCompleteType="Disabled" placeholder="Company Address" />
</div>
<div class="col-sm-1">
</div>
</div>
<div class="row" style="margin-top: 20px;">
<div class="col-sm-1"></div>
<div class="col-sm-10">
<asp:TextBox ID="txtCompContactNo" runat="server" MaxLength="255" CssClass="form-control input-xs"
ToolTip="Company Contact Number"
AutoCompleteType="Disabled" placeholder="Company Contact Number" />
</div>
<div class="col-sm-1">
</div>
</div>
</div>
</div>
</div>
<%-- Message label on modal page --%>
<div class="row" style="margin-top: 20px; margin-bottom: 10px;">
<div class="col-sm-1"></div>
<div class="col-sm-10">
<asp:Label ID="lblModalMessage" runat="server" ForeColor="Red" Font-Size="12px" Text="" />
</div>
<div class="col-sm-1"></div>
</div>
</div>
<%-- Add, Update and Cancel Buttons --%>
<div class="modal-footer">
<asp:Button ID="btnAddCompany" runat="server" class="btn btn-danger button-xs" data-dismiss="modal"
Text="Add Company"
Visible="true" CausesValidation="false"
OnClick="btnAddCompany_Click"
UseSubmitBehavior="false" />
<asp:Button ID="btnUpdCompany" runat="server" class="btn btn-danger button-xs" data-dismiss="modal"
Text="Update Company"
Visible="false" CausesValidation="false"
OnClick="btnUpdCompany_Click"
UseSubmitBehavior="false" />
<asp:Button ID="btnClose" runat="server" class="btn btn-info button-xs" data-dismiss="modal"
Text="Close" CausesValidation="false"
UseSubmitBehavior="false" />
</div>
</div>
</div>
</div>
</form>
</body>Right-click on the Companies.aspx file in the Solution Explorer and click on View Code.
Copy the code below and paste it into the Companies.aspx.cs file. Also, check that the namespace matches your project name.
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace HRWebsite
{
public partial class Companies : System.Web.UI.Page
{
int Comp_ID;
SqlConnection myCon = new SqlConnection(ConfigurationManager.ConnectionStrings["HRDBConnection"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DoGridView();
}
}
private void DoGridView()
{
try
{
myCon.Open();
using (SqlCommand myCom = new SqlCommand("dbo.usp_GetCompanies", myCon))
{
myCom.Connection = myCon;
myCom.CommandType = CommandType.StoredProcedure;
SqlDataReader myDr = myCom.ExecuteReader();
gvCompanies.DataSource = myDr;
gvCompanies.DataBind();
myDr.Close();
}
}
catch (Exception ex) { lblMessage.Text = "Error in Companies doGridView: " + ex.Message; }
finally { myCon.Close(); }
}
protected void lbNewComp_Click(object sender, EventArgs e)
{
try
{
txtCompanyName.Text = "";
txtCompAddress.Text = "";
txtCompContactNo.Text = "";
lblCompanyNew.Visible = true;
lblCompanyUpd.Visible = false;
btnAddCompany.Visible = true;
btnUpdCompany.Visible = false;
ScriptManager.RegisterStartupScript(this, this.GetType(), "Pop", "openCoDetail();", true);
}
catch (Exception) { throw; }
}
protected void btnAddCompany_Click(object sender, EventArgs e)
{
try
{
myCon.Open();
using (SqlCommand myCom = new SqlCommand("dbo.usp_InsCompany", myCon))
{
myCom.CommandType = CommandType.StoredProcedure;
myCom.Parameters.Add("@CompanyName", SqlDbType.VarChar).Value = txtCompanyName.Text;
myCom.Parameters.Add("@CompAddress", SqlDbType.VarChar).Value = txtCompAddress.Text;
myCom.Parameters.Add("@CompContactNo", SqlDbType.VarChar).Value = txtCompContactNo.Text;
myCom.ExecuteNonQuery();
}
}
catch (Exception ex) { lblMessage.Text = "Error in btnAddCompany_Click: " + ex.Message; }
finally { myCon.Close(); }
DoGridView();
}
protected void btnUpdCompany_Click(object sender, EventArgs e)
{
UpdCompany();
DoGridView();
}
protected void gvCompanies_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "UpdCompany")
{
Comp_ID = Convert.ToInt32(e.CommandArgument);
txtCompanyName.Text = "";
txtCompAddress.Text = "";
txtCompContactNo.Text = "";
lblCompanyNew.Visible = false;
lblCompanyUpd.Visible = true;
btnAddCompany.Visible = false;
btnUpdCompany.Visible = true;
GetCompany(Comp_ID);
ScriptManager.RegisterStartupScript(this, this.GetType(), "Pop", "openCoDetail();", true);
}
}
protected void gvCompanies_RowDeleting(Object sender, GridViewDeleteEventArgs e)
{
Comp_ID = Convert.ToInt32(gvCompanies.DataKeys[e.RowIndex].Value.ToString());
try
{
myCon.Open();
using (SqlCommand cmd = new SqlCommand("dbo.usp_DelCompany", myCon))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = Comp_ID;
cmd.ExecuteScalar();
}
}
catch (Exception ex) { lblMessage.Text = "Error in gvCompanies_RowDeleting: " + ex.Message; }
finally { myCon.Close(); }
DoGridView();
}
private void GetCompany(int Comp_ID)
{
try
{
myCon.Open();
using (SqlCommand myCmd = new SqlCommand("dbo.usp_GetCompany", myCon))
{
myCmd.Connection = myCon;
myCmd.CommandType = CommandType.StoredProcedure;
myCmd.Parameters.Add("@ID", SqlDbType.Int).Value = Comp_ID;
SqlDataReader myDr = myCmd.ExecuteReader();
if (myDr.HasRows)
{
while (myDr.Read())
{
txtCompanyName.Text = myDr.GetValue(1).ToString();
txtCompAddress.Text = myDr.GetValue(2).ToString();
txtCompContactNo.Text = myDr.GetValue(3).ToString();
lblCompID.Text = Comp_ID.ToString();
}
}
}
}
catch (Exception ex) { lblMessage.Text = "Error in Companies GetCompany: " + ex.Message; }
finally { myCon.Close(); }
}
private void UpdCompany()
{
try
{
myCon.Open();
using (SqlCommand cmd = new SqlCommand("dbo.usp_UpdCompany", myCon))
{
cmd.Connection = myCon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = int.Parse(lblCompID.Text);
cmd.Parameters.Add("@CompanyName", SqlDbType.VarChar).Value = txtCompanyName.Text;
cmd.Parameters.Add("@CompAddress", SqlDbType.VarChar).Value = txtCompAddress.Text;
cmd.Parameters.Add("@CompContactNo", SqlDbType.VarChar).Value = txtCompContactNo.Text;
int rows = cmd.ExecuteNonQuery();
}
}
catch (Exception ex) { lblMessage.Text = "Error in Companies UpdCompany: " + ex.Message; }
finally { myCon.Close(); }
}
}
}Employees.aspx
The employees page will have the following features:
- It will display a grid view of the employees
- It will have a link to add a new employee
- For each employee, you will be able to Update and Delete an employee
Double-click on the Employees.aspx page in the Solution Explorer to open the HTML code of the Employee’s page.
Replace the HTML code with the code below.
Double check that value for “Inherits=” is the same name as your Project.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Employees.aspx.cs" Inherits=" HRWebsite.Employees" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Employees Homepage</title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script type="text/javascript" src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/js/bootstrap.min.js"></script>
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css" />
<script type="text/javascript">
function openEmpDetail() {
//alert("Opening modal!");
$('#modEmpDetail').modal('show');
}
</script>
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
<div class="container">
<%-- Webpage Heading --%>
<div class="row">
<div class="col-xs-12">
<h1>Employees Homepage</h1>
</div>
</div>
<%-- Menu / Message / New link --%>
<div class="navbar-collapse collapse">
<div class="col-sm-4">
<ul class="nav navbar-nav" style="font-weight: bold;">
<li>
<asp:HyperLink ID="hlHome" NavigateUrl="~/Default.aspx" runat="server">Home</asp:HyperLink><br />
</li>
<li>
<asp:HyperLink ID="hlCompanies" NavigateUrl="~/Companies.aspx" runat="server">Companies</asp:HyperLink><br />
</li>
<li>
<asp:HyperLink ID="hlEmployees" NavigateUrl="~/Employees.aspx" runat="server">Employees</asp:HyperLink><br />
</li>
</ul>
</div>
<div class="col-sm-4">
<asp:Label ID="lblMessage" runat="server" Text="" />
</div>
<div class="col-sm-4" style="text-align: right;">
<asp:Label ID="Label5" runat="server" Text="[" Font-Size="12px" Visible="true"></asp:Label>
<asp:LinkButton ID="lbNewEmp" runat="server" Font-Size="12px" OnClick="lbNewEmp_Click">New</asp:LinkButton>
<asp:Label ID="Label6" runat="server" Text="]" Font-Size="12px" Visible="true"></asp:Label>
</div>
</div>
<%-- Gridview --%>
<div class="row" style="margin-top: 20px;">
<div class="col-sm-12">
<asp:GridView ID="gvEmployees" runat="server" AutoGenerateColumns="False" AllowSorting="True"
DataKeyNames="ID"
CssClass="table table-striped table-bordered table-condensed" BorderColor="Silver"
OnRowDeleting="gvEmployees_RowDeleting"
OnRowCommand="gvEmployees_RowCommand"
EmptyDataText="No data for this request!">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<%# Container.DataItemIndex + 1 %>
</ItemTemplate>
<HeaderStyle HorizontalAlign="Left" Width="25px" />
<ItemStyle HorizontalAlign="Left" Font-Bold="true" />
</asp:TemplateField>
<asp:BoundField DataField="EmployeeName" HeaderText="Employee Name">
<HeaderStyle HorizontalAlign="Left" />
<ItemStyle HorizontalAlign="Left" />
</asp:BoundField>
<asp:BoundField DataField="ContactNo" HeaderText="Contact Number">
<HeaderStyle HorizontalAlign="Left" />
<ItemStyle HorizontalAlign="Left" />
</asp:BoundField>
<asp:BoundField DataField="Email" HeaderText="Email">
<HeaderStyle HorizontalAlign="Left" />
<ItemStyle HorizontalAlign="Left" />
</asp:BoundField>
<asp:BoundField DataField="CompanyName" HeaderText="Company">
<HeaderStyle HorizontalAlign="Left" />
<ItemStyle HorizontalAlign="Left" />
</asp:BoundField>
<%-- Delete Employee --%>
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="lbDelEmployee" Text="Del" runat="server"
OnClientClick="return confirm('Are you sure you want to delete this employee?');" CommandName="Delete" />
</ItemTemplate>
<HeaderStyle HorizontalAlign="Left" />
<ItemStyle HorizontalAlign="Center" Width="50px" />
</asp:TemplateField>
<%-- Update Employee --%>
<asp:TemplateField HeaderText="">
<ItemTemplate>
<asp:LinkButton ID="lbUpdEmployee" runat="server" CommandArgument='<%# Eval("ID") %>'
CommandName="UpdEmployee" Text="Upd" CausesValidation="false"></asp:LinkButton>
</ItemTemplate>
<ItemStyle HorizontalAlign="Center" Width="80px" />
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</div>
</div>
<!-- Modal to Add New or View / Update a Company Details-->
<div class="modal fade" id="modEmpDetail" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
<div class="modal-dialog modal-lg" style="width: 600px;">
<div class="modal-content" style="font-size: 11px;">
<div class="modal-header" style="text-align: center;">
<asp:Label ID="lblEmployeeNew" runat="server" Text="Add New Employee" Font-Size="24px" Font-Bold="true" />
<asp:Label ID="lblEmployeeUpd" runat="server" Text="View / Update an Employee" Font-Size="24px" Font-Bold="true" />
</div>
<div class="modal-body">
<div class="row">
<div class="col-sm-12">
<%-- Employee Details Textboxes --%>
<div class="col-sm-12">
<div class="row" style="margin-top: 20px;">
<div class="col-sm-1"></div>
<div class="col-sm-10">
<asp:TextBox ID="txtEmployeeName" runat="server" MaxLength="255" CssClass="form-control input-xs"
ToolTip="Employee Name"
AutoCompleteType="Disabled" placeholder="Employee Name" />
<asp:Label runat="server" ID="lblEmpID" Visible="false" Font-Size="12px" />
</div>
<div class="col-sm-1">
</div>
</div>
<div class="row" style="margin-top: 20px;">
<div class="col-sm-1"></div>
<div class="col-sm-10">
<asp:TextBox ID="txtContactNo" runat="server" MaxLength="255" CssClass="form-control input-xs"
ToolTip="Contact Number"
AutoCompleteType="Disabled" placeholder="Contact Number" />
</div>
<div class="col-sm-1">
</div>
</div>
<div class="row" style="margin-top: 20px;">
<div class="col-sm-1"></div>
<div class="col-sm-10">
<asp:TextBox ID="txtEmail" runat="server" MaxLength="255" CssClass="form-control input-xs"
ToolTip="Email"
AutoCompleteType="Disabled" placeholder="Email" />
</div>
<div class="col-sm-1">
</div>
</div>
<div class="row" style="margin-top: 20px;">
<div class="col-sm-1"></div>
<div class="col-sm-10">
<asp:DropDownList ID="ddlCompany" runat="server" CssClass="form-control input-xs" />
</div>
<div class="col-sm-1">
</div>
</div>
</div>
</div>
</div>
<%-- Message label on modal page --%>
<div class="row" style="margin-top: 20px; margin-bottom: 10px;">
<div class="col-sm-1"></div>
<div class="col-sm-10">
<asp:Label ID="lblModalMessage" runat="server" ForeColor="Red" Font-Size="12px" Text="" />
</div>
<div class="col-sm-1"></div>
</div>
</div>
<%-- Add, Update and Cancel Buttons --%>
<div class="modal-footer">
<asp:Button ID="btnAddEmployee" runat="server" class="btn btn-danger button-xs" data-dismiss="modal"
Text="Add Employee"
Visible="true" CausesValidation="false"
OnClick="btnAddEmployee_Click"
UseSubmitBehavior="false" />
<asp:Button ID="btnUpdEmployee" runat="server" class="btn btn-danger button-xs" data-dismiss="modal"
Text="Update Employee"
Visible="false" CausesValidation="false"
OnClick="btnUpdEmployee_Click"
UseSubmitBehavior="false" />
<asp:Button ID="btnClose" runat="server" class="btn btn-info button-xs" data-dismiss="modal" Text="Close"
CausesValidation="false"
UseSubmitBehavior="false" />
</div>
</div>
</div>
</div>
</form>
</body>
</html>Right-click on the Employees.aspx file in the Solution Explorer and click on View Code.
Copy the code below and paste it into the Employees.aspx.cs file. Check that namespace matches your project name.
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace HRWebsite
{
public partial class Employees : System.Web.UI.Page
{
int Emp_ID;
SqlConnection myCon = new SqlConnection(ConfigurationManager.ConnectionStrings["HRDBConnection"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DoGridView();
}
}
private void DoGridView()
{
try
{
myCon.Open();
using (SqlCommand myCom = new SqlCommand("dbo.usp_GetEmployees", myCon))
{
myCom.Connection = myCon;
myCom.CommandType = CommandType.StoredProcedure;
SqlDataReader myDr = myCom.ExecuteReader();
gvEmployees.DataSource = myDr;
gvEmployees.DataBind();
myDr.Close();
}
}
catch (Exception ex) { lblMessage.Text = "Error in Employees doGridView: " + ex.Message; }
finally { myCon.Close(); }
}
protected void lbNewEmp_Click(object sender, EventArgs e)
{
try
{
txtEmployeeName.Text = "";
txtContactNo.Text = "";
txtEmail.Text = "";
lblEmployeeNew.Visible = true;
lblEmployeeUpd.Visible = false;
btnAddEmployee.Visible = true;
btnUpdEmployee.Visible = false;
GetCompaniesForDLL();
ScriptManager.RegisterStartupScript(this, this.GetType(), "Pop", "openEmpDetail();", true);
}
catch (Exception) { throw; }
}
protected void btnAddEmployee_Click(object sender, EventArgs e)
{
try
{
myCon.Open();
using (SqlCommand myCom = new SqlCommand("dbo.usp_InsEmployee", myCon))
{
myCom.CommandType = CommandType.StoredProcedure;
myCom.Parameters.Add("@EmployeeName", SqlDbType.VarChar).Value = txtEmployeeName.Text;
myCom.Parameters.Add("@ContactNo", SqlDbType.VarChar).Value = txtContactNo.Text;
myCom.Parameters.Add("@Email", SqlDbType.VarChar).Value = txtEmail.Text;
myCom.Parameters.Add("@CompID", SqlDbType.VarChar).Value = int.Parse(ddlCompany.SelectedValue);
myCom.ExecuteNonQuery();
}
}
catch (Exception ex) { lblMessage.Text = "Error in btnAddCompany_Click: " + ex.Message; }
finally { myCon.Close(); }
DoGridView();
}
protected void btnUpdEmployee_Click(object sender, EventArgs e)
{
UpdEmployee();
DoGridView();
}
protected void gvEmployees_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "UpdEmployee")
{
Emp_ID = Convert.ToInt32(e.CommandArgument);
txtEmployeeName.Text = "";
txtContactNo.Text = "";
txtEmail.Text = "";
lblEmployeeNew.Visible = false;
lblEmployeeUpd.Visible = true;
btnAddEmployee.Visible = false;
btnUpdEmployee.Visible = true;
GetCompaniesForDLL();
GetEmployee(Emp_ID);
ScriptManager.RegisterStartupScript(this, this.GetType(), "Pop", "openEmpDetail();", true);
}
}
protected void gvEmployees_RowDeleting(Object sender, GridViewDeleteEventArgs e)
{
Emp_ID = Convert.ToInt32(gvEmployees.DataKeys[e.RowIndex].Value.ToString());
try
{
myCon.Open();
using (SqlCommand cmd = new SqlCommand("dbo.usp_DelEmployee", myCon))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = Emp_ID;
cmd.ExecuteScalar();
}
}
catch (Exception ex) { lblMessage.Text = "Error in gvEmployees_RowDeleting: " + ex.Message; }
finally { myCon.Close(); }
DoGridView();
}
private void GetEmployee(int emp_ID)
{
try
{
myCon.Open();
using (SqlCommand myCmd = new SqlCommand("dbo.usp_GetEmployee", myCon))
{
myCmd.Connection = myCon;
myCmd.CommandType = CommandType.StoredProcedure;
myCmd.Parameters.Add("@ID", SqlDbType.Int).Value = emp_ID;
SqlDataReader myDr = myCmd.ExecuteReader();
if (myDr.HasRows)
{
while (myDr.Read())
{
txtEmployeeName.Text = myDr.GetValue(1).ToString();
txtContactNo.Text = myDr.GetValue(2).ToString();
txtEmail.Text = myDr.GetValue(3).ToString();
ddlCompany.SelectedValue = myDr.GetValue(4).ToString();
lblEmpID.Text = Emp_ID.ToString();
}
}
}
}
catch (Exception ex) { lblMessage.Text = "Error in Companies GetEmployee: " + ex.Message; }
finally { myCon.Close(); }
}
private void UpdEmployee()
{
try
{
myCon.Open();
using (SqlCommand cmd = new SqlCommand("dbo.usp_UpdEmployee", myCon))
{
cmd.Connection = myCon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = int.Parse(lblEmpID.Text);
cmd.Parameters.Add("@EmployeeName", SqlDbType.VarChar).Value = txtEmployeeName.Text;
cmd.Parameters.Add("@ContactNo", SqlDbType.VarChar).Value = txtContactNo.Text;
cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = txtEmail.Text;
cmd.Parameters.Add("@CompID", SqlDbType.VarChar).Value = ddlCompany.SelectedValue;
int rows = cmd.ExecuteNonQuery();
}
}
catch (Exception ex) { lblMessage.Text = "Error in Employees - UpdEmployee: " + ex.Message; }
finally { myCon.Close(); }
}
private void GetCompaniesForDLL()
{
try
{
myCon.Open();
using (SqlCommand cmd = new SqlCommand("dbo.usp_GetCompanies", myCon))
{
SqlDataReader myDr = cmd.ExecuteReader();
ddlCompany.DataSource = myDr;
ddlCompany.DataTextField = "CompanyName";
ddlCompany.DataValueField = "ID";
ddlCompany.DataBind();
ddlCompany.Items.Insert(0, new ListItem("-- Select Company --", "0"));
myDr.Close();
}
}
catch (Exception ex) { lblMessage.Text = "Error in Employees - GetCompaniesForDLL: " + ex.Message; }
finally { myCon.Close(); }
}
}
}Set the Default.aspx page as the Start Page by right-clicking on the Defaults.aspx page and selecting the Set As Start Page option.
Testing the Web Application
Now, run the website by pressing F5 and do a few tests. Alternatively, right-click on the Default.aspx page and select the View in Browser option (see image below).
Your default (Home) page will display in your default browser. Click on the Home, Companies, or Employees links to see the different pages.
Homepage
Click on Companies or Employees to go to those pages.
Companies Homepage
Employees Homepage
Click on New, Del or Upd to add, delete or edit records.
Add a Company
Delete a Company
Edit a Company
If you followed the above instructions, you should now have a working website where you can use the Stored Procedures to do the following:
- Create a new Company or Employee
- Read to display a grid view with the Companies and Employees
- Update a Company or Employee
- Delete a Company or Employee
I included a zip file of the solution and database scripts that you can download here.
Conclusion
In this tip, we have used the database created over the previous eight tutorials to create a functional website for CRUD operations on the database data.
Next Steps
In the next tip, we will look at creating a SQL Server Database project in the solution you have made where all your SQL objects can be kept.
Check out the many articles regarding SQL Server on the link below.
- Create a Microsoft SQL Server Database
- Create a Table in Microsoft SQL Server
- Create SQL Server Tables Using Foreign Keys for Referential Integrity
- How to Create a SQL Server Database, Tables, Foreign Keys and Data for an Application
- Create, Read, Update and Delete Data in SQL Server Tables
- Indexing in SQL with Clustered and Non-Clustered Indexes
- Create SQL Server Stored Procedures, Views and Functions
- Setup Security for a SQL Server Database via SSMS and T-SQL
