void SqlExecution(ErrorResultTO errors, int update)
{
var connectionBuilder = new ConnectionBuilder();
var connection = new SqlConnection(connectionBuilder.ConnectionString(Source.ConnectionString));
var startTime = Stopwatch.StartNew();
try
{
connection.Open();
var isXmlRead = ReadForXml(update, startTime, connection);
if (!isXmlRead)
{
using (SqlTransaction dbTransaction = connection.BeginTransaction())
{
try
{
using (var cmd = CreateCommand(connection, GetSqlParameters()))
{
cmd.Transaction = dbTransaction;
using (var reader = cmd.ExecuteReader())
{
var table = new DataTable();
table.Load(reader);
reader.Close();
dbTransaction.Commit();
Dev2Logger.Info("Time taken to process proc " + ProcedureName + ":" + startTime.Elapsed.Milliseconds + " Milliseconds", DataObj.ExecutionID.ToString());
var startTime1 = Stopwatch.StartNew();
TranslateDataTableToEnvironment(table, DataObj.Environment, update);
Dev2Logger.Info("Time taken to TranslateDataTableToEnvironment " + ProcedureName + ":" + startTime1.Elapsed.Milliseconds + " Milliseconds", DataObj.ExecutionID.ToString());
}
}
}
catch (Exception ex)
{
dbTransaction.Rollback();
Dev2Logger.Error("SQL Error:", ex, GlobalConstants.WarewolfError);
Dev2Logger.Error("SQL Error:", ex.StackTrace);
errors.AddError($"SQL Error: {ex.Message}");
}
}
}
}
catch (Exception ex)
{
Dev2Logger.Error("SQL Error:", ex, GlobalConstants.WarewolfError);
Dev2Logger.Error("SQL Error:", ex.StackTrace);
errors.AddError($"SQL Error: {ex.Message}");
}
finally
{
connection.Dispose();
}
}
void MssqlSqlExecution(int connectionTimeout, int?commandTimeout, ErrorResultTO errors, int update)
{
DataObj.StateNotifier?.LogAdditionalDetail(new
{
this.Source,
this.ProcedureName,
this.SqlQuery,
this.ConnectionTimeout,
this.CommandTimeout,
},
nameof(MssqlSqlExecution)
);
var connectionBuilder = new ConnectionBuilder();
var connection = new SqlConnection(connectionBuilder.ConnectionString(Source.GetConnectionStringWithTimeout(connectionTimeout)));
var startTime = Stopwatch.StartNew();
try
{
connection.Open();
if (MssqlIsStoredProcForXmlResult(connection, ProcedureName))
{
MssqlReadDataForXml(update, startTime, connection, commandTimeout);
}
else
{
MssqlReadData(update, startTime, connection, commandTimeout);
}
}
catch (Exception ex)
{
Dev2Logger.Error("SQL Error:", ex, GlobalConstants.WarewolfError);
Dev2Logger.Error("SQL Error:", ex.StackTrace);
errors.AddError($"SQL Error: {ex.Message}");
}
finally
{
connection.Dispose();
}
}
public void ConnectionBuilder_GivenConnectionString_ShouldReturnUpdatedConnectionWithPoolNoMARS()
{
//------------Setup for test--------------------------
var connectionBuilder = new ConnectionBuilder();
var source = new DbSource();
source.Server = "localhost";
source.ServerType = Common.Interfaces.Core.DynamicServices.enSourceType.SqlDatabase;
source.AuthenticationType = AuthenticationType.Windows;
//------------Execute Test---------------------------
var updatedConnectionString = connectionBuilder.ConnectionString(source.ConnectionString);
//------------Assert Results-------------------------
Assert.IsFalse(updatedConnectionString.Contains("MultipleActiveResultSets=True"));
StringAssert.Contains(updatedConnectionString, "Application Name=\"Warewolf Service\"");
StringAssert.Contains(updatedConnectionString, "Pooling=True");
StringAssert.Contains(updatedConnectionString, "Data Source=localhost");
StringAssert.Contains(updatedConnectionString, "Integrated Security=True");
StringAssert.Contains(updatedConnectionString, "Max Pool Size=100");
StringAssert.Contains(updatedConnectionString, "Connect Timeout=30");
}
public static SqlConnection GetConnectionString()
{
return(connectionBuilder.ConnectionString());
}
