www.pudn.com > whpower2007-8-24.rar > SqlDataConnect.cs, change:2007-06-12,size:10483b


using System; 
using System.Xml; 
using System.Data; 
using System.Data.SqlClient; 
using System.Configuration; 
using System.Collections; 
 
 
/// <summary> 
/// SqlDataConnect 的摘要说明 
/// SqlParameterCollection 
/// </summary> 
namespace com.etong.SqlDataConnect 
{ 
	    /// <summary> 
    /// MSSqlDataAccess 的摘要说明。 
    /// </summary> 
    public class MSSqlDataAccess 
    { 
 
        private SqlConnection _dbconn;     ///定义连接; 
        private SqlTransaction trans = null; ///定义事务;  
 
 
        /// <summary> 
        /// 构造函数 
        /// </summary> 
        public MSSqlDataAccess(SqlConnection conn) 
        { 
            this._dbconn = conn; 
        } 
        public MSSqlDataAccess(string connstr) 
        { 
            this._dbconn = new SqlConnection(connstr); 
        } 
 
        public MSSqlDataAccess(int i) 
        { 
            string sqlconnstr; 
            if (i == 2) 
            { 
                sqlconnstr = System.Configuration.ConfigurationManager.AppSettings["SqlConnection2"]; 
                //sqlconnstr = System.Configuration.ConfigurationManager.ConnectionStrings["connectionstring2"].ConnectionString; 
            } 
            else 
            { 
                sqlconnstr = System.Configuration.ConfigurationManager.AppSettings["SqlConnection"]; 
                //sqlconnstr = System.Configuration.ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString; 
            } 
            this._dbconn = new SqlConnection(sqlconnstr); 
        } 
 
        /// <summary> 
        /// 取得连接 
        /// </summary> 
        public SqlConnection DbConnection 
        { 
            get 
            { 
                return _dbconn; 
            } 
        } 
 
        /// <summary> 
        /// 打开连接 
        /// </summary> 
        public void Open() 
        { 
            DbConnection.Open(); 
        } 
 
        /// <summary> 
        /// 关闭连接 
        /// </summary> 
        public void Close() 
        { 
            DbConnection.Close(); 
        } 
 
        /// <summary> 
        /// 判断连接是否打开 
        /// </summary> 
        public bool IsClosed() 
        { 
            return DbConnection.State.Equals(ConnectionState.Closed); 
        } 
 
        /// <summary> 
        /// 开始事务,并返回事务对象 
        /// </summary> 
        public SqlTransaction BeginTransaction() 
        { 
            trans = this._dbconn.BeginTransaction(); 
            return trans; 
        } 
 
        public int ExecuteNonQuery(CommandType commandType, string commandText, QueryParameterCollection commandParameters) 
        { 
            SqlCommand cmd = new SqlCommand(); 
            PrepareCommand(cmd, commandType, commandText, commandParameters); 
            int tmpValue = cmd.ExecuteNonQuery(); 
            cmd.Parameters.Clear(); 
            return tmpValue; 
        } 
        public DataSet ExecuteDataset(CommandType commandType, string commandText, QueryParameterCollection commandParameters, DataSet ds, string tableName) 
        { 
            SqlCommand cmd = new SqlCommand(); 
            PrepareCommand(cmd, commandType, commandText, commandParameters); 
 
            SqlDataAdapter da = new SqlDataAdapter(cmd); 
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey; 
            if (Object.Equals(tableName, null) || (tableName.Length < 1)) 
                da.Fill(ds); 
            else 
                da.Fill(ds, tableName); 
 
            cmd.Parameters.Clear(); 
            return ds; 
        } 
 
        public SqlDataReader ExecuteReader(CommandType commandType, string commandText, QueryParameterCollection commandParameters) 
        { 
            SqlCommand cmd = new SqlCommand(); 
            PrepareCommand(cmd, commandType, commandText, commandParameters); 
            SqlDataReader dr = cmd.ExecuteReader(); 
            cmd.Parameters.Clear(); 
            return dr; 
 
        } 
 
        public object ExecuteScalar(CommandType commandType, string commandText, QueryParameterCollection commandParameters) 
        { 
            SqlCommand cmd = new SqlCommand(); 
            PrepareCommand(cmd, commandType, commandText, commandParameters); 
            object tmpValue = cmd.ExecuteScalar(); 
            cmd.Parameters.Clear(); 
            return tmpValue; 
        } 
 
        public XmlReader ExecuteXmlReader(CommandType commandType, string commandText, QueryParameterCollection commandParameters) 
        { 
            SqlCommand cmd = new SqlCommand(); 
            PrepareCommand(cmd, commandType, commandText, commandParameters); 
            XmlReader reader = cmd.ExecuteXmlReader(); 
            cmd.Parameters.Clear(); 
            return reader; 
        } 
 
