一.Hql 返回IList,组合成DataTable
string hql =" from Item I left join I.Products P left join P.FailureModes F "
+" where P.ProductName=?";
Castle.ActiveRecord.Queries.SimpleQuery query =
new Castle.ActiveRecord.Queries.SimpleQuery(typeof(Item),typeof(IList), hql, "aa");
IList item = (IList)Castle.ActiveRecord.ActiveRecordBase.ExecuteQuery(query);
具体要查的字段,要动态传入
private DataTable MakeTable(IList fields,IList item)
{
DataTable table = new DataTable("Data");
DataColumn column = null;
foreach (QueryField field in fields)
{
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.Caption = field.DisplayText;
column.ColumnName = field.DisplayText;
table.Columns.Add(column);
}
DataRow row;
foreach (object[] ol in item)
{
row = table.NewRow();
int j = 0;
foreach (object o in ol)
{
row[table.Columns[j]] = o;
j++;
}
table.Rows.Add(row);
}
return table;
}
二. 通过Sql语句返回DataTable
string ssql = " select I.ItemName,P.ProductName,F.FailureName "
+ " from Item I left join Product P on I.ItemId=P.ItemId "
+ " left join FailureMode F on P.ProductId=F.ProductId "
+ " where p.ProductName='aa'";
Castle.ActiveRecord.Framework.ISessionFactoryHolder sessionHolder =
Castle.ActiveRecord.ActiveRecordMediator.GetSessionFactoryHolder();
NHibernate.ISession session = sessionHolder.CreateSession(typeof(Item));
try
{
IDbCommand command = session.Connection.CreateCommand();
command.CommandText = ssql;
IDataReader rdr = command.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(rdr, LoadOption.Upsert);
dataGridView1.DataSource = dt;
}
finally
{
sessionHolder.ReleaseSession(session);
}
三。 public static IList<T> GetDataSource<T>(Project curProject)
{
try
{
Type t = typeof(T);
if (t == null) return null;
ParameterExpression param = Expression.Parameter(t, "tt");
//c.City=="London"
Expression left = Expression.Property(param,t.GetProperty("Project"));
Expression right = Expression.Constant(curProject);
Expression filter = Expression.Equal(left, right);
Expression pred = Expression.Lambda(filter, param);
//Where(c=>c.City=="London")
IQueryable<T> query= from tt in ActiveRecordLinq.AsQueryable<T>()
select tt;
Expression expr = Expression.Call(
typeof(Queryable),
"Where",
new Type[] { t },
Expression.Constant(query),
pred
);
var ls=query.ToList<T>().AsQueryable<T>().Provider.CreateQuery<T>(expr);
return ls.ToList<T>();
}
catch
{
return null;
}
}