1:首先如果使用MySql数据库就得安装MySql数据库(网上查)
2:使用C#操作MySql数据库,就得使用Mysql数据包
打开vs->工具->NuGet包管理器->管理解决方案的NuGet程序包->安装MySql.Data数据包
C#操作数据库中解决不能添加汉字或者添加汉字乱码
解决方法:在数据库连接字符串中增加字符集的说明,Charset=utf8,如
Charset=utf8;
MySQLConnection con = new MySQLConnection(“server=127.0.0.1;uid=root;pwd=;database=test;Charset=utf8”);*
在.cs文件中添加引用:
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.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
namespace OperationMySqlWinFormDemo
{
public partial class Form1 : Form
{
/// <summary>
/// 数据库连接
/// </summary>
MySqlConnection m_conn;
public Form1()
{
InitializeComponent();
}
/// <summary>
/// 打开数据库连接
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
if (openConnectionFunc())
{
MessageBox.Show("连接数据库成功");
}
else
{
MessageBox.Show("连接数据库失败");
}
}
/// <summary>
/// 连接数据库
/// </summary>
/// <returns></returns>
public bool openConnectionFunc()
{
bool my_ret = false;
try
{
string my_connectStr = "server=127.0.0.1;port=3306;User Id=root;password=root; database=mytest;Charset=utf8";//database:要连接的数据库名称;
// string my_connectStr = "server=localhost;User Id = root; password=root; Database=mytest";
m_conn = new MySqlConnection(my_connectStr);
m_conn.Open();
my_ret = true;
}
catch (MySqlException ex)
{
if (ex.ToString().Contains("Unknown database"))
{
Console.WriteLine("database错误,请检查...");
}
switch (ex.Number)
{
case 0:
Console.WriteLine(ex.Number);
Console.WriteLine("User Id/Password/database错误,请检查...");
break;
case 1042:
Console.WriteLine(ex.Number);
Console.WriteLine("IP/Port错误,请检查...");
break;
case 1045:
Console.WriteLine(ex.Number);
Console.WriteLine("User Id/Password错误,请检查...");
break;
default:
Console.WriteLine("连接失败");
break;
}
return false;
}
return my_ret;
}
/// <summary>
/// 关闭数据库连接
/// </summary>
/// <returns></returns>
public bool colseConnectionFunc()
{
try
{
m_conn.Close();
return true;
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
return false;
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button2_Click(object sender, EventArgs e)
{
if (colseConnectionFunc())
{
MessageBox.Show("数据库关闭成功");
}
else
{
MessageBox.Show("数据库关闭失败");
}
}
/// <summary>
/// 插入
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button3_Click(object sender, EventArgs e)
{
try
{
string my_query = "insert into user(Id,Name,Sex,Age)values(10,'xuww','男',25)";
if (this.openConnectionFunc())
{
MySqlCommand my_cmd = new MySqlCommand(my_query, m_conn);
my_cmd.ExecuteNonQuery();
this.colseConnectionFunc();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
/// <summary>
/// 删除
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button4_Click(object sender, EventArgs e)
{
try
{
string my_query = "delete from user where Id = 10";
if (this.openConnectionFunc())
{
MySqlCommand my_cmd = new MySqlCommand(my_query, m_conn);
my_cmd.ExecuteNonQuery();
this.colseConnectionFunc();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
/// <summary>
/// 改数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button5_Click(object sender, EventArgs e)
{
try
{
string my_query = "update user set Id=4,Name='xcc'where Id = 10";
if (this.openConnectionFunc())
{
MySqlCommand my_cmd = new MySqlCommand(my_query,m_conn);
my_cmd.ExecuteNonQuery();
this.colseConnectionFunc();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
/// <summary>
/// 查数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button6_Click(object sender, EventArgs e)
{
try
{
#region 查询的其他办法
/*
string str = "";
if (textBox1.Text.Trim() != "")
str = "where id like " + textBox1.Text.Trim();
try
{
string searchStr = ("select * from student " + str);
MySqlDataAdapter adapter = new MySqlDataAdapter(searchStr, SQLCon);
DataTable a = new DataTable();
adapter.Fill(a);
this.dataGridView1.DataSource = a;
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK);
}
*/
/*
2)查询条件不固定
//string sql = "select * from user where username='"+username+"' and password='"+password+"'"; //我们自己按照查询条件去组拼
//防止sql注入
string sql = "select * from user where username=@para1 and password=@para2";//在sql语句中定义parameter,然后再给parameter赋值
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("para1", username);
cmd.Parameters.AddWithValue("para2", password);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())//如果用户名和密码正确则能查询到一条语句,即读取下一行返回true
{
return true;
}
*/
#endregion
string my_query = "select * from user where Id = 4";
if (this.openConnectionFunc())
{
MySqlCommand my_cmd = new MySqlCommand(my_query,m_conn);
MySqlDataReader my_dataReader = my_cmd.ExecuteReader();
my_dataReader.Read();
Console.WriteLine("Id:" + my_dataReader["Id"] + "\n" + "Name:" +my_dataReader["Name"]+"\n"+ "Sex:" + my_dataReader["Sex"] + "\n" + "Age:" + my_dataReader["Age"] + "\n" );
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
/// <summary>
/// 创建表
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button7_Click(object sender, EventArgs e)
{
try
{
string createStatement = "CREATE TABLE People (Name VarChar(50), Age Integer)";
if (openConnectionFunc())
{
MySqlCommand my_cmd = new MySqlCommand(createStatement, m_conn);
my_cmd.ExecuteNonQuery();
this.colseConnectionFunc();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
/// <summary>
/// 编辑表
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button8_Click(object sender, EventArgs e)
{
try
{
string alterStatement = "ALTER TABLE People ADD Sex Boolean";
if (openConnectionFunc())
{
MySqlCommand my_cmd = new MySqlCommand(alterStatement, m_conn);
my_cmd.ExecuteNonQuery();
this.colseConnectionFunc();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
/// <summary>
/// 查询表名
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button9_Click(object sender, EventArgs e)
{
try
{
if (openConnectionFunc())
{
string my_sql = "show tables";
MySqlCommand my_cmd = new MySqlCommand(my_sql,m_conn);
MySqlDataReader my_read = my_cmd.ExecuteReader();
while (my_read.Read())
{
Console.WriteLine(my_read.GetString(0));
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
/// <summary>
/// 查询表下面字段名称
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button10_Click(object sender, EventArgs e)
{
try
{
List<string> my_list_colName = new List<string>();
List<Type> my__list_colType = new List<Type>();
List<string> my_list_coltype = new List<string>();
string sql = "show columns from user ;";
if (openConnectionFunc())
{
MySqlCommand my_cmd = new MySqlCommand(sql, m_conn);
MySqlDataReader my_read = my_cmd.ExecuteReader();
if (my_read.HasRows)
{
while (my_read.Read())
{
string my_t = my_read.GetString(0);
Type my_tt = my_read.GetValue(1) as Type;
string my_str_type = my_read.GetValue(1).ToString();
string my_ttt = my_read.GetString(1);
my_list_colName.Add(my_t);
my__list_colType.Add(my_tt);
my_list_coltype.Add(my_ttt);
}
}
colseConnectionFunc();
int my_count1 = my_list_colName.Count();
int my_count2 = my__list_colType.Count();
int my_count3 = my_list_coltype.Count();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
private void button11_Click(object sender, EventArgs e)
{
Form3 form3 = new Form3();
form3.Show();
}
}
}