1、command应用示例【示例1】本例说明如何通过Command对象执行INSERT语句向数据库中添加新记录,运行的效果图1所示。图1 使用INSERT语句添加记录示例运行效果图操作步骤如下:步骤1:在Visual Studio.Net中新建网站,选择“ASP.NET 网站”,然后在应用程序根目录下创建一个文件夹并命名为command_insert_exp。步骤2:将文档标题设置为“Command对象执行INSERT语句向数据库中添加新记录示例”。步骤3:插入一个表格,然后输入提示文本。步骤4:添加Web服务器控件到窗体上,并设置属性,如表1所示。表1 设置示例的控件属性控件类型属性设置说明Te
2、xtboxID=txtStudentNo用于要输入的学号TextboxID=txtStudentName用于要输入的姓名RadioButtonID=optSex1, GroupName=sexgroup用于选择性别RadioButtonID=optSex2, GroupName=sexgroup用于选择性别TextboxID=txtBirthday用于要输入的生日ButtonID=btnSave,Text=保存单击它时执行保存LabelID=lblMessage用于显示消息Web窗体设计效果如图2 所示。图2 Web窗体设计效果步骤4:打开代码隐藏类文件Default.aspx.cs,然后编写
3、代码。按钮btnSave的Click事件处理程序,代码如下。 protected void btnQuery _Click (object sender, EventArgs e) lblMessage.Text = ; if (txtStudentNo.Text =) lblMessage.Text = alert(请输入学号); return; if (txtStudentName.Text = ) lblMessage.Text = alert(请输入姓名); return; /新建连接对象 conn OleDbConnection conn = new OleDbConnection(
4、); string connstr = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + Server.MapPath(App_Data/student.mdb); conn.ConnectionString = connstr; /构建Command对象 cmd OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; cmd.CommandText = SELECT * FROM Tb_Students WHERE (Fld_StudentNo = ?); cmd.Paramet
5、ers.Add(Fld_StudentNo, OleDbType.Integer, 0, Fld_StudentNo); /构建Command对象 cmd1 OleDbCommand cmd1 = new OleDbCommand(); cmd1.Connection = conn; cmd1.CommandText = INSERT INTO Tb_Students (Fld_StudentNo, Fld_StudentName, Fld_Sex, Fld_Birthday) VALUES (?, ?, ?, ?); cmd1.Parameters.Add(Fld_StudentNo, Sy
6、stem.Data.OleDb.OleDbType.Integer, 0); cmd1.Parameters.Add(Fld_StudentName, System.Data.OleDb.OleDbType.VarWChar, 10); cmd1.Parameters.Add(Fld_Sex, System.Data.OleDb.OleDbType.VarWChar, 1); cmd1.Parameters.Add(Fld_Birthday, System.Data.OleDb.OleDbType.Date, 0); /打开数据连接 conn.Open(); cmd.ParametersFld
7、_StudentNo.Value = txtStudentNo.Text; OleDbDataReader rdstu = cmd.ExecuteReader(); if (rdstu.Read() lblMessage.Text = alert(此学号已经存在,请核对后重新输入学号!); rdstu.Close(); return; else rdstu.Close(); String ssex; if (optSex1.Checked) ssex = 男; else ssex = 女; cmd1.ParametersFld_StudentNo.Value = txtStudentNo.Te
8、xt; cmd1.ParametersFld_StudentName.Value = txtStudentName.Text; cmd1.ParametersFld_Sex.Value = ssex; if (txtBirthday.Text=) cmd1.ParametersFld_Birthday.Value =System.DateTime.Now; else cmd1.ParametersFld_Birthday.Value = txtBirthday.Text; if (cmd1.ExecuteNonQuery() 0) lblMessage.Text = 数据保存成功!; 步骤5:
9、选择“调试”“启动调试”,运行和测试程序。【示例2】本例说明如何调用Command对象的ExecuteNonQuery方法来执行UPDATE语句,并通过Parameter参数传递各个字段的值。当输入学号并单击查看按钮时,执行数据查询并将待修改的数据显示在Web窗体中;当为字段输入新值并单击更新按钮时,将所做的更改保存到数据库中。运行的效果图3所示。图3 使用UPDATE语句更新记录示例运行效果图操作步骤如下:步骤1:在Visual Studio.Net中新建网站,选择“ASP.NET 网站”,然后在应用程序根目录下创建一个文件夹并命名为command_update_exp。步骤2:将文档标题设
10、置为“Command对象执行UPDATE语句更新记录示例”。步骤3:添加Web服务器控件到窗体上,并设置属性,如表2所示。表2 设置示例的控件属性控件类型属性设置说明LabelID=lblSNO,Text=请输入学号:TextboxID=txtSNo用于要查找的学号ButtonID=btnQuery,Text=查询单击它时执行查询TextboxID=txtStudentNo用于显示学号TextboxID=txtStudentName用于要显示和修改的姓名RadioButtonID=optSex1, GroupName=sexgroup用于显示和选择性别RadioButtonID=optSex2
11、, GroupName=sexgroup用于显示和选择性别TextboxID=txtBirthday用于要显示和修改的生日TextboxID=txtAddress用于要显示和修改的地址ButtonID=btnUpdate,Text=更新单击它时执行更新LabelID=lblMessage用于显示消息Web窗体设计效果如图4所示。图4 Web窗体设计效果步骤4:打开代码隐藏类文件Default.aspx.cs,然后编写代码。按钮btnQuery的Click事件处理程序,代码如下。 protected void btnQuery _Click (object sender, EventArgs e
12、) string sno; string sex; lblMessage.Text = ; txtStudentNo.Text = ; txtStudentName.Text = ; txtAddress.Text = ; sno = txtSNO.Text; if (sno = ) lblMessage.Text = alert(请输入学号!); else /新建连接对象 conn OleDbConnection conn = new OleDbConnection(); string connstr = Provider=Microsoft.Jet.OLEDB.4.0;Data Sourc
13、e= + Server.MapPath(App_Data/student.mdb); conn.ConnectionString = connstr; /构建Command对象 cmd OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; cmd.CommandText = SELECT Fld_StudentNo,Fld_StudentName,Fld_Sex,Fld_Birthday,Fld_Address FROM Tb_Students WHERE (Fld_StudentNo = ?); cmd.Parameter
14、s.Add(Fld_StudentNo,OleDbType.VarWChar,8,Fld_StudentNo); /打开数据连接 conn.Open(); cmd.ParametersFld_StudentNo.Value = sno; /构建DataReader对象rdstu OleDbDataReader rdstu = cmd.ExecuteReader(); rdstu.Read(); if (rdstu.HasRows) txtStudentNo.Text = rdstu.GetValue(0).ToString(); txtStudentName.Text = rdstu.GetV
15、alue(1).ToString(); sex=rdstu.GetValue(2).ToString(); if (sex = 男) optSex1.Checked = true; else optSex2.Checked = true; txtBirthday.Text = rdstu.GetValue(3).ToString(); if (!(rdstu.IsDBNull(4) txtAddress.Text = rdstu.GetValue(4).ToString(); else lblMessage.Text = alert(查无此人,请核对后重新输入学号!); conn.Close(
16、); 按钮btnUpdate的Click事件处理程序,代码如下。 protected void btnUpdate _Click(object sender, EventArgs e) /新建连接对象 conn OleDbConnection conn = new OleDbConnection(); string connstr = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + Server.MapPath(App_Data/student.mdb); conn.ConnectionString = connstr; /构建Command对象
17、 cmd OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; cmd.CommandText = UPDATE Tb_Students SET Fld_StudentName = ?, Fld_Sex = ?, Fld_Birthday = ?, Fld_Address = ? WHERE (Fld_StudentNo = ?); cmd.Parameters.Add(Fld_StudentName, System.Data.OleDb.OleDbType.VarWChar, 10, Fld_StudentName); c
18、md.Parameters.Add(Fld_Sex, System.Data.OleDb.OleDbType.VarWChar, 1, Fld_Sex); cmd.Parameters.Add(Fld_Birthday, System.Data.OleDb.OleDbType.Date, 0, Fld_Birthday); cmd.Parameters.Add(Fld_Address,System.Data.OleDb.OleDbType.VarWChar,40,Fld_Address); cmd.Parameters.Add(Fld_StudentNo, System.Data.OleDb.
19、OleDbType.Integer, 0, Fld_StudentNo); /打开数据连接 conn.Open(); String ssex; if (optSex1.Checked) ssex = 男; else ssex = 女; cmd.ParametersFld_StudentNo.Value = txtStudentNo.Text; cmd.ParametersFld_StudentName.Value = txtStudentName.Text; cmd.ParametersFld_Sex.Value = ssex; if (txtBirthday.Text=) cmd.Param
20、etersFld_Birthday.Value =System.DateTime.Now; else cmd.ParametersFld_Birthday.Value = txtBirthday.Text; cmd.ParametersFld_Address.Value =txtAddress.Text; if (cmd.ExecuteNonQuery() 0) lblMessage.Text = 数据修改成功!;步骤5:选择“调试”“启动调试”,运行和测试程序。【示例3】本例说明如何通过Command对象执行DELETE语句,并通过Parameter参数传递各个字段的值。当输入学号并单击查找
21、按钮时,执行数据查询并以只读方式显示数据;当单击删除按钮时,从数据库中删除这条记录。运行效果如图5所示。图5 使用DELETE语句删除记录示例运行效果图设计步骤如下。步骤1:在Visual Studio.Net中新建网站,选择“ASP.NET 网站”,然后在应用程序根目录下创建一个文件夹并命名为command_delete_exp。步骤2:将文档标题设置为“使用DELETE语句删除记录示例”。步骤3:添加Web服务器控件到窗体上,并设置属性,如表3所示。表3 设置示例的控件属性控件类型属性设置说明LabelID=lblSNO,Text=请输入学号:TextboxID=txtSNo用于要查找的学
22、号ButtonID=btnQuery,Text=查询单击它时执行查询TextboxID=txtStudentNo用于显示学号TextboxID=txtStudentName用于要显示的姓名RadioButtonID=optSex1, GroupName=sexgroup用于显示性别RadioButtonID=optSex2, GroupName=sexgroup用于显示性别TextboxID=txtBirthday用于要显示的生日TextboxID=txtAddress用于要显示的地址ButtonID=btnDelete,Text=删除单击它时执行删除LabelID=lblMessage用于显
23、示消息Web窗体设计效果如图6 所示。图6 Web窗体设计效果步骤4:打开代码隐藏类文件Default.aspx.cs,然后编写代码。按钮btnQuery的Click事件处理程序,代码如下。 protected void btnQuery _Click (object sender, EventArgs e) string sno; string sex; lblMessage.Text = ; txtStudentNo.Text = ; txtStudentName.Text = ; txtAddress.Text = ; sno = txtSNO.Text; if (sno = ) lbl
24、Message.Text = alert(请输入学号!); else /新建连接对象 conn OleDbConnection conn = new OleDbConnection(); string connstr = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + Server.MapPath(App_Data/student.mdb); conn.ConnectionString = connstr; /构建Command对象 cmd OleDbCommand cmd = new OleDbCommand(); cmd.Connection
25、 = conn; cmd.CommandText = SELECT Fld_StudentNo,Fld_StudentName,Fld_Sex,Fld_Birthday,Fld_Address FROM Tb_Students WHERE (Fld_StudentNo = ?); cmd.Parameters.Add(Fld_StudentNo,OleDbType.VarWChar,8,Fld_StudentNo); /打开数据连接 conn.Open(); cmd.ParametersFld_StudentNo.Value = sno; /构建DataReader对象rdstu OleDbD
26、ataReader rdstu = cmd.ExecuteReader(); rdstu.Read(); if (rdstu.HasRows) txtStudentNo.Text = rdstu.GetValue(0).ToString(); txtStudentName.Text = rdstu.GetValue(1).ToString(); sex=rdstu.GetValue(2).ToString(); if (sex = 男) optSex1.Checked = true; else optSex2.Checked = true; txtBirthday.Text = rdstu.G
27、etValue(3).ToString(); if (!(rdstu.IsDBNull(4) txtAddress.Text = rdstu.GetValue(4).ToString(); else lblMessage.Text = alert(查无此人,请核对后重新输入学号!); conn.Close(); 按钮btnDelete的Click事件处理程序,代码如下。 protected void btnDelete _Click (object sender, EventArgs e) /新建连接对象 conn OleDbConnection conn = new OleDbConnect
28、ion(); string connstr = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + Server.MapPath(App_Data/student.mdb); conn.ConnectionString = connstr; /构建Command对象 cmd OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; cmd.CommandText = DELETE FROM Tb_Students WHERE (Fld_StudentNo = ?); cmd.Parameters.Add(Fld_StudentNo, System.Data.OleDb.OleDbType.Integer, 0, Fld_StudentNo); /打开数据连接 conn.Open(); cmd.ParametersFld_StudentNo.Value = txtStudentNo.Text; if (cmd.ExecuteNonQuery() 0) lblMessage.Text = 数据删除成功!;步骤5:选择“调试”“启动调试”,运行和测试程序。
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1