一份关于连接数据库的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; } } }