|
|
Building as Address Book using ASP.NET
Posted On December 29, 2007 by Sneha Latha filed under Miscellaneous
You must have written address in many places. For example in cellular phone, phone book or in a diary etc. But the problem was that none of them had the same information and when we need to change the phone number or name or address we need to make changes in different location. For that reason we will a web based address book that can be access any ware and any time.
In this article we will see how to build an address book using ASP.NET and SQL 2000 will be used as the backend database.
This project consists of a single data entity and four logical functions to manipulate the data. Using following steps you can build the application.
· Create a database table that will store the data.
· Create a Form to retrieve all the entries from the table.
· Create a Form to add a new record.
· Create a Form to modify an existing record.
· Create a Form to delete a record.
Creating a database table
In this article all the information about each person will be stored in a signal record in a single table. First thing we need to figure out what information we need to store regarding each person in the database. For some of the entries, you can also break the single table into multiple tables. For Example, instead of having duplicate address field in a single record, you might want to create an address table that is linked to the primary table with a primary key / foreign key relationship. But in this article we keep things simple and use a single table called adtable.
Crete the following table in the SQL Server 2000.
Table: Adtable
| Name | Data Type | Length |
| ID | int | 10 |
| Firstname | varchar | 25 |
| Lastname | varchar | 25 |
| Title | varchar | 25 |
| Companyname | varchar | 50 |
| Address | varchar | 200 |
| Hphone | varchar | 15 |
| Wphone | varchar | 15 |
| Fax | varchar | 15 |
| | varchar | 50 |
| webpage | varchar | 50 |
| Notes | text |
|
Build a Viewer
To build this page, you can use either C# ro Visual Basic .NET for the code. These two languages are supported in ASP.NET. ASP.NET contains number of controls that are designed to make life easier for the ASP.NET developers. Using these controls you can use data binding, which is an automatic way to put data on the page. In this article we will see how to retrieve the record from database.
For Example DataGrid control displays tabular data and optionally supports selecting, sorting, paging, and editing the data. By default, DataGrid generates a BoundColumn for each field in the data source (AutoGenerateColumns=true). Each field in the data is rendered in a separate column, in the order it occurs in the data. Field names appear in the grid's column headers, and values are rendered in text labels
Follow these steps to show the records from SQL Server database.
1. Create a connection to the database from the ASP.NET application using SqlConnection.
2. Retrieve the data from the database using a SqlDataReader object.
3. Retrieve the data from loop, and display the results in the HTML output in the Label control.
Now lets start actual coding work. Open the Visual Studio.NET, click on the Visual Basic Projects and select the ASP.NET web application project.
Add the following code in the view_addresses.aspx page as show below.
<%@ Page Language="VB" Debug="true" %>
<% @ Import Namespace="System" %>
<% @ Import Namespace="System.Data" %>
<% @ Import Namespace="System.Data.SqlClient" %>
<HTML>
<HEAD>
<title>All Contacts from Address Book</title>
</HEAD>
<body bgcolor="#ffffff">
<h2>All Contacts from the Address Book</h2>
<table cellpadding="4" cellspacing="0" width="100%">
<tr>
<th>
Name</th>
<th>
Title</th>
<th>
Company Name</th>
<th>
Work Phone</th>
</tr>
<asp:label id="lblOutput" runat="server"></asp:label>
</table>
<script runat="SERVER">
Sub Page_Load(Src As Object, e As EventArgs)
Dim sqlConn As New SqlConnection
Dim sqlCmd As New SqlCommand
Dim sdrData As SqlDataReader
Dim sb As New StringBuilder
sqlConn.ConnectionString = _
"server=localhost;database=newaddbook;uid=sa;pwd=;"
sqlConn.Open()
sqlCmd.CommandText = "SELECT * FROM adtable " _
& "ORDER BY FirstName, LastName"
sqlCmd.CommandType = CommandType.Text
sqlCmd.Connection = sqlConn
sdrData = sqlCmd.ExecuteReader()
While sdrData.Read()
sb.Append("<tr>" + vbCrLf)
sb.AppendFormat(" <td>{0}, {1}</td>" + vbCrLf, _
sdrData("FirstName"), _
sdrData("LastName"))
sb.AppendFormat(" <td>{0}</td>" + vbCrLf, sdrData("Title"))
sb.AppendFormat(" <td>{0}</td>" + vbCrLf, _
sdrData("CompanyName"))
sb.AppendFormat(" <td>{0}</td>" + vbCrLf, sdrData("WPhone"))
sb.Append("</tr>" + vbCrLf)
End While
sdrData.Close()
sqlConn.Close()
lblOutput.Text = sb.ToString()
End Sub
</script>
</body>
</HTML>
The first line is the page directive. The Language parameter specifies that Visual Basic .Net is used as the language for the code page. The Debug parameter specifies that detailed debugging information is to be provided when any errors occur. The next three lines specify various system libraries that need to be referenced in order to get different objects that we need for our application.
In the above code Page_load event occurs when the user request this particular page from the server. You can also specify more the one language by adding the Language parameter to the script tag. But in our article we use the default language of VB.
The System.Data.SqlClient namespace is the .NET Framework Data Provider for SQL Server.
The .NET Framework Data Provider for SQL Server describes a collection of classes used to access a SQL Server database in the managed space.
SqlConnection: A SqlConnection object represents a unique session to a SQL Server data source. In the case of a client/server database system, it is equivalent to a network connection to the server.
SqlCommand: Represents a Transact-SQL statement or stored procedure to execute against a SQL Server database. Also you can use OleDbCommand with other databases.
SqlDataReader: Provides access to the data returned from the SqlCommand query. The DataReader class is the fastest and most efficient way to get data from a database in a read-only. For other databases OleDbDataReader class is available.
StringBuilder Class : The StringBuilder class represents a mutable string of characters. It's called mutable because it can be modified once it has been created by using Append, Insert, Remove, and Replace methods. The StringBuilder class is defined in the System.Text namespace.
By using connection string you can connect to the database, and use the SQLCommand object to run the query. The result is available through the SqlDataReader by calling the ExecuteReader method on the SqlCommand object. Then we can build the HTML output.