Building a simple address control in .NET
Walkthrough: Build a simple .NET control that allows the insert, update and deletion of customer address records in SQL Server
If you want to run through this walkthrough, please follow the guide on setting up our test environment.
Experience Level - Intermediate
Aim
When designing your web applications you want to try and keep down the amount of space used to a minimum, so this code snippet for address management might well help (image to the right).
The only thing we need to do is create a couple of basic SQL tables and then bind a DetailsView to it.
Lets start by creating the code you need for the tables and a couple of stored procedures to handle the data input and output.
The other thing we are going to do is bind a drop down list within the edit and insert modes (I have seen this asked for a lot on forums).
SQL
CREATE TABLE UserAddressType(--The types of addresses you want...UserAddressTypeID INT IDENTITY(1,1) CONSTRAINT PK_UserAddressType PRIMARY KEY,UserAddressTypeName NVARCHAR(100))INSERT INTO UserAddressType(UserAddressTypeName) SELECT 'Home'INSERT INTO UserAddressType(UserAddressTypeName) SELECT 'Billing'INSERT INTO UserAddressType(UserAddressTypeName) SELECT 'Delivery'--INSERT INTO UserAddressType(UserAddressTypeName) SELECT 'And so on!'
CREATE TABLE UserAddress(--The address table...UserAddressID BIGINT IDENTITY(1,1) NOT NULL CONSTRAINT PK_UserAddress PRIMARY KEY,UserLinkID BIGINT,--This will need changing to the datatype that your user table usesUserAddressTypeLinkID INT CONSTRAINT FK_UserAddressType FOREIGN KEY REFERENCES UserAddressType(UserAddressTypeID),UserAddressName NVARCHAR(100),UserAddressL1 NVARCHAR(100),UserAddressL2 NVARCHAR(100),UserAddressL3 NVARCHAR(100),UserAddressTown NVARCHAR(100),UserAddressCounty NVARCHAR(100),UserAddressCountry NVARCHAR(100),UserAddressCode NVARCHAR(10))GO--The code to handle our updates...CREATE PROC UpdUserAddress(@UserAddressID BIGINT,@UserID BIGINT,@AddressTypeID INT,@UserAddressName NVARCHAR(100),@UserAddressL1 NVARCHAR(100),@UserAddressL2 NVARCHAR(100),@UserAddressL3 NVARCHAR(100),@UserAddressTown NVARCHAR(100),@UserAddressCounty NVARCHAR(100),@UserAddressCode NVARCHAR(10)) AS BEGINIF @UserAddressID=0 BEGININSERT INTO UserAddress(UserLinkID,UserAddressTypeLinkID,UserAddressName,UserAddressL1,UserAddressL2,UserAddressL3,UserAddressTown,UserAddressCounty,UserAddressCode)SELECT @UserID,@AddressTypeID,@UserAddressName,@UserAddressL1,@UserAddressL2,@UserAddressL3,@UserAddressTown,@UserAddressCounty,@UserAddressCodeENDELSE BEGINUPDATE UserAddress SET UserAddressTypeLinkID=@AddressTypeID,UserAddressName=@UserAddressName,UserAddressL1=@UserAddressL1,UserAddressL2=@UserAddressL2,UserAddressL3=@UserAddressL3,UserAddressTown=@UserAddressTown,UserAddressCounty=@UserAddressCounty,@UserAddressCode=UserAddressCodeWHERE UserAddressID=@UserAddressIDENDENDGO--The code to handle our selection...CREATE PROC GetUserAddress(@UserID BIGINT) AS BEGINSELECT * FROM UserAddressINNER JOIN UserAddressType ON UserAddressTypeLinkID=UserAddressTypeIDWHERE UserLinkID=@UserIDENDGOCreate a control
Within the Academy Project, we now need to add a new Web Form called AddressControl.
Within the HTML Section you can either drag in a DetailsView or use the following code...
HTML
<asp:DetailsView ID="AddressData" runat="server" AllowPaging="True" AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" AutoGenerateInsertButton="True" AutoGenerateRows="False" DataKeyNames="UserAddressID" style="text-align: center" Caption="Addresses" GridLines="None" CssClass="ControlGrid"> <EmptyDataRowStyle /> <AlternatingRowStyle CssClass="rs" /> <CommandRowStyle CssClass="cr" /> <EditRowStyle CssClass="rs" /> <EmptyDataTemplate> <asp:LinkButton ID="AddNewAddress" runat="server" onclick="AddNewAddress_Click">Add</asp:LinkButton> </EmptyDataTemplate> <FieldHeaderStyle CssClass="th" /> <PagerStyle CssClass="pgr" /> <RowStyle CssClass="rs" /> <Fields> <asp:TemplateField HeaderText="Address Type"> <EditItemTemplate> <asp:DropDownList ID="AddressType" runat="server" DataSourceID="SqlDataSource1" DataTextField="UserAddressTypeName" DataValueField="UserAddressTypeID" SelectedValue='<%#Eval("UserAddressTypeLinkID") %>'> </asp:DropDownList> </EditItemTemplate> <InsertItemTemplate> <asp:DropDownList ID="AddressType" runat="server" DataSourceID="SqlDataSource1" DataTextField="UserAddressTypeName" DataValueField="UserAddressTypeID"></asp:DropDownList> </InsertItemTemplate> <ItemTemplate> <asp:Label ID="AddressType" runat="server" Text='<%# Bind("UserAddressTypeName") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField AccessibleHeaderText="Address Name" HeaderText="Address Name"> <EditItemTemplate> <asp:TextBox ID="AddressName" runat="server" Text='<%# Bind("UserAddressName") %>'></asp:TextBox> </EditItemTemplate> <InsertItemTemplate> <asp:TextBox ID="AddressName" runat="server" Text='<%# Bind("UserAddressName") %>'></asp:TextBox> </InsertItemTemplate> <ItemTemplate> <asp:Label ID="AddressName" runat="server" Text='<%# Bind("UserAddressName") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Address Line 1"> <EditItemTemplate> <asp:TextBox ID="AddressL1" runat="server" Text='<%# Bind("UserAddressL1") %>'></asp:TextBox> </EditItemTemplate> <InsertItemTemplate> <asp:TextBox ID="AddressL1" runat="server" Text='<%# Bind("UserAddressL1") %>'></asp:TextBox> </InsertItemTemplate> <ItemTemplate> <asp:Label ID="Label9" runat="server" Text='<%# Bind("UserAddressL1") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Address Line 2"> <EditItemTemplate> <asp:TextBox ID="AddressL2" runat="server" Text='<%# Bind("UserAddressL2") %>'></asp:TextBox> </EditItemTemplate> <InsertItemTemplate> <asp:TextBox ID="AddressL2" runat="server" Text='<%# Bind("UserAddressL2") %>'></asp:TextBox> </InsertItemTemplate> <ItemTemplate> <asp:Label ID="Label10" runat="server" Text='<%# Bind("UserAddressL2") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Address Line 3"> <EditItemTemplate> <asp:TextBox ID="AddressL3" runat="server" Text='<%# Bind("UserAddressL3") %>'></asp:TextBox> </EditItemTemplate> <InsertItemTemplate> <asp:TextBox ID="AddressL3" runat="server" Text='<%# Bind("UserAddressL3") %>'></asp:TextBox> </InsertItemTemplate> <ItemTemplate> <asp:Label ID="Label11" runat="server" Text='<%# Bind("UserAddressL3") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Address Line 4"> <EditItemTemplate> <asp:TextBox ID="AddressL4" runat="server" Text='<%# Bind("UserAddressTown") %>'></asp:TextBox> </EditItemTemplate> <InsertItemTemplate> <asp:TextBox ID="AddressL4" runat="server" Text='<%# Bind("UserAddressTown") %>'></asp:TextBox> </InsertItemTemplate> <ItemTemplate> <asp:Label ID="Label12" runat="server" Text='<%# Bind("UserAddressTown") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="County"> <EditItemTemplate> <asp:TextBox ID="AddressL5" runat="server" Text='<%# Bind("UserAddressCounty") %>'></asp:TextBox> </EditItemTemplate> <InsertItemTemplate> <asp:TextBox ID="AddressL5" runat="server" Text='<%# Bind("UserAddressCounty") %>'></asp:TextBox> </InsertItemTemplate> <ItemTemplate> <asp:Label ID="Label13" runat="server" Text='<%# Bind("UserAddressCounty") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Post Code"> <EditItemTemplate> <asp:TextBox ID="AddressPC" runat="server" Text='<%# Bind("UserAddressCode") %>'></asp:TextBox> </EditItemTemplate> <InsertItemTemplate> <asp:TextBox ID="AddressPC" runat="server" Text='<%# Bind("UserAddressCode") %>'></asp:TextBox> </InsertItemTemplate> <ItemTemplate> <asp:Label ID="Label14" runat="server" Text='<%# Bind("UserAddressCode") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> </Fields> </asp:DetailsView>Adding styling
I have included some styling options below, if you know your CSS then have a play with this.
CSS
<style type="text/css"> .ControlGrid {border-collapse: collapse;display: block;background-color: #EEEEEE;width: 600px;} .ControlGrid td {border: 0px;} .ControlGrid .th {width: 195px;padding-right: 5px;text-align: right;} .ControlGrid .rs {color: Black;text-align: left;width: 600px; display: block;} .ControlGrid input[type="text"] {width: 390px;} .ControlGrid .cr {width: 100%;} .ControlGrid .cr table {margin: 0px auto 0px auto;} .ControlGrid .cr td {text-align: center;border: 0px;} .ControlGrid .cr a {color: Black; text-decoration: none;} .ControlGrid .cr a:hover {color: Green; text-decoration: none;} .ControlGrid .pgr {width: 100%;} .ControlGrid .pgr table {margin: 0px auto 0px auto;} .ControlGrid .pgr td {text-align: center;} .ControlGrid .pgr a {color: Black; text-decoration: none;} .ControlGrid .pgr a:hover {color: Green; text-decoration: none;} </style>Data binding
Now lets bind the data from the database, we will create code to handle inserting and updating the database, and put in an SQL Connection and SQL Data Source to our academy database (in this case linked to the WebConfig file, see below).
Your code will need to look something like this.
Connection string in web config
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SqlConnection %>" SelectCommand="SELECT * FROM [UserAddressType]"> </asp:SqlDataSource> <connectionStrings> <add name="SqlConnection" connectionString="Server=localhost\SQLEXPRESS;Database=ClaytabaseAcademy;Trusted_Connection=True;" providerName="System.Data.SqlClient" /> </connectionStrings>VB.NET
Imports System.Data.SqlClientPublic Class AddressControl Inherits System.Web.UI.Page Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("SqlConnection").ConnectionString) Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not IsPostBack Then con.Open() GetAddresses() con.Close() End If End Sub Protected Sub GetAddresses() AddressData.DataSource = Nothing AddressData.DataBind() Dim UserID As Integer = 1 Dim com As New SqlCommand("EXEC GetUserAddress '" & UserID & "'", con) Dim adapt As New SqlDataAdapter(com) Dim dt As New DataTable adapt.Fill(dt) AddressData.DataSource = dt AddressData.DataBind() End Sub Protected Sub UpdateAddress() Dim UserID As Integer = 1 Dim AddID As Integer = 0 If Not IsNothing(AddressData.DataKey("UserAddressID")) Then AddID = AddressData.DataKey("UserAddressID") Dim AddNameStr As TextBox = AddressData.FindControl("AddressName") Dim AddL1Str As TextBox = AddressData.FindControl("Addressl1") Dim AddL2Str As TextBox = AddressData.FindControl("AddressL2") Dim AddL3Str As TextBox = AddressData.FindControl("AddressL3") Dim AddL4Str As TextBox = AddressData.FindControl("AddressL4") Dim AddL5Str As TextBox = AddressData.FindControl("AddressL5") Dim AddPCStr As TextBox = AddressData.FindControl("AddressPC") Dim AddType As DropDownList = AddressData.FindControl("AddressType") Dim com As New SqlCommand("EXEC UpdUserAddress " & AddID & ",'" & UserID & "'," & Replace(AddType.SelectedValue, "'", "''") & ", " & "'" & Replace(AddNameStr.Text.ToString(), "'", "''") & "','" & Replace(AddL1Str.Text.ToString(), "'", "''") & "', " & "'" & Replace(AddL2Str.Text.ToString(), "'", "''") & "','" & Replace(AddL3Str.Text.ToString(), "'", "''") & "', " & "'" & Replace(AddL4Str.Text.ToString(), "'", "''") & "','" & Replace(AddL5Str.Text.ToString(), "'", "''") & "'," & "'" & Replace(AddPCStr.Text.ToString(), "'", "''") & "'", con) com.ExecuteNonQuery() AddressData.AllowPaging = True AddressData.ChangeMode(DetailsViewMode.ReadOnly) End Sub Protected Sub AddressData_ModeChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewModeEventArgs) Handles AddressData.ModeChanging 'handles the mode changing events, Insert/Update/Cancel etc... con.Open() If e.NewMode = DetailsViewMode.Edit Then AddressData.AllowPaging = False AddressData.ChangeMode(DetailsViewMode.Edit) GetAddresses() ElseIf e.NewMode = DetailsViewMode.Insert Then AddressData.AllowPaging = False AddressData.ChangeMode(DetailsViewMode.Insert) ElseIf e.NewMode = DetailsViewMode.ReadOnly Then AddressData.AllowPaging = True AddressData.ChangeMode(DetailsViewMode.ReadOnly) GetAddresses() Else End If con.Close() End Sub Protected Sub AddressData_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewPageEventArgs) Handles AddressData.PageIndexChanging 'Page changing event AddressData.PageIndex = Val(e.NewPageIndex) con.Open() GetAddresses() con.Close() End Sub Protected Sub AddNewAddress_Click(ByVal sender As Object, ByVal e As System.EventArgs) 'for when there are no addresses, sets the control to Insert mode. AddressData.AllowPaging = False AddressData.ChangeMode(DetailsViewMode.Insert) End Sub Protected Sub AddressData_ItemInserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertEventArgs) Handles AddressData.ItemInserting 'Insert code, we also re-bing the control to the database con.Open() UpdateAddress() GetAddresses() con.Close() End Sub Protected Sub AddressData_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdateEventArgs) Handles AddressData.ItemUpdating 'Update code the same as Insert con.Open() UpdateAddress() GetAddresses() con.Close() End Sub Protected Sub AddressData_ItemDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewDeleteEventArgs) Handles AddressData.ItemDeleting 'Deletion code... con.Open() AddressData.ChangeMode(DetailsViewMode.ReadOnly) Dim ID As Integer = AddressData.DataKey("UserAddressID").ToString() Dim com As New SqlCommand("DELETE FROM UserAddress WHERE UserAddressID=" & ID & "", con) com.ExecuteNonQuery() GetAddresses() con.Close() End SubEnd ClassYou should now be able to use your application and edit the database.
Website design by Claytabase
This is a section of code that has been modified from Ousia Content Management System code, one of the quickest and most optimised systems on the market, part of our website design services.
These are available with sites starting at around £500.
