CommonSlowXDataBaseHelper数据库操作相关方法
2015-09-04 13:19:07 访问(1504) 赞(0) 踩(0)
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.OracleClient;
using System.Data.OleDb;
using System.Text;
namespace SlowX.DataBaseHelper
{
/// <summary>
/// 数据库类型
/// </summary>
public enum DataBaseType
{
/// <summary>
/// SqlClient
/// </summary>
SqlClient = 1,
/// <summary>
/// OracleClient
/// </summary>
OracleClient,
/// <summary>
/// OleDb
/// </summary>
OleDb
}
/// <summary>
/// 数据库操作相关方法
/// </summary>
public class CommonSlowXDataBaseHelper
{
///// <summary>
///// 数据库链接串
///// </summary>
//public readonly static string dataBaseConnectionString = System.Configuration.ConfigurationManager.AppSettings["SlowX.DataBaseHelper.dataBaseConnectionString"];
//public readonly static DataBaseType curDataBaseTypeValue = 0;
// public static DataBaseType GetDataBaseType()
// {
// string
// }
/// <summary>
/// 查询的表格名
/// </summary>
public const string srcTableName = "SelectDataTable";
/// <summary>
/// 获得数据库类型
/// </summary>
/// <param name="cn"></param>
/// <returns></returns>
private static DataBaseType GetDataBaseType(IDbConnection cn)
{
if (cn == null)
throw new Exception("IDbConnection cn 为null。");
if (cn is SqlConnection)
return DataBaseType.SqlClient;
if (cn is OracleConnection)
return DataBaseType.OracleClient;
if (cn is OleDbConnection)
return DataBaseType.OleDb;
throw new Exception("IDbConnection cn = " + cn.GetType().FullName + " 为未知类型。");
}
/// <summary>
/// 获得数据库链接
/// </summary>
/// <param name="dataBaseTypeValue"></param>
/// <returns></returns>
private static IDbConnection CreateIDbConnection(DataBaseType dataBaseTypeValue)
{
switch (dataBaseTypeValue)
{
case DataBaseType.SqlClient:
return new SqlConnection();
case DataBaseType.OracleClient:
return new OracleConnection();
case DataBaseType.OleDb:
return new OleDbConnection();
default:
throw new Exception("DataBaseType dataBaseTypeValue = " + dataBaseTypeValue.ToString() + " 为未知枚举。");
}
}
/// <summary>
/// 创建命令
/// </summary>
/// <param name="dataBaseTypeValue"></param>
/// <returns></returns>
private static IDbCommand CreateIDbCommand(DataBaseType dataBaseTypeValue)
{
switch (dataBaseTypeValue)
{
case DataBaseType.SqlClient:
return new SqlCommand();
case DataBaseType.OracleClient:
return new OracleCommand();
case DataBaseType.OleDb:
return new OleDbCommand();
default:
throw new Exception("DataBaseType dataBaseTypeValue = " + dataBaseTypeValue.ToString() + " 为未知枚举。");
}
}
/// <summary>
/// 创建 IDbDataAdapter
/// </summary>
/// <param name="dataBaseTypeValue"></param>
/// <param name="cmd"></param>
/// <returns></returns>
private static IDbDataAdapter CreateIDbDataAdapter(DataBaseType dataBaseTypeValue, IDbCommand cmd)
{
switch (dataBaseTypeValue)
{
case DataBaseType.SqlClient:
return new SqlDataAdapter(cmd as SqlCommand);
case DataBaseType.OracleClient:
return new OracleDataAdapter(cmd as OracleCommand);
case DataBaseType.OleDb:
return new OleDbDataAdapter(cmd as OleDbCommand);
default:
throw new Exception("DataBaseType dataBaseTypeValue = " + dataBaseTypeValue.ToString() + " 为未知枚举。");
}
}
/// <summary>
/// 创建 IDataParameter
/// </summary>
/// <param name="dataBaseTypeValue"></param>
/// <returns></returns>
private static IDataParameter CreateIDataParameter(DataBaseType dataBaseTypeValue)
{
switch (dataBaseTypeValue)
{
case DataBaseType.SqlClient:
return new SqlParameter();
case DataBaseType.OracleClient:
return new OracleParameter();
case DataBaseType.OleDb:
return new OleDbParameter();
default:
throw new Exception("DataBaseType dataBaseTypeValue = " + dataBaseTypeValue.ToString() + " 为未知枚举。");
}
}
/// <summary>
/// 转成 IDataParameter
/// </summary>
/// <param name="dataBaseTypeValue"></param>
/// <param name="p"></param>
/// <returns></returns>
private static IDataParameter ToIDataParameter(DataBaseType dataBaseTypeValue, BasicIDataParameter p)
{
IDataParameter theResult = null;
if (p.Size != -1)
{
switch (dataBaseTypeValue)
{
case DataBaseType.SqlClient:
SqlParameter sqlParameterValue = new SqlParameter();
sqlParameterValue.Size = p.Size;
theResult = sqlParameterValue;
break;
case DataBaseType.OracleClient:
OracleParameter oracleParameterValue = new OracleParameter();
oracleParameterValue.Size = p.Size;
theResult = oracleParameterValue;
break;
case DataBaseType.OleDb:
OleDbParameter oleDbParameterValue = new OleDbParameter();
oleDbParameterValue.Size = p.Size;
theResult = oleDbParameterValue;
break;
default:
throw new Exception("DataBaseType dataBaseTypeValue = " + dataBaseTypeValue.ToString() + " 为未知枚举。");
}
}
else
{
theResult = CreateIDataParameter(dataBaseTypeValue);
}
theResult.DbType = p.DbType;
theResult.Direction = p.Direction;
theResult.ParameterName = p.ParameterName;
theResult.Value = p.Value;
return theResult;
}
#region 获得数据集 ExecuteDataSet
/// <summary>
/// 获得数据集
/// </summary>
/// <param name="_dbConnectionString"></param>
/// <param name="dataBaseTypeValue"></param>
/// <param name="_SQL"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet
(
string _dbConnectionString,
DataBaseType dataBaseTypeValue,
string _SQL
)
{
IDbConnection cn = null;
IDbCommand cmd = null;
IDbDataAdapter da = null;
DataSet theResult = null;
try
{
cn = CreateIDbConnection(dataBaseTypeValue);
cn.ConnectionString = _dbConnectionString;
cn.Open();
cmd = CreateIDbCommand(dataBaseTypeValue);
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
theResult = new DataSet();
da = CreateIDbDataAdapter(dataBaseTypeValue, cmd);
switch (dataBaseTypeValue)
{
case DataBaseType.OleDb:
(da as OleDbDataAdapter).Fill(theResult, srcTableName);
break;
case DataBaseType.SqlClient:
(da as SqlDataAdapter).Fill(theResult, srcTableName);
break;
case DataBaseType.OracleClient:
(da as OracleDataAdapter).Fill(theResult, srcTableName);
break;
default:
da.Fill(theResult);
break;
}
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
switch (dataBaseTypeValue)
{
case DataBaseType.OleDb:
(da as OleDbDataAdapter).Dispose();
break;
case DataBaseType.SqlClient:
(da as SqlDataAdapter).Dispose();
break;
case DataBaseType.OracleClient:
(da as OracleDataAdapter).Dispose();
break;
default:
break;
}
da = null;
}
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
cn.Dispose();
cn = null;
}
}
return theResult;
}
/// <summary>
/// 获得数据集
/// </summary>
/// <param name="_dbConnectionString"></param>
/// <param name="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet
(
string _dbConnectionString,
DataBaseType dataBaseTypeValue,
string _SQL,
List<BasicIDataParameter> theList
)
{
IDbConnection cn = null;
IDbCommand cmd = null;
IDbDataAdapter da = null;
DataSet theResult = null;
try
{
cn = CreateIDbConnection(dataBaseTypeValue);
cn.ConnectionString = _dbConnectionString;
cn.Open();
cmd = CreateIDbCommand(dataBaseTypeValue);
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (BasicIDataParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(ToIDataParameter(dataBaseTypeValue, p));
}
}
da = CreateIDbDataAdapter(dataBaseTypeValue, cmd);
theResult = new DataSet();
switch (dataBaseTypeValue)
{
case DataBaseType.OleDb:
(da as OleDbDataAdapter).Fill(theResult, srcTableName);
break;
case DataBaseType.SqlClient:
(da as SqlDataAdapter).Fill(theResult, srcTableName);
break;
case DataBaseType.OracleClient:
(da as OracleDataAdapter).Fill(theResult, srcTableName);
break;
default:
da.Fill(theResult);
break;
}
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
switch (dataBaseTypeValue)
{
case DataBaseType.OleDb:
(da as OleDbDataAdapter).Dispose();
break;
case DataBaseType.SqlClient:
(da as SqlDataAdapter).Dispose();
break;
case DataBaseType.OracleClient:
(da as OracleDataAdapter).Dispose();
break;
default:
break;
}
da = null;
}
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
cn.Dispose();
cn = null;
}
}
return theResult;
}
/// <summary>
/// 获得数据集
/// </summary>
/// <param name="cn"></param>
/// <param name="_SQL"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet
(
IDbConnection cn,
string _SQL
)
{
if (cn == null)
throw new Exception("IDbConnection cn为null。");
DataBaseType dataBaseTypeValue = GetDataBaseType(cn);
bool needOpen = false;
IDbCommand cmd = null;
IDbDataAdapter da = null;
DataSet theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = CreateIDbCommand(dataBaseTypeValue);
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
da = CreateIDbDataAdapter(dataBaseTypeValue, cmd);
theResult = new DataSet();
switch (dataBaseTypeValue)
{
case DataBaseType.OleDb:
(da as OleDbDataAdapter).Fill(theResult, srcTableName);
break;
case DataBaseType.SqlClient:
(da as SqlDataAdapter).Fill(theResult, srcTableName);
break;
case DataBaseType.OracleClient:
(da as OracleDataAdapter).Fill(theResult, srcTableName);
break;
default:
da.Fill(theResult);
break;
}
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
switch (dataBaseTypeValue)
{
case DataBaseType.OleDb:
(da as OleDbDataAdapter).Dispose();
break;
case DataBaseType.SqlClient:
(da as SqlDataAdapter).Dispose();
break;
case DataBaseType.OracleClient:
(da as OracleDataAdapter).Dispose();
break;
default:
break;
}
da = null;
}
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (needOpen)
{
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
}
}
}
return theResult;
}
/// <summary>
/// 获得数据集
/// </summary>
/// <param name="cn"></param>
/// <param name="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet
(
IDbConnection cn,
string _SQL,
List<BasicIDataParameter> theList
)
{
if (cn == null)
throw new Exception("IDbConnection cn为null。");
DataBaseType dataBaseTypeValue = GetDataBaseType(cn);
bool needOpen = false;
IDbCommand cmd = null;
IDbDataAdapter da = null;
DataSet theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = CreateIDbCommand(dataBaseTypeValue);
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (BasicIDataParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(ToIDataParameter(dataBaseTypeValue, p));
}
}
da = CreateIDbDataAdapter(dataBaseTypeValue, cmd);
theResult = new DataSet();
switch (dataBaseTypeValue)
{
case DataBaseType.OleDb:
(da as OleDbDataAdapter).Fill(theResult, srcTableName);
break;
case DataBaseType.SqlClient:
(da as SqlDataAdapter).Fill(theResult, srcTableName);
break;
case DataBaseType.OracleClient:
(da as OracleDataAdapter).Fill(theResult, srcTableName);
break;
default:
da.Fill(theResult);
break;
}
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
switch (dataBaseTypeValue)
{
case DataBaseType.OleDb:
(da as OleDbDataAdapter).Dispose();
break;
case DataBaseType.SqlClient:
(da as SqlDataAdapter).Dispose();
break;
case DataBaseType.OracleClient:
(da as OracleDataAdapter).Dispose();
break;
default:
break;
}
da = null;
}
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (needOpen)
{
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
}
}
}
return theResult;
}
/// <summary>
/// 获得数据集
/// </summary>
/// <param name="tran"></param>
/// <param name="_SQL"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet
(
IDbTransaction tran,
string _SQL
)
{
if (tran == null)
throw new Exception("IDbTransaction tran为null。");
IDbConnection cn = tran.Connection;
if (cn == null)
throw new Exception("IDbConnection cn为null。");
DataBaseType dataBaseTypeValue = GetDataBaseType(cn);
bool needOpen = false;
IDbCommand cmd = null;
IDbDataAdapter da = null;
DataSet theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = CreateIDbCommand(dataBaseTypeValue);
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
da = CreateIDbDataAdapter(dataBaseTypeValue, cmd);
theResult = new DataSet();
switch (dataBaseTypeValue)
{
case DataBaseType.OleDb:
(da as OleDbDataAdapter).Fill(theResult, srcTableName);
break;
case DataBaseType.SqlClient:
(da as SqlDataAdapter).Fill(theResult, srcTableName);
break;
case DataBaseType.OracleClient:
(da as OracleDataAdapter).Fill(theResult, srcTableName);
break;
default:
da.Fill(theResult);
break;
}
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
switch (dataBaseTypeValue)
{
case DataBaseType.OleDb:
(da as OleDbDataAdapter).Dispose();
break;
case DataBaseType.SqlClient:
(da as SqlDataAdapter).Dispose();
break;
case DataBaseType.OracleClient:
(da as OracleDataAdapter).Dispose();
break;
default:
break;
}
da = null;
}
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (needOpen)
{
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
}
}
}
return theResult;
}
/// <summary>
/// 获得数据集
/// </summary>
/// <param name="tran"></param>
/// <param name="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet
(
IDbTransaction tran,
string _SQL,
List<BasicIDataParameter> theList
)
{
if (tran == null)
throw new Exception("IDbTransaction tran为null。");
IDbConnection cn = tran.Connection;
if (cn == null)
throw new Exception("IDbConnection cn为null。");
DataBaseType dataBaseTypeValue = GetDataBaseType(cn);
bool needOpen = false;
IDbCommand cmd = null;
IDbDataAdapter da = null;
DataSet theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = CreateIDbCommand(dataBaseTypeValue);
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (BasicIDataParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(ToIDataParameter(dataBaseTypeValue, p));
}
}
da = CreateIDbDataAdapter(dataBaseTypeValue, cmd);
theResult = new DataSet();
switch (dataBaseTypeValue)
{
case DataBaseType.OleDb:
(da as OleDbDataAdapter).Fill(theResult, srcTableName);
break;
case DataBaseType.SqlClient:
(da as SqlDataAdapter).Fill(theResult, srcTableName);
break;
case DataBaseType.OracleClient:
(da as OracleDataAdapter).Fill(theResult, srcTableName);
break;
default:
da.Fill(theResult);
break;
}
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
switch (dataBaseTypeValue)
{
case DataBaseType.OleDb:
(da as OleDbDataAdapter).Dispose();
break;
case DataBaseType.SqlClient:
(da as SqlDataAdapter).Dispose();
break;
case DataBaseType.OracleClient:
(da as OracleDataAdapter).Dispose();
break;
default:
break;
}
da = null;
}
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (needOpen)
{
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
}
}
}
return theResult;
}
#endregion 获得数据集 ExecuteDataSet
#region 执行SQL语句，返回影响记录数
/// <summary>
/// 执行SQL语句，返回影响记录数
/// </summary>
/// <param name="_dbConnectionString"></param>
/// <param name="dataBaseTypeValue"></param>
/// <param name="_SQL"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
string _dbConnectionString,
DataBaseType dataBaseTypeValue,
string _SQL
)
{
IDbConnection cn = null;
IDbCommand cmd = null;
int theResult = -1;
try
{
cn = CreateIDbConnection(dataBaseTypeValue);
cn.ConnectionString = _dbConnectionString;
cn.Open();
cmd = CreateIDbCommand(dataBaseTypeValue);
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
theResult = cmd.ExecuteNonQuery();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
cn.Dispose();
cn = null;
}
}
return theResult;
}
/// <summary>
/// 执行SQL语句，返回影响记录数
/// </summary>
/// <param name="_dbConnectionString"></param>
/// <param name="dataBaseTypeValue"></param>
/// <param name="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
string _dbConnectionString,
DataBaseType dataBaseTypeValue,
string _SQL,
List<BasicIDataParameter> theList
)
{
IDbConnection cn = null;
IDbCommand cmd = null;
int theResult = -1;
try
{
cn = CreateIDbConnection(dataBaseTypeValue);
cn.ConnectionString = _dbConnectionString;
cn.Open();
cmd = CreateIDbCommand(dataBaseTypeValue);
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (BasicIDataParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(ToIDataParameter(dataBaseTypeValue, p));
}
}
theResult = cmd.ExecuteNonQuery();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
cn.Dispose();
cn = null;
}
}
return theResult;
}
/// <summary>
/// 执行SQL语句，返回影响记录数
/// </summary>
/// <param name="cn"></param>
/// <param name="_SQL"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
IDbConnection cn,
string _SQL
)
{
if (cn == null)
throw new Exception("IDbConnection cn为null。");
DataBaseType dataBaseTypeValue = GetDataBaseType(cn);
bool needOpen = false;
IDbCommand cmd = null;
int theResult = -1;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = CreateIDbCommand(dataBaseTypeValue);
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
theResult = cmd.ExecuteNonQuery();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (needOpen)
{
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
}
}
}
return theResult;
}
/// <summary>
/// 执行SQL语句，返回影响记录数
/// </summary>
/// <param name="cn"></param>
/// <param name="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
IDbConnection cn,
string _SQL,
List<BasicIDataParameter> theList
)
{
if (cn == null)
throw new Exception("IDbConnection cn为null。");
DataBaseType dataBaseTypeValue = GetDataBaseType(cn);
bool needOpen = false;
IDbCommand cmd = null;
int theResult = -1;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = CreateIDbCommand(dataBaseTypeValue);
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (BasicIDataParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(ToIDataParameter(dataBaseTypeValue, p));
}
}
theResult = cmd.ExecuteNonQuery();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (needOpen)
{
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
}
}
}
return theResult;
}
/// <summary>
/// 执行SQL语句，返回影响记录数
/// </summary>
/// <param name="tran"></param>
/// <param name="_SQL"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
IDbTransaction tran,
string _SQL
)
{
if (tran == null)
throw new Exception("IDbTransaction tran为null。");
IDbConnection cn = tran.Connection;
if (cn == null)
throw new Exception("IDbConnection cn为null。");
DataBaseType dataBaseTypeValue = GetDataBaseType(cn);
bool needOpen = false;
IDbCommand cmd = null;
int theResult = -1;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = CreateIDbCommand(dataBaseTypeValue);
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
theResult = cmd.ExecuteNonQuery();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (needOpen)
{
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
}
}
}
return theResult;
}
/// <summary>
/// 执行SQL语句，返回影响记录数
/// </summary>
/// <param name="tran"></param>
/// <param name="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
IDbTransaction tran,
string _SQL,
List<BasicIDataParameter> theList
)
{
if (tran == null)
throw new Exception("IDbTransaction tran为null。");
IDbConnection cn = tran.Connection;
if (cn == null)
throw new Exception("IDbConnection cn为null。");
DataBaseType dataBaseTypeValue = GetDataBaseType(cn);
bool needOpen = false;
IDbCommand cmd = null;
int theResult = -1;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = CreateIDbCommand(dataBaseTypeValue);
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (BasicIDataParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(ToIDataParameter(dataBaseTypeValue, p));
}
}
theResult = cmd.ExecuteNonQuery();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (needOpen)
{
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
}
}
}
return theResult;
}
#endregion 执行SQL语句，返回影响记录数
#region 执行SQL语句，返回影响记录数
/// <summary>
/// 执行SQL语句，返回影响记录数
/// </summary>
/// <param name="_dbConnectionString"></param>
/// <param name="_SQL"></param>
/// <returns></returns>
public static object ExecuteScalar
(
string _dbConnectionString,
DataBaseType dataBaseTypeValue,
string _SQL
)
{
IDbConnection cn = null;
IDbCommand cmd = null;
object theResult = null;
try
{
cn = CreateIDbConnection(dataBaseTypeValue);
cn.ConnectionString = _dbConnectionString;
cn.Open();
cmd = CreateIDbCommand(dataBaseTypeValue);
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
theResult = cmd.ExecuteScalar();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
cn.Dispose();
cn = null;
}
}
return theResult;
}
/// <summary>
/// 执行SQL语句，返回影响记录数
/// </summary>
/// <param name="_dbConnectionString"></param>
/// <param name="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static object ExecuteScalar
(
string _dbConnectionString,
DataBaseType dataBaseTypeValue,
string _SQL,
List<BasicIDataParameter> theList
)
{
IDbConnection cn = null;
IDbCommand cmd = null;
object theResult = null;
try
{
cn = CreateIDbConnection(dataBaseTypeValue);
cn.ConnectionString = _dbConnectionString;
cn.Open();
cmd = CreateIDbCommand(dataBaseTypeValue);
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (BasicIDataParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(ToIDataParameter(dataBaseTypeValue, p));
}
}
theResult = cmd.ExecuteScalar();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
cn.Dispose();
cn = null;
}
}
return theResult;
}
/// <summary>
/// 执行SQL语句，返回影响记录数
/// </summary>
/// <param name="cn"></param>
/// <param name="_SQL"></param>
/// <returns></returns>
public static object ExecuteScalar
(
IDbConnection cn,
string _SQL
)
{
if (cn == null)
throw new Exception("IDbConnection cn为null。");
DataBaseType dataBaseTypeValue = GetDataBaseType(cn);
bool needOpen = false;
IDbCommand cmd = null;
object theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = CreateIDbCommand(dataBaseTypeValue);
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
theResult = cmd.ExecuteScalar();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (needOpen)
{
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
}
}
}
return theResult;
}
/// <summary>
/// 执行SQL语句，返回影响记录数
/// </summary>
/// <param name="cn"></param>
/// <param name="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static object ExecuteScalar
(
IDbConnection cn,
string _SQL,
List<BasicIDataParameter> theList
)
{
if (cn == null)
throw new Exception("IDbConnection cn为null。");
DataBaseType dataBaseTypeValue = GetDataBaseType(cn);
bool needOpen = false;
IDbCommand cmd = null;
object theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = CreateIDbCommand(dataBaseTypeValue);
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (BasicIDataParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(ToIDataParameter(dataBaseTypeValue, p));
}
}
theResult = cmd.ExecuteScalar();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (needOpen)
{
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
}
}
}
return theResult;
}
/// <summary>
/// 执行SQL语句，返回影响记录数
/// </summary>
/// <param name="tran"></param>
/// <param name="_SQL"></param>
/// <returns></returns>
public static object ExecuteScalar
(
IDbTransaction tran,
string _SQL
)
{
if (tran == null)
throw new Exception("IDbTransaction tran为null。");
IDbConnection cn = tran.Connection;
if (cn == null)
throw new Exception("IDbConnection cn为null。");
DataBaseType dataBaseTypeValue = GetDataBaseType(cn);
bool needOpen = false;
IDbCommand cmd = null;
object theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = CreateIDbCommand(dataBaseTypeValue);
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
theResult = cmd.ExecuteScalar();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (needOpen)
{
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
}
}
}
return theResult;
}
/// <summary>
/// 执行SQL语句，返回影响记录数
/// </summary>
/// <param name="tran"></param>
/// <param name="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static object ExecuteScalar
(
IDbTransaction tran,
string _SQL,
List<BasicIDataParameter> theList
)
{
if (tran == null)
throw new Exception("IDbTransaction tran为null。");
IDbConnection cn = tran.Connection;
if (cn == null)
throw new Exception("IDbConnection cn为null。");
DataBaseType dataBaseTypeValue = GetDataBaseType(cn);
bool needOpen = false;
IDbCommand cmd = null;
object theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = CreateIDbCommand(dataBaseTypeValue);
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (BasicIDataParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(ToIDataParameter(dataBaseTypeValue, p));
}
}
theResult = cmd.ExecuteScalar();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (needOpen)
{
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
}
}
}
return theResult;
}
#endregion 执行SQL语句，返回影响记录数
}
}
标签：
CommonSlowXDataBaseHelper数据库操作相关方法 
上一条：
下一条：
相关评论
发表评论