using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace test
{
public partial class frmSql : Form
{
//两个文本框,名为txtUid,txtPwd;一个组合框,名为cboUser
//四个按钮,名为btnInsert,btnDelete,btnUpdate,btnSelect
//数据库名为testSql,表结构为:Users(Id, Username, Password)
public frmSql()
{
InitializeComponent();
}
string cnnStr =@"integrated security=true;database=testProcedure;server=.\sqlexpress";
private void frmSql_Load(object sender, EventArgs e)
{
SqlConnection cnn = new SqlConnection(cnnStr);
cnn.Open();
SqlCommand cmd = new SqlCommand("select * from users", cnn);
SqlDataReader dr = cmd.ExecuteReader();
string userName = "";
while (dr.Read()) //添加用户名到组合框中
{
userName = dr[1].ToString();
cboUser.Items.Add(userName);
}
dr.Close();
}
private void btnSelect_Click(object sender, EventArgs e)
{//按用户名模糊查找
SqlConnection cnn = new SqlConnection(cnnStr);
cnn.Open();
string strSql = string.Format("select * from users where username like '%{0}%'", txtUid.Text);
SqlCommand cmd = new SqlCommand(strSql, cnn);
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
if (dr.HasRows == true)
{
string userName = dr[1].ToString();
MessageBox.Show(userName + " 找着了!");
}
else
{
MessageBox.Show(txtUid.Text + " 用户不存在!");
}
dr.Close();
}
private void btnInsert_Click(object sender, EventArgs e)
{//新增用户
SqlConnection cnn = new SqlConnection(cnnStr);
cnn.Open();
if (txtUid.Text == "" || txtPwd.Text == "")
{
MessageBox.Show("用户名和密码不能为空!");
return;
}
string strSql = string.Format("insert into users(username,password) values('{0}','{1}')",
txtUid.Text,txtPwd.Text);
SqlCommand cmd = new SqlCommand(strSql, cnn);
try
{
cmd.ExecuteNonQuery();
MessageBox.Show("新增用户成功!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
cnn.Close();
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{//按用户名修改密码
SqlConnection cnn = new SqlConnection(cnnStr);
cnn.Open();
string strSql = string.Format("select count(*) from users where username ='{0}'", txtUid.Text);
SqlCommand cmd = new SqlCommand(strSql, cnn);
int res=Convert.ToInt32(cmd.ExecuteScalar());
if (res == 0)
{
MessageBox.Show("用户不存在!");
return;
}
strSql = string.Format("update users set password='{1}' where username='{0}'",
txtUid.Text, txtPwd.Text);
cmd.CommandText = strSql;
try
{
if (cmd.ExecuteNonQuery()>0)
MessageBox.Show("修改用户密码成功!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
cnn.Close();
}
}
private void btnDelete_Click(object sender, EventArgs e)
{//按用户名删除用户
SqlConnection cnn = new SqlConnection(cnnStr);
cnn.Open();
string strSql = string.Format("delete from users where username='{0}'", txtUid.Text);
SqlCommand cmd = new SqlCommand(strSql, cnn);
try
{
if (cmd.ExecuteNonQuery()>0)
MessageBox.Show("删除用户成功!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
cnn.Close();
}
}
}
}
在C#中通过命令对象执行SQL
最新推荐文章于 2018-10-10 22:25:41 发布