一份关于连接数据库的Util类

自己认认真真总结了一份关于连接数据库的Util类，适用于仍和一种类型的数据库的任何一种Sql语句处理类型，希望大家共同学习，不当的地方望大家不吝赐教：

public class DataTierUtil
    {
        public static bool isInitDatabaseInfo()
        {
            if (Util.isNullStr(DataInfo.DatabaseInfo.DBConnectionString, false))
            {
                return false;
            }
            return true;
        }

        public static void initDatabaseInfo(DataConnType connType, string connString)
        {
            DataInfo.DatabaseInfo.DBConnectionType = connType;
            DataInfo.DatabaseInfo.DBConnectionString = connString;
            DataEngine.InitDataEngine(connType, connString);  
        }

        public static DataSet getDataSet(string strCmd, CommandType cmdType)
        {
            DataSet o = new DataSet() ;
            DbConnection conn = DataConnFactory.instance.newObject(DataInfo.DatabaseInfo.DBConnectionType,
                                                                 DataInfo.DatabaseInfo.DBConnectionString);
            DbDataAdapter adapter = DataAdapterBuilder.buildDataAdapter(DataInfo.DatabaseInfo.DBConnectionType);
            adapter.SelectCommand = buildCommand(null , conn, cmdType, strCmd, null, null);
            switch (DataInfo.DatabaseInfo.DBConnectionType)
            {
                //case DataConnType.OdbcDB:
                //    ((OdbcDataAdapter)adapter).Fill(o);
                //    break;
                //case DataConnType.OleDB:
                //    ((OleDbDataAdapter)adapter).Fill(o);
                //    break;
                case DataConnType.OracleDB:
                    ((OracleDataAdapter)adapter).Fill(o);
                    break;
                case DataConnType.SqlDB:
                    ((SqlDataAdapter)adapter).Fill(o);
                    break;
                case DataConnType.MySqlDB:
                    ((MySqlDataAdapter)adapter).Fill(o);
                    break;
                default:
                    ((OleDbDataAdapter)adapter).Fill(o);
                    break;
            }
            return o;
        }
        public static DataTable PageCache(string PrimaryKey, string DisplayKey, string DataSource, string OrderClause,
                    int PageSize, int PageNo, out int RowCount)
        {
            string[] paras = new string[] { };
            object[] values = new object[] { };
            return PageCache(PrimaryKey, DisplayKey, DataSource, OrderClause, PageSize, PageNo, paras, values, out RowCount);
        }

        public static DataTable PageCache(string PrimaryKey, string DisplayKey, string DataSource, string OrderClause,
            int PageSize, int PageNo, string[] paras, object[] values, out int RowCount)
        {
            DataTable dt = new DataTable();
            string SQL = string.Empty; 
            switch (DataInfo.DatabaseInfo.DBConnectionType)
            {
                case DataConnType.OracleDB:
                    SQL = string.Format("Select {0} from {1}  order by {2}", PrimaryKey, DataSource, OrderClause);
                    break;
                case DataConnType.SqlDB:
                    SQL = string.Format("Select {0} from {1} V order by {2}", PrimaryKey, DataSource, OrderClause);
                    break;
            }
            dt = getDataTable(SQL,CommandType.Text, paras, values);
            RowCount = dt.Rows.Count;    //需要返回总记录数
            //if(RowCount == 0) return null ;
            int lStartRow = PageSize * (PageNo - 1) + 1;
            int lEndRow = lStartRow + PageSize - 1;
            //if (lStartRow > RowCount) return null ;
            if (lEndRow > RowCount) lEndRow = RowCount;
            string WhereClause = string.Empty;
            bool bIn =  dt.Columns[0].DataType == typeof(String) 
                || dt.Columns[0].DataType == typeof(string) 
                ||  dt.Columns[0].DataType==typeof(DateTime);   
            for (int i = lStartRow - 1; i < lEndRow; i++)
            {
                if (bIn)
                {
                    WhereClause = WhereClause + "'" + dt.Rows[i][0] + "',";
                }
                else
                {
                    WhereClause = WhereClause + dt.Rows[i][0] + ",";
                }
            }
            if (WhereClause.Length > 0) 
                WhereClause = WhereClause.Substring(0, WhereClause.Length - 1);
            else
                WhereClause = bIn? "''":"-1";
            int NumIndex = PrimaryKey.IndexOf(".");
            if (NumIndex > 0) PrimaryKey = PrimaryKey.Substring(NumIndex + 1);
            switch (DataInfo.DatabaseInfo.DBConnectionType)
            {
                case DataConnType.OracleDB:
                    SQL = string.Format("Select * from (Select {0} from {1} order by {4}) where {2} in ({3})",
                DisplayKey, DataSource, PrimaryKey, WhereClause, OrderClause);
                    break;
                case DataConnType.SqlDB:
                    SQL = string.Format("Select * from (Select {0} from {1} V1 )V where {2} in ({3})  order by {4}",
                DisplayKey, DataSource, PrimaryKey, WhereClause, OrderClause);
                    break;
            }
            dt = getDataTable(SQL,CommandType.Text, paras, values);
            return dt; //返回当前页的查询记录
        }

        public static DataTable getDataTable(string strCmd, CommandType cmdType)
        {
            return fillDataTable(null, null, null, strCmd, cmdType, null, null);
        }

        public static DataTable getDataTable(string strCmd, CommandType cmdType, string[] paramNames,
                                             object[] paramValues)
        {
            return fillDataTable(null, null, null, strCmd, cmdType, paramNames, paramValues);
        }

        public static DataTable getDataTable(DbTransaction trans, string strCmd, CommandType cmdType)
        {
            return fillDataTable(null, trans, null, strCmd, cmdType, null, null);
        }

        public static DataTable getDataTable(DbTransaction trans, string strCmd, CommandType cmdType,
                                             string[] paramNames, object[] paramValues)
        {
            return fillDataTable(null, trans, null, strCmd, cmdType, paramNames, paramValues);
        }

        public static DataTable getDataTable(DbConnection conn, string strCmd, CommandType cmdType)
        {
            return fillDataTable(null, null, conn, strCmd, cmdType, null, null);
        }

        public static DataTable getDataTable(DbConnection conn, string strCmd, CommandType cmdType, string[] paramNames,
                                             object[] paramValues)
        {
            return fillDataTable(null, null, conn, strCmd, cmdType, paramNames, paramValues);
        }

        public static DataTable fillDataTable(DataTable table, string strCmd, CommandType cmdType)
        {
            return fillDataTable(table, null, null, strCmd, cmdType, null, null);
        }

        public static DataTable fillDataTable(DataTable table, string strCmd, CommandType cmdType, string[] paramNames,
                                              object[] paramValues)
        {
            return fillDataTable(table, null, null, strCmd, cmdType, paramNames, paramValues);
        }

        public static DataTable fillDataTable(DbTransaction trans, DataTable table, string strCmd, CommandType cmdType)
        {
            return fillDataTable(table, trans, null, strCmd, cmdType, null, null);
        }

        public static DataTable fillDataTable(DbTransaction trans, DataTable table, string strCmd, CommandType cmdType,
                                              string[] paramNames, object[] paramValues)
        {
            return fillDataTable(table, trans, null, strCmd, cmdType, paramNames, paramValues);
        }

        public static DataTable fillDataTable(DbConnection conn, DataTable table, string strCmd, CommandType cmdType)
        {
            return fillDataTable(table, null, conn, strCmd, cmdType, null, null);
        }

        public static DataTable fillDataTable(DbConnection conn, DataTable table, string strCmd, CommandType cmdType,
                                              string[] paramNames, object[] paramValues)
        {
            return fillDataTable(table, null, conn, strCmd, cmdType, paramNames, paramValues);
        }

        private static DataTable fillDataTable(DataTable table, DbTransaction trans, DbConnection conn, string strCmd,
                                               CommandType cmdType, string[] paramNames, object[] paramValues)
        {
            bool isNewConn = false;
            if (table == null)
            {
                table = new DataTable();
            }
            try
            {
                if (trans == null)
                {
                    if (conn == null)
                    {
                        isNewConn = true;
                        conn =
                            DataConnFactory.instance.newObject(DataInfo.DatabaseInfo.DBConnectionType,
                                                                 DataInfo.DatabaseInfo.DBConnectionString);
                    }
                }
                else
                {
                    conn = trans.Connection;
                }
                DbDataAdapter adapter = DataAdapterBuilder.buildDataAdapter(DataInfo.DatabaseInfo.DBConnectionType);
                adapter.SelectCommand = buildCommand(trans, conn, cmdType, strCmd, paramNames, paramValues);
                switch (DataInfo.DatabaseInfo.DBConnectionType)
                {
                    //case DataConnType.OdbcDB:
                    //    ((OdbcDataAdapter)adapter).Fill(table);
                    //    break;
                    //case DataConnType.OleDB:
                    //    ((OleDbDataAdapter)adapter).Fill(table);
                    //    break;
                    case DataConnType.OracleDB:
                        ((OracleDataAdapter)adapter).Fill(table);
                        break;
                    case DataConnType.SqlDB:
                        ((SqlDataAdapter)adapter).Fill(table);
                        break;
                    case DataConnType.MySqlDB:
                        ((MySqlDataAdapter)adapter).Fill(table);
                        break;
                    default:
                        ((OleDbDataAdapter)adapter).Fill(table);
                        break;
                }
            }
            catch (Exception ex)
            {
                throw new DataTierException(ex.Message);
            }
            finally
            {
                if (trans == null)
                {
                    if (isNewConn)
                    {
                        DataConnFactory.instance.closeObject(conn);
                    }
                }
            }
            return table;
        }

        private static DbCommand buildCommand(DbTransaction trans, DbConnection conn, CommandType cmdType,
                                               string strCmd, string[] strParams, object[] strValues)
        {
            DbCommand command = conn.CreateCommand();
            switch (DataInfo.DatabaseInfo.DBConnectionType)
            {
                case DataConnType.SqlDB:
                    command.CommandText = strCmd.Replace(":", "@");
                    break;
                case DataConnType.OracleDB:
                    strCmd = strCmd.Replace("'@", "{$}");
                    strCmd = strCmd.Replace("@", ":");
                    command.CommandText = strCmd.Replace("{$}", "'@");
                    command.CommandText = command.CommandText.Replace("#", "@");
                    break;
                //case DataConnType.OleDB:
                //    strCmd = strCmd.Replace("'@", "{$}");
                //    if (strParams != null)
                //    {
                //        for (int i = 0; i < strParams.Length; i++)
                //        {
                //            strCmd = strCmd.Replace(strParams[i], "?");
                //        }
                //    }
                //    break;
                case DataConnType.MySqlDB:
                    command.CommandText = strCmd.Replace("@", "?");
                    break;
            }
            command.CommandType = cmdType;
            if (trans != null)
            {
                command.Transaction = trans;
            }
            else
            {
                command.Transaction = null;
            }
            if ((strParams != null) && (strValues != null))
            {
                long length = strParams.Length;
                if (length != strValues.Length)
                {
                    throw new DataTierException(string.Concat(new object[] { " ", length, " ", strValues.Length, "" }));
                }
                for (int j = 0; j < length; j++)
                {
                    command.Parameters.Add(createParameter(strParams[j], strValues[j]));
                }
            }
            return command;
        }

        private static DbParameter createParameter(string strParam, object val)
        {
            if (val == null)
            {
                val = DBNull.Value;
            }
            switch (DataInfo.DatabaseInfo.DBConnectionType)
            {
                case DataConnType.SqlDB:
                    if (strParam.StartsWith("@"))
                    {
                        return new SqlParameter(strParam, val);
                    }
                    return new SqlParameter("@" + strParam, val);

                case DataConnType.OracleDB:
                    return new OracleParameter(strParam.Replace("@", ""), val);
                case DataConnType.MySqlDB:
                    return new MySqlParameter(strParam.Replace("@", "?"), val);
            }
            return new OleDbParameter(strParam.Replace("@", ""), val);
        }

        public static int executeNoQuery(DbTransaction trans, string strCmd, CommandType cmdType)
        {
            return executeNoQuery(trans, null, strCmd, cmdType, null, null);
        }

        public static int executeNoQuery(DbTransaction trans, string strCmd, CommandType cmdType, string[] paramNames,
                                         object[] paramValues)
        {
            return executeNoQuery(trans, null, strCmd, cmdType, paramNames, paramValues);
        }

        public static int executeNoQuery(DbConnection conn, string strCmd, CommandType cmdType)
        {
            return executeNoQuery(null, conn, strCmd, cmdType, null, null);
        }

        public static int executeNoQuery(DbConnection conn, string strCmd, CommandType cmdType, string[] paramNames,
                                         object[] paramValues)
        {
            return executeNoQuery(null, conn, strCmd, cmdType, paramNames, paramValues);
        }

        public static int executeNoQuery(string strCmd, CommandType cmdType)
        {
            return executeNoQuery(null, null, strCmd, cmdType, null, null);
        }

        public static int executeNoQuery(string strCmd, CommandType cmdType, string[] paramNames, object[] paramValues)
        {
            return executeNoQuery(null, null, strCmd, cmdType, paramNames, paramValues);
        }

        private static int executeNoQuery(DbTransaction trans, DbConnection conn, string strCmd, CommandType cmdType,
                                          string[] paramNames, object[] paramValues)
        {
            bool isNewConn = false;
            int num;
            try
            {
                if (trans == null)
                {
                    if (conn == null)
                    {
                        isNewConn = true;
                        conn =
                            DataConnFactory.instance.newObject(DataInfo.DatabaseInfo.DBConnectionType,
                                                                 DataInfo.DatabaseInfo.DBConnectionString);
                    }
                }
                else
                {
                    conn = trans.Connection;
                }
                num = buildCommand(trans, conn, cmdType, strCmd, paramNames, paramValues).ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new DataTierException(ex.Message);
            }
            finally
            {
                if (trans == null)
                {
                    if (isNewConn)
                    {
                        DataConnFactory.instance.closeObject(conn);
                    }
                }
            }
            return num;
        }

        public static DbDataReader getDataReader(DbTransaction trans, string strCmd, CommandType cmdType)
        {
            return getDataReader(trans, null, strCmd, cmdType, null, null);
        }

        public static DbDataReader getDataReader(DbTransaction trans, string strCmd, CommandType cmdType,
                                                string[] paramNames, object[] paramValues)
        {
            return getDataReader(trans, null, strCmd, cmdType, paramNames, paramValues);
        }

        public static DbDataReader getDataReader(DbConnection conn, string strCmd, CommandType cmdType)
        {
            return getDataReader(null, conn, strCmd, cmdType, null, null);
        }

        public static DbDataReader getDataReader(DbConnection conn, string strCmd, CommandType cmdType,
                                                string[] paramNames, object[] paramValues)
        {
            return getDataReader(null, conn, strCmd, cmdType, paramNames, paramValues);
        }

        private static DbDataReader getDataReader(DbTransaction trans, DbConnection conn, string strCmd,
                                                 CommandType cmdType, string[] paramNames, object[] paramValues)
        {
            try
            {
                if (trans == null)
                {
                    if (conn == null)
                    {
                        throw new DataTierException("没有数据连接");
                    }
                }
                else
                {
                    conn = trans.Connection;
                }
                return buildCommand(trans, conn, cmdType, strCmd, paramNames, paramValues).ExecuteReader();
            }
            catch (Exception ex)
            {
                throw new DataTierException(ex.Message);
            }
        }

        public static DbDataReader getDataReader(DbTransaction trans, string strCmd, CommandType cmdType,
                                                CommandBehavior behavior)
        {
            return getDataReader(trans, null, strCmd, cmdType, behavior, null, null);
        }

        public static DbDataReader getDataReader(DbTransaction trans, string strCmd, CommandType cmdType,
                                                CommandBehavior behavior, string[] paramNames, object[] paramValues)
        {
            return getDataReader(trans, null, strCmd, cmdType, behavior, paramNames, paramValues);
        }

        public static DbDataReader getDataReader(DbConnection conn, string strCmd, CommandType cmdType,
                                                CommandBehavior behavior)
        {
            return getDataReader(null, conn, strCmd, cmdType, behavior, null, null);
        }

        public static DbDataReader getDataReader(DbConnection conn, string strCmd, CommandType cmdType,
                                                CommandBehavior behavior, string[] paramNames, object[] paramValues)
        {
            return getDataReader(null, conn, strCmd, cmdType, behavior, paramNames, paramValues);
        }

        private static DbDataReader getDataReader(DbTransaction trans, DbConnection conn, string strCmd,
                                                 CommandType cmdType, CommandBehavior behavior,
                                                 string[] paramNames, object[] paramValues)
        {
            try
            {
                if (trans == null)
                {
                    if (conn == null)
                    {
                        throw new DataTierException("没有数据连接");
                    }
                }
                else
                {
                    conn = trans.Connection;
                }
                //return buildCommand(trans, conn, cmdType, strCmd, paramNames, paramValues).ExecuteReader(behavior);
                return buildCommand(trans, conn, cmdType, strCmd, paramNames, paramValues).ExecuteReader();
            }
            catch (Exception ex)
            {
                throw new DataTierException(ex.Message);
            }
        }

        public static DbDataReader getDataReader(string strCmd, CommandType cmdType)
        {
            DbConnection conn =
                DataConnFactory.instance.newObject(DataInfo.DatabaseInfo.DBConnectionType,
                                                     DataInfo.DatabaseInfo.DBConnectionString);
            return getDataReader(null, conn, strCmd, cmdType, CommandBehavior.CloseConnection, null, null);
        }

        public static DbDataReader getDataReader(string strCmd, CommandType cmdType, string[] paramNames,
                                                object[] paramValues)
        {
            DbConnection conn =
                DataConnFactory.instance.newObject(DataInfo.DatabaseInfo.DBConnectionType,
                                                     DataInfo.DatabaseInfo.DBConnectionString);
            return getDataReader(null, conn, strCmd, cmdType, CommandBehavior.CloseConnection, paramNames, paramValues);
        }

        public static object getScalar(DbTransaction trans, string strCmd, CommandType cmdType)
        {
            return getScalar(trans, null, strCmd, cmdType, null, null);
        }

        public static object getScalar(DbTransaction trans, string strCmd, CommandType cmdType, string[] paramNames,
                                       object[] paramValues)
        {
            return getScalar(trans, null, strCmd, cmdType, paramNames, paramValues);
        }

        public static object getScalar(DbConnection conn, string strCmd, CommandType cmdType)
        {
            return getScalar(null, conn, strCmd, cmdType, null, null);
        }

        public static object getScalar(DbConnection conn, string strCmd, CommandType cmdType, string[] paramNames,
                                       object[] paramValues)
        {
            return getScalar(null, conn, strCmd, cmdType, paramNames, paramValues);
        }

        public static object getScalar(string strCmd, CommandType cmdType)
        {
            return getScalar(null, null, strCmd, cmdType, null, null);
        }

        public static object getScalar(string strCmd, CommandType cmdType, string[] paramNames, object[] paramValues)
        {
            return getScalar(null, null, strCmd, cmdType, paramNames, paramValues);
        }

        private static object getScalar(DbTransaction trans, DbConnection conn, string strCmd, CommandType cmdType,
                                        string[] paramNames, object[] paramValues)
        {
            bool isNewConn = false;
            try
            {
                if (trans == null)
                {
                    if (conn == null)
                    {
                        isNewConn = true;
                        conn =
                            DataConnFactory.instance.newObject(DataInfo.DatabaseInfo.DBConnectionType,
                                                                 DataInfo.DatabaseInfo.DBConnectionString);
                    }
                }
                else
                {
                    conn = trans.Connection;
                }
                return buildCommand(trans, conn, cmdType, strCmd, paramNames, paramValues).ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new DataTierException(ex.Message);
            }
            finally
            {
                if (trans == null)
                {
                    if (isNewConn)
                    {
                        DataConnFactory.instance.closeObject(conn);
                    }
                }
            }
        }

        /// <summary>
        /// 开始事务
        /// </summary>
        public static void beginTransaction(out DbTransaction trans)
        {
            try
            {
                DbConnection conn = DataConnFactory.instance.newObject(DataInfo.DatabaseInfo.DBConnectionType,
                                                                          DataInfo.DatabaseInfo.DBConnectionString);
                trans = conn.BeginTransaction();
            }
            catch (Exception ex)
            {
                throw new DataTierException(ex.Message);
            }
        }

       /// <summary>
       /// 回滚事务
       /// </summary>
        public static void rollbackTransaction(ref DbTransaction trans)
        {
            if (trans != null)
            {
                if (trans.Connection != null)
                {
                    trans.Rollback();
                    if (trans.Connection != null)
                    {
                        if (trans.Connection.State == ConnectionState.Open)
                        {
                            trans.Connection.Close();
                        }
                        trans.Connection.Dispose();
                    }
                }
                trans = null;
            }
        }

       /// <summary>
       /// 结束事务
       /// </summary>
        public static void endTransaction(ref DbTransaction trans)
        {
            if (trans != null)
            {
                if (trans.Connection != null)
                {
                    trans.Commit();
                    if (trans.Connection != null)
                    {
                        if (trans.Connection.State == ConnectionState.Open)
                        {
                            trans.Connection.Close();
                        }
                        trans.Connection.Dispose();
                    }
                }
                trans = null;
            }
        }
    }