public virtual void ExecuteNonQuery(SqlStatementWithParameters sqlStatementWithParameters)
{
var connection = OpenConnection();
var logTimer = LogTimer("Executing non query {Query}.", sqlStatementWithParameters.Statement);
var command = PrepareSqlCommand(sqlStatementWithParameters);
command.Connection = connection;
command.CommandTimeout = 60 * 60;
try
{
command.ExecuteNonQuery();
logTimer.Done();
}
catch (DbException ex)
{
var newEx = new Exception($"Sql fails:\r\n{command.CommandText}\r\n{ex.Message}", ex);
throw newEx;
}
finally
{
connection.Close();
connection.Dispose();
}
}
public override RowSet ExecuteQuery(SqlStatementWithParameters sqlStatementWithParameters)
{
Log(LogSeverity.Verbose, "Executing query {Query}.", sqlStatementWithParameters.Statement);
using (var command = PrepareSqlCommand(sqlStatementWithParameters))
{
command.Connection = _connection;
var rowSet = new RowSet();
using (var sqlReader = command.ExecuteReader())
{
try
{
while (sqlReader.Read())
{
var row = new Row();
for (var i = 0; i < sqlReader.FieldCount; i++)
{
row.Add(sqlReader.GetName(i), sqlReader[i]);
}
rowSet.Rows.Add(row);
}
return(rowSet);
}
catch (SQLiteException ex)
{
var newEx = new Exception($"Sql fails:\r\n{command.CommandText}\r\n{ex.Message}", ex);
throw newEx;
}
}
}
}
public virtual object ExecuteScalar(SqlStatementWithParameters sqlStatementWithParameters)
{
object result;
var connection = OpenConnection();
// Log(LogSeverity.Verbose, "Executing scalar {Query}.", sqlStatementWithParameters.Statement);
var logTimer = LogTimer("Executing scalar {Query}.", sqlStatementWithParameters.Statement);
var command = PrepareSqlCommand(sqlStatementWithParameters);
command.Connection = connection;
try
{
result = command.ExecuteScalar();
}
catch (DbException ex)
{
var newEx = new Exception($"Sql fails:\r\n{command.CommandText}\r\n{ex.Message}", ex);
throw newEx;
}
finally
{
connection.Close();
connection.Dispose();
}
logTimer.Done();
return(result);
}
protected override void ExecuteNonQueryMaster(SqlStatementWithParameters sqlStatementWithParameters)
{
using (var command = PrepareSqlCommand(sqlStatementWithParameters))
{
command.ExecuteNonQuery();
}
}
protected override void ExecuteNonQueryMaster(SqlStatementWithParameters sqlStatementWithParameters)
{
SqlConnection.ClearAllPools(); // force closing connections to normal database to be able to exetute DDLs.
var connection = OpenConnectionMaster();
Log(LogSeverity.Verbose, "Executing query {Query} on master.", sqlStatementWithParameters.Statement);
var command = PrepareSqlCommand(sqlStatementWithParameters);
command.Connection = connection;
try
{
command.ExecuteNonQuery();
}
catch (DbException ex)
{
var newEx = new Exception($"Sql fails:\r\n{command.CommandText}\r\n{ex.Message}", ex);
throw newEx;
}
finally
{
connection.Close();
connection.Dispose();
}
}
public override void ExecuteNonQuery(SqlStatementWithParameters sqlStatementWithParameters)
{
var sqlStatements = BreakIfMultipleCommands(sqlStatementWithParameters);
foreach (var sqlStatement in sqlStatements)
{
base.ExecuteNonQuery(sqlStatement);
}
}
public void SpSimpleQuryBuilder(SqlEngineVersion version)
{
var db = new DbWithSpQueryBuilder();
Init(version, db);
var sqlStatementWithParameters = new SqlStatementWithParameters("EXEC GetCompaniesWithParameter @Id=@Id");
sqlStatementWithParameters.Parameters.Add("@Id", 1);
SqlExecuterTestAdapter.GetExecuter(version.UniqueName).ExecuteQuery(sqlStatementWithParameters);
}
private static List <SqlStatementWithParameters> BreakIfMultipleCommands(SqlStatementWithParameters sqlStatementWithParameters)
{
var sqlStatements = new List <SqlStatementWithParameters>();
if (!(sqlStatementWithParameters.Statement.Trim().StartsWith("BEGIN", StringComparison.InvariantCultureIgnoreCase) &&
sqlStatementWithParameters.Statement.Trim().EndsWith("END;", StringComparison.InvariantCultureIgnoreCase)))
{
var count = 0;
foreach (var c in sqlStatementWithParameters.Statement)
{
if (c == ';')
{
count++;
}
if (count > 1)
{
break;
}
}
var sqlStatementTrimEnd = sqlStatementWithParameters.Statement.TrimEnd();
if (count == 1 && sqlStatementTrimEnd[^ 1] == ';')
{
sqlStatementWithParameters.Statement = sqlStatementTrimEnd.Remove(sqlStatementTrimEnd.Length - 1);
sqlStatements.Add(sqlStatementWithParameters);
}
else if (count > 1)
{
foreach (var subStatement in sqlStatementWithParameters.Statement.Split(';'))
{
if (subStatement.Trim().Length > 0)
{
sqlStatements.Add(new SqlStatementWithParameters(subStatement, sqlStatementWithParameters.Parameters));
}
}
}
else
{
sqlStatements.Add(sqlStatementWithParameters);
}
}
else
{
sqlStatements.Add(sqlStatementWithParameters);
}
return(sqlStatements);
}
public virtual RowSet ExecuteQuery(SqlStatementWithParameters sqlStatementWithParameters)
{
var connection = OpenConnection();
//Log(LogSeverity.Verbose, "Executing query {Query}.", sqlStatementWithParameters.Statement);
var logTimer = LogTimer("Executing query {Query}.", sqlStatementWithParameters.Statement);
var command = PrepareSqlCommand(sqlStatementWithParameters);
command.Connection = connection;
try
{
var rowSet = new RowSet();
using (var sqlReader = command.ExecuteReader())
{
var rowCount = 0;
while (sqlReader.Read())
{
rowCount++;
var row = new Row();
for (var i = 0; i < sqlReader.FieldCount; i++)
{
row.Add(sqlReader.GetName(i), sqlReader[i]);
}
rowSet.Rows.Add(row);
}
logTimer.Done();
Log(LogSeverity.Verbose, "{rowCount} rows returned", rowCount);
}
logTimer.Done();
return(rowSet);
}
catch (DbException ex)
{
var newEx = new Exception($"Sql fails:\r\n{command.CommandText}\r\n{ex.Message}", ex);
throw newEx;
}
finally
{
connection.Close();
connection.Dispose();
}
}
public override SqlStatementWithParameters CreateDbTableDescription(SqlTable table)
{
var sqlTableDescription = table.Properties.OfType <SqlTableDescription>().FirstOrDefault();
if (sqlTableDescription == null)
{
return(null);
}
var sqlStatementWithParameters = new SqlStatementWithParameters("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value = @Description, @level0type=N'SCHEMA', @level0name = @SchemaName, @level1type=N'TABLE', @level1name = @TableName");
sqlStatementWithParameters.Parameters.Add("@Description", sqlTableDescription.Description);
sqlStatementWithParameters.Parameters.Add("@SchemaName", table.SchemaAndTableName.Schema ?? Context.Settings.SqlVersionSpecificSettings.GetAs <string>("DefaultSchema"));
sqlStatementWithParameters.Parameters.Add("@TableName", table.SchemaAndTableName.TableName);
return(sqlStatementWithParameters);
}
public override object ExecuteScalar(SqlStatementWithParameters sqlStatementWithParameters)
{
Log(LogSeverity.Verbose, "Executing scalar {Query}.", sqlStatementWithParameters.Statement);
using (var command = PrepareSqlCommand(sqlStatementWithParameters))
{
try
{
var result = command.ExecuteScalar();
return(result);
}
catch (SQLiteException ex)
{
var newEx = new Exception($"Sql fails:\r\n{command.CommandText}\r\n{ex.Message}", ex);
throw newEx;
}
}
}
public virtual DbCommand PrepareSqlCommand(SqlStatementWithParameters sqlStatementWithParameters)
{
var dbf = DbProviderFactories.GetFactory(ConnectionString.ProviderName);
var command = dbf.CreateCommand();
#pragma warning disable CA2100 // Review SQL queries for security vulnerabilities
command.CommandText = sqlStatementWithParameters.Statement;
#pragma warning restore CA2100 // Review SQL queries for security vulnerabilities
foreach (var parameter in sqlStatementWithParameters.Parameters)
{
var dbParameter = command.CreateParameter();
dbParameter.ParameterName = parameter.Key;
dbParameter.Value = parameter.Value;
command.Parameters.Add(dbParameter);
}
return(command);
}
public override void ExecuteNonQuery(SqlStatementWithParameters sqlStatementWithParameters)
{
Log(LogSeverity.Verbose, "Executing non query {Query}.", sqlStatementWithParameters.Statement);
using (var command = PrepareSqlCommand(sqlStatementWithParameters))
{
command.Connection = _connection;
try
{
{
command.ExecuteNonQuery();
}
}
catch (SQLiteException ex)
{
var newEx = new Exception($"Sql fails:\r\n{command.CommandText}\r\n{ex.Message}", ex);
throw newEx;
}
}
}
protected abstract void ExecuteNonQueryMaster(SqlStatementWithParameters sqlStatementWithParameters);
public override DbCommand PrepareSqlCommand(SqlStatementWithParameters sqlStatementWithParameters)
{
var dbCommand = base.PrepareSqlCommand(sqlStatementWithParameters);
return(OracleDbCommandPreparer.PrepareSqlCommand(dbCommand));
}
protected override void ExecuteNonQueryMaster(SqlStatementWithParameters sqlStatementWithParameters)
{
ExecuteQuery(sqlStatementWithParameters);
}
