csharp: Procedure with DAO and DAL
来源：互联网 发布：php 伪造来路 编辑：程序博客网 时间：2024/06/01 19:04
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 DuCardTypeSET[CardTypeName]=@CardTypeName ,[CardTypeColor]=@CardTypeColorwhere[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)asDELETEDuCardTypeWHERECardTypeId = @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 DuCardTypeGODAL
///<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); } 0 0
- csharp: Procedure with DAO and DAL
- csharp: Procedure with DAO and DAL
- csharp: MySQL Stored Procedure using DAL
- csharp: Oracle Stored Procedure DAL using ODP.NET
- csharp: Oracle Stored Procedure DAL using ODP.NET
- dal&dao
- Dao DAL BIZ
- DAO与DAL的区别
- Implementing a Recursive Procedure with IA32 and Y86 Assembly Code
- Lazy Initialization and the DAO pattern with Hibernate and Spring
- DAL、DAO、ORM、Active Record辨析
- DAL、DAO、ORM、Active Record辨析
- DAL、DAO、ORM、Active Record辨析
- DAL、DAO、ORM、Active Record辨析
- Getting SQLite metadata with Csharp
- Getting SQLite metadata with csharp
- Oracle Procedure and Function
- oracle procedure and webservice
- Light oj 1200 - Thief 《完全背包》
- 使用TensorFlow创建自己的手写识别引擎
- 使用git提交文件修改提交者姓名
- C++学习手记
- 试试
- csharp: Procedure with DAO and DAL
- 敏感词过滤与DFA算法Trie树
- Android事件总线EventBus的用法详解
- webvirtmgr推荐
- svm从理论到opencv实践
- 图像处理类期刊
- csharp: Procedure with DAO and DAL
- android 6 中init.rc的生成过程
- 使用Python学习selenium测试工具-3：unittest库介绍
