Introduction
Introducing a daily use application developed in ASP.NET to manage the contact
numbers, birthday, email etc. This article will guide using step by step
approach to create such applications. I am using SQL Server and Access Databases
separately; any one may be used by the user of this application.
Why I Developed This?
Actually, my mobile phone a huge list of contact and it is very difficult to
manage on handset always, reason may be anything like data-loose or difficult to
find on mobile keypad. I exported my entire contact in CSV format and then I
imported that in Microsoft Access database and then in SQL Server.
Next, I developed a web application to manage that database having searching,
shorting, inserting, selecting, editing deleting like features. I would like to
share the processes here. Find the screen of running application.
Database Structure
Find the database structure that I have created for my application.
AccessDB.aspx Page (Front) Code
I am using show-hide div option to insert new record in database by using some
javascript codes.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AccessDB.aspx.cs" Inherits="_Default" %>
<!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>Using
Access Database</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<script type="text/javascript">
function toggle(o) {
var e =
document.getElementById(o);
e.style.display = e.style.display ==
'block' ?
'none' : 'block';
}
onload = function () {
var e, i = 0;
while (e =
document.getElementById(['list1'][i++])) {
e.style.display = 'none';
}
}
</script>
<div class="tabledata1">
<a href="#" onclick="toggle('list1');">Insert
New Record</a>
</div>
<div id="list1" style="font-family:Verdana;
font-size:11px;
background-color:#cccccc;
padding:10px;">
<table>
<tr>
<td>
<hr />
<table>
<tr>
<td>
Name
</td>
<td>
Mobile
</td>
<td>
Home Number
</td>
<td>
Company Number
</td>
<td>
Email
</td>
<td>
Office
</td>
<td>
Fax
</td>
<td>
Birthday
</td>
</tr>
<tr>
<td>
<asp:TextBox ID="name" runat="server" Width="105px"></asp:TextBox>
</td>
<td>
<asp:TextBox ID="mobile" runat="server" Width="105px"></asp:TextBox>
</td>
<td>
<asp:TextBox ID="homenumber" runat="server" Width="105px"></asp:TextBox>
</td>
<td>
<asp:TextBox ID="companynumber" runat="server" Width="105px"></asp:TextBox>
</td>
<td>
<asp:TextBox ID="email" runat="server" Width="105px"></asp:TextBox>
</td>
<td>
<asp:TextBox ID="office" runat="server" Width="105px"></asp:TextBox>
</td>
<td>
<asp:TextBox ID="fax" runat="server" Width="105px"></asp:TextBox>
</td>
<td>
<asp:TextBox ID="birthday" runat="server" Width="105px"></asp:TextBox>
</td>
</tr>
</table>
<div style="text-align:
right">
<asp:Button ID="btnSubmit" runat="server" Text="Insert"
onclick="btnSubmit_Click" />
</div>
<hr />
</td>
</tr>
</table>
</div>
</div>
<br />
<div style="font-family:Verdana;
font-size:11px;
background-color:#c0c0c0;
padding:10px;">
Search by
<asp:DropDownList ID="DropDownList1" runat="server">
<asp:ListItem>Person_Name</asp:ListItem>
<asp:ListItem>Mobile</asp:ListItem>
<asp:ListItem>Home</asp:ListItem>
<asp:ListItem>Company</asp:ListItem>
<asp:ListItem>Email</asp:ListItem>
<asp:ListItem>Office</asp:ListItem>
<asp:ListItem>Fax</asp:ListItem>
<asp:ListItem>Birthday</asp:ListItem>
</asp:DropDownList>
where like
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Filter" onclick="Button1_Click" />
<asp:Button ID="Button2" runat="server" Text="Clear Filter" OnClick="Button2_Click" />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
BackColor="White" BorderColor="White" BorderStyle="Ridge" BorderWidth="2px"
CellPadding="3" CellSpacing="1" DataKeyNames="ID"
DataSourceID="AccessDataSource1"
EmptyDataText="There
are no data records to display." GridLines="None"
Width="871px" AllowSorting="True">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True"
ShowSelectButton="True" />
<asp:BoundField DataField="Person_Name" HeaderText="Person_Name"
SortExpression="Person_Name" />
<asp:BoundField DataField="Mobile" HeaderText="Mobile"
SortExpression="Mobile" />
<asp:BoundField DataField="Home" HeaderText="Home" SortExpression="Home" />
<asp:BoundField DataField="Company" HeaderText="Company"
SortExpression="Company" />
<asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />
<asp:BoundField DataField="Office" HeaderText="Office"
SortExpression="Office" />
<asp:BoundField DataField="Fax" HeaderText="Fax" SortExpression="Fax" />
<asp:BoundField DataField="Birthday" HeaderText="Birthday"
SortExpression="Birthday" />
</Columns>
<FooterStyle BackColor="#C6C3C6" ForeColor="Black" />
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#E7E7FF" />
<PagerStyle BackColor="#C6C3C6" ForeColor="Black" HorizontalAlign="Right" />
<RowStyle BackColor="#DEDFDE" ForeColor="Black" />
<SelectedRowStyle BackColor="#9471DE" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#594B9C" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#33276A" />
</asp:GridView>
<asp:AccessDataSource ID="AccessDataSource1" runat="server"
DataFile="App_Data\ContactsDB.mdb"
DeleteCommand="DELETE
FROM `ContactsTB` WHERE `ID` = ?"
InsertCommand="INSERT
INTO `ContactsTB` (`ID`, `Person_Name`, `Mobile`, `Home`, `Company`, `Email`,
`Office`, `Fax`, `Birthday`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
SelectCommand="SELECT
`ID`, `Person_Name`, `Mobile`, `Home`, `Company`, `Email`, `Office`, `Fax`,
`Birthday` FROM `ContactsTB`"
UpdateCommand="UPDATE
`ContactsTB` SET `Person_Name` = ?, `Mobile` = ?, `Home` = ?, `Company` = ?,
`Email` = ?, `Office` = ?, `Fax` = ?, `Birthday` = ? WHERE `ID` = ?">
<DeleteParameters>
<asp:Parameter Name="ID" Type="Int32" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="ID" Type="Int32" />
<asp:Parameter Name="Person_Name" Type="String" />
<asp:Parameter Name="Mobile" Type="String" />
<asp:Parameter Name="Home" Type="String" />
<asp:Parameter Name="Company" Type="String" />
<asp:Parameter Name="Email" Type="String" />
<asp:Parameter Name="Office" Type="String" />
<asp:Parameter Name="Fax" Type="String" />
<asp:Parameter Name="Birthday" Type="String" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="Person_Name" Type="String" />
<asp:Parameter Name="Mobile" Type="String" />
<asp:Parameter Name="Home" Type="String" />
<asp:Parameter Name="Company" Type="String" />
<asp:Parameter Name="Email" Type="String" />
<asp:Parameter Name="Office" Type="String" />
<asp:Parameter Name="Fax" Type="String" />
<asp:Parameter Name="Birthday" Type="String" />
<asp:Parameter Name="ID" Type="Int32" />
</UpdateParameters>
</asp:AccessDataSource>
</div>
</form>
</body>
</html>
AccessDB.aspx Page Code-Behind
In the code given below I am using quicker code to filter contact and to insert
new record I am calling a execution() method that has sql query and rest
mechanism.
public partial class _Default : System.Web.UI.Page
{
protected void
Button1_Click(object sender,
EventArgs e)
{
string FilterExpression =
string.Concat(DropDownList1.SelectedValue,
" LIKE '%{0}%'");
AccessDataSource1.FilterParameters.Clear();
AccessDataSource1.FilterParameters.Add(new ControlParameter(DropDownList1.SelectedValue,
"TextBox1",
"Text"));
AccessDataSource1.FilterExpression = FilterExpression;
}
protected void
Button2_Click(object sender,
EventArgs e)
{
AccessDataSource1.SelectParameters.Clear();
}
protected void
btnSubmit_Click(object sender,
EventArgs e)
{
execution(Convert.ToString(name.Text),
Convert.ToString(mobile.Text),
Convert.ToString(homenumber.Text),
Convert.ToString(companynumber.Text),
Convert.ToString(email.Text),
Convert.ToString(office.Text),
Convert.ToString(fax.Text),
Convert.ToString(birthday.Text));
Response.Redirect("~/AccessDB.aspx");
}
private void
execution(string person_name,
string mobile,
string home, string company,
string email,
string office, string fax,
string birthday)
{
try
{
string connectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + Server.MapPath("App_Data\\ContactsDB.mdb")
+ ";";
OleDbConnection conn
= new
OleDbConnection(connectionString);
conn.Open();
OleDbCommand cmd =
new
OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = "INSERT
INTO ContactsTB (Person_Name, Mobile, Home, Company, Email, Office, Fax,
Birthday) VALUES (@person_name, @mobile, @home, @company, @email, @office, @fax,
@birthday)";
cmd.Parameters.Add("@person_name",
OleDbType.VarChar).Value =
person_name;
cmd.Parameters.Add("@mobile",
OleDbType.VarChar).Value =
mobile;
cmd.Parameters.Add("@home",
OleDbType.VarChar).Value = home;
cmd.Parameters.Add("@company",
OleDbType.VarChar).Value =
company;
cmd.Parameters.Add("@email",
OleDbType.VarChar).Value = email;
cmd.Parameters.Add("@office",
OleDbType.VarChar).Value =
office;
cmd.Parameters.Add("@fax",
OleDbType.VarChar).Value = fax;
cmd.Parameters.Add("@birthday",
OleDbType.VarChar).Value =
birthday;
cmd.ExecuteNonQuery();
conn.Close();
}
catch (System.Data.SqlClient.SqlException
ex_msg)
{
string msg =
"Error occured while inserting the
comment.";
msg += ex_msg.Message;
throw
new Exception(msg);
}
}
}
The same process used for SQL Server Database, you can download the attached
project and take a look at procedures.
I hope you like it. Please post your comments.