dbutils.cs

oracle dal gen,生成.NET ORACLE DAL层代码

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Windows.Forms;

using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using OracleDALGen.Classes.Database;
using OracleDALGen.Classes.Attributes;

namespace OracleDALGen
{
    public class DBUtils
    {
        public static DataSet LoadDataset(string procedure, List<OracleParameter> parameters, CommandType cmdType, OraConnection connection)
        {
            Hashtable ht = new Hashtable();

            return LoadDataset(procedure, parameters, ht, null, null, cmdType, connection);
        }

        public static DataSet LoadDataset(string procedure, List<OracleParameter> parameters, int? startRow, int? endRow, CommandType cmdType, OraConnection connection)
        {
            Hashtable ht = new Hashtable();

            return LoadDataset(procedure, parameters, ht, startRow, endRow, cmdType, connection);
        }

        public static DataSet LoadSQL(string sql, List<OracleParameter> parameters, OraConnection connection)
        {
            OracleConnection conn = null;
            OracleCommand cmd = null;
            try
            {
                conn = new OracleConnection(connection.ConnectionString);
                conn.Open();
                cmd = new OracleCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;

                if (parameters != null)
                {
                    foreach (OracleParameter op in parameters)
                        cmd.Parameters.Add(op);
                }

                System.Data.DataSet ds = new DataSet();
                OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                adapter.Fill(ds, "data");

                return ds;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();

                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }

        private static DataSet LoadDataset(string procedure, List<OracleParameter> parameters, Hashtable mappings, int? startRow, int? endRow, CommandType cmdType, OraConnection connection)
        {
            OracleConnection conn = null;
            OracleCommand cmd = null;
            try
            {
                conn = new OracleConnection(connection.ConnectionString);
                conn.Open();
                cmd = new OracleCommand();
                cmd.Connection = conn;
                cmd.CommandType = cmdType;
                cmd.CommandText = procedure;

                if (parameters != null)
                {
                    foreach (OracleParameter op in parameters)
                        cmd.Parameters.Add(op);
                }

                System.Data.DataSet ds = new DataSet();
                OracleDataAdapter adapter = new OracleDataAdapter(cmd);

                if (startRow != null && endRow != null)
                {
                    DataTable table = ds.Tables.Add("data");
                    adapter.Fill(startRow.Value, endRow.Value, table);
                }
                else
                    adapter.Fill(ds, "data");

                return ds;

            }
            catch (Exception ex)
            {
                MessageBox.Show("Error loading dataset: \n" + ex.Message);
                return null;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();

                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }

        public static bool Execute(string procedure, List<OracleParameter> parameters, CommandType cmdType, OraConnection connection)
        {
            OracleConnection conn = null;
            OracleCommand cmd = null;
            try
            {
                conn = new OracleConnection(connection.ConnectionString);
                conn.Open();
                cmd = new OracleCommand();
                cmd.Connection = conn;
                cmd.CommandType = cmdType;
                cmd.CommandText = procedure;

                if (parameters != null)
                {
                    foreach (OracleParameter op in parameters)
                        cmd.Parameters.Add(op);
                }

                cmd.ExecuteNonQuery();

                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error executing procedure: " + ex.Message);
                return false;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();

                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }

        public static object[] Save(string procedure, List<OracleParameter> parameters, CommandType cmdType, OraConnection connection)
        {
            OracleConnection conn = null;
            OracleCommand cmd = null;
            try
            {
                conn = new OracleConnection(connection.ConnectionString);
                conn.Open();
                cmd = new OracleCommand();
                cmd.Connection = conn;
                cmd.CommandType = cmdType;
                cmd.CommandText = procedure;

                List<OracleParameter> retList = new List<OracleParameter>();
                foreach (OracleParameter op in parameters)
                {
                    if (op.Direction == ParameterDirection.Output)
                        retList.Add(op);

                    cmd.Parameters.Add(op);
                }

                cmd.ExecuteNonQuery();

                List<object> retValues = new List<object>();

                foreach (OracleParameter op in retList)
                {
                    if (op.OracleDbType == OracleDbType.Clob)
                        retValues.Add(CastHelper<OracleClob>(op.Value).Value);
                    else if (op.OracleDbType == OracleDbType.Blob)
                        retValues.Add(((OracleBlob)op.Value).Value);
                    else if (op.OracleDbType == OracleDbType.Varchar2)
                        retValues.Add(((OracleString)op.Value).Value);
                    else
                        retValues.Add(op.Value);
                }

                return retValues.ToArray();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error executing procedure: " + ex.Message);
                return null;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();

                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }

        private static T CastHelper<T>(object value)
        {
            if (value == null || value is DBNull)
                return Activator.CreateInstance<T>();
            else
            {
                return (T)value;
            }
        }

        public static object ExecuteNonQuery(string procedure, List<OracleParameter> parameters, OraConnection connection, CommandType cmdType, OracleDbType type)
        {
            OracleConnection conn = null;
            OracleCommand cmd = null;
            try
            {
                conn = new OracleConnection(connection.ConnectionString);
                conn.Open();
                cmd = new OracleCommand();
                cmd.Connection = conn;
                cmd.CommandType = cmdType;
                cmd.CommandText = procedure;

                OracleParameter retP = null;
                foreach (OracleParameter op in parameters)
                {
                    if (op.Direction == ParameterDirection.ReturnValue || op.Direction == ParameterDirection.Output)
                        retP = op;

                    cmd.Parameters.Add(op);                   
                }

                cmd.ExecuteNonQuery();

                object retVal = null;
                if (type == OracleDbType.Decimal)
                    retVal = ((OracleDecimal)retP.Value).Value;
                else if (type == OracleDbType.Clob)
                    retVal = ((OracleClob)retP.Value).Value;
                else if (type == OracleDbType.Blob)
                    retVal = ((OracleBlob)retP.Value).Value;
                else if (type == OracleDbType.Varchar2)
                    retVal = ((OracleString)retP.Value).Value;
                
                return retVal;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error executing procedure: " + ex.Message);
                return null;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();

                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }

        public static byte[] StringToBlob(string str)
        {
            System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();
            return encoding.GetBytes(str);            
        }

        public static double? GetDouble(object obj)
        {
            if (obj is DBNull || obj == null)
                return null;
            else
                return Convert.ToDouble(obj);
        }

        public static int? GetInt(object obj)
        {
            if (obj is DBNull || obj == null)
                return null;
            else
                return Convert.ToInt32(obj);
        }

        public static string GetString(object obj)
        {
            if (obj is DBNull || obj == null)
                return "";
            else
                return obj.ToString();
        }

        public static bool GetBool(object obj, bool d) {
            bool? b = GetBool(obj);

            if (b != null)
                return b.Value;
            else
                return d;
        }

        public static bool? GetBool(object obj)
        {
            if (obj is DBNull || obj == null)
                return null;
            else {
                if (obj is string)
                    return (obj.ToString().ToLower().Equals("y"));
                else if (obj is int)
                    return (Convert.ToInt32(obj) == 1);
                else if (obj is Decimal)
                    return (Convert.ToInt32(obj) == 1);
                else
                    throw new Exception("Wrong type: " + obj.GetType().Name);
            }
        }

        public static DateTime? GetDate(object obj)
        {
            if (obj is DBNull || obj == null)
                return null;
            else
            {
                return DateTime.Parse(obj.ToString());
            }
        }
    }
}