public static void CLR_GetTablesList(SqlInt32 accountID, SqlInt32 channelID)
{
try
{
StringBuilder sb = new StringBuilder();
DummyMapper mapper = new DummyMapper();
#region Getting EdgeObjectTypes Tables
//**************************************************//
foreach (Type type in typeof(Creative).Assembly.GetTypes())
{
if (!type.Equals(typeof(Segment)) && !type.IsAbstract)
{
#region Type of Edge Object
if (type.IsSubclassOf(typeof(EdgeObject)))
{
StringBuilder select = new StringBuilder();
string from = string.Empty;
StringBuilder where = new StringBuilder();
//Setting Select statement
foreach (var field in mapper.GetFieldsMapping(type))
{
select.Append(string.Format("[{0}] as [{1}],", field.Value, field.Key));
}
select.Remove(select.Length - 1, 1);
//Get table name from class attribute
string tableName = ((TableInfoAttribute)Attribute.GetCustomAttribute(type, typeof(TableInfoAttribute))).Name;
where.Append(string.Format("AccountID ={0} ", accountID, type.Name));
//Add channel to statement only if type inherit from ChannelSpecificObject class
if (type.IsSubclassOf(typeof(ChannelSpecificObject)))
{
if (channelID.Value != null)
{
where.Append(string.Format(" AND ChannelID =''{0}'' ", channelID.Value));
}
else
{
where.Append(" AND ChannelID != NULL ");
}
}
if (type != typeof(Ad))
{
where.Append(string.Format(" AND ObjectType=''{0}''", type.Name));
}
//setting From statement
from = GetTableSource(type);
sb.Append(string.Format("SELECT '{0}' as TableName,'{1}' as [select],'{2}' as [From], '{3}' as [Where] Union ", tableName, select, from, where));
}
#endregion
#region non EdgeObject Types with Attribute
/*================================================*/
//Getting Other tables such as - Channel, Account ...
else if (((TableInfoAttribute)Attribute.GetCustomAttribute(type, typeof(TableInfoAttribute))) != null)
{
StringBuilder select = new StringBuilder();
string from = string.Empty;
StringBuilder where = new StringBuilder();
//Setting Select statement
foreach (var field in mapper.GetFieldsMapping(type))
{
select.Append(string.Format("[{0}] as [{1}],", field.Value, field.Key));
}
select.Remove(select.Length - 1, 1);
//setting Where statement
where = where.Append(string.Format("AccountID in (-1,{0})", accountID, type.Name));
// where = where.Append(string.Format("AccountID in (-1,{0}) AND ObjectType=''{1}''", accountID, type.Name));
string tableName = ((TableInfoAttribute)Attribute.GetCustomAttribute(type, typeof(TableInfoAttribute))).Name;
from = tableName;
sb.Append(string.Format("SELECT '{0}' as TableName,'{1}' as [select],'{2}' as [From], '{3}' as [Where] Union ", tableName, select, from, where));
}
/*================================================*/
#endregion
}
}
//**************************************************//
#endregion
#region Getting Tables list from Connection Definition Table
/********************************************************************/
//Getting connections from data base
List <ConnectionDef> accountConnections = GetConnections(accountID.Value, channelID.Value);
foreach (ConnectionDef connection in accountConnections)
{
StringBuilder select = new StringBuilder();
string fromTable = string.Empty;
StringBuilder where = new StringBuilder();
Type type = GetDotNetTypeByName(connection.BaseValue);
#region Setting Select statement
foreach (var field in mapper.GetFieldsMapping(type))
{
if (field.Key != "ConnectionDefinitionID")
{
select.Append(string.Format("[{0}] as [{1}],", field.Value, field.Key));
}
}
select.Remove(select.Length - 1, 1);
#endregion
#region Setting Where statement
if (type.IsSubclassOf(typeof(Segment)) || type == typeof(Segment))
{
string ConnectionDefinitionIDField = mapper.GetSqlTargetMapFieldName(type, "ConnectionDefinitionID");
where = where.Append(string.Format("AccountID in (-1,{0})", accountID));
//Add channel to statement only if type inherit from ChannelSpecificObject class
if (type.IsSubclassOf(typeof(ChannelSpecificObject)))
{
if (channelID.Value != null)
{
where.Append(string.Format(" AND ChannelID =''{0}'' ", channelID.Value));
}
else
{
where.Append(" AND ChannelID != NULL ");
}
where = where.Append(string.Format(" AND ObjectType =''{0}'' AND {1} = {2}", typeof(Segment).Name, ConnectionDefinitionIDField, connection.ID));
}
}
else
{
where = where.Append(string.Format("AccountID = {0} ", accountID));
if (channelID.Value != null)
{
where.Append(string.Format(" AND ChannelID = ''{0}'' ", channelID.Value));
}
else
{
where.Append(" AND ChannelID != NULL ");
}
where = where.Append(string.Format(" AND ObjectType = ''{0}''", type.Name));
}
#endregion
fromTable = GetTableSource(type);
sb.Append(string.Format("SELECT '{0}' as TableName,'{1}' as [select],'{2}' as [From], '{3}' as [Where] Union ", connection.Name, select, fromTable, where));
}
sb.Remove(sb.Length - 6, 6);
/********************************************************************/
#endregion
SqlCommand cmd = new SqlCommand(sb.ToString());
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
cmd.Connection = conn;
using (SqlDataReader reader = cmd.ExecuteReader())
{
SqlContext.Pipe.Send(reader);
}
}
}
catch (Exception e)
{
throw new Exception("Could not get table list from data object data base " + e.ToString(), e);
}
}
public static void CLR_GetTableStructure(SqlString virtualTableName)
{
try
{
DummyMapper mapper = new DummyMapper();
string classNamespace = typeof(Creative).Namespace;
Type type = GetDotNetTypeByName(virtualTableName.Value);
//Creating Select by Dummy table name
StringBuilder col = new StringBuilder();
#region Members
/*******************************************************/
Dictionary <string, string> tableStructure = GetSqlTableStructure(type);
foreach (MemberInfo member in type.GetMembers())
{
if (IsRelevant(member))
{
string sql_name = string.Empty;
string sql_type = string.Empty;
string dotNet_name = string.Empty;
string dotNet_type = string.Empty;
string display = string.Empty;
bool isEnum = false;
string memberNamespace = string.Empty;
MemberTypes memberType = member.MemberType;
//Get MemberName
if (member.MemberType.Equals(MemberTypes.Field))
{
memberNamespace = ((FieldInfo)(member)).FieldType.Namespace;
}
else
{
memberNamespace = ((PropertyInfo)(member)).PropertyType.Namespace;
}
//Verify that memeber is class member of Edge.Data.Object
if (memberNamespace == typeof(EdgeObject).Namespace || memberNamespace.StartsWith(classNamespace + ".")) //Memeber is class member from Edge.Data.Object
{
//Getting Enum Types
if ((((FieldInfo)(member)).FieldType).BaseType == typeof(Enum))
{
sql_name = member.Name;
sql_type = tableStructure[mapper.GetSqlTargetMapFieldName(type, member.Name)];
dotNet_name = member.Name;
//dotNet_type = ((MemberInfo)(((FieldInfo)(member)).FieldType)).Name;
dotNet_type = member.ReflectedType.Name;
display = string.Format("{0} {1}", dotNet_name, sql_type);
isEnum = true;
}
//Getting Types that are not Enum but member of Edge.Data.Object
if ((((FieldInfo)(member)).FieldType).BaseType != typeof(Enum))
{
sql_name = member.Name + "ID";
dotNet_name = member.Name + ".ID";
if (!tableStructure.TryGetValue(sql_name, out sql_type))
{
sql_name = member.Name + "GK";
dotNet_name = member.Name + ".GK";
sql_type = tableStructure[sql_name];
}
//sql_type = tableStructure.TryGetValue(sql_name);
//dotNet_type = ((MemberInfo)(((FieldInfo)(member)).FieldType)).Name;
dotNet_type = member.ReflectedType.Name;
display = string.Format("{0} {1}", sql_name, sql_type);
}
}
else
{ //Incase that memeber is not class member of Edge.Data.Object for ex. Budget
sql_name = mapper.GetSqlTargetMapFieldName(type, member.Name);
sql_type = tableStructure[sql_name];
dotNet_name = member.Name;
//dotNet_type = ((MemberInfo)(((FieldInfo)(member)).FieldType)).Name;
dotNet_type = member.ReflectedType.Name;
display = string.Format("{0} {1}", dotNet_name, sql_type);
}
//Creating sql select query
if (!string.IsNullOrEmpty(sql_name))
{
col.Append(string.Format(" Select '{0}' as 'Display Name' Union ", display));
}
}
}
if (col.Length > 0)
{
//Removing last "union string"
col.Remove(col.Length - 6, 6);
//Creating SQL command
SqlCommand cmd = new SqlCommand(col.ToString());
SqlContext.Pipe.Send(col.ToString());
try
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
cmd.Connection = conn;
using (SqlDataReader reader = cmd.ExecuteReader())
{
SqlContext.Pipe.Send(reader);
}
}
}
catch (Exception e)
{
throw new Exception("Could not get table data - GetTableStructureByName() " + e.ToString(), e);
}
}
/****************************************************************/
#endregion
}
catch (Exception ex)
{
SqlContext.Pipe.Send(ex.Message);
}
}