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

时间:2021-09-02 00:58:59

1.前言:

运行环境:VS2013+SQL2008+Windows10

程序界面预览:

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

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

 

2.功能具体介绍:

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

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

C#在winform中操作数据库,实现数据增删改查C#在winform中操作数据库,实现数据增删改查
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.代码仅供参考:

C#在winform中操作数据库,实现数据增删改查C#在winform中操作数据库,实现数据增删改查
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