发个DA层操作数据库的类


// Description:Structure
namespace Kola.DataAccess
{

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Diagnostics;
    using System.Configuration;
    /// <summary>
    /// Structure of Connection parameter
    /// </summary>
    public struct SConnParameterSql
    {
        /// <summary>
        /// Set up DataSet.
        /// </summary>
        public DataSet ds;
        /// <summary>
        /// Table name in this dataset.
        /// </summary>
        public string MappingTableName;
        /// <summary>
        /// Primary key for this table.
        /// </summary>
        public DataColumn[] primarykey;
        /// <summary>
        /// Command Text for the Connection.
        /// </summary>
        public string commandtext;
        /// <summary>
        /// Command Typefor the Connection.
        /// </summary>
        public CommandType commandtype;
        /// <summary>
        /// Parameter the Connection
        /// </summary>
        public SqlParameter[] parameters;
    }

    /// <summary>
    /// General method of connect database
    /// </summary>
    public class DBCommonSQL
    {
        private SqlConnection connect = new SqlConnection();
        private SqlCommand command = new SqlCommand();
        private SqlDataAdapter da = new SqlDataAdapter();
        private SqlTransaction transaction = null;

        /// <summary>
        /// Initialize the connetion
        /// </summary>
        public DBCommonSQL()
        {
            connect = new SqlConnection(this.ConnectionString);

            command.Connection = (SqlConnection)connect;

            da = new SqlDataAdapter(command);

        }

        /// <summary>
        /// Get the strong-type dataset
        /// </summary>
        /// <param name="connParameter"></param>
        /// <returns>DataSet</returns>
        internal DataSet GetStrongTypeDataSet(SConnParameterSql connParameter)
        {
            //if the provided connection is not open, we will open it
            try
            {
                if (connect.State != ConnectionState.Open)
                {
                    connect.Open();
                }
                command.CommandText = connParameter.commandtext;
                command.CommandType = connParameter.commandtype;
                // connParameter.ds.Tables[connParameter.MappingTableName].PrimaryKey = connParameter.primarykey;

                command.Parameters.Clear();
                if (connParameter.parameters != null)
                {
                    foreach (SqlParameter parameter in connParameter.parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                }

                da.SelectCommand = command;
                //da.FillSchema(connParameter.ds, SchemaType.Mapped, connParameter.MappingTableName);
                da.Fill(connParameter.ds, connParameter.MappingTableName);

                connParameter.ds.RemotingFormat = SerializationFormat.Binary;
                return connParameter.ds;
            }
            catch (Exception e)
            {
                CatchException(e);
                return null;
            }
            finally
            {
                command.Dispose();
                da.Dispose();
                if (connect.State == ConnectionState.Open)
                {
                    connect.Close();
                }
            }
        }

        /// <summary>
        /// Get the DataTable
        /// </summary>
        /// <param name="connParameter"></param>
        /// <returns>DataSet</returns>
        internal DataTable GetDataTable(SConnParameterSql connParameter)
        {
            //if the provided connection is not open, we will open it
            try
            {
                if (connect.State != ConnectionState.Open)
                {
                    connect.Open();
                }
                command.CommandText = connParameter.commandtext;
                command.CommandType = connParameter.commandtype;

                command.Parameters.Clear();
                if (connParameter.parameters != null)
                {
                    foreach (SqlParameter parameter in connParameter.parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                }
                da.SelectCommand = command;

                da.Fill(connParameter.ds);

                return connParameter.ds.Tables[0];
            }
            catch (Exception e)
            {
                CatchException(e);
                return null;
            }
            finally
            {
                command.Dispose();
                da.Dispose();
                if (connect.State == ConnectionState.Open)
                {
                    connect.Close();
                }
            }
        }

        /// <summary>
        /// Get DataReader
        /// </summary>
        /// <param name="connParameter"></param>
        /// <returns>IDataReader</returns>
        internal IDataReader GetDataReader(SConnParameterSql connParameter)
        {
            //if the provided connection is not open, we will open it
            try
            {
                if (connect.State != ConnectionState.Open)
                {
                    connect.Open();
                }

                command.CommandText = connParameter.commandtext;
                command.CommandType = connParameter.commandtype;
                SqlDataReader dr = null;

                command.Parameters.Clear();
                if (connParameter.parameters != null)
                {
                    foreach (SqlParameter parameter in connParameter.parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                }

                dr = command.ExecuteReader(CommandBehavior.CloseConnection);
                GetOutParameters(connParameter.parameters, command);
                return dr;
            }
            catch (Exception e)
            {
                CatchException(e);
                return null;
            }
            finally
            {
                command.Dispose();
                if (connect.State == ConnectionState.Open)
                {
                    connect.Close();
                }
            }
        }

        /// <summary> 
        /// operate the database by  StoredProcedure with update,deleteal,add
        /// </summary> 
        /// <param name="connParameter"></param>
        internal void EditTableWithReturnValue(SConnParameterSql connParameter)
        {
            EditTable(connParameter, true);
        }
        /// <summary> 
        /// operate the database by  StoredProcedure with update,deleteal,add
        /// </summary> 
        /// <param name="connParameter"></param>
        internal void EditTableNoReturnValue(SConnParameterSql connParameter)
        {
            EditTable(connParameter, false);
        }
        /// <summary> 
        /// operate the database by  StoredProcedure with update,deleteal,add
        /// </summary> 
        /// <param name="connParameter"></param>
        /// <param name="isReturnValue"></param>
        private void EditTable(SConnParameterSql connParameter, bool isReturnValue)
        {
            try
            {
                command.CommandText = connParameter.commandtext;
                command.CommandType = connParameter.commandtype;

                //Add parameters values
                command.Parameters.Clear();
                if (connParameter.parameters != null)
                {
                    foreach (SqlParameter parameter in connParameter.parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                }
                //if the provided connection is not open, we will open it
                if (connect.State != ConnectionState.Open)
                {
                    connect.Open();
                }
                //Commit Value
                command.ExecuteNonQuery();

                //Get return parameters va
                if (isReturnValue)
                    GetOutParameters(connParameter.parameters, command);
            }
            catch (Exception e)
            {
                CatchException(e);
            }
            finally
            {
                command.Dispose();
                if (connect.State == ConnectionState.Open)