/// <inheritdoc/>
public Dictionary <DataAccessContext, List <TableInfo> > GetAllTablesUsingCredentials(DataAccessCredentials credentials)
{
Dictionary <DataAccessContext, List <int> > toReturn = new Dictionary <DataAccessContext, List <int> >();
toReturn.Add(DataAccessContext.Any, new List <int>());
toReturn.Add(DataAccessContext.DataExport, new List <int>());
toReturn.Add(DataAccessContext.DataLoad, new List <int>());
toReturn.Add(DataAccessContext.InternalDataProcessing, new List <int>());
toReturn.Add(DataAccessContext.Logging, new List <int>());
using (var con = _repository.GetConnection())
{
using (var cmd = DatabaseCommandHelper.GetCommand(
"SELECT TableInfo_ID,Context FROM DataAccessCredentials_TableInfo WHERE DataAccessCredentials_ID = @cid",
con.Connection, con.Transaction))
{
cmd.Parameters.Add(DatabaseCommandHelper.GetParameter("@cid", cmd));
cmd.Parameters["@cid"].Value = credentials.ID;
using (var r = cmd.ExecuteReader())
while (r.Read())
{
//get the context
DataAccessContext context = GetContext(r);
//add the TableInfo under that context
toReturn[context].Add((int)r["TableInfo_ID"]);
}
}
}
return(toReturn.ToDictionary(k => k.Key, v => _repository.GetAllObjectsInIDList <TableInfo>(v.Value).ToList()));
}
/// <inheritdoc/>
public DataAccessCredentials GetCredentialsIfExistsFor(TableInfo tableInfo, DataAccessContext context)
{
int toReturn = -1;
using (var con = _repository.GetConnection())
{
var cmd = DatabaseCommandHelper.GetCommand("SELECT DataAccessCredentials_ID,Context FROM DataAccessCredentials_TableInfo WHERE TableInfo_ID = @tid and (Context =@context OR Context=" + ((int)DataAccessContext.Any) + ") ", con.Connection, con.Transaction);
cmd.Parameters.Add(DatabaseCommandHelper.GetParameter("@tid", cmd));
cmd.Parameters["@tid"].Value = tableInfo.ID;
cmd.Parameters.Add(DatabaseCommandHelper.GetParameter("@context", cmd));
cmd.Parameters["@context"].Value = context;
var r = cmd.ExecuteReader();
//gets the first liscenced usage
if (r.Read())
{
//there is one
//get it by it's id
toReturn = Convert.ToInt32(r["DataAccessCredentials_ID"]);
//if the first record is liscenced for Any
if (Convert.ToInt32(r["Context"]) == ((int)DataAccessContext.Any))
{
//see if there is a more specific second record (e.g. DataLoad)
if (r.Read())
{
toReturn = Convert.ToInt32(r["DataAccessCredentials_ID"]);
}
}
}
}
return(toReturn != -1 ?_repository.GetObjectByID <DataAccessCredentials>(toReturn):null);
}
public void CommitResults(CacheCommitArguments arguments)
{
var configuration = arguments.Configuration;
var operation = arguments.Operation;
DeleteCacheEntryIfAny(configuration, operation);
//Do not change Types of source columns unless there is an explicit override
arguments.Results.SetDoNotReType(true);
using (var con = _server.GetConnection())
{
con.Open();
string nameWeWillGiveTableInCache = operation + "_AggregateConfiguration" + configuration.ID;
//either it has no name or it already has name we want so its ok
arguments.Results.TableName = nameWeWillGiveTableInCache;
//add explicit types
var tbl = _database.ExpectTable(nameWeWillGiveTableInCache);
if (tbl.Exists())
{
tbl.Drop();
}
tbl = _database.CreateTable(nameWeWillGiveTableInCache, arguments.Results, arguments.ExplicitColumns);
if (!tbl.Exists())
{
throw new Exception("Cache table did not exist even after CreateTable completed without error!");
}
var cmdCreateNew =
DatabaseCommandHelper.GetCommand(
"INSERT INTO CachedAggregateConfigurationResults (Committer,AggregateConfiguration_ID,SqlExecuted,Operation,TableName) Values (@Committer,@AggregateConfiguration_ID,@SqlExecuted,@Operation,@TableName)", con);
cmdCreateNew.Parameters.Add(DatabaseCommandHelper.GetParameter("@Committer", cmdCreateNew));
cmdCreateNew.Parameters["@Committer"].Value = Environment.UserName;
cmdCreateNew.Parameters.Add(DatabaseCommandHelper.GetParameter("@AggregateConfiguration_ID", cmdCreateNew));
cmdCreateNew.Parameters["@AggregateConfiguration_ID"].Value = configuration.ID;
cmdCreateNew.Parameters.Add(DatabaseCommandHelper.GetParameter("@SqlExecuted", cmdCreateNew));
cmdCreateNew.Parameters["@SqlExecuted"].Value = arguments.SQL.Trim();
cmdCreateNew.Parameters.Add(DatabaseCommandHelper.GetParameter("@Operation", cmdCreateNew));
cmdCreateNew.Parameters["@Operation"].Value = operation.ToString();
cmdCreateNew.Parameters.Add(DatabaseCommandHelper.GetParameter("@TableName", cmdCreateNew));
cmdCreateNew.Parameters["@TableName"].Value = tbl.GetRuntimeName();
cmdCreateNew.ExecuteNonQuery();
arguments.CommitTableDataCompleted(tbl);
}
}
public void Add(Type o, DbConnectionStringBuilder builder, DbConnection connection, DbTransaction transaction)
{
var command = DatabaseCommandHelper.GetCommand("UPDATE " + o.Name + " SET {0} WHERE ID=@ID;", connection, transaction);
var props = TableRepository.GetPropertyInfos(o);
foreach (PropertyInfo p in props)
{
command.Parameters.Add(DatabaseCommandHelper.GetParameter("@" + p.Name, command));
}
command.CommandText = string.Format(command.CommandText, string.Join(",", props.Where(p => p.Name != "ID").Select(p => "[" + p.Name + "]=@" + p.Name)));
UpdateCommands.Add(o, command);
}
/// <inheritdoc/>
public Dictionary <DataAccessContext, DataAccessCredentials> GetCredentialsIfExistsFor(TableInfo tableInfo)
{
var toReturn = new Dictionary <DataAccessContext, int>();
using (var con = _repository.GetConnection())
{
var cmd = DatabaseCommandHelper.GetCommand("SELECT DataAccessCredentials_ID,Context FROM DataAccessCredentials_TableInfo WHERE TableInfo_ID = @tid", con.Connection, con.Transaction);
cmd.Parameters.Add(DatabaseCommandHelper.GetParameter("@tid", cmd));
cmd.Parameters["@tid"].Value = tableInfo.ID;
var r = cmd.ExecuteReader();
toReturn = GetLinksFromReader(r);
}
return(toReturn.ToDictionary(k => k.Key, v => _repository.GetObjectByID <DataAccessCredentials>(v.Value)));
}
/// <inheritdoc/>
public void CreateLinkBetween(DataAccessCredentials credentials, TableInfo tableInfo, DataAccessContext context)
{
using (var con = _repository.GetConnection())
{
var cmd = DatabaseCommandHelper.GetCommand("INSERT INTO DataAccessCredentials_TableInfo(DataAccessCredentials_ID,TableInfo_ID,Context) VALUES (@cid,@tid,@context)", con.Connection, con.Transaction);
cmd.Parameters.Add(DatabaseCommandHelper.GetParameter("@cid", cmd));
cmd.Parameters["@cid"].Value = credentials.ID;
cmd.Parameters.Add(DatabaseCommandHelper.GetParameter("@tid", cmd));
cmd.Parameters["@tid"].Value = tableInfo.ID;
cmd.Parameters.Add(DatabaseCommandHelper.GetParameter("@context", cmd));
cmd.Parameters["@context"].Value = context;
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Returns all <see cref="Lookup"/> relationships that exist between the main dataset <paramref name="foreignKeyTable"/> and the
/// assumed lookup table <paramref name="primaryKeyTable"/>
/// </summary>
/// <param name="foreignKeyTable">The main dataset table</param>
/// <param name="primaryKeyTable">The hypothesized lookup table</param>
/// <returns>All lookup relationships, a given table could have 2+ of these e.g. SendingLocation and DischargeLocation could both reference z_Location lookup</returns>
public static Lookup[] GetAllLookupsBetweenTables(TableInfo foreignKeyTable, TableInfo primaryKeyTable)
{
if (foreignKeyTable.Equals(primaryKeyTable))
{
throw new NotSupportedException("Tables must be different");
}
if (!foreignKeyTable.Repository.Equals(primaryKeyTable.Repository))
{
throw new NotSupportedException("TableInfos come from different repositories!");
}
var repo = ((CatalogueRepository)foreignKeyTable.Repository);
using (var con = repo.GetConnection())
{
DbCommand cmd;
cmd = DatabaseCommandHelper.GetCommand(@"SELECT * FROM [Lookup]
WHERE
(SELECT TableInfo_ID FROM ColumnInfo where ID = PrimaryKey_ID) = @primaryKeyTableID
AND
(SELECT TableInfo_ID FROM ColumnInfo where ID = [ForeignKey_ID]) = @foreignKeyTableID"
, con.Connection, con.Transaction);
cmd.Parameters.Add(DatabaseCommandHelper.GetParameter("@primaryKeyTableID", cmd));
cmd.Parameters.Add(DatabaseCommandHelper.GetParameter("@foreignKeyTableID", cmd));
cmd.Parameters["@primaryKeyTableID"].Value = primaryKeyTable.ID;
cmd.Parameters["@foreignKeyTableID"].Value = foreignKeyTable.ID;
DbDataReader r = cmd.ExecuteReader();
List <Lookup> toReturn = new List <Lookup>();
while (r.Read())
{
toReturn.Add(new Lookup(repo, r));
}
r.Close();
return(toReturn.ToArray());
}
}
public int?GetOrderIfExistsFor(AggregateConfiguration configuration)
{
if (configuration.Repository != this)
{
if (((CatalogueRepository)configuration.Repository).ConnectionString != CatalogueRepository.ConnectionString)
{
throw new NotSupportedException("AggregateConfiguration is from a different repository than this with a different connection string");
}
}
using (var con = CatalogueRepository.GetConnection())
{
DbCommand cmd = DatabaseCommandHelper.GetCommand("SELECT [Order] FROM CohortAggregateContainer_AggregateConfiguration WHERE AggregateConfiguration_ID = @AggregateConfiguration_ID", con.Connection, con.Transaction);
cmd.Parameters.Add(DatabaseCommandHelper.GetParameter("@AggregateConfiguration_ID", cmd));
cmd.Parameters["@AggregateConfiguration_ID"].Value = configuration.ID;
return(CatalogueRepository.ObjectToNullableInt(cmd.ExecuteScalar()));
}
}
public DbCommand PrepareCommand(DbCommand cmd, Dictionary <string, object> parameters)
{
foreach (var kvp in parameters)
{
var paramName = kvp.Key.StartsWith("@") ? kvp.Key : "@" + kvp.Key;
// Check that this parameter name actually exists in the sql
if (!cmd.CommandText.Contains(paramName))
{
throw new InvalidOperationException("Parameter '" + paramName + "' does not exist in the SQL command (" + cmd.CommandText + ")");
}
//if it isn't yet in the command add it
if (!cmd.Parameters.Contains(paramName))
{
cmd.Parameters.Add(DatabaseCommandHelper.GetParameter(paramName, cmd));
}
//set it's value
SetParameterToValue(cmd.Parameters[paramName], kvp.Value);
}
return(cmd);
}
