看别人发了个DBHelper，我也发一个自己用的。

Question

如果需要使用MySql，请把注释打开。好久好久都不写代码了。这是很久之前用的，大家凑合看吧。或许能有些帮助！using System;
using System.Collections;
using System.Data;
using System.Data.Common;
//using MySql.Data.MySqlClient;
//using MySql.Data;
using System.Reflection;namespace I5iw.Lib
{
    #region 数据访问助手类  For NET2.0
    /// <summary>
    /// 数据访问助手类 For NET2.0 by PHF
    /// 本类支持常见的数据库类型。支持范围参见
    /// DbHelper.DataProviderType
    /// 一个填充数据集的例子
    /// </summary>
    /// <example> 一个填充数据集的例子.
    /// <code>
    ///private void button1_Click(object sender, EventArgs e)
    ///    {
    ///        string connStr = "server=127.0.0.1; user id=sa; pwd=;database=pubs";
    ///        //DbHelper dbHelper = new DbHelper(connStr);一个参数的构造函数
    ///        DbHelper dbHelper = new DbHelper(DbHelper.DataProviderType.SqlServer, connStr);
    ///        ds = dbHelper.GetDataSetWithSql(dbHelper.ConnString, "SELECT * FROM titles", null);
    ///        dataGridView1.DataSource = ds.Tables[0];
    ///    }
    /// </code>
    /// </example>
    /// <res>
    /// 备注
    /// </res>    public class DbHelper
    {
        /// <summary>
        /// 全局连接器
        /// </summary>
        DbConnection G_connection = null;
        /// <summary>
        /// 全局连接器
        /// </summary>
        public DbConnection Connection
        {
            get { return G_connection; }
        }        /// <summary>
        /// 全局命令行
        /// </summary>
        DbCommand G_command = null;
        /// <summary>
        /// 全局命令构造器
        /// </summary>
        DbCommandBuilder G_commandBuilder = null;
        /// <summary>
        /// 全局数据适配器
        /// </summary>
        DbDataAdapter G_dataAdapter = null;        private string connString = "server=127.0.0.1; user id=sa; pwd=;database=pubs";
        
        private string errorText = string.Empty;
        /// <summary>
        /// 错误信息属性
        /// </summary>
        public string ErrorText
        {
            get { return errorText; }            
        }        /// <summary>
        /// 连接字符串
        /// </summary>
        public string ConnString
        {
            get { return connString; }
            set { connString = value; }
        }
        #region 私有变量
        /// <summary>
        /// DBHelper支持的数据库类型集合
        /// </summary>
        public enum DataProviderType
        {
            /// <summary>
            /// sqlServer类型
            /// 这个就不用废话了
            /// </summary>
            SqlServer,
            /// <summary>
            /// access类型
            /// 这个就不用废话了
            /// </summary>
            Access,
            /// <summary>
            /// 适用于 Oracle 数据源
            /// 支持 Oracle 客户端软件 8.1.7 和更高版本
            /// </summary>
            Oracle,
            /// <summary>
            /// 提供对使用 ODBC 公开的数据源中数据的访问
            /// </summary>
            Odbc,
            /// <summary>
            /// 提供对使用 OLE DB 公开的数据源中数据的访问
            /// </summary>
            OleDb,
            /// <summary>
            /// 可以创建能部署在桌面计算机、
            /// 智能设备和 Tablet PC 上的压缩数据库
            /// 3.5版本
            /// </summary>
            SqlServerCe,
            /// <summary>
            /// MySql数据库
            /// </summary>
            MySql,
            /// <summary>
            /// IBM的Db2数据库
            /// </summary>
            DB2
        }
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        protected string m_connectionstring = null;
        /// <summary>
        /// 数据库类型(.net可识别的类型)
        /// </summary>
        private string dbType = string.Empty;        /// <summary>
        /// 数据库类型(.net可识别的类型)
        /// </summary>
        public string DbType
        {
            get { return dbType; }
        }        /// <summary>
        /// DbProviderFactory实例
        /// </summary>
        private DbProviderFactory m_factory = null;
        /// <summary>
        /// 查询次数统计
        /// </summary>
        private int m_querycount = 0;
        /// <summary>
        /// Parameters缓存哈希表
        /// </summary>
        private Hashtable m_paramcache = Hashtable.Synchronized(new Hashtable());
        private object lockHelper = new object();        #endregion        #region 属性        /// <summary>
        /// 查询次数统计
        /// </summary>
        public int QueryCount
        {
            get { return m_querycount; }
            set { m_querycount = value; }
        }        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public string ConnectionString
        {
            get
            {
                return m_connectionstring;
            }
            set
            {
                m_connectionstring = value;
            }
        }

Answer 1

        /// <summary>
        /// DbFactory实例
        /// </summary>
        public DbProviderFactory Factory
        {
            get { return m_factory; }
        }
        #endregion        #region 构造函数
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="dataProviderType">数据库类型</param>
        /// <param name="connString">连接字符串</param>
        public DbHelper(DataProviderType dataProviderType, string connString)
        {
            dbType = dataProviderType.ToString();
            string ole_str = string.Empty;
            switch (dataProviderType)
            {
                case DataProviderType.SqlServer:
                    {
                        m_factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
                        break;
                    }
                case DataProviderType.Access:
                    {
                        m_factory = DbProviderFactories.GetFactory("System.Data.OleDb");
                        break;
                    }
                case DataProviderType.Oracle:
                    {
                        try
                        {   
                            //.Net自带的链接方式有问题，只好使用oledb
                            this.connString = connString;
                            this.ConnString = connString;
                            ConnectionString = connString;
                            G_connection = new System.Data.OleDb.OleDbConnection(connString);
                            G_connection.Open();
                            G_command = new System.Data.OleDb.OleDbCommand();
                            G_commandBuilder = new System.Data.OleDb.OleDbCommandBuilder();
                            G_dataAdapter = new System.Data.OleDb.OleDbDataAdapter();
                            return;
                        }
                        catch (Exception exp)
                        {
                            errorText = exp.Message;
                        }
                        break;
                    }
                case DataProviderType.Odbc:
                    {
                        m_factory = DbProviderFactories.GetFactory("System.Data.Odbc");
                        break;
                    }
                case DataProviderType.OleDb:
                    {
                        m_factory = DbProviderFactories.GetFactory("System.Data.OleDb");
                        break;
                    }
                case DataProviderType.SqlServerCe:
                    {
                        m_factory = DbProviderFactories.GetFactory("System.Data.SqlServerCe.3.5");
                        break;
                    }
                case DataProviderType.MySql:
                    {
                        //由于未知原因，所以不用再通过  getConnection 获取连接。                        
                        //m_factory = DbProviderFactories.GetFactory("MySql.Data.MySqlClient");
                        //break;
                        //this.connString = connString;
                        //this.ConnString = connString;
                        //ConnectionString = connString;                        //G_connection = new MySql.Data.MySqlClient.MySqlConnection(ConnString);
                        //G_connection.Open();
                        //G_command = new MySqlCommand();
                        //G_commandBuilder = new MySqlCommandBuilder();
                        //G_dataAdapter = new MySqlDataAdapter();                                                return;
                    }
                case DataProviderType.DB2:
                    {
                        try
                        {
                            this.connString = connString;
                            this.ConnString = connString;
                            ConnectionString = connString;
                            //m_factory = DbProviderFactories.GetFactory("MySql.Data.MySqlClient");
                            //break;
                            //如果是32位系统
                            if (CommandTools.PlatformSize() < 64)
                            {
                                if (!System.IO.File.Exists(@"IBM.Data.DB2.dll"))
                                {
                                    return;
                                }
                                Assembly assembly = Assembly.LoadFrom(@"IBM.Data.DB2.dll");
                                Type DB2Connection = assembly.GetType("IBM.Data.DB2.DB2Connection");
                                Type DB2Command = assembly.GetType("IBM.Data.DB2.DB2Command");
                                Type DB2CommandBuilder = assembly.GetType("IBM.Data.DB2.DB2CommandBuilder");
                                Type DB2DataAdapter = assembly.GetType("IBM.Data.DB2.DB2DataAdapter");                                G_connection =(DbConnection) Activator.CreateInstance(DB2Connection, new string[] { connString });                                
                                G_connection.Open();
                                G_command = (DbCommand)Activator.CreateInstance(DB2Command); ;
                                G_commandBuilder =(DbCommandBuilder)Activator.CreateInstance(DB2CommandBuilder); 
                                G_dataAdapter = (DbDataAdapter)Activator.CreateInstance(DB2DataAdapter); 
                            }
                            else//如果是64位系统
                            {
                                G_connection = new System.Data.OleDb.OleDbConnection(connString);
                                G_connection.Open();
                                G_command = new System.Data.OleDb.OleDbCommand();
                                G_commandBuilder = new System.Data.OleDb.OleDbCommandBuilder();
                                G_dataAdapter = new System.Data.OleDb.OleDbDataAdapter();
                                //Provider = IBMDADB2; Database = myDataBase; Hostname = myServerAddress; Protocol = TCPIP; Port = 50000; Uid = myUsername; Pwd = myPassword;                            
                            }
                            //由于类型特殊，所以不用再通过  getConnection 获取连接。
                            //break;                            return;                        }
                        catch (Exception exp)
                        {                            throw exp;
                        }
                        break;
                    }                default:
                    m_factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
                    break;
            }            if (connString.Trim() != string.Empty)
            {
                ConnectionString = connString;
                ConnString = connString;
            }
            //如果指定方式无法连接，则转入 oledb方式。  
            if (getConnection() == null)
            {
                try
                {
                    switch (dataProviderType)
                    {
                        case DataProviderType.SqlServer:
                            {
                                string tmp_Uname = I5iw.Lib.Text.GetStrBetween(ConnectionString, "User ID=", ";", false);
                                if (tmp_Uname == "")
                                {
                                    tmp_Uname = "Integrated Security=True";
                                }
                                string tmp_Upass = I5iw.Lib.Text.GetStrBetween(ConnectionString, "Password=", ";", false);
                                string tmp_UdataBase = I5iw.Lib.Text.GetStrBetween(ConnectionString, "Data Source=", ";", false);                                connString = "Provider = sqloledb; Data Source = " + tmp_UdataBase + "; User Id =" + tmp_Uname + "; Password = " + tmp_Upass + ";";
                                ConnectionString = connString;
                                G_connection = new System.Data.OleDb.OleDbConnection(connString);
                                G_connection.Open();
                                G_command = new System.Data.OleDb.OleDbCommand();
                                G_commandBuilder = new System.Data.OleDb.OleDbCommandBuilder();
                                G_dataAdapter = new System.Data.OleDb.OleDbDataAdapter();
                                break;
                            }
                    }           
                }
                catch (Exception exp)
                {
                    errorText = "共尝试两种方式登录，均未成功！" + Environment.NewLine + "1、" + errorText + Environment.NewLine + "2、" + exp.Message; ;
                    
                    G_connection = null;
                }    
            }        }

Answer 2

/// <summary>
        /// 构造函数(默认使用SqlServer，想更改驱动类型请使用含有两个参数的构造函数
        /// public DbHelper(DataProviderType dataProviderType, string connString))
        /// </summary>
        /// <param name="connString">连接字符串</param>
        public DbHelper(string connString)
        {
            dbType = "SqlServer";
            m_factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
            if (connString.Trim() != string.Empty)
                if (connString.Trim() != string.Empty)
                {
                    ConnectionString = connString;
                    ConnString = connString;
                }
            //如果SqlClient方式无法连接，则转入 oledb方式。
            if (getConnection() == null)
            {
                m_factory = DbProviderFactories.GetFactory("System.Data.OleDb");
                if (connString.Trim() != string.Empty)
                    if (connString.Trim() != string.Empty)
                    {
                        ConnectionString = connString;
                        ConnString = connString;
                        G_connection = Factory.CreateConnection();
                        G_connection.ConnectionString = connString;
                        G_command = Factory.CreateCommand();
                        G_commandBuilder = Factory.CreateCommandBuilder();
                        G_dataAdapter = Factory.CreateDataAdapter();
                    }
            }
            //开始根据字符串判断数据库类型。仅为有限枚举。
        }
        #endregion        #region  数据库操作方法(全静态) 
        //------------------------------------------------------------------------------
        // 存储参数的哈希表（暂时不用）
        private Hashtable parmCache = Hashtable.Synchronized(new Hashtable());        #region  返回数据库连接对象
        /// <summary>
        /// 数据库连接对象
        /// </summary>
        /// <returns></returns>
        public DbConnection getConnection()
        {
            errorText = string.Empty;
            try
            {                
                if ((G_connection == null)&&(m_factory!=null))
                {
                    G_connection = Factory.CreateConnection();
                    G_connection.ConnectionString = connString;
                    G_command = Factory.CreateCommand();
                    G_commandBuilder = Factory.CreateCommandBuilder();
                    G_dataAdapter = Factory.CreateDataAdapter();
                }
                return G_connection;
            }
            catch (Exception exp)
            {
                //return null;                
                errorText = exp.Message;
                return null; 
            }        }
        #endregion        #region 测试是否能够连接
        /// <summary>
        /// 数据库是否可以正常连接
        /// </summary>
        /// <returns></returns>
        public bool IsConnection()
        {
            try
            {
                if (Connection.State == ConnectionState.Open)
                {                   
                    return true;
                }
                else
                {
                    Connection.Open();
                    if (Connection.State == ConnectionState.Open)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
            }
            catch(Exception exp)
            {
                errorText = exp.Message;
                return false;
            }
            finally
            {
                Connection.Close();
            }        }
        #endregion

Answer 3

 #region 参数处理，分解参数列表
        #region 参数处理
        /// <summary>
        /// 对传入的参数列表进行预处理（DbConnection）
        /// </summary>
        /// <param name="cmd">命令执行对象</param>
        /// <param name="conn">数据库连接对象</param>
        /// <param name="trans">事务管理对象</param>
        /// <param name="cmdType">命令执行方式（sql执行还是存储过程）</param>
        /// <param name="procName">命令执行语句.例如 Select * from Products</param>
        /// <param name="cmdParms">命令参数</param>
        private void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType cmdType, string cmdText, DbParameter[] cmdParms)
        {            if (conn != null)
            {
                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 (DbParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
           // cmd.Connection.Close();
        }
        #endregion        #region 参数处理
        /// <summary>
        /// 对传入的参数进行处理(connString)
        /// </summary>
        /// <param name="cmd">DbCommand对象</param>
        /// <param name="connString">连接字符串</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="selectText">命令语句</param>
        /// <param name="cmdParms">参数列表</param>
        private void PrepareCommand(DbCommand cmd, string connectionString, CommandType cmdType, string cmdText, DbParameter[] cmdParms)
        {
            try
            {                if (G_connection != null)
                {
                    G_connection.Close();
                    G_connection.ConnectionString = ConnectionString;
                    G_connection.Open();                    this.G_command.Connection = G_connection;
                }                G_command.CommandText = cmdText;                G_command.CommandType = cmdType;                if (cmdParms != null)
                {
                    foreach (DbParameter parm in cmdParms)
                        G_command.Parameters.Add(parm);
                }
            }
            catch (Exception)
            {                this.G_connection.Close();
                //connection.Dispose();
            }
            finally
            {
                //cmd.Connection.Close();
            }
        }
        #endregion        #region 参数处理
        /// <summary>
        /// 对传入的参数进行处理
        /// </summary>
        /// <param name="cmd">DbCommand对象</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="procName">sql语句</param>
        /// <param name="cmdParms">参数列表</param>
        private void PrepareCommand(DbCommand cmd, CommandType cmdType, string cmdText, DbParameter[] cmdParms)
        {            cmd.CommandText = cmdText;            cmd.CommandType = cmdType;            if (cmdParms != null)
            {
                foreach (DbParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
        #endregion        #region 参数处理
        /// <summary>
        /// 对传入的参数进行处理
        /// </summary>
        /// <param name="cmd">DbCommand对象</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdParms">参数列表</param>
        private void PrepareCommand(DbCommand cmd, CommandType cmdType, DbParameter[] cmdParms)
        {            cmd.CommandType = cmdType;            if (cmdParms != null)
            {
                foreach (DbParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
        #endregion        #endregion

Answer 4

现在有些框架都不用写sql语句了，lz可以研究下。提一点意见
参数化sql语句和读取结果用反射全自动化，编码效率可以提高很多。

Answer 5

还是使用了很多的第三方dllIBM.Data.DB2.dll
...

Answer 6

贴代码太麻烦了。上传到这里了。
http://download.csdn.net/detail/l0f/4263392