MysqlHelper.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
public class MySQLHelper
{
private string connectionString;
public MySQLHelper(string connectionString)
{
this.connectionString = connectionString;
}
//
/// <summary>
/// 执行不返回结果集的SQL语句,影响的行数
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public int ExecuteNonQuery(string sql, params MySqlParameter[] parameters)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
using (MySqlCommand command = new MySqlCommand(sql, connection))
{
// 添加参数
command.Parameters.AddRange(parameters);
// 打开连接
connection.Open();
// 执行SQL语句并返回影响行数
return command.ExecuteNonQuery();
}
}
}
//
/// <summary>
/// 执行一个查询，并返回结果集中第一行的第一列
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public object ExecuteScalar(string sql, params MySqlParameter[] parameters)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
using (MySqlCommand command = new MySqlCommand(sql, connection))
{
// 添加参数
command.Parameters.AddRange(parameters);
// 打开连接
connection.Open();
// 执行SQL查询并返回第一行第一列的值
return command.ExecuteScalar();
}
}
}
// 执行一个查询，并返回结果集
public DataTable ExecuteQuery(string sql, params MySqlParameter[] parameters)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
using (MySqlCommand command = new MySqlCommand(sql, connection))
{
// 添加参数
command.Parameters.AddRange(parameters);
// 打开连接
connection.Open();
// 创建DataAdapter和DataTable对象，并填充数据
using (MySqlDataAdapter adapter = new MySqlDataAdapter(command))
{
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
return dataTable;
}
}
}
}
// 执行一个查询，并将结果集映射到一个对象列表
public List<T> ExecuteQuery<T>(string sql, Func<IDataRecord, T> selector, params MySqlParameter[] parameters)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
using (MySqlCommand command = new MySqlCommand(sql, connection))
{
// 添加参数
command.Parameters.AddRange(parameters);
// 打开连接
connection.Open();
// 创建DataReader对象并读取数据，将每行数据映射到对象并添加到列表中
using (MySqlDataReader reader = command.ExecuteReader())
{
List<T> list = new List<T>();
while (reader.Read())
{
list.Add(selector(reader));
}
return list;
}
}
}
}
// 向数据库中插入数据
public int Insert(string tableName, Dictionary<string, object> data)
{
string[] columns = new string[data.Count];
object[] values = new object[data.Count];
int i = 0;
foreach (KeyValuePair<string, object> item in data)
{
// 获取列名和值
columns[i] = item.Key;
values[i] = item.Value;
i++;
}
string sql = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", tableName, string.Join(",", columns), "@" + string.Join(",@", columns));
// 将Dictionary转换为MySqlParameter数组，并执行SQL语句
return ExecuteNonQuery(sql, ToMySqlParameters(data));
}
// 更新数据库中的数据
public int Update(string tableName, Dictionary<string, object> data, string whereClause = "")
{
string[] setValues = new string[data.Count];
int i = 0;
foreach (KeyValuePair<string, object> item in data)
{
// 获取列名和值
setValues[i] = string.Format("{0}=@{0}", item.Key);
i++;
}
string sql = string.Format("UPDATE {0} SET {1}", tableName, string.Join(",", setValues));
if (!string.IsNullOrEmpty(whereClause))
{
sql += " WHERE " + whereClause;
}
// 将Dictionary转换为MySqlParameter数组，并执行SQL语句
return ExecuteNonQuery(sql, ToMySqlParameters(data));
}
// 删除数据库中的数据
public int Delete(string tableName, string whereClause = "")
{
string sql = string.Format("DELETE FROM {0}", tableName);
if (!string.IsNullOrEmpty(whereClause))
{
sql += " WHERE " + whereClause;
}
// 执行SQL语句并返回影响
return ExecuteNonQuery(sql);
}
// 将Dictionary转换为MySqlParameter数组
private MySqlParameter[] ToMySqlParameters(Dictionary<string, object> data)
{
List<MySqlParameter> parameters = new List<MySqlParameter>();
foreach (KeyValuePair<string, object> item in data)
{
parameters.Add(new MySqlParameter("@" + item.Key, item.Value));
}
return parameters.ToArray();
}
}
首先
private static string consql = "server=localhost;user=root;password=root;database=test;port=3306;;Charset=utf8;";
private MySQLHelper mySQLHelper = new MySQLHelper(consql);
然后
//string sql = "CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET utf8 COLLATE utf8_general_ci;";
//mySQLHelper.ExecuteNonQuery(sql);
//string sql = "SELECT * FROM `ssapp`.`data_account` where status=@status order by id desc;";
//var aaa = mySQLHelper.ExecuteScalar(sql,new MySqlParameter("@status",1));
string sql = "SELECT * FROM `data_account` where status=@status order by id desc;";
//var aa = mySQLHelper.ExecuteQuery(sql,new MySqlParameter("@status", 1));
var aa = mySQLHelper.ExecuteQuery(sql, r => new Info
{
gname = r["gname"].ToString(),
id = int.Parse(r["id"].ToString())
}, new MySqlParameter("@status", 1));
//两种方法都可以
try
{
var bb = mySQLHelper.ExecuteQuery(sql, new MySqlParameter("@status", 1));
List<Info> cc = new List<Info>();
foreach (DataRow info in bb.Rows)
{
cc.Add(ToModel.DataRowToModel<Info>(info));
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
//throw;
}
文中Info
private class Info
{
public int id { get; set; }
public string gname { get; set; }
}
Tmodel
public static class ToModel
{
public static Tmodel DataRowToModel<Tmodel>(this DataRow dr)
{
Type type = typeof(Tmodel);
Tmodel md = (Tmodel)Activator.CreateInstance(type);
foreach (var prop in type.GetProperties())
{
object value = dr[prop.Name].ToString().Trim();
if (value != DBNull.Value)
prop.SetValue(md, dr[prop.Name]);
}
return md;
}
}
评论 (0)