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(); } }