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(); }