static void InitSettings()
{
//SqlFormatter = new InlineFormatter();
SqlFormatter = new SqlServerFormatter();
// for normal usage, this will return a System.Diagnostics.Stopwatch to collect times - unit tests can explicitly set how much time elapses
StopwatchProvider = StopwatchWrapper.StartNew;
}
public void OnError(IDbCommand profiledDbCommand, SqlExecuteType executeType, Exception exception)
{
var formatter = new SqlServerFormatter();
exception.Data["SQL"] = formatter.FormatSql(profiledDbCommand.CommandText, SqlTiming.GetCommandParameters(profiledDbCommand));
_wrapped.OnError(profiledDbCommand, executeType, exception);
}
private void LogCommandAsError(Exception exception, ExecuteType type)
{
var formatter = new SqlServerFormatter();
SqlTiming timing = new SqlTiming(this, type, null);
exception.Data["SQL"] = formatter.FormatSql(timing);
}
public string FormatSql(SqlTiming timing)
{
var sqlFormatter = new SqlServerFormatter();
var sqlFormat = sqlFormatter.FormatSql(timing);
var poorMansFormatter = new TSqlStandardFormatter();
var fullFormatter = new SqlFormattingManager(poorMansFormatter);
return(fullFormatter.Format(sqlFormat));
}
public void TabelQueryWithoutParameters()
{
const string text = "select 1";
var cmd = CreateDbCommand(CommandType.Text, text);
var formatter = new SqlServerFormatter();
var actualOutput = GenerateOutput(formatter, cmd, text);
const string expectedOutput = "select 1;";
Assert.Equal(expectedOutput, actualOutput);
}
public void TableQueryWithOneParameter(string at)
{
const string text = "select 1 from dbo.Table where x = @a";
var cmd = CreateDbCommand(CommandType.Text, text);
AddDbParameter <int>(cmd, at + "a", 123);
var formatter = new SqlServerFormatter();
var actualOutput = GenerateOutput(formatter, cmd, text);
const string expectedOutput = "DECLARE @a int = 123;\n\nselect 1 from dbo.Table where x = @a;";
Assert.Equal(expectedOutput, actualOutput);
}
public void TableQueryWithVarchar(string at)
{
const string text = "select 1 from dbo.Table where x = @x, y = @y";
var cmd = CreateDbCommand(CommandType.Text, text);
AddDbParameter <string>(cmd, at + "x", "bob", size: 20, type: DbType.AnsiString);
AddDbParameter <string>(cmd, at + "y", "bob2", size: -1, type: DbType.AnsiString);
var formatter = new SqlServerFormatter();
var actualOutput = GenerateOutput(formatter, cmd, text);
const string expectedOutput = "DECLARE @x varchar(20) = 'bob',\n @y varchar(max) = 'bob2';\n\nselect 1 from dbo.Table where x = @x, y = @y;";
Assert.Equal(expectedOutput, actualOutput);
}
public void TableQueryWithDecimalNullable(string at)
{
const string text = "select 1 from dbo.Table where x = @x, y = @y";
var cmd = CreateDbCommand(CommandType.Text, text);
AddDbParameter <decimal?>(cmd, at + "x", 123.45);
AddDbParameter <decimal?>(cmd, at + "y", null);
var formatter = new SqlServerFormatter();
var actualOutput = GenerateOutput(formatter, cmd, text);
const string expectedOutput = "DECLARE @x decimal(5,2) = 123.45,\n @y decimal = null;\n\nselect 1 from dbo.Table where x = @x, y = @y;";
Assert.Equal(expectedOutput, actualOutput);
}
public void TableQueryWithDecimalZeroPrecision(string at)
{
const string text = "select 1 from dbo.Table where x = @x, y = @y";
var cmd = CreateDbCommand(CommandType.Text, text);
AddDbParameter <decimal>(cmd, at + "x", 12345.0, type: DbType.Decimal);
AddDbParameter <decimal>(cmd, at + "y", -54321.0, type: DbType.Decimal);
var formatter = new SqlServerFormatter();
var actualOutput = GenerateOutput(formatter, cmd, text);
const string expectedOutput = "DECLARE @x decimal(5,0) = 12345,\n @y decimal(5,0) = -54321;\n\nselect 1 from dbo.Table where x = @x, y = @y;";
Assert.Equal(expectedOutput, actualOutput);
}
public void TableQueryWithXml(string at)
{
const string text = "select 1 from dbo.Table where x = @x, y = @y";
var cmd = CreateDbCommand(CommandType.Text, text);
AddDbParameter <string>(cmd, at + "x", "<root></root>", type: DbType.Xml);
AddDbParameter <string>(cmd, at + "y", "<root><node/></root>", type: DbType.Xml);
var formatter = new SqlServerFormatter();
var actualOutput = GenerateOutput(formatter, cmd, text);
const string expectedOutput = "DECLARE @x xml = '<root></root>',\n @y xml = '<root><node/></root>';\n\nselect 1 from dbo.Table where x = @x, y = @y;";
Assert.Equal(expectedOutput, actualOutput);
}
public void TableQueryWithBit(string at)
{
const string text = "select 1 from dbo.Table where x = @x, y = @y";
var cmd = CreateDbCommand(CommandType.Text, text);
AddDbParameter <bool?>(cmd, at + "x", true, type: DbType.Boolean);
AddDbParameter <bool?>(cmd, at + "y", null, type: DbType.Boolean);
var formatter = new SqlServerFormatter();
var actualOutput = GenerateOutput(formatter, cmd, text);
const string expectedOutput = "DECLARE @x bit = 1,\n @y bit = null;\n\nselect 1 from dbo.Table where x = @x, y = @y;";
Assert.Equal(expectedOutput, actualOutput);
}
public void TableQueryWithTime(string at)
{
const string text = "select 1 from dbo.Table where x = @x, y = @y";
var cmd = CreateDbCommand(CommandType.Text, text);
AddDbParameter <DateTime>(cmd, at + "x", new DateTime(2017, 1, 30, 5, 13, 21), type: DbType.Time);
AddDbParameter <DateTime>(cmd, at + "y", new DateTime(2001, 1, 1, 18, 12, 11), type: DbType.Time);
var formatter = new SqlServerFormatter();
var actualOutput = GenerateOutput(formatter, cmd, text);
const string expectedOutput = "DECLARE @x datetime = '05:13:21',\n @y datetime = '18:12:11';\n\nselect 1 from dbo.Table where x = @x, y = @y;";
Assert.NotEqual(expectedOutput, actualOutput); // Auto-translation of DbType.Time to DbType.DateTime breaks output
}
public void StoredProcedureCallWithoutParameters()
{
// arrange
_formatter = new VerboseSqlServerFormatter();
_commandText = "dbo.SOMEPROCEDURE";
const string expectedOutput = "EXEC dbo.SOMEPROCEDURE;";
CreateDbCommand(CommandType.StoredProcedure);
// act
var actualOutput = GenerateOutput();
// assert
Assert.Equal(expectedOutput, actualOutput);
}
public void TableQueryWithDateTimeOffset(string at)
{
const string text = "select 1 from dbo.Table where x = @x, y = @y";
var cmd = CreateDbCommand(CommandType.Text, text);
AddDbParameter <DateTimeOffset>(cmd, at + "x", new DateTimeOffset(2017, 1, 30, 5, 13, 21, TimeSpan.FromHours(4.5)), type: DbType.DateTimeOffset);
AddDbParameter <DateTimeOffset>(cmd, at + "y", new DateTimeOffset(2001, 1, 1, 18, 12, 11, TimeSpan.FromHours(-4.5)), type: DbType.DateTimeOffset);
var formatter = new SqlServerFormatter();
var actualOutput = GenerateOutput(formatter, cmd, text);
const string expectedOutput = "DECLARE @x datetimeoffset = '2017-01-30T05:13:21+04:30',\n @y datetimeoffset = '2001-01-01T18:12:11-04:30';\n\nselect 1 from dbo.Table where x = @x, y = @y;";
Assert.Equal(expectedOutput, actualOutput);
}
public void TableQueryWithCurrency(string at)
{
const string text = "select 1 from dbo.Table where x = @x, y = @y";
var cmd = CreateDbCommand(CommandType.Text, text);
AddDbParameter <decimal>(cmd, at + "x", 123.45, type: DbType.Currency);
AddDbParameter <decimal>(cmd, at + "y", -54.321, type: DbType.Currency);
var formatter = new SqlServerFormatter();
var actualOutput = GenerateOutput(formatter, cmd, text);
const string expectedOutput = "DECLARE @x money = 123.45,\n @y money = -54.321;\n\nselect 1 from dbo.Table where x = @x, y = @y;";
Assert.Equal(expectedOutput, actualOutput);
}
public void EnsureVerboseSqlServerFormatterOnlyAddsInformation()
{
// arrange
// overwrite the formatter
_formatter = new VerboseSqlServerFormatter(true);
_commandText = "select 1";
const string expectedOutput = "-- Command Type: Text\r\n-- Database: TestDatabase\r\n\r\nselect 1;";
CreateDbCommand(CommandType.Text);
// act
var actualOutput = GenerateOutput();
// assert
Assert.Equal(expectedOutput, actualOutput);
}
public void StoredProcedureCallWithOneParameter(string at)
{
// arrange
_formatter = new VerboseSqlServerFormatter();
_commandText = "dbo.SOMEPROCEDURE";
const string expectedOutput = "DECLARE @x int = 123;\r\n\r\nEXEC dbo.SOMEPROCEDURE @x = @x;";
CreateDbCommand(CommandType.StoredProcedure);
AddDbParameter <int>(at + "x", 123, ParameterDirection.Input);
// act
var actualOutput = GenerateOutput();
// assert
Assert.Equal(expectedOutput, actualOutput);
}
public void StoredProcedureCallWithOneReturnParameter(string at)
{
// arrange
_formatter = new VerboseSqlServerFormatter();
_commandText = "dbo.SOMEPROCEDURE";
const string expectedOutput = "DECLARE @retval int;\r\n\r\nEXEC @retval = dbo.SOMEPROCEDURE;\r\nSELECT @retval AS ReturnValue;";
CreateDbCommand(CommandType.StoredProcedure);
AddDbParameter <int>(at + "retval", null, ParameterDirection.ReturnValue);
// act
var actualOutput = GenerateOutput();
// assert
Assert.Equal(expectedOutput, actualOutput);
}
static async Task TestSomeCalls()
{
Console.WriteLine(@"Enter Servername to test(i.e. mydbserver\sqlexpress):");
string readServerName = Console.ReadLine();
Console.WriteLine("Enter Username:"******"Enter Password:"******"localhost\sqlexpress" : readServerName;
string login = String.IsNullOrWhiteSpace(readServerName) ? "sa" : readUsername;
string password = String.IsNullOrWhiteSpace(readPassword) ? "my_sa_passworx" : readPassword;
DbOptions dbOption = new DbOptions()
{
DbHostname = server,
DbName = "AssetWorx",
DbUsername = login,
DbPassword = password,
DbType = "Sql Server"
};
SqlServerFormatter sqlServerFormatter = new SqlServerFormatter(dbOption);
LocationRepository locationRepo = new LocationRepository(dbOption, sqlServerFormatter);
ApplicationSettingRepository appSettingsRepo = new ApplicationSettingRepository(dbOption, sqlServerFormatter);
LocationHistoryRepository locationHistoryRepo = new LocationHistoryRepository(dbOption, sqlServerFormatter);
EventRepository eventRepo = new EventRepository(dbOption, appSettingsRepo, sqlServerFormatter);
MapAreaRepository mapAreaRepo = new MapAreaRepository(dbOption, sqlServerFormatter);
AssetRepository assetRepo = new AssetRepository(dbOption, locationRepo, locationHistoryRepo, eventRepo,
appSettingsRepo, mapAreaRepo, sqlServerFormatter);
List <Asset> assets = await assetRepo.GetAll(null, null, null);
Console.WriteLine("***************ALL ASSETS**************");
foreach (Asset asset in assets)
{
Console.WriteLine(string.Format("Got asset {0}", asset.Name));
}
assets = await assetRepo.GetChangedAssets(DateTimeOffset.Now.AddDays(-10));
Console.WriteLine("***************CHANGED ASSETS**************");
foreach (Asset asset in assets)
{
Console.WriteLine(string.Format("Got changed asset {0}", asset.Name));
}
Console.ReadLine();
}
public void StoredProcedureCallWithInOutputParameter(string at)
{
// arrange
_formatter = new VerboseSqlServerFormatter();
_commandText = "dbo.SOMEPROCEDURE";
const string expectedOutput = "DECLARE @x int = 123;\r\n\r\nEXEC dbo.SOMEPROCEDURE @x = @x OUTPUT;\r\nSELECT @x AS x;";
CreateDbCommand(CommandType.StoredProcedure);
// note: since the sql-OUTPUT parameters can be read within the procedure, we need to support setting the value
AddDbParameter <int>(at + "x", 123, ParameterDirection.InputOutput);
// act
var actualOutput = GenerateOutput();
// assert
Assert.Equal(expectedOutput, actualOutput);
}
public string FormatSql(string commandText, List <SqlTimingParameter> parameters)
{
var sqlFormatter = new SqlServerFormatter();
string sqlFormat;
try
{
sqlFormat = sqlFormatter.GetFormattedSql(commandText, parameters);
}
catch (IndexOutOfRangeException)
{
return(string.Format("Could not format SQL: {0} params {1}", commandText, parameters));
}
var poorMansFormatter = new TSqlStandardFormatter();
var fullFormatter = new SqlFormattingManager(poorMansFormatter);
return(fullFormatter.Format(sqlFormat));
}
public void StoredProcedureCallWithOneOutputParameterAndOneReturnParameter([Values(None, At)] string at)
{
// arrange
_formatter = new VerboseSqlServerFormatter();
_commandText = "dbo.SOMEPROCEDURE";
const string expectedOutput = "DECLARE @x int = 123,\r\n @retval int;\r\n\r\nEXEC @retval = dbo.SOMEPROCEDURE @x = @x OUTPUT;\r\nSELECT @retval AS ReturnValue, @x AS x;";
CreateDbCommand(CommandType.StoredProcedure);
// note: since the sql-OUTPUT parameters can be read within the procedure, we need to support setting the value
AddDbParameter <int>(at + "x", 123, ParameterDirection.Output);
AddDbParameter <int>(at + "retval", null, ParameterDirection.ReturnValue);
// act
var actualOutput = GenerateOutput();
// assert
Assert.AreEqual(expectedOutput, actualOutput);
}
public void TableQueryWithTwoParametersDisabled(string at)
{
const string text = "select 1 from dbo.Table where x = @x, y = @y";
var cmd = CreateDbCommand(CommandType.Text, text);
AddDbParameter <int>(cmd, at + "x", 123);
AddDbParameter <long>(cmd, at + "y", 123);
var formatter = new SqlServerFormatter()
{
IncludeParameterValues = false
};
var actualOutput = GenerateOutput(formatter, cmd, text);
const string expectedOutput = "DECLARE @x int,\n @y bigint;\n\nselect 1 from dbo.Table where x = @x, y = @y;";
Assert.Equal(expectedOutput, actualOutput);
}
public void VerboseSqlServerFormatterAddsTransactionInformation()
{
// note: since we don't have an active sql connection we cannot test the transactions coupled to a connection
// the only thing we can do is test the TransactionScope transaction
// arrange
// overwrite the formatter
_formatter = new VerboseSqlServerFormatter(true);
_commandText = "select 1";
const string expectedOutput = "-- Command Type: Text\r\n-- Database: TestDatabase\r\n-- Transaction Scope Iso Level: Serializable\r\n\r\nselect 1;";
CreateDbCommand(CommandType.Text);
TransactionScope transactionScope = new TransactionScope();
// act
var actualOutput = GenerateOutput();
transactionScope.Dispose();
// assert
Assert.AreEqual(expectedOutput, actualOutput);
}
private string GenerateOutput(SqlServerFormatter _formatter, SqlCommand _dbCommand, string _commandText)
{
var sqlParameters = _dbCommand.GetParameters();
return(_formatter.GetFormattedSql(_commandText, sqlParameters, _dbCommand));
}
public void TestSetup()
{
_formatter = new SqlServerFormatter();
}
public SqlFormatterTests()
{
CreateDbTypeMap();
_formatter = new SqlServerFormatter();
}
