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); } }