243 lines
8.1 KiB
C#
243 lines
8.1 KiB
C#
using MySql.Data.MySqlClient;
|
||
using System;
|
||
using System.Collections.Generic;
|
||
using System.Configuration;
|
||
using System.Data;
|
||
using System.Text;
|
||
using System.Text.RegularExpressions;
|
||
|
||
namespace BLL.Helper
|
||
{
|
||
public class MySqlHelper
|
||
{
|
||
public string _connectionString = ConfigurationManager.ConnectionStrings["zxdContext"].ToString();
|
||
|
||
|
||
|
||
|
||
private MySqlParameter[] GetParameterByany(dynamic parameters)
|
||
{
|
||
if (parameters == null)
|
||
return null;
|
||
|
||
var ilistStr = new List<MySqlParameter>();
|
||
if (parameters is IDictionary<string, object>)
|
||
{
|
||
foreach (var kv in (parameters as IDictionary<string, object>))
|
||
{
|
||
//当数值为空时，参数值不去掉
|
||
ilistStr.Add(new MySqlParameter { ParameterName = "@" + kv.Key, Value = kv.Value });
|
||
}
|
||
}
|
||
else
|
||
{
|
||
var piList = parameters.GetType().GetProperties();
|
||
foreach (var p in piList)
|
||
{
|
||
//当数值为空时，参数值不去掉
|
||
ilistStr.Add(new MySqlParameter { ParameterName = "@" + p.Name, Value = p.GetValue(parameters, null) });
|
||
}
|
||
}
|
||
|
||
var spList = ilistStr.ToArray();
|
||
foreach (MySqlParameter p in spList)
|
||
{
|
||
if (p.Value == null)
|
||
{
|
||
p.Value = DBNull.Value;
|
||
}
|
||
}
|
||
return spList;
|
||
}
|
||
|
||
|
||
#region inner
|
||
|
||
/// <summary>
|
||
/// 给定连接的数据库用假设参数执行一个sql命令（不返回数据集）
|
||
/// </summary>
|
||
/// <param name="connectionString">一个有效的连接字符串</param>
|
||
/// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
|
||
/// <param name="cmdSql">存储过程名称或者sql命令语句</param>
|
||
/// <param name="commandParameters">执行命令所用参数的集合</param>
|
||
/// <returns>执行命令所影响的行数</returns>
|
||
protected int _ExecuteNonQuery(CommandType cmdType, string cmdSql, params MySqlParameter[] commandParameters)
|
||
{
|
||
|
||
MySqlCommand cmd = new MySqlCommand();
|
||
using (MySqlConnection conn = new MySqlConnection(_connectionString))
|
||
{
|
||
_PrepareCommand(cmd, conn, null, cmdType, cmdSql, commandParameters);
|
||
int val = cmd.ExecuteNonQuery();
|
||
cmd.Parameters.Clear();
|
||
return val;
|
||
}
|
||
}
|
||
/// <summary>
|
||
/// 准备执行一个命令
|
||
/// </summary>
|
||
/// <param name="cmd">sql命令</param>
|
||
/// <param name="conn">OleDb连接</param>
|
||
/// <param name="trans">OleDb事务</param>
|
||
/// <param name="cmdType">命令类型例如 存储过程或者文本</param>
|
||
/// <param name="cmdText">命令文本,例如:Select * from Products</param>
|
||
/// <param name="cmdParms">执行命令的参数</param>
|
||
protected void _PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)
|
||
{
|
||
|
||
if (conn.State != ConnectionState.Open)
|
||
conn.Open();
|
||
|
||
cmd.Connection = conn;
|
||
cmd.CommandText = cmdText;
|
||
|
||
if (trans != null)
|
||
cmd.Transaction = trans;
|
||
|
||
cmd.CommandType = cmdType;
|
||
|
||
if (cmdParms != null)
|
||
{
|
||
foreach (MySqlParameter parm in cmdParms)
|
||
cmd.Parameters.Add(parm);
|
||
}
|
||
}
|
||
|
||
|
||
#endregion
|
||
|
||
public int ExecuteNonQuery(string sql, dynamic parameters)
|
||
{
|
||
try
|
||
{
|
||
return _ExecuteNonQuery(System.Data.CommandType.Text, sql, parameters);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
|
||
}
|
||
return -1;
|
||
}
|
||
|
||
|
||
/// <summary>
|
||
/// 返回DataSet
|
||
/// </summary>
|
||
/// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
|
||
/// <param name="cmdText">存储过程名称或者sql命令语句</param>
|
||
/// <param name="commandParameters">执行命令所用参数的集合</param>
|
||
/// <returns></returns>
|
||
protected DataSet _ExecuteDataSet(CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
|
||
{
|
||
//创建一个MySqlCommand对象
|
||
MySqlCommand cmd = new MySqlCommand();
|
||
using (MySqlConnection conn = new MySqlConnection(_connectionString))
|
||
{
|
||
//调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数
|
||
_PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
|
||
//调用 MySqlCommand 的 ExecuteReader 方法
|
||
MySqlDataAdapter adapter = new MySqlDataAdapter();
|
||
adapter.SelectCommand = cmd;
|
||
DataSet ds = new DataSet();
|
||
|
||
adapter.Fill(ds);
|
||
//清除参数
|
||
cmd.Parameters.Clear();
|
||
conn.Close();
|
||
return ds;
|
||
}
|
||
}
|
||
|
||
|
||
|
||
|
||
/// <summary>
|
||
/// DataTable 转换成 List
|
||
/// </summary>
|
||
/// <typeparam name="T"></typeparam>
|
||
/// <param name="table"></param>
|
||
/// <returns></returns>
|
||
public List<T> DataTableToList<T>(DataTable table) where T : new()
|
||
{
|
||
var listT = new List<T>();
|
||
var list = typeof(T).GetProperties();
|
||
var dataColumns = table.Columns;
|
||
for (var i = 0; i < table.Rows.Count; i++)
|
||
{
|
||
var model = new T();
|
||
foreach (var p in list)
|
||
{
|
||
if (dataColumns.Contains(p.Name) && table.Rows[i][p.Name] != DBNull.Value)
|
||
{
|
||
try
|
||
{
|
||
var obj = Convert.ChangeType(table.Rows[i][p.Name], p.PropertyType);
|
||
p.SetValue(model, obj, null);
|
||
}
|
||
catch (Exception)
|
||
{
|
||
p.SetValue(model, table.Rows[i][p.Name], null);
|
||
}
|
||
}
|
||
}
|
||
listT.Add(model);
|
||
}
|
||
return listT;
|
||
}
|
||
|
||
public IList<IDictionary<string, object>> QueryDicList(string sql, dynamic paramobj)
|
||
{
|
||
IList<IDictionary<string, object>> result = new List<IDictionary<string, object>>();
|
||
try
|
||
{
|
||
if (paramobj == null)
|
||
{
|
||
return SelectDicList(sql, null);
|
||
}
|
||
else
|
||
{
|
||
return SelectDicList(sql, GetParameterByany(paramobj));
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
}
|
||
return result;
|
||
}
|
||
|
||
|
||
public IList<IDictionary<string, object>> SelectDicList(string sql, MySqlParameter[] parameters)
|
||
{
|
||
IList<IDictionary<string, object>> result = new List<IDictionary<string, object>>();
|
||
var ds = _ExecuteDataSet(System.Data.CommandType.Text, sql, parameters);
|
||
//格式转换
|
||
if (ds.Tables.Count > 0)
|
||
{
|
||
var table = ds.Tables[0];
|
||
var columns = table.Columns;
|
||
foreach (DataRow row in table.Rows)
|
||
{
|
||
IDictionary<string, object> r = new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);//不区分大小写
|
||
foreach (var col in columns)
|
||
{
|
||
var colName = col.ToString();
|
||
if (!r.ContainsKey(colName))
|
||
{
|
||
r.Add(col.ToString(), row[col.ToString()]);
|
||
}
|
||
}
|
||
result.Add(r);
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
}
|
||
}
|