/// <summary>
/// Insert the entity using the model provided.
/// </summary>
/// <typeparam name="T">The type parameter</typeparam>
/// <param name="dbAccess">The DB access.</param>
/// <param name="insert">The insert.</param>
/// <param name="transaction">The transaction.</param>
/// <param name="selectIdentity">if set to <c>true</c> [select identity].</param>
/// <returns>
/// The <see cref="int" />.
/// </returns>
public static long Insert <T>(
[NotNull] this IDbAccess dbAccess,
[NotNull] T insert,
[CanBeNull] IDbTransaction transaction = null,
bool selectIdentity = false) where T : IEntity
{
CodeContracts.VerifyNotNull(dbAccess, "dbAccess");
if (transaction != null && transaction.Connection != null)
{
using (var command = transaction.Connection.CreateCommand())
{
OrmLiteConfig.DialectProvider.PrepareParameterizedInsertStatement <T>(command);
OrmLiteConfig.DialectProvider.SetParameterValues <T>(command, insert);
return(selectIdentity
? OrmLiteConfig.DialectProvider.InsertAndGetLastInsertId <T>(command)
: dbAccess.ExecuteNonQuery(command, transaction));
}
}
// no transaction
using (var connection = dbAccess.CreateConnectionOpen())
{
using (var command = connection.CreateCommand())
{
OrmLiteConfig.DialectProvider.PrepareParameterizedInsertStatement <T>(command);
OrmLiteConfig.DialectProvider.SetParameterValues <T>(command, insert);
return(selectIdentity
? OrmLiteConfig.DialectProvider.InsertAndGetLastInsertId <T>(command)
: dbAccess.ExecuteNonQuery(command, transaction));
}
}
}
/// <summary>
/// Runs the update command.
/// </summary>
/// <typeparam name="T">The type Parameter</typeparam>
/// <param name="dbAccess">The DB access.</param>
/// <param name="update">The update.</param>
/// <param name="transaction">The transaction.</param>
/// <returns>
/// The <see cref="int" />.
/// </returns>
public static int Update <T>(
[NotNull] this IDbAccess dbAccess,
[NotNull] T update,
[CanBeNull] IDbTransaction transaction = null)
where T : IEntity
{
CodeContracts.VerifyNotNull(dbAccess, "dbAccess");
if (transaction?.Connection != null)
{
using (var command = transaction.Connection.CreateCommand())
{
OrmLiteConfig.DialectProvider.PrepareParameterizedUpdateStatement <T>(command);
OrmLiteConfig.DialectProvider.SetParameterValues <T>(command, update);
return(dbAccess.ExecuteNonQuery(command, transaction));
}
}
// no transaction
using (var connection = dbAccess.CreateConnectionOpen())
{
using (var command = connection.CreateCommand())
{
OrmLiteConfig.DialectProvider.PrepareParameterizedUpdateStatement <T>(command);
OrmLiteConfig.DialectProvider.SetParameterValues <T>(command, update);
return(dbAccess.ExecuteNonQuery(command, transaction));
}
}
}
/// <summary>
/// Insert the entity using the model provided.
/// </summary>
/// <param name="dbAccess">
/// The db access.
/// </param>
/// <param name="insert">
/// The insert.
/// </param>
/// <param name="transaction">
/// The transaction.
/// </param>
/// <returns>
/// The <see cref="int"/>.
/// </returns>
public static int Insert <T>([NotNull] this IDbAccess dbAccess, [NotNull] T insert, [CanBeNull] IDbTransaction transaction = null)
where T : IEntity
{
CodeContracts.VerifyNotNull(dbAccess, "dbAccess");
if (transaction != null && transaction.Connection != null)
{
using (var command = OrmLiteConfig.DialectProvider.CreateParameterizedInsertStatement(insert, transaction.Connection))
{
command.Populate(transaction);
dbAccess.ExecuteNonQuery(command, transaction);
return((int)OrmLiteConfig.DialectProvider.GetLastInsertId(command));
}
}
// no transaction
using (var connection = dbAccess.CreateConnectionOpen())
{
using (var command = OrmLiteConfig.DialectProvider.CreateParameterizedInsertStatement(insert, connection))
{
command.Connection = connection;
dbAccess.ExecuteNonQuery(command, transaction);
return((int)OrmLiteConfig.DialectProvider.GetLastInsertId(command));
}
}
}
/// <summary>
/// The execute non query.
/// </summary>
/// <param name="dbAccess">
/// The db access.
/// </param>
/// <param name="cmd">
/// The cmd.
/// </param>
public static void ExecuteNonQuery([NotNull] this IDbAccess dbAccess, [NotNull] IDbCommand cmd)
{
CodeContracts.ArgumentNotNull(dbAccess, "dbAccess");
CodeContracts.ArgumentNotNull(cmd, "cmd");
dbAccess.ExecuteNonQuery(cmd, false);
}
/// <summary>
/// The update.
/// </summary>
/// <param name="dbAccess">
/// The db access.
/// </param>
/// <param name="update">
/// The update.
/// </param>
/// <param name="transaction">
/// The transaction.
/// </param>
/// <typeparam name="T">
/// </typeparam>
/// <returns>
/// The <see cref="int"/>.
/// </returns>
public static int Update <T>([NotNull] this IDbAccess dbAccess, [NotNull] T update, [CanBeNull] IDbTransaction transaction = null)
where T : IEntity
{
CodeContracts.VerifyNotNull(dbAccess, "dbAccess");
using (var connection = dbAccess.CreateConnection())
{
using (var command = OrmLiteConfig.DialectProvider.CreateParameterizedUpdateStatement(update, connection))
{
return(dbAccess.ExecuteNonQuery(command, transaction));
}
}
}
/// <summary>
/// Executes a non query in a transaction
/// </summary>
/// <param name="dbAccess">The database access.</param>
/// <param name="cmd">The command.</param>
/// <param name="useTransaction">if set to <c>true</c> [use transaction].</param>
/// <param name="isolationLevel">The isolation level.</param>
/// <returns>Returns the Result</returns>
public static int ExecuteNonQuery(
[NotNull] this IDbAccess dbAccess, [NotNull] IDbCommand cmd, bool useTransaction, IsolationLevel isolationLevel = IsolationLevel.ReadUncommitted)
{
CodeContracts.VerifyNotNull(dbAccess, "dbAccess");
CodeContracts.VerifyNotNull(cmd, "cmd");
if (!useTransaction)
{
return(dbAccess.ExecuteNonQuery(cmd));
}
using (var dbTransaction = dbAccess.BeginTransaction(isolationLevel))
{
var result = dbAccess.Execute(c => c.ExecuteNonQuery(), cmd, dbTransaction);
dbTransaction.Commit();
return(result);
}
}
private static string GetSequenceName(IDbAccess dataAccess, string tableName, string pkColumnName, bool checkSequence)
{
if (dataAccess == null)
throw new ArgumentNullException(nameof(dataAccess));
if (String.IsNullOrEmpty(tableName))
throw new ArgumentNullException(nameof(tableName));
if (String.IsNullOrEmpty(pkColumnName))
throw new ArgumentNullException(nameof(pkColumnName));
string[] arr = tableName.Split('.');
bool withUser = arr.Length > 1;
string sequenceName = null;
if (withUser)
sequenceName = arr[0] + ".SQE_" + arr[1];
else
sequenceName = "SQE_" + tableName;
if (checkSequence)
{
object temp = null;
decimal ret = 0M, minVal = 1M, curVal = 0M;
SqlQuery query = new SqlQuery();
StringBuilder text = query.Text;
try
{
text.Append("SELECT MAX(");
text.Append(tableName);
text.Append('.');
text.Append(pkColumnName);
text.Append(") FROM ");
text.Append(tableName);
temp = dataAccess.ExecuteScalar(query);
if (temp != null && temp.GetType() != CachedTypes.DBNull)
{
minVal = ((Decimal)temp) + 1M;
}
query.Clear();
text = query.Text;
text.Append(" SELECT COUNT(*) FROM");
if (withUser)
{
text.Append(" ALL_SEQUENCES T WHERE T.SEQUENCE_OWNER = :SEQUENCE_OWNER AND");
query.Parameters.Add("SEQUENCE_OWNER", arr[0]);
query.Parameters.Add("SEQUENCE_NAME", "SQE_" + arr[1]);
}
else
{
text.Append(" USER_SEQUENCES T WHERE");
query.Parameters.Add("SEQUENCE_NAME", sequenceName);
}
text.Append(" T.SEQUENCE_NAME = :SEQUENCE_NAME");
ret = (Decimal)dataAccess.ExecuteScalar(query);
if (ret == 0M)
{
query.Clear();
text = query.Text;
text.Append("CREATE SEQUENCE ");
text.Append(sequenceName);
text.AppendLine();
text.Append("MINVALUE 0");
text.AppendLine();
text.Append("MAXVALUE 9999999999999999999999999");
text.AppendLine();
text.Append("START WITH ");
text.Append(minVal);
text.AppendLine();
text.Append("INCREMENT BY 1");
text.AppendLine();
text.Append("CACHE 20");
text.AppendLine();
dataAccess.ExecuteNonQuery(query);
}
else
{
query.Clear();
text = query.Text;
text.Append("SELECT T.LAST_NUMBER FROM");
if (withUser)
{
text.Append(" ALL_SEQUENCES T WHERE T.SEQUENCE_OWNER = :SEQUENCE_OWNER AND T.SEQUENCE_NAME = :SEQUENCE_NAME");
query.Parameters.Add("SEQUENCE_OWNER", arr[0]);
query.Parameters.Add("SEQUENCE_NAME", "SQE_" + arr[1]);
}
else
{
text.Append(" USER_SEQUENCES T WHERE T.SEQUENCE_NAME = :SEQUENCE_NAME");
query.Parameters.Add("SEQUENCE_NAME", sequenceName);
}
curVal = (Decimal)dataAccess.ExecuteScalar(query);
if (minVal > curVal)
{
query.Clear();
text = query.Text;
text.Append("ALTER SEQUENCE ");
text.Append(sequenceName);
text.Append(" INCREMENT BY :INC;");
query.Parameters.Add("INC", minVal - curVal);
text.AppendLine();
text.Append("SELECT ");
text.Append(sequenceName);
text.Append(".NEXTVAL FROM DUAL;");
text.AppendLine();
text.Append("ALTER SEQUENCE ");
text.Append(sequenceName);
text.Append(" INCREMENT BY 1;");
dataAccess.ExecuteScalar(query);
}
}
}
catch (Exception ex)
{
throw new InvalidOperationException(
$"An error occurred while creating a sequence for '{tableName}' Oracle table. Error detail: '{ex.Message}'");
}
}
return sequenceName;
}
