自己认认真真总结了一份关于连接数据库的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;
}
}
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
