• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号

C#在winform中操作数据库,实现数据增删改查

原作者: [db:作者] 来自: [db:来源] 收藏 邀请

运行环境: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

 


鲜花

握手

雷人

路过

鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
上一篇:
C#控件TabControl隐藏page发布时间:2022-07-18
下一篇:
【机试题】c#是否是素数,找出比它大的第一个素数发布时间:2022-07-18
热门推荐
热门话题
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服(服务时间 9:00~18:00)

在线QQ客服
地址:深圳市南山区西丽大学城创智工业园
电邮:jeky_zhao#qq.com
移动电话:139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap