class MysqlHelper : IDBHelper
{
/// <summary>
/// 连接字符串
/// </summary>
private string connString;
/// <summary>
/// 数据库连接对象
/// </summary>
private MySqlConnection conn;
/// <summary>
/// MySqlDataReader
/// </summary>
private MySqlDataReader reader;
private MySqlCommand command;
/// <summary>
/// 初始化
/// </summary>
public MysqlHelper()
{
this.connString = InitConnString();//初始化连接字符串
}
/// <summary>
/// 获取数据库连接
/// </summary>
/// <returns></returns>
public void GetConn()
{
MySqlConnection conn = null;
try
{
//判断连接字符串时候已经准备好
if (this.connString.Length == 0)
{
this.InitConnString();
}
this.conn = new MySqlConnection(this.connString);
this.conn.Open();
}
catch (MySqlException ex)
{
throw ex;
}
}
/// <summary>
/// 初始化连接字符串
/// </summary>
/// <returns></returns>
public string InitConnString()
{
string connString = "";
connString += "server=" + ConfigurationManager.AppSettings.Get("ip");
connString += ";uid=" + ConfigurationManager.AppSettings.Get("user");
connString += ";pwd=" + ConfigurationManager.AppSettings.Get("pwd");
connString += ";database=" + ConfigurationManager.AppSettings.Get("database");
connString += ";CharSet=" + ConfigurationManager.AppSettings.Get("encoding");
return connString;
}
/// <summary>
/// 执行插入操作
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="placeHolders">占位符集合</param>
/// <param name="param">代替占位符的数据（顺序须和占位符顺序一致）</param>
/// <param name="returnFieldName">返回列名</param>
/// <returns>插入后的主键ID值</returns>
public int ExcuteInsert(string sql, string[] placeHolders, object[] param, string returnFieldName)
{
int returnInt = 0;
try
{
command = new MySqlCommand(sql, conn);
int paramsNum = placeHolders.Count();
for (int i = 0; i < paramsNum; i++)
{
command.Parameters.AddWithValue(placeHolders[i], param[i]);
}
if (reader != null)
{
reader.Close();
}
reader = command.ExecuteReader();
while (reader.Read())
{
returnInt = reader.GetInt32(returnFieldName);
}
}
catch (Exception ex)
{
throw new MysqlException(ex.Message, ex);
}
return returnInt;
}
/// <summary>
/// 执行更新删除操作
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="placeHolders">占位符数组</param>
/// <param name="param">占位符对应的值数组（顺序要一致）</param>
/// <returns>影响的行数</returns>
public int ExcuteUpdte(string sql, string[] placeHolders, object[] param)
{
int ret = 0;
try
{
if (reader != null)
{
reader.Close();
}
command = new MySqlCommand(sql, conn);
int paramsNum = placeHolders.Count();
for (int i = 0; i < paramsNum; i++)
{
command.Parameters.AddWithValue(placeHolders[i], param[i]);
}
ret = command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new MysqlException(ex.Message, ex);
}
return ret;
}
/// <summary>
/// 执行查询语句
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="placeHolders">占位符数组</param>
/// <param name="param">占位符对应的值数组（顺序要一致）</param>
/// <returns><code>MySqlDataReader</code></returns>
public MySqlDataReader excuteQuery(string sql, string[] placeHolders, object[] param)
{
try
{
command = new MySqlCommand(sql, conn);
int paramsNum = placeHolders.Count();
for (int i = 0; i < paramsNum; i++)
{
command.Parameters.AddWithValue(placeHolders[i], param[i]);
}
if (reader != null)
{
reader.Close();
}
reader = command.ExecuteReader();
}
catch (Exception ex)
{
throw new MysqlException(ex.Message, ex);
}
return reader;
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void Close()
{
if (this.reader != null && (!this.reader.IsClosed))
{
reader.Close();
}
if (this.conn != null)
{
this.conn.Close();
}
}
}
调用：public class Template
{
private IDBHelper dbHelper = DBFactory.GetDbHelper();//数据库帮助类
/// <summary>
/// 列表查询，返回对应实体对象的列表
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="mapping">实体映射</param>
/// <param name="placeHolders">占位符数组</param>
/// <param name="param">占位符对应的值数组</param>
/// <returns>对应实体的列表</returns>
public IList<object> ListQuery(string sql, IEntityMapping mapping, string[] placeHolders, object[] param)
{
IList<object> retList = new List<object>();
MySqlDataReader reader = null;
try
{
dbHelper.GetConn();
reader = dbHelper.excuteQuery(sql, placeHolders, param);
while (reader.Read())
{
retList.Add(mapping.mapping(reader));
}
}
catch (Exception ex)
{
throw new MysqlException(ex.Message, ex);
}
finally
{
dbHelper.Close();
}
return retList;
}
/// <summary>
/// 获取单个实体对象
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="mapping">实体映射</param>
/// <param name="placeHolders">占位符数组</param>
/// <param name="param">占位符对应的值数组</param>
/// <returns>对应实体的列表</returns>
public object objectQuery(string sql, IEntityMapping mapping, string[] placeHolders, object[] param)
{
try
{
IList<object> objectList = ListQuery(sql, mapping, placeHolders, param);
if (objectList.Count == 1)
{
return objectList[0];
}
else
{
return null;
}
}
catch (Exception ex)
{
throw new MysqlException(ex.Message, ex);
}
finally
{
dbHelper.Close();
}
}
/// <summary>
/// 插入数据
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="mapping">实体映射</param>
/// <param name="placeHolders">占位符数组</param>
/// <param name="param">占位符对应的值数组</param>
/// <param name="returnFieldName">返回列名</param>
/// <returns>插入成功后生成的主键ID</returns>
public int Insert(string sql, IEntityMapping mapping, string[] placeHolders, object[] param, string returnFieldName)
{
int retInt = 0;
try
{
dbHelper.GetConn();
retInt = dbHelper.ExcuteInsert(sql, placeHolders, param, returnFieldName);
}
catch (Exception ex)
{
throw new MysqlException(ex.Message, ex);
}
finally
{
dbHelper.Close();
}
return retInt;
}
/// <summary>
/// 执行更新操作
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="mapping">实体映射</param>
/// <param name="placeHolders">占位符数组</param>
/// <param name="param">占位符对应的值数组</param>
/// <returns>影响的行数</returns>
public int update(string sql, IEntityMapping mapping, string[] placeHolders, object[] param)
{
int retInt = 0;
try
{
dbHelper.GetConn();
retInt = dbHelper.ExcuteUpdte(sql, placeHolders, param);
}
catch (Exception ex)
{
throw new MysqlException(ex.Message, ex);
}
finally
{
dbHelper.Close();
}
return retInt;
}
}
mapping例子：
/// <summary>
/// 入口地址参数映射实现
/// </summary>
public class EntryAddrParamsMapping : IEntityMapping
{
public object mapping(MySqlDataReader reader)
{
int i = 0;
return new EntryUrlParams(reader.GetInt32(i++), reader.GetInt32(i++),
reader.GetInt32(i++), reader.GetInt32(i++), reader.GetFloat(i++));
}
}