/// <summary>
/// Prepares a command for future use.
/// </summary>
/// <param name="oldPreparedCommand"></param>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="commandText"></param>
/// <param name="paramCount"></param>
/// <returns></returns>
public static SqlPreparedCommand PrepareCommand(SqlPreparedCommand oldPreparedCommand, IDbConnection connection, IDbTransaction transaction, string commandText, int paramCount)
{
var result = oldPreparedCommand;
if (oldPreparedCommand == null || connection != oldPreparedCommand.Connection || transaction != oldPreparedCommand.Transaction)
{
if (oldPreparedCommand != null)
{
oldPreparedCommand.Dispose();
}
result = new SqlPreparedCommand()
{
Command = connection.CreateCommand(),
Connection = connection,
Transaction = transaction
};
result.Command.Transaction = transaction;
result.Command.CommandText = commandText;
for (var c = 0; c < paramCount; c++)
{
IDbDataParameter sqlParameter = result.Command.CreateParameter();
result.Command.Parameters.Add(sqlParameter);
}
}
return(result);
}
/// <summary>
/// Returns a prepared command by the name given by the derived class.
/// </summary>
/// <param name="commandName"></param>
/// <returns></returns>
private SqlPreparedCommand FetchExistingPreparedCommand(string commandName)
{
SqlPreparedCommand existing = null;
_Commands.TryGetValue(commandName, out existing);
return(existing);
}
/// <summary>
/// Executes the insert statement passed across and return the ID number of the new record.
/// </summary>
/// <param name="preparedCommand"></param>
/// <param name="log"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static long ExecuteInsert(SqlPreparedCommand preparedCommand, TextWriter log, params object[] parameters)
{
SetParameters(preparedCommand.Command, parameters);
LogCommand(log, preparedCommand.Command);
var result = Exec.ExecuteScalar(preparedCommand.Command);
return(result == null ? 0L : (long)result);
}
/// <summary>
/// Prepares an insert command.
/// </summary>
protected SqlPreparedCommand PrepareInsert(IDbConnection connection, IDbTransaction transaction, string commandName, string uniqueIdColumnName, params string[] columnNames)
{
SqlPreparedCommand existing = FetchExistingPreparedCommand(commandName);
SqlPreparedCommand result = Sql.PrepareInsert(existing, connection, transaction, TableName, uniqueIdColumnName, columnNames);
RecordPreparedCommand(commandName, existing, result);
return(result);
}
/// <summary>
/// Retrieves or creates a prepared command.
/// </summary>
/// <returns></returns>
protected SqlPreparedCommand PrepareCommand(IDbConnection connection, IDbTransaction transaction, string commandName, string commandText, int paramCount)
{
SqlPreparedCommand existing = FetchExistingPreparedCommand(commandName);
SqlPreparedCommand result = Sql.PrepareCommand(existing, connection, transaction, commandText, paramCount);
RecordPreparedCommand(commandName, existing, result);
return(result);
}
/// <summary>
/// Saves a prepared command against the name given by the derived class.
/// </summary>
/// <param name="commandName"></param>
/// <param name="existing"></param>
/// <param name="result"></param>
private void RecordPreparedCommand(string commandName, SqlPreparedCommand existing, SqlPreparedCommand result)
{
if (existing == null)
{
_Commands.Add(commandName, result);
}
else if (!Object.ReferenceEquals(existing, result))
{
_Commands[commandName] = result;
}
}
/// <summary>
/// Prepares an insert command for future execution with an <see cref="ExecuteInsert"/>.
/// </summary>
/// <param name="preparedCommand"></param>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="tableName"></param>
/// <param name="uniqueIdColumn"></param>
/// <param name="columnNames"></param>
/// <returns></returns>
public static SqlPreparedCommand PrepareInsert(SqlPreparedCommand preparedCommand, IDbConnection connection, IDbTransaction transaction, string tableName, string uniqueIdColumn, params object[] columnNames)
{
var result = preparedCommand;
if (result == null || result.Connection != connection || result.Transaction != transaction)
{
// Start the command off
var commandText = new StringBuilder();
commandText.AppendFormat("INSERT INTO [{0}](", tableName);
// Add all of the column names
for (var c = 0; c < columnNames.Length; c++)
{
if (c > 0)
{
commandText.Append(", ");
}
commandText.AppendFormat("[{0}]", (string)columnNames[c]);
}
// Add the values - just add parameter placeholders
commandText.Append(") VALUES (");
for (var c = 0; c < columnNames.Length; c++)
{
if (c > 0)
{
commandText.Append(", ");
}
commandText.Append('?');
}
commandText.Append(')');
if (uniqueIdColumn != null)
{
commandText.AppendFormat("; SELECT [{0}] FROM [{1}] WHERE _ROWID_ = last_insert_rowid()", uniqueIdColumn, tableName);
}
result = Sql.PrepareCommand(result, connection, transaction, commandText.ToString(), columnNames.Length);
}
return(result);
}
/// <summary>
/// Prepares a command for future use.
/// </summary>
/// <param name="oldPreparedCommand"></param>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="commandText"></param>
/// <param name="paramCount"></param>
/// <returns></returns>
public static SqlPreparedCommand PrepareCommand(SqlPreparedCommand oldPreparedCommand, IDbConnection connection, IDbTransaction transaction, string commandText, int paramCount)
{
var result = oldPreparedCommand;
if(oldPreparedCommand == null || connection != oldPreparedCommand.Connection || transaction != oldPreparedCommand.Transaction) {
if (oldPreparedCommand != null) oldPreparedCommand.Dispose();
result = new SqlPreparedCommand() {
Command = connection.CreateCommand(),
Connection = connection,
Transaction = transaction
};
result.Command.Transaction = transaction;
result.Command.CommandText = commandText;
for(var c = 0;c < paramCount;c++) {
IDbDataParameter sqlParameter = result.Command.CreateParameter();
result.Command.Parameters.Add(sqlParameter);
}
}
return result;
}
/// <summary>
/// Writes values into the parameters on a prepared command. The number of parameters being written must match the
/// number of parameters on the command.
/// </summary>
/// <param name="preparedCommand"></param>
/// <param name="paramValues"></param>
public static void SetParameters(SqlPreparedCommand preparedCommand, params object[] paramValues)
{
SetParameters(preparedCommand.Command, paramValues);
}
/// <summary>
/// Writes values into the parameters on a prepared command. The number of parameters being written must match the
/// number of parameters on the command.
/// </summary>
/// <param name="preparedCommand"></param>
/// <param name="paramValues"></param>
public static void SetParameters(SqlPreparedCommand preparedCommand, params object[] paramValues)
{
SetParameters(preparedCommand.Command, paramValues);
}
/// <summary>
/// Executes the insert statement passed across and return the ID number of the new record.
/// </summary>
/// <param name="preparedCommand"></param>
/// <param name="log"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static long ExecuteInsert(SqlPreparedCommand preparedCommand, TextWriter log, params object[] parameters)
{
SetParameters(preparedCommand.Command, parameters);
LogCommand(log, preparedCommand.Command);
var result = preparedCommand.Command.ExecuteScalar();
return result == null ? 0L : (long)result;
}
/// <summary>
/// Prepares an insert command for future execution with an <see cref="ExecuteInsert"/>.
/// </summary>
/// <param name="preparedCommand"></param>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="tableName"></param>
/// <param name="uniqueIdColumn"></param>
/// <param name="columnNames"></param>
/// <returns></returns>
public static SqlPreparedCommand PrepareInsert(SqlPreparedCommand preparedCommand, IDbConnection connection, IDbTransaction transaction, string tableName, string uniqueIdColumn, params object[] columnNames)
{
var result = preparedCommand;
if(result == null || result.Connection != connection || result.Transaction != transaction) {
// Start the command off
var commandText = new StringBuilder();
commandText.AppendFormat("INSERT INTO [{0}](", tableName);
// Add all of the column names
for(var c = 0;c < columnNames.Length;c++) {
if(c > 0) commandText.Append(", ");
commandText.AppendFormat("[{0}]", (string)columnNames[c]);
}
// Add the values - just add parameter placeholders
commandText.Append(") VALUES (");
for(var c = 0;c < columnNames.Length;c++) {
if(c > 0) commandText.Append(", ");
commandText.Append('?');
}
commandText.Append(')');
if(uniqueIdColumn != null) commandText.AppendFormat("; SELECT [{0}] FROM [{1}] WHERE _ROWID_ = last_insert_rowid()", uniqueIdColumn, tableName);
result = Sql.PrepareCommand(result, connection, transaction, commandText.ToString(), columnNames.Length);
}
return result;
}
/// <summary>
/// Sets the named parameter at the index passed across.
/// </summary>
/// <param name="command"></param>
/// <param name="index"></param>
/// <param name="name"></param>
/// <param name="value"></param>
protected void SetNamedParameter(SqlPreparedCommand command, int index, string name, object value)
{
Sql.SetNamedParameter(command.Command, index, name, value);
}
/// <summary>
/// Saves a prepared command against the name given by the derived class.
/// </summary>
/// <param name="commandName"></param>
/// <param name="existing"></param>
/// <param name="result"></param>
private void RecordPreparedCommand(string commandName, SqlPreparedCommand existing, SqlPreparedCommand result)
{
if (existing == null) _Commands.Add(commandName, result);
else if (!Object.ReferenceEquals(existing, result)) _Commands[commandName] = result;
}