using System; using System.Data; using System.Data.Common; using System.Data.SqlClient; using System.Collections; using System.Collections.Generic; using Microsoft.Practices.EnterpriseLibrary; using Microsoft.Practices.ObjectBuilder; using Microsoft.Practices.EnterpriseLibrary.Common; using Microsoft.Practices.EnterpriseLibrary.Data; using DBML; ///Date Created: 2009年10月26日 ///Created By: Generated by James Deng namespace FIH { //数据访问类(抽象类、基类) public abstract class BaseUnitDALBase { #region IsExistDataInTable /// <summary> /// 判断表中是否含有此实例已赋值字段的数据记录,参数:通过表的所有主键 /// </summary> public bool IsExistDataInTable ( System.String UnitCode ) { Database db = DatabaseFactory.CreateDatabase(); //插入的SQL语句 string mySQL = " SELECT COUNT(*) FROM BaseUnit " + " WHERE " + " UnitCode = @UnitCode " ; DbCommand myDbCommand = db.GetSqlStringCommand(mySQL); //对参数赋值 db.AddInParameter(myDbCommand, "@UnitCode", DbType.String, UnitCode); DataSet dt = db.ExecuteDataSet(myDbCommand); int intResult = Convert.ToInt32(dt.Tables[0].Rows[0][0]); if( intResult > 0 ){ return true; }else{ return false; } } /// <summary> /// 判断表中是否含有此实例已赋值字段的数据记录,参数:数据表对应的实体类实例 /// </summary> public bool IsExistDataInTable ( BaseUnit myBaseUnit ) { return IsExistDataInTable ( myBaseUnit.UnitCode ); } #endregion IsExistDataInTable #region GetByKey /// <summary> /// 根据主键查询数据表的记录,参数:表的所有主键字段 /// </summary> public BaseUnit GetByKey ( System.String UnitCode ) { Database db = DatabaseFactory.CreateDatabase(); //插入的SQL语句 string mySQL = "SELECT " + " UnitCode, UnitName, Remark, IsValid, ModifyCode, ModifyDate " + "FROM BaseUnit " + "WHERE " + "UnitCode = @UnitCode " ; DbCommand myDbCommand = db.GetSqlStringCommand(mySQL); //对参数赋值 db.AddInParameter(myDbCommand, "@UnitCode", DbType.String, UnitCode); BaseUnit detail = new BaseUnit(); using( IDataReader dr = db.ExecuteReader(myDbCommand) ) { if( dr.Read() ){ if (!dr.IsDBNull(0)) detail.UnitCode = dr.GetString(0); if (!dr.IsDBNull(1)) detail.UnitName = dr.GetString(1); if (!dr.IsDBNull(2)) detail.Remark = dr.GetString(2); if (!dr.IsDBNull(3)) detail.IsValid = dr.GetBoolean(3); if (!dr.IsDBNull(4)) detail.ModifyCode = dr.GetString(4); if (!dr.IsDBNull(5)) detail.ModifyDate = dr.GetDateTime(5); } } return detail; } #endregion GetByKey #region FindAllByCondition /// <summary> /// 根据条件查询所有数据表的记录,参数:表的所有字段 /// </summary> public IList< BaseUnit > FindAllByCondition ( System.String UnitCode , System.String UnitName , System.String Remark , System.Boolean? IsValid , System.String ModifyCode , System.DateTime? ModifyDate ) { Database db = DatabaseFactory.CreateDatabase(); //SQL语句 string mySQL = ""; mySQL += " SELECT " + " UnitCode , UnitName , Remark , IsValid , ModifyCode , ModifyDate " + " FROM BaseUnit" + " WHERE " + " ((@UnitCode IS NULL) OR ( UnitCode LIKE @UnitCode )) AND" + " ((@UnitName IS NULL) OR ( UnitName LIKE @UnitName )) AND" + " ((@Remark IS NULL) OR ( Remark LIKE @Remark )) AND" + " ((@IsValid IS NULL) OR ( IsValid LIKE @IsValid )) AND" + " ((@ModifyCode IS NULL) OR ( ModifyCode LIKE @ModifyCode )) AND" + " ((@ModifyDate IS NULL) OR (DateDiff(d,@ModifyDate,ModifyDate)=0 )) " + " ORDER BY " + " UnitCode ASC " ; DbCommand myDbCommand = db.GetSqlStringCommand(mySQL); //对参数赋值 db.AddInParameter(myDbCommand, "@UnitCode", DbType.String, UnitCode); db.AddInParameter(myDbCommand, "@UnitName", DbType.String, UnitName); db.AddInParameter(myDbCommand, "@Remark", DbType.String, Remark); db.AddInParameter(myDbCommand, "@IsValid", DbType.Boolean, IsValid); db.AddInParameter(myDbCommand, "@ModifyCode", DbType.String, ModifyCode); db.AddInParameter(myDbCommand, "@ModifyDate", DbType.DateTime, ModifyDate); IList<BaseUnit> list = new List<BaseUnit>(); BaseUnit detail = null; using( IDataReader dr = db.ExecuteReader(myDbCommand) ) { while( dr.Read() ) { detail = new BaseUnit(); if (!dr.IsDBNull(0)) detail.UnitCode = dr.GetString(0); if (!dr.IsDBNull(1)) detail.UnitName = dr.GetString(1); if (!dr.IsDBNull(2)) detail.Remark = dr.GetString(2); if (!dr.IsDBNull(3)) detail.IsValid = dr.GetBoolean(3); if (!dr.IsDBNull(4)) detail.ModifyCode = dr.GetString(4); if (!dr.IsDBNull(5)) detail.ModifyDate = dr.GetDateTime(5); list.Add(detail); } dr.Close(); } return list; } /// <summary> /// 根据条件查询所有数据表的记录,参数:参数:数据表对应的实体类实例 /// </summary> public IList< BaseUnit > FindAllByCondition ( BaseUnit myBaseUnit ) { return FindAllByCondition ( myBaseUnit.UnitCode, myBaseUnit.UnitName, myBaseUnit.Remark, myBaseUnit.IsValid, myBaseUnit.ModifyCode, myBaseUnit.ModifyDate ); } #endregion FindAllByCondition #region Find /// <summary> /// 根据条件查询数据表的记录(从开始行----结束行),参数:表的所有字段,startRowIndex,maximumRows(和GridView的PageIndex,PageSize对应). /// 注:PageIndex表示GridView的第N页的第一行Index数 /// </summary> public IList< BaseUnit > Find ( System.String UnitCode, System.String UnitName, System.String Remark, System.Boolean? IsValid, System.String ModifyCode, System.DateTime? ModifyDate, int startRowIndex, int maximumRows ) { //判断是否为空格or NULL if ( !String.IsNullOrEmpty(UnitCode) ) {UnitCode = '%'+ UnitCode + '%';} else {UnitCode = null;}; if ( !String.IsNullOrEmpty(UnitName) ) {UnitName = '%'+ UnitName + '%';} else {UnitName = null;}; if ( !String.IsNullOrEmpty(Remark) ) {Remark = '%'+ Remark + '%';} else {Remark = null;}; if ( !String.IsNullOrEmpty(ModifyCode) ) {ModifyCode = '%'+ ModifyCode + '%';} else {ModifyCode = null;}; Database db = DatabaseFactory.CreateDatabase(); //SQL语句 string mySQL = ""; mySQL += " DECLARE @MaxUnitCode AS nvarchar(50)"; //得到@MAX if ((startRowIndex > 0) && (maximumRows > 0)) { mySQL += " SELECT " + " @MaxUnitCode = MAX(UnitCode) " + " FROM " + " (SELECT DISTINCT TOP (" + Convert.ToString(startRowIndex) +")" + " UnitCode " + " FROM BaseUnit" + " WHERE 1=1" + " AND ((@UnitCode IS NULL) OR ( UnitCode LIKE @UnitCode )) " + " AND ((@UnitName IS NULL) OR ( UnitName LIKE @UnitName )) " + " AND ((@Remark IS NULL) OR ( Remark LIKE @Remark )) " + " AND ((@IsValid IS NULL) OR ( IsValid LIKE @IsValid )) " + " AND ((@ModifyCode IS NULL) OR ( ModifyCode LIKE @ModifyCode )) " + " AND ((@ModifyDate IS NULL) OR (DateDiff(d,@ModifyDate,ModifyDate)=0 )) " + " ORDER BY " + " UnitCode ASC " + " ) AS T "; } //当前页中需要显示的数据 mySQL += " SELECT DISTINCT TOP ("+ Convert.ToString(maximumRows) +") " + " UnitCode , UnitName , Remark , IsValid , ModifyCode , ModifyDate " + " FROM BaseUnit" + " WHERE " + " ((@MaxUnitCode IS NULL) OR (UnitCode > @MaxUnitCode)) AND " + " ((@UnitCode IS NULL) OR ( UnitCode LIKE @UnitCode )) AND" + " ((@UnitName IS NULL) OR ( UnitName LIKE @UnitName )) AND" + " ((@Remark IS NULL) OR ( Remark LIKE @Remark )) AND" + " ((@IsValid IS NULL) OR ( IsValid LIKE @IsValid )) AND" + " ((@ModifyCode IS NULL) OR ( ModifyCode LIKE @ModifyCode )) AND" + " ((@ModifyDate IS NULL) OR (DateDiff(d,@ModifyDate,ModifyDate)=0 )) " + " ORDER BY " + " UnitCode ASC " ; DbCommand myDbCommand = db.GetSqlStringCommand(mySQL); //对参数赋值 db.AddInParameter(myDbCommand, "@UnitCode", DbType.String, UnitCode); db.AddInParameter(myDbCommand, "@UnitName", DbType.String, UnitName); db.AddInParameter(myDbCommand, "@Remark", DbType.String, Remark); db.AddInParameter(myDbCommand, "@IsValid", DbType.Boolean, IsValid); db.AddInParameter(myDbCommand, "@ModifyCode", DbType.String, ModifyCode); db.AddInParameter(myDbCommand, "@ModifyDate", DbType.DateTime, ModifyDate); IList<BaseUnit> list = new List<BaseUnit>(); BaseUnit detail = null; using( IDataReader dr = db.ExecuteReader(myDbCommand) ) { while( dr.Read() ) { detail = new BaseUnit(); if (!dr.IsDBNull(0)) detail.UnitCode = dr.GetString(0); if (!dr.IsDBNull(1)) detail.UnitName = dr.GetString(1); if (!dr.IsDBNull(2)) detail.Remark = dr.GetString(2); if (!dr.IsDBNull(3)) detail.IsValid = dr.GetBoolean(3); if (!dr.IsDBNull(4)) detail.ModifyCode = dr.GetString(4); if (!dr.IsDBNull(5)) detail.ModifyDate = dr.GetDateTime(5); list.Add(detail); } dr.Close(); } return list; } /// <summary> /// 根据条件查询数据表的记录(从开始行----结束行),参数:数据表对应的实体类实例,startRowIndex,maximumRows(和GridView的PageIndex,PageSize对应) /// 注:PageIndex表示GridView的第N页的第一行Index数 /// </summary> public IList< BaseUnit > Find ( BaseUnit myBaseUnit, int startRowIndex, int maximumRows ) { return Find ( myBaseUnit.UnitCode, myBaseUnit.UnitName, myBaseUnit.Remark, myBaseUnit.IsValid, myBaseUnit.ModifyCode, myBaseUnit.ModifyDate, startRowIndex, maximumRows ); } #endregion Find #region FindCount /// <summary> /// 根据条件查询数据表的记录,参数:表的所有字段,startRowIndex,maximumRows(和GridView的PageIndex,PageSize对应) /// 注:PageIndex表示GridView的第N页的第一行Index数 /// </summary> public int FindCount ( System.String UnitCode, System.String UnitName, System.String Remark, System.Boolean? IsValid, System.String ModifyCode, System.DateTime? ModifyDate, int startRowIndex, int maximumRows ) { //判断是否为空格or NULL if ( !String.IsNullOrEmpty(UnitCode) ) {UnitCode = '%'+ UnitCode + '%';} else {UnitCode = null;}; if ( !String.IsNullOrEmpty(UnitName) ) {UnitName = '%'+ UnitName + '%';} else {UnitName = null;}; if ( !String.IsNullOrEmpty(Remark) ) {Remark = '%'+ Remark + '%';} else {Remark = null;}; if ( !String.IsNullOrEmpty(ModifyCode) ) {ModifyCode = '%'+ ModifyCode + '%';} else {ModifyCode = null;}; Database db = DatabaseFactory.CreateDatabase(); //SQL语句 string mySQL = " SELECT COUNT(DISTINCT UnitCode) as recordCount " + " FROM BaseUnit" + " WHERE 1=1" + " AND ((@UnitCode IS NULL) OR ( UnitCode LIKE @UnitCode )) " + " AND ((@UnitName IS NULL) OR ( UnitName LIKE @UnitName )) " + " AND ((@Remark IS NULL) OR ( Remark LIKE @Remark )) " + " AND ((@IsValid IS NULL) OR ( IsValid LIKE @IsValid )) " + " AND ((@ModifyCode IS NULL) OR ( ModifyCode LIKE @ModifyCode )) " + " AND ((@ModifyDate IS NULL) OR (DateDiff(d,@ModifyDate,ModifyDate)=0 )) " ; DbCommand myDbCommand = db.GetSqlStringCommand(mySQL); //对参数赋值 db.AddInParameter(myDbCommand, "@UnitCode", DbType.String, UnitCode); db.AddInParameter(myDbCommand, "@UnitName", DbType.String, UnitName); db.AddInParameter(myDbCommand, "@Remark", DbType.String, Remark); db.AddInParameter(myDbCommand, "@IsValid", DbType.Boolean, IsValid); db.AddInParameter(myDbCommand, "@ModifyCode", DbType.String, ModifyCode); db.AddInParameter(myDbCommand, "@ModifyDate", DbType.DateTime, ModifyDate); DataSet dt = db.ExecuteDataSet(myDbCommand); int intResult = Convert.ToInt32(dt.Tables[0].Rows[0][0]); return intResult; } /// <summary> /// 根据条件查询数据表的记录,参数:数据表对应的实体类实例,startRowIndex,maximumRows(和GridView的PageIndex,PageSize对应) /// 注:PageIndex表示GridView的第N页的第一行Index数 /// </summary> public int FindCount ( BaseUnit myBaseUnit, int startRowIndex, int maximumRows ) { return FindCount ( myBaseUnit.UnitCode, myBaseUnit.UnitName, myBaseUnit.Remark, myBaseUnit.IsValid, myBaseUnit.ModifyCode, myBaseUnit.ModifyDate, startRowIndex, maximumRows ); } #endregion Find #region Insert /// <summary> /// 插入一条数据记录,参数:数据表对应的所有字段 /// </summary> public int Insert ( System.String UnitCode , System.String UnitName , System.String Remark , System.Boolean? IsValid , System.String ModifyCode , System.DateTime? ModifyDate ) { Database db = DatabaseFactory.CreateDatabase(); //插入的SQL语句 string mySQL = "INSERT INTO BaseUnit" + "( UnitCode, UnitName, Remark, IsValid, ModifyCode, ModifyDate )" + " VALUES " + "(@UnitCode, @UnitName, @Remark, @IsValid, @ModifyCode, @ModifyDate )"; DbCommand myDbCommand = db.GetSqlStringCommand(mySQL); //对参数赋值 db.AddInParameter(myDbCommand, "@UnitCode", DbType.String, UnitCode); db.AddInParameter(myDbCommand, "@UnitName", DbType.String, UnitName); db.AddInParameter(myDbCommand, "@Remark", DbType.String, Remark); db.AddInParameter(myDbCommand, "@IsValid", DbType.Boolean, IsValid); db.AddInParameter(myDbCommand, "@ModifyCode", DbType.String, ModifyCode); db.AddInParameter(myDbCommand, "@ModifyDate", DbType.DateTime, ModifyDate); return db.ExecuteNonQuery(myDbCommand); } /// <summary> /// 插入一条数据记录,参数:数据表对应的实体类实例 /// </summary> public int Insert ( BaseUnit myBaseUnit ) { return Insert ( myBaseUnit.UnitCode, myBaseUnit.UnitName, myBaseUnit.Remark, myBaseUnit.IsValid, myBaseUnit.ModifyCode, myBaseUnit.ModifyDate ); } #endregion Insert #region Update /// <summary> /// 修改一条数据记录,参数:数据表对应的所有字段 /// </summary> public int Update ( System.String UnitCode , System.String UnitName , System.String Remark , System.Boolean? IsValid , System.String ModifyCode , System.DateTime? ModifyDate ) { Database db = DatabaseFactory.CreateDatabase(); //插入的SQL语句 string mySQL = "UPDATE BaseUnit " + "SET " + "UnitName = @UnitName, " + "Remark = @Remark, " + "IsValid = @IsValid, " + "ModifyCode = @ModifyCode, " + "ModifyDate = @ModifyDate " + "WHERE " + "UnitCode = @UnitCode " ; DbCommand myDbCommand = db.GetSqlStringCommand(mySQL); //对参数赋值 db.AddInParameter(myDbCommand, "@UnitCode", DbType.String, UnitCode); db.AddInParameter(myDbCommand, "@UnitName", DbType.String, UnitName); db.AddInParameter(myDbCommand, "@Remark", DbType.String, Remark); db.AddInParameter(myDbCommand, "@IsValid", DbType.Boolean, IsValid); db.AddInParameter(myDbCommand, "@ModifyCode", DbType.String, ModifyCode); db.AddInParameter(myDbCommand, "@ModifyDate", DbType.DateTime, ModifyDate); return db.ExecuteNonQuery(myDbCommand); } /// <summary> /// 插入一条数据记录,参数:数据表对应的实体类实例 /// </summary> public int Update ( BaseUnit myBaseUnit ) { return Update ( myBaseUnit.UnitCode, myBaseUnit.UnitName, myBaseUnit.Remark, myBaseUnit.IsValid, myBaseUnit.ModifyCode, myBaseUnit.ModifyDate ); } #endregion Update #region Delete /// <summary> /// 删除一条数据记录,参数:数据表对应的所有主键 /// </summary> public int Delete ( System.String UnitCode ) { Database db = DatabaseFactory.CreateDatabase(); //插入的SQL语句 string mySQL = "DELETE FROM BaseUnit " + "WHERE " + "UnitCode = @UnitCode " ; DbCommand myDbCommand = db.GetSqlStringCommand(mySQL); //对参数赋值 db.AddInParameter(myDbCommand, "@UnitCode", DbType.String, UnitCode); return db.ExecuteNonQuery(myDbCommand); } /// <summary> /// 删除一条数据记录,参数:数据表对应的实体类实例 /// </summary> public int Delete ( BaseUnit myBaseUnit ) { return Delete ( myBaseUnit.UnitCode ); } #endregion Delete } }