ASP.NET+SQL房地产管理系统设计与实现

系统功能流程图

系统功能的流程图如图3-1所示。

添加图片注释，不超过 140 字（可选）

3.2 数据库设计

3.2.1 数据库的总体设计

为实现房地产管理系统功能，配套系统各个功能设计多个数据库表。为更好的实现系统并在多个表建立关联并使用了触发器。

3.2.2 数据库表的设计

1．登录表(username,userpwd,useridentity)其中username为主关键字。该表用来存储登录用户的用户名、密码和身份。表的结构如表3-1所示：

表 3-1登录表

添加图片注释，不超过 140 字（可选）

2.员工信息表

(empid ,empname ,emppwd,empaddress, emptel,empemail,empidentitycard）

该表用来存储员工的信息。empid为主关键字。当员工信息表插入信息时，通过触发器自动往登录表插入相关信息。表的结构如表3-2所示。

添加图片注释，不超过 140 字（可选）

3．客户信息表（客户编号，客户姓名 ,客户密码，联系电话,电邮地址,身份证号,房源编号, 登记日期）其中客户编号为主关键字，登记日期为系统自动生成。当客户信息表插入信息时，通过触发器自动往登录表插入相关信息。表的结构如表3-3所示。

添加图片注释，不超过 140 字（可选）

4．房源登记表（房源编号，所属区域,街区,房型,楼层,总层数,朝阳,面积,装修情况,房屋设施,房主性质,价格,客户编号,登记日期）其中房源编号为主关键字,客户编号为外关键字（外键为客户信息表的客户编号）当客户信息被删除时其相应的房子的信息也被级联删除，表的结构如表3-4所示。

添加图片注释，不超过 140 字（可选）

4.2 系统的实现

4.2.1 用户登录界面

经过上面各节的工作后，我们基本上完成了一个房地产信息管理系统的开发。将工程中的welcom.aspx设为起始页，然后执行程序，系统会显示房产信息管理系统的首页，如图4-1所示。输入用户名和密码，单击“确认”按钮，与数据库的登录表进行连接并进行身份验证。当身份验证成功后就可以使用系统列表中的功能了。

添加图片注释，不超过 140 字（可选）

用户登录界面其主要代码.

 Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        '在此处放置初始化页的用户代码
        Me.Button1.Attributes.Add("onclick", "window.close()")

    End Sub

    Private Sub btnlogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnlogin.Click
        Dim connstr As String = "data source=(local);initial catalog=lwc;uid=sa;pwd=sa"
        Dim sconn As New SqlConnection
        sconn.ConnectionString = connstr
        sconn.Open()
        Dim username As String
        Dim userpwd As String
        Dim identity As String
        identity = DDlidentity.SelectedItem.Text
        username = txtusername.Text
        userpwd = txtpwd.Text
        Dim scomm As New SqlCommand("select count(*) from login where username = '" + username + "' And userpwd = '" + userpwd + "'and useridentity='" + identity + "'", sconn)

        Dim s As Single
        s = scomm.ExecuteScalar()
        If s > 0 Then 'And ‘identity = "管理员" Then
            Session("id") = identity

            Response.Redirect("zhjm.aspx")
        End If

    End Sub

4.2.2 房源信息表

单击“房源信息表”链接，进入房源信息表如图4-2所示。员工可以登记修改房源信息。在页面中，我们可以查看添加修改房源信息并保存到数据库的相应表中。

添加图片注释，不超过 140 字（可选）

房源信息表用于房源信息的添加修改与删除其主要代码如下：

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not Page.IsPostBack Then
            Me.dbind()
        End If
        '在此处放置初始化页的用户代码

    End Sub

    Private Sub LinkButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LinkButton1.Click
        Response.Redirect("kehu.aspx")
    End Sub
    Private Sub dbind()
        Dim connstr1 As String = "data source=(local);initial catalog=lwc;uid=sa;pwd=sa"
        Dim sconn1 As New SqlConnection
        sconn1.ConnectionString = connstr1
        sconn1.Open()

        Dim ada As New SqlDataAdapter
        Dim scomm1 As New SqlCommand("select *  from 房源登记表", sconn1)
        ada.SelectCommand = scomm1
        Dim ds As New DataSet
        ds.Clear()
        ada.Fill(ds, "房源登记表")
        Me.DataGrid1.DataKeyField = "房源编号"
        Me.DataGrid1.DataSource = ds
        Me.DataGrid1.DataBind()
    End Sub


    Private Sub DataGrid1_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles DataGrid1.PageIndexChanged
        Me.DataGrid1.CurrentPageIndex = e.NewPageIndex
        Me.dbind()
    End Sub

    Private Sub DataGrid1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles DataGrid1.ItemDataBound
        If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
         
            Dim link As LinkButton = CType(e.Item.Cells(11).Controls(0), LinkButton)
            link.Attributes.Add("onclick", "return confirm('你确认删除吗？')")

        End If
    End Sub

    Private Sub DataGrid1_DeleteCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.DeleteCommand
        Dim empid As String
        empid = Me.DataGrid1.DataKeys(e.Item.ItemIndex)
        Dim connstr1 As String = "data source=(local);initial catalog=lwc;uid=sa;pwd=sa"
        Dim sconn1 As New SqlConnection
        sconn1.ConnectionString = connstr1
        sconn1.Open()
        Dim ada As New SqlDataAdapter
        Dim scomm1 As New SqlCommand("delete from 房源登记表 where 房源编号='" + empid + "'", sconn1)
        'ada.DeleteCommand = scomm1
        ' Dim ds As New DataSet
        'ada.Fill(ds, "employees")
        'Me.DataGrid1.DataSource = ds
        ' Me.DataGrid1.DataBind()
        scomm1.ExecuteNonQuery()
        Me.dbind()
    End Sub

    Private Sub DataGrid1_EditCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.EditCommand
        Me.DataGrid1.EditItemIndex = e.Item.ItemIndex
        Me.dbind()
    End Sub

    Private Sub DataGrid1_CancelCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.CancelCommand
        Me.DataGrid1.EditItemIndex = -1
        Me.dbind()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Response.Redirect("kehu.aspx")

    End Sub

    Private Sub DataGrid1_UpdateCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.UpdateCommand
        Dim connstr1 As String = "data source=(local);initial catalog=lwc;uid=sa;pwd=sa"
        Dim sconn1 As New SqlConnection
        sconn1.ConnectionString = connstr1
        sconn1.Open()
        Dim empid As String
        empid = Me.DataGrid1.DataKeys(e.Item.ItemIndex)
        Dim ssqy, jq, fx, lc, zcs, cy, mj, zxqk, fwss, djrq As String
        Dim txtssqy As TextBox = CType(e.Item.Cells(1).Controls(0), TextBox)
        Dim txtjq As TextBox = CType(e.Item.Cells(2).Controls(0), TextBox)
        Dim txtfx As TextBox = CType(e.Item.Cells(3).Controls(0), TextBox)
        Dim txtlc As TextBox = CType(e.Item.Cells(4).Controls(0), TextBox)
        Dim txtzcs As TextBox = CType(e.Item.Cells(5).Controls(0), TextBox)
        Dim txtcy As TextBox = CType(e.Item.Cells(6).Controls(0), TextBox)
        Dim txtmj As TextBox = CType(e.Item.Cells(7).Controls(0), TextBox)
        Dim txtzxqk As TextBox = CType(e.Item.Cells(8).Controls(0), TextBox)
        Dim txtfwss As TextBox = CType(e.Item.Cells(9).Controls(0), TextBox)
        Dim txtdjrq As TextBox = CType(e.Item.Cells(10).Controls(0), TextBox)
        ssqy = txtssqy.Text
        jq = txtjq.Text
        fx = txtfx.Text
        lc = txtlc.Text
        zcs = txtzcs.Text
        cy = txtcy.Text
        mj = txtmj.Text
        zxqk = txtzxqk.Text
        fwss = txtfwss.Text
        djrq = txtdjrq.Text

        Dim scomm As New SqlCommand("update 房源登记表  set 所属区域='" + ssqy + "',街区='" + jq + "',房型='" + fx + "',楼层='" + lc + "',总层数='" + zcs + "',朝阳='" + cy + "',面积='" + mj + "',装修情况='" + zxqk + "',房屋设施='" + fwss + "' where 房源编号='" + empid + "'", sconn1)
        scomm.ExecuteNonQuery()
        sconn1.Close()
        Me.DataGrid1.EditItemIndex = -1
        Me.dbind()
    End Sub

    Private Sub CustomValidator1_ServerValidate(ByVal source As System.Object, ByVal args As System.Web.UI.WebControls.ServerValidateEventArgs) Handles CustomValidator1.ServerValidate
        Dim name As String
        name = args.Value
        Dim connstr As String = "data source=(local);initial catalog=lwc;uid=sa;pwd=sa"
        Dim sconn As New SqlConnection
        sconn.ConnectionString = connstr
        sconn.Open()
        Dim scomm As New SqlCommand("select count(*) from 房源登记表 where 房源编号='" + name + "'", sconn)
        Dim s As Single
        s = scomm.ExecuteScalar()

        If s > 0 Then
            args.IsValid = False

        Else
            args.IsValid = True

        End If
    End Sub

    Private Sub LinkButton3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LinkButton3.Click
        If Me.IsValid Then
            Dim zfbh, ssqy, jq, fx, lc, zcs, cy, mj, zxqk, fwss, djrq As String
            zfbh = TextBox1.Text
            ssqy = TextBox2.Text
            jq = TextBox3.Text
            fx = TextBox4.Text
            lc = TextBox5.Text
            zcs = TextBox6.Text
            cy = TextBox7.Text
            mj = TextBox9.Text
            zxqk = TextBox9.Text
            fwss = TextBox10.Text
            djrq = TextBox11.Text
            Dim connstr1 As String = "data source=(local);initial catalog=lwc;uid=sa;pwd=sa"
            Dim sconn1 As New SqlConnection
            sconn1.ConnectionString = connstr1
            sconn1.Open()
            Dim scomm As New SqlCommand("insert into 房源登记表 values('" + zfbh + "','" + ssqy + "','" + jq + "','" + fx + "','" + lc + "','" + zcs + "','" + cy + "','" + mj + "','" + zxqk + "','" + fwss + "','" + djrq + "')", sconn1)
            scomm.ExecuteNonQuery()
            sconn1.Close()
            Me.dbind()
        End If
    End Sub
    Private Sub LinkButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LinkButton2.Click
        Response.Redirect("khxxb.aspx")
    End Sub
    Private Sub LinkButton4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LinkButton4.Click
        Response.Redirect("zhjm.aspx")

链接：https://pan.baidu.com/s/1Xwd0U_KktL0hRFPaggIOGQ?pwd=6688 
提取码：6688