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

[ASP.net教程]csharp: Procedure with DAO(Data Access Object) and DAL(Data Access Layer)


sql script code:

CREATE TABLE DuCardType(	CardTypeId INT IDENTITY(1,1) PRIMARY KEY,	CardTypeName NVARCHAR(100) NOT NULL,     --卡类名称	CardTypeColor NVARCHAR(50) NOT NULL		   --卡颜色(或样本))GOIF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Insert_DuCardType')DROP PROCEDURE proc_Insert_DuCardTypeGOCREATE PROCEDURE proc_Insert_DuCardType(	@CardTypeName NVarChar(200),	@CardTypeColor NVarChar(100)) AS IF NOT EXISTS (SELECT * FROM DuCardType WHERE [CardTypeName]=@CardTypeName)BEGININSERT INTO DuCardType(	[CardTypeName] ,	[CardTypeColor])	VALUES(	@CardTypeName ,	@CardTypeColor)ENDGOIF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Insert_DuCardTypeOutput')DROP PROCEDURE proc_Insert_DuCardTypeOutputGOCREATE PROCEDURE proc_Insert_DuCardTypeOutput(	@CardTypeName NVarChar(200),	@CardTypeColor NVarChar(100), 	@CardTypeId int output)ASIF NOT EXISTS (SELECT * FROM DuCardType WHERE [CardTypeName]=@CardTypeName)BEGININSERT INTO DuCardType(	[CardTypeName] ,	[CardTypeColor])	VALUES(	@CardTypeName ,	@CardTypeColor)select @CardTypeId=@@IDENTITYENDGOIF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Update_DuCardType')DROP PROCEDURE proc_Update_DuCardTypeGOCREATE PROCEDURE proc_Update_DuCardType(	@CardTypeId Int,	@CardTypeName NVarChar(200),	@CardTypeColor NVarChar(100))ASIF NOT EXISTS (SELECT * FROM DuCardType WHERE [CardTypeName]=@CardTypeName)BEGINUPDATE DuCardType	SET		[CardTypeName]=@CardTypeName ,		[CardTypeColor]=@CardTypeColor	where		[CardTypeId]=@CardTypeIdENDGOIF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Update_DuCardTypeField')DROP PROCEDURE proc_Update_DuCardTypeFieldGOCREATE PROCEDURE proc_Update_DuCardTypeField(	@CardTypeId Int,	@FieldValue NVARCHAR(1500))ASDECLARE @sql NVARCHAR(4000)SET @sql='update DuCardType set '+@FieldValue+' WHERE CardTypeId ='+ @CardTypeIdEXEC(@sql)GOIF EXISTS (select * from sysobjects where [name] = 'proc_Delete_DuCardType')DROP PROCEDURE proc_Delete_DuCardTypeGOCREATE PROCEDURE proc_Delete_DuCardType(	@CardTypeId Int)asDELETE	DuCardType	WHERE		CardTypeId = @CardTypeIdGOIF EXISTS (select * from sysobjects where [name] = 'proc_Delete_DuCardTypeId')DROP PROCEDURE proc_Delete_DuCardTypeIdGOCREATE PROCEDURE proc_Delete_DuCardTypeId(	@CardTypeId varchar(2000))asDECLARE @strsql varchar(3000)SET @strsql='DELETE DuCardType WHERE CardTypeId in('+@CardTypeId+')'EXEC(@strsql)GOIF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Select_DuCardType')DROP PROCEDURE proc_Select_DuCardTypeGOCREATE PROCEDURE proc_Select_DuCardType(	@CardTypeId Int)ASSELECT * FROM DuCardType WHERE CardTypeId = @CardTypeIdGOIF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Select_DuCardTypeExists')DROP PROCEDURE proc_Select_DuCardTypeExistsGOCREATE PROCEDURE proc_Select_DuCardTypeExists(	@CardTypeId Int)ASSELECT count(1) as H FROM DuCardType WHERE CardTypeId = @CardTypeIdGOIF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Select_DuCardTypeCount')DROP PROCEDURE proc_Select_DuCardTypeCountGOCREATE PROCEDURE proc_Select_DuCardTypeCount(	@where NVARCHAR(1000))ASDECLARE @sql NVARCHAR(4000)SET @sql='select count(*) as H from DuCardType 'IF @where<>''	SET @sql=@sql+@whereEXEC(@sql)GOIF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Select_DuCardTypeTitle')DROP PROCEDURE proc_Select_DuCardTypeTitleGOCREATE PROCEDURE proc_Select_DuCardTypeTitle(	@FieldName NVARCHAR(100),	@CardTypeId Int)ASDECLARE @sql NVARCHAR(4000)SET @sql='select top 1 '+@FieldName+' from DuCardType WHERE CardTypeId ='+ CAST(@CardTypeId AS VARCHAR(50))EXEC(@sql)GOIF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Select_DuCardTypeFuzzySearch')DROP PROCEDURE proc_Select_DuCardTypeFuzzySearchGOCREATE PROCEDURE proc_Select_DuCardTypeFuzzySearch(	@FieldList NVARCHAR(1000),	@where NVARCHAR(2000))ASDECLARE @sql NVARCHAR(4000)IF(@where<>'')	SET @where=' WHERE '+@where IF(@FieldList='')	SET @FieldList=' * ' SET @sql='select '+@FieldList+' from DuCardType '+@whereEXEC(@sql)GOIF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Select_DuCardTypeAll')DROP PROCEDURE proc_Select_DuCardTypeAllGOCREATE PROCEDURE proc_Select_DuCardTypeAllASSELECT * FROM DuCardTypeGO

  DAL

