public class MySQLDBHelper
{
private string connString = "";
public MySQLDBHelper() { }
public MySQLDBHelper(string connStr)
{
connString = connStr;
}
public IDbConnection Connection()
{
var conn = new MySqlConnection(connString);
conn.Open();
return conn;
}
#region Dapper
#region +ExcuteNonQuery 增、删、改同步操作
/// <summary>
/// 增、删、改同步操作
/// </summary>
/// <param name="cmd">sql语句</param>
/// <param name="param">参数</param>
/// <param name="flag">true存储过程,false sql语句</param>
/// <returns>int</returns>
public int ExcuteNonQuery(string cmd, object param=null, bool flag = false)
{
string connection = connString;
int result = 0;
using (MySqlConnection con = new MySqlConnection(connection))
{
if (flag)
{
result = con.Execute(cmd, param, null, null, CommandType.StoredProcedure);
}
else
{
result = con.Execute(cmd, param, null, null, CommandType.Text);
}
}
return result;
}
#endregion
#region +QueryData 同步查询数据集合
/// <summary>
/// 同步查询数据集合
/// </summary>
/// <param name="cmd">sql语句</param>
/// <param name="param">参数</param>
/// <param name="flag">true存储过程,false sql语句</param>
/// <returns>t</returns>
public List<Dictionary<String,object>> QueryData(string cmd, object param=null, bool flag = false)
{
string connection = connString;
IDataReader dataReader = null;
using (MySqlConnection con = new MySqlConnection(connection))
{
if (flag)
{
dataReader = con.ExecuteReader(cmd, param, null, null, CommandType.StoredProcedure);
}
else
{
dataReader = con.ExecuteReader(cmd, param, null, null, CommandType.Text);
}
List<Dictionary<String, object>> list = new List<Dictionary<string, object>>();
Dictionary<String, object> dic = null;
string colName = "";
while (dataReader.Read())
{
dic = new Dictionary<string, object>();
for (int i = 0; i < dataReader.FieldCount; i++)
{
colName = dataReader.GetName(i);
dic.Add(colName, dataReader[colName]);
}
if (dic.Keys.Count > 0)
{
list.Add(dic);
}
}
return list;
}
}
#endregion
#region +ExecuteScalar 同步查询操作
/// <summary>
/// 同步查询操作
/// </summary>
/// <param name="cmd">sql语句</param>
/// <param name="param">参数</param>
/// <param name="flag">true存储过程,false sql语句</param>
/// <returns>object</returns>
public object ExecuteScalar(string cmd,object param=null, bool flag = false)
{
string connection = connString;
object result = null;
using (MySqlConnection con = new MySqlConnection(connection))
{
if (flag)
{
result = con.ExecuteScalar(cmd, param, null, null, CommandType.StoredProcedure);
}
else
{
result = con.ExecuteScalar(cmd, param, null, null, CommandType.Text);
}
}
return result;
}
#endregion
#region +QueryPage 同步分页查询操作
/// <summary>
/// 同步分页查询操作
/// </summary>
/// <param name="sql">查询语句</param>
/// <param name="orderBy">排序字段</param>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">页面容量</param>
/// <param name="count">总条数</param>
/// <param name="param">参数</param>
/// <param name="strWhere">条件</param>
/// <returns>返回结果的数据集合</returns>
public List<Dictionary<string, Object>> QueryPage(string sql, string orderBy, int pageIndex, int pageSize, out int count, object param = null, string strWhere = "")
{
count = 0;
List<Dictionary<String, Object>> list = new List<Dictionary<string, object>>();
if (sql.Contains("where"))
{
sql = sql + strWhere;
}
else
{
sql = sql + " where 1=1 " + strWhere;
}
string strSQL = "SELECT (@i:=@i+1) AS row_id,tab.* FROM (" + sql + ") AS TAB,(SELECT @i:=0) AS it ORDER BY " + orderBy + " LIMIT " + (pageIndex - 1) + "," + pageSize;
list = QueryData(strSQL, param, false);
string strCount = "SELECT count(*) FROM (" + sql + ") tcount";
count = Convert.ToInt32(ExecuteScalar(strCount));
return list;
}
#endregion
#endregion
}
{
private string connString = "";
public MySQLDBHelper() { }
public MySQLDBHelper(string connStr)
{
connString = connStr;
}
public IDbConnection Connection()
{
var conn = new MySqlConnection(connString);
conn.Open();
return conn;
}
#region Dapper
#region +ExcuteNonQuery 增、删、改同步操作
/// <summary>
/// 增、删、改同步操作
/// </summary>
/// <param name="cmd">sql语句</param>
/// <param name="param">参数</param>
/// <param name="flag">true存储过程,false sql语句</param>
/// <returns>int</returns>
public int ExcuteNonQuery(string cmd, object param=null, bool flag = false)
{
string connection = connString;
int result = 0;
using (MySqlConnection con = new MySqlConnection(connection))
{
if (flag)
{
result = con.Execute(cmd, param, null, null, CommandType.StoredProcedure);
}
else
{
result = con.Execute(cmd, param, null, null, CommandType.Text);
}
}
return result;
}
#endregion
#region +QueryData 同步查询数据集合
/// <summary>
/// 同步查询数据集合
/// </summary>
/// <param name="cmd">sql语句</param>
/// <param name="param">参数</param>
/// <param name="flag">true存储过程,false sql语句</param>
/// <returns>t</returns>
public List<Dictionary<String,object>> QueryData(string cmd, object param=null, bool flag = false)
{
string connection = connString;
IDataReader dataReader = null;
using (MySqlConnection con = new MySqlConnection(connection))
{
if (flag)
{
dataReader = con.ExecuteReader(cmd, param, null, null, CommandType.StoredProcedure);
}
else
{
dataReader = con.ExecuteReader(cmd, param, null, null, CommandType.Text);
}
List<Dictionary<String, object>> list = new List<Dictionary<string, object>>();
Dictionary<String, object> dic = null;
string colName = "";
while (dataReader.Read())
{
dic = new Dictionary<string, object>();
for (int i = 0; i < dataReader.FieldCount; i++)
{
colName = dataReader.GetName(i);
dic.Add(colName, dataReader[colName]);
}
if (dic.Keys.Count > 0)
{
list.Add(dic);
}
}
return list;
}
}
#endregion
#region +ExecuteScalar 同步查询操作
/// <summary>
/// 同步查询操作
/// </summary>
/// <param name="cmd">sql语句</param>
/// <param name="param">参数</param>
/// <param name="flag">true存储过程,false sql语句</param>
/// <returns>object</returns>
public object ExecuteScalar(string cmd,object param=null, bool flag = false)
{
string connection = connString;
object result = null;
using (MySqlConnection con = new MySqlConnection(connection))
{
if (flag)
{
result = con.ExecuteScalar(cmd, param, null, null, CommandType.StoredProcedure);
}
else
{
result = con.ExecuteScalar(cmd, param, null, null, CommandType.Text);
}
}
return result;
}
#endregion
#region +QueryPage 同步分页查询操作
/// <summary>
/// 同步分页查询操作
/// </summary>
/// <param name="sql">查询语句</param>
/// <param name="orderBy">排序字段</param>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">页面容量</param>
/// <param name="count">总条数</param>
/// <param name="param">参数</param>
/// <param name="strWhere">条件</param>
/// <returns>返回结果的数据集合</returns>
public List<Dictionary<string, Object>> QueryPage(string sql, string orderBy, int pageIndex, int pageSize, out int count, object param = null, string strWhere = "")
{
count = 0;
List<Dictionary<String, Object>> list = new List<Dictionary<string, object>>();
if (sql.Contains("where"))
{
sql = sql + strWhere;
}
else
{
sql = sql + " where 1=1 " + strWhere;
}
string strSQL = "SELECT (@i:=@i+1) AS row_id,tab.* FROM (" + sql + ") AS TAB,(SELECT @i:=0) AS it ORDER BY " + orderBy + " LIMIT " + (pageIndex - 1) + "," + pageSize;
list = QueryData(strSQL, param, false);
string strCount = "SELECT count(*) FROM (" + sql + ") tcount";
count = Convert.ToInt32(ExecuteScalar(strCount));
return list;
}
#endregion
#endregion
}