C#在winform中操作数据库，实现数据增删改查__数据库增删改查命令

相关文章推荐

帅呆的马克杯 · Visual ...· 4 月前 ·

留胡子的电影票 · Use a web app ...· 6 月前 ·

性感的绿豆 · Must use ...· 8 月前 ·

八块腹肌的草稿本 · JS生成指定范围内的随机数_空白_回忆的博客 ...· 1 年前 ·

机灵的绿茶 · python可视化---axhline()函 ...· 1 年前 ·

using System.Configuration; using System.Data.SqlClient; using System.Security.Cryptography; namespace Data class SqlDesigner private static string connStr = ConfigurationManager.ConnectionStrings["data"].ConnectionString; ///

/// 返回受影响的数据行数 ///

/// /// public static int ExecuteNoQuery(string sql) using (SqlConnection conn=new SqlConnection(connStr)) conn.Open(); using (SqlCommand cmd=conn.CreateCommand()) cmd.CommandText = sql; return cmd.ExecuteNonQuery(); ///

/// 返回一个数据集 ///

/// /// public static DataSet ExecuteDataSet(string sql) using (SqlConnection xonn=new SqlConnection(connStr)) xonn.Open(); using (SqlCommand cmd = xonn.CreateCommand()) cmd.CommandText = sql; SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataSet dataset = new DataSet(); adapter.Fill(dataset); return dataset; public static object ExecuteScalar(string sql) using (SqlConnection conn=new SqlConnection(connStr)) conn.Open(); using (SqlCommand cmd=conn.CreateCommand()) cmd.CommandText = sql; return cmd.ExecuteScalar(); ///

/// md5加密 ///

/// /// public static string GetMD5(string strPwd) string pwd = ""; //实例化一个md5对象 MD5 md5 = MD5.Create(); // 加密后是一个字节类型的数组 byte[] s = md5.ComputeHash(Encoding.UTF8.GetBytes(strPwd)); //翻转生成的MD5码 s.Reverse(); //通过使用循环，将字节类型的数组转换为字符串，此字符串是常规字符格式化所得 //只取MD5码的一部分，这样恶意访问者无法知道取的是哪几位 for (int i = 3; i < s.Length - 1; i++) //将得到的字符串使用十六进制类型格式。格式后的字符是小写的字母，如果使用大写（X）则格式后的字符是大写字符 //进一步对生成的MD5码做一些改造 pwd = pwd + (s[i] < 198 ? s[i] + 28 : s[i]).ToString("X"); return pwd;

（2）运用建立的公共类，进行数据库的操作：

a.数据查询：

ds = SqlDesigner.ExecuteDataSet("select * from dtuser");            
dt = ds.Tables[0];            
dataGridView1.DataSource = dt;

b.数据添加

 i = SqlDesigner.ExecuteNoQuery("insert into dtuser(uid,uname,pwd,uflag)values('" + textBox1.Text + "','" + textBox2.Text + "','" +textBox3.Text+ "','" + textBox4.Text + "')");

c.数据删除

string currentIndex = dataGridView1.CurrentRow.Cells[0].Value.ToString();
i = SqlDesigner.ExecuteNoQuery("delete from dtuser where uid='" + currentIndex + "'");

d.数据修改

i = SqlDesigner.ExecuteNoQuery("update dtrole set rname='" + textBox2.Text + "',flag='" + textBox3.Text + "'where rid='" + textBox1.Text + "'");

e.一些细节

这里，我们修改一下添加数据，让添加的数据变成字符串的形式，也就是加密操作：

string str = SqlDesigner.GetMD5(textBox3.Text.Trim());                
 i = SqlDesigner.ExecuteNoQuery("insert into dtuser(uid,uname,pwd,uflag)values('" + textBox1.Text + "','" + textBox2.Text + "','" + str + "','" + textBox4.Text + "')");

（3）dataGridView控件：

//绑定数据源
dataGridView1.DataSource = dt;
//自动适应列宽
dataGridView1.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;

