C#使用mysql需要引用Mysql.Data.dll文件
在C#中引用成功后将会看到如下示意图:
程序当中添加using MySql.Data.MySqlClient;方可使用。
实现简单的功能:创建数据库、生成表格、插入数据、查询数据、删除数据、更新数据、事务处理。
附上代码:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
namespace MysqlExample
{
public partial class Form1 : Form
{
string connetStr = "server=localhost;port=3306;user=root;password=123456;CharSet=utf8";
//string connetStr = "server=localhost;port=3306;user=root;password=123456;database=data;CharSet=utf8";//其中database为数据库,表示选择当前数据库进行操作
MySqlConnection conn = new MySqlConnection();
/// <summary>
/// 执行mysql的语句
/// </summary>
/// <param name="str_sql">mysql语句</param>
/// <returns>返回受执行语句影响的函数或者执行错误的错误原因</returns>
private string ExcuteMysql(string str_sql)
{
int result = 0;
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
}
catch { }
using (MySqlCommand cmd = new MySqlCommand(str_sql, conn))
{
try
{
result = cmd.ExecuteNonQuery();//3.执行插入、删除、更改语句。执行成功返回受影响的数据的行数,返回1可做true判断。执行失败不返回任何数据,报错,下面代码都不执行
}
catch (MySqlException ex)
{
return ex.Message;
}
finally
{
conn.Close();
}
}
return result.ToString();
}
/// <summary>
/// 获取mysql数据库中的数据
/// </summary>
/// <param name="str_sql">mysql语句</param>
/// <returns>成功则返回数据否则返回null</returns>
private DataSet GetMysqlData(string str_sql)
{
DataSet dataSet = new DataSet();
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
}
catch { }
using (MySqlDataAdapter dataAdapter = new MySqlDataAdapter(str_sql, conn))
{
try
{
dataAdapter.Fill(dataSet);
return dataSet;
}
catch (MySqlException ex)
{
return null;
}
finally
{
conn.Close();
}
}
}
/// <summary>
/// 事务处理,要么全部执行,要么全部失败
/// </summary>
/// <param name="sqls">每一个string,均为一条mysql语句</param>
/// <returns>成功返回true,失败返回false</returns>
private bool MysqlTrans(List<string> sqls)
{
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
using (MySqlTransaction trans = conn.BeginTransaction())//開啟事務
{
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = conn;//添加連接工具
cmd.Transaction = trans;//添加事務
try
{
foreach (string sql in sqls)
{
cmd.CommandText = sql;//獲取sql語句
cmd.ExecuteNonQuery();//執行
}
trans.Commit();//執行完成之後提交
conn.Close();
return true;
}
catch (Exception ex)
{
trans.Rollback();//執行sql語句失敗,事務回滾
conn.Close();
return false;
}
}
}
}
catch (Exception ex)
{
return false;
}
}
public Form1()
{
InitializeComponent();
conn.ConnectionString = connetStr;
InformationTransfer.FormSize formSize=new InformationTransfer.FormSize(this);//界面大小自动调整类
}
private void CreateBase_Click(object sender, EventArgs e)
{
if(dataBase1.Text!=string.Empty)
{
string str_sql = "CREATE DATABASE `" + dataBase1.Text + "`;";
string ret=ExcuteMysql(str_sql);
try
{
int result = int.Parse(ret);
MessageBox.Show("创建数据库成功!");
}
catch
{
MessageBox.Show("创建数据库失败!");
}
}
}
private void DeleteBase_Click(object sender, EventArgs e)
{
if (dataBase1.Text != string.Empty)
{
string str_sql = "DROP DATABASE `" + dataBase1.Text + "`;";
string ret = ExcuteMysql(str_sql);
try
{
int result = int.Parse(ret);
MessageBox.Show("删除数据库成功!");
}
catch
{
MessageBox.Show("删除数据库失败!");
}
}
}
private void CreateTable_Click(object sender, EventArgs e)
{
if(dataBase2.Text!=string.Empty&&table1.Text!=string.Empty&&field_name0.Text!=string.Empty&& field_type0.Text!=string.Empty)
{
string str_sql = string.Empty;
if (grou1.Text == "INT")
{
str_sql = "CREATE TABLE `" + dataBase2.Text + "`.`" + table1.Text + "`(`" + field_name0.Text + "` " + field_type0.Text + ");";
}
else
{
str_sql = "CREATE TABLE `" + dataBase2.Text + "`.`" + table1.Text + "`(`" + field_name0.Text + "` VARCHAR(100));";
}
string ret = ExcuteMysql(str_sql);
try
{
int result = int.Parse(ret);
MessageBox.Show("创建表成功!");
}
catch
{
MessageBox.Show("创建表失败!");
}
}
}
private void DeleteTable_Click(object sender, EventArgs e)
{
if (dataBase2.Text != string.Empty && table1.Text != string.Empty && field_name0.Text != string.Empty)
{
string str_sql = str_sql = "DROP TABLE `" + dataBase2.Text + "`.`" + table1.Text + ";";
string ret = ExcuteMysql(str_sql);
try
{
int result = int.Parse(ret);
MessageBox.Show("删除表成功!");
}
catch
{
MessageBox.Show("删除表失败!");
}
}
}
private void Insert_Click(object sender, EventArgs e)
{
if(dataBase3.Text!=string.Empty&&table2.Text!=string.Empty&&field_name1.Text!=string.Empty&&data_field_name1.Text!=string.Empty)
{
string str_sql = "INSERT INTO `"+ dataBase3.Text+"`.`"+ table2.Text+"`(`"+ field_name1.Text + "`) VALUES('"+ data_field_name1.Text+"'); ";
string ret = ExcuteMysql(str_sql);
try
{
int result = int.Parse(ret);
MessageBox.Show("插入数据成功!");
}
catch
{
MessageBox.Show("插入数据失败!");
}
}
}
private void Update_Click(object sender, EventArgs e)
{
if(dataBase4.Text!=string.Empty&&table3.Text!=string.Empty&&update_field_name1.Text!=string.Empty&&data_upodate_field_name1.Text!=string.Empty&&condition_field_name1.Text!=string.Empty&&data_condition_field_name1.Text!=string.Empty)
{
string str_sql = "UPDATE `"+dataBase4.Text+"`.`"+ table3.Text+"` SET `"+ update_field_name1.Text +"`='"+ data_upodate_field_name1.Text+"' WHERE `"+ condition_field_name1.Text +"`='"+ data_condition_field_name1.Text+"';";
string ret = ExcuteMysql(str_sql);
try
{
int result = int.Parse(ret);
MessageBox.Show("更新数据成功!");
}
catch
{
MessageBox.Show("更新数据失败!");
}
}
}
private void Delete_Click(object sender, EventArgs e)
{
if(dataBase5.Text!=string.Empty&&table4.Text!=string.Empty&&condition_field_name2.Text!=string.Empty&&data_condition_field_name2.Text!=string.Empty)
{
string str_sql = "DELETE FROM `"+ dataBase5.Text+"`.`"+ table4.Text+"` WHERE "+ condition_field_name2.Text +"= '"+ data_condition_field_name2.Text+"';";
string ret = ExcuteMysql(str_sql);
try
{
int result = int.Parse(ret);
MessageBox.Show("删除数据成功!");
}
catch
{
MessageBox.Show("删除数据失败!");
}
}
}
private void Inquire_Click(object sender, EventArgs e)
{
if (dataBase6.Text != string.Empty && table5.Text != string.Empty && condition_field_name3.Text != string.Empty && data_condition_field_name3.Text != string.Empty)
{
string str_sql = "SELECT * FROM `" + dataBase6.Text + "`.`" + table5.Text + "` WHERE " + condition_field_name3.Text + "= '" + data_condition_field_name3.Text + "';";
DataSet data = GetMysqlData(str_sql);
if(data!=null)
{
dataGridView1.DataSource = data.Tables[0];
}
else
{
MessageBox.Show("查询失败!");
}
}
}
private void Execute_Click(object sender, EventArgs e)
{
if(richTextBox1.Text!=string.Empty)
{
List<string> sqls = new List<string>();
sqls.AddRange(richTextBox1.Text.Split('\n'));
bool ret=MysqlTrans(sqls);
if(ret)
{
MessageBox.Show("事务处理完成!");
}
else
{
MessageBox.Show("事务处理失败!");
}
}
}
private void add_field_name_button_Click(object sender, EventArgs e)
{
if (dataBase2.Text != string.Empty && table1.Text != string.Empty && add_field_name.Text != string.Empty && field_type1.Text != string.Empty)
{
string str_sql = string.Empty;
if (grou1.Text == "INT")
{
str_sql = "ALTER TABLE `" + dataBase2.Text + "`.`" + table1.Text + "` ADD `" + add_field_name.Text + "` " + field_type1.Text + ";";
}
else
{
str_sql = "ALTER TABLE `" + dataBase2.Text + "`.`" + table1.Text + "` ADD `" + add_field_name.Text + "` VARCHAR(100);";
}
string ret = ExcuteMysql(str_sql);
try
{
int result = int.Parse(ret);
MessageBox.Show("添加字段成功!");
}
catch
{
MessageBox.Show("添加字段失败!");
}
}
}
}
}
代码分享地址:
https://download.csdn.net/download/syx3904/12329156