dbfunc.cs

JAVA的精彩实例

using System;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Diagnostics;
using System.Web;
using System.Configuration;

namespace feiyun0112.cnblogs.com.CSDNReader.Functions
{

    public class DBFunc : IDisposable
    {
        private static string mConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=CSDNReader.mdb;Persist Security Info=False";
        private OleDbConnection mCon ;
        private OleDbDataAdapter mAdapter;
        private OleDbCommand mCom ;
        private OleDbTransaction mTran ;

        //Whether need to create Transaction
        private bool mIsHaveTransaction;
        //Whether auto close database connection runed a mothed
        private bool mIsAutoClose ;

        /// <summary>
        /// Init DBFunc class
        /// </summary>
        /// <param name="isHaveTransaction">Whether need to create a transaction </param>
        public DBFunc(bool isAutoClose, bool isHaveTransaction)
        {
            mIsHaveTransaction = isHaveTransaction;
            mIsAutoClose = isAutoClose;

            OpenCon();
            mCom = new OleDbCommand();
            mCom.Connection = mCon;

            if (mIsHaveTransaction)
            {
                mTran = mCon.BeginTransaction();
                mCom.Transaction = mTran;
            }
        }

        /// <summary>
        /// default isAutoClose is true, isHaveTransaction is false
        /// </summary>
        public DBFunc()
        {
            mIsAutoClose = true;
            OpenCon();
            mCom = new OleDbCommand();
            mCom.Connection = mCon;
        }

        /// <summary>
        /// Whether need to create a transaction
        /// </summary>
        public bool IsHaveTransaction
        {
            get
            {
                return mIsHaveTransaction;
            }
            set
            {
                mIsHaveTransaction = value;
            }
        }
        /// <summary>
        /// Whether auto close data connection
        /// </summary>
        public bool IsAutoClose
        {
            get
            {
                return mIsAutoClose;
            }
            set
            {
                mIsAutoClose = value;
            }
        }

        /// <summary>
        /// get or set a connection string
        /// </summary>
        /// 
        public static string ConnectionDBString
        {
            get
            {
                return mConnectionString;
            }
            set
            {
                mConnectionString = value;
            }
        }

        /// <summary>
        /// get or set a OleDbConnection,if OleDbConnection is not existing or close,create a OleDbconnection or open it
        /// </summary>
        public OleDbConnection Con
        {
            get
            {
                return mCon;
            }
            set
            {
                mCon = value;
            }
        }

        /// <summary>
        /// get or set a OleDbCommand
        /// </summary>
        public OleDbCommand Com
        {
            get
            {
                return mCom;
            }
            set
            {
                mCom = value;
            }
        }

        /// <summary>
        ///Execute multi OleDb,and return nothing
        /// </summary>
        /// <param name="OleDbList">include multi OleDb</param>
        public void ExecuteSql(ArrayList OleDbList)
        {
            string Sql = "";

            if (OleDbList != null)
            {
                for (int i = 0; i < OleDbList.Count; i++)
                {
                    Sql = OleDbList[i].ToString();
                    WriteOleDbLog(Sql);
                    mCom.CommandText = Sql;
                    mCom.ExecuteNonQuery();
                }
            }

            AutoCloseCon();
        }

        /// <summary>
        /// Execute single OleDb,and return nothing
        /// </summary>
        /// <param name="OleDb">input OleDb</param>
        /// <returns></returns>
        public void ExecuteSql(string Sql)
        {
            WriteOleDbLog(Sql);
            mCom.CommandText = Sql;
            mCom.ExecuteNonQuery();
            AutoCloseCon();
        }


        /// <summary>
        /// Execute single OleDb,and return OleDbDataReader
        /// </summary>
        /// <param name="OleDb">input OleDb</param>
        /// <returns>OleDbDataReader</returns>
        public OleDbDataReader GetDataReader(string Sql)
        {
            OleDbDataReader dataReader;

            WriteOleDbLog(Sql);
            if (mCom == null)
            {
                mCom = new OleDbCommand();
                mCom.Connection = mCon;
            }
            mCom.CommandText = Sql;

            dataReader = mCom.ExecuteReader();

            AutoCloseCon();

            return dataReader;
        }

        /// <summary>
        /// Execture a OleDb,return a Double type value
        /// </summary>
        /// <param name="OleDb">input OleDb</param>
        /// <returns></returns>
        public double GetDoubleValue(string Sql)
        {
            object oRet = null;
            WriteOleDbLog(Sql);
            mCom.CommandText = Sql;
            oRet = mCom.ExecuteScalar();

            AutoCloseCon();
            if (oRet == null || oRet == System.DBNull.Value)
                return 0;
            else
                return double.Parse(oRet.ToString());
        }

        /// <summary>
        /// Execture a OleDb,return a float type value
        /// </summary>
        /// <param name="OleDb">input OleDb</param>
        /// <returns></returns>
        public float GetFloatValue(string Sql)
        {
            object oRet = null;
            WriteOleDbLog(Sql);
            mCom.CommandText = Sql;
            oRet = mCom.ExecuteScalar();

            AutoCloseCon();
            if (oRet == null || oRet == System.DBNull.Value)
                return 0f;
            else
                return float.Parse(oRet.ToString());
        }

