第三部分 数据库应用系统案例

第三部分 数据库应用系统案例 第1章 商品信息管理系统 USE Goods IF EXISTS（SELECT name FROM sysobjects WHERE name='proc_Prd' AND type='p'） DROP PROCEDURE proc_Prd Go CREATE PROCEDURE proc_Prd @ProductsNo int AS INSERT UPDATE_Log VALUES（@ProductsNo，DEFAULT） GO

USE Goods IF EXISTS（SELECT name FROM sysobjects WHERE name='tr_UpPrd' AND type='TR'） DROP PROCEDURE tr_UpPrd Go CREATE TRIGGER tr_UpPrd ON Products FOR DELETE，UPDATE AS DECLARE @ProductsNo int SELECT @ProductsNo = 编码 FROM deleted EXEC proc_Prd @ProductsNo GO

Public gConnStr As String '连接字符串变量 Public txtSQL As String '存放SQL语句 Public DBSet As DataSet '查询得到的记录集 Public ErrorMsg As String '存放错误信息 Public intAuthority As Integer '权限值 Public currentUser As String '当前登录的用户

Public Sub main() Dim mf As New frmLogin gConnStr = "Data Source=202.197.77.2;Initial Catalog=Goods;User ID=xyj;Password=xyj; " mf.ShowDialog() End Sub

Public Function ExecSQL (ByVal strSQL As String, ByRef errMsg As String) As Integer '函数执行SQL的INSERT、DELETE、UPDATE和SELECT语句 '对于INSERT、DELETE、UPDATE语句，ExecuteSQL返回更新的记录数：-1表示程序异常；表示更新失败；大于表示操作成功，更新的记录数 '对于SELECT语句：DBSet为返回的数据集；ExecuteSQL为返回的查询记录数。 Dim cnn As SqlClient.SqlConnection Dim cmd As New SqlClient.SqlCommand() Dim adpt As SqlClient.SqlDataAdapter Dim rst As New DataSet() Dim SplitSQL() As String errMsg = "" Try SplitSQL = Split(strSQL) cnn = New SqlClient.SqlConnection(gConnStr) If InStr("INSERT,DELETE,UPDATE", UCase$(SplitSQL(0))) Then cmd.Connection = cnn cmd.Connection.Open() cmd.CommandText = strSQL ExecuteSQL = cmd.ExecuteNonQuery() '返回更新数据记录条数 Else adpt = New SqlClient.SqlDataAdapter(strSQL, cnn) adpt.Fill(rst) ExecuteSQL = rst.Tables(0).Rows.Count '返回查询记录条数 DBSet = rst End If Catch ex As Exception errMsg = ex.Message ExecuteSQL = -1 '表示执行SQL失败 Finally rst = Nothing cnn = Nothing End Try End Function

Private Sub mnuInsert_Click() '判断权限 If intAuthority<5 Then MsgBox "对本起，你没有该权限！"，vbExclamation Exit Sub End If Dim frmchild As New frmInsert '生成子窗体 frmchild.MdiParent = Me '指定子窗体的父窗体 frmchild.Show() '显示子窗体 End Sub

'定义全局变量currentUser、intAuthority，用于进入系统后的模块权限验证 Public intAuthority As Integer '权限值 Public currentUser As String '当前登录的用户 '当单击“取消”按钮时，执行cmdCancel_Click（）事件 Private Sub cmdCancel_Click（） Me.Close() End Sub ' 当单击“确认”按钮时，执行cmdOk_Click（）事件 Private Sub cmdOk_Click（） If TextBox1.Text = "" Then MsgBox("请输入用户名！") TextBox1.Focus() Exit Sub End If If TextBox2.Text = "" Then MsgBox("请输入密码！") TextBox2.Focus() Exit Sub End If Dim icount As Integer txtSQL = "SELECT * FROM Users WHERE 用户名='" & TextBox1.Text & "'" icount = ExecSQL(txtSQL, ErrorMsg) '从Users表中提取输入的用户信息,返回记录数icount和数据集DBSet If icount = 0 Then MsgBox("没有此用户，请重新输入用户名！", vbExclamation) TextBox1.Focus() Exit Sub ElseIf icount = -1 Then MsgBox("查询出错！", vbExclamation) TextBox1.Focus() Exit Sub End If If Trim(TextBox2.Text) = Trim(DBSet.Tables.Item(0).Rows.Item(0).Item("密码").ToString()) Then intAuthority = DBSet.Tables.Item(0).Rows.Item(0).Item("权限等级") currentUser = DBSet.Tables.Item(0).Rows.Item(0).Item("用户名") frmMain.Show() '显示主窗体 Finalize() '释放登录窗体的资源 Else MsgBox("密码不正确，请重新输入密码！", vbExclamation) TextBox2.Focus() End If End Sub

Private Sub frmUser_Load() ComboBoxAuthority.Items.Clear() ComboBoxAuthority.Items.Add("一般用户") ComboBoxAuthority.Items.Add("数据修改者") ComboBoxAuthority.Items.Add("管理人员") ComboBoxAuthority.Items.Add("数据录入员") ComboBoxAuthority.Items.Add("系统管理员") ComboBoxAuthority.SelectedIndex = 0 '在窗体的数据网络中显示预采数据 txtSQL = "select * FROM Users" ExecSQL(txtSQL, ErrorMsg) '返回值为SQL检索记录数 DataGridView1.DataSource = DBSet.Tables.Item(0) '在DataGridView控件中显示查询结果 '判断用户的权限，如果是系统管理员则可以添加用户 If intAuthority < 8 Then cmdAdd.Enabled = False '禁用“添加”用户按钮 cmdDelete.Enabled = False '禁用“删除”用户按钮 ComboBoxAuthority.Enabled = False '禁用权限 TextBoxUserName.Text = currentUser TextBoxUserName.Enabled = False '禁止输入用户名称 End If End Sub

Private Sub cmdAdd_Click() Dim intAuth As Integer '定义权限变量 '检查输入框信息 If TextBoxUserName.Text = "" Then MsgBox("请输入用户名！") TextBoxUserName.Focus() Exit Sub End If If TextBoxPassword1.Text = "" Then MsgBox("请输入密码！") TextBoxPassword1.Focus() Exit Sub End If If TextBoxPassword2.Text = "" Then MsgBox("请输入确认密码！") TextBoxPassword2.Focus() Exit Sub End If If TextBoxPassword2.Text <> TextBoxPassword1.Text Then MsgBox("确认密码不正确！") TextBoxPassword2.Focus() Exit Sub End If '初始化权限值