/// <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>
/// <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>
/// The db_runsql.
/// </summary>
/// <param name="sql">
/// The sql.
/// </param>
/// <param name="connectionManager">
/// The conn man.
/// </param>
/// <param name="useTransaction">
/// The use Transaction.
/// </param>
/// <returns>
/// The db_runsql.
/// </returns>
public static string RunSQL(this IDbAccess dbAccess, [NotNull] string sql, bool useTransaction)
{
try
{
using (var trans = dbAccess.CreateConnectionOpen().BeginTransaction())
{
sql = CommandTextHelpers.GetCommandTextReplaced(sql.Trim());
using (var cmd = trans.Connection.CreateCommand())
{
// added so command won't timeout anymore...
cmd.CommandTimeout = int.Parse(Config.SqlCommandTimeout);
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
return(InnerRunSqlExecuteReader(cmd as SqlCommand, useTransaction));
}
}
}
finally
{
messageRunSql = string.Empty;
}
}
/// <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 begin transaction.
/// </summary>
/// <param name="dbAccess">
/// The DB access.
/// </param>
/// <param name="isolationLevel">
/// The isolation level.
/// </param>
/// <returns>
/// The <see cref="IDbTransaction"/> .
/// </returns>
public static IDbTransaction BeginTransaction(
[NotNull] this IDbAccess dbAccess,
IsolationLevel isolationLevel = IsolationLevel.ReadUncommitted)
{
CodeContracts.VerifyNotNull(dbAccess, "dbAccess");
return(dbAccess.CreateConnectionOpen().BeginTransaction(isolationLevel));
}
/// <summary>
/// The run.
/// </summary>
/// <param name="dbAccess">
/// The DB access.
/// </param>
/// <param name="runFunc">
/// The run function.
/// </param>
/// <typeparam name="T">The type Parameter</typeparam>
/// <returns>
/// The <see cref="T"/> .
/// </returns>
public static T Run <T>([NotNull] this IDbAccess dbAccess, Func <IDbConnection, T> runFunc)
{
CodeContracts.VerifyNotNull(dbAccess, "dbAccess");
CodeContracts.VerifyNotNull(runFunc, "runFunc");
using (var connection = dbAccess.CreateConnectionOpen())
{
return(runFunc(connection));
}
}
/// <summary>
/// The db_recovery_mode.
/// </summary>
/// <param name="dbAccess">
/// The db Access.
/// </param>
/// <param name="recoveryMode">
/// The recovery mode.
/// </param>
public static string ChangeRecoveryMode(this IDbAccess dbAccess, [NotNull] string recoveryMode)
{
try
{
var recoveryModeSql =
$"ALTER DATABASE {dbAccess.CreateConnectionOpen().Database} SET RECOVERY {recoveryMode}";
return(dbAccess.Execute(
db => db.Connection.Scalar <string>(recoveryModeSql)));
}
catch (Exception error)
{
var expressDb = string.Empty;
if (error.Message.ToUpperInvariant().Contains("'SET'"))
{
expressDb = "MS SQL Server Express Editions are not supported by the application.";
}
return($"\r\n{error.Message}\r\n{expressDb}");
}
}
/// <summary>
/// Test the DB Connection.
/// </summary>
/// <param name="dbAccess">
/// The db Access.
/// </param>
/// <param name="exceptionMessage">
/// outbound ExceptionMessage
/// </param>
/// <returns>
/// true if successfully connected
/// </returns>
public static bool TestConnection([NotNull] this IDbAccess dbAccess, [NotNull] out string exceptionMessage)
{
exceptionMessage = string.Empty;
var success = false;
try
{
using (var connection = dbAccess.CreateConnectionOpen())
{
// we're connected!
var conn = connection;
}
// success
success = true;
}
catch (DbException x)
{
// unable to connect...
exceptionMessage = x.Message;
}
return(success);
}
/// <summary>
/// The db_recovery_mode.
/// </summary>
/// <param name="DBName">
/// The db name.
/// </param>
/// <param name="recoveryMode">
/// The recovery mode.
/// </param>
public static string ChangeRecoveryMode(this IDbAccess dbAccess, [NotNull] string recoveryMode)
{
try
{
var recoveryModeSql = "ALTER DATABASE {0} SET RECOVERY {1}".FormatWith(
dbAccess.CreateConnectionOpen().Database,
recoveryMode);
using (var cmd = dbAccess.GetCommand(recoveryModeSql, CommandType.Text))
{
return(dbAccess.ExecuteScalar(cmd).ToString());
}
}
catch (Exception error)
{
var expressDb = string.Empty;
if (error.Message.ToUpperInvariant().Contains("'SET'"))
{
expressDb = "MS SQL Server Express Editions are not supported by the application.";
}
return("\r\n{0}\r\n{1}".FormatWith(error.Message, expressDb));
}
}
/// <summary>
/// The system_initialize_fixaccess.
/// </summary>
/// <param name="grant">
/// The grant.
/// </param>
public static void SystemInitializeFixaccess(this IDbAccess dbAccess, bool grant)
{
using (var trans = dbAccess.CreateConnectionOpen().BeginTransaction())
{
var sqlConnection = trans.Connection as SqlConnection;
// REVIEW : Ederon - would "{databaseOwner}.{objectQualifier}" work, might need only "{objectQualifier}"
using (var da = new SqlDataAdapter(
"select Name,IsUserTable = OBJECTPROPERTY(id, N'IsUserTable'),IsScalarFunction = OBJECTPROPERTY(id, N'IsScalarFunction'),IsProcedure = OBJECTPROPERTY(id, N'IsProcedure'),IsView = OBJECTPROPERTY(id, N'IsView') from dbo.sysobjects where Name like '{databaseOwner}.{objectQualifier}%'",
sqlConnection))
{
da.SelectCommand.Transaction = trans as SqlTransaction;
using (var dt = new DataTable("sysobjects"))
{
da.Fill(dt);
using (var cmd = trans.Connection.CreateCommand())
{
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select current_user";
var userName = (string)cmd.ExecuteScalar();
if (grant)
{
cmd.CommandType = CommandType.Text;
foreach (var row in dt.Select("IsProcedure=1 or IsScalarFunction=1"))
{
cmd.CommandText =
"grant execute on \"{0}\" to \"{1}\"".FormatWith(row["Name"], userName);
cmd.ExecuteNonQuery();
}
foreach (var row in dt.Select("IsUserTable=1 or IsView=1"))
{
cmd.CommandText =
"grant select,update on \"{0}\" to \"{1}\"".FormatWith(row["Name"], userName);
cmd.ExecuteNonQuery();
}
}
else
{
cmd.CommandText = "sp_changeobjectowner";
cmd.CommandType = CommandType.StoredProcedure;
foreach (var row in dt.Select("IsUserTable=1"))
{
cmd.Parameters.Clear();
cmd.AddParam("@objname", row["Name"]);
cmd.AddParam("@newowner", "dbo");
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException)
{
}
}
foreach (var row in dt.Select("IsView=1"))
{
cmd.Parameters.Clear();
cmd.AddParam("@objname", row["Name"]);
cmd.AddParam("@newowner", "dbo");
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException)
{
}
}
}
}
}
}
trans.Commit();
}
}
/// <summary>
/// System initialize and execute script's.
/// </summary>
/// <param name="script">The script.</param>
/// <param name="scriptFile">The script file.</param>
/// <param name="useTransactions">The use transactions.</param>
public static void SystemInitializeExecutescripts(
this IDbAccess dbAccess,
[NotNull] string script,
[NotNull] string scriptFile,
bool useTransactions)
{
script = CommandTextHelpers.GetCommandTextReplaced(script);
var statements = Regex.Split(script, "\\sGO\\s", RegexOptions.IgnoreCase).ToList();
// use transactions...
if (useTransactions)
{
using (var trans = dbAccess.CreateConnectionOpen().BeginTransaction())
{
foreach (var sql in statements.Select(sql0 => sql0.Trim()))
{
try
{
if (sql.ToLower().IndexOf("setuser") >= 0)
{
continue;
}
if (sql.Length <= 0)
{
continue;
}
using (var cmd = trans.Connection.CreateCommand())
{
// added so command won't timeout anymore...
cmd.CommandTimeout = int.Parse(Config.SqlCommandTimeout);
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql.Trim();
cmd.ExecuteNonQuery();
}
}
catch (Exception x)
{
trans.Rollback();
throw new Exception(
"FILE:\n{0}\n\nERROR:\n{2}\n\nSTATEMENT:\n{1}".FormatWith(scriptFile, sql, x.Message));
}
}
trans.Commit();
}
}
else
{
// don't use transactions
foreach (var sql in statements.Select(sql0 => sql0.Trim()))
{
try
{
if (sql.ToLower().IndexOf("setuser") >= 0)
{
continue;
}
if (sql.Length <= 0)
{
continue;
}
using (var cmd = dbAccess.GetCommand(sql.Trim(), CommandType.Text))
{
dbAccess.ExecuteScalar(cmd).ToType <string>();
}
}
catch (Exception x)
{
throw new Exception(
"FILE:\n{0}\n\nERROR:\n{2}\n\nSTATEMENT:\n{1}".FormatWith(scriptFile, sql, x.Message));
}
}
}
}