        /// <summary>
        /// Execture a OleDb,return a float type value
        /// </summary>
        /// <param name="OleDb">input OleDb</param>
        /// <returns></returns>
        public int GetIntValue(string Sql)
        {
            object oRet = null;
            WriteOleDbLog(Sql);
            mCom.CommandText = Sql;
            oRet = mCom.ExecuteScalar();

            AutoCloseCon();
            if (oRet == null || oRet == System.DBNull.Value)
                return 0;
            else
                return int.Parse(oRet.ToString());
        }
        /// <summary>
        /// Execture a OleDb,return a string type value
        /// </summary>
        /// <param name="OleDb">input OleDb</param>
        /// <returns></returns>
        public object GetObjectValue(string Sql)
        {
            object oRet = null;
            WriteOleDbLog(Sql);
            mCom.CommandText = Sql;
            oRet = mCom.ExecuteScalar();

            AutoCloseCon();

            return oRet;
        }


        /// <summary>
        /// Execture a OleDb,return a string type value
        /// </summary>
        /// <param name="OleDb">input OleDb</param>
        /// <returns></returns>
        public string GetValue(string Sql)
        {
            string sRet = "";
            object oRet = null;
            WriteOleDbLog(Sql);
            mCom.CommandText = Sql;
            oRet = mCom.ExecuteScalar();

            if (oRet == null || oRet == System.DBNull.Value)
            {
                sRet = null;
            }
            else
            {
                sRet = oRet.ToString();
            }
            AutoCloseCon();

            return sRet;
        }

        /// <summary>
        /// Execture a OleDb return a dataset with default table name
        /// </summary>
        /// <param name="OleDb">Input OleDb</param>
        public DataSet GetDataSet(string Sql)
        {
            DataSet dataSet = new DataSet();

            WriteOleDbLog(Sql);
            mCom.CommandText = Sql;
            if (mAdapter == null)
            {
                mAdapter = new OleDbDataAdapter();
            }
            mAdapter.SelectCommand = mCom;
            mAdapter.Fill(dataSet);

            AutoCloseCon();

            return dataSet;
        }
        public DataRow GetDataFirstRow(string Sql)
        {
            DataSet dataSet = new DataSet();

            WriteOleDbLog(Sql);
            mCom.CommandText = Sql;
            if (mAdapter == null)
            {
                mAdapter = new OleDbDataAdapter();
            }
            mAdapter.SelectCommand = mCom;
            mAdapter.Fill(dataSet);

            AutoCloseCon();
            if (dataSet.Tables[0].Rows.Count == 0)
            {
                return null;
            }
            else
            {
                return dataSet.Tables[0].Rows[0];
            }


        }

        /// <summary>
        /// Execture a OleDb return a dataset with a table name
        /// </summary>
        /// <param name="OleDb">input OleDb</param>
        /// <param name="TableName">Table name in dataset </param>
        /// <param name="dataSet">Fill the data in the dataset</param>
        public void GetDataSet(string Sql, string TableName, DataSet dataSet)
        {
            WriteOleDbLog(Sql);
            mCom.CommandText = Sql;
            if (mAdapter == null)
            {
                mAdapter = new OleDbDataAdapter();
            }
            mAdapter.SelectCommand = mCom;
            mAdapter.Fill(dataSet, TableName);

            AutoCloseCon();
        }


        public void CloseCon()
        {

            if (mCon != null && mCon.State == ConnectionState.Open)
            {
                mCon.Close();
            }

            if (mCom != null)
            {
                mCom.Dispose();
                mCom = null;
            }

            if (mAdapter != null)
            {
                mAdapter.Dispose();
                mAdapter = null;
            }

            if (mCon != null)
            {
                mCon.Dispose();
                mCon = null;
            }
        }


        /// <summary>
        /// Close OleDbConnection and release resources
        /// </summary>
        public void CommitTranAndCloseCon()
        {
            CommitTran();

            CloseCon();
        }

        private void AutoCloseCon()
        {
            if (mIsAutoClose)
            {
                CommitTranAndCloseCon();
            }
        }
        /// <summary>
        /// Open a OleDbConnection ,if OleDbConnection is existed ,and do nothing
        /// </summary>
        public void OpenCon()
        {
            mCon = new OleDbConnection();
            mCon.ConnectionString = mConnectionString;
            mCon.Open();
        }

        /// <summary>
        /// Commit a existed transaction
        /// </summary>
        public void CommitTran()
        {
            if (mTran != null)
            {
                mTran.Commit();
                mTran = null;
            }
        }
        /// <summary>
        /// Create a transation
        /// </summary>
        public void BeginTran()
        {
            if (mTran == null)
            {
                mTran = mCon.BeginTransaction();
            }
        }
        /// <summary>
        /// Rollback transaction
        /// </summary>
        public void RollbackTran()
        {
            if (mTran != null)
            {
                mTran.Rollback();
                mTran = null;
            }
        }
        /// <summary>
        /// Rollback transaction
        /// </summary>
        public void RollbackTranAndClose()
        {
            if (mTran != null)
            {
                mTran.Rollback();
                mTran = null;
            }
            CloseCon();
        }
        //Wtite OleDb Log
        private static void WriteOleDbLog(string strOleDb)
        {
            Debug.WriteLine(strOleDb);
        }

        public static string SpecialChar(string str)
        {
            if (str == null)
            {
                return string.Empty;
            }
            else
            {
                return str.Replace(@"'", @"''");
            }
        }

        #region IDisposable 成员

        public  void Dispose()
        {
            Dispose(true);
        }
        void Dispose(bool disposing)
        {
            if (disposing)
            {
                CloseCon();
            }
            else
            {
                CloseCon();
            }
        }

        #endregion
    }


}