public int ExecuteNonQuery(string connKey, CommandType cmdType, string cmdText, int timeout, params DbParameter[] commandParameters)
{
string connectionString;
GetConnectionInfo(connKey, out connectionString);
DbCommand cmd = _dbFactory.CreateCommand();
ConnectionWrapper <DbConnection> wrapper = null;
try
{
wrapper = GetOpenConnection(connectionString, _dbFactory);
PrepareCommand(cmd, wrapper.Connection, null, cmdType, cmdText, timeout, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return(val);
}
catch (System.Exception ex)
{
throw new DataAccessException(ex, connectionString, cmdText, commandParameters);
}
finally
{
if (wrapper != null)
{
wrapper.Dispose();
}
}
}
public void FetchStoredProcedures(
Func <IDbCommand, List <IDbDataParameter>, string, string, bool> procedureProcessor,
Func <IDbCommand, List <IDbDataParameter>, string, string, bool> functionProcessor,
bool continueOnProcessorException = false, string dbName = "")
{
VerifyArgument.IsNotNull("procedureProcessor", procedureProcessor);
VerifyArgument.IsNotNull("functionProcessor", functionProcessor);
VerifyConnection();
DataTable proceduresDataTable = GetSchema(_connection);
DataColumn procedureDataColumn = GetDataColumn(proceduresDataTable, "ROUTINE_NAME");
DataColumn procedureTypeColumn = GetDataColumn(proceduresDataTable, "ROUTINE_TYPE");
DataColumn procedureSchemaColumn = GetDataColumn(proceduresDataTable, "SPECIFIC_SCHEMA");
// ROUTINE_CATALOG - ROUTINE_SCHEMA ,SPECIFIC_SCHEMA
foreach (DataRow row in proceduresDataTable.Rows)
{
var type = row[procedureTypeColumn];
if (type.ToString().ToUpperInvariant() == "SQL_TABLE_VALUED_FUNCTION")
{
continue;
}
string fullProcedureName = GetFullProcedureName(row, procedureDataColumn, procedureSchemaColumn);
using (
IDbCommand command = _factory.CreateCommand(_connection, CommandType.StoredProcedure,
fullProcedureName))
{
try
{
List <IDbDataParameter> parameters = GetProcedureParameters(command);
//string helpText = FetchHelpTextContinueOnException(fullProcedureName, _connection);
string helpText = "";
if (IsStoredProcedure(row, procedureTypeColumn))
{
procedureProcessor(command, parameters, helpText, fullProcedureName);
}
else if (IsFunction(row, procedureTypeColumn))
{
functionProcessor(command, parameters, helpText, fullProcedureName);
}
else if (IsTableValueFunction(row, procedureTypeColumn))
{
functionProcessor(command, parameters, helpText,
CreateTVFCommand(fullProcedureName, parameters));
}
}
catch (Exception)
{
if (!continueOnProcessorException)
{
throw;
}
}
}
}
}
public override void Execute(string template, params object[] args)
{
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
using (var command = factory.CreateCommand(String.Format(template, args), Connection))
{
command.CommandTimeout = Options.Timeout;
command.ExecuteNonQuery();
}
}
public override bool Exists(string template, params object[] args)
{
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
using (var command = factory.CreateCommand(String.Format(template, args), Connection, Transaction))
using (var reader = command.ExecuteReader())
{
return(reader.Read());
}
}
public bool Connect(string connectionString, CommandType commandType, string commandText)
{
_connection = _factory.CreateConnection(connectionString);
VerifyArgument.IsNotNull("commandText", commandText);
if (commandText.ToLower().StartsWith("select "))
{
commandType = CommandType.Text;
}
_command = _factory.CreateCommand(_connection, commandType, commandText);
_connection.Open();
return(true);
}
public static DataTable ExecuteDataTable(string connStrKey, CommandType cmdType, string cmdText, object parameter = null)
{
cmdText = ProcessDbName(cmdText);
IDbFactory dbFactory = GetDbFactory(connStrKey);
string connectionString = GetConnectionString(connStrKey);
DbParameter[] commandParameters = DataMapper.BuildDbParameters(cmdText, parameter, dbFactory.CreateParameter, dbFactory.BuildParameterName);
DbCommand cmd = dbFactory.CreateCommand();
ConnectionWrapper wrapper = null;
DataTable table = new DataTable();
try
{
wrapper = GetOpenConnection(connectionString, dbFactory);
PrepareCommand(cmd, wrapper.Connection, null, cmdType, cmdText, commandParameters);
DbDataAdapter sda = dbFactory.CreateDataAdapter();
sda.SelectCommand = cmd;
sda.Fill(table);
cmd.Parameters.Clear();
}
catch (Exception ex)
{
throw new DaoSqlException(ex, connectionString, cmdText, commandParameters);
}
finally
{
if (wrapper != null)
{
wrapper.Dispose();
}
}
return(table);
}
public static object ExecuteScalar(string connStrKey, CommandType cmdType, string cmdText, object parameter = null)
{
cmdText = ProcessDbName(cmdText);
IDbFactory dbFactory = GetDbFactory(connStrKey);
string connectionString = GetConnectionString(connStrKey);
DbParameter[] commandParameters = DataMapper.BuildDbParameters(cmdText, parameter, dbFactory.CreateParameter, dbFactory.BuildParameterName);
DbCommand cmd = dbFactory.CreateCommand();
ConnectionWrapper wrapper = null;
try
{
wrapper = GetOpenConnection(connectionString, dbFactory);
PrepareCommand(cmd, wrapper.Connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return(val);
}
catch (Exception ex)
{
throw new DaoSqlException(ex, connectionString, cmdText, commandParameters);
}
finally
{
if (wrapper != null)
{
wrapper.Dispose();
}
}
}
public override DataSet Read(string template, params object[] args)
{
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
var ds = new DataSet();
using (var command = factory.CreateCommand(String.Format(template, args), Connection, Transaction))
{
var adapter = factory.CreateDataAdapter(command);
adapter.Fill(ds);
return(ds);
}
}
public override void Execute(string template, params object[] args)
{
if (template == null)
{
throw new ArgumentNullException("template");
}
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
using (var command = _factory.CreateCommand(String.Format(template, args), Connection))
{
command.ExecuteNonQuery();
}
}
public override bool Exists(string template, params object[] args)
{
if (template == null)
{
throw new ArgumentNullException("template");
}
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
using (var command = _factory.CreateCommand(String.Format(template, args), Connection))
using (var reader = command.ExecuteReader())
{
return(reader.Read());
}
}
public void Create(string[] columnDefinitions)
{
var sb = new StringBuilder();
if (!string.IsNullOrEmpty(_schema))
{
sb.AppendFormat("CREATE SCHEMA {0} ", quoter.QuoteSchemaName(_schema));
}
var columns = string.Join(", ", columnDefinitions);
sb.AppendFormat("CREATE TABLE {0} ({1})", NameWithSchema, columns);
using (var command = Factory.CreateCommand(sb.ToString(), Connection, Transaction))
{
command.ExecuteNonQuery();
}
}
/// <summary>
/// 创建命令
/// </summary>
/// <returns></returns>
private bool CreateCommand(string strCmdText, CommandType cmdType)
{
_dbCommand = _dbFactory.CreateCommand();
_dbCommand.Connection = _dbConn;
_dbCommand.CommandText = strCmdText;
_dbCommand.CommandType = cmdType;
return(true);
}
public bool ReturnConnection(CommandType commandType, string commandText)
{
commandType = SetCommandType(commandText, commandType);
_command = _factory.CreateCommand(_connection, commandType, commandText, CommandTimeout);
if (!_testing)
{
_connection.Open();
}
return(true);
}
public void FetchStoredProcedures(Func <IDbCommand, List <IDbDataParameter>, List <IDbDataParameter>, string, string, bool> procedureProcessor, Func <IDbCommand, List <IDbDataParameter>, List <IDbDataParameter>, string, string, bool> functionProcessor, bool continueOnProcessorException, string dbName)
{
VerifyArgument.IsNotNull("procedureProcessor", procedureProcessor);
VerifyArgument.IsNotNull("functionProcessor", functionProcessor);
VerifyConnection();
_owner = dbName;
var proceduresDataTable = GetSchema(_connection);
foreach (DataRow row in proceduresDataTable.Rows)
{
var type = row["ROUTINE_TYPE"];
if (type.ToString().ToUpperInvariant() == "FUNCTION")
{
continue;
}
var fullProcedureName = row["NAME"].ToString();
if (row["DB"].ToString().Equals(dbName, StringComparison.OrdinalIgnoreCase))
{
using (IDbCommand command = _factory.CreateCommand(_connection, CommandType.StoredProcedure, _owner + "." + fullProcedureName, CommandTimeout))
{
TryProcessProcedure(procedureProcessor, continueOnProcessorException, dbName, fullProcedureName, command);
}
}
}
}
public override bool Exists(string template, params object[] args)
{
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
using (var command = factory.CreateCommand(String.Format(template, args), Connection))
{
command.CommandTimeout = Options.Timeout;
using (var reader = command.ExecuteReader())
{
try
{
return(reader.Read());
}
catch
{
return(false);
}
}
}
}
public static DbDataReader ExecuteReader(string connStrKey, CommandType cmdType, string cmdText, object parameter = null)
{
cmdText = ProcessDbName(cmdText);
IDbFactory dbFactory = GetDbFactory(connStrKey);
string connectionString = GetConnectionString(connStrKey);
DbParameter[] commandParameters = DataMapper.BuildDbParameters(cmdText, parameter, dbFactory.CreateParameter, dbFactory.BuildParameterName);
DbCommand cmd = dbFactory.CreateCommand();
DbConnection conn = GetOpenConnection(connectionString, dbFactory).Connection;
CommandBehavior cmdBehavior;
if (Transaction.Current != null)
{
cmdBehavior = CommandBehavior.Default;
}
else
{
cmdBehavior = CommandBehavior.CloseConnection;
}
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
DbDataReader rdr = cmd.ExecuteReader(cmdBehavior);
//cmd.Parameters.Clear();
return(rdr);
}
catch (Exception ex)
{
if (conn != null)
{
try
{
conn.Close();
conn.Dispose();
}
catch { }
}
throw new DaoSqlException(ex, connectionString, cmdText, commandParameters);;
}
}
public void FetchStoredProcedures(Func <IDbCommand, List <IDbDataParameter>, List <IDbDataParameter>, string, string, bool> procedureProcessor, Func <IDbCommand, List <IDbDataParameter>, List <IDbDataParameter>, string, string, bool> functionProcessor, bool continueOnProcessorException = false, string dbName = "")
{
VerifyArgument.IsNotNull("procedureProcessor", procedureProcessor);
VerifyArgument.IsNotNull("functionProcessor", functionProcessor);
VerifyConnection();
_owner = dbName;
DataTable proceduresDataTable = GetSchema(_connection);
// ROUTINE_CATALOG - ROUTINE_SCHEMA ,SPECIFIC_SCHEMA
foreach (DataRow row in proceduresDataTable.Rows)
{
var type = row["ROUTINE_TYPE"];
if (type.ToString().ToUpperInvariant() == "FUNCTION")
{
continue;
}
string fullProcedureName = row["NAME"].ToString();
if (row["DB"].ToString().Equals(dbName, StringComparison.OrdinalIgnoreCase))
{
using (IDbCommand command = _factory.CreateCommand(_connection, CommandType.StoredProcedure, _owner + "." + fullProcedureName))
{
try
{
List <IDbDataParameter> outParameters;
List <IDbDataParameter> parameters = GetProcedureParameters(command, dbName, fullProcedureName, out outParameters);
string helpText = FetchHelpTextContinueOnException(fullProcedureName, _connection);
procedureProcessor(command, parameters, outParameters, helpText, fullProcedureName);
}
catch (Exception)
{
if (!continueOnProcessorException)
{
throw;
}
}
}
}
}
}
public void FetchStoredProcedures(Func <IDbCommand, List <IDbDataParameter>, List <IDbDataParameter>, string, string, bool> procedureProcessor, Func <IDbCommand, List <IDbDataParameter>, List <IDbDataParameter>, string, string, bool> functionProcessor, bool continueOnProcessorException = false, string dbName = "")
{
VerifyArgument.IsNotNull("procedureProcessor", procedureProcessor);
VerifyArgument.IsNotNull("functionProcessor", functionProcessor);
VerifyConnection();
var proceduresDataTable = GetSchema(_connection);
// ReSharper disable once LoopCanBePartlyConvertedToQuery
foreach (DataRow row in proceduresDataTable.Rows)
{
var type = row["proretset"];
if (type.ToString().ToUpperInvariant() == "FALSE")
{
continue;
}
var fullProcedureName = row["Name"].ToString();
if (row["Db"].ToString() == dbName)
{
using (
var command = _factory.CreateCommand(_connection, CommandType.StoredProcedure,
fullProcedureName))
{
try
{
List <IDbDataParameter> outParameters;
var parameters = GetProcedureParameters(command, fullProcedureName, out outParameters);
var helpText = FetchHelpTextContinueOnException(fullProcedureName, _connection);
procedureProcessor(command, parameters, outParameters, helpText, fullProcedureName);
}
catch (Exception)
{
if (!continueOnProcessorException)
{
throw;
}
}
}
}
}
}
public void FetchStoredProcedures(Func <IDbCommand, List <IDbDataParameter>, List <IDbDataParameter>, string, string, bool> procedureProcessor, Func <IDbCommand, List <IDbDataParameter>, List <IDbDataParameter>, string, string, bool> functionProcessor, bool continueOnProcessorException = false, string dbName = "")
{
VerifyArgument.IsNotNull("procedureProcessor", procedureProcessor);
VerifyArgument.IsNotNull("functionProcessor", functionProcessor);
VerifyConnection();
DataTable proceduresDataTable = GetSchema(_connection);
// ROUTINE_CATALOG - ROUTINE_SCHEMA ,SPECIFIC_SCHEMA
foreach (DataRow row in proceduresDataTable.Rows)
{
string fullProcedureName = row["Name"].ToString();
if (row["Db"].ToString() == dbName)
{
using (
IDbCommand command = _factory.CreateCommand(_connection, CommandType.StoredProcedure,
fullProcedureName))
{
try
{
List <IDbDataParameter> outParameters;
List <IDbDataParameter> parameters = GetProcedureParameters(command, dbName, fullProcedureName, out outParameters);
string helpText = FetchHelpTextContinueOnException(fullProcedureName, _connection);
procedureProcessor(command, parameters, outParameters, helpText, fullProcedureName);
}
catch (Exception)
{
if (!continueOnProcessorException)
{
throw;
}
}
}
}
}
}
public void FetchStoredProcedures(Func <IDbCommand, List <IDbDataParameter>, List <IDbDataParameter>, string, string, bool> procedureProcessor, Func <IDbCommand, List <IDbDataParameter>, List <IDbDataParameter>, string, string, bool> functionProcessor, bool continueOnProcessorException, string dbName)
{
VerifyArgument.IsNotNull("procedureProcessor", procedureProcessor);
VerifyArgument.IsNotNull("functionProcessor", functionProcessor);
VerifyConnection();
var proceduresDataTable = GetSchema(_connection);
foreach (DataRow row in proceduresDataTable.Rows)
{
var fullProcedureName = row["Name"].ToString();
if (row["Db"].ToString() == dbName)
{
using (
IDbCommand command = _factory.CreateCommand(_connection, CommandType.StoredProcedure,
fullProcedureName))
{
TryProcessProcedure(procedureProcessor, continueOnProcessorException, dbName, fullProcedureName, command);
}
}
}
}
// Use for sql updates
public int Update(string sql, CommandType commandType)
{
IDbConnection connection = factory.CreateConnection();
try
{
connection.ConnectionString = connectionString;
IDbCommand command = factory.CreateCommand();
command.Connection = connection;
command.CommandType = commandType;
command.CommandText = sql;
connection.Open();
int rowsAffected = command.ExecuteNonQuery();
return(rowsAffected);
}
catch (Exception ex)
{
log.Fatal(ex);
return(0);
}
finally
{
connection.Close();
connection.Dispose();
}
}
