idbo.cs

c#数据库访问自动生成代码程序

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Data;
using log4net;
using log4net.Config;


namespace IDBO
{
    class IDBO
    {
        private string mConn = "";
        private OracleConnection conn = null;
        private OracleCommand cmd = null;
        private OracleTransaction trans=null;
        
        private static readonly ILog iLog = LogManager.GetLogger(typeof(IDBO));

        static IDBO()
        {
               XmlConfigurator.Configure(new System.IO.FileInfo("./log4j.xml"));  
        }

        public static IDBO getIDBO(string conn)
        {            
            IDBO idbo = new IDBO();
            idbo.mConn =  conn;
            return idbo;
        }
        

        public void openDatabase()
        {
            try{
                conn = new OracleConnection(mConn);
                conn.Open();
            } catch (Exception e)
            {
                iLog.Error(mConn, e);
                throw e;
            }
        }


        public void closeDatabase()
        {
            if ((conn != null) && (conn.State != ConnectionState.Closed))
            {
                conn.Close();
                
            }

        }


        public void BeginTransaction()
        {
            trans=conn.BeginTransaction();

            
        }

        public void CommitTransaction()
        {
            trans.Commit();


        }

        public void RollbackTransaction()
        {
            trans.Rollback();
        }


        public string executeSql(string sql)
        {

            try
            {
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;

                if (trans != null)
                {
                    cmd.Transaction = trans;
                    
                }
               
                OracleDataReader odr = cmd.ExecuteReader();
                string str = null;
                odr.Read();
                str = odr.GetString(0);

                OracleCommandBuilder ocb = new OracleCommandBuilder();
                

                odr.Close();
                return str;
            }
            catch (Exception e)
            {
                iLog.Error(sql, e);
                throw e; 
            }
        }

        public DataSet executeSelectSql(string sql)
        {

            try
            {
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;

                if (trans != null)
                {
                    cmd.Transaction = trans;

                }

                OracleDataAdapter da = new OracleDataAdapter(cmd);
                da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception e)
            {
                iLog.Error(sql, e);
                throw e;
            }
        }

        public DataSet executeProcedure(string name, IPrameter[] pm)
        {

            try
            {
                cmd = conn.CreateCommand();               
                cmd.CommandText = name;

                if (trans != null)
                {
                    cmd.Transaction = trans;
                }
                
                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < pm.Length; i++)
                {
                    cmd.Parameters.Add(pm[i].pm);
                }
                
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                DataSet ds = new DataSet();
                da.Fill(ds);
                
                return ds;
            }
            catch (Exception e)
            {
                iLog.Error(getProcedureCall(name,pm),e);
                throw e;
            }
        }



        public void executeFunction(string name, IPrameter[] pm)
        {

            try
            {
                cmd = conn.CreateCommand();
                cmd.CommandText = name;


                if (trans != null)
                {
                    cmd.Transaction = trans;
                }

                cmd.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < pm.Length; i++)
                {
                    cmd.Parameters.Add(pm[i].pm);
                }

                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                iLog.Error(getProcedureCall(name, pm), e);
                throw e;
            }
        }





        private string getProcedureCall(string name, IPrameter[] pm)
        {
            string str;
            str = name+"(";
            for (int i=0; i < pm.Length; i++)
            {
                if (i == pm.Length-1)
                {
                    if (pm[i].pm.Direction == System.Data.ParameterDirection.Input || pm[i].pm.Direction == System.Data.ParameterDirection.InputOutput)
                    {
                        str = str + "'"+pm[i].Value.ToString()+"'" + ")";
                    }
                    else if (pm[i].pm.Direction == System.Data.ParameterDirection.Output)
                    {
                        str = str + pm[i].Name + ")";
                    }
                }
                else
                {
                    if (pm[i].pm.Direction == System.Data.ParameterDirection.Input || pm[i].pm.Direction == System.Data.ParameterDirection.InputOutput)
                    {
                        str = str + "'"+pm[i].Value.ToString()+"'" + ",";
                    }
                    else if (pm[i].pm.Direction == System.Data.ParameterDirection.Output)
                    {
                        str = str + pm[i].Name + ",";
                    }
                }
            }
            return str;
        }



    }
}