Contact Numbers (An E-Diary) Application in ASP.NET using SQL Server and Access Databases
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.
Comments
Post a Comment