datasethelper2.cs

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

/* ***************************************************************
 * Version		: 2.0
 * Copyright	: XingE Software Co.Ltd,ShangHai,China
 * Created on	: 2006-01-08
 * Author		: zhaopeng
 * Email		: [e-mail]
 * Comment		: 基于数据库表访问助手类
 * ***************************************************************/

using System;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using Microsoft.ApplicationBlocks.Data;

using Xinge.AspMng.Common;
using Xinge.AspMng.SystemFramework;

namespace Xinge.AspMng.DataAccess
{
    /// <summary>
    /// 帮助以表结构建立的DataSet存取数据库的助手类
    /// </summary>
    public static partial class DataSetHelper
    {
        ///// <summary>数据库连接字符串，静态属性，只读。</summary>
        ///// <value>数据库连接字符串。</value>
        //private static readonly string AspMngConfiguration.DBConString;

        //public static string DBConnStr
        //{
        //    get { return AspMngConfiguration.DBConString; }
        //}

		/// <summary>
		/// 拼装sql的where语句
		/// where:" Select * from "
		/// filter:"name='tom'"
		/// 结果：Select * from where name='tom'
		/// </summary>
		/// <param name="where">已有的sql语句</param>
		/// <param name="filter">要添加过滤条件</param>
		/// <returns>where + " AND " + filter </returns>
		public static string AddWhere( string where, string filter )
		{
			return AddWhere(where, filter, true, true);
		}
        public static string AddWhere(string where, string filter, bool needWhere)
		{
			return AddWhere(where, filter, needWhere, true);
		}

        public static string AddWhere(string where, string filter, bool needWhere, bool isAnd)
		{
			string relation;
			if ( isAnd )
			{
				relation = " AND ";
			}
			else
			{
				relation = " OR ";

			}

			string key = "";
			if ( needWhere )
			{
				key = " WHERE ";
			}
			if ( filter == null || filter.Length == 0 )
			{
				return where;
			}

			if ( where == null || where.Length == 0 )
			{

				return key + filter;
			}
			else
			{
				return where + relation + filter;
			}
		}


        //internal static SqlConnection GetDbConn()
        //{
        //    return DataSetHelper.GetDbConn(false);
        //}
        public static SqlConnection GetDbConn(bool open)
        {

            SqlConnection dbConn = new SqlConnection(AspMngConfiguration.DBConString);
            if (open)
            {
                dbConn.Open();
            }
            return dbConn;
        }

        //static DA()
        //{
        //    _dbConnStr = System.Configuration.ConfigurationManager.ConnectionStrings["AspDB"].ConnectionString;
        //    if ( String.IsNullOrEmpty(_dbConnStr) )
        //    {
        //        throw new ASPAutoException("获取数据库连接串错误。"); 
        //    }
        //}

		static public string GetCnfgValue( string key )
		{
			string value = string.Empty;
			try
			{
				value = System.Configuration.ConfigurationManager.AppSettings[key];
			}

			catch ( System.Configuration.ConfigurationException e )
			{
				throw e;
			}
			return value;
		}

		/// <summary>
		/// 提供数据访问层的FIllDataSet功能
		/// </summary>
		/// <param name="ds"></param>
		/// <param name="cmdSql"></param>
		/// <param name="tableNames"></param>
		/// <param name="commandParameters"></param>
        static public void FillDataset(DataSet ds, string cmdSql, string[] tableNames, params SqlParameter[] commandParameters)
		{
			try
			{
				if ( commandParameters == null )
				{
                    SqlHelper.FillDataset(AspMngConfiguration.DBConString, CommandType.Text, cmdSql, ds, tableNames);
				}
				else
				{
                    SqlHelper.FillDataset(AspMngConfiguration.DBConString, CommandType.Text, cmdSql, ds, tableNames, commandParameters);
				}
			}
			catch ( SqlException e )
			{
				throw e;
			}
		}

        static public object ExecuteScalar(string cmdSql)
		{
			return ExecuteScalar(cmdSql, null);
		}
        static public object ExecuteScalar(string cmdSql, params SqlParameter[] commandParameters)
		{
			//SqlConnection dbConn = DA.GetDbConn(false);
			//return ExecuteScalar(dbConn, cmdSql, commandParameters);
			return ExecuteScalar(cmdSql, CommandType.Text, commandParameters);
		}

        static  object ExecuteScalar(string cmdSql, CommandType commandType, params SqlParameter[] commandParameters)
		{
			SqlConnection dbConn = DataSetHelper.GetDbConn(false);
			return ExecuteScalar(dbConn, commandType, cmdSql, commandParameters);
		}

        static  object ExecuteScalar(SqlConnection dbConn, string cmdSql, params SqlParameter[] commandParameters)
		{
			return ExecuteScalar(dbConn, CommandType.Text, cmdSql, commandParameters);
		}

