你的位置:首页 > ASP.net教程

[ASP.net教程]C#在winform中操作数据库,实现数据增删改查


1.前言:

运行环境:VS2013+SQL2008+Windows10

程序界面预览:

使用的主要控件:dataGridview和menuStrip等。

 

2.功能具体介绍:

1.首先,我们要先实现基本的数据操作,增删改查这几个操作。

(1)先定义一个数据库操作的公共类:

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Data;using System.Configuration;using System.Data.SqlClient;using System.Security.Cryptography;namespace Data{  class SqlDesigner  {    private static string connStr = ConfigurationManager.ConnectionStrings["data"].ConnectionString;    /// <summary>    /// 返回受影响的数据行数    /// </summary>    /// <param name="sql"></param>    /// <returns></returns>    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();                 }      }    }    /// <summary>    /// 返回一个数据集    /// </summary>    /// <param name="sql"></param>    /// <returns></returns>    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();        }      }    }    /// <summary>    /// md5加密    /// </summary>    /// <param name="strPwd"></param>    /// <returns></returns>    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;      try      {        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 + "')");      }      else      {        try        {          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("添加成功");      }      else      {        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 + "'");      }      else      {        i = SqlDesigner.ExecuteNoQuery("delete from dtrole where rid='" + currentIndex + "'");      }      if (i > 0)      {        MessageBox.Show("删除成功");      }      else      {        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!");      }      else      {        MessageBox.Show("Failed!");      }    }              }}

View All Code