<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping assembly="Domain" namespace="Domain" xmlns="urn:nhibernate-mapping-2.2">
<class name="DuCardType" table="DuCardType" lazy="true" >
<id name="CardTypeId" column="CardTypeId">
<generator class="identity" />
</id>
<property name="CardTypeName">
<column name="CardTypeName" sql-type="nvarchar" not-null="true" />
</property>
<property name="CardTypeColor">
<column name="CardTypeColor" sql-type="nvarchar" not-null="true" />
</property>
<sql-insert name="InsertDuCardType">exec proc_Insert_DuCardType ?,?,?</sql-insert>
<!--<sql-insert name="SqlInsertDuCardType">INSERT INTO DuCardType (CardTypeName, CardTypeColor) VALUES (?,?,?)</sql-insert>-->
<!--<sql-insert>
<sql-update>
<sql-delete>-->
</class>
</hibernate-mapping>
///<summary> /// 追加记录返回值 ///</summary> ///<param name="DuCardType">输入参数:DuCardTypeInfo</param> ///<param name="CardTypeId">返回参数:CardTypeId</param> ///<returns>返回是否添加的个数</returns> public int InsertDuCardTypeOutput(DuCardType cardType, out int cardTypeId) { int sid = 1; NHibernate.ISession session = this.SessionFactory.GetCurrentSession(); ITransaction tran = session.BeginTransaction(); try { session.Save(cardType); cardTypeId = cardType.CardTypeId; tran.Commit(); } catch (MappingException ex) { sid = 0; tran.Rollback(); throw ex; } return sid; //return dal.InsertDuCardTypeOutput(duCardType,out cardTypeId); }
///<summary>
///修改记录
///</summary>
///<param name="DuCardType">输入参数:DuCardTypeInfo</param>
///<returns>返回修改的多少记录数</returns>
public int UpdateDuCardType(DuCardType duCardType)
{
int sid = 1;
NHibernate.ISession session = this.SessionFactory.GetCurrentSession();
ITransaction tran = session.BeginTransaction();
try
{
session.Update(duCardType);
tran.Commit();
}
catch (Exception ex)
{
sid = 0;
tran.Rollback();
throw ex;
}
return sid;
//return dal.UpdateDuCardType(duCardType);
}
/// <summary>
/// 删除
/// </summary>
/// <param name="customer"></param>
/// <returns></returns>
public int Dell(DuCardType cardType)
{
int sid = 1;
NHibernate.ISession session = this.SessionFactory.GetCurrentSession();
ITransaction tran = session.BeginTransaction();
try
{
session.Delete(cardType);
tran.Commit();
}
catch (Exception ex)
{
sid = 0;
tran.Rollback();
throw ex;
}
return sid;
}
///<summary>
/// 删除记录
///</summary>
///<param name="cardTypeId">输入参数:CardTypeId</param>
///<returns>返回删除记录条数</returns>
public bool DeleteDuCardType(int cardTypeId)
{
bool isok = false;
try
{
IDbDataParameter[] par = new SqlParameter[]
{
new SqlParameter(),
};
par[0].ParameterName = "CardTypeId";
par[0].Value = cardTypeId;
isok = DBHelper.ExecuteNonQuery(SessionFactory, CommandType.StoredProcedure, "proc_Delete_DuCardType");
}
catch (NotImplementedException ex)
{
ex.Message.ToString();
}
return isok;
//return dal.DeleteDuCardType(cardTypeId);
}
/// <summary>
/// 删除
/// </summary>
/// <param name="customer"></param>
/// <returns></returns>
public int Dell(DuCardType cardType)
{
int sid = 1;
NHibernate.ISession session = this.SessionFactory.GetCurrentSession();
ITransaction tran = session.BeginTransaction();
try
{
session.Delete(cardType);
tran.Commit();
}
catch (Exception ex)
{
sid = 0;
tran.Rollback();
throw ex;
}
return sid;
}
///<summary>
/// 删除多条记录
///</summary>
///<param name="cardTypeId">输入参数:CardTypeId</param>
///<returns>返回删除多少记录</returns>
public bool DeleteDuCardTypeId(string cardTypeId)
{
bool isok = false;
try
{
IDbDataParameter[] par = new SqlParameter[]
{
new SqlParameter(),
};
par[0].ParameterName = "CardTypeId";
par[0].Value = cardTypeId;
isok = DBHelper.ExecuteNonQuery(SessionFactory, CommandType.StoredProcedure, "proc_Delete_DuCardTypeId");
}
catch (NotImplementedException ex)
{
ex.Message.ToString();
}
return isok;
//return dal.DeleteDuCardTypeId(cardTypeId);
}
/// <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;
}
GetNamedQuery: 存储过程
/// <summary>
/// 存储过程查询
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public IList<DuCardType> CardTypeProcedures(int id)
{
//读到数据
IList<DuCardType> li = null;
NHibernate.ISession session = this.SessionFactory.GetCurrentSession();
try
{
IQuery query = session.GetNamedQuery("proc_Select_DuCardType")
.SetParameter("CardTypeId", id)
.SetResultTransformer(
Transformers.AliasToBean(typeof(DuCardType)));
li = query.List<DuCardType>();
//var executor = new HibernateStoredProcedureExecutor(session);
//var lie = executor.ExecuteStoredProcedure<DuCardType>(
// "proc_Select_DuCardType", //find no
// new[]
// {
// new SqlParameter("CardTypeId", id),
// //new SqlParameter("startDate", startDate),
// // new SqlParameter("endDate", endDate),
// });
//li = lie.ToList();
//return li;
//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;
}
catch (MappingException ex)
{
ex.Message.ToString();
}
return li;
}
/// <summary>
/// Linq查询
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public IQueryable<DuCardType> getCardTypeID(int id)
{
NHibernate.ISession session = this.SessionFactory.GetCurrentSession();
var query = session.Query<DuCardType>();
var result = from cardType in query
where cardType.CardTypeId == id
select cardType;
return result;
}
/// <summary>
/// 存储过程分页
/// 涂聚文 2016.07.03
/// </summary>
/// <param name="strwhere">Where条件</param>
/// <param name="aecdesc">排序字段</param>
/// <param name="pageIndex">开始页码</param>
/// <param name="pageSize">页大小</param>
/// <param name="RecordCount">输出总页数</param>
/// <returns></returns>
public DataTable GetDataPage(string strwhere, string aecdesc, int pageIndex, int pageSize, out int RecordCount)
{
NHibernate.ISession session = this.SessionFactory.GetCurrentSession();
DataSet data = new DataSet();
try
{
//SessionFactory.ConnectionProvider.Driver
IDbCommand cmd = SessionFactory.OpenSession().Connection.CreateCommand();
//cmd.CommandText = "testaa";
//IDbConnection con = session.Connection;
//Type conType = con.GetType();
//string conTypeName = conType.FullName;
// if (!conTypeName.EndsWith("Connection"))
// throw new Exception("Unable to interpret connection type name: " + conTypeName);
//string adapterTypeName = conTypeName.Substring(0, conTypeName.Length - 10) + "DataAdapter";
IDbDataAdapter adapter = new SqlDataAdapter();
//IDbDataAdapter adapter = conType.Assembly.CreateInstance(adapterTypeName) as IDbDataAdapter;
//if (adapter == null)
// throw new Exception("Unable to load IDbDataAdapter: " + adapterTypeName);
// IDbCommand cmd = con.CreateCommand();
cmd.CommandText = "GetPagedRecordFor2005_2008";
cmd.CommandType = CommandType.StoredProcedure;
IDbDataParameter p = cmd.CreateParameter();
IDbDataParameter p1 = cmd.CreateParameter();
IDbDataParameter p2 = cmd.CreateParameter();
IDbDataParameter p3 = cmd.CreateParameter();
IDbDataParameter p4 = cmd.CreateParameter();
IDbDataParameter p5 = cmd.CreateParameter();
IDbDataParameter p6 = cmd.CreateParameter();
// IDbDataParameter p7 = cmd.CreateParameter();
if (string.IsNullOrEmpty(strwhere))
{
strwhere = " 1=1 ";
}
p.ParameterName = "Table";
p.Value = "DuCardType";
p1.ParameterName = "TIndex";
p1.Value = "CardTypeId";
p2.ParameterName = "Column";
p2.Value = " * ";
p3.ParameterName = "Sql";
p3.Value = strwhere;
p4.ParameterName = "PageIndex";
p4.Value = pageIndex;
p5.ParameterName = "PageSize";
p5.Value = pageSize;
p6.ParameterName = "Sort";
p6.Value = aecdesc;
//p1.ParameterName = "geovindu";
//p1.Size = 10;
//p1.Direction = ParameterDirection.Output;
//p7.ParameterName = "TotalRecords";
//p7.Size = 10;
//p7.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p);
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Parameters.Add(p4);
cmd.Parameters.Add(p5);
cmd.Parameters.Add(p6);
// cmd.Parameters.Add(p7);
adapter.SelectCommand = cmd;
adapter.Fill(data);
//RecordCount =(int)p7.Value;
cmd.Cancel();
cmd.Dispose();
RecordCount = data.Tables[0].Rows.Count;
}
catch (NotImplementedException ex)
{
RecordCount = 0;
ex.Message.ToString();
}
return data.Tables[0];
// return dal.GetDataPage(strwhere, aecdesc, pageIndex, pageSize, out RecordCount);
}
/// <summary>
/// 存储过程分页
/// 涂聚文 2016.07.03
/// </summary>
/// <param name="strwhere">Where条件</param>
/// <param name="aecdesc">排序字段</param>
/// <param name="pageIndex">开始页码</param>
/// <param name="pageSize">页大小</param>
/// <param name="RecordCount">输出总页数</param>
/// <returns></returns>
public DataTable GetDataPage(string strwhere, string aecdesc, int pageIndex, int pageSize, out int RecordCount)
{
NHibernate.ISession session = this.SessionFactory.GetCurrentSession();
DataSet data = new DataSet();
try
{
//SessionFactory.ConnectionProvider.Driver
IDbCommand cmd = SessionFactory.OpenSession().Connection.CreateCommand();
//cmd.CommandText = "testaa";
//IDbConnection con = session.Connection;
//Type conType = con.GetType();
//string conTypeName = conType.FullName;
// if (!conTypeName.EndsWith("Connection"))
// throw new Exception("Unable to interpret connection type name: " + conTypeName);
//string adapterTypeName = conTypeName.Substring(0, conTypeName.Length - 10) + "DataAdapter";
IDbDataAdapter adapter = new SqlDataAdapter();
//IDbDataAdapter adapter = conType.Assembly.CreateInstance(adapterTypeName) as IDbDataAdapter;
//if (adapter == null)
// throw new Exception("Unable to load IDbDataAdapter: " + adapterTypeName);
// IDbCommand cmd = con.CreateCommand();
//1.
// cmd.CommandText = "GetPagedRecordFor2005_2008";
// cmd.CommandType = CommandType.StoredProcedure;
// IDbDataParameter p = new SqlParameter();// cmd.CreateParameter();
// IDbDataParameter p1 = cmd.CreateParameter();
// IDbDataParameter p2 = cmd.CreateParameter();
// IDbDataParameter p3 = cmd.CreateParameter();
// IDbDataParameter p4 = cmd.CreateParameter();
// IDbDataParameter p5 = cmd.CreateParameter();
// IDbDataParameter p6 = cmd.CreateParameter();
//// IDbDataParameter p7 = cmd.CreateParameter();
// if (string.IsNullOrEmpty(strwhere))
// {
// strwhere = " 1=1 ";
// }
// p.ParameterName = "Table";
// p.Value = "DuCardType";
// p1.ParameterName = "TIndex";
// p1.Value = "CardTypeId";
// p2.ParameterName = "Column";
// p2.Value = " * ";
// p3.ParameterName = "Sql";
// p3.Value = strwhere;
// p4.ParameterName = "PageIndex";
// p4.Value = pageIndex;
// p5.ParameterName = "PageSize";
// p5.Value = pageSize;
// p6.ParameterName = "Sort";
// p6.Value = aecdesc;
// //p1.ParameterName = "geovindu";
// //p1.Size = 10;
// //p1.Direction = ParameterDirection.Output;
//输出值
// //p7.ParameterName = "TotalRecords";
// //p7.Size = 10;
// //p7.Direction = ParameterDirection.Output;
// cmd.Parameters.Add(p);
// cmd.Parameters.Add(p1);
// cmd.Parameters.Add(p2);
// cmd.Parameters.Add(p3);
// cmd.Parameters.Add(p4);
// cmd.Parameters.Add(p5);
// cmd.Parameters.Add(p6);
//// cmd.Parameters.Add(p7);
// adapter.SelectCommand = cmd;
// adapter.Fill(data);
// //RecordCount =(int)p7.Value;
// cmd.Cancel();
// cmd.Dispose();
//2.
IDbDataParameter[] par = new SqlParameter[]
{
new SqlParameter(), //表名
new SqlParameter(),//主键，可以带表头
new SqlParameter(),//读取字段
new SqlParameter(),//Where条件
new SqlParameter(),//开始页码
new SqlParameter(),//页大小
new SqlParameter(),//排序字段
};
if (string.IsNullOrEmpty(strwhere))
{
strwhere = " 1=1 ";
}
par[0].ParameterName = "Table"; //表名
par[0].Value="DuCardType";
par[1].ParameterName = "TIndex";
par[1].Value = "CardTypeId";
par[2].ParameterName = "Column";
par[2].Value = " * ";
par[3].ParameterName = "Sql";//查询条件
par[3].Value = strwhere;
par[4].ParameterName = "PageIndex";
par[4].Value = pageIndex;
par[5].ParameterName = "PageSize";
par[5].Value = pageSize;
par[6].ParameterName = "Sort";
par[6].Value = aecdesc;
data = DBHelper.ExecuteDataSet(SessionFactory, CommandType.StoredProcedure, "GetPagedRecordFor2005_2008", par);
RecordCount = data.Tables[0].Rows.Count;
}
catch (NotImplementedException ex)
{
RecordCount = 0;
ex.Message.ToString();
}
return data.Tables[0];
// return dal.GetDataPage(strwhere, aecdesc, pageIndex, pageSize, out RecordCount);
}
/// <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;
}
}
DBHelper.cs
//
private IDbConnection _idbConnection;
private IDataReader _iDataReader;
private IDbCommand _idbCommand;
private IDbTransaction _idbTransaction;
private IDbDataParameter[] _idbParameters;
/// <summary>
///
/// </summary>
public IDbConnection Connection
{
get { return _idbConnection; }
set { _idbConnection = value; }
}
public IDataReader DataReader
{
get { return _iDataReader; }
set { _iDataReader = value; }
}
public IDbCommand Command
{
get { return _idbCommand; }
set { _idbCommand = value; }
}
public IDbTransaction Transaction
{
get { return _idbTransaction; }
set { _idbTransaction = value; }
}
public IDbDataParameter[] Parameters
{
get { return _idbParameters; }
set { _idbParameters = value; }
}
public static IDbTransaction GetTransaction(NHibernate.ISession session)
{
IDbConnection iDbConnection = session.Connection;
IDbTransaction iDbTransaction = iDbConnection.BeginTransaction();
return iDbTransaction;
}
public static IDbDataParameter[] GetParameters(int paramsCount)
{
IDbDataParameter[] idbParams = new IDbDataParameter[paramsCount];
for (int i = 0; i < paramsCount; i++)
{
idbParams[i] = new SqlParameter();
}
return idbParams;
}
/// <summary>
///
/// </summary>
/// <param name="paramsCount"></param>
public void CreateParameters(int paramsCount)
{
Parameters = new IDbDataParameter[paramsCount];
Parameters = GetParameters(paramsCount);
}
public void AddParameters(int index, string paramName, object objValue)
{
if (index < Parameters.Length)
{
Parameters[index].ParameterName = paramName;
Parameters[index].Value = objValue;
}
}
/// <summary>
///
/// </summary>
/// <param name="sessionFactory"></param>
/// <param name="commandType"></param>
/// <param name="commandText"></param>
/// <returns></returns>
public IDataReader ExecuteReader(NHibernate.ISessionFactory sessionFactory, CommandType commandType, string commandText)
{
Command = sessionFactory.OpenSession().Connection.CreateCommand();
Command.Connection = Connection;
PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters);
DataReader = Command.ExecuteReader();
Command.Parameters.Clear();
return DataReader;
}
/// <summary>
///
/// </summary>
/// <param name="sessionFactory"></param>
/// <param name="commandType"></param>
/// <param name="commandText"></param>
/// <returns></returns>
public int ExecuteNonQuery(NHibernate.ISessionFactory sessionFactory, CommandType commandType, string commandText)
{
Command = sessionFactory.OpenSession().Connection.CreateCommand();
PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters);
int returnValue = Command.ExecuteNonQuery();
Command.Parameters.Clear();
return returnValue;
}
public object ExecuteScalar(NHibernate.ISessionFactory sessionFactory, CommandType commandType, string commandText)
{
Command = sessionFactory.OpenSession().Connection.CreateCommand();
PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters);
object returnValue = Command.ExecuteScalar();
Command.Parameters.Clear();
return returnValue;
}
/// <summary>
///
/// </summary>
/// <param name="sessionFactory"></param>
/// <param name="commandType"></param>
/// <param name="commandText"></param>
/// <returns></returns>
public DataSet ExecuteDataSet(NHibernate.ISessionFactory sessionFactory, CommandType commandType, string commandText)
{
Command = sessionFactory.OpenSession().Connection.CreateCommand();
PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters);
IDbDataAdapter dataAdapter = new SqlDataAdapter(); //根据不同的数据库选择不同的数据库
dataAdapter.SelectCommand = Command;
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
Command.Parameters.Clear();
return dataSet;
}
/// <summary>
///
/// </summary>
/// <param name="sessionFactory"></param>
/// <param name="commandType"></param>
/// <param name="commandText"></param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public DataSet ExecuteDataSet(NHibernate.ISessionFactory sessionFactory, CommandType commandType, string commandText, IDbDataParameter[] sqlParams)
{
Command = sessionFactory.OpenSession().Connection.CreateCommand();
PrepareCommand(Command, Connection, Transaction, commandType, commandText, sqlParams);
IDbDataAdapter dataAdapter = new SqlDataAdapter(); //根据不同的数据库选择不同的数据库
dataAdapter.SelectCommand = Command;
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
Command.Parameters.Clear();
return dataSet;
}
/// <summary>
///
/// </summary>
/// <param name="command"></param>
/// <param name="commandParameters"></param>
private void AttachParameters(IDbCommand command, IDbDataParameter[] commandParameters)
{
foreach (IDbDataParameter idbParameter in commandParameters)
{
if (idbParameter.Direction == ParameterDirection.InputOutput && idbParameter.Value == null)
{
idbParameter.Value = DBNull.Value;
}
command.Parameters.Add(idbParameter);
}
}
/// <summary>
///
/// </summary>
/// <param name="command"></param>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="commandType"></param>
/// <param name="commandText"></param>
/// <param name="commandParameters"></param>
private void PrepareCommand(IDbCommand command, IDbConnection connection, IDbTransaction transaction,
CommandType commandType, string commandText, IDbDataParameter[] commandParameters)
{
command.Connection = connection;
command.CommandText = commandText;
command.CommandType = commandType;
if (transaction != null)
{
command.Transaction = transaction;
}
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
}