数据库小型餐饮管理系统

1、苏州科技学院电子与信息工程学院数据库原理课程设计报告学号 1430107130 姓名 郑宏艳 班级 1421 日期 2015年12月 一、 任务及要求： 小型餐饮管理系统主要功能：餐饮消费查询、餐饮消费更新、餐饮消费统计、餐饮消费会员管理二、 数据库设计：(要求叙述数据库设计的过程，画出E-R图，列出数据库中的表名称及结构，并说明表间的关系)（） 图： （2）实体模型：我的报告总共两个表，第一个表是会员表（会员id，会员密码，会员姓名，会员电话，收款金额，会员性别等），第二个表是菜单管理表（菜单名称，菜单价格，食物照片，配料，功效） ruser（会员表）序号字段名称字段描述字段类型长度备注1U

2、ser_id会员账号Nvarchar r20不允许空2Passcode会员密码Nvarchar20不允许空3Name会员姓名Nvarchar20不允许空4Phone会员电话Nvarchar20不允许空5Money收款金额Money10不允许空6Sex会员性别Nvarchar4允许空food (食谱表)序号字段名称字段描述字段类型长度备注1Name食谱名称Nvarchar20不允许空2Price价格Nvarchar10不允许空3Photo食物照片Nvarchar100允许空4Formula配料Nvarchar50不允许空5Effect功效Nvarchar50不允许空三、实现的功能及相应的界面和代

3、码:1.连接数据库的实现（1）首先实现数据库的连接，代码如下所示： SqlConnection conn = new SqlConnection(); conn.ConnectionString = "data source = RONGER-PCSQLEXPRESS;initial Catalog =小型餐馆;user id =123;password =123" conn.Open(); conn.Close(); conn.Dispose();（2）链接数据库，后台取的数据库菜单列表的所有数据，在页面前台进行展示视图展现：代码的实现： protected void B

4、utton_Click(object sender, EventArgs e) SqlConnection conn = new SqlConnection(); conn.ConnectionString = "data source =W530SQLEXPRESS;initial Catalog=小型餐馆;user id =123;password = 123 " conn.Open(); TextBox9.Text = "连接成功！" string yy =”select * from Menu” SqlCommand sqlCmd1 = new

5、SqlCommand(yy, conn); sqlCmd1.ExecuteNonQuery(); conn.Close(); conn.Dispose(); 2. 用户登录界面以及用户信息添加删除修改界面（1） 代码的实现Imports System.DataPublic Class Form2 Private Sub Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click Me.Close() End Sub Private Sub Button_Click(

6、ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click If TextBox3.Text.Trim = "" Or TextBox4.Text.Trim = "" Then MsgBox("请填写用户名和密码", MsgBoxStyle.Exclamation) Exit Sub End If 'connectSQL() Dim conn As SqlConnection = New SqlConnection(&quo

7、t;Server=(local);uid=sa;pwd=sa;Database=D:新建文 件夹EATFOODS.MDF") conn.Open() Dim str As String str = "select * from ruser where user_id ='" + TextBox3.Text + "' and passcode= '" + TextBox4.Text + "' and style= '" + ComboBox2.Text + "'&quo

8、t; Dim scom = New SqlClient.SqlCommand(str, conn) scom.CommandType = CommandType.Text Dim sexe = scom.ExecuteReader() If (sexe.Read() Then Me.Visible = False MessageBox.Show("登陆成功") Else MessageBox.Show("用户名或密码不正确！", "警告") End If Me.Close() conn.Close() End SubEnd Class

9、 (2) 代码的实现 Private Sub Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click If (Me.TextBox10.Text <> "" And Me.TextBox11.Text <> "" And Me.TextBox12.Text <> "" And Me.TextBox13.Text <> "") Then

10、Dim conn As SqlConnection = New SqlConnection("Server=(local);uid=sa;pwd=sa;Database=D:新建文件夹EATFOODS.MDF") conn.Open() Dim mysql As String mysql = "insert into rcustomer(name,call,passcode,id,addtime,userid) values('" mysql += Me.TextBox10.Text.ToString() mysql += "'

11、,'" 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

12、 += Me.TextBox3.Text.ToString() mysql += "')" Dim sql As SqlCommand sql = New SqlCommand(mysql, conn) If Me.TextBox12.Text.ToString <> Me.TextBox13.Text.ToString Then MessageBox.Show("密码不一致,重新输入", "提示") Return End If If (sql.ExecuteNonQuery() > 0) Then Mess

13、ageBox.Show("添加成功", "提示") End If conn.Close() Else MessageBox.Show("请核对信息是否完整！", "提示") End IfEnd Sub Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click Dim conn As SqlConnection = New SqlConnection("

14、;Server=(local);uid=sa;pwd=sa;Database=D:新建文件夹EATFOODS.MDF") conn.Open() Dim mysql As String mysql = "delete from ruser where name='" mysql += "'" Dim sql As SqlCommand sql = New SqlCommand(mysql, conn) If (sql.ExecuteNonQuery() > 0) Then MessageBox.Show("删除成

15、功", "提示") End If conn.Close() End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click If (Me.TextBox1.Text <> "" And Me.TextBox2.Text <> "" And Me.TextBox3.Text <> "" And Me.Tex

16、tBox4.Text <> "") Then Dim conn As SqlConnection = New SqlConnection("Server=(local);uid=sa;pwd=sa;Database=D:新建文件夹EATFOODS.MDF") conn.Open() Dim mysql As String mysql = "update food set name=' " mysql += "',price='" mysql += "',form

17、ula='" mysql += "',effect='" mysql += "'" Dim sql As SqlCommand sql = New SqlCommand(mysql, conn) If (sql.ExecuteNonQuery() > 0) Then MessageBox.Show("修改成功！", "提示") conn.Close() Else Try Catch ex As Exception MessageBox.Show(ex.Message)

18、 End Try End If Else MessageBox.Show("信息未填完整", "提示") End If End Sub3.从数据库用户菜单表取的用户点餐信息，可以对点的菜进行更新和修改，并且有统计功能 （1）用户已点菜单更新代码 protected void Button_Click(object sender, EventArgs e) SqlConnection conn = new SqlConnection(); conn.ConnectionString = "data source =W530SQLEXPRESS;i

19、nitial 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); sqlC

20、md1.ExecuteNonQuery(); conn.Close(); conn.Dispose();（2）用户已点菜单删除代码 Private Sub Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click Dim conn As SqlConnection = New SqlConnection("Server=(local);uid=sa;pwd=sa;Database=D:新建文件夹EATFOODS.MDF") conn.Open(

21、) Dim mysql As String mysql = "delete from ruser where name='" mysql += "'" Dim sql As SqlCommand sql = New SqlCommand(mysql, conn) If (sql.ExecuteNonQuery() > 0) Then MessageBox.Show("删除成功") End If conn.Close() End Sub4. 新菜单添加功能连接数据库添加新的菜单 （1）添加菜单的代码Private

22、Sub Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Dim conn As SqlConnection = New SqlConnection("Server=(local);uid=sa;pwd=sa;Database=D:新建文件夹EATFOODS.MDF") conn.Open() Dim str As String str = DataGridView3.Rows(DataGridView3.CurrentRow.Index).Cells(0).Value Dim mysql As String mysql = "insert into menu（menu_dishes，menu_price，menu_zhonglei）values（dishes，price， zhonglei） ” mysql += str Dim sql As SqlCommand