private StringBuilder getMultiCondition(ModelRelation sr)
{
StringBuilder sc = new StringBuilder();
for (int j = 0; j < this.Relations.Count; j++)
{
ModelRelation tempsr = this.Relations[j];
if (tempsr.IsRelated == false && ((tempsr.BaseSchema == sr.BaseSchema && tempsr.RelatedSchema == sr.RelatedSchema) || (tempsr.BaseSchema == sr.RelatedSchema && tempsr.RelatedSchema == sr.BaseSchema)))
{
sc.AppendFormat(" and {0}.{1} equals {2}.{3} ", sr.BaseSchema, sr.BaseSchemaField, sr.RelatedSchema, sr.RelatedSchemaField);
}
sr.IsRelated = true;
}
return(sc);
}
private StringBuilder getChildRelation(ModelRelation sr)
{
StringBuilder sc = new StringBuilder();
for (int j = 0; j < this.Relations.Count; j++)
{
ModelRelation tempsr = this.Relations[j];
if (tempsr.IsRelated == false && tempsr.BaseSchema == sr.RelatedSchema)
{
sc.AppendFormat("join {0} in dt_{0}.AsEnumerable() on {1}.Field<{4}>({2}) equals {0}.Field<{5}>({3})\n", tempsr.RelatedSchema,
this.Relations[j].BaseSchema, "\"" + tempsr.BaseSchemaField + "\"", "\"" + tempsr.RelatedSchemaField + "\"",
getColumnFieldType(this.mSchema.Tables[tempsr.BaseSchema].Columns[tempsr.BaseSchemaField]),
getColumnFieldType(this.mSchema.Tables[tempsr.RelatedSchema].Columns[tempsr.RelatedSchemaField]));
tempsr.IsRelated = true;
}
//else if (tempsr.IsRelated == false && tempsr.BaseSchema == sr.BaseSchema)
//{
// sc.AppendFormat("join {0} in dt_{0}.AsEnumerable() on {1}.Field<{4}>({2}) equals {0}.Field<{5}>({3})\n", tempsr.RelatedSchema,
// this.Relations[j].BaseSchema, tempsr.BaseSchemaField, tempsr.RelatedSchemaField, getColumnFieldType(this.mSchema.Tables[tempsr.BaseSchema].Columns[tempsr.BaseSchemaField]),
// getColumnFieldType(this.mSchema.Tables[tempsr.RelatedSchema].Columns[tempsr.RelatedSchemaField]));
// tempsr.IsRelated = true;
//}
//else if (tempsr.IsRelated == false && tempsr.RelatedSchema == sr.RelatedSchema)
//{
// sc.AppendFormat("join {0} in dt_{0}.AsEnumerable() on {1}.Field<{4}>({2}) equals {0}.Field<{5}>({3})\n", tempsr.RelatedSchema,
// this.Relations[j].BaseSchema, tempsr.BaseSchemaField, tempsr.RelatedSchemaField,
// getColumnFieldType(this.mSchema.Tables[tempsr.BaseSchema].Columns[tempsr.BaseSchemaField]),
// getColumnFieldType(this.mSchema.Tables[tempsr.RelatedSchema].Columns[tempsr.RelatedSchemaField]));
// tempsr.IsRelated = true;
//}
//else if (tempsr.IsRelated == false && tempsr.RelatedSchema == sr.BaseSchema)
//{
// sc.AppendFormat("join {0} in dt_{0}.AsEnumerable() on {1}.Field<{4}>({2}) equals {0}.Field<{5}>({3})\n", tempsr.RelatedSchema,
// this.Relations[j].BaseSchema, tempsr.BaseSchemaField, tempsr.RelatedSchemaField, getColumnFieldType(this.mSchema.Tables[tempsr.BaseSchema].Columns[tempsr.BaseSchemaField]),
// getColumnFieldType(this.mSchema.Tables[tempsr.RelatedSchema].Columns[tempsr.RelatedSchemaField]));
// tempsr.IsRelated = true;
//}
}
return(sc);
}
private DataModel addRelation(ModelRelation relation)
{
Relations.Add(relation);
return(this);
}
public object getResult()
{
//DataSet ds;
//List<SchemaRelation> rela;
//DynamicSchema dy = new DynamicSchema(ds,rela);
//dy.addRelation(new SchemaRelation("", "", "", ""), new SchemaRelation("", "", "", ""));
this.Tables = new List <string>();
for (int i = 0; i < this.Relations.Count; i++)
{
ModelRelation sr = new ModelRelation();
sr = this.Relations[i];
if (!isTableExist(sr.BaseSchema))
{
this.Tables.Add(sr.BaseSchema);
}
if (!isTableExist(sr.RelatedSchema))
{
this.Tables.Add(sr.RelatedSchema);
}
}
CSharpCodeProvider c = new CSharpCodeProvider();
ICodeCompiler icc = c.CreateCompiler();
CompilerParameters cp = new CompilerParameters();
cp.ReferencedAssemblies.Add("system.dll");
cp.ReferencedAssemblies.Add("system.xml.dll");
cp.ReferencedAssemblies.Add("system.data.dll");
cp.ReferencedAssemblies.Add("system.windows.forms.dll");
cp.ReferencedAssemblies.Add("system.drawing.dll");
cp.ReferencedAssemblies.Add("System.Collections.dll");
cp.ReferencedAssemblies.Add("System.ComponentModel.dll");
cp.ReferencedAssemblies.Add("System.Data.DataSetExtensions.dll");
cp.ReferencedAssemblies.Add("System.Linq.dll");
cp.ReferencedAssemblies.Add("mscorlib.dll");
cp.ReferencedAssemblies.Add("System.Threading.Tasks.dll");
cp.ReferencedAssemblies.Add("System.Reflection.dll");
cp.ReferencedAssemblies.Add("Microsoft.CSharp.dll");
cp.ReferencedAssemblies.Add("System.Core.dll");
cp.ReferencedAssemblies.Add(this.AssemblyPath() + "Mysql.Data.dll");
// cp.ReferencedAssemblies.Add("ForziaUtilities.dll");
cp.CompilerOptions = "/t:library";
cp.GenerateInMemory = true;
StringBuilder sb = new StringBuilder("");
sb.Append("using System;\n");
sb.Append("using System.Xml;\n");
sb.Append("using System.Data.SqlClient;\n");
sb.Append("using System.Windows.Forms;\n");
sb.Append("using System.Drawing;\n");
sb.Append("using System.Collections.Generic;\n");
sb.Append("using System.ComponentModel;\n");
sb.Append("using System.Data;\n");
sb.Append("using System.Linq;\n");
sb.Append("using System.Text;\n");
sb.Append("using System.Threading.Tasks;\n");
sb.Append("using System.Reflection;\n");
sb.Append("using System.CodeDom.Compiler;\n");
sb.Append("using System.Diagnostics;\n");
sb.Append("using Microsoft.CSharp;\n");
sb.Append("using MySql.Data.MySqlClient;\n");
sb.Append("using MySql.Data;\n");
sb.Append("namespace CodeEvalution{ \n");
sb.Append("public class CodeEvalutor{ \n");
sb.Append("public object EvalCode(DataSet ds){ \n");
// sb.Append(sbCode + " \n");
for (int itbl = 0; itbl < this.Tables.Count; itbl++)
{
sb.AppendFormat("DataTable dt_{0} = new DataTable();\n", this.Tables[itbl]);
sb.AppendFormat("dt_{0} = ds.Tables[{1}] ;\n", this.Tables[itbl], "\"" + this.Tables[itbl] + "\"");
}
for (int itbl = 0; itbl < this.Tables.Count; itbl++)
{
if (itbl == 0)
{
sb.AppendFormat("var query = from {0} in dt_{0}.AsEnumerable()\n", Tables[itbl]);
}
else
{
sb.AppendFormat(" from {0} in dt_{0}.AsEnumerable()\n", Tables[itbl]);
}
}
for (int i = 0; i < this.Relations.Count; i++)
{
ModelRelation sr = new ModelRelation();
sr = this.Relations[i];
if (i == 0)
{
sb.Append(" .Where (mapping => ");
continue;
}
else
{
sb.AppendFormat("mapping.Field<{3}>({4}) == {0}.Field<{1}>({2})", sr.BaseSchema, getColumnFieldType(this.mSchema.Tables[sr.BaseSchema].Columns[sr.BaseSchemaField]), "\"" + sr.BaseSchemaField + "\"", getColumnFieldType(this.mSchema.Tables[sr.RelatedSchema].Columns[sr.RelatedSchemaField]), "\"" + sr.RelatedSchemaField + "\"");
}
if (i != this.Relations.Count - 1)
{
sb.Append(" && \n");
}
else
{
sb.Append(" ).DefaultIfEmpty() \n");
}
////////////////////////////////////////// direct relationship commented due to left join missing /sampathkumar.A
//SchemaRelation sr= new SchemaRelation() ;
//sr=this.Relations[i];
//if (i == 0) {
//sb.Append(" where ");
//}
//sb.AppendFormat("{0}.Field<{1}>({2}) == {3}.Field<{4}>({5})\n", this.Relations[i].BaseSchema, getColumnFieldType(this.mSchema.Tables[sr.BaseSchema].Columns[sr.BaseSchemaField]), "\"" + sr.BaseSchemaField + "\"", sr.RelatedSchema, getColumnFieldType(this.mSchema.Tables[sr.RelatedSchema].Columns[sr.RelatedSchemaField]), "\"" + sr.RelatedSchemaField + "\"");
//if (i != this.Relations.Count -1)
//{
// sb.Append(" && \n");
//}
//////////////////////////////////////////////////////
// Unable to solve multiple field join in relation so that it is commented /sampathkumar.a
//if (i == 0) {
// sb.AppendFormat("var query = from {0} in dt_{0}.AsEnumerable()\n",this.Relations[i].BaseSchema);
//}
//if (sr.IsRelated == false) {
// sb.AppendFormat("join {0} in dt_{0}.AsEnumerable() on {1}.Field<{4}>({2}) equals {0}.Field<{5}>({3})\n", sr.RelatedSchema,
// this.Relations[i].BaseSchema, "\"" + sr.BaseSchemaField + "\"", "\"" + sr.RelatedSchemaField + "\"", getColumnFieldType(this.mSchema.Tables[sr.BaseSchema].Columns[sr.BaseSchemaField]),
// getColumnFieldType(this.mSchema.Tables[sr.RelatedSchema].Columns[sr.RelatedSchemaField]));
// sr.IsRelated = true;
//}
//sb.Append(this.getMultiCondition(sr));
//sb.Append (this.getChildRelation(sr));
}
sb.Append("select new {");
for (int ifld = 0; ifld < this.Fields.Count; ifld++)
{
sb.AppendFormat(" {0} = ({3} == null)? {4} : {3}.Field<{1}>({2})", this.Fields[ifld].FieldName, getFieldType(this.Fields[ifld]),
"\"" + this.Fields[ifld].FieldName + "\"", this.Fields[ifld].Schema, getNullValue(this.Fields[ifld]));
if (ifld != (this.Fields.Count - 1))
{
sb.AppendFormat(",");
}
}
sb.Append("};\n");
sb.Append(" DataTable rst = new DataTable();\n");
sb.Append(" rst = ConvertToDataTable(query);\n");
sb.Append("return rst; \n");
sb.Append("} \n");
sb.Append("public DataTable ConvertToDataTable<T>(IEnumerable<T> varlist) \n");
sb.Append("{ \n");
sb.Append("DataTable dtReturn = new DataTable(); \n");
// column names
sb.Append("PropertyInfo[] oProps = null; \n");
sb.Append("if (varlist == null) return dtReturn; \n");
sb.Append("foreach (T rec in varlist) \n");
sb.Append("{ \n");
sb.Append("if (oProps == null) \n");
sb.Append("{ \n");
sb.Append("oProps = ((Type)rec.GetType()).GetProperties(); \n");
sb.Append("foreach (PropertyInfo pi in oProps) \n");
sb.Append("{ \n");
sb.Append("Type colType = pi.PropertyType; \n");
sb.Append("if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>))) \n");
sb.Append("{ \n");
sb.Append("colType = colType.GetGenericArguments()[0]; \n");
sb.Append("} \n");
sb.Append("dtReturn.Columns.Add(new DataColumn(pi.Name, colType)); \n");
sb.Append("} \n");
sb.Append("} \n");
sb.Append("DataRow dr = dtReturn.NewRow(); \n");
sb.Append("foreach (PropertyInfo pi in oProps) \n");
sb.Append("{ \n");
sb.Append("dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue \n");
sb.Append("(rec, null); \n");
sb.Append("} \n");
sb.Append("dtReturn.Rows.Add(dr); \n");
sb.Append("} \n");
sb.Append("return dtReturn; \n");
sb.Append("} \n");
sb.Append("} \n");
sb.Append("}\n");
CompilerResults cr = icc.CompileAssemblyFromSource(cp, sb.ToString());
if (cr.Errors.Count > 0)
{
for (int i = 0; i < cr.Errors.Count; i++)
{
SchemaErrors.Add(new SchemaError(cr.Errors[i].ErrorText, cr.Errors[i].Line));
return(null);
}
}
System.Reflection.Assembly a = cr.CompiledAssembly;
object o = a.CreateInstance("CodeEvalution.CodeEvalutor");
Type t = o.GetType();
MethodInfo mi = t.GetMethod("EvalCode");
object[] arguments = new object[1];
arguments[0] = this.mSchema;
object s = mi.Invoke(o, arguments);
return(s);
}
