private DataTable ReturnTable(OleDbCommand cmd)
{
using (OleDbConnection selectConnection = Connection)
{
cmd.Connection = selectConnection;
try
{
adapter.SelectCommand = cmd;
DataTable dt = new DataTable();
if (!string.IsNullOrEmpty(_fieldName))
{
DataColumn dc = new DataColumn(_fieldName);
dc.AutoIncrement = true;
dc.AutoIncrementSeed = 1;
dt.Columns.Add(dc);
}
adapter.Fill(dt);
adapter.Dispose();
return(dt);
}
catch (OleDbException exception)
{
throw exception;
}
}
}
public void Dispose()
{
if (_updateAdapter != null)
{
_updateAdapter.Dispose();
_updateAdapter = null;
}
if (_schemaTable != null)
{
_schemaTable.Dispose();
_schemaTable = null;
}
GC.Collect(0);
}
public DataTable ReadFromFile(string commandText)
{
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this.sFilePath + ";Extended Properties=\"Excel 8.0;HDR=YES;\"";
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
DbDataAdapter adapter = factory.CreateDataAdapter();
DbCommand selectCommand = factory.CreateCommand();
selectCommand.CommandText = commandText;
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
selectCommand.Connection = connection;
adapter.SelectCommand = selectCommand;
DataSet cities = new DataSet();
adapter.Fill(cities);
connection.Close();
adapter.Dispose();
return(cities.Tables[0]);
}
/// <summary>
/// 返回DataSet对象。
/// </summary>
/// <param name="sql">sql语句名称</param>
/// <param name="parameters">参数数组</param>
/// <param name="strTableName">操作表的名称</param>
/// <returns>DataSet对象</returns>
/// <author>天志</author>
/// <log date="2007-04-05">创建</log>
public DataSet ExecuteDataSet(string sql, IDataParameter[] parameters, string tableName)
{
//初始化一个DataSet对象，一个DataAdapter对象
DataSet ds = new DataSet();
DbDataAdapter da = this.CreateDataAdapter(sql);
//初始化一个command对象
DbCommand cmdSql = this.GetPreCommand(sql, parameters);
try
{
// 返回DataSet对象
da.SelectCommand = cmdSql;
// 打开数据库连接
this.Open();
da.Fill(ds, tableName);
return(ds);
}
finally
{
//判断是否在事务中
// if (!this.bInTrans)
// {
// this.Close();
// }
cmdSql.Parameters.Clear();
cmdSql.Dispose();
da.Dispose();
ds.Dispose();
}
}
/// <summary>
/// 释放资源
/// </summary>
/// <param name="disposing"></param>
protected virtual void Dispose(bool disposing)
{
if (!disposing)
{
return;
}
if (_dataAdapter != null)
{
_dataAdapter.Dispose();
_dataAdapter = null;
}
if (_dbcmd != null)
{
_dbcmd.Dispose();
_dbcmd = null;
}
//WinForm
if (_connection != null) //WinForm
{
if (_connection.State == ConnectionState.Broken ||
_connection.State != ConnectionState.Closed)
{
_connection.Close();
}
}
}
public static DataSet ExecuteDataset(CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
{
DbCommand cmd = _factory.CreateCommand();
DbConnection conn = _factory.CreateConnection();
conn.ConnectionString = ConnectionString;
DbDataAdapter adapter = _factory.CreateDataAdapter();
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
adapter.SelectCommand = cmd;
DataSet ds = new DataSet();
adapter.Fill(ds);
cmd.Parameters.Clear();
return(ds);
}
catch
{
throw;
}
finally
{
adapter.Dispose();
conn.Close();
}
}
public static Hashtable SimpleQuery(string TableName, string Where, string OrderBy,
string Columns)
{
Hashtable HT = new Hashtable();
try
{
using (DbConnection Conn = Settings.Factory.CreateConnection())
{
Conn.ConnectionString = Settings.ConnectionString;
Conn.Open();
DataSet DS = new DataSet();
DataTable DT = new DataTable();
DbDataAdapter DAP = Settings.Factory.CreateDataAdapter();
DbCommand Comm = Settings.Factory.CreateCommand();
Comm.Connection = Conn;
string strCommand = "SELECT " + Columns + " FROM " + TableName + " " + Where + " " + OrderBy;
Comm.CommandText = strCommand;
DAP.SelectCommand = Comm;
DAP.Fill(DS);
DAP.Fill(DT);
HT.Add("DataSet", DS);
HT.Add("DataTable", DT);
HT.Add("DataCount", DS.Tables[0].Rows.Count);
Conn.Close();
DAP.Dispose();
Comm.Dispose();
}
}
catch (Exception Ex)
{
HT.Add("Error", Ex.Message);
}
return(HT);
}
private static void DisposeDataAdapter(DbDataAdapter dataAdapter)
{
if (dataAdapter != null)
{
dataAdapter.Dispose();
}
}
public DataTable ExecuteDbDataTable(DbCommand scdCommand)
{
DbConnection Conn = Conexao.abrirConexao();
scdCommand.Connection = Conn;
DbDataAdapter daAdapter = Conexao.obterDataAdapter(scdCommand.Connection);
try
{
DataTable dtTable = new DataTable();
daAdapter.SelectCommand = scdCommand;
daAdapter.Fill(dtTable);
return(dtTable);
}
catch (Exception ex)
{
throw new Exception(string.Format("Ocorreu um erro ao tentar acessar o Banco de Dados. Descrição:{0}", ex.Message));
}
finally
{
daAdapter.Dispose();
Conexao.fecharConexao(scdCommand.Connection);
scdCommand.Dispose();
}
}
/// <summary>
/// 更新表格
/// </summary>
/// <param name="query">sql语句</param>
/// <param name="dt">需要更新的表</param>
/// <param name="setAllValues">指定 update 语句中是包含所有列值还是仅包含更改的列值。</param>
/// <param name="conflictOption">指定将如何检测和解决对数据源的相互冲突的更改。</param>
/// <returns>对表进行更新操作</returns>
public bool UpdateTable(string query, DataTable dt, bool setAllValues, ConflictOption conflictOption)
{
DbDataAdapter adapter = null;
try
{
SetCommand(query, CommandType.Text);
adapter = DbProvider.CreateDataAdapter();
adapter.SelectCommand = command;
DbCommandBuilder builder = DbProvider.CreateCommandBuilder();
builder.DataAdapter = adapter;
builder.SetAllValues = setAllValues;
builder.ConflictOption = conflictOption;
adapter.Update(dt);
dt.AcceptChanges();
return(true);
}
catch (Exception ex)
{
ErroMsg = ex.Message;
return(false);
}
finally
{
this.parameters.Clear();
adapter?.Dispose();
}
}
/**
* @ 清理资源
* */
public void Close()
{
try
{
if (reader != null)
{
reader.Close();
}
if (conn != null && conn.State != ConnectionState.Closed)
{
conn.Close();
}
if (ccmd != null)
{
if (dbparas != null)
{
dbparas.Clear();
}
ccmd.Dispose();
}
if (adapter != null)
{
adapter.Dispose();
}
}
finally
{
ConnectionRequestCount--;
OpenedConnection = false;
}
}
/// <summary>
/// 运行SQL语句、参数化的SQL语句或者存储过程，返回DataSet。
/// 可以传入多条查询语句，返回的DataSet里会有多个DataTable
/// </summary>
/// <param name="text">查询语句或者存储过程的名称。
/// 比如select * from tableName1 select * from tableName2
/// 或者 Proc_xxxGetDataSet
/// </param>
/// <returns>返回DataSet</returns>
public virtual DataSet ExecuteFillDataSet(string text)
{
//设置command
_dal.SetCommand(text);
//创建一个DataAdapter，用于填充数据
DbDataAdapter da = CommonFactory.CreateDataAdapter(_dal.Command, _dal.DatabaseProvider);
//关联DataAdapter 和 Command
da.SelectCommand = _dal.Command;
try
{
var ds = new DataSet();
da.Fill(ds); //打开数据库，填充数据
return(ds);
}
catch (Exception ex)
{
_dal.SetError("ExecuteFillDataSet", text, ex.Message, _dal.ConnectionString); //处理错误
return(null);
}
finally
{
//自动关闭了，不用手动关闭。
da.Dispose();
}
}
/// <summary>
/// 用数据适配器填充一个数据集中一个表
/// </summary>
/// <param name="storeProcedureName">存储过程名</param>
/// <param name="parameters">参数名</param>
/// <param name="ds">数据集</param>
/// <param name="tableName">表名</param>
/// <param name="timeOut">超时时间</param>
public override void AdapterFillDataSet(string storeProcedureName, ParameterEx[] parameters, DataSet ds, string tableName, int timeOut)
{
try
{
IDbCommand cmd = this.GetCommand();
cmd.CommandText = storeProcedureName;
cmd.CommandType = CommandType.StoredProcedure;
//参数赋值
if (parameters != null && parameters.Length > 0)
{
SetParameters(cmd, parameters);
}
if (timeOut > 30) //默认为30秒
{
cmd.CommandTimeout = timeOut;
}
DbDataAdapter adapter = this.GetAdapter(cmd);
//回传数据
if (parameters != null && parameters.Length > 0)
{
ReturnParameters(cmd, parameters);
}
adapter.Fill(ds, tableName);
adapter.Dispose();
cmd.Dispose();
cmd = null;
}
catch (Exception err)
{
throw new Exception("AdapterFillDataSet\\DatabaseAccess" + err.Message);
}
}
public int DeleteDataTable(DataTable dt, string sql)
{
if (string.IsNullOrEmpty(sql))
{
throw new ArgumentNullException("sql", "A SQL query or stored procedure name is required");
}
if (dt == null)
{
throw new ArgumentNullException("dt", "DataSet cannot be null.");
}
DbDataAdapter adapter = null;
try
{
adapter = _dbProviderFactory.CreateDataAdapter();
adapter.DeleteCommand = Command;
adapter.DeleteCommand.CommandText = sql;
return(adapter.Update(dt));
}
finally
{
if (adapter.DeleteCommand != null)
{
adapter.DeleteCommand.Dispose();
}
adapter.Dispose();
}
}
public void Dispose()
{
CloseConnection();
if (_connection != null)
{
_connection.Dispose();
_connection = null;
}
if (_command != null)
{
_command.Dispose();
_command = null;
}
if (_dadaadpter != null)
{
_dadaadpter.Dispose();
_dadaadpter = null;
}
if (_transaction != null)
{
_transaction.Dispose();
_transaction = null;
}
_isDispose = true;
}
public DataTable ExecuteDbDataTable(DbCommand scdCommand, DbConnection Conn)
{
scdCommand.Connection = Conn;
DbDataAdapter daAdapter = Conexao.obterDataAdapter(scdCommand.Connection);
try
{
DataTable dtTable = new DataTable();
daAdapter.SelectCommand = scdCommand;
daAdapter.Fill(dtTable);
return(dtTable);
}
catch (Exception ex)
{
int numeroErro = LogEventError.Write(ex.Message);
throw new Exception("Ocorreu um erro ao tentar acessar o Banco de Dados. Err.ID " + numeroErro);
}
finally
{
daAdapter.Dispose();
scdCommand.Dispose();
}
}
public DataSet ExecuteDbDataSet(DbCommand scdCommand)
{
DbConnection Conn = Conexao.abrirConexao(ConnectionStringName);
scdCommand.Connection = Conn;
DbDataAdapter daAdapter = Conexao.obterDataAdapter(scdCommand.Connection);
try
{
DataSet dsDados = new DataSet();
daAdapter.SelectCommand = scdCommand;
daAdapter.Fill(dsDados);
daAdapter = null;
return(dsDados);
}
catch (Exception ex)
{
throw ex;
}
finally
{
daAdapter.Dispose();
Conexao.fecharConexao(scdCommand.Connection);
scdCommand.Dispose();
}
}
public DataTable dataTableQuery(string sql, List <DbParameter> dbParameters)
{
DataTable dt = new DataTable();
if (CreateConnection())
{
DbCommand cmd = dbConnection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
AddCommandPara(cmd, dbParameters);
try
{
DbDataAdapter da = ProviderFactory.GetDbProviderFactory(dbDialect).CreateDataAdapter();
da.SelectCommand = cmd;
da.Fill(dt);
da.Dispose();
da = null;
}
catch (Exception ex)
{
err = ex.ToString();
//throw;
}
finally
{
if (null != cmd)
{
cmd.Dispose();
}
}
}
return(dt);
}
public DataTable ExecuteDbDataTable(DbCommand scdCommand)
{
DbConnection Conn = ConexaoAntigo.abrirConexao();
scdCommand.Connection = Conn;
DbDataAdapter daAdapter = ConexaoAntigo.obterDataAdapter(scdCommand.Connection);
try
{
DataTable dtTable = new DataTable();
daAdapter.SelectCommand = scdCommand;
daAdapter.Fill(dtTable);
return(dtTable);
}
catch (Exception ex)
{
throw ex;
}
finally
{
daAdapter.Dispose();
ConexaoAntigo.fecharConexao(scdCommand.Connection);
scdCommand.Dispose();
}
}
/// <summary>
/// 事务更新
/// </summary>
/// <param name="dt">更新表</param>
/// <param name="query">查询语句</param>
/// <param name="setAllValues"></param>
/// <param name="conflictOption"></param>
/// <returns></returns>
public bool TransactionUpdateDataTable(ref DataTable dt, string query, bool setAllValues = false, ConflictOption conflictOption = ConflictOption.OverwriteChanges)
{
bool result;
try
{
SetTransCommand(query);
DbDataAdapter adapter = DbProvider.CreateDataAdapter();
adapter.SelectCommand = this.transCommand;
DbCommandBuilder builder = DbProvider.CreateCommandBuilder();
builder.DataAdapter = adapter;
builder.SetAllValues = setAllValues;
builder.ConflictOption = conflictOption;
adapter.Update(dt);
result = true;
adapter.Dispose();
}
catch (Exception ex)
{
result = false;
this.ErroMsg = ex.Message;
this.TransctionRollBack();
}
return(result);
}
/// <summary>
/// dispose方法
/// </summary>
/// <param name="disposing"></param>
protected virtual void Dispose(bool disposing)
{
if (Disposed)
{
return;
}
if (disposing)
{
if (DbConnection != null)
{
DbConnection.Dispose();
}
if (DbCommand != null)
{
//_dbCommand.Parameters.Clear();
DbCommand.Dispose();
}
if (DbTransaction != null)
{
DbTransaction.Dispose();
}
if (DbaAdapter != null)
{
DbaAdapter.Dispose();
}
}
DbaAdapter = null;
DbConnection = null;
DbCommand = null;
DbTransaction = null;
Disposed = true;
}
public void Fill(DBServerType dbType, string connectionString, string sqlstr, DataSet ds, String tbl)
{
DbConnection sqlconn = CreateDbConnection(dbType, connectionString);
DbCommand sqlcomm = CreateDbCommand(dbType);
DbDataAdapter sqlda = CreateDbDataAdapter(dbType);
sqlcomm.CommandType = CommandType.Text;
sqlcomm.CommandText = sqlstr;
sqlcomm.Connection = sqlconn;
sqlda.SelectCommand = sqlcomm;
try
{
if (sqlconn.State == ConnectionState.Closed)
{
sqlconn.Open();
}
sqlda.Fill(ds, tbl);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlconn.State == ConnectionState.Open)
{
sqlconn.Close();
}
sqlda.Dispose();
sqlcomm.Dispose();
sqlconn.Dispose();
}
}
public DataSet ExecuteDbDataSet(DbCommand scdCommand)
{
DbConnection Conn = Conexao.abrirConexao();
scdCommand.Connection = Conn;
DbDataAdapter daAdapter = Conexao.obterDataAdapter(scdCommand.Connection);
try
{
DataSet dsDados = new DataSet();
daAdapter.SelectCommand = scdCommand;
daAdapter.Fill(dsDados);
daAdapter = null;
return(dsDados);
}
catch (Exception ex)
{
int numeroErro = LogEventError.Write(ex.Message);
throw new Exception("Ocorreu um erro ao tentar acessar o Banco de Dados. Err.ID " + numeroErro);
}
finally
{
daAdapter.Dispose();
Conexao.fecharConexao(scdCommand.Connection);
scdCommand.Dispose();
}
}
public static DataTable QueryDT(string SQLString)
{
using (IDbConnection connection = new OracleConnection(connStr))
{
DataTable dt = null;
DataSet ds = new DataSet();
DbDataAdapter command = null;
try
{
connection.Open();
command = defaultPrv.getDataAdapter(SQLString, connection);
PrintSqlTool.printSqlToText(SQLString, dsr);
command.SelectCommand.CommandTimeout = 1200;
command.Fill(ds, "temp");
dt = ds.Tables["temp"];
}
catch (DbException ex)
{
throw new Exception(ex.Message);
}
finally
{
command.Dispose();
connection.Close();
}
return(dt);
}
}
protected void RunSqlQuery(SqlDataQueryContext ctx, out DataTable dataTbl, String sqlCmd, params DbParameter[] dbParmArr)
{
DbConnection dbConn = ctx.DbConnection;
DbCommand dbCmd = dbConn.CreateCommand();
DbDataAdapter dbAdapter = mS_DbObjFactory.CreateDataAdapter();
try
{
dbCmd.CommandType = CommandType.Text;
dbCmd.CommandText = sqlCmd;
dbCmd.Parameters.AddRange(dbParmArr);
dataTbl = new DataTable();
dbAdapter.SelectCommand = dbCmd;
dbAdapter.Fill(dataTbl);
}
finally
{
dbAdapter.Dispose();
dbAdapter = null;
dbCmd.Dispose();
dbCmd = null;
}
}
protected virtual void Dispose(bool disposing)
{
try
{
if (disposing)
{
if (sql_con != null)
{
sql_con.Close();
sql_con.Dispose();
sql_con = null;
}
if (sql_cmd != null)
{
sql_cmd.Dispose();
sql_cmd = null;
}
if (sql_da != null)
{
sql_da.Dispose();
sql_da = null;
}
}
} catch (Exception caught)
{
Console.WriteLine(caught.Message);
}
}
/// <summary>
/// 执行命令返回DataSet
/// </summary>
/// <param name="commandType">命令类型</param>
/// <param name="sql">sql语句或存储过程名称</param>
/// <param name="parameters">参数数组</param>
/// <returns>DataSet</returns>
public virtual DataSet GetDataSet(CommandType commandType, string sqlOrProcName, IEnumerable <DbParameter> parameters)
{
DbConnection con = CreateConnection();
DbCommand cmd = CreateCommand(con, commandType, sqlOrProcName, parameters);
DataSet set = new DataSet();
DbDataAdapter adapter = this.provider.CreateDataAdapter();
try
{
con.Open();
adapter.SelectCommand = cmd;
adapter.Fill(set);
return(set);
}
catch
{
throw;
}
finally
{
adapter.Dispose();
cmd.Dispose();
con.Close();
}
}
public DataSet ExecuteDataSet(string sql)
{
DataSet dataSet = null;
DbCommand cmd = null;
DbDataAdapter da = null;
DbProviderFactory factory = null;
try
{
factory = DbProviderFactories.GetFactory(dataSource.Provider);
da = factory.CreateDataAdapter();
cmd = conn.CreateCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
dataSet = new DataSet();
da.SelectCommand = cmd;
da.Fill(dataSet);
}
catch (Exception ex)
{
if (da != null)
{
da.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
logger.Write(Severity.Error, ex.ToString());
throw ex;
}
finally
{
if (da != null)
{
da.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
}
return(dataSet);
}
/// <summary>
/// 用数据适配器填充一个数据集--加密的方法，屏蔽数据库跟踪器
/// 2013-1-31 增加
/// </summary>
/// <param name="commandText"></param>
/// <param name="ds"></param>
/// <param name="timeout"></param>
/// <param name="BeginTransaction">自动开启事务</param>
public virtual void AdapterFillDataSet_Encryption(string commandText, DataSet ds, int timeout, bool AutoTransaction)
{
try
{
if (AutoTransaction)
{
this.BeginTransaction();
}
string strProcedure_drop = @"
IF EXISTS (SELECT name FROM sysobjects WHERE name = N'{0}'
AND type = 'P')
DROP PROCEDURE {0}
";
string strProcedure_create = @"
CREATE PROCEDURE [dbo].{0}
with encryption
as
begin
{1}
DROP PROCEDURE [dbo].{0}
end
";
string strProcedure_name = "SP_tmp_" + Guid.NewGuid().ToString().Replace("-", "_");
strProcedure_create = string.Format(strProcedure_create, new object[] { strProcedure_name, commandText });
strProcedure_drop = string.Format(strProcedure_drop, new object[] { strProcedure_name });
try
{
this.DoCommand(strProcedure_drop);
this.DoCommand(strProcedure_create);
DbDataAdapter adapter = this.GetAdapter(" exec " + strProcedure_name, timeout);
adapter.Fill(ds);
adapter.Dispose();
this.CommitTransaction();
}
catch
{
if (AutoTransaction)
{
this.RollbackTransaction();
}
throw new Exception("AdapterFillDataSet_Encryption:加密运行脚本出错");
}
finally
{
this.DoCommand(strProcedure_drop);
}
}
catch (Exception err)
{
throw new Exception("AdapterFillDataSet_Encryption:" + err.Message);
}
}
public static Hashtable SecurityQuery(string TableName, Hashtable Where, string OrderBy, string Columns)
{
Hashtable HT = new Hashtable();
try
{
using (DbConnection Conn = Settings.Factory.CreateConnection())
{
Conn.ConnectionString = Settings.ConnectionString;
Conn.Open();
DataSet DS = new DataSet();
DataTable DT = new DataTable();
DbDataAdapter DAP = Settings.Factory.CreateDataAdapter();
DbCommand Comm = Settings.Factory.CreateCommand();
Comm.Connection = Conn;
Comm.CommandText = "SELECT " + Columns + " FROM " + TableName + " {0} " + OrderBy;
string strWhere = "";
int qCount = 0;
foreach (string var in Where.Keys as ICollection)
{
if (var[0] != '@')
{
if ((qCount / 2) == 0)
{
strWhere += " " + Where["@" + qCount] + " ";
}
strWhere += "[" + var + "] = @" + var + " ";
qCount++;
DbParameter Parameter = Settings.Factory.CreateParameter();
Parameter.ParameterName = "@" + var;
Parameter.Value = Where[var];
Comm.Parameters.Add(Parameter);
}
}
strWhere = "Where " + strWhere;
string strCommand = string.Format(Comm.CommandText, strWhere);
Comm.CommandText = strCommand;
DAP.SelectCommand = Comm;
DAP.Fill(DS);
DAP.Fill(DT);
HT.Add("DataSet", DS);
HT.Add("DataTable", DT);
HT.Add("DataCount", DS.Tables[0].Rows.Count);
Conn.Close();
DAP.Dispose();
Comm.Dispose();
}
}
catch (Exception Ex)
{
Console.WriteLine("SecurityQuery hata:" + Ex.Message);
OlayGunluk.Olay("SecurityQuery hata:" + Ex.Message);
HT.Add("Error", Ex.Message);
}
return(HT);
}