private void GetInsertData(DiscoveredServer server, DiscoveredDatabase database, ICheckNotifier checkNotifier)
{
var memoryRepository = new MemoryCatalogueRepository();
var sytnaxHelper = server.GetQuerySyntaxHelper();
string tableName = _tableInfo.Name;
string archiveTableName = sytnaxHelper.EnsureFullyQualified(database.GetRuntimeName(), _tableInfo.Schema, _tableInfo.GetRuntimeName() + "_Archive");
var whereStatement = "";
foreach (ColumnInfo pk in _pks)
{
whereStatement += string.Format("{0}.{1} = {2}.{1} AND ", tableName, pk.GetRuntimeName(), archiveTableName);
}
var qb = new QueryBuilder(null, null, new[] { _tableInfo });
qb.TopX = _batchSize;
qb.AddColumnRange(_tableInfo.ColumnInfos.Select(c => new ColumnInfoToIColumn(memoryRepository, c)).ToArray());
//where
var filter1 = new SpontaneouslyInventedFilter(memoryRepository, null, SpecialFieldNames.DataLoadRunID + " = " + _dataLoadRunID, "DataLoadRunID matches", null, null);
var filter2 =
new SpontaneouslyInventedFilter(memoryRepository, null,
string.Format(@" not exists (
select 1 from {0} where {1} {2} < {3}
)", archiveTableName, whereStatement, SpecialFieldNames.DataLoadRunID, _dataLoadRunID),
"Record doesn't exist in archive", null, null);
qb.RootFilterContainer = new SpontaneouslyInventedFilterContainer(memoryRepository, null, new [] { filter1, filter2 }, FilterContainerOperation.AND);
Inserts = new DataTable();
FillTableWithQueryIfUserConsents(Inserts, qb.SQL, checkNotifier, server);
}
protected override void SetUp()
{
base.SetUp();
var workingDir = new DirectoryInfo(TestContext.CurrentContext.TestDirectory);
parentDir = workingDir.CreateSubdirectory("FlatFileAttacherTests");
DirectoryInfo toCleanup = parentDir.GetDirectories().SingleOrDefault(d => d.Name.Equals("Test_CSV_Attachment"));
if (toCleanup != null)
{
toCleanup.Delete(true);
}
LoadDirectory = LoadDirectory.CreateDirectoryStructure(parentDir, "Test_CSV_Attachment");
// create a separate builder for setting an initial catalog on (need to figure out how best to stop child classes changing ServerICan... as this then causes TearDown to fail)
_database = GetCleanedServer(DatabaseType.MicrosoftSQLServer);
using (var con = _database.Server.GetConnection())
{
con.Open();
var cmdCreateTable = _database.Server.GetCommand("CREATE Table " + _database.GetRuntimeName() + "..Bob([name] [varchar](500),[name2] [varchar](500))", con);
cmdCreateTable.ExecuteNonQuery();
}
_table = _database.ExpectTable("Bob");
}
/// <inheritdoc/>
public virtual SortedDictionary <string, Patch> GetAllPatchesInAssembly(DiscoveredDatabase db)
{
var assembly = GetDbAssembly();
var subdirectory = ResourceSubdirectory;
Regex upgradePatchesRegexPattern;
if (string.IsNullOrWhiteSpace(subdirectory))
{
upgradePatchesRegexPattern = new Regex(@".*\.up\.(.*\.sql)");
}
else
{
upgradePatchesRegexPattern = new Regex(@".*\." + Regex.Escape(subdirectory) + @"\.up\.(.*\.sql)");
}
var files = new SortedDictionary <string, Patch>();
//get all resources out of
foreach (string manifestResourceName in assembly.GetManifestResourceNames())
{
var match = upgradePatchesRegexPattern.Match(manifestResourceName);
if (match.Success)
{
string fileContents = new StreamReader(assembly.GetManifestResourceStream(manifestResourceName)).ReadToEnd();
files.Add(match.Groups[1].Value, new Patch(match.Groups[1].Value, fileContents));
}
}
return(files);
}
private void SetupLowPrivilegeUserRightsFor(DiscoveredDatabase db, TestLowPrivilegePermissions permissions, ITableInfo ti)
{
var dbType = db.Server.DatabaseType;
//get access to the database using the current credentials
var username = TestDatabaseSettings.GetLowPrivilegeUsername(dbType);
var password = TestDatabaseSettings.GetLowPrivilegePassword(dbType);
if (string.IsNullOrWhiteSpace(username) || string.IsNullOrWhiteSpace(password))
{
Assert.Inconclusive();
}
//give the user access to the table
var sql = GrantAccessSql(username, dbType, permissions);
using (var con = db.Server.GetConnection())
UsefulStuff.ExecuteBatchNonQuery(sql, con);
if (ti != null)
{
//remove any existing credentials
foreach (DataAccessCredentials cred in CatalogueRepository.GetAllObjects <DataAccessCredentials>())
{
CatalogueRepository.TableInfoCredentialsManager.BreakAllLinksBetween(cred, ti);
}
//set the new ones
DataAccessCredentialsFactory credentialsFactory = new DataAccessCredentialsFactory(CatalogueRepository);
credentialsFactory.Create(ti, username, password, DataAccessContext.Any);
}
}
public CreateNewCohortDatabaseWizard(DiscoveredDatabase targetDatabase, ICatalogueRepository catalogueRepository, IDataExportRepository dataExportRepository, bool allowNullReleaseIdentifiers)
{
AllowNullReleaseIdentifiers = allowNullReleaseIdentifiers;
_catalogueRepository = catalogueRepository;
_dataExportRepository = dataExportRepository;
_targetDatabase = targetDatabase;
}
private void AddFilter(DiscoveredDatabase db, string filterName, string whereSqlFirstHalf, AggregateConfiguration ac, bool useParameter, string paramName, string paramValue)
{
var container = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND);
ac.RootFilterContainer_ID = container.ID;
ac.SaveToDatabase();
//create a filter
var filter = new AggregateFilter(CatalogueRepository, filterName, container);
if (useParameter)
{
filter.WhereSQL = whereSqlFirstHalf + paramName;
var parameterSql = db.Server.GetQuerySyntaxHelper()
.GetParameterDeclaration(paramName, new DatabaseTypeRequest(typeof(DateTime)));
var parameter = filter.GetFilterFactory().CreateNewParameter(filter, parameterSql);
parameter.Value = paramValue;
parameter.SaveToDatabase();
}
else
{
filter.WhereSQL = whereSqlFirstHalf + paramValue;
}
filter.SaveToDatabase();
}
private void DropBadView(DiscoveredDatabase db, bool ignoreFailure)
{
using (var con = db.Server.GetConnection())
{
con.Open();
var cmd = db.Server.GetCommand($"DROP VIEW {GetBadTableName(db)}", con);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception)
{
if (!ignoreFailure)
{
throw;
}
Console.WriteLine("Drop view failed, this is expected, since FAnsi won't see this dodgy table name we can't drop it as normal before tests");
}
}
//the table that the view reads from
var abc = db.ExpectTable("ABC");
if (abc.Exists())
{
abc.Drop();
}
}
private void SetVersion(DiscoveredDatabase db, string name, string version)
{
var versionTable = db.ExpectTable(RoundhouseVersionTable, RoundhouseSchemaName);
versionTable.Truncate();
//repository_path version entry_date modified_date entered_by
//Patching 2.6.0.1 2018-02-05 08:26:54.000 2018-02-05 08:26:54.000 DUNDEE\TZNind
using (var con = db.Server.GetConnection())
{
con.Open();
var sql = "INSERT INTO " + versionTable.GetFullyQualifiedName() +
"(repository_path,version,entry_date,modified_date,entered_by) VALUES (@repository_path,@version,@entry_date,@modified_date,@entered_by)";
var cmd = db.Server.GetCommand(sql, con);
var dt = DateTime.Now;
db.Server.AddParameterWithValueToCommand("@repository_path", cmd, name);
db.Server.AddParameterWithValueToCommand("@version", cmd, version);
db.Server.AddParameterWithValueToCommand("@entry_date", cmd, dt);
db.Server.AddParameterWithValueToCommand("@modified_date", cmd, dt);
db.Server.AddParameterWithValueToCommand("@entered_by", cmd, Environment.UserName);
cmd.ExecuteNonQuery();
}
}
public void Initialize(DiscoveredDatabase dbInfo, LoadStage loadStage)
{
if (loadStage != LoadStage.AdjustStaging && loadStage != LoadStage.PostLoad)
{
throw new Exception(typeof(BackupDatabaseMutilation).Name + " can only be done in AdjustStaging or PostLoad (this minimises redundant backups that would otherwise be created while you attempt to fix RAW / constraint related load errors)");
}
}
public MigrationConfiguration(DiscoveredDatabase fromDatabaseInfo, LoadBubble fromBubble, LoadBubble toBubble, INameDatabasesAndTablesDuringLoads namer)
{
_fromDatabaseInfo = fromDatabaseInfo;
_fromBubble = fromBubble;
_toBubble = toBubble;
_namer = namer;
}
/// <summary>
/// Create a table with the given name. Set your columns in <see cref="ExplicitColumnDefinitions"/>
/// </summary>
public CreateTableArgs(DiscoveredDatabase database, string tableName, string schema)
{
Database = database;
TableName = tableName;
Schema = schema;
GuessSettings = new GuessSettingsFactory().Create();
}
public void TestBulkInsert_ExplicitDateTimeFormats(DatabaseType type)
{
DiscoveredDatabase db = GetTestDatabase(type);
DiscoveredTable tbl = db.CreateTable("MyDateTestTable",
new[]
{
new DatabaseColumnRequest("MyDate", new DatabaseTypeRequest(typeof(DateTime)))
{
AllowNulls = false
},
});
//There are no rows in the table yet
Assert.AreEqual(0, tbl.GetRowCount());
using (var dt = new DataTable())
{
dt.Columns.Add("MyDate");
dt.Rows.Add("20011230");
using (IBulkCopy bulk = tbl.BeginBulkInsert())
{
bulk.Timeout = 30;
bulk.DateTimeDecider.Settings.ExplicitDateFormats = new [] { "yyyyMMdd" };
bulk.Upload(dt);
}
}
var dtDown = tbl.GetDataTable();
Assert.AreEqual(new DateTime(2001, 12, 30), dtDown.Rows[0]["MyDate"]);
}
public MigrationHost(DiscoveredDatabase sourceDbInfo, DiscoveredDatabase destinationDbInfo, MigrationConfiguration migrationConfig, HICDatabaseConfiguration databaseConfiguration)
{
_sourceDbInfo = sourceDbInfo;
_destinationDbInfo = destinationDbInfo;
_migrationConfig = migrationConfig;
_databaseConfiguration = databaseConfiguration;
}
public override void DropDatabase(DiscoveredDatabase database)
{
var master = database.Server.ExpectDatabase("postgres");
NpgsqlConnection.ClearAllPools();
using (var con = (NpgsqlConnection)master.Server.GetConnection())
{
con.Open();
// https://dba.stackexchange.com/a/11895
using (var cmd = new NpgsqlCommand($"UPDATE pg_database SET datallowconn = 'false' WHERE datname = '{database.GetRuntimeName()}';", con))
cmd.ExecuteNonQuery();
using (var cmd = new NpgsqlCommand($@"SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = '{database.GetRuntimeName()}';"
, con))
cmd.ExecuteNonQuery();
using (var cmd = new NpgsqlCommand("DROP DATABASE \"" + database.GetRuntimeName() + "\"", con))
cmd.ExecuteNonQuery();
}
NpgsqlConnection.ClearAllPools();
}
public void Test_GetManagedConnection_OngoingTransaction(DatabaseType dbType)
{
DiscoveredDatabase db = GetTestDatabase(dbType);
IManagedConnection ongoingCon;
//pretend that there is an ongoing transaction already
using (ongoingCon = db.Server.BeginNewTransactedConnection())
{
IManagedTransaction ongoingTrans = ongoingCon.ManagedTransaction;
//BeginNewTransactedConnection should open itself
Assert.AreEqual(ConnectionState.Open, ongoingCon.Connection.State);
Assert.IsNotNull(ongoingTrans);
//a managed connection with an ongoing transaction
IManagedConnection con;
using (con = db.Server.GetManagedConnection(ongoingTrans))
{
//still open
Assert.AreEqual(ConnectionState.Open, con.Connection.State);
Assert.IsTrue(con.Connection == ongoingCon.Connection); //same underlying connection
}
//it should still be open after this finally block
Assert.AreEqual(ConnectionState.Open, con.Connection.State);
}
//this is the using on the transaction this one should now close itself
Assert.AreEqual(ConnectionState.Closed, ongoingCon.Connection.State);
}
public static void ShowIfRequired(DiscoveredDatabase database, ITableRepository repository, IPatcher patcher)
{
if (Patch.IsPatchingRequired(database, patcher, out _, out _, out _) == Patch.PatchingState.Required)
{
new PatchingUI(database, repository, patcher).ShowDialog();
}
}
/// <summary>
/// Creates a new patient index table based on Biochemistry which selects the distinct dates of "NA" test results
/// for every patient
/// </summary>
/// <param name="db"></param>
/// <param name="people"></param>
/// <param name="r"></param>
/// <param name="cic"></param>
/// <returns></returns>
private JoinableCohortAggregateConfiguration SetupPatientIndexTable(DiscoveredDatabase db, PersonCollection people, Random r, CohortIdentificationConfiguration cic)
{
var syntax = db.Server.GetQuerySyntaxHelper();
var tbl = CreateDataset <Biochemistry>(db, people, 10000, r);
var cata = Import(tbl, out _, out _, out _, out ExtractionInformation[] eis);
var chi = eis.Single(ei => ei.GetRuntimeName().Equals("chi", StringComparison.CurrentCultureIgnoreCase));
var code = eis.Single(ei => ei.GetRuntimeName().Equals("TestCode", StringComparison.CurrentCultureIgnoreCase));
var date = eis.Single(ei => ei.GetRuntimeName().Equals("SampleDate", StringComparison.CurrentCultureIgnoreCase));
chi.IsExtractionIdentifier = true;
chi.SaveToDatabase();
var ac = new AggregateConfiguration(CatalogueRepository, cata, "NA by date");
ac.AddDimension(chi);
ac.AddDimension(code);
ac.AddDimension(date);
ac.CountSQL = null;
cic.EnsureNamingConvention(ac);
var and = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND);
var filter = new AggregateFilter(CatalogueRepository, "TestCode is NA", and);
filter.WhereSQL = syntax.EnsureWrapped("TestCode") + " = 'NA'";
filter.SaveToDatabase();
ac.RootFilterContainer_ID = and.ID;
ac.SaveToDatabase();
return(new JoinableCohortAggregateConfiguration(CatalogueRepository, cic, ac));
}
private PatchingUI(DiscoveredDatabase database, ITableRepository repository, IPatcher patcher)
{
_database = database;
_repository = repository;
_patcher = patcher;
InitializeComponent();
if (LicenseManager.UsageMode == LicenseUsageMode.Designtime)
{
return;
}
string name = patcher.Name + " v" + patcher.GetDbAssembly().GetName().Version.ToString(3);
tbPatch.Text = $"{name}";
if (_database == null)
{
tbDatabase.Text = "Form loaded without a specific database to target!";
tbDatabase.ForeColor = Color.Red;
}
else
{
tbDatabase.Text = string.Format("{0}, Version:{1}", _database.GetRuntimeName(), repository.GetVersion());
}
}
public void Create(DiscoveredDatabase databaseICanCreateRandomTablesIn, ICatalogueRepository catalogueRepository)
{
CreateFunctionSQL = @"
if exists (select 1 from sys.objects where name = 'MyAwesomeFunction')
drop function MyAwesomeFunction
GO
CREATE FUNCTION MyAwesomeFunction
(
-- Add the parameters for the function here
@startNumber int ,
@stopNumber int,
@name varchar(50)
)
RETURNS
@ReturnTable TABLE
(
-- Add the column definitions for the TABLE variable here
Number int,
Name varchar(50)
)
AS
BEGIN
-- Fill the table variable with the rows for your result set
DECLARE @i int;
set @i = @startNumber
while(@i < @stopNumber)
begin
INSERT INTO @ReturnTable(Name,Number) VALUES (@name,@i);
set @i = @i + 1;
end
RETURN
END
";
using (var con = databaseICanCreateRandomTablesIn.Server.GetConnection())
{
con.Open();
UsefulStuff.ExecuteBatchNonQuery(CreateFunctionSQL, con);
}
var tbl = databaseICanCreateRandomTablesIn.ExpectTableValuedFunction("MyAwesomeFunction");
TableValuedFunctionImporter importer = new TableValuedFunctionImporter(catalogueRepository, tbl);
importer.DoImport(out TableInfoCreated, out ColumnInfosCreated);
importer.ParametersCreated[0].Value = "5";
importer.ParametersCreated[0].SaveToDatabase();
importer.ParametersCreated[1].Value = "10";
importer.ParametersCreated[1].SaveToDatabase();
importer.ParametersCreated[2].Value = "'fish'";
importer.ParametersCreated[2].SaveToDatabase();
ForwardEngineerCatalogue forwardEngineerCatalogue = new ForwardEngineerCatalogue(TableInfoCreated, ColumnInfosCreated, true);
forwardEngineerCatalogue.ExecuteForwardEngineering(out Cata, out CataItems, out ExtractionInformations);
}
public BackfillSqlHelper(ColumnInfo timePeriodicityField, DiscoveredDatabase stagingDbInfo, DiscoveredDatabase liveDbInfo)
{
_timePeriodicityField = timePeriodicityField;
_stagingDbInfo = stagingDbInfo;
_liveDbInfo = liveDbInfo;
_tiWithTimeColumn = _timePeriodicityField.TableInfo;
}
private void SetupCohort(out DiscoveredDatabase db, out CohortIdentificationConfiguration cic, out DataTable dt)
{
dt = new DataTable();
dt.Columns.Add("PK");
//add lots of rows
for (int i = 0; i < 100000; i++)
{
dt.Rows.Add(i);
}
db = GetCleanedServer(DatabaseType.MicrosoftSQLServer, true);
var tbl = db.CreateTable("CohortCompilerRunnerTestsTable", dt);
var cata = Import(tbl);
var ei = cata.CatalogueItems[0].ExtractionInformation;
ei.IsExtractionIdentifier = true;
ei.SaveToDatabase();
var agg = new AggregateConfiguration(CatalogueRepository, cata, "MyAgg");
agg.CountSQL = null;
agg.SaveToDatabase();
var dimension = new AggregateDimension(CatalogueRepository, ei, agg);
cic = new CohortIdentificationConfiguration(CatalogueRepository, "MyCic");
cic.CreateRootContainerIfNotExists();
cic.RootCohortAggregateContainer.AddChild(agg, 0);
}
protected override void SetUp()
{
base.SetUp();
_database = GetCleanedServer(FAnsi.DatabaseType.MicrosoftSQLServer);
_function.Create(_database, CatalogueRepository);
}
private PatchingUI(DiscoveredDatabase database, ITableRepository repository, Version databaseVersion, IPatcher patcher, Patch[] patchesInDatabase, SortedDictionary <string, Patch> allPatchesInAssembly)
{
_database = database;
_repository = repository;
_databaseVersion = databaseVersion;
_patcher = patcher;
InitializeComponent();
if (LicenseManager.UsageMode == LicenseUsageMode.Designtime)
{
return;
}
_hostAssemblyVersion = new Version(FileVersionInfo.GetVersionInfo(_patcher.GetDbAssembly().Location).FileVersion);
_patchesInDatabase = patchesInDatabase;
_allPatchesInAssembly = allPatchesInAssembly;
string name = patcher.Name + " v" + patcher.GetDbAssembly().GetName().Version.ToString(3);
int numberOfPatchesToApply = _allPatchesInAssembly.Values.Except(_patchesInDatabase).Count();
tbPatch.Text = $"{name} ({numberOfPatchesToApply} Patch{(numberOfPatchesToApply > 1 ? "es":"")})";
if (_database == null)
{
tbDatabase.Text = "Form loaded without a specific database to target!";
tbDatabase.ForeColor = Color.Red;
}
else
{
tbDatabase.Text = string.Format("{0}, Version:{1}", _database.GetRuntimeName(), repository.GetVersion());
}
}
protected void DeleteTables(DiscoveredDatabase database)
{
var tables = new RelationshipTopologicalSort(database.DiscoverTables(true));
foreach (var t in tables.Order.Reverse())
{
try
{
t.Drop();
}
catch (Exception ex)
{
throw new Exception($"Failed to drop table '{t.GetFullyQualifiedName()} during cleanup", ex);
}
}
foreach (var t in database.DiscoverTableValuedFunctions())
{
try
{
t.Drop();
}
catch (Exception ex)
{
throw new Exception($"Failed to drop table '{t.GetFullyQualifiedName()} during cleanup", ex);
}
}
}
internal ArchivalDataLoadInfo(DbDataReader r, DiscoveredDatabase loggingDatabase)
{
_loggingDatabase = loggingDatabase;
ID = Convert.ToInt32(r["ID"]);
DataLoadTaskID = Convert.ToInt32(r["dataLoadTaskID"]);
//populate basic facts from the table
StartTime = (DateTime)r["startTime"];
if (r["endTime"] == null || r["endTime"] == DBNull.Value)
{
EndTime = null;
}
else
{
EndTime = Convert.ToDateTime(r["endTime"]);
}
Description = r["description"] as string;
HasErrors = r["hasErrors"] != DBNull.Value;
_knownTableInfos = new Lazy <List <ArchivalTableLoadInfo> >(GetTableInfos);
_knownErrors = new Lazy <List <ArchivalFatalError> >(GetErrors);
_knownProgress = new Lazy <List <ArchivalProgressLog> >(GetProgress);
}
public override void DropDatabase(DiscoveredDatabase database)
{
bool userIsCurrentlyInDatabase = database.Server.GetCurrentDatabase().GetRuntimeName().Equals(database.GetRuntimeName());
var serverConnectionBuilder = new SqlConnectionStringBuilder(database.Server.Builder.ConnectionString);
if (userIsCurrentlyInDatabase)
{
serverConnectionBuilder.InitialCatalog = "master";
}
// Create a new server so we don't mutate database.Server and cause a whole lot of side-effects in other code, e.g. attachers
var server = new DiscoveredServer(serverConnectionBuilder);
var databaseToDrop = database.GetRuntimeName();
string sql = "ALTER DATABASE [" + databaseToDrop + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE" + Environment.NewLine;
sql += "DROP DATABASE [" + databaseToDrop + "]";
using (var con = (SqlConnection)server.GetConnection())
{
con.Open();
using (var cmd = new SqlCommand(sql, con))
cmd.ExecuteNonQuery();
}
SqlConnection.ClearAllPools();
}
public ExitCodeType Execute(string commandText, DiscoveredDatabase db)
{
var syntax = db.Server.GetQuerySyntaxHelper();
commandText = _regexEntity.Replace(commandText, m => GetEntityForMatch(m, syntax));
try
{
Dictionary <int, Stopwatch> performance = new Dictionary <int, Stopwatch>();
using (var con = db.Server.GetConnection())
{
con.Open();
UsefulStuff.ExecuteBatchNonQuery(commandText, con, null, out performance, 600000);
}
foreach (KeyValuePair <int, Stopwatch> section in performance)
{
_job.OnNotify(this,
new NotifyEventArgs(ProgressEventType.Information,
"Batch ending on line \"" + section.Key + "\" finished after " + section.Value.Elapsed));
}
}
catch (Exception e)
{
throw new Exception("Failed to execute the query: " + e);
}
return(ExitCodeType.Success);
}
public ArchivalTableLoadInfo(ArchivalDataLoadInfo parent, DbDataReader r, DiscoveredDatabase loggingDatabase)
{
Parent = parent;
_loggingDatabase = loggingDatabase;
ID = Convert.ToInt32(r["ID"]);
Start = (DateTime)r["startTime"];
var e = r["endTime"];
if (e == null || e == DBNull.Value)
{
End = null;
}
else
{
End = (DateTime)e;
}
TargetTable = (string)r["targetTable"];
Inserts = ToNullableInt(r["inserts"]);
Updates = ToNullableInt(r["updates"]);
Deletes = ToNullableInt(r["deletes"]);
Notes = r["notes"] as string;
_knownDataSource = new Lazy <List <ArchivalDataSource> >(GetDataSources);
}
public void SetUp(DiscoveredServer server)
{
_server = server;
var databaseToLoadName = "HICPipelineTests";
// Create the databases
server.ExpectDatabase(databaseToLoadName).Create(true);
server.ChangeDatabase(databaseToLoadName);
// Create the dataset table
DatabaseToLoad = server.ExpectDatabase(databaseToLoadName);
using (var con = DatabaseToLoad.Server.GetConnection())
{
con.Open();
const string createDatasetTableQuery =
"CREATE TABLE TestData ([Col1] [int], [hic_dataLoadRunID] [int] NULL, [hic_validFrom] [datetime] NULL, CONSTRAINT [PK_TestData] PRIMARY KEY CLUSTERED ([Col1] ASC))";
const string addValidFromDefault =
"ALTER TABLE TestData ADD CONSTRAINT [DF_TestData__hic_validFrom] DEFAULT (getdate()) FOR [hic_validFrom]";
using (var cmd = DatabaseCommandHelper.GetCommand(createDatasetTableQuery, con))
cmd.ExecuteNonQuery();
using (var cmd = DatabaseCommandHelper.GetCommand(addValidFromDefault, con))
cmd.ExecuteNonQuery();
}
// Ensure the dataset table has been created
var datasetTable = DatabaseToLoad.ExpectTable("TestData");
Assert.IsTrue(datasetTable.Exists());
}
public override IEnumerable <DiscoveredTable> ListTables(DiscoveredDatabase parent, IQuerySyntaxHelper querySyntaxHelper, DbConnection connection, string database, bool includeViews, DbTransaction transaction = null)
{
List <DiscoveredTable> tables = new List <DiscoveredTable>();
//find all the tables
using (var cmd = new OracleCommand("SELECT table_name FROM all_tables where owner='" + database + "'", (OracleConnection)connection))
{
cmd.Transaction = transaction as OracleTransaction;
var r = cmd.ExecuteReader();
while (r.Read())
{
tables.Add(new DiscoveredTable(parent, r["table_name"].ToString(), querySyntaxHelper));
}
}
//find all the views
if (includeViews)
{
using (var cmd = new OracleCommand("SELECT view_name FROM all_views where owner='" + database + "'", (OracleConnection)connection))
{
cmd.Transaction = transaction as OracleTransaction;
var r = cmd.ExecuteReader();
while (r.Read())
{
tables.Add(new DiscoveredTable(parent, r["view_name"].ToString(), querySyntaxHelper, null, TableType.View));
}
}
}
return(tables.ToArray());
}
