.NET での単純なアドレスコントロールの構築

チュートリアル: SQL Server で顧客住所レコードの挿入、更新、および削除を可能にする単純な .NET コントロールを構築する

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=@UserIDENDGO

Create 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 Class

You should now be able to use your application and edit the database.

Claytabaseによるウェブサイトのデザイン

これは、当社の Web サイトデザインサービスの一部である、市場で最も高速で最適化されたシステムの 1 つである Ousia コンテンツ管理システムコードから変更されたコードのセクションです。

これらは、約£500から始まるサイトで利用できます.

詳細: レスポンシブで高速。コンテンツ管理システムを使用した Web 開発、設計、およびホスティング