3.代码仅供参考：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace Data
    public partial class Form1 : Form
        public Form1()
            InitializeComponent();            
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        private void TextBoxNull()
            textBox1.Text = "";
            textBox2.Text = "";
            textBox3.Text = "";
            textBox4.Text = "";
        private void 用户ToolStripMenuItem_Click(object sender, EventArgs e)
            TextBoxNull();
            ds = SqlDesigner.ExecuteDataSet("select * from dtuser");            
            dt = ds.Tables[0];
            dataGridView1.DataSource = dt;
            labelshow();
        private void 角色ToolStripMenuItem_Click(object sender, EventArgs e)
            TextBoxNull();
            ds = SqlDesigner.ExecuteDataSet("select *from dtrole");
            dt = ds.Tables[0];
            dataGridView1.DataSource = dt;
            label4.Text = "None";
            textBox4.Text = "None";
            labelshow();
        private void 对象ToolStripMenuItem_Click(object sender, EventArgs e)
            TextBoxNull();
            ds = SqlDesigner.ExecuteDataSet("select * from dtfunction");
            dt = ds.Tables[0];
            dataGridView1.DataSource = dt;
            labelshow();
        private void 帮助ToolStripMenuItem_Click(object sender, EventArgs e)
            TextBoxNull();
            ds = SqlDesigner.ExecuteDataSet("select * from help");
            dt = ds.Tables[0];
            dataGridView1.DataSource = dt;
            dataGridView1.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
        //双击dataGridView1
        private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
            string index = dataGridView1.CurrentRow.Cells[0].Value.ToString();
            if (label1.Text == "uid")
                ds = SqlDesigner.ExecuteDataSet("select *from dtuser where uid='" + index + "'");
                dt = ds.Tables[0];
                DataRow row = dt.Rows[0];
                textBox1.Text = row["uid"].ToString();
                textBox2.Text = row["uname"].ToString();
                textBox3.Text = row["pwd"].ToString();
                textBox4.Text = row["uflag"].ToString();
            if (label1.Text == "rid")
                ds = SqlDesigner.ExecuteDataSet("select *from dtrole where rid='" + index + "'");
                dt = ds.Tables[0];
                DataRow row = dt.Rows[0];
                textBox1.Text = row["rid"].ToString();
                textBox2.Text = row["rname"].ToString();
                textBox3.Text = row["flag"].ToString();
                textBox4.Text = "None";
            if (label1.Text == "fid")
                ds = SqlDesigner.ExecuteDataSet("select *from dtfunction where fid='" + index + "'");
                dt = ds.Tables[0];
                DataRow row = dt.Rows[0];
                textBox1.Text = row["fid"].ToString();
                textBox2.Text = row["fname"].ToString();
                textBox3.Text = row["flag"].ToString();
                textBox4.Text = row["uflag"].ToString();
        private void labelshow() 
            label1.Text = dataGridView1.Columns[0].HeaderText;
            label2.Text = dataGridView1.Columns[1].HeaderText;
            label3.Text = dataGridView1.Columns[2].HeaderText;
                label4.Text = dataGridView1.Columns[3].HeaderText;
            catch (Exception)
                label4.Text = "None";
        private void btn_add_Click(object sender, EventArgs e)
            int i = 0;
            if (label1.Text=="uid")
                string str = SqlDesigner.GetMD5(textBox3.Text.Trim());                
                i = SqlDesigner.ExecuteNoQuery("insert into dtuser(uid,uname,pwd,uflag)values('" + textBox1.Text + "','" + textBox2.Text + "','" + str + "','" + textBox4.Text + "')");
            else if (label1.Text == "rid")
                i = SqlDesigner.ExecuteNoQuery("insert into dtrole(rid,rname,flag)values('" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "')");
                    i = SqlDesigner.ExecuteNoQuery("insert into dtfunction(fid,rid,uid,uflag)values('" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "')");
                catch (Exception)
                    MessageBox.Show("添加失败");
            if (i > 0)
                MessageBox.Show("添加成功");
                MessageBox.Show("添加失败");
        private void btn_del_Click(object sender, EventArgs e)
            int i = 0;
            string currentIndex = dataGridView1.CurrentRow.Cells[0].Value.ToString();
            if (label1.Text=="uid")
                i = SqlDesigner.ExecuteNoQuery("delete from dtuser where uid='" + currentIndex + "'");
            else if (label1.Text=="fid")
                i = SqlDesigner.ExecuteNoQuery("delete from dtfunction where fid='" + currentIndex + "'");
                i = SqlDesigner.ExecuteNoQuery("delete from dtrole where rid='" + currentIndex + "'");
            if (i > 0)
                MessageBox.Show("删除成功");
                MessageBox.Show("删除失败");
        private void btn_update_Click(object sender, EventArgs e)
            int i = 0;
            if (label1.Text == "rid")
                i = SqlDesigner.ExecuteNoQuery("update dtrole set rname='" + textBox2.Text + "',flag='" + textBox3.Text + "'where rid='" + textBox1.Text + "'");
            if (label1.Text == "uid")
                i = SqlDesigner.ExecuteNoQuery("update dtuser set uname='" + textBox2.Text + "',pwd='" + textBox3.Text + "',uflag='" + textBox4.Text + "'where uid='" + textBox1.Text + "'");
            if (label1.Text=="fid")
                i = SqlDesigner.ExecuteNoQuery("update dtfunction set rid='" + textBox2.Text + "',uid='" + textBox3.Text + "',uflag='" + textBox4.Text + "'where fid='" + textBox1.Text + "'");
            if (i > 0)
                MessageBox.Show("Succeed！");
                MessageBox.Show("Failed！");