在C#中通过命令对象执行SQL

最新推荐文章于 2019-04-10 13:21:29 发布

原创最新推荐文章于 2019-04-10 13:21:29 发布 · 2.7k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#c# #exception #string #object #cmd #security

SQL&SQL Server 专栏收录该内容

21 篇文章

订阅专栏

本文介绍了一个使用C#进行数据库操作的简单示例，包括插入、更新、删除及查询等基本操作。通过实例展示了如何连接数据库、执行SQL语句，并处理结果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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();
            }
        }        
    }
}