protected override RowUpdatedEventArgs CreateRowUpdatedEvent(DataRow dataRow, IDbCommand command,
StatementType statementType,
DataTableMapping tableMapping)
{
NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "CreateRowUpdatedEvent");
return new NpgsqlRowUpdatedEventArgs(dataRow, command, statementType, tableMapping);
}
private void _PrepPagination(IDbCommand _cmd, int iOffset, int pageSize, int pageDirection)
{
_cmd.Parameters.Add(base.AddParameter("@offset", iOffset));
_cmd.Parameters.Add(base.AddParameter("@maximumRows", pageSize));
_cmd.Parameters.Add(base.AddParameter("@forw", pageDirection));
_cmd.Parameters.Add(base.AddParameterOutputInt32("@totRows"));
}
public void Enlist(IDbCommand command)
{
if (_transaction != null)
{
_transaction.Enlist(command);
}
}
public bool Delete(long id)
{
try {
long tstart = DateTime.Now.Ticks;
DbUtil.OpenConnection();
if (deleteByIdCmd == null) {
deleteByIdCmd = DbUtil.CreateCommand(SQL_DELETE_BY_ID, DbUtil.CurrentConnection);
deleteByIdCmd.Parameters.Add(DbUtil.CreateParameter("@nextActionID", DbType.Int64));
}
((IDataParameter)deleteByIdCmd.Parameters["@nextActionID"]).Value = id;
bool ok = deleteByIdCmd.ExecuteNonQuery() == 1;
long tend = DateTime.Now.Ticks;
log.Info("Delete Next Action: " + id + " " + ok + " " + ((tend - tstart) / 10000) + "ms");
return ok;
} catch (Exception e) {
log.Error(e.Message);
throw e;
} finally {
DbUtil.CloseConnection();
}
}
public static void BuildCommand(IDbCommand cmd, string sql, params object[] parameters)
{
if (cmd == null) throw new ArgumentNullException("cmd");
if (String.IsNullOrEmpty(sql)) throw new ArgumentNullException("sql");
if (parameters == null)
{
parameters = new object[0];
}
cmd.CommandText = new Regex(" *[\r\n]+ *").Replace(sql, " ");
var parameterNames = FindUniqueParameters(sql);
if (parameterNames.Count != parameters.Length)
{
throw new MicrOrmException(String.Format("Parameter count mismatch. {0} in the SQL string, {1} supplied", parameterNames.Count, parameters.Length));
}
for (var i = 0; i < parameters.Length; i++)
{
if (!parameterNames.ContainsKey(i))
{
throw new MicrOrmException(String.Format("Parameter ordinal mismatch. Parameter with ordinal {0} is missing in the SQL string", i));
}
var dbParameter = cmd.CreateParameter();
dbParameter.ParameterName = parameterNames[i];
dbParameter.Value = parameters[i];
cmd.Parameters.Add(dbParameter);
}
}
public IList<NextActionData> FindAll()
{
try {
long tstart = DateTime.Now.Ticks;
DbUtil.OpenConnection();
if (findAllCmd == null) {
findAllCmd = DbUtil.CreateCommand(SQL_FIND_ALL, DbUtil.CurrentConnection);
}
using (IDataReader rdr = findAllCmd.ExecuteReader()) {
IList<NextActionData> nextActionList = new List<NextActionData>();
while (rdr.Read()) {
nextActionList.Add(fillNextAction(rdr));
}
long tend = DateTime.Now.Ticks;
log.Info("Get all next actions: " + nextActionList.Count + " " + ((tend - tstart) / 10000) + "ms");
return nextActionList;
}
} catch (Exception e) {
log.Error(e.Message);
throw e;
} finally {
DbUtil.CloseConnection();
}
}
private static void DisposeCommandAndReader(IDbConnection connection, IDbCommand command, IDataReader reader)
{
using (connection)
using (command)
using (reader)
{ /* NoOp */ }
}
public static void AddParameter(IDbCommand dbCommand, string nombre, object value)
{
IDbDataParameter dbDataParameter=dbCommand.CreateParameter ();
dbDataParameter.ParameterName = nombre;
dbDataParameter.Value = value;
dbCommand.Parameters.Add (dbDataParameter);
}
private List<TripFormResponse> GetTripFormResponses(IDbCommand command)
{
var connection = _dbConnection;
try
{
connection.Open();
command.Connection = connection;
var reader = command.ExecuteReader();
var responses = new List<TripFormResponse>();
while (reader.Read())
{
var response = new TripFormResponse();
response.ContactId = reader.GetInt32(reader.GetOrdinal("Contact_ID"));
var donorId = SafeInt32(reader, "Donor_ID");
response.DonorId = donorId;
response.FundraisingGoal = SafeDecimal(reader,"Fundraising_Goal");
response.ParticipantId = reader.GetInt32(reader.GetOrdinal("Participant_ID"));
response.PledgeCampaignId = SafeInt32(reader, "Pledge_Campaign_ID");
response.EventId = SafeInt32(reader, "Event_ID");
response.DestinationId = reader.GetInt32(reader.GetOrdinal("Destination_ID"));
responses.Add(response);
}
return responses;
}
finally
{
connection.Close();
}
}
public bool fetchWordList(ref WorldList wl)
{
// Doesnt exist by default
wl.setExistance(false);
string sqlQuery = "select * from users where username='******' and passwordmd5='"+wl.getPassword()+"';";
queryExecuter= conn.CreateCommand();
queryExecuter.CommandText = sqlQuery;
dr= queryExecuter.ExecuteReader();
while(dr.Read()){
// Player is on the DB
wl.setExistance(true);
wl.setUserID((int) dr.GetDecimal(0));
dr.GetBytes(6,0,wl.getPrivateExponent(),0,96);
dr.GetBytes(5,0,wl.getPublicModulus(),0,96);
wl.setTimeCreated((int)dr.GetDecimal(7));
}
dr.Close();
// If doesnt exist... should not do more things
if (!wl.getExistance()){
Output.writeToLogForConsole("[WORLD DB ACCESS] fetchWordList : Player not found on DB with #" + wl.getUsername() + "# and #" + wl.getPassword() + "#");
conn.Close();
return false;
}
return true;
}
public override void AttachParameter(IDbCommand command, IDbDataParameter parameter)
{
if (parameter.Value is string && parameter.DbType == DbType.Guid)
parameter.DbType = DbType.AnsiString;
base.AttachParameter(command, parameter);
}
private string PrepareInsertCommand(string sql, IDbCommand command, IEnumerable<Column> columns)
{
var parameterFactory = _adapter.ProviderHelper.GetCustomProvider<IDbParameterFactory>(_schemaProvider)
?? new GenericDbParameterFactory(command);
var columnLookup = columns.ToDictionary(c => c.QuotedName, c => c);
if (columnLookup.Count == 0) return PrepareCommand(sql, command);
int openParenIndex = sql.IndexOf('(');
int closeParenLength = sql.IndexOf(')') - openParenIndex;
var columnNameList = sql.Substring(openParenIndex, closeParenLength).Trim('(', ')').Split(',');
int index = 0;
var sqlBuilder = new StringBuilder();
foreach (var c in sql)
{
if (c == '?')
{
var column = columnLookup[columnNameList[index]];
var parameter = parameterFactory.CreateParameter(_schemaProvider.NameParameter("p" + index), column);
command.Parameters.Add(parameter);
sqlBuilder.Append(parameter.ParameterName);
index++;
}
else
{
sqlBuilder.Append(c);
}
}
return sqlBuilder.ToString();
}
/// <summary>
/// Determines if a parameter is an XML type parameter.
/// </summary>
/// <param name="command">The related command object.</param>
/// <param name="parameter">The parameter to test.</param>
/// <returns>True if the parameter is an XML parameter.</returns>
public override bool IsXmlParameter(IDbCommand command, IDataParameter parameter)
{
if (parameter == null) throw new ArgumentNullException("parameter");
var op = (OracleParameter)parameter;
return op.OracleDbType == OracleDbType.XmlType;
}
/// <summary>
/// Enlist the <see cref="IDbCommand"/> in the current <see cref="ITransaction"/>.
/// </summary>
/// <param name="command">The <see cref="IDbCommand"/> to enlist in this Transaction.</param>
/// <remarks>
/// <para>
/// This takes care of making sure the <see cref="IDbCommand"/>'s Transaction property
/// contains the correct <see cref="IDbTransaction"/> or <see langword="null" /> if there is no
/// Transaction for the ISession - ie <c>BeginTransaction()</c> not called.
/// </para>
/// <para>
/// This method may be called even when the transaction is disposed.
/// </para>
/// </remarks>
public void Enlist(IDbCommand command)
{
if (trans == null)
{
if (log.IsWarnEnabled)
{
if (command.Transaction != null)
{
log.Warn("set a nonnull IDbCommand.Transaction to null because the Session had no Transaction");
}
}
command.Transaction = null;
return;
}
else
{
if (log.IsWarnEnabled)
{
// got into here because the command was being initialized and had a null Transaction - probably
// don't need to be confused by that - just a normal part of initialization...
if (command.Transaction != null && command.Transaction != trans)
{
log.Warn("The IDbCommand had a different Transaction than the Session. This can occur when " +
"Disconnecting and Reconnecting Sessions because the PreparedCommand Cache is Session specific.");
}
}
log.Debug("Enlist Command");
command.Transaction = trans;
}
}
public void DeriveParameters(IDbCommand cmd)
{
if ((cmd as SqlCommand) != null)
{
SqlCommandBuilder.DeriveParameters(cmd as SqlCommand);
}
}
/// <summary>
/// Formats the SQL in a SQL-Server friendly way, with DECLARE statements for the parameters up top.
/// </summary>
public string FormatSql(string commandText, List<SqlTimingParameter> parameters, IDbCommand command = null)
{
StringBuilder buffer = new StringBuilder();
if (command != null && IncludeMetaData)
{
buffer.AppendLine("-- Command Type: " + command.CommandType);
buffer.AppendLine("-- Database: " + command.Connection.Database);
if (command.Transaction != null)
{
buffer.AppendLine("-- Command Transaction Iso Level: " + command.Transaction.IsolationLevel);
}
if (Transaction.Current != null)
{
// transactions issued by TransactionScope are not bound to the database command but exists globally
buffer.AppendLine("-- Transaction Scope Iso Level: " + Transaction.Current.IsolationLevel);
}
buffer.AppendLine();
}
string baseOutput = base.FormatSql(commandText, parameters, command);
buffer.Append(baseOutput);
return buffer.ToString();
}
/// <summary>
/// Unwraps the given command and returns the inner command.
/// </summary>
/// <param name="command">The outer command.</param>
/// <returns>The inner command.</returns>
public override IDbCommand GetInnerCommand(IDbCommand command)
{
if (command == null) throw new ArgumentNullException("command");
GlimpseDbCommand profiledCommand = (GlimpseDbCommand)command;
return profiledCommand.InnerCommand;
}
private DbCommandDecorator(IDbCommand command)
{
if (command == null)
throw new ArgumentNullException("command");
_command = command;
}
protected override void OnExecutingCommand(IDbCommand cmd)
{
if (log.IsDebugEnabled)
{
log.Debug(FormatCommand(cmd));
}
}
public static DbDataAdapter CreateDataAdapter(DbProviderType DbProviderType, IDbCommand command, IDbConnection connection)
{
switch (DbProviderType)
{
case DbProviderType.SQLSERVER:
SqlDataAdapter sqlda = new SqlDataAdapter();
sqlda.SelectCommand = (SqlCommand) command;
command.Connection = connection;
return sqlda;
case DbProviderType.ORACLE:
OracleDataAdapter orada = new OracleDataAdapter();
orada.SelectCommand = (OracleCommand) command;
command.Connection = connection;
return orada;
// case DbProviderType.MYSQL:
// MySqlDataAdapter mysqlda = new MySqlDataAdapter();
// mysqlda.SelectCommand = (MySqlCommand) command;
// command.Connection = connection;
// return mysqlda;
default:
OleDbDataAdapter oleda = new OleDbDataAdapter();
oleda.SelectCommand = (OleDbCommand) command;
command.Connection = connection;
return oleda;
}
}
/// <summary>
/// Tracks when 'command' is started.
/// </summary>
/// <param name="command">The command.</param>
/// <param name="type">The type.</param>
public void ExecuteStartImpl(IDbCommand command, ExecuteType type)
{
var id = Tuple35.Create((object)command, type);
var sqlTiming = new SqlTiming(command, type, Profiler);
_inProgress[id] = sqlTiming;
}
public void Script(IDbCommand command)
{
foreach (ISqlScripter scripter in _scripters)
{
scripter.Script(command);
}
}
public bool Execute(string qry)
{
cn = new OleDbConnection(cnString.GetConnString());
cmd = new OleDbCommand(qry, (OleDbConnection)cn);
try
{
cn.Open();
IDbTransaction tran = cn.BeginTransaction();
cmd.Transaction = tran;
int affectedRows = cmd.ExecuteNonQuery();
Console.WriteLine(affectedRows);
if (affectedRows > 0)
{
tran.Commit();
return true;
}
else
{
tran.Rollback();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
cn.Close();
}
return false;
}
void SqlMapper.ICustomQueryParameter.AddParameter(IDbCommand command, string name)
{
var param = command.CreateParameter();
param.ParameterName = name;
Set(param, table, typeName);
command.Parameters.Add(param);
}
public override System.Data.IDataAdapter GetDataAdapter(IDbCommand cmd)
{
SqlCommand sqlCmd = (SqlCommand)cmd;
SqlDataAdapter sqlAdpt = new SqlDataAdapter(sqlCmd);
return sqlAdpt;
}
public override void Set(IDbCommand cmd, object value, int index)
{
if (value is EnumStoredAsString && (EnumStoredAsString)value == EnumStoredAsString.Unspecified)
base.Set(cmd, null, index);
else
base.Set(cmd, value, index);
}
public override void AttachParameter(IDbCommand command, IDbDataParameter parameter)
{
if (parameter.DbType == DbType.DateTime2)
parameter.DbType = DbType.DateTime;
base.AttachParameter(command, parameter);
}
/// <summary>
/// Execute the command and take the first cell of each row to build an Enumerable of string
/// </summary>
/// <param name="cmd">A sql or mdx query to execute</param>
/// <returns>The first cell of row returned by the query</returns>
public virtual IEnumerable<string> Build(IDbCommand cmd)
{
var qe = new QueryEngineFactory().GetExecutor(cmd);
var ds = qe.Execute();
var list = Load(ds);
return list;
}
public override void Initialize(IDatabaseEngine engine, IDbCommand command)
{
string innerParameterName = engine.GetParameterName(ParameterName);
sqlSnippet = sqlSnippet.Replace(REPLACEMENT_VALUE, innerParameterName);
_innerParameter = command.CreateParameter();
_innerParameter.ParameterName = innerParameterName;
}
/// <summary>
/// Populates the specified IDbCommand object's Parameters collection with
/// parameter information for the stored procedure specified in the IDbCommand.
/// </summary>
/// <remarks>
/// See the <see cref="DbManager.AddDataProvider(DataProviderBase)"/> method to find an example.
/// </remarks>
/// <seealso cref="DbManager.AddDataProvider(DataProviderBase)">AddDataManager Method</seealso>
/// <param name="command">The IDbCommand referencing the stored procedure for which the parameter information is to be derived. The derived parameters will be populated into the Parameters of this command.</param>
public override bool DeriveParameters(IDbCommand command)
{
// SQLiteCommandBuilder does not implement DeriveParameters.
// This is not surprising, since SQLite has no support for stored procs.
//
return false;
}
public SegmentImpl(WSBase owner)
{
fStmt = owner.GetDataBase().GetConnection().CreateCommand();
fStmt.CommandType = System.Data.CommandType.Text;
}
static bool Execute(IDbCommand cmd)
{
cmd.ExecuteNonQuery();
return true;
}
public void Test3()
{
try
{
ConsoleWriteLine("Test3 (Date Paramaters) Start.");
ConsoleWriteLine("Create connection...");
SqliteConnection con = new SqliteConnection();
string dbFilename = Path.Combine(ApplicationData.Current.LocalFolder.Path, @"SqliteTest3.db");
string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
ConsoleWriteLine(String.Format("Set connection String: {0}", cs));
if (FileExists(dbFilename))
{
FileDelete(dbFilename);
}
con.ConnectionString = cs;
ConsoleWriteLine("Open database...");
con.Open();
ConsoleWriteLine("create command...");
IDbCommand cmd = con.CreateCommand();
ConsoleWriteLine("create table TEST_TABLE...");
cmd.CommandText = "CREATE TABLE TBL ( ID NUMBER, DATE_TEXT REAL)";
cmd.ExecuteNonQuery();
ConsoleWriteLine("insert ...");
cmd.CommandText = "INSERT INTO TBL ( ID, DATE_TEXT) VALUES ( 1, @DATETEXT)";
cmd.Parameters.Add(
new SqliteParameter
{
ParameterName = "@DATETEXT",
Value = DateTime.Now
}
);
cmd.ExecuteNonQuery();
ConsoleWriteLine("SELECT data from TBL...");
cmd.CommandText = "SELECT * FROM tbl";
IDataReader reader = cmd.ExecuteReader();
int r = 0;
ConsoleWriteLine("Read the data...");
while (reader.Read())
{
ConsoleWriteLine(String.Format(" Row: {0}", r));
int i = reader.GetInt32(reader.GetOrdinal("ID"));
ConsoleWriteLine(String.Format(" ID: {0}", i));
string s = reader.GetString(reader.GetOrdinal("DATE_TEXT"));
ConsoleWriteLine(String.Format(" DATE_TEXT: {0}", s));
r++;
}
ConsoleWriteLine(String.Format("Rows retrieved: {0}", r));
ConsoleWriteLine("Close and cleanup...");
con.Close();
con = null;
ConsoleWriteLine("Test3 Done.");
}
catch (Exception e)
{
ConsoleWriteError("ERROR: " + e.Message);
ConsoleWriteError(e.StackTrace);
}
}
public void Test2()
{
try
{
ConsoleWriteLine("Test2 Start.");
ConsoleWriteLine("Create connection...");
SqliteConnection con = new SqliteConnection();
string dbFilename = Path.Combine(ApplicationData.Current.LocalFolder.Path, @"SqliteTest3.db");
string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
ConsoleWriteLine(String.Format("Set connection String: {0}", cs));
if (FileExists(dbFilename))
{
FileDelete(dbFilename);
}
con.ConnectionString = cs;
ConsoleWriteLine("Open database...");
con.Open();
ConsoleWriteLine("create command...");
IDbCommand cmd = con.CreateCommand();
ConsoleWriteLine("create table TEST_TABLE...");
cmd.CommandText = "CREATE TABLE TBL ( ID NUMBER, NAME TEXT)";
cmd.ExecuteNonQuery();
ConsoleWriteLine("insert row 1...");
cmd.CommandText = "INSERT INTO TBL ( ID, NAME ) VALUES (1, 'ONE' )";
cmd.ExecuteNonQuery();
ConsoleWriteLine("insert row 2...");
cmd.CommandText = "INSERT INTO TBL ( ID, NAME ) VALUES (2, 'ä¸æ–‡' )";
cmd.ExecuteNonQuery();
//Console.WriteLine("commit...");
//cmd.CommandText = "COMMIT";
//cmd.ExecuteNonQuery();
ConsoleWriteLine("SELECT data from TBL...");
cmd.CommandText = "SELECT id,NAME FROM tbl WHERE name = 'ä¸æ–‡'";
IDataReader reader = cmd.ExecuteReader();
int r = 0;
ConsoleWriteLine("Read the data...");
while (reader.Read())
{
ConsoleWriteLine(String.Format(" Row: {0}", r));
int i = reader.GetInt32(reader.GetOrdinal("ID"));
ConsoleWriteLine(String.Format(" ID: {0}", i));
string s = reader.GetString(reader.GetOrdinal("NAME"));
ConsoleWriteLine(String.Format(" NAME: {0} = {1}", s, s == "ä¸æ–‡"));
r++;
}
ConsoleWriteLine(String.Format("Rows retrieved: {0}", r));
ConsoleWriteLine("Close and cleanup...");
con.Close();
con = null;
ConsoleWriteLine("Test2 Done.");
}
catch (Exception e)
{
ConsoleWriteError("ERROR: " + e.Message);
ConsoleWriteError(e.StackTrace);
}
}
public override DbDataAdapter CreateDataAdapter(IDbCommand selectCommand,
IDbCommand insertCommand, IDbCommand deleteCommand, IDbCommand updateCommand)
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
if (selectCommand != null)
{
adapter.SelectCommand = selectCommand as OleDbCommand;
}
if (insertCommand != null)
{
adapter.InsertCommand = insertCommand as OleDbCommand;
}
if (deleteCommand != null)
{
adapter.DeleteCommand = deleteCommand as OleDbCommand;
}
if (updateCommand != null)
{
adapter.UpdateCommand = updateCommand as OleDbCommand;
}
return(adapter);
}
public void TestSimpleCommand()
{
using (IDbConnection conn = new SnowflakeDbConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "select 1";
// command type can only be text, stored procedure are not supported.
Assert.AreEqual(CommandType.Text, cmd.CommandType);
try
{
cmd.CommandType = CommandType.StoredProcedure;
Assert.Fail();
}
catch (SnowflakeDbException e)
{
Assert.AreEqual(270009, e.ErrorCode);
}
Assert.AreEqual(UpdateRowSource.None, cmd.UpdatedRowSource);
try
{
cmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
Assert.Fail();
}
catch (SnowflakeDbException e)
{
Assert.AreEqual(270009, e.ErrorCode);
}
Assert.AreSame(conn, cmd.Connection);
try
{
cmd.Connection = null;
Assert.Fail();
}
catch (SnowflakeDbException e)
{
Assert.AreEqual(270009, e.ErrorCode);
}
Assert.IsFalse(((SnowflakeDbCommand)cmd).DesignTimeVisible);
try
{
((SnowflakeDbCommand)cmd).DesignTimeVisible = true;
Assert.Fail();
}
catch (SnowflakeDbException e)
{
Assert.AreEqual(270009, e.ErrorCode);
}
object val = cmd.ExecuteScalar();
Assert.AreEqual(1L, (long)val);
conn.Close();
}
}
/// <summary>
/// This cleans up the parameter syntax for an SQL Server call. This was split out from PrepareCommand so that it could be called independently.
/// </summary>
/// <param name="command">An IDbCommand object containing the CommandText to clean.</param>
public override void CleanParameterSyntax(IDbCommand command)
{
// do nothing for SQL
}
protected override RowUpdatingEventArgs CreateRowUpdatingEvent(DataRow dataRow, IDbCommand command, StatementType statementType, DataTableMapping tableMapping)
{
return(new MySqlRowUpdatingEventArgs(dataRow, command, statementType, tableMapping));
}
public abstract object ExecuteScalar(IDbCommand command);
/// <summary>
/// Review .NET Framework documentation.
/// </summary>
protected override int Update(DataRow[] dataRows, DataTableMapping tableMapping)
{
int updated = 0;
IDbCommand command = null;
StatementType statementType = StatementType.Insert;
ICollection <IDbConnection> connections = new List <IDbConnection>();
RowUpdatingEventArgs updatingArgs = null;
Exception updateException = null;
foreach (DataRow row in dataRows)
{
updateException = null;
if (row.RowState == DataRowState.Detached ||
row.RowState == DataRowState.Unchanged)
{
continue;
}
switch (row.RowState)
{
case DataRowState.Unchanged:
case DataRowState.Detached:
continue;
case DataRowState.Added:
command = this.InsertCommand;
statementType = StatementType.Insert;
break;
case DataRowState.Modified:
command = this.UpdateCommand;
statementType = StatementType.Update;
break;
case DataRowState.Deleted:
command = this.DeleteCommand;
statementType = StatementType.Delete;
break;
}
/* The order of execution can be reviewed in the .NET 1.1 documentation
*
* 1. The values in the DataRow are moved to the parameter values.
* 2. The OnRowUpdating event is raised.
* 3. The command executes.
* 4. If the command is set to FirstReturnedRecord, then the first returned result is placed in the DataRow.
* 5. If there are output parameters, they are placed in the DataRow.
* 6. The OnRowUpdated event is raised.
* 7 AcceptChanges is called.
*/
try
{
updatingArgs = this.CreateRowUpdatingEvent(row, command, statementType, tableMapping);
/* 1. Update Parameter values (It's very similar to what we
* are doing in the FbCommandBuilder class).
*
* Only input parameters should be updated.
*/
if (command != null && command.Parameters.Count > 0)
{
try
{
this.UpdateParameterValues(command, statementType, row, tableMapping);
}
catch (Exception ex)
{
updatingArgs.Errors = ex;
updatingArgs.Status = UpdateStatus.ErrorsOccurred;
}
}
// 2. Raise RowUpdating event
this.OnRowUpdating(updatingArgs);
if (updatingArgs.Status == UpdateStatus.SkipAllRemainingRows)
{
break;
}
else if (updatingArgs.Status == UpdateStatus.ErrorsOccurred)
{
if (updatingArgs.Errors == null)
{
throw new InvalidOperationException("RowUpdatingEvent: Errors occurred; no additional information is available.");
}
throw updatingArgs.Errors;
}
else if (updatingArgs.Status == UpdateStatus.SkipCurrentRow)
{
updated++;
continue;
}
else if (updatingArgs.Status == UpdateStatus.Continue)
{
if (command != updatingArgs.Command)
{
command = updatingArgs.Command;
}
if (command == null)
{
/* Samples of exceptions thrown by DbDataAdapter class
*
* Update requires a valid InsertCommand when passed DataRow collection with new rows
* Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
* Update requires a valid DeleteCommand when passed DataRow collection with deleted rows.
*/
throw new InvalidOperationException(this.CreateExceptionMessage(statementType));
}
// 3. Execute the command
if (command.Connection.State == ConnectionState.Closed)
{
command.Connection.Open();
// Track command connection
connections.Add(command.Connection);
}
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected == 0)
{
throw new DBConcurrencyException(new DBConcurrencyException().Message, null, new DataRow[] { row });
}
updated++;
// http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=933212&SiteID=1
if (statementType == StatementType.Insert)
{
row.AcceptChanges();
}
/* 4. If the command is set to FirstReturnedRecord, then the
* first returned result is placed in the DataRow.
*
* We have nothing to do in this case as there are no
* support for batch commands.
*/
/* 5. Check if we have output parameters and they should
* be updated.
*
* Only output parameters should be updated
*/
if (command.UpdatedRowSource == UpdateRowSource.OutputParameters ||
command.UpdatedRowSource == UpdateRowSource.Both)
{
// Process output parameters
foreach (IDataParameter parameter in command.Parameters)
{
if ((parameter.Direction == ParameterDirection.Output ||
parameter.Direction == ParameterDirection.ReturnValue ||
parameter.Direction == ParameterDirection.InputOutput) &&
!String.IsNullOrEmpty(parameter.SourceColumn))
{
DataColumn column = null;
DataColumnMapping columnMapping = tableMapping.GetColumnMappingBySchemaAction(
parameter.SourceColumn,
this.MissingMappingAction);
if (columnMapping != null)
{
column = columnMapping.GetDataColumnBySchemaAction(
row.Table,
null,
this.MissingSchemaAction);
if (column != null)
{
row[column] = parameter.Value;
}
}
}
}
}
}
}
catch (Exception ex)
{
row.RowError = ex.Message;
updateException = ex;
}
if (updatingArgs != null && updatingArgs.Status == UpdateStatus.Continue)
{
// 6. Raise RowUpdated event
RowUpdatedEventArgs updatedArgs = this.CreateRowUpdatedEvent(row, command, statementType, tableMapping);
this.OnRowUpdated(updatedArgs);
if (updatedArgs.Status == UpdateStatus.SkipAllRemainingRows)
{
break;
}
else if (updatedArgs.Status == UpdateStatus.ErrorsOccurred)
{
if (updatingArgs.Errors == null)
{
throw new InvalidOperationException("RowUpdatedEvent: Errors occurred; no additional information available.");
}
throw updatedArgs.Errors;
}
else if (updatedArgs.Status == UpdateStatus.SkipCurrentRow)
{
}
else if (updatingArgs.Status == UpdateStatus.Continue)
{
// If the update result is an exception throw it
if (!this.ContinueUpdateOnError && updateException != null)
{
this.CloseConnections(connections);
throw updateException;
}
// 7. Call AcceptChanges
if (this.AcceptChangesDuringUpdate && !row.HasErrors)
{
row.AcceptChanges();
}
}
}
else
{
// If the update result is an exception throw it
if (!this.ContinueUpdateOnError && updateException != null)
{
this.CloseConnections(connections);
throw updateException;
}
}
}
this.CloseConnections(connections);
return(updated);
}
public abstract object GetOutputParameterValue(IDbCommand command, string paramName);
public abstract int ExecuteNonQuery(IDbCommand command);
public abstract void AddParameter(IDbCommand command, string paramName, DbType type,
ParameterDirection direction);
public abstract void AddParameterArray(IDbCommand command, ParameterDirection direction, string paramName,
DbType type, int size);
public abstract void AddParameterWithValue(IDbCommand command, string paramName, DbType type,
ParameterDirection direction, object value);
public abstract void AddParameter(IDbCommand command, string paramName, DbType type, int size);
public abstract DataSet ExecuteDataSet(IDbCommand command);
public abstract void AddParameterWithValue(IDbCommand command, string paramName, object value);
public void Issue_119()
{
try
{
ConsoleWriteLine("Issue 119 Start.");
ConsoleWriteLine("Create connection...");
SqliteConnection con = new SqliteConnection();
string dbFilename = Path.Combine(ApplicationData.Current.LocalFolder.Path, @"SqliteTest3.db");
string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
ConsoleWriteLine(String.Format("Set connection String: {0}", cs));
if (FileExists(dbFilename))
{
FileDelete(dbFilename);
}
con.ConnectionString = cs;
ConsoleWriteLine("Open database...");
con.Open();
ConsoleWriteLine("create command...");
IDbCommand cmd = con.CreateCommand();
ConsoleWriteLine("create table TEST_TABLE...");
cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT, COLC DATETIME )";
cmd.ExecuteNonQuery();
ConsoleWriteLine("insert row 1...");
cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (123,'ABC','2008-12-31 18:19:20' )";
cmd.ExecuteNonQuery();
ConsoleWriteLine("insert row 2...");
cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (124,'DEF', '2009-11-16 13:35:36' )";
cmd.ExecuteNonQuery();
ConsoleWriteLine("SELECT data from TEST_TABLE...");
cmd.CommandText = "SELECT RowID, COLA, COLB, COLC FROM TEST_TABLE";
IDataReader reader = cmd.ExecuteReader();
int r = 0;
ConsoleWriteLine("Read the data...");
while (reader.Read())
{
ConsoleWriteLine(String.Format(" Row: {0}", r));
int rowid = reader.GetInt32(reader.GetOrdinal("RowID"));
ConsoleWriteLine(String.Format(" RowID: {0}", rowid));
int i = reader.GetInt32(reader.GetOrdinal("COLA"));
ConsoleWriteLine(String.Format(" COLA: {0}", i));
string s = reader.GetString(reader.GetOrdinal("COLB"));
ConsoleWriteLine(String.Format(" COLB: {0}", s));
DateTime dt = reader.GetDateTime(reader.GetOrdinal("COLC"));
ConsoleWriteLine(String.Format(" COLB: {0}", dt.ToString("MM/dd/yyyy HH:mm:ss")));
r++;
}
ConsoleWriteLine("Close and cleanup...");
con.Close();
con = null;
ConsoleWriteLine("Issue 119 Done.");
}
catch (Exception e)
{
ConsoleWriteError("ERROR: " + e.Message);
ConsoleWriteError(e.StackTrace);
}
}
public abstract IDataReader ExecuteDataReader(IDbCommand command);
public static IDataParameter Add(this IDataParameterCollection paramCollection, IDbCommand cmd, string name)
{
var param = cmd.CreateParameter();
param.ParameterName = name;
paramCollection.Add(param);
return(param);
}
/// <summary>
/// Derives the parameter list from a stored procedure command.
/// </summary>
/// <param name="command">The command to derive.</param>
public override void DeriveParametersFromStoredProcedure(IDbCommand command)
{
OracleCommandBuilder.DeriveParameters(command as OracleCommand);
}
/// <summary>業務処理を実装</summary>
/// <param name="parameterValue">引数クラス</param>
/// <param name="returnValue">戻り値クラス</param>
protected override void UOC_DoAction(BaseParameterValue parameterValue, ref BaseReturnValue returnValue)
{
// 戻り値を生成しておく。
returnValue = new MyReturnValue();
// 自動トランザクションで開始したトランザクションを閉じる。
this.GetDam().CommitTransaction();
// コネクションを閉じる。
this.GetDam().ConnectionClose();
// データアクセス制御クラスをクリア。
this.SetDam(null);
// Dam用ワーク
BaseDam damWork;
// 共通Dao
CmnDao cmnDao;
// カバレージ上げ用
IDbConnection idcnn = null;
IDbTransaction idtx = null;
IDbCommand idcmd = null;
IDataAdapter idapt = null;
DataSet ds = null;
// SQLの戻り値を受ける
object obj;
#region SQL Server
damWork = new DamSqlSvr();
#region 接続しない
BaseLogic.InitDam("XXXX", damWork);
this.SetDam(damWork);
// なにもしない。
// プロパティにアクセス（デバッガで確認）
idcnn = ((DamSqlSvr)this.GetDam()).DamSqlConnection;
idtx = ((DamSqlSvr)this.GetDam()).DamSqlTransaction;
// nullの時に呼んだ場合。
this.GetDam().CommitTransaction();
this.GetDam().ConnectionClose();
#endregion
#region SQL_NT
BaseLogic.InitDam("SQL_NT", damWork);
this.SetDam(damWork);
// 行数
// Damを直接使用することもできるが、
// 通常は、データアクセスにはDaoを使用する。
cmnDao = new CmnDao(this.GetDam());
cmnDao.SQLText = "SELECT COUNT(*) FROM SHIPPERS";
obj = (object)cmnDao.ExecSelectScalar();
//this.GetDam().CommitTransaction();
this.GetDam().ConnectionClose();
#endregion
#region SQL_UC
BaseLogic.InitDam("SQL_UC", damWork);
this.SetDam(damWork);
// 行数
// Damを直接使用することもできるが、
// 通常は、データアクセスにはDaoを使用する。
cmnDao = new CmnDao(this.GetDam());
cmnDao.SQLText = "SELECT COUNT(*) FROM SHIPPERS";
obj = (object)cmnDao.ExecSelectScalar();
this.GetDam().CommitTransaction();
this.GetDam().ConnectionClose();
#endregion
#region SQL_RC
BaseLogic.InitDam("SQL_RC", damWork);
this.SetDam(damWork);
// 行数
// Damを直接使用することもできるが、
// 通常は、データアクセスにはDaoを使用する。
cmnDao = new CmnDao(this.GetDam());
cmnDao.SQLText = "SELECT COUNT(*) FROM SHIPPERS";
obj = (object)cmnDao.ExecSelectScalar();
// プロパティにアクセス（デバッガで確認）
idcnn = ((DamSqlSvr)this.GetDam()).DamSqlConnection;
idtx = ((DamSqlSvr)this.GetDam()).DamSqlTransaction;
idcmd = ((DamSqlSvr)this.GetDam()).DamSqlCommand;
idapt = ((DamSqlSvr)this.GetDam()).DamSqlDataAdapter;
ds = new DataSet();
idapt.Fill(ds);
this.GetDam().CommitTransaction();
this.GetDam().ConnectionClose();
// ２連続で呼んだ場合。
this.GetDam().CommitTransaction();
this.GetDam().ConnectionClose();
#endregion
#region SQL_RR
BaseLogic.InitDam("SQL_RR", damWork);
this.SetDam(damWork);
// 行数
// Damを直接使用することもできるが、
// 通常は、データアクセスにはDaoを使用する。
cmnDao = new CmnDao(this.GetDam());
cmnDao.SQLText = "SELECT COUNT(*) FROM SHIPPERS";
obj = (object)cmnDao.ExecSelectScalar();
this.GetDam().CommitTransaction();
this.GetDam().ConnectionClose();
#endregion
#region SQL_SZ
BaseLogic.InitDam("SQL_SZ", damWork);
this.SetDam(damWork);
// 行数
// Damを直接使用することもできるが、
// 通常は、データアクセスにはDaoを使用する。
cmnDao = new CmnDao(this.GetDam());
cmnDao.SQLText = "SELECT COUNT(*) FROM SHIPPERS";
obj = (object)cmnDao.ExecSelectScalar();
this.GetDam().CommitTransaction();
this.GetDam().ConnectionClose();
#endregion
#region SQL_SS
BaseLogic.InitDam("SQL_SS", damWork);
this.SetDam(damWork);
// 行数
// Damを直接使用することもできるが、
// 通常は、データアクセスにはDaoを使用する。
cmnDao = new CmnDao(this.GetDam());
cmnDao.SQLText = "SELECT COUNT(*) FROM SHIPPERS";
obj = (object)cmnDao.ExecSelectScalar();
this.GetDam().CommitTransaction();
this.GetDam().ConnectionClose();
#endregion
#region SQL_DF
BaseLogic.InitDam("SQL_DF", damWork);
this.SetDam(damWork);
// 行数
// Damを直接使用することもできるが、
// 通常は、データアクセスにはDaoを使用する。
cmnDao = new CmnDao(this.GetDam());
cmnDao.SQLText = "SELECT COUNT(*) FROM SHIPPERS";
obj = (object)cmnDao.ExecSelectScalar();
this.GetDam().CommitTransaction();
this.GetDam().ConnectionClose();
#endregion
#endregion
#region Oracle
damWork = new DamOraOdp();
#region 接続しない
BaseLogic.InitDam("XXXX", damWork);
this.SetDam(damWork);
// なにもしない。
// プロパティにアクセス（デバッガで確認）
idcnn = ((DamOraOdp)this.GetDam()).DamOracleConnection;
idtx = ((DamOraOdp)this.GetDam()).DamOracleTransaction;
// nullの時に呼んだ場合。
this.GetDam().CommitTransaction();
this.GetDam().ConnectionClose();
#endregion
#region ODP2_NT
BaseLogic.InitDam("ODP2_NT", damWork);
this.SetDam(damWork);
// 行数
// Damを直接使用することもできるが、
// 通常は、データアクセスにはDaoを使用する。
cmnDao = new CmnDao(this.GetDam());
cmnDao.SQLText = "SELECT COUNT(*) FROM SHIPPERS";
obj = (object)cmnDao.ExecSelectScalar();
//this.GetDam().CommitTransaction();
this.GetDam().ConnectionClose();
#endregion
#region ODP2_UC
// ★ サポートされない分離レベル
#endregion
#region ODP2_RC
BaseLogic.InitDam("ODP2_RC", damWork);
this.SetDam(damWork);
// 行数
// Damを直接使用することもできるが、
// 通常は、データアクセスにはDaoを使用する。
cmnDao = new CmnDao(this.GetDam());
cmnDao.SQLText = "SELECT COUNT(*) FROM SHIPPERS";
obj = (object)cmnDao.ExecSelectScalar();
// プロパティにアクセス（デバッガで確認）
idcnn = ((DamOraOdp)this.GetDam()).DamOracleConnection;
idtx = ((DamOraOdp)this.GetDam()).DamOracleTransaction;
idcmd = ((DamOraOdp)this.GetDam()).DamOracleCommand;
idapt = ((DamOraOdp)this.GetDam()).DamOracleDataAdapter;
ds = new DataSet();
idapt.Fill(ds);
this.GetDam().CommitTransaction();
this.GetDam().ConnectionClose();
// ２連続で呼んだ場合。
this.GetDam().CommitTransaction();
this.GetDam().ConnectionClose();
#endregion
#region ODP2_RR
// ★ サポートされない分離レベル
#endregion
#region ODP2_SZ
BaseLogic.InitDam("ODP2_SZ", damWork);
this.SetDam(damWork);
// 行数
// Damを直接使用することもできるが、
// 通常は、データアクセスにはDaoを使用する。
cmnDao = new CmnDao(this.GetDam());
cmnDao.SQLText = "SELECT COUNT(*) FROM SHIPPERS";
obj = (object)cmnDao.ExecSelectScalar();
this.GetDam().CommitTransaction();
this.GetDam().ConnectionClose();
#endregion
#region ODP2_SS
// ★ サポートされない分離レベル
#endregion
#region ODP2_DF
BaseLogic.InitDam("ODP2_DF", damWork);
this.SetDam(damWork);
// 行数
// Damを直接使用することもできるが、
// 通常は、データアクセスにはDaoを使用する。
cmnDao = new CmnDao(this.GetDam());
cmnDao.SQLText = "SELECT COUNT(*) FROM SHIPPERS";
obj = (object)cmnDao.ExecSelectScalar();
this.GetDam().CommitTransaction();
this.GetDam().ConnectionClose();
#endregion
#endregion
#region DB2
//damWork = new DamDB2();
#region 接続しない
//BaseLogic.InitDam("XXXX", damWork);
//this.SetDam(damWork);
//// なにもしない。
//// プロパティにアクセス（デバッガで確認）
//idcnn = ((DamDB2)this.GetDam()).DamDB2Connection;
//idtx = ((DamDB2)this.GetDam()).DamDB2Transaction;
//// nullの時に呼んだ場合。
//this.GetDam().CommitTransaction();
//this.GetDam().ConnectionClose();
#endregion
#region DB2_NT
//BaseLogic.InitDam("DB2_NT", damWork);
//this.SetDam(damWork);
//// 行数
//// Damを直接使用することもできるが、
//// 通常は、データアクセスにはDaoを使用する。
//cmnDao = new CmnDao(this.GetDam());
//cmnDao.SQLText = "SELECT COUNT(*) FROM SHIPPERS";
//obj = (object)cmnDao.ExecSelectScalar();
////this.GetDam().CommitTransaction();
//this.GetDam().ConnectionClose();
#endregion
#region DB2_UC
//BaseLogic.InitDam("DB2_UC", damWork);
//this.SetDam(damWork);
//// 行数
//// Damを直接使用することもできるが、
//// 通常は、データアクセスにはDaoを使用する。
//cmnDao = new CmnDao(this.GetDam());
//cmnDao.SQLText = "SELECT COUNT(*) FROM SHIPPERS";
//obj = (object)cmnDao.ExecSelectScalar();
//this.GetDam().CommitTransaction();
//this.GetDam().ConnectionClose();
#endregion
#region DB2_RC
//BaseLogic.InitDam("DB2_RC", damWork);
//this.SetDam(damWork);
//// 行数
//// Damを直接使用することもできるが、
//// 通常は、データアクセスにはDaoを使用する。
//cmnDao = new CmnDao(this.GetDam());
//cmnDao.SQLText = "SELECT COUNT(*) FROM SHIPPERS";
//obj = (object)cmnDao.ExecSelectScalar();
//// プロパティにアクセス（デバッガで確認）
//idcnn = ((DamDB2)this.GetDam()).DamDB2Connection;
//idtx = ((DamDB2)this.GetDam()).DamDB2Transaction;
//idcmd = ((DamDB2)this.GetDam()).DamDB2Command;
//idapt = ((DamDB2)this.GetDam()).DamDB2DataAdapter;
//ds = new DataSet();
//idapt.Fill(ds);
//this.GetDam().CommitTransaction();
//this.GetDam().ConnectionClose();
//// ２連続で呼んだ場合。
//this.GetDam().CommitTransaction();
//this.GetDam().ConnectionClose();
#endregion
#region DB2_RR
//BaseLogic.InitDam("DB2_RR", damWork);
//this.SetDam(damWork);
//// 行数
//// Damを直接使用することもできるが、
//// 通常は、データアクセスにはDaoを使用する。
//cmnDao = new CmnDao(this.GetDam());
//cmnDao.SQLText = "SELECT COUNT(*) FROM SHIPPERS";
//obj = (object)cmnDao.ExecSelectScalar();
//this.GetDam().CommitTransaction();
//this.GetDam().ConnectionClose();
#endregion
#region DB2_SZ
//BaseLogic.InitDam("DB2_SZ", damWork);
//this.SetDam(damWork);
//// 行数
//// Damを直接使用することもできるが、
//// 通常は、データアクセスにはDaoを使用する。
//cmnDao = new CmnDao(this.GetDam());
//cmnDao.SQLText = "SELECT COUNT(*) FROM SHIPPERS";
//obj = (object)cmnDao.ExecSelectScalar();
//this.GetDam().CommitTransaction();
//this.GetDam().ConnectionClose();
#endregion
#region DB2_SS
// ★ サポートされない分離レベル
#endregion
#region DB2_DF
//BaseLogic.InitDam("DB2_DF", damWork);
//this.SetDam(damWork);
//// 行数
//// Damを直接使用することもできるが、
//// 通常は、データアクセスにはDaoを使用する。
//cmnDao = new CmnDao(this.GetDam());
//cmnDao.SQLText = "SELECT COUNT(*) FROM SHIPPERS";
//obj = (object)cmnDao.ExecSelectScalar();
//this.GetDam().CommitTransaction();
//this.GetDam().ConnectionClose();
#endregion
#endregion
#region MySQL
damWork = new DamMySQL();
#region 接続しない
BaseLogic.InitDam("XXXX", damWork);
this.SetDam(damWork);
// なにもしない。
// プロパティにアクセス（デバッガで確認）
idcnn = ((DamMySQL)this.GetDam()).DamMySqlConnection;
idtx = ((DamMySQL)this.GetDam()).DamMySqlTransaction;
// nullの時に呼んだ場合。
this.GetDam().CommitTransaction();
this.GetDam().ConnectionClose();
#endregion
#region MCN_NT
BaseLogic.InitDam("MCN_NT", damWork);
this.SetDam(damWork);
// 行数
// Damを直接使用することもできるが、
// 通常は、データアクセスにはDaoを使用する。
cmnDao = new CmnDao(this.GetDam());
cmnDao.SQLText = "SELECT COUNT(*) FROM SHIPPERS";
obj = (object)cmnDao.ExecSelectScalar();
//this.GetDam().CommitTransaction();
this.GetDam().ConnectionClose();
#endregion
#region MCN_UC
BaseLogic.InitDam("MCN_UC", damWork);
this.SetDam(damWork);
// 行数
// Damを直接使用することもできるが、
// 通常は、データアクセスにはDaoを使用する。
cmnDao = new CmnDao(this.GetDam());
cmnDao.SQLText = "SELECT COUNT(*) FROM SHIPPERS";
obj = (object)cmnDao.ExecSelectScalar();
this.GetDam().CommitTransaction();
this.GetDam().ConnectionClose();
#endregion
#region MCN_RC
BaseLogic.InitDam("MCN_RC", damWork);
this.SetDam(damWork);
// 行数
// Damを直接使用することもできるが、
// 通常は、データアクセスにはDaoを使用する。
cmnDao = new CmnDao(this.GetDam());
cmnDao.SQLText = "SELECT COUNT(*) FROM SHIPPERS";
obj = (object)cmnDao.ExecSelectScalar();
// プロパティにアクセス（デバッガで確認）
idcnn = ((DamMySQL)this.GetDam()).DamMySqlConnection;
idtx = ((DamMySQL)this.GetDam()).DamMySqlTransaction;
idcmd = ((DamMySQL)this.GetDam()).DamMySqlCommand;
idapt = ((DamMySQL)this.GetDam()).DamMySqlDataAdapter;
ds = new DataSet();
idapt.Fill(ds);
this.GetDam().CommitTransaction();
this.GetDam().ConnectionClose();
// ２連続で呼んだ場合。
this.GetDam().CommitTransaction();
this.GetDam().ConnectionClose();
#endregion
#region MCN_RR
BaseLogic.InitDam("MCN_RR", damWork);
this.SetDam(damWork);
// 行数
// Damを直接使用することもできるが、
// 通常は、データアクセスにはDaoを使用する。
cmnDao = new CmnDao(this.GetDam());
cmnDao.SQLText = "SELECT COUNT(*) FROM SHIPPERS";
obj = (object)cmnDao.ExecSelectScalar();
this.GetDam().CommitTransaction();
this.GetDam().ConnectionClose();
#endregion
#region MCN_SZ
BaseLogic.InitDam("MCN_SZ", damWork);
this.SetDam(damWork);
// 行数
// Damを直接使用することもできるが、
// 通常は、データアクセスにはDaoを使用する。
cmnDao = new CmnDao(this.GetDam());
cmnDao.SQLText = "SELECT COUNT(*) FROM SHIPPERS";
obj = (object)cmnDao.ExecSelectScalar();
this.GetDam().CommitTransaction();
this.GetDam().ConnectionClose();
#endregion
#region MCN_SS
// ★ サポートされない分離レベル
#endregion
#region MCN_DF
BaseLogic.InitDam("MCN_DF", damWork);
this.SetDam(damWork);
// 行数
// Damを直接使用することもできるが、
// 通常は、データアクセスにはDaoを使用する。
cmnDao = new CmnDao(this.GetDam());
cmnDao.SQLText = "SELECT COUNT(*) FROM SHIPPERS";
obj = (object)cmnDao.ExecSelectScalar();
this.GetDam().CommitTransaction();
this.GetDam().ConnectionClose();
#endregion
#endregion
#region エラー処理（ロールバックのテスト）
if ((parameterValue.ActionType.Split('%'))[1] != "-")
{
#region エラー時のDamの状態選択
if ((parameterValue.ActionType.Split('%'))[2] == "UT")
{
// トランザクションあり
damWork = new DamSqlSvr();
damWork.ConnectionOpen(GetConfigParameter.GetConnectionString("ConnectionString_SQL"));
damWork.BeginTransaction(DbEnum.IsolationLevelEnum.ReadCommitted);
this.SetDam(damWork);
}
else if ((parameterValue.ActionType.Split('%'))[2] == "NT")
{
// トランザクションなし
damWork = new DamSqlSvr();
damWork.ConnectionOpen(GetConfigParameter.GetConnectionString("ConnectionString_SQL"));
this.SetDam(damWork);
}
else if ((parameterValue.ActionType.Split('%'))[2] == "NC")
{
// コネクションなし
damWork = new DamSqlSvr();
this.SetDam(damWork);
}
else if ((parameterValue.ActionType.Split('%'))[2] == "NULL")
{
// データアクセス制御クラス = Null
this.SetDam(null);
}
#endregion
#region エラーのスロー
if ((parameterValue.ActionType.Split('%'))[1] == "Business")
{
// 業務例外のスロー
throw new BusinessApplicationException(
"ロールバックのテスト",
"ロールバックのテスト",
"エラー情報");
}
else if ((parameterValue.ActionType.Split('%'))[1] == "System")
{
// システム例外のスロー
throw new BusinessSystemException(
"ロールバックのテスト",
"ロールバックのテスト");
}
else if ((parameterValue.ActionType.Split('%'))[1] == "Other")
{
// その他、一般的な例外のスロー
throw new Exception("ロールバックのテスト");
}
else if ((parameterValue.ActionType.Split('%'))[1] == "Other-Business")
{
// その他、一般的な例外（業務例外へ振り替え）のスロー
throw new Exception("Other-Business");
}
else if ((parameterValue.ActionType.Split('%'))[1] == "Other-System")
{
// その他、一般的な例外（システム例外へ振り替え）のスロー
throw new Exception("Other-System");
}
#endregion
}
#endregion
}
public void NonQueryExecuting(IDbCommand command, DbCommandInterceptionContext <int> interceptionContext)
{
Debug.WriteLine(AppendDbCommandInfo(command));
Console.WriteLine(command.CommandText);
AmendParameter(command);
}
public override void Insert(NodeRow value)
{
NodeRow _existing = GetByIPAddress(value.Ip_address);
if (_existing != null)
{
throw new Exception("Node with the same IP Address [" + value.DottedIPAddress + "] already exists.");
}
string sqlStr = "DECLARE " + base.Database.CreateSqlParameterName(NodeRow.node_id_PropName) + " smallint " +
"SET " + base.Database.CreateSqlParameterName(NodeRow.node_id_PropName) +
" = COALESCE((SELECT MAX(" + NodeRow.node_id_DbName + ") FROM Node) + 1, 1) " +
"INSERT INTO [dbo].[Node] (" +
"[" + NodeRow.node_id_DbName + "], " +
"[" + NodeRow.platform_id_DbName + "], " +
"[" + NodeRow.description_DbName + "], " +
"[" + NodeRow.node_config_DbName + "], " +
"[" + NodeRow.transport_type_DbName + "], " +
"[" + NodeRow.user_name_DbName + "], " +
"[" + NodeRow.password_DbName + "], " +
"[" + NodeRow.ip_address_DbName + "], " +
"[" + NodeRow.port_DbName + "], " +
"[" + NodeRow.status_DbName + "], " +
"[" + NodeRow.billing_export_frequency_DbName + "], " +
"[" + NodeRow.cdr_publishing_frequency_DbName + "]" +
") VALUES (" +
base.Database.CreateSqlParameterName(NodeRow.node_id_PropName) + ", " +
base.Database.CreateSqlParameterName(NodeRow.platform_id_PropName) + ", " +
base.Database.CreateSqlParameterName(NodeRow.description_PropName) + ", " +
base.Database.CreateSqlParameterName(NodeRow.node_config_PropName) + ", " +
base.Database.CreateSqlParameterName(NodeRow.transport_type_PropName) + ", " +
base.Database.CreateSqlParameterName(NodeRow.user_name_PropName) + ", " +
base.Database.CreateSqlParameterName(NodeRow.password_PropName) + ", " +
base.Database.CreateSqlParameterName(NodeRow.ip_address_PropName) + ", " +
base.Database.CreateSqlParameterName(NodeRow.port_PropName) + ", " +
base.Database.CreateSqlParameterName(NodeRow.status_PropName) + ", " +
base.Database.CreateSqlParameterName(NodeRow.billing_export_frequency_PropName) + ", " +
base.Database.CreateSqlParameterName(NodeRow.cdr_publishing_frequency_PropName) + ")" +
" SELECT " + base.Database.CreateSqlParameterName(NodeRow.node_id_PropName);
IDbCommand cmd = base.Database.CreateCommand(sqlStr);
//base.Database.AddParameter(cmd, NodeRow.node_id_PropName, DbType.Int16, value.Node_id);
base.Database.AddParameter(cmd, NodeRow.platform_id_PropName, DbType.Int16, value.Platform_id);
base.Database.AddParameter(cmd, NodeRow.description_PropName, DbType.AnsiString, value.Description);
base.Database.AddParameter(cmd, NodeRow.node_config_PropName, DbType.Int32, value.Node_config);
base.Database.AddParameter(cmd, NodeRow.transport_type_PropName, DbType.Byte, value.Transport_type);
base.Database.AddParameter(cmd, NodeRow.user_name_PropName, DbType.AnsiString, value.User_name);
base.Database.AddParameter(cmd, NodeRow.password_PropName, DbType.AnsiString, value.Password);
base.Database.AddParameter(cmd, NodeRow.ip_address_PropName, DbType.Int32, value.Ip_address);
base.Database.AddParameter(cmd, NodeRow.port_PropName, DbType.Int32, value.Port);
base.Database.AddParameter(cmd, NodeRow.status_PropName, DbType.Byte, value.Status);
base.Database.AddParameter(cmd, NodeRow.billing_export_frequency_PropName, DbType.Int32, value.Billing_export_frequency);
base.Database.AddParameter(cmd, NodeRow.cdr_publishing_frequency_PropName, DbType.Int32, value.Cdr_publishing_frequency);
object _res = cmd.ExecuteScalar();
value.Node_id = (short)_res;
}
/// <inheritdoc/>
public override void FixupCommand(IDbCommand command)
{
var cmd = (OracleCommand)command;
cmd.BindByName = true;
}
public static IDataParameter GetParameter(this IDbCommand cmd, int index)
{
return((IDataParameter)cmd.Parameters[index]);
}
/// <summary>
/// 执行command操作
/// </summary>
/// <param name="cmd"></param>
/// <param name="conn"></param>
/// <returns></returns>
public int ExecuteCommand(IDbCommand cmd)
{
return(cmd.ExecuteNonQuery());
}
private void PrepareStatements()
{
// _deleteJobCommand
_deleteJobCommand = _connection.CreateCommand();
_deleteJobCommand.CommandText = "delete from SyncJobs where Id=@Id";
_deleteJobCommand.Parameters.Add(_deleteJobCommand, "@Id");
// _deleteJobItemsCommand
_deleteJobItemsCommand = _connection.CreateCommand();
_deleteJobItemsCommand.CommandText = "delete from SyncJobItems where JobId=@JobId";
_deleteJobItemsCommand.Parameters.Add(_deleteJobItemsCommand, "@JobId");
// _insertJobCommand
_insertJobCommand = _connection.CreateCommand();
_insertJobCommand.CommandText = "insert into SyncJobs (Id, TargetId, Name, Profile, Quality, Bitrate, Status, Progress, UserId, ItemIds, Category, ParentId, UnwatchedOnly, ItemLimit, SyncNewContent, DateCreated, DateLastModified, ItemCount) values (@Id, @TargetId, @Name, @Profile, @Quality, @Bitrate, @Status, @Progress, @UserId, @ItemIds, @Category, @ParentId, @UnwatchedOnly, @ItemLimit, @SyncNewContent, @DateCreated, @DateLastModified, @ItemCount)";
_insertJobCommand.Parameters.Add(_insertJobCommand, "@Id");
_insertJobCommand.Parameters.Add(_insertJobCommand, "@TargetId");
_insertJobCommand.Parameters.Add(_insertJobCommand, "@Name");
_insertJobCommand.Parameters.Add(_insertJobCommand, "@Profile");
_insertJobCommand.Parameters.Add(_insertJobCommand, "@Quality");
_insertJobCommand.Parameters.Add(_insertJobCommand, "@Bitrate");
_insertJobCommand.Parameters.Add(_insertJobCommand, "@Status");
_insertJobCommand.Parameters.Add(_insertJobCommand, "@Progress");
_insertJobCommand.Parameters.Add(_insertJobCommand, "@UserId");
_insertJobCommand.Parameters.Add(_insertJobCommand, "@ItemIds");
_insertJobCommand.Parameters.Add(_insertJobCommand, "@Category");
_insertJobCommand.Parameters.Add(_insertJobCommand, "@ParentId");
_insertJobCommand.Parameters.Add(_insertJobCommand, "@UnwatchedOnly");
_insertJobCommand.Parameters.Add(_insertJobCommand, "@ItemLimit");
_insertJobCommand.Parameters.Add(_insertJobCommand, "@SyncNewContent");
_insertJobCommand.Parameters.Add(_insertJobCommand, "@DateCreated");
_insertJobCommand.Parameters.Add(_insertJobCommand, "@DateLastModified");
_insertJobCommand.Parameters.Add(_insertJobCommand, "@ItemCount");
// _updateJobCommand
_updateJobCommand = _connection.CreateCommand();
_updateJobCommand.CommandText = "update SyncJobs set TargetId=@TargetId,Name=@Name,Profile=@Profile,Quality=@Quality,Bitrate=@Bitrate,Status=@Status,Progress=@Progress,UserId=@UserId,ItemIds=@ItemIds,Category=@Category,ParentId=@ParentId,UnwatchedOnly=@UnwatchedOnly,ItemLimit=@ItemLimit,SyncNewContent=@SyncNewContent,DateCreated=@DateCreated,DateLastModified=@DateLastModified,ItemCount=@ItemCount where Id=@ID";
_updateJobCommand.Parameters.Add(_updateJobCommand, "@Id");
_updateJobCommand.Parameters.Add(_updateJobCommand, "@TargetId");
_updateJobCommand.Parameters.Add(_updateJobCommand, "@Name");
_updateJobCommand.Parameters.Add(_updateJobCommand, "@Profile");
_updateJobCommand.Parameters.Add(_updateJobCommand, "@Quality");
_updateJobCommand.Parameters.Add(_updateJobCommand, "@Bitrate");
_updateJobCommand.Parameters.Add(_updateJobCommand, "@Status");
_updateJobCommand.Parameters.Add(_updateJobCommand, "@Progress");
_updateJobCommand.Parameters.Add(_updateJobCommand, "@UserId");
_updateJobCommand.Parameters.Add(_updateJobCommand, "@ItemIds");
_updateJobCommand.Parameters.Add(_updateJobCommand, "@Category");
_updateJobCommand.Parameters.Add(_updateJobCommand, "@ParentId");
_updateJobCommand.Parameters.Add(_updateJobCommand, "@UnwatchedOnly");
_updateJobCommand.Parameters.Add(_updateJobCommand, "@ItemLimit");
_updateJobCommand.Parameters.Add(_updateJobCommand, "@SyncNewContent");
_updateJobCommand.Parameters.Add(_updateJobCommand, "@DateCreated");
_updateJobCommand.Parameters.Add(_updateJobCommand, "@DateLastModified");
_updateJobCommand.Parameters.Add(_updateJobCommand, "@ItemCount");
// _insertJobItemCommand
_insertJobItemCommand = _connection.CreateCommand();
_insertJobItemCommand.CommandText = "insert into SyncJobItems (Id, ItemId, ItemName, MediaSourceId, JobId, TemporaryPath, OutputPath, Status, TargetId, DateCreated, Progress, AdditionalFiles, MediaSource, IsMarkedForRemoval, JobItemIndex) values (@Id, @ItemId, @ItemName, @MediaSourceId, @JobId, @TemporaryPath, @OutputPath, @Status, @TargetId, @DateCreated, @Progress, @AdditionalFiles, @MediaSource, @IsMarkedForRemoval, @JobItemIndex)";
_insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@Id");
_insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@ItemId");
_insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@ItemName");
_insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@MediaSourceId");
_insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@JobId");
_insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@TemporaryPath");
_insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@OutputPath");
_insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@Status");
_insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@TargetId");
_insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@DateCreated");
_insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@Progress");
_insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@AdditionalFiles");
_insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@MediaSource");
_insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@IsMarkedForRemoval");
_insertJobItemCommand.Parameters.Add(_insertJobItemCommand, "@JobItemIndex");
// _updateJobItemCommand
_updateJobItemCommand = _connection.CreateCommand();
_updateJobItemCommand.CommandText = "update SyncJobItems set ItemId=@ItemId,ItemName=@ItemName,MediaSourceId=@MediaSourceId,JobId=@JobId,TemporaryPath=@TemporaryPath,OutputPath=@OutputPath,Status=@Status,TargetId=@TargetId,DateCreated=@DateCreated,Progress=@Progress,AdditionalFiles=@AdditionalFiles,MediaSource=@MediaSource,IsMarkedForRemoval=@IsMarkedForRemoval,JobItemIndex=@JobItemIndex where Id=@Id";
_updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@Id");
_updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@ItemId");
_updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@ItemName");
_updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@MediaSourceId");
_updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@JobId");
_updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@TemporaryPath");
_updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@OutputPath");
_updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@Status");
_updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@TargetId");
_updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@DateCreated");
_updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@Progress");
_updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@AdditionalFiles");
_updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@MediaSource");
_updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@IsMarkedForRemoval");
_updateJobItemCommand.Parameters.Add(_updateJobItemCommand, "@JobItemIndex");
}
private void OnExecutingDBCommand(Database arg1, IDbCommand arg2)
{
// logger.Info("Database query: " + arg2.CommandText);
}
