public List <string> GetListAttrByObjType(ObjectsProperty myObj)
{
List <string> listAttrByObjType = new List <string>();
con.Open();
SqlCommand get = new SqlCommand(@"SELECT distinct(F_NAME) FROM IMS_ATTRIBUTES ATR
RIGHT JOIN
(SELECT F_ATTRIBUTE_ID FROM IMS_OBJECT_ATTRS objATTR
RIGHT JOIN
(SELECT F_OBJECT_ID FROM IMS_OBJECTS WHERE F_OBJECT_TYPE = " + myObj.OBJECT_TYPE + @") AS tempOBJ
ON objATTR.F_OBJECT_ID = tempOBJ.F_OBJECT_ID) AS ATRid
ON ATR.F_ATTRIBUTE_ID = ATRid.F_ATTRIBUTE_ID" );
get.Connection = con;
get.CommandType = CommandType.Text;
reader = get.ExecuteReader();
while (reader.Read())
{
listAttrByObjType.Add(reader["F_NAME"].ToString());
}
con.Close();
reader.Close();
return(listAttrByObjType);
}
private void treeViewMain_NodeMouseClick(object sender, TreeNodeMouseClickEventArgs e)
{
EditOrNotEdit = false;
if (e.Button == MouseButtons.Left)
{
dataGridView1.Visible = true;
myObj = sqlObj.objectsProperty.Where(x => x.NAME.Equals(e.Node.Text)).ToList().FirstOrDefault();
if (myObj != null)
{
sqlObj.GetObjectWithAttr(myObj); // получили список(objectsWithAttributes) обьектов по выбраному обьекту-типу
selected_Object_Type = myObj.OBJECT_TYPE;
if (indexOfControlOnPanel > 1)
{
tableLayoutPanel1.Controls.RemoveAt(indexOfControlOnPanel); // удаляем предыдущий контрол
indexOfControlOnPanel = 0;
}
if (sqlObj.objectsWithAttributes.Count != 0)
{
dataGridView1.DataSource = null;
dataGridView1.DataSource = sqlObj.objectsWithAttributes;
}
else
{
dataGridView1.DataSource = null;
}
/////////////////////////////////////////////////////////////////////////////////////////
}
else
{
dataGridView1.DataSource = null;
}
}
else if (e.Button == MouseButtons.Right)
{
if (indexOfControlOnPanel > 1)
{
tableLayoutPanel1.Controls.RemoveAt(indexOfControlOnPanel);
indexOfControlOnPanel = 0;
}
dataGridView1.Visible = false;
ContextMenu contextMenuForObjects = new ContextMenu();
MenuItem menuItemAdd = new MenuItem("Создать объект", new System.EventHandler(this.AddObjectMenuItem_Click));
contextMenuForObjects.MenuItems.Add(menuItemAdd);
contextMenuForObjects.Show(treeViewMain, e.Location);
}
}
public void GetObjectWithAttr(ObjectsProperty myObj)
{
var list = GetListAttrByObjType(myObj);
string attributesNames = "";
if (list.Count != 0)
{
foreach (var item in list)
{
if (!((item == "") || item == null))
{
attributesNames += "[" + item + "], ";
}
}
attributesNames = attributesNames.Remove(attributesNames.Count() - 2, 2);
}
objectsWithAttributes.Clear();
con.Open();
SqlCommand command = new SqlCommand(@"select F_OBJECT_ID + " + attributesNames +
@"from (select atr.F_NAME, objAtr.F_STRING_VALUE, temp.F_OBJECT_ID
from
(select F_OBJECT_ID from IMS_OBJECTS where F_OBJECT_TYPE = " + myObj.OBJECT_TYPE + @")
as temp, IMS_OBJECT_ATTRS objAtr, IMS_ATTRIBUTES atr
where temp.F_OBJECT_ID = objAtr.F_OBJECT_ID
and objAtr.F_ATTRIBUTE_ID = atr.F_ATTRIBUTE_ID) as TEMPORAR
pivot
(
MIN(TEMPORAR.F_STRING_VALUE) for TEMPORAR.F_NAME IN ( " + attributesNames + @")
)
AS TESTpIVOT ", con);
command.CommandType = CommandType.Text;
////////////////////////////////////////////////////////////////////////////////////////////////////
//создавать класс динамически со свойствами
reader = command.ExecuteReader();
while (reader.Read())
{
objectsWithAttributes.Add(new IMS_Object_Attributes {
//OBJECT_ID = reader["F_OBJECT_ID"].ToString(),
STRING_VALUE = reader[list[2]].ToString()
});
}
con.Close();
reader.Close();
}