Command对象对用户信息表进行操作.doc
数据库应用技术 第五讲Command对象第五讲 Command对象四、案例（在小型超市管理系统中的实现）l SqlCommand .CommandType= CommandType.Text（SQL命令）的例子功能描述：实现对表tbYHXXB（用户信息表【由用户ID、用户名、密码、用户类型4个字段构成】，如下图）的添加、修改和删除，没有返回结果，操作结果看表的内容变化代码参看附录1l SqlCommand .CommandType= CommandType.StoredProcedure（存储过程）的例子用到SqlParameter对象功能描述：实现对表tbYHXXB（用户信息表）的添加、修改和删除，没有返回结果，操作结果看表的内容变化代码参看附录2l SqlCommand .CommandType= CommandType. TableDirect（表）的例子功能描述：打开表tbYHXXB（用户信息表），用ExcuteReader方法实现。代码参看附录3五、上机实践仿照【案例】上机练习，验证。运行界面如下图【附录1】提示：MySQL为前面创建的动态链接库ClassLibOfSuperMarket.dllprivate void button1_Click(object sender, EventArgs e) /用SQL命令添加一个用户:“10001,张三，收银员” MySQL getConn=new MySQL(); SqlConnection theConn = null; try theConn = getConn.GetConnetion(); theConn.Open(); SqlCommand sqlCmd = new SqlCommand(); sqlCmd.Connection = theConn; sqlCmd.CommandType = CommandType.Text; sqlCmd.CommandText = "Insert Into tbYHXXB Values('10001','张三','','收银员')" sqlCmd.ExecuteNonQuery(); catch (SqlException sqlEx) MessageBox.Show(sqlEx.Message, "SQL错误", MessageBoxButtons.OK, MessageBoxIcon.Error); catch(Exception Ex) MessageBox.Show(Ex.Message, "SQL错误", MessageBoxButtons.OK, MessageBoxIcon.Error); finally if (theConn.State = System.Data.ConnectionState.Open) theConn.Close(); MessageBox.Show("记录添加成功！请查看数据库中的表tbYHXXB", "添加记录", MessageBoxButtons.OK, MessageBoxIcon.Information); private void button2_Click(object sender, EventArgs e) /用SQL命令修改用户参数:将“10001,张三，收银员”改为 /“10001,我的名字，经理” MySQL getConn = new MySQL(); SqlConnection theConn = null; try theConn = getConn.GetConnetion(); theConn.Open(); SqlCommand sqlCmd = new SqlCommand(); sqlCmd.Connection = theConn; sqlCmd.CommandType = CommandType.Text; sqlCmd.CommandText = "Update tbYHXXB Set 用户名='我的名字',密码='',用户类型='经理'" sqlCmd.ExecuteNonQuery(); catch (SqlException sqlEx) MessageBox.Show(sqlEx.Message, "SQL错误", MessageBoxButtons.OK, MessageBoxIcon.Error); catch (Exception Ex) MessageBox.Show(Ex.Message, "SQL错误", MessageBoxButtons.OK, MessageBoxIcon.Error); finally if (theConn.State = System.Data.ConnectionState.Open) theConn.Close(); MessageBox.Show("记录修改成功！请查看数据库中的表tbYHXXB", "修改记录", MessageBoxButtons.OK, MessageBoxIcon.Information); private void button3_Click(object sender, EventArgs e) /用SQL命令删除一个用户:“10001,张三，收银员” MySQL getConn = new MySQL(); SqlConnection theConn = null; try theConn = getConn.GetConnetion(); theConn.Open(); SqlCommand sqlCmd = new SqlCommand(); sqlCmd.Connection = theConn; sqlCmd.CommandType = CommandType.Text; sqlCmd.CommandText = "Delete from tbYHXXB where 用户ID='10001'" sqlCmd.ExecuteNonQuery(); catch (SqlException sqlEx) MessageBox.Show(sqlEx.Message, "SQL错误", MessageBoxButtons.OK, MessageBoxIcon.Error); catch (Exception Ex) MessageBox.Show(Ex.Message, "SQL错误", MessageBoxButtons.OK, MessageBoxIcon.Error); finally if (theConn.State = System.Data.ConnectionState.Open) theConn.Close(); MessageBox.Show("记录删除成功！请查看数据库中的表tbYHXXB", "删除记录", MessageBoxButtons.OK, MessageBoxIcon.Information); 【附录2】：private void button4_Click(object sender, EventArgs e) /调用存储过程AddUser添加一个用户:“10001,张三，收银员” MySQL getConn = new MySQL(); SqlConnection theConn = null; try theConn = getConn.GetConnetion(); theConn.Open(); SqlCommand sqlCmd = new SqlCommand(); sqlCmd.Connection = theConn; sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.CommandText = "AddUser" SqlParameter UserID = new SqlParameter(); UserID.ParameterName = "UserID" UserID.Value = "10001" UserID.DbType = DbType.String; UserID.Direction = ParameterDirection.Input; SqlParameter UserName = new SqlParameter(); UserName.ParameterName = "UserName" UserName.Value = "张三" UserName.DbType = DbType.String; UserName.Direction = ParameterDirection.Input; SqlParameter Pwd= new SqlParameter(); Pwd.ParameterName = "Pwd" Pwd.Value = "" Pwd.DbType = DbType.String; Pwd.Direction = ParameterDirection.Input; SqlParameter UserType = new SqlParameter(); UserType.ParameterName = "Type" UserType.Value = "收银员" UserType.DbType = DbType.String; UserType.Direction = ParameterDirection.Input; sqlCmd.Parameters.Add(UserID); sqlCmd.Parameters.Add(UserName); sqlCmd.Parameters.Add(Pwd); sqlCmd.Parameters.Add(UserType); sqlCmd.ExecuteNonQuery(); catch (SqlException sqlEx) MessageBox.Show(sqlEx.Message, "SQL错误", MessageBoxButtons.OK, MessageBoxIcon.Error); catch (Exception Ex) MessageBox.Show(Ex.Message, "SQL错误", MessageBoxButtons.OK, MessageBoxIcon.Error); finally if (theConn.State = System.Data.ConnectionState.Open) theConn.Close(); MessageBox.Show("记录添加成功！请查看数据库中的表tbYHXXB", "添加记录", MessageBoxButtons.OK, MessageBoxIcon.Information); private void button5_Click(object sender, EventArgs e) /调用存储过程ModiUser修改用户:“12345,玉溪，经理” MySQL getConn = new MySQL(); SqlConnection theConn = null; try theConn = getConn.GetConnetion(); theConn.Open(); SqlCommand sqlCmd = new SqlCommand(); sqlCmd.Connection = theConn; sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.CommandText = "ModiUser" SqlParameter UserID = new SqlParameter(); UserID.ParameterName = "UserID" UserID.Value = "12345" UserID.DbType = DbType.String; UserID.Direction = ParameterDirection.Input; SqlParameter UserName = new SqlParameter(); UserName.ParameterName = "UserName" UserName.Value = "玉溪" UserName.DbType = DbType.String; UserName.Direction = ParameterDirection.Input; SqlParameter Pwd = new SqlParameter(); Pwd.ParameterName = "Pwd" Pwd.Value = "" Pwd.DbType = DbType.String; Pwd.Direction = ParameterDirection.Input; SqlParameter UserType = new SqlParameter(); UserType.ParameterName = "Type" UserType.Value = "经理" UserType.DbType = DbType.String; UserType.Direction = ParameterDirection.Input; sqlCmd.Parameters.Add(UserID); sqlCmd.Parameters.Add(UserName); sqlCmd.Parameters.Add(Pwd); sqlCmd.Parameters.Add(UserType); sqlCmd.ExecuteNonQuery(); catch (SqlException sqlEx) MessageBox.Show(sqlEx.Message, "SQL错误", MessageBoxButtons.OK, MessageBoxIcon.Error); catch (Exception Ex) MessageBox.Show(Ex.Message, "SQL错误", MessageBoxButtons.OK, MessageBoxIcon.Error); finally if (theConn.State = System.Data.ConnectionState.Open) theConn.Close(); MessageBox.Show("记录修改成功！请查看数据库中的表tbYHXXB", "添加记录", MessageBoxButtons.OK, MessageBoxIcon.Information); private void button6_Click(object sender, EventArgs e) /调用存储过程DeliUser删除用户:“12345,玉溪，经理” MySQL getConn = new MySQL(); SqlConnection theConn = null; try theConn = getConn.GetConnetion(); theConn.Open(); SqlCommand sqlCmd = new SqlCommand(); sqlCmd.Connection = theConn; sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.CommandText = "DelUser" SqlParameter UserID = new SqlParameter(); UserID.ParameterName = "UserID" UserID.Value = "" UserID.DbType = DbType.String; UserID.Direction = ParameterDirection.Input; sqlCmd.Parameters.Add(UserID); sqlCmd.ExecuteNonQuery(); catch (SqlException sqlEx) MessageBox.Show(sqlEx.Message, "SQL错误", MessageBoxButtons.OK, MessageBoxIcon.Error); catch (Exception Ex) MessageBox.Show(Ex.Message, "SQL错误", MessageBoxButtons.OK, MessageBoxIcon.Error); finally if (theConn.State = System.Data.ConnectionState.Open) theConn.Close(); MessageBox.Show("记录删除成功！请查看数据库中的表tbYHXXB", "添加记录", MessageBoxButtons.OK, MessageBoxIcon.Information); 【附录3】 private void button7_Click(object sender, EventArgs e) /调用存储过程DeliUser删除用户:“12345,玉溪，经理” /运行出错，只支持OleDbConnection MySQL getConn = new MySQL(); SqlConnection theConn = null; try SqlCommand sqlCmd = new SqlCommand(); sqlCmd.Connection = theConn; sqlCmd.CommandText = "tbYHXXB" sqlCmd.CommandType = CommandType.TableDirect; /SqlDataReader sqlDa = new SqlDataReader(); sqlCmd.ExecuteReader(); catch (SqlException sqlEx) MessageBox.Show(sqlEx.Message, "SQL错误", MessageBoxButtons.OK, MessageBoxIcon.Error); catch (Exception Ex) MessageBox.Show(Ex.Message, "SQL错误", MessageBoxButtons.OK, MessageBoxIcon.Error); finally if (theConn.State = System.Data.ConnectionState.Open) theConn.Close(); MessageBox.Show("直接打开表成功！请查看数据库中的表tbYHXXB", "添加记录", MessageBoxButtons.OK, MessageBoxIcon.Information); 从实施课程改革以来，我反复学习有关的教育教学理论，深刻领会新课标精神，认真反思自身教学实际，研究学生，探究教法第 9 页 共 9 页
