Sqlhelper

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++));
        }
    }