同济数据库大作业.doc

房屋租售管理系统项目报告书同济大学数据库技术及应用实验报告实验报告题目：房屋租售管理系统 小组成员：）)） 班级： 数据库周五56节 年级： 08级 专业：交通工程 指导教师： 张红云 日期： 2010 年 06 月 14 日目录1、项目建设意义.32、项目特色.33、可行性分析.44、设计阶段说明.55、程序调试.76、程序代码227、心得体会92一、项目建设意义随着信息自动化处理技术的日益发展，如何科学，全面，高效的对单位纷繁复杂的档案管理事务进行计算机的自动化管理是个热门话题，当前企事业单位为了提升自身的竞争力，各单位都在定制和购买各项业务应用软件，运用高科技手段进行科学，规范的管理。作为房屋租赁公司的管理者，希望能够对房屋租赁事务管理的整个流程状态，信息资料的情况了如指掌，使其可以做出科学的决策。作为工作人员期望能够避免繁琐的手工操作，甩掉传统的手工记录方式，达到事半功倍的效果。一个能够使其实现管理系统化、规范化、自动化的计算机系统就显得很有必要。二、项目特色本项目使用了流行的MDI窗体，使得整个系统的界面简洁，功能的使用十分方便。在“基本信息”里，则使用了继承窗体技术，大大减少了代码，而且增强了代码的可读性和易于修改性。本项目还实现了高级搜索功能，提供了绝大部分搜索功能，并且可以随意组合，方便各式各样的信息查找，并提供查找出来的记录数。在“交易信息”的搜索中，还提供对某一个时间段交易量的信息查询。在系统信息上，本项目在最开始的欢迎界面就提供了系统名称，系统版本号等信息，在“系统信息”界面还提供系统功能等具体信息。最后，本项目具有对管理员信息进行增加，删除，修改密码的功能，方便对管理员进行实时管理。本项目还具有锁定系统的功能，解决管理员临时离开而不必关闭系统的问题。三、可行性分析通过之前对编程语言 c/c+的学习，加之数据库教学前期对 VB。Net 软件平台摸索和 ADO.Net学习，房屋销售管理的系统软件在前台的界面设计和数据库信息处理技术方面，是可以实现的。后台物理数据库的创建和管理知识，则更多的是来自数据库的课堂，通过一学期的学习，从现实世界到概念模式，从 ER 图到数据表，从垃圾表到三范式，从最基本的创建数据表 到创建存储过程、触发器，我们对数据库基本知识的学习已经完 成并趋于系统。选用 Sql Server 做物理数据库平台，VB.Net 做界面平台，ADO.Net 做交互平台，在这样的软件平台的支撑下， 我们的房屋销售管理系统软件的建立是确实可行的。四、概念设计说明1、E-R图楼龄家具装修朝向总层数现层性质户型面积小区名称地址房屋编号总价单价付款期限首付比例租赁时间移动电话固定电话工作单位婚姻状况姓名身份证年龄性别房屋交易用户由E-R图的关系模式：用户（身份证，姓名，性别，年龄，工作单位，固定电话，移动电话，婚姻状况）房屋（房屋编号，地址，小区名称，面积，户型，性质，现层，总层数，朝向，装修，楼龄，家具）交易（首付比例，付款日期，租赁时间）由此建表如下：用户信息表房屋信息表交易信息表已交易房屋表管理员表2、系统结构图根据结构图设计界面五、程序调试1、管理员登陆界面键入不正确用户名：弹出提示：“用户名不存在，请重新输入”键入正确用户名和不正确密码，弹出提示：“密码不正确，请重新输入”键入正确用户名和密码时，登陆成功，进入管理界面：2、用户信息维护初始界面：搜出其中一条记录：添加新用户信息：新用户添加成功，出现在列表中：高级搜索：搜索结果：3、房屋信息维护初始界面：删除房屋记录：删除成功后：房屋信息高级搜索：4、交易信息维护1）新增交易2）已交易房屋已销售房屋信息查看：已出租房屋信息查看：3）交易信息搜索4）删除交易5、系统管理1）管理员初始界面：添加管理员：添加成功后：删除管理员：删除后：更改密码：2）系统锁定3）系统信息六、程序代码1）触发器添加交易：CREATE TRIGGER tri_add ON dbo.交易信息表 FOR INSERT AS declare houseid char(15) select houseid=房屋编号 from inserted insert 已交易房屋表 select * from 房屋信息表 hi where hi.房屋编号=houseid delete 房屋信息表 from 房屋信息表 hi ,inserted i where hi.房屋编号=i.房屋编号删除交易：CREATE TRIGGER tri_delete ON dbo.交易信息表 FOR DELETE AS declare houseid char(15) select houseid=房屋编号 from deleted insert 房屋信息表 select * from 已交易房屋表 hi where hi.房屋编号=houseid delete 已交易房屋表 from 已交易房屋表 hi where hi.房屋编号=houseid更新：CREATE TRIGGER tri_update ON dbo.用户信息表 FOR UPDATE AS if update(姓名) begin update 交易信息表 set 客户姓名=i.姓名 from 交易信息表 br,deleted d,inserted i where br.客户姓名=d.姓名 end if update(身份证) begin update 交易信息表 set 身份证=i.身份证 from 交易信息表 hi ,deleted d,insert交易时间约束：ALTER TRIGGER tri_time ON dbo.交易信息表FOR INSERTAS declare _time as datetime select _time = i.交易时间from inserted i if convert(int,getdate() convert(int,_time) begin raiserror(交易时间不合法，请仔细核对日期！,16,1,error) rollback TRANend删除用户：CREATE TRIGGER tir_delete ON dbo.用户信息表 FOR DELETE AS declare 身份证 char(15) select 身份证=身份证 from deleted delete 交易信息表 from 交易信息表 hi where hi.身份证=身份证 Public Class LoginFrm Public bResult As Boolean Public UserName As String = Public UserPwd As String = Dim db As DataBase = New DataBase() Dim dv1 As DataView = New DataView() Dim dv2 As DataView = New DataView() Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click dv1 = db.RunSelectSQL(select 账号from 管理员表where 账号= _ & Me.UsernameTextBox.Text.Trim() + ) dv2 = db.RunSelectSQL(select 密码from 管理员表where 账号= _ & Me.UsernameTextBox.Text.Trim() + ) If dv1.Count = 0 Then Me.UsernameTextBox.Text = Me.PasswordTextBox.Text = MsgBox(用户名不存在，请重新输入, , 提示) UsernameTextBox.Focus() Else UserName = dv1.Item(0)(账号) UserPwd = dv2.Item(0)(密码) End If If dv1.Count 0 And Me.PasswordTextBox.Text Me.UserPwd.Trim() Then Me.PasswordTextBox.Text = MsgBox(密码不正确，请重新输入, , 提示) PasswordTextBox.Focus() End If If Me.UserName.Trim() = Me.UsernameTextBox.Text.Trim() And Me.UserPwd.Trim() = Me.PasswordTextBox.Text.Trim() And Me.UsernameTextBox.Text.Trim() Then Dim Frmmain As New FrmMain() Frmmain.Show() Me.Finalize() End If End Sub Private Sub Cancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Cancel.Click Me.Close() Application.Exit() End Sub Private Sub PasswordTextBox_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PasswordTextBox.TextChanged If UsernameTextBox.Text And PasswordTextBox.Text Then Me.OK.Enabled = True Else Me.OK.Enabled = False End If End Sub Private Sub UsernameTextBox_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UsernameTextBox.TextChanged If UsernameTextBox.Text And PasswordTextBox.Text Then Me.OK.Enabled = True Else Me.OK.Enabled = False End If End Sub Private Sub LoginFrm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load End SubEnd ClassPublic Class FrmUser Dim strUser As String = Dim con As New SqlClient.SqlConnection(DataBase.sConn) Public Overrides Sub Prepare() Me.Text = 用户信息维护 Dim db As DataBase = New DataBase db.Dispose() Me.Label1.Text = 身份证号 Me.Label2.Text = 姓名 End Sub Public Overrides Sub Research() Dim strFilter As String If txbOne.Text.Trim.Length = 0 Then If txbTwo.Text.Trim.Length = 0 Then strFilter = Else strFilter = where 姓名like strFilter += txbTwo.Text.Trim + % End If Else strFilter = where 身份证like strFilter += txbOne.Text.Trim + % If txbTwo.Text.Trim.Length 0 Then strFilter += and 姓名like strFilter += txbTwo.Text.Trim + % End If End If strUser = select * from 用户信息表 + strFilter End Sub Public Overrides Sub SetDataGrid() Research() con.Open() Dim ds As New DataSet Dim da As New SqlClient.SqlDataAdapter(strUser, con) da.Fill(ds) dgdList.DataSource = ds.Tables(0) dtData = ds.Tables(0) dgp = dgdList.CurrentRowIndex lblCount.Text = 记录数: + dtData.Rows.Count.ToString() con.Close() End Sub Public Overrides Sub SetTextBox() Dim i As Integer i = dgdList.CurrentRowIndex Me.UserNameTextBox.Text = dgdList.Item(i, 0) If dgdList.Item(i, 1) = 男 Then Me.UserSexComboBox.SelectedIndex = 0 ElseIf dgdList.Item(i, 1) = 女 Then Me.UserSexComboBox.SelectedIndex = 1 End If Me.UserAgeTextBox.Text = dgdList.Item(i, 2) Me.UserIDTextBox.Text = dgdList.Item(i, 3) Me.WorkSpaceTextBox.Text = dgdList.Item(i, 4) Me.HomeTelTextBox.Text = dgdList.Item(i, 5) Me.MobelTelTextBox.Text = dgdList.Item(i, 6) If dgdList.Item(i, 7) = 未婚 Then Me.MarrigeTextBox.SelectedIndex = 0 ElseIf dgdList.Item(i, 7) = 已婚 Then Me.MarrigeTextBox.SelectedIndex = 1 End If End Sub Public Overrides Sub SetTextBoxState(ByVal bState As Boolean) Me.GroupBox2.Enabled = Not bState End Sub Public Overrides Sub Clear() Me.UserIDTextBox.Text = Me.UserNameTextBox.Text = Me.UserAgeTextBox.Text = Me.HomeTelTextBox.Text = Me.UserNameTextBox.Text = Me.MobelTelTextBox.Text = Me.WorkSpaceTextBox.Text = Me.MarrigeTextBox.Text = Me.UserAgeTextBox.Text = End Sub Public Overrides Sub SaveForAdd() Try con.Open() Dim dc As New SqlClient.SqlCommand(insert into 用户信息表values( _ & Me.UserNameTextBox.Text.Trim() & , _ & Me.UserSexComboBox.Text.Trim() & , _ & Me.UserAgeTextBox.Text.Trim() & , _ & Me.UserIDTextBox.Text.Trim() & , _ & Me.WorkSpaceTextBox.Text.Trim() & , _ & Me.HomeTelTextBox.Text.Trim() & , _ & Me.MobelTelTextBox.Text.Trim() & , _ & Me.MarrigeTextBox.Text.Trim() & ), con) dc.ExecuteNonQuery() Catch ex As Exception MessageBox.Show(数据添加失败!) Finally con.Close() End Try SetDataGrid() SetTextBox() End Sub Public Overrides Sub DeleteData() Try con.Open() Dim row As Integer row = dgdList.CurrentRowIndex Dim UserID As String = dgdList.Item(row, 3) Dim dc As New SqlClient.SqlCommand(delete from 用户信息表where 身份证like _ & UserID.Trim() & , con) dc.ExecuteNonQuery() Catch ex As Exception MessageBox.Show(数据删除失败!) Finally con.Close() End Try SetDataGrid() SetTextBox() End Sub Public Overrides Sub SaveForUpdate() Try con.Open() Dim row As Integer row = dgdList.CurrentRowIndex Dim UserID As String = dgdList.Item(row, 3) Dim dc As New SqlClient.SqlCommand(update 用户信息表set 姓名= _ & Me.UserNameTextBox.Text.Trim() & ,性别= _ & Me.UserSexComboBox.Text.Trim() & ,年龄= _ & Me.UserAgeTextBox.Text.Trim() & ,身份证= _ & Me.UserIDTextBox.Text.Trim() & ,工作单位= _ & Me.WorkSpaceTextBox.Text.Trim() & ,固定电话= _ & Me.HomeTelTextBox.Text.Trim() & ,移动电话= _ & Me.MobelTelTextBox.Text.Trim() & ,婚姻状况= _ & Me.MarrigeTextBox.Text.Trim() & where 身份证like & UserID.Trim() & , con) dc.ExecuteNonQuery() Catch ex As Exception MessageBox.Show(数据修改失败!) Finally con.Close() End Try SetDataGrid() SetTextBox() End Sub Private Sub UserAgeTextBox_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UserAgeTextBox.TextChanged End SubEnd ClassPublic Class FrmBase Protected bmData As BindingManagerBase Protected dtData As DataTable bAdd = true 为新增 bAdd = false 为修改 Protected bAdd As Boolean 定义一个指针用来获得datagrid行索引 Protected dgp As Integer Private Sub frmBase_Load(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles MyBase.Load Prepare() SetDataGrid() SetTextBox() SetTextBoxState(True) SetToolBarState(True) End Sub Private Sub btnSearch_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnSearch.Click SearchResult() End Sub Private Sub dgdList_CurrentCellChanged(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles dgdList.CurrentCellChanged SetTextBox() End Sub Private Sub SetToolBarState(ByVal bState As Boolean) 设置Button是否能够单击 Me.tsbFirst.Enabled = bState Me.tsbPre.Enabled = bState Me.tsbNext.Enabled = bState Me.tsbLast.Enabled = bState Me.tsbAdd.Enabled = bState Me.tsbUpdate.Enabled = bState Me.tsbDel.Enabled = bState Me.tsbSave.Enabled = Not bState Me.tsbCancel.Enabled = Not bState End Sub Public Overridable Sub Prepare() End Sub 设置DataGrid控件 Public Overridable Sub SetDataGrid() End Sub 查询函数 Public Overridable Sub SearchResult() SetDataGrid() SetTextBox() End Sub Public Overridable Sub Research() End Sub 新增函数 Public Overridable Sub LoadData() End Sub Public Overridable Sub SaveForAdd() End Sub 更新函数 Public Overridable Sub SaveForUpdate() End Sub 删除函数 Public Overridable Sub DeleteData() End Sub 清除函数 Public Overridable Sub Clear() End Sub 设置TextBox函数 Public Overridable Sub SetTextBoxState(ByVal bState As Boolean) End Sub Public Overridable Sub SetData() End Sub Private Sub ToolStrip1_ItemClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ToolStripItemClickedEventArgs) Handles ToolStrip1.ItemClicked Select Case e.ClickedItem.ToolTipText Case 首记录 返回首记录 dgp = 0 Case 上一条 If dgp 0 Then dgp -= 1 End If Case 下一条 If dgp dtData.Rows.Count.ToString - 1 Then dgp += 1 End If Case 尾记录 返回尾记录 dgp = dtData.Rows.Count.ToString - 1 Case 新增 Me.dgdList.Enabled = False SetTextBoxState(False) SetToolBarState(False) bAdd = True Clear() Case 修改 Me.dgdList.Enabled = False SetTextBoxState(False) SetToolBarState(False) bAdd = False Case 删除 弹出询问对话框 Dim dlgResult As DialogResult = _ MessageBox.Show(你确认删除此数据？, _ 删除, MessageBoxButtons.OKCancel) If dlgResult = Windows.Forms.DialogResult.OK Then DeleteData() End If Case 保存 SetToolBarState(True) SetTextBoxState(True) If bAdd = True Then 为新增数据保存 SaveForAdd() Else 为更新数据保存 SaveForUpdate() End If Me.dgdList.Enabled = True Case 取消 SetTextBoxState(True) SetToolBarState(True) Me.dgdList.Enabled = True SetDataGrid() SetTextBox() Case 退出 关闭本窗体 Me.Close() End Select Me.dgdList.CurrentRowIndex = dgp End Sub Public Overridable Sub SetTextBox() End Sub Private Sub txbOne_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txbOne.TextChanged End SubEnd ClassPublic Class FrmHouse Dim strhouse As String = Dim con As New SqlClient.SqlConnection(DataBase.sConn) Public Overrides Sub Prepare() Me.Text = 房屋信息维护 Dim db As DataBase = New DataBase db.Dispose() Me.Label1.Text = 房屋编号 Me.Label2.Text = 小区名称 End Sub Public Overrides Sub Research() Dim strFilter As String If txbOne.Text.Trim.Length = 0 Then If txbTwo.Text.Trim.Length = 0 Then strFilter = Else strFilter = where 小区名称like strFilter += txbTwo.Text.Trim + % End If Else strFilter = where 房屋编号like strFilter += txbOne.Text.Trim + % If txbTwo.Text.Trim.Length 0 Then strFilter += and 小区名称like strFilter += txbTwo.Text.Trim + % End If End If strhouse = select * from 房屋信息表 + strFilter End Sub Public Overrides Sub SetDataGrid() Research() con.Open() Dim ds As New DataSet Dim da As New SqlClient.SqlDataAdapter(strhouse, con) da.Fill(ds) dgdList.DataSource = ds.Tables(0) dtData = ds.Tables(0) dgp = dgdList.CurrentRowIndex lblCount.Text = 记录数： + dtData.Rows.Count.ToString() con.Close() End Sub Public Overrides Sub SetTextBox() Dim i As Integer i = dgdList.CurrentRowIndex Me.HouseIdTextBox.Text = dgdList.Item(i, 0) Me.HouseAddTextBox.Text = dgdList.Item(i, 1) Me.CommunityTextBox.Text = dgdList.Item(i, 2) Me.SquareTextBox.Text = dgdList.Item(i, 3) Dim HousePlan As String = dgdList.Item(i, 4) If HousePlan = 一室一厅 Then Me.PlanComboBox.SelectedIndex = 0 ElseIf HousePlan = 两室一厅 Then Me.PlanComboBox.SelectedIndex = 1 ElseIf HousePlan = 两室两厅一卫 Then Me.PlanComboBox.SelectedIndex = 2 ElseIf HousePlan = 两室两厅两卫 Then Me.PlanComboBox.SelectedIndex = 3 ElseIf HousePlan = 三室两厅一卫