public string NameOfRelatedEntity2(DbEntityType relatedEntType1, DbEntityType relatedEntType2)
{
string where = WhereIn(relatedEntType1);
DbEntity relatedEnt1 = new DbEntity(relatedEntType1);
DbEntity relatedEnt2 = new DbEntity(relatedEntType2);
string x = String.Format("select {0} from {1}.{2}.{3} where {4} in (select {5} from {6}.{7}.{8} {9})"
, relatedEnt2.namefld
, relatedEnt2.fulldb
, relatedEnt2.schema
, relatedEnt2.table_name
, relatedEnt1.pkfld
, relatedEnt1.pkfld
, relatedEnt1.fulldb
, relatedEnt1.schema
, relatedEnt1.table_name
, where);
SQL_utils sql = new SQL_utils(relatedEnt2.db);
string name = sql.StringScalar_from_SQLstring(x);
sql.Close();
return(name);
}
public DbEntity(DbEntityType myentitytype)
{
entitytype = myentitytype;
if (myentitytype == DbEntityType.study)
{
db = "backend";
schema = "dbo";
table_name = "tblstudy";
pkfld = "studyID";
namefld = "studyname";
}
else if (myentitytype == DbEntityType.studymeas)
{
db = "backend";
schema = "dbo";
table_name = "tblstudymeas";
pkfld = "studymeasID";
namefld = "studymeasname";
}
else if (myentitytype == DbEntityType.measure)
{
db = "backend";
schema = "dbo";
table_name = "tblmeasure";
pkfld = "measureID";
namefld = "measname";
}
else if (myentitytype == DbEntityType.tbl)
{
db = "data";
schema = "def";
table_name = "tbl";
pkfld = "tblpk";
namefld = "tblname";
}
else if (myentitytype == DbEntityType.fld)
{
db = "data";
schema = "def";
table_name = "fld";
pkfld = "fldpk";
namefld = "fldname";
}
else if (myentitytype == DbEntityType.timepoint)
{
db = "backend";
schema = "dbo";
table_name = "tbltimepoint";
pkfld = "timepointID";
namefld = "timepoint_text";
}
}
//
public string WhereIn(DbEntityType entTypeToFind)
{
DbEntity entToFind = new DbEntity(entTypeToFind);
string x = String.Format(" where {0} IN (select {0} from {1}.{2}.{3} where {4}={5})"
, entToFind.pkfld
, ent.fulldb
, ent.schema
, ent.table_name
, ent.pkfld
, instance_pkval);
return(x);
}
public int PkvalOfRelatedEntity(DbEntityType relatedEntType)
{
string where = WhereIn(relatedEntType);
DbEntity relatedEnt = new DbEntity(relatedEntType);
string x = String.Format("select {0} from {1}.{2}.{3} {4}"
, relatedEnt.pkfld
, relatedEnt.fulldb
, relatedEnt.schema
, relatedEnt.table_name
, where);
SQL_utils sql = new SQL_utils(relatedEnt.db);
int pkval = sql.IntScalar_from_SQLstring(x);
sql.Close();
return(pkval);
}
public string NameOfRelatedEntity(DbEntityType relatedEntType)
{
string where = WhereIn(relatedEntType);
DbEntity relatedEnt = new DbEntity(relatedEntType);
string x = String.Format("select {0} from {1}.{2}.{3} {4}"
, relatedEnt.namefld
, relatedEnt.fulldb
, relatedEnt.schema
, relatedEnt.table_name
, where);
SQL_utils sql = new SQL_utils(relatedEnt.db);
string name = sql.StringScalar_from_SQLstring(x);
sql.Close();
return(name);
}
public async void Populate()
{
Items.Clear();
//aggiungiamo le colonne
_setColumns();
//questa variabile rappresenta il metodo statico GetAllAsync della classe associata al nostro controllo
MethodInfo getAllAsyncMethod = DbEntityType.GetMethod("GetAllAsync", BindingFlags.Public | BindingFlags.Static);
ListViewItem item;
ListViewItem.ListViewSubItem subItem;
object propertyValue;
//Chiamiamo il metodo asincrono GetAllAsync.
//Facendo il cast con il tipo "dynamic" indichiamo al compilatore che il tipo dell'oggetto restituito
//sarà determinato solo quando il codice sarà eseguito.
//Sappiamo, però, che sarà una collezione enumerabile di oggetti che implementano l'interfaccia IDbEntity,
//ovvero oggetti che rispecchiano tabelle nel database.
IEnumerable <IDBEntity> result = await(dynamic) getAllAsyncMethod.Invoke(null, null);
//TODO: check for display properties before iterating through them
foreach (IDBEntity dbEntity in result)
{
item = new ListViewItem();
//Prendiamo il valore della prima proprietà
propertyValue = Properties[0].GetValue(dbEntity);
item.Text = propertyValue == null ? "" : propertyValue.ToString();
//Ripetiamo il procedimento per le altre proprietà dell'oggetto
for (int i = 1; i < Properties.Count(); i++)
{
subItem = new ListViewItem.ListViewSubItem();
propertyValue = Properties[i].GetValue(dbEntity);
subItem.Text = propertyValue == null ? "" : propertyValue.ToString();
item.SubItems.Add(subItem);
}
item.Tag = dbEntity;
Items.Add(item);
}
//Le colonne devono adattarsi al contenuto delle celle.
AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize);
}
protected int GetEntityID(SQL_utils sql)
{
var qs = Request.RawUrl;
int entityid = -1;
DbEntityType enttype = (DbEntityType)Convert.ToInt32(Request.QueryString["entitytypeid"]);
if (enttype == DbEntityType.study)
{
entityid = sql.IntScalar_from_SQLstring("select dbo.fnDefaultStudyidByUser()");
}
else if (enttype == DbEntityType.subject)
{
entityid = Convert.ToInt32(Request.QueryString["entityid"]);
}
return(entityid);
}
public static void InsertTestData(TestDbContext ctx)
{
if (ctx.UserLogins.FirstOrDefault(u => u.Id == 1) != null)
{
return;
}
var userLogin = new DbUserLogin()
{
Initials = "TE",
UserPrincipalName = "*****@*****.**",
DisplayName = "Test user"
};
ctx.UserLogins.Add(userLogin);
var entityType = new DbEntityType()
{
Description = "Test",
TableName = "TEST",
CreatedAt = DateTime.Now,
CreatedByUserLogin = userLogin,
ModifiedAt = DateTime.Now,
ModifiedByUserLogin = userLogin
};
ctx.EntityTypes.Add(entityType);
var cir = new DbCIR()
{
Code = "C1",
Description = "D1",
CreatedAt = DateTime.Now,
CreatedByUserLogin = userLogin,
ModifiedAt = DateTime.Now,
ModifiedByUserLogin = userLogin
};
ctx.CIRs.Add(cir);
for (int i = 0; i < 10; i++)
{
ctx.Entities.Add(new DbEntity()
{
Code = $"C{i}",
Description = $"D{i}",
EntityKey = 123,
EntityType = entityType,
IsActive = true,
CreatorCIR = cir,
MasterCIR = cir,
CreatedAt = DateTime.Now,
CreatedByUserLogin = userLogin,
ModifiedAt = DateTime.Now,
ModifiedByUserLogin = userLogin
});
}
ctx.SaveChanges();
}
