dbaccess.cs

自己编写的基本Orcale的通用的数据库初始化工具。

using System;
using System.Data;
using System.Data.OracleClient;
using System.Configuration;

namespace DS.EMIS.StartPrepare
{
	/// <summary>
	/// DBAccess 通过ADO连接数据库，并且执行各种操作
	/// </summary>
    /// <summary>
    /// DBAccess 的摘要说明。
    /// </summary>
    internal class DBAccess
    {
//        private static OracleConnection _srcCnt110;
//        private static OracleConnection _srcCnt120;
        private static OracleConnection _dstCnt;
//        private static string _dmPath = Application.StartupPath+"\\DictFile";
        public static string UserID = "";
        public static string UserPD = "";
        public static string ServiceName = "";

//        public static OracleConnection SrcConnection
//        {
//            get
//            {
//                if(KSImportUtility.SourceType == 1)
//                {
//                    if(_srcCnt110 == null)
//                    {
//                        string cntString = ConfigurationSettings.AppSettings["SrcCntString110"];
//                        _srcCnt110 = new OracleConnection(cntString);
//                    }
//                    return _srcCnt110;
//                }
//                else
//                {
//                    if(_srcCnt120 == null)
//                    {
//                        string cntString = ConfigurationSettings.AppSettings["SrcCntString120"];
//                        _srcCnt120 = new OracleConnection(cntString);
//                    }
//                    return _srcCnt120;
//                }
//            }
//        }

        public static OracleConnection DstConnection
        {
            get
            {
                if(_dstCnt == null)
                {
                    string cntString = "data source =" + ServiceName +" ;user id =" + UserID +";password = " + UserPD;
					//string cntString = "data source =dev2db2; user id = ksdb0420; password = ksdb0420";
                    _dstCnt = new OracleConnection(cntString);
                }
                return _dstCnt;
            }
            set
            {
                _dstCnt = value;
            }
        }

//        public static string DMPath
//        {
//            get
//            {
//                return _dmPath;
//            }
//        }

        private DBAccess()
        {
				
        }

        private void CloseConnection(IDbConnection cnt)
        {
            if(cnt!=null && cnt.State!=ConnectionState.Closed)
            {
                cnt.Close();
            }
        }

        public static bool TryConnect(string cntString)
        {
            OracleConnection cnt = null;
            try
            {
                cnt = new OracleConnection(cntString);
                cnt.Open();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                if(cnt != null)
                {
                    cnt.Close();
                    cnt.Dispose();
                    cnt = null;
                }
            }
        }


        public static object ExecuteQuery(string query,DbQueryType type)
        {
            object obj = null;
            OracleCommand cmd = null;
            System.Data.OracleClient.OracleTransaction tran = null;
            try
            {
                DstConnection.Open();
                tran = DstConnection.BeginTransaction();
                cmd = new OracleCommand(query,_dstCnt);
                cmd.Transaction = tran;
                switch(type)
                {	
                    case DbQueryType.NoneQuery:
                        cmd.ExecuteNonQuery();
                        break;
                    case DbQueryType.Scalar:
                        obj = cmd.ExecuteScalar();
                        break;
                    case DbQueryType.DataReader:
                        obj = cmd.ExecuteReader();
                        break;
                }
                tran.Commit();
            }
            catch(Exception ex)
            {
                obj = ex.ToString();
                tran.Rollback();
            }
            finally
            {
                DstConnection.Close();
            }
            return obj;
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public static int ExecuteSQL(string sql,OracleTransaction tran)
        {
            int result = -1;
            try
            {
                OracleCommand cmd = new OracleCommand(sql,_dstCnt);
                cmd.Transaction = tran;
                result = cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return result;
        }

        /// <summary>
        /// 读取数据 added by yangjl 2007-09-03
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable GetDataByCommandText(string sql)
        {
            DataTable dt = new DataTable();
            if(DstConnection.State != System.Data.ConnectionState.Open)
                DstConnection.Open();

            dt = FillInTranscation(sql);

            DstConnection.Close();
            return dt;
        }

        /// <summary>
        /// 在事务内读取数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable FillInTranscation(string sql)
        {
            DataTable dt = new DataTable();
            string connString = "data source = "+ServiceName+";user = "+UserID+";pwd = "+UserPD+";";
            System.Data.OracleClient.OracleDataAdapter oraAd = new OracleDataAdapter(sql,connString);
            oraAd.Fill(dt);
            return dt;
        }


		public static int GetNextVal(string tableName)
		{
			string cmdStr = string.Format("SELECT S_{0}.NEXTVAL FROM DUAL",tableName);
			OracleCommand cmd = new OracleCommand(cmdStr,DBAccess.DstConnection);
			int val = Convert.ToInt32(cmd.ExecuteScalar());
			cmd.Dispose();
			return val+1;
		}
    }   

    public enum DbQueryType
    {
        NoneQuery,DataReader,Scalar
    }

}