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.

image002.jpg

Database Structure

Find the database structure that I have created for my application.

image004.jpg
 


 

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&nbsp;
            <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>
            &nbsp;
            where like
            &nbsp;
            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
            &nbsp;&nbsp;
            <asp:Button ID="Button1" runat="server" Text="Filter" onclick="Button1_Click" />
            &nbsp;&nbsp;&nbsp;
            <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.