-
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
namespace SlowX.DAL.DataBaseHelpers
{
/// <summary>
/// ADO.NET的数据库参数
/// 伪IDataParameter
/// </summary>
[Serializable]
public class BasicIDataParameter
{
/// <summary>
/// 数据库类型
/// </summary>
protected DbType m_DbType = DbType.String;
/// <summary>
/// 数据库类型
/// </summary>
public DbType DbType
{
get
{
return m_DbType;
}
set
{
m_DbType = value;
}
}
/// <summary>
/// 参数链接方向
/// </summary>
protected ParameterDirection m_Direction = ParameterDirection.Input;
/// <summary>
/// 参数链接方向
/// </summary>
public ParameterDirection Direction
{
get
{
return m_Direction;
}
set
{
m_Direction = value;
}
}
/// <summary>
/// 参数名称（不能带前缀）
/// </summary>
protected string m_ParameterName = string.Empty;
/// <summary>
/// 参数名称（不能带前缀）
/// </summary>
public string ParameterName
{
get
{
return m_ParameterName;
}
set
{
m_ParameterName = value;
}
}
/// <summary>
/// 参数值
/// </summary>
protected object m_Value = DBNull.Value;
/// <summary>
/// 参数值
/// </summary>
public object Value
{
get
{
return m_Value;
}
set
{
if (value == null)
m_Value = DBNull.Value;
else
m_Value = value;
}
}
/// <summary>
/// 参数大小
/// </summary>
protected int m_Size = -1;
/// <summary>
/// 参数大小
/// </summary>
public int Size
{
get
{
return m_Size;
}
set
{
m_Size = value;
}
}
/// <summary>
/// 构造函数
/// </summary>
public BasicIDataParameter()
{
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="_ParameterName"></param>
/// <param name="_Value"></param>
public BasicIDataParameter(string _ParameterName, object _Value)
{
ParameterName = _ParameterName;
Value = _Value;
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="_ParameterName"></param>
/// <param name="_Value"></param>
/// <param name="_Size"></param>
public BasicIDataParameter(string _ParameterName, object _Value, int _Size)
{
ParameterName = _ParameterName;
Value = _Value;
Size = _Size;
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="_ParameterName"></param>
/// <param name="_Value"></param>
/// <param name="_Direction"></param>
public BasicIDataParameter(string _ParameterName, object _Value, ParameterDirection _Direction)
{
ParameterName = _ParameterName;
Value = _Value;
Direction = _Direction;
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="_ParameterName"></param>
/// <param name="_Value"></param>
/// <param name="_Direction"></param>
/// <param name="_Size"></param>
public BasicIDataParameter(string _ParameterName, object _Value, ParameterDirection _Direction, int _Size)
{
ParameterName = _ParameterName;
Value = _Value;
Direction = _Direction;
Size = _Size;
}
}
}
-
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.DAL.DataBaseHelpers
{
/// <summary>
/// 数据库类型
/// </summary>
public enum DataBaseType
{
/// <summary>
/// SqlClient
/// </summary>
SqlClient = 1,
/// <summary>
/// OracleClient
/// </summary>
OracleClient,
/// <summary>
/// OleDb
/// </summary>
OleDb
}
/// <summary>
/// 数据库操作相关方法
/// </summary>
public class CommonSlowXDALHelper
{
///// <summary>
///// 数据库链接串
///// </summary>
//public readonly static string dataBaseConnectionString = System.Configuration.ConfigurationManager.AppSettings["SlowX.DAL.DataBaseHelpers.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="dataBaseTypeValue"></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="dataBaseTypeValue"></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="dataBaseTypeValue"></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语句，返回影响记录数
}
}
-
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;
namespace SlowX.DAL.DataBaseHelpers
{
/// <summary>
/// 数据库操作相关方法
/// </summary>
public class OleDBSlowXDALHelper
{
/// <summary>
/// 数据库链接串
/// </summary>
public readonly static string dataBaseConnectionString = System.Configuration.ConfigurationManager.AppSettings["SlowX.DAL.DataBaseHelpers.dataBaseConnectionString"];
/// <summary>
/// 查询的表格名
/// </summary>
public const string srcTableName = "SelectDataTable";
#region 获得数据集 ExecuteDataSet
/// <summary>
/// 获得数据集
/// </summary>
/// <param name="_SQL"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet
(
string _SQL
)
{
OleDbConnection cn = null;
OleDbCommand cmd = null;
OleDbDataAdapter da = null;
DataSet theResult = null;
try
{
cn = new OleDbConnection(dataBaseConnectionString);
cn.Open();
cmd = new OleDbCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
da = new OleDbDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
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="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet
(
string _SQL,
List<OleDbParameter> theList
)
{
OleDbConnection cn = null;
OleDbCommand cmd = null;
OleDbDataAdapter da = null;
DataSet theResult = null;
try
{
cn = new OleDbConnection(dataBaseConnectionString);
cn.Open();
cmd = new OleDbCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (OleDbParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(p);
}
}
da = new OleDbDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
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>
/// <returns></returns>
public static DataSet ExecuteDataSet
(
string _dbConnectionString,
string _SQL
)
{
OleDbConnection cn = null;
OleDbCommand cmd = null;
OleDbDataAdapter da = null;
DataSet theResult = null;
try
{
cn = new OleDbConnection(_dbConnectionString);
cn.Open();
cmd = new OleDbCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
da = new OleDbDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
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,
string _SQL,
List<OleDbParameter> theList
)
{
OleDbConnection cn = null;
OleDbCommand cmd = null;
OleDbDataAdapter da = null;
DataSet theResult = null;
try
{
cn = new OleDbConnection(_dbConnectionString);
cn.Open();
cmd = new OleDbCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (OleDbParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(p);
}
}
da = new OleDbDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
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
(
OleDbConnection cn,
string _SQL
)
{
if (cn == null)
throw new Exception("OleDbConnection cn为null。");
bool needOpen = false;
OleDbCommand cmd = null;
OleDbDataAdapter da = null;
DataSet theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new OleDbCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
da = new OleDbDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
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
(
OleDbConnection cn,
string _SQL,
List<OleDbParameter> theList
)
{
if (cn == null)
throw new Exception("OleDbConnection cn为null。");
bool needOpen = false;
OleDbCommand cmd = null;
OleDbDataAdapter da = null;
DataSet theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new OleDbCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (OleDbParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(p);
}
}
da = new OleDbDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
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
(
OleDbTransaction tran,
string _SQL
)
{
if (tran == null)
throw new Exception("OleDbTransaction tran为null。");
OleDbConnection cn = tran.Connection;
if (cn == null)
throw new Exception("OleDbConnection cn为null。");
bool needOpen = false;
OleDbCommand cmd = null;
OleDbDataAdapter da = null;
DataSet theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new OleDbCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
da = new OleDbDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
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
(
OleDbTransaction tran,
string _SQL,
List<OleDbParameter> theList
)
{
if (tran == null)
throw new Exception("OleDbTransaction tran为null。");
OleDbConnection cn = tran.Connection;
if (cn == null)
throw new Exception("OleDbConnection cn为null。");
bool needOpen = false;
OleDbCommand cmd = null;
OleDbDataAdapter da = null;
DataSet theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new OleDbCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (OleDbParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(p);
}
}
da = new OleDbDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
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="_SQL"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
string _SQL
)
{
OleDbConnection cn = null;
OleDbCommand cmd = null;
int theResult = -1;
try
{
cn = new OleDbConnection(dataBaseConnectionString);
cn.Open();
cmd = new OleDbCommand();
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="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
string _SQL,
List<OleDbParameter> theList
)
{
OleDbConnection cn = null;
OleDbCommand cmd = null;
int theResult = -1;
try
{
cn = new OleDbConnection(dataBaseConnectionString);
cn.Open();
cmd = new OleDbCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (OleDbParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(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="_dbConnectionString"></param>
/// <param name="_SQL"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
string _dbConnectionString,
string _SQL
)
{
OleDbConnection cn = null;
OleDbCommand cmd = null;
int theResult = -1;
try
{
cn = new OleDbConnection(_dbConnectionString);
cn.Open();
cmd = new OleDbCommand();
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="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
string _dbConnectionString,
string _SQL,
List<OleDbParameter> theList
)
{
OleDbConnection cn = null;
OleDbCommand cmd = null;
int theResult = -1;
try
{
cn = new OleDbConnection(_dbConnectionString);
cn.Open();
cmd = new OleDbCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (OleDbParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(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
(
OleDbConnection cn,
string _SQL
)
{
if (cn == null)
throw new Exception("OleDbConnection cn为null。");
bool needOpen = false;
OleDbCommand cmd = null;
int theResult = -1;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new OleDbCommand();
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
(
OleDbConnection cn,
string _SQL,
List<OleDbParameter> theList
)
{
if (cn == null)
throw new Exception("OleDbConnection cn为null。");
bool needOpen = false;
OleDbCommand cmd = null;
int theResult = -1;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new OleDbCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (OleDbParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(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
(
OleDbTransaction tran,
string _SQL
)
{
if (tran == null)
throw new Exception("OleDbTransaction tran为null。");
OleDbConnection cn = tran.Connection;
if (cn == null)
throw new Exception("OleDbConnection cn为null。");
bool needOpen = false;
OleDbCommand cmd = null;
int theResult = -1;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new OleDbCommand();
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
(
OleDbTransaction tran,
string _SQL,
List<OleDbParameter> theList
)
{
if (tran == null)
throw new Exception("OleDbTransaction tran为null。");
OleDbConnection cn = tran.Connection;
if (cn == null)
throw new Exception("OleDbConnection cn为null。");
bool needOpen = false;
OleDbCommand cmd = null;
int theResult = -1;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new OleDbCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (OleDbParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(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="_SQL"></param>
/// <returns></returns>
public static object ExecuteScalar
(
string _SQL
)
{
OleDbConnection cn = null;
OleDbCommand cmd = null;
object theResult = null;
try
{
cn = new OleDbConnection(dataBaseConnectionString);
cn.Open();
cmd = new OleDbCommand();
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="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static object ExecuteScalar
(
string _SQL,
List<OleDbParameter> theList
)
{
OleDbConnection cn = null;
OleDbCommand cmd = null;
object theResult = null;
try
{
cn = new OleDbConnection(dataBaseConnectionString);
cn.Open();
cmd = new OleDbCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (OleDbParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(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="_dbConnectionString"></param>
/// <param name="_SQL"></param>
/// <returns></returns>
public static object ExecuteScalar
(
string _dbConnectionString,
string _SQL
)
{
OleDbConnection cn = null;
OleDbCommand cmd = null;
object theResult = null;
try
{
cn = new OleDbConnection(_dbConnectionString);
cn.Open();
cmd = new OleDbCommand();
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,
string _SQL,
List<OleDbParameter> theList
)
{
OleDbConnection cn = null;
OleDbCommand cmd = null;
object theResult = null;
try
{
cn = new OleDbConnection(_dbConnectionString);
cn.Open();
cmd = new OleDbCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (OleDbParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(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
(
OleDbConnection cn,
string _SQL
)
{
if (cn == null)
throw new Exception("OleDbConnection cn为null。");
bool needOpen = false;
OleDbCommand cmd = null;
object theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new OleDbCommand();
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
(
OleDbConnection cn,
string _SQL,
List<OleDbParameter> theList
)
{
if (cn == null)
throw new Exception("OleDbConnection cn为null。");
bool needOpen = false;
OleDbCommand cmd = null;
object theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new OleDbCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (OleDbParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(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
(
OleDbTransaction tran,
string _SQL
)
{
if (tran == null)
throw new Exception("OleDbTransaction tran为null。");
OleDbConnection cn = tran.Connection;
if (cn == null)
throw new Exception("OleDbConnection cn为null。");
bool needOpen = false;
OleDbCommand cmd = null;
object theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new OleDbCommand();
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
(
OleDbTransaction tran,
string _SQL,
List<OleDbParameter> theList
)
{
if (tran == null)
throw new Exception("OleDbTransaction tran为null。");
OleDbConnection cn = tran.Connection;
if (cn == null)
throw new Exception("OleDbConnection cn为null。");
bool needOpen = false;
OleDbCommand cmd = null;
object theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new OleDbCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (OleDbParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(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语句，获得一个记录
}
}
-
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
namespace SlowX.DAL.DataBaseHelpers
{
/// <summary>
/// 数据库操作相关方法
/// </summary>
public class OracleSlowXDALHelper
{
/// <summary>
/// 数据库链接串
/// </summary>
public readonly static string dataBaseConnectionString = System.Configuration.ConfigurationManager.AppSettings["SlowX.DAL.DataBaseHelpers.dataBaseConnectionString"];
/// <summary>
/// 查询的表格名
/// </summary>
public const string srcTableName = "SelectDataTable";
#region 获得数据集 ExecuteDataSet
/// <summary>
/// 获得数据集
/// </summary>
/// <param name="_SQL"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet
(
string _SQL
)
{
OracleConnection cn = null;
OracleCommand cmd = null;
OracleDataAdapter da = null;
DataSet theResult = null;
try
{
cn = new OracleConnection(dataBaseConnectionString);
cn.Open();
cmd = new OracleCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
da = new OracleDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
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="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet
(
string _SQL,
List<OracleParameter> theList
)
{
OracleConnection cn = null;
OracleCommand cmd = null;
OracleDataAdapter da = null;
DataSet theResult = null;
try
{
cn = new OracleConnection(dataBaseConnectionString);
cn.Open();
cmd = new OracleCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (OracleParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(p);
}
}
da = new OracleDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
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>
/// <returns></returns>
public static DataSet ExecuteDataSet
(
string _dbConnectionString,
string _SQL
)
{
OracleConnection cn = null;
OracleCommand cmd = null;
OracleDataAdapter da = null;
DataSet theResult = null;
try
{
cn = new OracleConnection(_dbConnectionString);
cn.Open();
cmd = new OracleCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
da = new OracleDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
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,
string _SQL,
List<OracleParameter> theList
)
{
OracleConnection cn = null;
OracleCommand cmd = null;
OracleDataAdapter da = null;
DataSet theResult = null;
try
{
cn = new OracleConnection(_dbConnectionString);
cn.Open();
cmd = new OracleCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (OracleParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(p);
}
}
da = new OracleDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
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
(
OracleConnection cn,
string _SQL
)
{
if (cn == null)
throw new Exception("OracleConnection cn为null。");
bool needOpen = false;
OracleCommand cmd = null;
OracleDataAdapter da = null;
DataSet theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new OracleCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
da = new OracleDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
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
(
OracleConnection cn,
string _SQL,
List<OracleParameter> theList
)
{
if (cn == null)
throw new Exception("OracleConnection cn为null。");
bool needOpen = false;
OracleCommand cmd = null;
OracleDataAdapter da = null;
DataSet theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new OracleCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (OracleParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(p);
}
}
da = new OracleDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
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
(
OracleTransaction tran,
string _SQL
)
{
if (tran == null)
throw new Exception("OracleTransaction tran为null。");
OracleConnection cn = tran.Connection;
if (cn == null)
throw new Exception("OracleConnection cn为null。");
bool needOpen = false;
OracleCommand cmd = null;
OracleDataAdapter da = null;
DataSet theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new OracleCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
da = new OracleDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
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
(
OracleTransaction tran,
string _SQL,
List<OracleParameter> theList
)
{
if (tran == null)
throw new Exception("OracleTransaction tran为null。");
OracleConnection cn = tran.Connection;
if (cn == null)
throw new Exception("OracleConnection cn为null。");
bool needOpen = false;
OracleCommand cmd = null;
OracleDataAdapter da = null;
DataSet theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new OracleCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (OracleParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(p);
}
}
da = new OracleDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
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="_SQL"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
string _SQL
)
{
OracleConnection cn = null;
OracleCommand cmd = null;
int theResult = -1;
try
{
cn = new OracleConnection(dataBaseConnectionString);
cn.Open();
cmd = new OracleCommand();
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="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
string _SQL,
List<OracleParameter> theList
)
{
OracleConnection cn = null;
OracleCommand cmd = null;
int theResult = -1;
try
{
cn = new OracleConnection(dataBaseConnectionString);
cn.Open();
cmd = new OracleCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (OracleParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(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="_dbConnectionString"></param>
/// <param name="_SQL"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
string _dbConnectionString,
string _SQL
)
{
OracleConnection cn = null;
OracleCommand cmd = null;
int theResult = -1;
try
{
cn = new OracleConnection(_dbConnectionString);
cn.Open();
cmd = new OracleCommand();
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="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
string _dbConnectionString,
string _SQL,
List<OracleParameter> theList
)
{
OracleConnection cn = null;
OracleCommand cmd = null;
int theResult = -1;
try
{
cn = new OracleConnection(_dbConnectionString);
cn.Open();
cmd = new OracleCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (OracleParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(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
(
OracleConnection cn,
string _SQL
)
{
if (cn == null)
throw new Exception("OracleConnection cn为null。");
bool needOpen = false;
OracleCommand cmd = null;
int theResult = -1;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new OracleCommand();
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
(
OracleConnection cn,
string _SQL,
List<OracleParameter> theList
)
{
if (cn == null)
throw new Exception("OracleConnection cn为null。");
bool needOpen = false;
OracleCommand cmd = null;
int theResult = -1;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new OracleCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (OracleParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(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
(
OracleTransaction tran,
string _SQL
)
{
if (tran == null)
throw new Exception("OracleTransaction tran为null。");
OracleConnection cn = tran.Connection;
if (cn == null)
throw new Exception("OracleConnection cn为null。");
bool needOpen = false;
OracleCommand cmd = null;
int theResult = -1;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new OracleCommand();
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
(
OracleTransaction tran,
string _SQL,
List<OracleParameter> theList
)
{
if (tran == null)
throw new Exception("OracleTransaction tran为null。");
OracleConnection cn = tran.Connection;
if (cn == null)
throw new Exception("OracleConnection cn为null。");
bool needOpen = false;
OracleCommand cmd = null;
int theResult = -1;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new OracleCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (OracleParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(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="_SQL"></param>
/// <returns></returns>
public static object ExecuteScalar
(
string _SQL
)
{
OracleConnection cn = null;
OracleCommand cmd = null;
object theResult = null;
try
{
cn = new OracleConnection(dataBaseConnectionString);
cn.Open();
cmd = new OracleCommand();
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="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static object ExecuteScalar
(
string _SQL,
List<OracleParameter> theList
)
{
OracleConnection cn = null;
OracleCommand cmd = null;
object theResult = null;
try
{
cn = new OracleConnection(dataBaseConnectionString);
cn.Open();
cmd = new OracleCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (OracleParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(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="_dbConnectionString"></param>
/// <param name="_SQL"></param>
/// <returns></returns>
public static object ExecuteScalar
(
string _dbConnectionString,
string _SQL
)
{
OracleConnection cn = null;
OracleCommand cmd = null;
object theResult = null;
try
{
cn = new OracleConnection(_dbConnectionString);
cn.Open();
cmd = new OracleCommand();
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,
string _SQL,
List<OracleParameter> theList
)
{
OracleConnection cn = null;
OracleCommand cmd = null;
object theResult = null;
try
{
cn = new OracleConnection(_dbConnectionString);
cn.Open();
cmd = new OracleCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (OracleParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(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
(
OracleConnection cn,
string _SQL
)
{
if (cn == null)
throw new Exception("OracleConnection cn为null。");
bool needOpen = false;
OracleCommand cmd = null;
object theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new OracleCommand();
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
(
OracleConnection cn,
string _SQL,
List<OracleParameter> theList
)
{
if (cn == null)
throw new Exception("OracleConnection cn为null。");
bool needOpen = false;
OracleCommand cmd = null;
object theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new OracleCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (OracleParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(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
(
OracleTransaction tran,
string _SQL
)
{
if (tran == null)
throw new Exception("OracleTransaction tran为null。");
OracleConnection cn = tran.Connection;
if (cn == null)
throw new Exception("OracleConnection cn为null。");
bool needOpen = false;
OracleCommand cmd = null;
object theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new OracleCommand();
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
(
OracleTransaction tran,
string _SQL,
List<OracleParameter> theList
)
{
if (tran == null)
throw new Exception("OracleTransaction tran为null。");
OracleConnection cn = tran.Connection;
if (cn == null)
throw new Exception("OracleConnection cn为null。");
bool needOpen = false;
OracleCommand cmd = null;
object theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new OracleCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (OracleParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(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语句，获得一个记录
}
}
-
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace SlowX.DAL.DataBaseHelpers
{
/// <summary>
/// 数据库操作相关方法
/// </summary>
public class SqlServerSlowXDALHelper
{
/// <summary>
/// 数据库链接串
/// </summary>
public readonly static string dataBaseConnectionString = System.Configuration.ConfigurationManager.AppSettings["SlowX.DAL.DataBaseHelpers.dataBaseConnectionString"];
/// <summary>
/// 查询的表格名
/// </summary>
public const string srcTableName = "SelectDataTable";
#region 获得数据集 ExecuteDataSet
/// <summary>
/// 获得数据集
/// </summary>
/// <param name="_SQL"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet
(
string _SQL
)
{
SqlConnection cn = null;
SqlCommand cmd = null;
SqlDataAdapter da = null;
DataSet theResult = null;
try
{
cn = new SqlConnection(dataBaseConnectionString);
cn.Open();
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
da = new SqlDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
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="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet
(
string _SQL,
List<SqlParameter> theList
)
{
SqlConnection cn = null;
SqlCommand cmd = null;
SqlDataAdapter da = null;
DataSet theResult = null;
try
{
cn = new SqlConnection(dataBaseConnectionString);
cn.Open();
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (SqlParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(p);
}
}
da = new SqlDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
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>
/// <returns></returns>
public static DataSet ExecuteDataSet
(
string _dbConnectionString,
string _SQL
)
{
SqlConnection cn = null;
SqlCommand cmd = null;
SqlDataAdapter da = null;
DataSet theResult = null;
try
{
cn = new SqlConnection(_dbConnectionString);
cn.Open();
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
da = new SqlDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
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,
string _SQL,
List<SqlParameter> theList
)
{
SqlConnection cn = null;
SqlCommand cmd = null;
SqlDataAdapter da = null;
DataSet theResult = null;
try
{
cn = new SqlConnection(_dbConnectionString);
cn.Open();
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (SqlParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(p);
}
}
da = new SqlDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
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
(
SqlConnection cn,
string _SQL
)
{
if (cn == null)
throw new Exception("SqlConnection cn为null。");
bool needOpen = false;
SqlCommand cmd = null;
SqlDataAdapter da = null;
DataSet theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
da = new SqlDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
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
(
SqlConnection cn,
string _SQL,
List<SqlParameter> theList
)
{
if (cn == null)
throw new Exception("SqlConnection cn为null。");
bool needOpen = false;
SqlCommand cmd = null;
SqlDataAdapter da = null;
DataSet theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (SqlParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(p);
}
}
da = new SqlDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
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
(
SqlTransaction tran,
string _SQL
)
{
if (tran == null)
throw new Exception("SqlTransaction tran为null。");
SqlConnection cn = tran.Connection;
if (cn == null)
throw new Exception("SqlConnection cn为null。");
bool needOpen = false;
SqlCommand cmd = null;
SqlDataAdapter da = null;
DataSet theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
da = new SqlDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
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
(
SqlTransaction tran,
string _SQL,
List<SqlParameter> theList
)
{
if (tran == null)
throw new Exception("SqlTransaction tran为null。");
SqlConnection cn = tran.Connection;
if (cn == null)
throw new Exception("SqlConnection cn为null。");
bool needOpen = false;
SqlCommand cmd = null;
SqlDataAdapter da = null;
DataSet theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (SqlParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(p);
}
}
da = new SqlDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
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="_SQL"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
string _SQL
)
{
SqlConnection cn = null;
SqlCommand cmd = null;
int theResult = -1;
try
{
cn = new SqlConnection(dataBaseConnectionString);
cn.Open();
cmd = new SqlCommand();
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="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
string _SQL,
List<SqlParameter> theList
)
{
SqlConnection cn = null;
SqlCommand cmd = null;
int theResult = -1;
try
{
cn = new SqlConnection(dataBaseConnectionString);
cn.Open();
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (SqlParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(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="_dbConnectionString"></param>
/// <param name="_SQL"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
string _dbConnectionString,
string _SQL
)
{
SqlConnection cn = null;
SqlCommand cmd = null;
int theResult = -1;
try
{
cn = new SqlConnection(_dbConnectionString);
cn.Open();
cmd = new SqlCommand();
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="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
string _dbConnectionString,
string _SQL,
List<SqlParameter> theList
)
{
SqlConnection cn = null;
SqlCommand cmd = null;
int theResult = -1;
try
{
cn = new SqlConnection(_dbConnectionString);
cn.Open();
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (SqlParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(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
(
SqlConnection cn,
string _SQL
)
{
if (cn == null)
throw new Exception("SqlConnection cn为null。");
bool needOpen = false;
SqlCommand cmd = null;
int theResult = -1;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new SqlCommand();
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
(
SqlConnection cn,
string _SQL,
List<SqlParameter> theList
)
{
if (cn == null)
throw new Exception("SqlConnection cn为null。");
bool needOpen = false;
SqlCommand cmd = null;
int theResult = -1;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (SqlParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(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
(
SqlTransaction tran,
string _SQL
)
{
if (tran == null)
throw new Exception("SqlTransaction tran为null。");
SqlConnection cn = tran.Connection;
if (cn == null)
throw new Exception("SqlConnection cn为null。");
bool needOpen = false;
SqlCommand cmd = null;
int theResult = -1;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new SqlCommand();
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
(
SqlTransaction tran,
string _SQL,
List<SqlParameter> theList
)
{
if (tran == null)
throw new Exception("SqlTransaction tran为null。");
SqlConnection cn = tran.Connection;
if (cn == null)
throw new Exception("SqlConnection cn为null。");
bool needOpen = false;
SqlCommand cmd = null;
int theResult = -1;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (SqlParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(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="_SQL"></param>
/// <returns></returns>
public static object ExecuteScalar
(
string _SQL
)
{
SqlConnection cn = null;
SqlCommand cmd = null;
object theResult = null;
try
{
cn = new SqlConnection(dataBaseConnectionString);
cn.Open();
cmd = new SqlCommand();
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="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static object ExecuteScalar
(
string _SQL,
List<SqlParameter> theList
)
{
SqlConnection cn = null;
SqlCommand cmd = null;
object theResult = null;
try
{
cn = new SqlConnection(dataBaseConnectionString);
cn.Open();
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (SqlParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(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="_dbConnectionString"></param>
/// <param name="_SQL"></param>
/// <returns></returns>
public static object ExecuteScalar
(
string _dbConnectionString,
string _SQL
)
{
SqlConnection cn = null;
SqlCommand cmd = null;
object theResult = null;
try
{
cn = new SqlConnection(_dbConnectionString);
cn.Open();
cmd = new SqlCommand();
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,
string _SQL,
List<SqlParameter> theList
)
{
SqlConnection cn = null;
SqlCommand cmd = null;
object theResult = null;
try
{
cn = new SqlConnection(_dbConnectionString);
cn.Open();
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (SqlParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(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
(
SqlConnection cn,
string _SQL
)
{
if (cn == null)
throw new Exception("SqlConnection cn为null。");
bool needOpen = false;
SqlCommand cmd = null;
object theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new SqlCommand();
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
(
SqlConnection cn,
string _SQL,
List<SqlParameter> theList
)
{
if (cn == null)
throw new Exception("SqlConnection cn为null。");
bool needOpen = false;
SqlCommand cmd = null;
object theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (SqlParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(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
(
SqlTransaction tran,
string _SQL
)
{
if (tran == null)
throw new Exception("SqlTransaction tran为null。");
SqlConnection cn = tran.Connection;
if (cn == null)
throw new Exception("SqlConnection cn为null。");
bool needOpen = false;
SqlCommand cmd = null;
object theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new SqlCommand();
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
(
SqlTransaction tran,
string _SQL,
List<SqlParameter> theList
)
{
if (tran == null)
throw new Exception("SqlTransaction tran为null。");
SqlConnection cn = tran.Connection;
if (cn == null)
throw new Exception("SqlConnection cn为null。");
bool needOpen = false;
SqlCommand cmd = null;
object theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (SqlParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(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语句，获得一个记录
}
}