datasethelper2.cs

解决FTP、拨号、文件读取、Excel等等历程。很哭的彩旦、压缩等等等。

				arParams[i++] = paras2[j];
			}
			return arParams;
		}
		/// <summary>
		/// 事务处理开始的异常捕获
		/// </summary>
		/// <param name="conn"></param>
        static SqlTransaction BeginTransaction(SqlConnection conn)
		{
			SqlTransaction trans = null;
			try
			{
				trans = conn.BeginTransaction();
			}
			catch ( SqlException ex )
			{
				throw ex;
			}
			return trans;

		}
		/// <summary>
		/// 事务处理回滚的异常捕获
		/// </summary>
		/// <param name="trans"></param>
        static void Rollback(SqlTransaction trans)
		{
			try
			{
				trans.Rollback();
			}
			catch ( SqlException ex )
			{
				throw ex;
			}
		}


		/// <summary>
		/// 事务处理提交的异常捕获
		/// </summary>
		/// <param name="trans"></param>
        static void Commit(SqlTransaction trans)
		{
			try
			{
				trans.Commit();
			}
			catch ( SqlException ex )
			{
				throw ex;
			}

		}

		/// <summary>
		/// 检查是否在数据集的某个数据表中存在数据
		/// </summary>
		/// <param name="ds"></param>
		/// <param name="tableName"></param>
		/// <returns></returns>
        public static DataRow GetFirstRow(DataSet ds, string tableName)
		{

			DataRow dr = null;
			if ( ds.Tables[tableName].Rows.Count > 0 )
			{
				dr = ds.Tables[tableName].Rows[0];
			}
			else
			{
				throw new Exception(tableName + " DataSet表中没有任何数据");
			}

			return dr;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="dbTrans"></param>
		/// <param name="cmdSql"></param>
		/// <param name="cmdType"></param>
		/// <param name="commandParameters"></param>
		/// <returns></returns>
        static  SqlDataReader ExecuteReader(SqlTransaction dbTrans, string cmdSql, CommandType cmdType, params SqlParameter[] commandParameters)
		{
			SqlDataReader sdr;
			if ( commandParameters == null )
			{
				sdr = SqlHelper.ExecuteReader(dbTrans, cmdType, cmdSql);
			}
			else
			{
				sdr = SqlHelper.ExecuteReader(dbTrans, cmdType, cmdSql, commandParameters);
			}
			return sdr;
		}
        static public SqlDataReader ExecuteReader(string cmdSql, CommandType cmdType, params SqlParameter[] commandParameters)
		{
			SqlDataReader sdr;
			try
			{
				if ( commandParameters == null )
				{
                    sdr = SqlHelper.ExecuteReader(AspMngConfiguration.DBConString, cmdType, cmdSql);
				}
				else
				{
                    sdr = SqlHelper.ExecuteReader(AspMngConfiguration.DBConString, cmdType, cmdSql, commandParameters);
				}
			}
			catch ( SqlException e )
			{
				throw e;
			}
			return sdr;
		}

		/// <summary>
		/// 获取DataTable的分页数据将其填充
		/// </summary>
		/// <param name="data">被填充的DataTable</param>
		/// <param name="sortPage">分页信息Struct</param>
        static public void FillPageDataTable(DataTable data, ref SortPage sortPage)
		{
			SqlConnection dbConn = GetDbConn(true);

			SqlDataAdapter dap = new SqlDataAdapter();
			SqlCommand sc = new SqlCommand("SP_GetIDListFullFld", dbConn);

			sc.CommandType = CommandType.StoredProcedure;
			sc.Parameters.Add(new SqlParameter("@iPageSize", SqlDbType.Int, 4));
			sc.Parameters.Add(new SqlParameter("@iCurPage", SqlDbType.Int, 4));
			sc.Parameters.Add(new SqlParameter("@sField", SqlDbType.VarChar, 2000));
			sc.Parameters.Add(new SqlParameter("@sFrom", SqlDbType.VarChar, 2000));
			sc.Parameters.Add(new SqlParameter("@sPKField", SqlDbType.VarChar, 1000));
			sc.Parameters.Add(new SqlParameter("@iPKFieldType", SqlDbType.Bit, 1));
			sc.Parameters.Add(new SqlParameter("@sOrderField", SqlDbType.VarChar, 1000));
			sc.Parameters.Add(new SqlParameter("@bOrder", SqlDbType.Bit, 1));
            sc.Parameters.Add(new SqlParameter("@bDistinct", SqlDbType.Bit, 1));
			sc.Parameters.Add(new SqlParameter("@sWhere", SqlDbType.VarChar, 1000));
			sc.Parameters.Add(new SqlParameter("@sGroupBy", SqlDbType.VarChar, 1000));
			SqlParameter p1 = new SqlParameter("@iPageCount", SqlDbType.Int, 4);
			p1.Direction = ParameterDirection.Output;
			sc.Parameters.Add(p1);
			SqlParameter p2 = new SqlParameter("@iRecordCount", SqlDbType.BigInt, 8);
			p2.Direction = ParameterDirection.Output;
			sc.Parameters.Add(p2);

			dap.SelectCommand = sc;

			dap.SelectCommand.Parameters["@iPageSize"].Value = sortPage.pageSize;
			dap.SelectCommand.Parameters["@iCurPage"].Value = sortPage.curPage;
			dap.SelectCommand.Parameters["@sField"].Value = sortPage.Field;
			dap.SelectCommand.Parameters["@sFrom"].Value = sortPage.From;
			dap.SelectCommand.Parameters["@sPKField"].Value = sortPage.PKField;
			dap.SelectCommand.Parameters["@iPKFieldType"].Value = (int)sortPage.PKFieldType;
			dap.SelectCommand.Parameters["@sOrderField"].Value = sortPage.OrderField;
			dap.SelectCommand.Parameters["@bOrder"].Value = (int)sortPage.Order;
            dap.SelectCommand.Parameters["@bDistinct"].Value = (int)sortPage.bDistinct;
			dap.SelectCommand.Parameters["@sWhere"].Value = sortPage.Where;
			dap.SelectCommand.Parameters["@sGroupBy"].Value = sortPage.GroupBy;
			try
			{
				dap.Fill(data);
			}
			catch ( System.Exception e )
			{
				throw e;
			}

			sortPage.pageCount = System.Convert.ToInt16(dap.SelectCommand.Parameters["@iPageCount"].Value.ToString());
			sortPage.recordCount = System.Convert.ToInt32(dap.SelectCommand.Parameters["@iRecordCount"].Value.ToString());
			return;
		}


		/// <summary>
		/// 封装了ExecuteNonQuery带参和事物的重载
		/// </summary>
		/// <param name="cmdSql"></param>
		/// <param name="arParas"></param>
		/// <param name="err"></param>
		/// <param name="funcName"></param>
        static public int ExecuteNonQueryWithTrans(string cmdSql, SqlParameter[] arParas, ErrorNumber err, string funcName)
		{
			SqlConnection dbConn = DataSetHelper.GetDbConn(true);
			SqlTransaction trans = DataSetHelper.BeginTransaction(dbConn);
			int result = 0;
			try
			{
				result = DataSetHelper.ExecuteNonQuery(trans, cmdSql, arParas);
			}
			catch ( SqlException e )
			{
				DataSetHelper.Rollback(trans);
				throw e;
			}
			DataSetHelper.Commit(trans);
			dbConn.Close();
			return result;
		}
		/// <summary>
		/// 封装了ExecuteNonQuery带参和事物的重载
		/// </summary>
		/// <param name="cmdSql"></param>
		/// <param name="arParas"></param>
		/// <param name="err"></param>
		/// <param name="funcName"></param>
        static public int ExecuteNonQueryWithTrans(string cmdSql, SqlParameter[] arParas, ErrorNumber err, string funcName, SqlTransaction trans)
		{
			try
			{
				return DataSetHelper.ExecuteNonQuery(trans, cmdSql, arParas);
			}
			catch ( SqlException e )
			{
				DataSetHelper.Rollback(trans);
				throw e;
			}
		}


		/// <summary>
		/// 写入事件查看器
		/// </summary>
		/// <param name="message"></param>
		/// <param name="eventType"></param>
		/// <param name="eventID"></param>
		public static void WriteEventLog( string message, EventLogEntryType eventType, int eventID )
		{
			System.Diagnostics.EventLog.WriteEntry("App", message, eventType, eventID);
		}


		/// <summary>
		/// 
		/// </summary>
		/// <param name="dbTrans"></param>
		/// <param name="cmdSql"></param>
		/// <param name="cmdType"></param>
		/// <param name="commandParameters"></param>
		/// <returns></returns>
        static public DataSet ExecuteDataset(SqlTransaction dbTrans, string cmdSql, CommandType cmdType, params SqlParameter[] commandParameters)
		{
			DataSet sdr;
			try
			{
				if ( commandParameters == null )
				{
					sdr = SqlHelper.ExecuteDataset(dbTrans, cmdType, cmdSql);
				}
				else
				{
					sdr = SqlHelper.ExecuteDataset(dbTrans, cmdType, cmdSql, commandParameters);
				}
			}
			catch ( SqlException e )
			{
				throw e;
			}
			return sdr;
		}
        static public DataSet ExecuteDataset(string cmdSql, CommandType cmdType, params SqlParameter[] commandParameters)
		{
			DataSet sdr;
			try
			{
				if ( commandParameters == null )
				{
                    sdr = SqlHelper.ExecuteDataset(AspMngConfiguration.DBConString, cmdType, cmdSql);
				}
				else
				{
                    sdr = SqlHelper.ExecuteDataset(AspMngConfiguration.DBConString, cmdType, cmdSql, commandParameters);
				}
			}
			catch ( SqlException e )
			{
				throw e;
			}
			return sdr;
		}
        /// <summary>
        /// 存储过程执行
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        static public DataSet ExecProcedure(string spName, params SqlParameter[] commandParameters)
        {
            DataSet sdr;
            try
            {
                sdr = SqlHelper.ExecuteDataset(AspMngConfiguration.DBConString, spName, commandParameters);
            }
            catch (SqlException e)
            {
                throw e;
            }
            return sdr;
        }
        ///// <summary>
        ///// 只适合单表的
        ///// </summary>
        ///// <param name="spName"></param>
        ///// <param name="commandParameters"></param>
        ///// <returns></returns>
        //static public DataTable ExecuteDataset(string spName, params SqlParameter[] commandParameters)
        //{
        //    DataSet sdr;
        //    sdr = ExecuteDataset(spName, commandParameters);
        //    if (sdr != null)
        //        return sdr.Tables[0];
        //    return null;
        //}

		static public decimal GetKB( decimal b )
		{
			return (Math.Round((b / 1024), 2));
		}

		/// <summary>
		/// 转换通过 WMI  获得的邮箱信息中的时间格式到C#日期型
		/// </summary>
		/// <param name="mbiDateTime"></param>
		/// <returns></returns>
		static public DateTime GetDateTime( object mbiDateTime )
		{

			//日期在MailBox中的格式是"20050511091645.000777"

			DateTime dateTime = DateTime.MinValue;
			if ( mbiDateTime != null )
			{

				string sDateTime = mbiDateTime.ToString();
				int year = Convert.ToInt32(sDateTime.Substring(0, 4));
				int month = Convert.ToInt32(sDateTime.Substring(4, 2));
				int day = Convert.ToInt32(sDateTime.Substring(6, 2));
				int hour = Convert.ToInt32(sDateTime.Substring(8, 2));
				int min = Convert.ToInt32(sDateTime.Substring(10, 2));
				int sec = Convert.ToInt32(sDateTime.Substring(12, 2));
				dateTime = new DateTime(year, month, day, hour, min, sec);// = mbiDateTime;

			}

			return dateTime;
		}
    }
}