        public DataView ExecuteDataView(CommandType commandType, string commandText, QueryParameterCollection commandParameters) 
        { 
            DataSet ds = new DataSet(); 
            SqlCommand cmd = new SqlCommand(); 
            PrepareCommand(cmd, commandType, commandText, commandParameters); 
 
            SqlDataAdapter da = new SqlDataAdapter(cmd); 
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey; 
            da.Fill(ds, "MyTableName"); 
 
            cmd.Parameters.Clear(); 
            return ds.Tables["MyTableName"].DefaultView; 
 
            //ds = this.ExecuteDataset(commandType,commandText,commandParameters,ds,"MyTableName"); 
            //return ds.Tables["MyTableName"].DefaultView; 
        } 
 
        private void PrepareCommand(SqlCommand cmd, CommandType commandType, string commandText, QueryParameterCollection commandParameters) 
        { 
            cmd.CommandType = commandType; 
            cmd.CommandText = commandText; 
            cmd.Connection = this._dbconn; 
            if (trans != null) cmd.Transaction = trans; 
            if ((commandParameters != null) && (commandParameters.Count > 0)) 
            { 
                for (int i = 0; i < commandParameters.Count; i++) 
                { 
                    cmd.Parameters.AddWithValue(commandParameters[i].ParameterName, commandParameters[i].Value); 
                } 
            } 
        } 
 
    } 
 
    /// <summary> 
    /// QueryParameter 的摘要说明。 
    /// </summary> 
    public sealed class QueryParameter : MarshalByRefObject 
    { 
        public QueryParameter(string ParameterName, object Value) 
        { 
            this.m_ParameterName = ParameterName; 
            this.m_Value = Value; 
        } 
 
        private string m_ParameterName; 
        public string ParameterName 
        { 
            get { return this.m_ParameterName; } 
            set { this.m_ParameterName = value; } 
        } 
 
        private object m_Value; 
        public object Value 
        { 
            get { return this.m_Value; } 
            set { this.m_Value = value; } 
        } 
    } 
 
    /// <summary> 
	/// QueryParameterCollection 的摘要说明。 
	/// </summary> 
    public sealed class QueryParameterCollection : MarshalByRefObject 
    { 
        int intitialCapacity = 10; 
        public QueryParameterCollection() 
        { 
        } 
        public QueryParameterCollection(int initCapacity) 
        { 
            intitialCapacity = initCapacity; 
        } 
        private ArrayList items; 
        private ArrayList ArrayList() 
        { 
            if (this.items == null) 
            { 
                this.items = new ArrayList(intitialCapacity); 
            } 
            return this.items; 
        } 
        public int Count 
        { 
            get 
            { 
                if (this.items == null) 
                { 
                    return 0; 
                } 
                return this.items.Count; 
 
            } 
        } 
 
        public QueryParameter Add(QueryParameter param) 
        { 
            this.ArrayList().Add(param); 
            return param; 
        } 
 
        public QueryParameter Add(string ParameterName, object Value) 
        { 
            return this.Add(new QueryParameter(ParameterName, Value)); 
        } 
 
        private void Replace(int index, QueryParameter newValue) 
        { 
            this.Validate(index, newValue); 
            this.items[index] = newValue; 
        } 
 
        public QueryParameter this[int index] 
        { 
            get 
            { 
                this.RangeCheck(index); 
                return ((QueryParameter)this.items[index]); 
            } 
 
            set 
            { 
                this.RangeCheck(index); 
                this.Replace(index, value); 
            } 
        } 
 
        public QueryParameter this[string ParameterName] 
        { 
            get 
            { 
                int num1 = this.RangeCheck(ParameterName); 
                return ((QueryParameter)this.items[num1]); 
            } 
 
            set 
            { 
                int num1 = this.RangeCheck(ParameterName); 
                this.Replace(num1, value); 
            } 
        } 
 
        private void ValidateType(object Value) 
        { 
        } 
        private void Validate(int index, QueryParameter Value) 
        { } 
 
        private void RangeCheck(int index) 
        { 
            if ((index < 0) || (this.Count <= index)) 
            { 
                throw new IndexOutOfRangeException("Number " + index.ToString() + " is out of Range"); 
 
            } 
        } 
 
        private int RangeCheck(string ParameterName) 
        { 
            int num1; 
            num1 = this.IndexOf(ParameterName); 
            if (num1 < 0) 
            { 
                throw new IndexOutOfRangeException("ParameterName " + ParameterName + " dose not exist"); 
            } 
            return num1; 
        } 
 
        public int IndexOf(string ParameterName) 
        { 
            int index = -1; 
            if (this.items != null) 
            { 
                for (int i = 0; i < this.items.Count; i++) 
                { 
                    if (((QueryParameter)items[i]).ParameterName.Equals(ParameterName)) 
                    { 
                        index = i; 
                        break; 
                    } 
                } 
            } 
            return index; 
        } 
 
        public void Clear() 
        { 
            this.ArrayList().Clear(); 
        } 
    } 
}