/**/
/// <summary>
/// 执行查询语句，返回IDataReader
/// </summary>
/// <param name="strSQL">查询语句</param>
/// <returns>IDataReader</returns>
public IDataReader ExecuteReader(string strSQL)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
System.Data.IDbCommand iCmd = GetCommand();
try
{
PrepareCommand(out iCmd, iConn, null, strSQL, null);
System.Data.IDataReader iReader = iCmd.ExecuteReader();
iCmd.Parameters.Clear();
return(iReader);
}
catch (System.Exception e)
{
throw new Exception(e.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
/**/
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public DataSet Query(string sqlString)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
using (System.Data.IDbCommand iCmd = GetCommand(sqlString, iConn))
{
DataSet ds = new DataSet();
iConn.Open();
try
{
System.Data.IDataAdapter iAdapter = this.GetAdapater(sqlString, iConn);
iAdapter.Fill(ds);
return(ds);
}
catch (System.Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
/**/
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="sqlString">查询语句</param>
/// <param name="dataSet">要填充的DataSet</param>
/// <param name="tableName">要填充的表名</param>
/// <returns>DataSet</returns>
public DataSet Query(string sqlString, DataSet dataSet, string tableName)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
using (System.Data.IDbCommand iCmd = GetCommand(sqlString, iConn))
{
iConn.Open();
try
{
System.Data.IDataAdapter iAdapter = this.GetAdapater(sqlString, iConn);
((OleDbDataAdapter)iAdapter).Fill(dataSet, tableName);
return(dataSet);
}
catch (System.Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
/**/
/// <summary>
/// 执行SQL语句 返回存储过程
/// </summary>
/// <param name="sqlString">Sql语句</param>
/// <param name="dataSet">要填充的DataSet</param>
/// <param name="startIndex">开始记录</param>
/// <param name="pageSize">页面记录大小</param>
/// <param name="tableName">表名称</param>
/// <returns>DataSet</returns>
public DataSet Query(string sqlString, DataSet dataSet, int startIndex, int pageSize, string tableName)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
iConn.Open();
try
{
System.Data.IDataAdapter iAdapter = this.GetAdapater(sqlString, iConn);
((OleDbDataAdapter)iAdapter).Fill(dataSet, startIndex, pageSize, tableName);
return(dataSet);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
/// <summary>
/// 执行查询语句
/// </summary>
/// <param name="SqlString">查询语句</param>
/// <returns>DataTable </returns>
public DataTable ExecuteQuery(string SqlString, string Proc)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
using (System.Data.IDbCommand iCmd = GetCommand(SqlString, iConn))
{
iCmd.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
try
{
System.Data.IDataAdapter iDataAdapter = this.GetAdapater(SqlString, iConn);
iDataAdapter.Fill(ds);
}
catch (System.Exception e)
{
throw new Exception(e.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
return(ds.Tables[0]);
}
}
}
/// <summary>
///
/// </summary>
/// <param name="Sql"></param>
/// <returns></returns>
public DataView ExeceuteDataView(string Sql)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
using (System.Data.IDbCommand iCmd = GetCommand(Sql, iConn))
{
DataSet ds = new DataSet();
try
{
System.Data.IDataAdapter iDataAdapter = this.GetAdapater(Sql, iConn);
iDataAdapter.Fill(ds);
return(ds.Tables[0].DefaultView);
}
catch (System.Exception e)
{
throw new Exception(e.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
/**/
/* / <summary> */
/*
* / 执行查询语句
* / </summary>
* / <param name="SqlString">查询语句</param>
* / <returns>DataTable </returns>
*/
public DataTable ExecuteQuery(string sqlString)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
/* System.Data.IDbCommand iCmd = GetCommand(sqlString,iConn); */
DataSet ds = new DataSet();
try
{
System.Data.IDataAdapter iAdapter = this.GetAdapater(sqlString, iConn);
iAdapter.Fill(ds);
}
catch (System.Exception e)
{
throw new Exception(e.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
return(ds.Tables[0]);
}
}
/**/
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public int ExecuteSqlTran(ArrayList SQLStringList)
{
int i = 1;
using (System.Data.IDbConnection iConn = this.GetConnection())
{
iConn.Open();
using (System.Data.IDbCommand iCmd = GetCommand())
{
iCmd.Connection = iConn;
using (System.Data.IDbTransaction iDbTran = iConn.BeginTransaction())
{
iCmd.Transaction = iDbTran;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
iCmd.CommandText = strsql;
iCmd.ExecuteNonQuery();
}
}
iDbTran.Commit();
}
catch (System.Exception ex)
{
iDbTran.Rollback();
i = -1;
LogHelper.WriteLog(ex);
return(i);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
return(i);
}
}
}
}
/**/
/// <summary>
/// 执行查询语句
/// </summary>
/// <param name="SqlString">查询语句</param>
/// <returns>DataTable </returns>
public DataTable ExecuteQuery(string sqlString)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
//System.Data.IDbCommand iCmd = GetCommand(sqlString,iConn);
DataSet ds = new DataSet();
try
{
System.Data.IDataAdapter iAdapter = this.GetAdapater(sqlString, iConn);
iAdapter.Fill(ds);
}
catch (System.Exception ex)
{
LogHelper.WriteLog(ex, sqlString);
return(null);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
return(ds.Tables[0]);
}
}
/**/
/* / <summary> */
/*
* / 执行查询语句，返回List
* / </summary>
* / <param name="SQLString">查询语句</param>
* / <returns>List</returns>
*/
public IList <T> ExcuteQuery <T>(String sql) where T : class, new()
{
DataSet ds = new DataSet();
using (System.Data.IDbConnection iConn = this.GetConnection())
{
try
{
System.Data.IDataAdapter iAdapter = this.GetAdapater(sql, iConn);
iAdapter.Fill(ds);
}
catch (System.Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
IList <T> modelist = new List <T>();
//获取类T中所有属性
PropertyInfo[] pros = typeof(T).GetProperties();
foreach (DataRow row in ds.Tables[0].Rows)
{
T model = new T();
foreach (PropertyInfo pro in pros)
{
//用来设置类T中对应的公共属性的值为记录中对应属性名的值
//例如把UserInfo的Number属性值=row【“NUmber”】
//要求类T中的公共公共属性名必须和数据表中的字段名一致（包括大小写）
if (row[pro.Name] is DBNull)
{
pro.SetValue(model, "", null);
}
else if (row[pro.Name] is String)
{
pro.SetValue(model, row[pro.Name], null);
}
else if (row[pro.Name] is int)
{
pro.SetValue(model, row[pro.Name], null);
}
else
{
pro.SetValue(model, row[pro.Name].ToString(), null);
}
}
modelist.Add(model);
}
return(modelist);
}
protected virtual DataSet QueryDataSetExec(QueryContext query)
{
System.Data.DataSet ret = new DataSet();
using (System.Data.IDbCommand cmd = this.InitCommand(query))
{
System.Data.IDataAdapter reader = DataAdapterFactory(cmd);
reader.Fill(ret);
// !!! tester performance entre un dataadaptater et un simple datareader
}
return(ret);
}
protected static DataTable ExecuteAssementExecutionCommand(string command)
{
System.Data.IDataAdapter adapter = DataAdapterFactory.CreateAssesmentExecutionAdapter(command);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
if (dataSet.Tables.Count == 0)
{
return(null);
}
return(dataSet.Tables[0]);
}
/**/
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlDataReader</returns>
public SqlDataReader RunProcedure(string storedProcName, params IDataParameter[] parameters)
{
System.Data.IDbConnection iConn = this.GetConnection();
{
iConn.Open();
using (SqlCommand sqlCmd = BuildQueryCommand(iConn, storedProcName, parameters))
{
return(sqlCmd.ExecuteReader(CommandBehavior.CloseConnection));
}
}
}
/**/
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <returns>DataSet</returns>
public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
DataSet dataSet = new DataSet();
iConn.Open();
System.Data.IDataAdapter iDA = this.GetAdapater();
iDA = this.GetAdapater(BuildQueryCommand(iConn, storedProcName, parameters));
((SqlDataAdapter)iDA).Fill(dataSet, tableName);
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
return(dataSet);
}
}
/// <summary>
/// 执行查询语句，返回IDataAdapter
/// </summary>
/// <param name="strSQL">查询语句</param>
/// <returns>IDataAdapter</returns>
public IDataAdapter ExecuteReader(string strSQL)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
iConn.Open();
try
{
System.Data.IDataAdapter iAdapter = this.GetAdapater(strSQL, iConn);
return(iAdapter);
}
catch (System.Exception e)
{
throw new Exception(e.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
