关注我
属性 | 说明 |
Connection | Command对象使用的数据库连接 |
CommandText | 执行的SQL语句 |
方法 | 说明 |
ExecuteNonQuery | 执行不返回行的语句，如Update等 |
ExecuteReader | 执行查询命令，返回DataReader对象 |
ExecuteScalar | 返回单个值，如执行count(*) |
using System.Data.SqlClient;
namespace MySchool
{//此类维护数据库连接字符串和Connection对象
class DBHelper
{ //数据库连接字符串
private static string connString =
"Data Source = .;Initial Catalog = MySchool;User ID = sa;Pwd = sa";
//数据库连接Connection对象
public static SqlConnection connection = new SqlConnection(connString);
}
}
此题主要用到了Command对象的ExecuteScalar()方法
using System.Data.SqlClient;
namespace MySchool
{
public partial class LoginForm : Form
{
private void btnLogin_Click(object sender, EventArgs e)
{
bool isValidateUser = false; //标识是否为合法用户
string message = ""; //如果登录失败，显示的消息提示
//调用用户验证方法，根据返回值确定是否通过验证
if (ValidateInput())
{ //调用用户验证方法
isValidateUser = ValidateUser(cboLoginType.Text, txtLoginId.Text,
txtLoginPwd.Text, ref message);
if (isValidateUser) //如果是合法用户，显示相应的窗体
{
UserHelper.loginId = txtLoginId.Text; //将输入的用户名保存到静态变量中
UserHelper.loginType = cboLoginType.Text; //将登录类型保存到静态变量中
ShowUserForm(); //显示相应用户的主窗体
this.Visible = false; //将当前窗体隐藏
}
//如果登录失败，显示相应的消息
else
{
MessageBox.Show(message, "登录失败",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
//验证用户是否进行了输入和选择
private bool ValidateInput()
{
if (txtLoginId.Text.Trim() == "") //判定“用户名”文本框中的字符串是否为空
{
MessageBox.Show("请输入用户名", "输入提示",
MessageBoxButtons.OK, MessageBoxIcon.Information);
txtLoginId.Focus();
return false;
}
else if (txtLoginPwd.Text.Trim() == "")
{
MessageBox.Show("请输入密码", "输入提示",
MessageBoxButtons.OK, MessageBoxIcon.Information);
txtLoginPwd.Focus();
return false;
}
else if (cboLoginType.Text.Trim() == "选择登录类型")
{
MessageBox.Show("请选择登录类型", "输入提示",
MessageBoxButtons.OK, MessageBoxIcon.Information);
cboLoginType.Focus();
return false;
}
else
{
return true;
}
}
public bool ValidateUser(string loginType, string loginId, string loginPwd,
ref string message)
{
int count = 0; //查询数据库的结果
bool isValidateUser = false; //返回值，是否找到该用户
//查询是否存在匹配的用户名和密码
if (loginType == "管理员")
{
//查询用的SQL语句
string sql = string.Format("select count(*) from Admin where " +
"LoginID = '{0}' and LoginPwd = '{1}'", loginId, loginPwd);
try
{
//创建Command命令
SqlCommand command = new SqlCommand(sql, DBHelper.connection);
DBHelper.connection.Open();
count = (int)command.ExecuteScalar();
if (count == 1)
{
isValidateUser = true;
}
else
{
message = "用户名或密码不存在！";
isValidateUser = false;
}
}
catch (Exception ex)
{
message = ex.Message;
Console.WriteLine(ex.Message);
}
finally
{
DBHelper.connection.Close();
}
}
return isValidateUser;
}
public void ShowUserForm()//根据登录类型，显示相应的窗体
{
switch (cboLoginType.Text)
{
case "管理员":
AdminForm adminForm = new AdminForm();
adminForm.Show();
break;
default:
MessageBox.Show("抱歉，您请求的功能尚未完成！");
break;
}
}
此题中主要用到了ListView控件显示商品的详细信息，其视图模式选择Details、DataReader、快捷菜单使用ContextMenuStrip控件、Command对象的ExecuteNonQuery()方法执行删除操作、SQL的分组操作等。
using System.Data.SqlClient;
namespace MySchool
{
public partial class GoodsInfo : Form
{
private void btnLook_Click(object sender, EventArgs e)
{
if (cboGoodsType.Text.Trim() == "选择商品类别")
{
MessageBox.Show("请输入商品类别", "选择提示",
MessageBoxButtons.OK, MessageBoxIcon.Information);
cboGoodsType.Focus();
}
else
{
FillListView();
}
}
private void FillListView()
{
string goodsName, goodsType, goodsNumber, goodsPrice;
try
{
string sql = string.Format(
"select name, type, number, price from GoodsInfo where type = '{0}'", cboGoodsType.Text.Trim());
SqlCommand cmd = new SqlCommand(sql, DBHelper.connection);
DBHelper.connection.Open();
SqlDataReader dataReader = cmd.ExecuteReader();
lvGoodsInfo.Items.Clear();
if (!dataReader.HasRows) //如果结果中没有数据行，就弹出提示
{
MessageBox.Show("抱歉，没有该类型商品！", "查询结果提示",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
//将查到的结果循环写到ListView中
while (dataReader.Read())
{
goodsName = (string)dataReader["name"];
goodsType = (string)dataReader["type"];
//goodsNumber = (string)dataReader["number"];
//goodsPrice = (string)dataReader["price"];
////ex.Message = "无法将类型为“System.Int32”的对象强制转换为类型“System.String”。"
goodsNumber = dataReader["number"].ToString();
goodsPrice = dataReader["price"].ToString();
//创建一个ListView项
ListViewItem lviGoodsInfo = new ListViewItem(goodsName);
lviGoodsInfo.Tag = goodsName;
lvGoodsInfo.Items.Add(lviGoodsInfo); //向ListView中添加一个新项
lviGoodsInfo.SubItems.AddRange(new string[] { goodsType, goodsNumber, goodsPrice });
}
}
dataReader.Close();
}
catch (Exception ex)
{
MessageBox.Show("查询数据出错", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
Console.WriteLine(ex.Message);
}
finally
{
DBHelper.connection.Close();
}
}
private void btnClose_Click(object sender, EventArgs e)
{
this.Close();
}
private void GoodsInfo_Load(object sender, EventArgs e)
{
try
{
string sql = "select type from GoodsInfo group by type";
SqlCommand cmd = new SqlCommand(sql, DBHelper.connection);
DBHelper.connection.Open();
SqlDataReader dataReader = cmd.ExecuteReader();
string goodsType = "";
while (dataReader.Read())
{
goodsType = (string)dataReader[0];
cboGoodsType.Items.Add(goodsType);
}
dataReader.Close();
}
catch (Exception ex)
{
MessageBox.Show("操作数据库出错");
Console.WriteLine(ex.Message);
}
finally
{
DBHelper.connection.Close();
}
}
private void tsmiDelete_Click(object sender, EventArgs e)
{
if (lvGoodsInfo.SelectedItems.Count == 0)
{
MessageBox.Show("你没有选择任何商品", "操作提示", MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
else
{
DialogResult choice = MessageBox.Show("确定要删除该商品信息吗？", "操作警告",
MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (choice == DialogResult.Yes)
{
int result = 0;
try
{
//string sql = string.Format("delete from GoodsInfo where name = {0}",
// lvGoodsInfo.SelectedItems[0].Tag.ToString());
//sql = "delete from GoodsInfo where name = 农夫山泉"
//注意出现错误的原因→ex.Message = "列名'农夫山泉' 无效。"
string sql = string.Format("delete from GoodsInfo where name = '{0}'",
lvGoodsInfo.SelectedItems[0].Tag.ToString());
SqlCommand cmd = new SqlCommand(sql, DBHelper.connection);
DBHelper.connection.Open();
result = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
DBHelper.connection.Close();
}
if (result < 1)
{
MessageBox.Show("删除失败！", "操作结果", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
else
{
MessageBox.Show("删除成功！", "操作结果", MessageBoxButtons.OK, MessageBoxIcon.Information);
FillListView();
}
下题中用到了DataGridView控件、DataSet、DataAdapter对象的Update方法
using System.Data.SqlClient;
namespace MySchool
{
public partial class GoodsInfoListForm : Form
{
private DataSet dataSet = new DataSet();
private SqlDataAdapter dataAdapter;
private void GoodsInfoListForm_Load(object sender, EventArgs e)
{
string sql = "select id, name, type, number, price from GoodsInfo";
dataAdapter = new SqlDataAdapter(sql, DBHelper.connection);
dataAdapter.Fill(dataSet, "GoodsInfo");
dgvGoodsInfo.DataSource = dataSet.Tables["GoodsInfo"];
}
private void btnUpdate_Click(object sender, EventArgs e)
{
/**为了支持数据库更新，我们可以使用SqlCommandBuilder自动生成DataAdapter的InsertCommand, UpdateCommand,和DeleteCommand属性。为了支持自动生成，指定的SelectCommand必须包含主键或唯一性 列。如果没有，则无法生成InsertCommand,UpdateCommand,和DeleteCommand，并会抛出InvalidOperation异常。注意：string sql = "select id,中必须包含列名’id’，因为在进行更新时要靠主键进行*/
DialogResult result = MessageBox.Show(
"确定要保存修改吗？", "操作提示",
MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (result == DialogResult.OK)
{
SqlCommandBuilder builder = new SqlCommandBuilder(dataAdapter);
dataAdapter.Update(dataSet, "GoodsInfo");
}
}