目录
- 准备工作
- 连接数据库
- 关闭连接
- 增删改查
- 增删改
- 查
- 可复用方法
准备工作
准备工作只有一件事：引用MySql.Data.dll文件。
有两种方法：
1.下载Mysql.Data.dll文件后添加引用。
2.使用NuGet安装。
如图，在弹出的窗口中，搜索框输入mysql，选择作者为Oracle的Mysql.Data，点击右侧的安装即可。注意后面会弹出安装依赖项的确认框，点确认就可以。
连接数据库
1.首先新建一个类CallMySqlClass，增加using MySql.Data.MySqlClient;
2.定义全局变量，将数据库连接参数写到这里。
public static string sql_ip = "127.0.0.1"; //IP
public static string sql_port = "3306"; //端口号
public static string sql_user = "root"; //用户名
public static string sql_pw = "root"; //密码
public static string sql_name = "catlrgv2"; //数据库名
public static string sql_con_state = "off"; //连接状态
public static MySqlConnection sql_connection;3.写连接数据库的方法。
注意：定义mysql连接字符串有两种方式，注释掉的两行为手动编写方式，通过MySqlConnectionStringBuilder的方式和手动编写的方式最终结果是一样的。
/// <summary>
/// 连接数据库
/// </summary>
public static void MySqlCon()
{
MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder();
builder.Server = sql_ip;
builder.Port = (uint)PublicClass.ToInt(sql_port);
builder.UserID = sql_user;
builder.Password = sql_pw;
builder.Database = sql_name;
builder.Pooling = true;
builder.CharacterSet = "utf8";
//string ConnectionString = "server = " + sql_ip + "; port = " + (uint)PublicClass.ToInt(sql_port) + "; userid = " + sql_user + "; password = " + sql_pw + "; database =" + sql_name + "; pooling=true; Charset=utf8;"; // string ConnectionString = "server = " + this.sql_ip.Text + "; userid = "+this.sql_user.Text+"; password = "+ this.sql_pw.Text + "; database ="+ this.sql_name.Text + "; persistsecurityinfo = True;";
//sql_connection = new MySqlConnection(ConnectionString);
sql_connection = new MySqlConnection(builder.ConnectionString);
try
{
sql_connection.Open();
sql_con_state = "on";
}
catch (MySqlException ex)
{
sql_con_state = "off";
}
}关闭连接
/// <summary>
/// 关闭连接
/// </summary>
public static void MySqlUnCon()
{
if (sql_con_state == "on")
{
sql_connection.Close();
sql_con_state = "off";
}
}增删改查
在数据库里新建表test_table用于测试，结构如下。
实体类：
/// <summary>
/// 实体类
/// </summary>
public class TestTableData
{
public string traycode { get; set; }
public int testtype { get; set; }
public TestTableData()
{
}
public TestTableData(string traycode, int testtype)
{
this.traycode = traycode;
this.testtype = testtype;
}
}增删改
由于都是通过写sql语句来执行，所以增删改的代码大同小异，需要注意的是返回值为执行完sql语句后受影响的行数。
/// <summary>
/// 增
/// </summary>
public static int insert_data()
{
string table_name = "test_table";
string sql = "insert into " + table_name + " (traycode,testtype) values ('test',0)";
if (table_name == "" || sql == "")
{
return -1;
}
if (sql_con_state != "on")
{
return -2;
}
MySqlCommand cmd = new MySqlCommand(sql, sql_connection);
return cmd.ExecuteNonQuery();
}
/// <summary>
/// 删
/// </summary>
public static int del_data()
{
string table_name = "test_table";
string sql = "delete from " + table_name + " where traycode = 'test'";
if (table_name == "" || sql == "")
{
return -1;
}
if (sql_con_state != "on")
{
return -2;
}
MySqlCommand cmd = new MySqlCommand(sql, sql_connection);
return cmd.ExecuteNonQuery();
}
/// <summary>
/// 改
/// </summary>
public static int update_data()
{
string table_name = "test_table";
string sql = "update " + table_name + " set testtype = 1 where traycode = 'test'";
if (table_name == "" || sql == "")
{
return -1;
}
if (sql_con_state != "on")
{
return -2;
}
MySqlCommand cmd = new MySqlCommand(sql, sql_connection);
return cmd.ExecuteNonQuery();
}查
查询单独来讲，是因为要把查询结果映射到实体类。
方法一：每一行数据通过下标访问进行赋值映射到实体类（注释掉的部分则是如果只查一行数据或一列数据，就直接放到一个List<string>里就可以）
/// <summary>
/// 查
/// </summary>
public static List<TestTableData> select_data()
{
List<TestTableData> list = new List<TestTableData>();
//List<string> listtest = new List<string>();
string table_name = "test_table";
string sql = "select * from " + table_name;
MySqlCommand cmd = new MySqlCommand(sql, sql_connection);//创建命令对象;
MySqlDataReader reader = cmd.ExecuteReader();//执行命令,ExcuteReader返回的是DataReader对象
while (reader.Read())
{
TestTableData testTableData = new TestTableData();
testTableData.traycode = reader[0].ToString();
testTableData.testtype = (int)reader[1];
list.Add(testTableData);
/* for (int i = 0; i < reader.FieldCount; i++)
{
listtest.Add(reader[i].ToString());//每一行的每一个数据作为string都写入list
}*/
}
return list;
}方法二：用DataTable转换实体类（推荐使用这种方式，可直接复用方法）
/// <summary>
/// 查
/// </summary>
public static List<TestTableData> select_data()
{
List<TestTableData> list = new List<TestTableData>();
DataTable dt = new DataTable();
string table_name = "test_table";
string sql = "select * from " + table_name;
MySqlCommand cmd = new MySqlCommand(sql, sql_connection);//创建命令对象;
MySqlDataAdapter mda = new MySqlDataAdapter(cmd); //获取结果集
mda.Fill(dt);
list = DataTableHelper<FormStatusTableData>.DataTableConvertToModel(dt);
return list;
}
public class DataTableHelper<T> where T : class
{
/// <summary>
/// 用DataTable转换实体类
/// </summary>
public static List<T> DataTableConvertToModel(DataTable dt)
{
if (dt == null || dt.Rows.Count == 0)
{
return null;
}
List<T> modelList = new List<T>();
foreach (DataRow dr in dt.Rows)//从该表的行的集合中循环取出一行数据
{
T model = (T)Activator.CreateInstance(typeof(T));
for (int i = 0; i < dr.Table.Columns.Count; i++)
{
PropertyInfo propertyInfo = model.GetType().GetProperty(dr.Table.Columns[i].ColumnName);
if (propertyInfo != null && dr[i] != DBNull.Value)
propertyInfo.SetValue(model, Convert.ChangeType(dr[i], propertyInfo.PropertyType), null);
}
modelList.Add(model);
}
return modelList;
}
}可复用方法
根据上面的例子，将表名作为可复用方法的参数。
上述仅可查询表test_table的全部数据的方法select_data()返回值是List<TestTableData>；
那么使用可复用方法DataTableHelper<T>.select_data(string table_name)时：List<TestTableData> list = DataTableHelper<TestTableData>.select_data("test_table");
public class DataTableHelper<T> where T : class
{
/// <summary>
/// 用DataTable转换实体类
/// </summary>
public static List<T> DataTableConvertToModel(DataTable dt)
{
if (dt == null || dt.Rows.Count == 0)
{
return null;
}
List<T> modelList = new List<T>();
foreach (DataRow dr in dt.Rows)//从该表的行的集合中循环取出一行数据
{
T model = (T)Activator.CreateInstance(typeof(T));
for (int i = 0; i < dr.Table.Columns.Count; i++)
{
PropertyInfo propertyInfo = model.GetType().GetProperty(dr.Table.Columns[i].ColumnName);
if (propertyInfo != null && dr[i] != DBNull.Value)
propertyInfo.SetValue(model, Convert.ChangeType(dr[i], propertyInfo.PropertyType), null);
}
modelList.Add(model);
}
return modelList;
}
/// <summary>
/// 通用查
/// </summary>
/// <param name="table_name">表名</param>
public static List<T> select_data(string table_name)
{
List<T> list = new List<T>();
DataTable dt = new DataTable();
string sql = "select * from " + table_name;
MySqlCommand cmd = new MySqlCommand(sql, sql_connection);//创建命令对象;
MySqlDataAdapter mda = new MySqlDataAdapter(cmd); //获取结果集
mda.Fill(dt);
list = DataTableHelper<T>.DataTableConvertToModel(dt);
return list;
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
