sqlfactory.cs

设计模式实现的数据库底层操作组件在应用程序的设计中

using System;
using System.Data;
using System.Data.SqlClient;

namespace CFSoft.Zhanghz.DBMnager
{
    /// <summary>
    /// SqlServer数据库连接对象
    /// </summary>
    public class SqlDBOperate:DBOperate            
    {
        private static SqlConnection conn; 
        private string _strConn;
        /// <summary>
        /// SqlServer数据库对象
        /// </summary>
        public SqlDBOperate()   
        {
        }
        /// <summary>
        /// SqlServer数据库对象
        /// </summary>
        /// <param name="ConnectionString">连接字符串</param>
        public SqlDBOperate(string ConnectionString)   
        {
            conn = new SqlConnection(ConnectionString );
            _strConn = ConnectionString;
        }
        /// <summary>
        /// 打开数据库
        /// </summary>
        private void Open()
        {
            if( (conn == null || conn.ConnectionString == string.Empty ) && _strConn != string.Empty)
            {
                conn = new SqlConnection(_strConn);
            }
            if( conn.State != ConnectionState.Open ) 
            { 
                conn.Open(); 
            }
        }

        /// <summary>
        /// 关闭数据库
        /// </summary>
        public void Close()
        {
            conn.Close(); 
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="CommandText">SQL语句</param>
        /// <param name="para">参数结构</param>
        /// <returns>影响行数</returns>
        public int ExecuteNonQuery(string CommandText, ParamInfo[] para)
        {
            Open();

            SqlCommand cmd = new SqlCommand(); 
  　        
            cmd.Connection = conn ; 
            
            SqlTransaction trans = conn.BeginTransaction() ;
            
            cmd.Transaction = trans ;
            
            cmd.CommandText = CommandText;
            
            if( para != null ) 
            { 
                for(int i = 0;i< para.Length;i++) 
                {    
                    cmd.Parameters.Add(para[i].ParameterName,para[i].ParameterValue );                                                                                        
                }
            }
            int Num = -1;
            try
            { 
   　　         Num = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch(Exception ex)
            {
                trans.Rollback();
                throw(ex);
            }
            finally
            {
                trans = null;
                cmd.Dispose(); 
            }

            return Num;
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="CommandObject">SQL语句</param>
        /// <returns>影响行数</returns>
        public int ExecuteNonQuery(string CommandObject)
        {
            return ExecuteNonQuery(CommandObject, null);
        }

        /// <summary>
        /// 带参数的SQL查询
        /// </summary>
        /// <param name="CommandText">SQL语句</param>
        /// <param name="para">参数结构</param>
        /// <returns>DataSet</returns>
        public DataSet Select(string CommandText, ParamInfo[] para)
        {
            Open();

            SqlCommand cmd = new SqlCommand(); 
            
            cmd.Connection = conn ; 
   　　     
            DataSet ds = new DataSet(); 
   　　
            SqlDataAdapter ad = new SqlDataAdapter(); 
            
            cmd.CommandText = CommandText; 
            
            if( para != null ) 
            { 
                for(int i = 0;i< para.Length;i++) 
                {    
                    cmd.Parameters.Add(para[i].ParameterName,para[i].ParameterValue );                                                                                        
                }
            }
             
   　　     ad.SelectCommand = cmd; 

            ad.Fill(ds); 
   　　     
            return ds; 
        }
        /// <summary>
        ///SQL查询
        /// </summary>
        /// <param name="CommandObject">SQL语句</param>
        /// <returns>DataSet</returns>
        public DataSet Select(string CommandObject)
        {
            return Select(CommandObject,null);
        }
        /// <summary>
        /// 设置或获取数据库的连接状态
        /// </summary>
        public ConnectionState State
        {
            get
            {
                return conn.State; 
            }
            set
            {
                if (value == ConnectionState.Open && conn.State != ConnectionState.Open )
                {
                    Open();
                }
            }
        }

        /// <summary>
        /// 设置或获取数据库的连接状态
        /// </summary>
        public string ConnectionString
        {
            get{ return _strConn; }
            set{ _strConn = value;}
        }

    }
}