///<summary>		/// 追加记录		///</summary>		///<param name="DuCardType">输入参数:DuCardTypeInfo</param>		///<returns>返回添加的记录条数</returns>		public int InsertDuCardType(DuCardTypeInfo duCardType)		{			int ret = 0;			try			{				SqlParameter[] par = new SqlParameter[]{				new SqlParameter("@CardTypeName",SqlDbType.NVarChar,200),				new SqlParameter("@CardTypeColor",SqlDbType.NVarChar,100),				};				par[0].Value = duCardType.CardTypeName;				par[1].Value = duCardType.CardTypeColor;				ret = DBHelper.ExecuteSql("proc_Insert_DuCardType", CommandType.StoredProcedure, par);			}			catch (SqlException ex)			{				throw ex;			}			return ret;		}		///<summary>		/// 追加记录返回值		///</summary>		///<param name="DuCardType">输入参数:DuCardTypeInfo</param>		///<param name="CardTypeId">返回参数:CardTypeId</param>		///<returns>返回是否添加的个数</returns>		public int InsertDuCardTypeOutput (DuCardTypeInfo duCardType,out int cardTypeId)		{			int ret = 0;			cardTypeId= 0;			try			{				SqlParameter[] par = new SqlParameter[]{				new SqlParameter("@CardTypeName",SqlDbType.NVarChar,200),				new SqlParameter("@CardTypeColor",SqlDbType.NVarChar,100),				new SqlParameter("@CardTypeId",SqlDbType.Int,8),				};				par[0].Value = duCardType.CardTypeName;				par[1].Value = duCardType.CardTypeColor;				par[2].Direction = ParameterDirection.Output;				ret = DBHelper.ExecuteSql("proc_Insert_DuCardTypeOutput", CommandType.StoredProcedure, par);							if(ret>0)				{									cardTypeId=(int)par[2].Value;							}			}			catch (SqlException ex)			{				throw ex;			}			return ret;		}		///<summary>		///修改记录		///</summary>		///<param name="DuCardType">输入参数:DuCardTypeInfo</param>		///<returns>返回修改的多少记录数</returns>		public int UpdateDuCardType(DuCardTypeInfo duCardType)		{			int ret = 0;			try			{				SqlParameter[] par = new SqlParameter[]{				new SqlParameter("@CardTypeId",SqlDbType.Int,4),				new SqlParameter("@CardTypeName",SqlDbType.NVarChar,200),				new SqlParameter("@CardTypeColor",SqlDbType.NVarChar,100),				};				par[0].Value = duCardType.CardTypeId;				par[1].Value = duCardType.CardTypeName;				par[2].Value = duCardType.CardTypeColor;				ret = DBHelper.ExecuteSql("proc_Update_DuCardType", CommandType.StoredProcedure, par);			}			catch (SqlException ex)			{				throw ex;			}			return ret;		}		///<summary>		/// 删除记录		///</summary>		///<param name="cardTypeId">输入参数:CardTypeId</param>		///<returns>返回删除记录条数</returns>		public bool DeleteDuCardType(int cardTypeId)		{			bool ret = false;			try			{				SqlParameter par =new SqlParameter("@CardTypeId",cardTypeId);				int temp = 0 ;				temp = DBHelper.ExecuteSql("proc_Delete_DuCardType", CommandType.StoredProcedure, par);				if(temp!=0)				{					ret = true;				}			}			catch (SqlException ex)			{				throw ex;			}			return ret;		}		///<summary>		/// 删除多条记录		///</summary>		///<param name="cardTypeId">输入参数:CardTypeId</param>		///<returns>返回删除多少记录</returns>		public bool DeleteDuCardTypeId(string cardTypeId)		{			bool ret = false;			try			{				SqlParameter par =new SqlParameter("@CardTypeId",cardTypeId);				int temp = 0 ;				temp = DBHelper.ExecuteSql("proc_Delete_DuCardTypeId", CommandType.StoredProcedure, par);				if(temp!=0)				{					ret = true;				}			}			catch (SqlException ex)			{				throw ex;			}			return ret;		}		///<summary>		/// 查询记录		///</summary>		///<param name="cardTypeId">输入参数:CardTypeId</param>		///<returns>返回DuCardTypeInfo</returns>		public DuCardTypeInfo SelectDuCardType(int cardTypeId)		{			DuCardTypeInfo duCardType = null;			try			{				SqlParameter par =new SqlParameter("@CardTypeId",cardTypeId);				using (SqlDataReader reader = DBHelper.GetReader("proc_Select_DuCardType", CommandType.StoredProcedure, par))				{					if (reader.Read())					{						duCardType = new DuCardTypeInfo();						duCardType.CardTypeId =(!DBNull.Equals(reader["CardTypeId"],null))? (int) reader["CardTypeId"]:0;						duCardType.CardTypeName =(!DBNull.Equals(reader["CardTypeName"],null))? (string) reader["CardTypeName"].ToString():"";						duCardType.CardTypeColor =(!DBNull.Equals(reader["CardTypeColor"],null))? (string) reader["CardTypeColor"].ToString():"";											}				}			}			catch (SqlException ex)			{				throw ex;			}			return duCardType;		}				///<summary>		/// 查询所有记录		///</summary>		///<param name="cardTypeId">无输入参数</param>		///<returns>返回表所有记录(List)DuCardTypeInfo</returns>		public List<DuCardTypeInfo> SelectDuCardTypeAll()		{			List<DuCardTypeInfo> list = new List<DuCardTypeInfo>();			DuCardTypeInfo duCardType = null;			try			{				using (SqlDataReader reader = DBHelper.GetReader("proc_Select_DuCardTypeAll", CommandType.StoredProcedure, null))				{					while (reader.Read())					{						duCardType = new DuCardTypeInfo();						duCardType.CardTypeId =(!DBNull.Equals(reader["CardTypeId"],null))? (int) reader["CardTypeId"]:0;						duCardType.CardTypeName =(!DBNull.Equals(reader["CardTypeName"],null))? (string) reader["CardTypeName"].ToString():"";						duCardType.CardTypeColor =(!DBNull.Equals(reader["CardTypeColor"],null))? (string) reader["CardTypeColor"].ToString():"";						list.Add(duCardType);											}				}			}			catch (SqlException ex)			{				throw ex;			}			return list;		}		///<summary>		/// 查询所有记录		///</summary>		///<param name="cardTypeId">无输入参数</param>		///<returns>返回(DataTable)DuCardType表所有记录</returns>		public DataTable SelectDuCardTypeDataTableAll()		{			DataTable dt = new DataTable();			try			{				using (DataTable reader = DBHelper.GetTable("proc_Select_DuCardTypeAll", CommandType.StoredProcedure, null))				{					dt = reader;															}			}			catch (SqlException ex)			{				throw ex;			}			return dt;		}		/// <summary>		/// SQL script查询分页		/// </summary>		/// <param name="pageSize">每页页数</param>		/// <param name="currentPage">当前页码</param>		/// <param name="strWhere">查询的条件</param>		/// <param name="filedOrder">排序字段</param>		/// <param name="recordCount">每页的记录数</param>		/// <returns></returns>		public DataSet GetPageList(int pageSize, int currentPage, string strWhere, string filedOrder, out int recordCount)		{			int topNum = pageSize * currentPage;			StringBuilder strSql = new StringBuilder();			strSql.Append("select * FROM DuCardType");			if (strWhere.Trim() !="")			{				strSql.Append(" where " + strWhere);			}			recordCount = Convert.ToInt32(DBHelper.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));			return DBHelper.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, currentPage, strSql.ToString(), filedOrder));		}		/// <summary>		/// 模糊查询		/// </summary>		/// <param name="filedlist">显示字段列表</param>		/// <param name="strkey">输入的关键字</param>		/// <returns></returns>		public DataTable GetDataTableDuCardTypeFuzzySearch(string filedlist, string strkey)		{			DataTable dt = new DataTable();			try			{				SqlParameter[] par = new SqlParameter[]{					new SqlParameter("@FieldList",SqlDbType.NVarChar,2000),//可以考虑类型					new SqlParameter("@where",SqlDbType.NVarChar,1000),				};				par[0].Value = filedlist;				par[1].Value = StringConvert.getStrWhere("CardTypeName,CardTypeColor",strkey);//获取字段为字符串的字段列表(varchar,nvarchar,text,ntext)				using (DataTable reader = DBHelper.GetTable("proc_Select_DuCardTypeFuzzySearch", CommandType.StoredProcedure, par))				{					dt = reader;				}			}			catch (SqlException ex)			{				throw ex;			}			return dt;			}		/// <summary>		/// 是否存在该记录		/// </summary>		/// <param name="Id"></param>		/// <returns></returns>		public bool Exists(int Id)		{			bool isok = false;			int count = 0;			try			{				SqlParameter par = new SqlParameter("@Id", Id);				using (SqlDataReader reader = DBHelper.GetReader("proc_Select_DuCardTypeExists", CommandType.StoredProcedure, par))				{					if (reader.Read())					{						count = (!DBNull.Equals(reader["H"], null)) ? (int)reader["H"] : 0;						if (count > 0)							isok = true;						}					}				}				catch (SqlException ex)				{					throw ex;				}				return isok;		}		/// <summary>		/// 返回数据总数		/// </summary>		/// <param name="strWhere">查询条件</param>		/// <returns></returns>		public int GetCount(string where)		{			int count = 0;			try			{				SqlParameter par = new SqlParameter("@where", where);				using (SqlDataReader reader = DBHelper.GetReader("proc_Select_DuCardTypeCount", CommandType.StoredProcedure, par))				{					if (reader.Read())					{						count = (!DBNull.Equals(reader["H"], null)) ? (int)reader["H"] : 0;					}				}			}			catch (SqlException ex)			{				throw ex;			}			return count;		}		/// <summary>		/// 更新一列数据		/// </summary>		/// <param name="Id"></param>		/// <param name="strValue">字段名=值</param>		/// <returns></returns>		public int UpdateField(int Id, string fieldValue)		{			int ret = 0;			try			{				SqlParameter[] par = new SqlParameter[]{					new SqlParameter("@Id",SqlDbType.Int,4),					new SqlParameter("@FieldValue",SqlDbType.NVarChar,2000),				};				par[0].Value = Id;				par[1].Value = fieldValue;				ret = DBHelper.ExecuteSql("proc_Update_DuCardTypeField", CommandType.StoredProcedure, par);			}			catch (SqlException ex)			{				throw ex;			}			return ret;		}		/// <summary>		/// 返回指字字段的字串		/// </summary>		/// <param name="Id"></param>		/// <param name="fieldName">字段名</param>		/// <returns></returns>		public string GetTitle(int Id, string fieldName)		{			string title = string.Empty;			try			{				SqlParameter[] par = new SqlParameter[]{					new SqlParameter("@FieldName",SqlDbType.NVarChar,2000),					new SqlParameter("@Id",SqlDbType.Int,4),				};				par[0].Value = fieldName;				par[1].Value = Id;				using (SqlDataReader reader = DBHelper.GetReader("proc_Select_DuConfigTitle", CommandType.StoredProcedure, par))				{					title =(!DBNull.Equals(reader[0],null))? (string) reader[0]:"";				}			}			catch (SqlException ex)			{				throw ex;			}			return title;		}		/// <summary>		/// 存储过程分页		/// </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)		{			DataTable dt = new DataTable();			try			{				SqlParameter[] par = new SqlParameter[]{					new SqlParameter("@Table",SqlDbType.NVarChar,400), //表名					new SqlParameter("@TIndex",SqlDbType.NVarChar,1000),//主键,可以带表头					new SqlParameter("@Column",SqlDbType.NVarChar,200),//读取字段					new SqlParameter("@Sql",SqlDbType.NVarChar,1600),//Where条件					new SqlParameter("@PageIndex",SqlDbType.Int, 8),//开始页码					new SqlParameter("@PageSize",SqlDbType.Int, 8),//页大小					new SqlParameter("@Sort",SqlDbType.NVarChar,1600),//排序字段					};				if (string.IsNullOrEmpty(strwhere))				{					strwhere = " 1=1 ";				}				par[0].Value = "DuCardType"; //表名				par[1].Value = "CardTypeId";				par[2].Value = " * ";				par[3].Value = strwhere;//查询条件				par[4].Value = pageIndex;				par[5].Value = pageSize;				par[6].Value = aecdesc;				using (DataTable reader = DBHelper.GetTable("GetPagedRecordFor2005_2008", CommandType.StoredProcedure, par))				{					dt = reader;					RecordCount = dt.Rows.Count;				}			}			catch (SqlException ex)			{				RecordCount = 0;				throw ex;			}			return dt;		}			}

  DAO:

 ///<summary>    /// 追加记录    ///</summary>    ///<param name="DuCardType">输入参数:DuCardTypeInfo</param>    ///<returns>返回添加的记录条数</returns>    public int InsertDuCardType(DuCardType duCardType)    {      int saveid = 0;      //DuCardType info = null;      NHibernate.ISession session = this.SessionFactory.GetCurrentSession();      ITransaction tran = session.BeginTransaction();      try      {        if (!object.Equals(session, null))        {          //2.          session.Save(duCardType);                    //1          //string sql = @"exec GetDepartmentId @Id=:Id";// @"exec GetDepartmentId :Id";          //IQuery query = session.CreateSQLQuery(sql) //CreateSQLQuery(sql) //GetNamedQuery("GetDepartmentId")                       //    .SetParameter("Id", 1)                     //    .SetResultTransformer(          //    Transformers.AliasToBean(typeof(DuCardTypeInfo)));          //var clients = query.UniqueResult();// query.List<Department>().ToList(); //不能强制转化          //info = (DuCardTypeInfo)clients; //无法将类型为“System.Object[]”的对象强制转换为类型          //关联的数据也查出来了            //string sql = @"exec proc_Insert_DuCardType @CardTypeName = :CardTypeName,@CardTypeColor = :CardTypeColor ";// @"exec GetDepartmentId :Id";          //IQuery query = session.CreateSQLQuery(sql) //CreateSQLQuery(sql) //GetNamedQuery("GetDepartmentId")                       //    .SetParameter("CardTypeName", duCardType.CardTypeName)          //    .SetParameter("CardTypeColor", duCardType.CardTypeColor)          //    .SetResultTransformer(          //    Transformers.AliasToBean(typeof(DuCardType)));          //IQuery query = session.CreateSQLQuery(sql)                        //    .SetParameter("CardTypeName", duCardType.CardTypeName)          //    .SetParameter("CardTypeColor", duCardType.CardTypeColor);          //query.SetParameter("CardTypeName", duCardType.CardTypeName, NHibernateUtil.String);          //query.SetParameter("CardTypeColor", duCardType.CardTypeColor, NHibernateUtil.String);          //query.ExecuteUpdate();          //IQuery query = session.CreateSQLQuery(sql)          //  .SetString("CardTypeName", duCardType.CardTypeName)          //  .SetString("CardTypeColor", duCardType.CardTypeColor);          //.ExecuteUpdate();          //IQuery query = session.CreateSQLQuery("UPDATE DuCardType as c set c.CardTypeName =?,c.CardTypeColor=? where c.CardTypeId = ? ")          //  .SetString(0, duCardType.CardTypeName)          //  .SetString(1, duCardType.CardTypeColor)          //  .executeUpdate();          //          IQuery query = session.CreateSQLQuery(@"insert into DuCardType (CardTypeName, CardTypeColor)          //                    values (:cardTypeName, :cardTypeColor)")          //             .SetParameter("cardTypeName", duCardType.CardTypeName, NHibernateUtil.String)          //             .SetParameter("cardTypeColor", duCardType.CardTypeColor, NHibernateUtil.String);          // IQuery query = session.CreateSQLQuery(@"insert into DuCardType (CardTypeName, CardTypeColor) vlaues('" + duCardType.CardTypeName + "','" + duCardType.CardTypeColor + "')");          //int result = query.ExecuteUpdate();          // saveid = query.ExecuteUpdate();          tran.Commit();          saveid = 1;        }      }      catch (MappingException ex)      {        saveid = 0;        tran.Rollback();        ex.Message.ToString();      }      return saveid;      //return dal.InsertDuCardType(duCardType);    }    ///<summary>    /// 存储过程追加记录    ///</summary>    ///<param name="DuCardType">输入参数:DuCardTypeInfo</param>    ///<returns>返回添加的记录条数</returns>    public int InsertDuCardTypeProc(DuCardType duCardType)    {      int ret = 0;      try      {        IDbDataParameter[] par = new SqlParameter[]        {          new SqlParameter(), 					new SqlParameter(),        };        par[0].ParameterName = "@CardTypeName";        par[0].Value = duCardType.CardTypeName;        par[1].ParameterName = "@CardTypeColor";        par[1].Value = duCardType.CardTypeColor;        ret = DBHelper.ExecuteNonQuery(SessionFactory, CommandType.StoredProcedure, "proc_Insert_DuCardType", par);              }      catch (NotImplementedException ex)      {        ex.Message.ToString();      }      return ret;    }    ///<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>    ///<param name="CardTypeId">返回参数:CardTypeId</param>    ///<returns>返回是否添加的个数</returns>    public int InsertDuCardTypeOutputProc(DuCardType duCardType, out int cardTypeId)    {      int rets = 0;      cardTypeId = 0;      try      {        IDbDataParameter[] par = new SqlParameter[]        {          new SqlParameter(), 					new SqlParameter(),          new SqlParameter(),        };        par[0].ParameterName = "@CardTypeName";         par[0].Value = duCardType.CardTypeName;        par[1].ParameterName = "@CardTypeColor";        par[1].Value = duCardType.CardTypeColor;        par[2].ParameterName = "@CardTypeId";        par[2].Size = 10;        par[2].DbType = DbType.Int32;        par[2].Direction = ParameterDirection.Output;        rets = DBHelper.ExecuteNonQuery(SessionFactory, CommandType.StoredProcedure, "proc_Insert_DuCardTypeOutput", par);        if (rets > 0)        {          cardTypeId = int.Parse(par[2].Value.ToString());        }            }      catch (NotImplementedException ex)      {        rets = 0;        ex.Message.ToString();      }      return rets;    }    ///<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="DuCardType">输入参数:DuCardTypeInfo</param>    ///<returns>返回修改的多少记录数</returns>    public int UpdateDuCardTypeProc(DuCardType duCardType)    {      int rets = 0;          try      {        IDbDataParameter[] par = new SqlParameter[]        {          new SqlParameter(), 					new SqlParameter(),          new SqlParameter(),        };        par[0].ParameterName = "@CardTypeId";        par[0].Value = duCardType.CardTypeId;        par[1].ParameterName = "@CardTypeName";        par[1].Value = duCardType.CardTypeName;        par[2].ParameterName = "@CardTypeColor";        par[2].Value = duCardType.CardTypeColor;        rets = DBHelper.ExecuteNonQuery(SessionFactory, CommandType.StoredProcedure, "proc_Update_DuCardType", par);             }      catch (NotImplementedException ex)      {        rets = 0;        ex.Message.ToString();      }      return rets;    }    /// <summary>    /// 查询所有    /// </summary>    /// <returns></returns>    public IQueryable<DuCardType> GetAllCardTypes()    {      NHibernate.ISession session = this.SessionFactory.GetCurrentSession();      //if (!object.Equals(session, null))      //{      //  string s = "";      //}      var query = session.Query<DuCardType>();      var result = from cardType in query             orderby cardType.CardTypeName //名字排序             select cardType;      return result;    }    /// <summary>    /// Sql查询    /// </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 card = session.CreateQuery("from DuCardType c where c.CardTypeName='" + firstname + "'")        //  .List<DuCardType>();        //写法2:位置型参数        //card = session.CreateQuery("from DuCardType c where c.CardTypeId=:CardTypeId")        //  .SetInt32("CardTypeId", id)        //  .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>());        //card = 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();      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>    /// 删除记录    ///</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 int DeleteDuCardTypeId(string cardTypeId)    {      int isok = 0;            try      {        IDbDataParameter[] par = new SqlParameter[]        {          					new SqlParameter(),        };        par[0].ParameterName = "@CardTypeId";        par[0].Value = cardTypeId;        isok = DBHelper.ExecuteNonQuery(SessionFactory, CommandType.StoredProcedure, "proc_Delete_DuCardTypeId",par);      }      catch (NotImplementedException ex)      {        ex.Message.ToString();      }      return isok;      //return dal.DeleteDuCardTypeId(cardTypeId);    }    ///<summary>    /// 查询记录    ///</summary>    ///<param name="cardTypeId">输入参数:CardTypeId</param>    ///<returns>返回DuCardTypeInfo</returns>    public DuCardType SelectDuCardType(int cardTypeId)    {      NHibernate.ISession session = this.SessionFactory.GetCurrentSession();      //var query = session.CreateQuery("");      var query =session.Query<DuCardType>() ;// ;session.Get<DuCardType>(cardTypeId)      var result = from cardType in query             where cardType.CardTypeId == cardTypeId             select cardType;      List<DuCardType> ty = query.ToList<DuCardType>();      return ty[0];      //return dal.SelectDuCardType(cardTypeId);    }    ///<summary>    /// 查询所有记录    ///</summary>    ///<param name="cardTypeId">无输入参数</param>    ///<returns>返回表所有记录(List)DuCardTypeInfo</returns>    public List<DuCardType> SelectDuCardTypeAll()    {      NHibernate.ISession session = this.SessionFactory.GetCurrentSession();      var query = session.Query<DuCardType>();      var result = from cardType in query             select cardType;      List<DuCardType> list = result.ToList<DuCardType>();      return list;      //return dal.SelectDuCardTypeAll();    }    ///<summary>    /// 查询所有记录    ///</summary>    ///<param name="cardTypeId">无输入参数</param>    ///<returns>返回(DataTable)DuCardType表所有记录</returns>    public DataTable SelectDuCardTypeDataTableAll()    {      DataTable dt = new DataTable();      try      {        dt = DBHelper.ExecuteDataTable(SessionFactory, CommandType.StoredProcedure, "proc_Select_DuCardTypeAll");      }      catch (NotImplementedException ex)      {        ex.Message.ToString();      }      return dt;      //return dal.SelectDuCardTypeDataTableAll();    }    /// <summary>    /// SQL script查询分页    /// </summary>    /// <param name="pageSize">每页页数</param>    /// <param name="currentPage">当前页码</param>    /// <param name="strWhere">查询的条件</param>    /// <param name="filedOrder">排序字段</param>    /// <param name="recordCount">每页的记录数</param>    /// <returns></returns>    public DataSet GetPageList(int pageSize, int currentPage, string strWhere, string filedOrder, out int recordCount)    {      DataSet ds = new DataSet();      recordCount = 0;      try      {        int topNum = pageSize * currentPage;        StringBuilder strSql = new StringBuilder();        strSql.Append("select * FROM DuCardType");        if (strWhere.Trim() != "")        {          strSql.Append(" where " + strWhere);        }        recordCount = Convert.ToInt32(DBHelper.ExecuteScalar(SessionFactory, CommandType.Text,PagingHelper.CreateCountingSql(strSql.ToString())));        ds=DBHelper.ExecuteDataSet(SessionFactory, CommandType.Text, PagingHelper.CreatePagingSql(recordCount, pageSize, currentPage, strSql.ToString(), filedOrder));      }      catch (NotImplementedException ex)      {        ex.Message.ToString();      }      return ds;            //return dal.GetPageList(pageSize, currentPage, strWhere, filedOrder, out recordCount);    }    /// <summary>    /// 模糊查询    /// </summary>    /// <param name="filedlist">显示字段列表</param>    /// <param name="strkey">输入的关键字</param>    /// <returns></returns>    public DataTable GetDataTableDuCardTypeFuzzySearch(string filedlist, string strkey)    {      DataTable dt = new DataTable();      try      {        IDbDataParameter[] par = new SqlParameter[]        {          new SqlParameter(), 					new SqlParameter(),        };        par[0].ParameterName = "@FieldList"; //表名        par[0].Value = filedlist;        par[1].ParameterName = "where";        par[1].Value = StringConvert.getStrWhere("CardTypeName,CardTypeColor", strkey); ;        dt = DBHelper.ExecuteDataTable(SessionFactory, CommandType.StoredProcedure, "proc_Select_DuCardTypeFuzzySearch", par);      }      catch (NotImplementedException ex)      {        ex.Message.ToString();      }      return dt;      //return dal.GetDataTableDuCardTypeFuzzySearch(filedlist, strkey);    }    /// <summary>    /// 是否存在该记录    /// </summary>    /// <param name="Id"></param>    /// <returns></returns>    public bool Exists(int Id)    {      bool isok=false;      int i = 0;            try      {        IDbDataParameter[] par = new SqlParameter[]        {          					new SqlParameter(),        };        par[0].ParameterName = "@Id";        par[0].Value = Id;        using (IDataReader reader = DBHelper.ExecuteReader(SessionFactory, CommandType.StoredProcedure, "proc_Select_DuCardTypeExists", par))        {            i = (!DBNull.Equals(reader["H"], null)) ? (int)reader["H"] : 0;						if (i > 0)							isok = true;					        }         }      catch (NotImplementedException ex)      {        ex.Message.ToString();      }      return isok;      //return dal.Exists(Id);    }    /// <summary>    /// 返回数据总数    /// </summary>    /// <param name="strWhere">查询条件</param>    /// <returns></returns>    public int GetCount(string where)    {      int i = 0;      try      {        IDbDataParameter[] par = new SqlParameter[]        {          					new SqlParameter(),        };        par[0].ParameterName = "@where";        par[0].Value = where;        object obj = DBHelper.ExecuteScalar(SessionFactory, CommandType.StoredProcedure, "proc_Select_DuCardTypeCount", par);        i = (int)obj;      }      catch (NotImplementedException ex)      {        ex.Message.ToString();      }      return i;      //return dal.GetCount(where);    }    /// <summary>    /// 更新一列数据    /// </summary>    /// <param name="Id"></param>    /// <param name="strValue">字段名=值</param>    /// <returns></returns>    public int UpdateField(int Id, string fieldValue)    {      int ret = 0;      try      {        IDbDataParameter[] par = new SqlParameter[]        {          new SqlParameter(), 					new SqlParameter(),        };        par[0].ParameterName = "@Id"; //表名        par[0].Value =Id ;        par[1].ParameterName = "@FieldValue";        par[1].Value = fieldValue;        ret = DBHelper.ExecuteNonQuery(SessionFactory, CommandType.StoredProcedure, "proc_Update_DuCardTypeField", par);              }      catch (NotImplementedException ex)      {        ex.Message.ToString();      }      return ret;      //return dal.UpdateField(Id, fieldValue);    }    /// <summary>    /// 返回指字字段的字串    /// </summary>    /// <param name="Id"></param>    /// <param name="fieldName">字段名</param>    /// <returns></returns>    public string GetTitle(int Id, string fieldName)    {      string title = string.Empty;      try      {        IDbDataParameter[] par = new SqlParameter[]        {          new SqlParameter(), 					new SqlParameter(),        };        par[0].ParameterName = "@FieldName";         par[0].Value = fieldName;        par[1].ParameterName = "@Id";        par[1].Value = Id;        object obj = DBHelper.ExecuteScalar(SessionFactory, CommandType.StoredProcedure, "proc_Select_DuConfigTitle", par);        title = obj.ToString();      }      catch (NotImplementedException ex)      {        ex.Message.ToString();      }      return title;      //return dal.GetTitle(Id, fieldName);    }    /// <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);    }