		static  object ExecuteScalar( SqlConnection dbConn, CommandType commandType, string cmdSql, params SqlParameter[] commandParameters )
		{
			try
			{
				if ( commandParameters == null )
				{
					return SqlHelper.ExecuteScalar(dbConn, commandType, cmdSql);
				}
				else
				{
					return SqlHelper.ExecuteScalar(dbConn, commandType, cmdSql, commandParameters);
				}
			}
			catch ( SqlException e )
			{
				throw e;
			}
		}

        static  object ExecuteScalar(SqlTransaction dbTrans, string cmdSql, params SqlParameter[] commandParameters)
		{
			try
			{
				if ( commandParameters == null )
				{
					return SqlHelper.ExecuteScalar(dbTrans, CommandType.Text, cmdSql);
				}
				else
				{
					return SqlHelper.ExecuteScalar(dbTrans, CommandType.Text, cmdSql, commandParameters);
				}
			}
			catch ( SqlException e )
			{
				throw e;
			}
		}

        static public int ExecuteNonQuery(string cmdSql)
		{
			return DataSetHelper.ExecuteNonQuery(cmdSql, null);
		}

        public static int ExecuteNonQuery(string sCmd, string ParnName, object value)
        {
            SqlParameter[] objList = new SqlParameter[] { new SqlParameter(ParnName, value) };
            return ExecuteNonQuery(sCmd, objList);
        }

        static public int ExecuteNonQuery(string cmdSql, params SqlParameter[] commandParameters)
		{
            return SqlHelper.ExecuteNonQuery(AspMngConfiguration.DBConString, CommandType.Text, cmdSql, commandParameters);
		}

        static  int ExecuteNonQuery(SqlTransaction dbTrans, string cmdSql)
		{
            return DataSetHelper.ExecuteNonQuery(dbTrans, cmdSql, null);
		}
        static  int ExecuteNonQuery(SqlTransaction dbTrans, string cmdSql, params SqlParameter[] commandParameters)
		{
			int result;
			try
			{
				if ( commandParameters == null )
				{
					result = SqlHelper.ExecuteNonQuery(dbTrans, CommandType.Text, cmdSql);
				}
				else
				{
					result = SqlHelper.ExecuteNonQuery(dbTrans, CommandType.Text, cmdSql, commandParameters);
				}
			}
			catch ( SqlException e )
			{
				throw e;
			}
			return result;
		}
        static  int ExecuteNonQuery(SqlTransaction dbTrans, string cmdSql, CommandType cmdType, params SqlParameter[] commandParameters)
		{
			int result;
			try
			{
				if ( commandParameters == null )
				{
					result = SqlHelper.ExecuteNonQuery(dbTrans, cmdType, cmdSql);
				}
				else
				{
					result = SqlHelper.ExecuteNonQuery(dbTrans, cmdType, cmdSql, commandParameters);
				}
			}
			catch ( SqlException e )
			{
				throw e;
			}

			return result;
		}


		/// <summary>
		/// 多个参数
		/// </summary>
		/// <param name="field">多个字段组成的串</param>
		/// <param name="dr"></param>
		/// <returns>SqlParameter[]</returns>
		/// <remarks>字段名和变量名必须相同</remarks>
        public static SqlParameter[] GetSqlParams(string field, DataRow dr)
		{
			SqlParameter[] arParams = null;
			if ( (field != null) && (field.Trim() != "") )
			{

				string[] fields = field.Split(new Char[] { ',' });
				arParams = new SqlParameter[fields.Length];
				int i = 0;
				foreach ( string fld in fields )
				{
					arParams[i] = new SqlParameter("@" + fld.Trim(), dr[fld.Trim()]);
					i++;
				}
			}
			return arParams;
		}
		/// <summary>
		/// 单个参数
		/// </summary>
		/// <param name="field">单个字段名</param>
		/// <param name="value"></param>
		/// <returns>SqlParameter[]</returns>
        public static SqlParameter[] GetSqlParams(string field, object value)
		{
			SqlParameter[] arParams = new SqlParameter[1];
			arParams[0] = new SqlParameter("@" + field.Trim(), value);

			return arParams;

		}
		/// <summary>
		/// 多个参数
		/// </summary>
		/// <param name="field">多个字段组成的串</param>
		/// <param name="values">字段的值一维array</param>
		/// <returns>SqlParameter[]</returns>
        public static SqlParameter[] GetSqlParams(string field, params object[] values)
		{
			SqlParameter[] arParams = null;
			if ( (field != null) && (field.Trim() != "") )
			{
				string[] fields = field.Split(new Char[] { ',' });
				arParams = new SqlParameter[fields.Length];
				//arParams
				int i = 0;
				foreach ( string fld in fields )
				{
					arParams[i] = new SqlParameter("@" + fld.Trim(), values[i]);
					i++;
				}
			}
			return arParams;
		}
        public static SqlParameter[] GetSqlParams(SqlParameter[] paras1, SqlParameter[] paras2)
		{
			SqlParameter[] arParams = null;

			int count = paras1.Length + paras1.Length;

			arParams = new SqlParameter[count];

			int i = 0;
			for ( int j = 0; j < paras1.Length; j++ )
			{
				arParams[i++] = paras1[j];
			}
			for ( int j = 0; j < paras2.Length; j++ )
			{