public static SqlConnection CreateAndOpenSqlConnection()
{
var db = new ProjectFirmaSqlDatabase();
var sqlConnection = db.CreateConnection();
sqlConnection.Open();
return(sqlConnection);
}
public void TestFixtureSetup()
{
lock (_setupLockObject)
{
if (!_projectFirmaDatabaseIsSetUp)
{
_db = new ProjectFirmaSqlDatabase();
_projectFirmaDatabaseIsSetUp = true;
}
}
}
public static void DoPublishingSql()
{
try
{
var sqlDatabaseConnectionString = FirmaWebConfiguration.DatabaseConnectionString;
var sqlQueryOne = $"dbo.pImportLoaTabularData";
using (var command = new SqlCommand(sqlQueryOne))
{
var sqlConnection = new SqlConnection(sqlDatabaseConnectionString);
using (var conn = sqlConnection)
{
command.Connection = conn;
command.CommandTimeout = 400;
ProjectFirmaSqlDatabase.ExecuteSqlCommand(command);
}
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw new SitkaDisplayErrorException($"Problem calling SQL: {e.Message}");
}
}
public void TestFixtureSetup()
{
_db = new ProjectFirmaSqlDatabase();
}
public void UrlsInTextColumnsInDatabaseMatchDatabaseRecords()
{
//TODO: Not sure if ProjectUpdateBatch should be in the exception list, but not clear how to correct data that has already posted that is breaking this test.
const string findAnyColumnsThatCouldContainUrls = @"
select c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS c
join INFORMATION_SCHEMA.TABLES t on c.TABLE_CATALOG = t.TABLE_CATALOG and c.TABLE_SCHEMA = t.TABLE_SCHEMA and c.TABLE_NAME = t.TABLE_NAME
where c.DATA_TYPE in ('char','nvarchar','text','ntext','varchar')
and t.TABLE_TYPE = 'BASE TABLE' and t.TABLE_NAME not in ('AuditLog', 'FirmaPage', 'ProjectUpdateBatch')
";
List <string> sqlQueriesToGatherDataWithUrls;
using (var command = new SqlCommand(findAnyColumnsThatCouldContainUrls))
{
var sqlConnection = _db.CreateConnection();
using (var conn = sqlConnection)
{
command.Connection = conn;
using (var dt = ProjectFirmaSqlDatabase.ExecuteSqlCommand(command).Tables[0])
{
sqlQueriesToGatherDataWithUrls =
dt.Rows.Cast <DataRow>()
.Select(
x =>
String.Format(
"select '{0}' as TableCatalog, '{1}' as TableSchema, '{2}' as TableName, '{3}' as ColumnName, [{3}] as ColumnValue from [{0}].[{1}].[{2}] where [{3}] like '%{4}%' or [{3}] like '%{5}%'",
x["TABLE_CATALOG"],
x["TABLE_SCHEMA"],
x["TABLE_NAME"],
x["COLUMN_NAME"],
SitkaWebConfiguration.ApplicationDomain,
ControllerPartOfUri))
.ToList();
}
}
}
var dataFromRowsAndColumnsWithUrls = sqlQueriesToGatherDataWithUrls.AsParallel().SelectMany(query =>
{
List <ResultRow> thisColumnData;
using (var command = new SqlCommand(query))
{
var sqlConnection = _db.CreateConnection();
using (var conn = sqlConnection)
{
command.Connection = conn;
using (var dt = ProjectFirmaSqlDatabase.ExecuteSqlCommand(command).Tables[0])
{
thisColumnData =
dt.Rows.Cast <DataRow>()
.Select(x => new ResultRow((string)x["TableCatalog"], (string)x["TableSchema"], (string)x["TableName"], (string)x["ColumnName"], (string)x["ColumnValue"]))
.ToList();
}
}
}
return(thisColumnData);
}).ToList();
AssertThatAllReferencedFileResourceGuidsExist(dataFromRowsAndColumnsWithUrls);
AssertThatAllUrlsAreServerRootRelative(dataFromRowsAndColumnsWithUrls);
AssertThatAllUrlsDoNotContainAbsoluteUrlsToProdOrQa(dataFromRowsAndColumnsWithUrls);
}
