你的位置:首页 > ASP.net教程

[ASP.net教程]csharp:Nhibernate Procedure with CreateSQLQuery


<?

  

 

 

 /// <summary>    ///     /// </summary>    /// <param name="id"></param>    /// <returns></returns>    public IList<DuCardType> CardTypeSql(int id)    {      NHibernate.ISession session = this.SessionFactory.GetCurrentSession();      IList<DuCardType> card = new List<DuCardType>();      if (!object.Equals(session, null))      {        //写法1        //return _session.CreateQuery("from DuCardType c where c.CardTypeName='" + firstname + "'")        //  .List<DuCardType>();        //写法2:位置型参数        //return _session.CreateQuery("from DuCardType c where c.CardTypeName=?")        //  .SetString(0, firstname)        //  .List<DuCardType>();        //写法4.        //var sql = "select * from DuCardType";        //var query = session.CreateSQLQuery(sql)        //.AddScalar("CardTypeId", NHibernateUtil.Int32)        //.AddScalar("CardTypeName", NHibernateUtil.String)        //.AddScalar("CardTypeColor", NHibernateUtil.String)        //.SetResultTransformer(Transformers.AliasToBean<DuCardType>());        //result = query.List<DuCardType>();         //写法3:命名型参数(推荐)        var query = session.CreateSQLQuery("Select * FROM DuCardType where CardTypeId=:CardTypeId")          .SetInt32("CardTypeId", id)          .SetResultTransformer(Transformers.AliasToBean(typeof(DuCardType)));        card = query.List<DuCardType>();      }      return card;    }    /// <summary>    /// 存储过程    /// </summary>    /// <param name="id"></param>    /// <returns></returns>    public IList<DuCardType> CardTypeProcedures(int id)    {      //读到数据      IList<DuCardType> li = null;      NHibernate.ISession session = this.SessionFactory.GetCurrentSession();      string sql = @"exec proc_Select_DuCardType :CardTypeId";      IQuery query = session.CreateSQLQuery(sql)        .SetInt32("CardTypeId", id);      var list = query.List();      foreach (object[] item in list)      {        li =new List<DuCardType>();        var cardType = new DuCardType();        cardType.CardTypeId = (int)item[0];        cardType.CardTypeName = (string)item[1];        cardType.CardTypeColor = item[2].ToString();        li.Add(cardType);      }      //query = query.SetResultTransformer(Transformers.AliasToBean<OrderDto>());      //var list2 = query.List<OrderDto>();      var result = from cardType in li             orderby cardType.CardTypeName //名字排序             select cardType;      return li;    }

  

 /// <summary>  /// 2016 geovindu  /// </summary>  public static class IListConverDataTable  {    /// <summary>    ///     /// </summary>    /// <typeparam name="T"></typeparam>    /// <param name="data"></param>    /// <returns></returns>    public static DataTable ToDataTable<T>(this IList<T> data)    {      PropertyDescriptorCollection properties =        TypeDescriptor.GetProperties(typeof(T));      DataTable table = new DataTable();      foreach (PropertyDescriptor prop in properties)        table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);      foreach (T item in data)      {        DataRow row = table.NewRow();        foreach (PropertyDescriptor prop in properties)          row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;        table.Rows.Add(row);      }      return table;    }    /// <summary>    ///     /// </summary>    /// <param name="list"></param>    /// <returns></returns>    public static DataTable ToDataTable(IList list)    {      DataTable result = new DataTable();      if (list.Count > 0)      {        PropertyInfo[] propertys = list[0].GetType().GetProperties();        foreach (PropertyInfo pi in propertys)        {          result.Columns.Add(pi.Name, pi.PropertyType);        }        for (int i = 0; i < list.Count; i++)        {          ArrayList tempList = new ArrayList();          foreach (PropertyInfo pi in propertys)          {            object obj = pi.GetValue(list[i], null);            tempList.Add(obj);          }          object[] array = tempList.ToArray();          result.LoadDataRow(array, true);        }      }      return result;    }  }