数据库小型餐饮管理系统分析解析

苏州科技学院电子与信息工程学院《数据库原理课程设计》报告学号1430107130姓名郑宏艳班级1421日期2015年12月一、任务及要求：小型餐饮管理系统主要功能：餐饮消费查询、餐饮消费更新、餐饮消费统计、餐饮消费会员管理二、数据库设计：(要求叙述数据库设计的过程，画出E-R图，列出数据库中的表名称及结构，并说明表间的关系)（１）Ｅ—Ｒ图：（2）实体模型：我的报告总共两个表，第一个表是会员表（会员id，会员密码，会员姓名，会员电话，收款金额，会员性别等），第二个表是菜单管理表（菜单名称，菜单价格，食物照片，配料，功效）ruser（会员表）food (食谱表)三、实现的功能及相应的界面和代码:1.连接数据库的实现（1）首先实现数据库的连接，代码如下所示：SqlConnection conn = new SqlConnection();conn.ConnectionString = "data source = RONGER-PC\\SQLEXPRESS;initial Catalog =小型餐馆;user id =123;password =123";conn.Open();conn.Close();conn.Dispose();（2）链接数据库，后台取的数据库菜单列表的所有数据，在页面前台进行展示视图展现：代码的实现：protected void Button_Click(object sender, EventArgs e){SqlConnection conn = new SqlConnection();conn.ConnectionString = "data source =W530\\SQLEXPRESS;initial Catalog=小型餐馆;user id =123;password = 123 ";conn.Open();TextBox9.Text = "连接成功！";string yy =”select * from Menu”SqlCommand sqlCmd1 = new SqlCommand(yy, conn);sqlCmd1.ExecuteNonQuery();conn.Close();conn.Dispose();}2.用户登录界面以及用户信息添加删除修改界面（1）代码的实现Imports System.DataImports System.Data.SqlClientPublic Class Form2Private Sub Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.ClickMe.Close()End SubPrivate Sub Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.ClickIf TextBox3.Text.Trim = "" Or TextBox4.Text.Trim = "" ThenMsgBox("请填写用户名和密码", MsgBoxStyle.Exclamation)Exit SubEnd If'connectSQL()Dim conn As SqlConnection = New SqlConnection("Server=(local);uid=sa;pwd=sa;Database=D:\新建文件夹\EATFOODS.MDF")conn.Open()Dim str As Stringstr = "select * from ruser where user_id ='" + TextBox3.Text + "' and passcode= '" + TextBox4.Text + "' and style= '" + ComboBox2.Text + "'"Dim scom = New SqlClient.SqlCommand(str, conn)mandType = CommandType.TextDim sexe = scom.ExecuteReader()If (sexe.Read()) ThenMe.Visible = FalseMessageBox.Show("登陆成功")ElseMessageBox.Show("用户名或密码不正确！", "警告")End IfMe.Close()conn.Close()End SubEnd Class(2)代码的实现Private Sub Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.ClickIf (Me.TextBox10.Text <> "" And Me.TextBox11.Text <> "" And Me.TextBox12.Text <> "" And Me.TextBox13.Text <> "") ThenDim conn As SqlConnection = New SqlConnection("Server=(local);uid=sa;pwd=sa;Database=D:\新建文件夹\EATFOODS.MDF")conn.Open()Dim mysql As Stringmysql = "insert into rcustomer(name,call,passcode,id,addtime,userid) values('"mysql += Me.TextBox10.Text.ToString()mysql += "','"mysql += Me.TextBox11.Text.ToString()mysql += "','"mysql += Me.TextBox12.Text.ToString()mysql += "','"mysql += Me.TextBox1.Text.ToString()mysql += "','"mysql += Me.TextBox2.Text.ToString()mysql += "','"mysql += Me.TextBox3.Text.ToString()mysql += "')"Dim sql As SqlCommandsql = New SqlCommand(mysql, conn)If Me.TextBox12.Text.ToString <> Me.TextBox13.Text.ToString ThenMessageBox.Show("密码不一致,重新输入", "提示")ReturnEnd IfIf (sql.ExecuteNonQuery() > 0) ThenMessageBox.Show("添加成功", "提示")End Ifconn.Close()ElseMessageBox.Show("请核对信息是否完整！", "提示")End IfEnd SubPrivate Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.ClickDim conn As SqlConnection = New SqlConnection("Server=(local);uid=sa;pwd=sa;Database=D:\新建文件夹\EATFOODS.MDF")conn.Open()Dim mysql As Stringmysql = "delete from ruser where name='"mysql += Me.TextBox6.Textmysql += "'"Dim sql As SqlCommandsql = New SqlCommand(mysql, conn)If (sql.ExecuteNonQuery() > 0) ThenMessageBox.Show("删除成功", "提示")End Ifconn.Close()End SubPrivate Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.ClickIf (Me.TextBox1.Text <> "" And Me.TextBox2.Text <> "" And Me.TextBox3.Text <> "" And Me.TextBox4.Text <> "") ThenDim conn As SqlConnection = New SqlConnection("Server=(local);uid=sa;pwd=sa;Database=D:\新建文件夹\EATFOODS.MDF")conn.Open()Dim mysql As Stringmysql = "update food set name=' "mysql += Me.TextBox1.Textmysql += "',price='"mysql += Me.TextBox2.Textmysql += "',formula='"mysql += Me.TextBox3.Textmysql += "',effect='"mysql += Me.TextBox4.Textmysql += "'"Dim sql As SqlCommandsql = New SqlCommand(mysql, conn)If (sql.ExecuteNonQuery() > 0) ThenMessageBox.Show("修改成功！", "提示")conn.Close()ElseTryCatch ex As ExceptionMessageBox.Show(ex.Message)End TryEnd IfElseMessageBox.Show("信息未填完整", "提示")End IfEnd Sub3.从数据库用户菜单表取的用户点餐信息，可以对点的菜进行更新和修改，并且有统计功能（1）用户已点菜单更新代码protected void Button_Click(object sender, EventArgs e){SqlConnection conn = new SqlConnection();conn.ConnectionString = "data source =W530\\SQLEXPRESS;initial Catalog=小型餐馆;user id =123;password =123 ";conn.Open();TextBox9.Text = "更新成功！";string yy = "update UserMenu set 菜单名称=('" + TextBox2.Text + "') where 会员用户=('" + TextBox1.Text + "')";SqlCommand sqlCmd1 = new SqlCommand(yy, conn);sqlCmd1.ExecuteNonQuery();conn.Close();conn.Dispose();}（2）用户已点菜单删除代码Private Sub Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.ClickDim conn As SqlConnection = New SqlConnection("Server=(local);uid=sa;pwd=sa;Database=D:\新建文件夹\EA TFOODS.MDF")conn.Open()Dim mysql As Stringmysql = "delete from ruser where name='"mysql += Me.TextBox6.Textmysql += "'"Dim sql As SqlCommandsql = New SqlCommand(mysql, conn)If (sql.ExecuteNonQuery() > 0) ThenMessageBox.Show("删除成功")End Ifconn.Close()End Sub4. 新菜单添加功能连接数据库添加新的菜单（1）添加菜单的代码Private Sub Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.ClickDim conn As SqlConnection = New SqlConnection("Server=(local);uid=sa;pwd=sa;Database=D:\新建文件夹\EATFOODS.MDF")conn.Open()Dim str As Stringstr = DataGridView3.Rows(DataGridView3.CurrentRow.Index).Cells(0).ValueDim mysql As Stringmysql = "insert into menu（menu_dishes，menu_price，menu_zhonglei）values（dishes，price，zhonglei）”mysql += strDim sql As SqlCommandsql = New SqlCommand(mysql, conn)If (sql.ExecuteNonQuery() > 0) ThenMessageBox.Show("添加成功", )End IfDataGridView3.Rows.RemoveAt(DataGridView3.CurrentRow.Index)conn.Close()End Sub四、调试分析：（包括遇到的问题及解决办法，经验与体会等）经过一年半的学习，我们对数据库、C#等相关知识有了一定的了解。