internal static rcadDataContext CreateDbContext(string connectionString)
{
var dc = new rcadDataContext(connectionString);
//if (!string.IsNullOrEmpty(ConfigurationManager.AppSettings["RcadDbLog"]))
dc.Log = Console.Out;
return(dc);
}
public int Load()
{
string dbConnectionString = _dbConnection.Connection.BuildConnectionString();
try
{
_dc = CreateDbContext(dbConnectionString);
// Retrieve all the TAXIDs we want. We essentially want all taxonomy IDs
// which are parented by the selected id. The easiest way to do this is with a
// CTE using SQL2005+. Unfortunately, LINQ doesn't support that so we drop to straight
// T-SQL for this.
const string sqlQuery = @"with cte as (select TaxID from Taxonomy where TaxID={0} " +
@"union all select t.TaxID from Taxonomy t join cte tp on t.ParentTaxID = tp.TaxID) " +
@"select * from cte";
var results = _dc
.ExecuteQuery <Taxonomy>(sqlQuery, _dbConnection.ParentTaxId)
.Select(tax => tax.TaxID).ToList();
// BUGBUG: if the # of sequences is > 2100 then we cannot generate the proper
// query here since T-SQL doesn't support that many parameters. So, here we'll group
// it in batches of 2000.
var queryGroups = results.Select((id, index) => new { GroupID = index / 2000, id }).GroupBy(x => x.GroupID);
// Now, grab all the sequences from this group of TaxIds.
// select distinct seqid from dbo.sequence where alnid = @alignment_id
//KJD (10/21/2009) - Change query to use vAlignmentGridUngapped which takes into account column indirection supported by the rCAD schema
//instead of querying the Sequence table directly
var allSequences = new List <int>();
foreach (var singleGroup in queryGroups)
{
var thisGroup = singleGroup;
var query = from seq in _dc.SequenceMains
where thisGroup.Select(x => x.id).Contains(seq.TaxID) &&
_dc.vAlignmentGridUngappeds.Where(s => ((s.SeqID == seq.SeqID) && (s.AlnID == _dbConnection.AlignmentId))).FirstOrDefault() != null &&
seq.TaxID > 0 &&
seq.SeqLength > 0 &&
(_dbConnection.LocationId == 0 || seq.LocationID == _dbConnection.LocationId) &&
(seq.SeqTypeID == _dbConnection.SequenceTypeId)
select seq.SeqID;
allSequences.AddRange(query.Cast <int>());
}
// Now get the sequence headers based on the above collection
queryGroups = allSequences.Distinct().Select((id, index) => new { GroupID = index / 1000, id }).GroupBy(x => x.GroupID);
_entities = new List <IAlignedBioEntity>();
// 2 minute timeout
_dc.CommandTimeout = 60 * 2 * 1000;
// Now execute the queries
//KJD (10/21/2009) - Change query to use vAlignmentGridUngapped which takes into account column indirection supported by the rCAD schema
//instead of querying the Sequence table directly
foreach (var singleGroup in queryGroups)
{
var thisGroup = singleGroup;
var query = from smd in _dc.SequenceMains
from tax in _dc.TaxonomyNamesOrdereds
let maxColumnNumber = _dc.vAlignmentGridUngappeds.Where(s => ((s.SeqID == smd.SeqID) && (s.AlnID == _dbConnection.AlignmentId))).Max(s => s.LogicalColumnNumber)
where thisGroup.Select(x => x.id).Contains(smd.SeqID) &&
tax.TaxID == smd.TaxID
select new DbAlignedBioEntity(_dc, dbConnectionString, smd.SeqID, _dbConnection.AlignmentId, maxColumnNumber)
{
ScientificName = tax.ScientificName,
TaxonomyId = tax.LineageName,
Validator = BioValidator.rRNAValidator
};
// Add the results to our list.
_entities.AddRange(query.Cast <IAlignedBioEntity>());
}
return(_entities.Count);
}
catch (Exception ex)
{
IErrorVisualizer errorVisualizer = ViewModel.ServiceProvider.Resolve <IErrorVisualizer>();
if (errorVisualizer != null)
{
errorVisualizer.Show("Encountered error loading data", ex.Message);
}
_dc.Dispose();
_dc = null;
}
return(0);
}
