OracleService類

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Net.Mail;
using System.Net.Mime;
using System.ServiceModel;
using System.ServiceModel.Channels;
using System.Text;
using System.Threading;
using System.Web;
using System.Web.Hosting;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

public class OracleService : IOracle
{
    protected OracleConnection con;
    protected Dictionary<string, DataTable> schemaTables;
    protected Dictionary<string, DataTable> pkTables;
    protected Circumstances SapEenvironment;

    
    public OracleService()
    {
        con = new OracleConnection();
    }
    public StoredProcedureParameter[] GetStoredProcedureValues(string procedureName, StoredProcedureParameter[] spps)
    {
        OracleCommand command = new OracleCommand(procedureName, con);
        command.CommandTimeout = 600;
        command.CommandType = CommandType.StoredProcedure;
        command.BindByName = true;
        for (int i = 0; i < spps.Length; i++)
        {
            OracleParameter parameter = command.Parameters.Add(spps[i].Name,
                getDbType(spps[i].OracleType), getDbDirection(spps[i].Direction));
            if (spps[i].Direction == DbDirection.Input || spps[i].Direction == DbDirection.InputOutput)
            {
                parameter.Value = spps[i].Value;
            }
        }

        OracleDataAdapter oda = new OracleDataAdapter();
        try
        {
            con.Open();
            int result = command.ExecuteNonQuery();
            for (int i = 0; i < spps.Length; i++)
            {
                DataSet ds = new DataSet();
                if (spps[i].Direction == DbDirection.Output || spps[i].Direction == DbDirection.InputOutput ||
                    spps[i].Direction == DbDirection.ReturnValue)
                {
                    if (spps[i].OracleType == DbType.RefCursor)
                    {
                        OracleRefCursor orc = (OracleRefCursor)command.Parameters[spps[i].Name].Value;
                        oda.Fill(ds, orc);
                        spps[i].Cursor = ds;
                        spps[i].Value = null;
                    }
                    else
                    {
                        spps[i].Value = command.Parameters[spps[i].Name].Value.ToString();
                        spps[i].Cursor = null;
                    }
                }
            }
            return spps;
        }
        catch (Exception ex)
        {
            writeLog(ex.Source + "\t" + ex.Message + "\r\n\t\t\t" + procedureName + "\r\n");
            throw ex;
        }
        finally
        {
            oda.Dispose();
            command.Dispose();
            con.Close();
        }
    }

    private ParameterDirection getDbDirection(DbDirection direction)
    {
        ParameterDirection pd;
        switch (direction)
        {
            case DbDirection.Input:
                pd = ParameterDirection.Input;
                break;
            case DbDirection.InputOutput:
                pd = ParameterDirection.InputOutput;
                break;
            case DbDirection.Output:
                pd = ParameterDirection.Output;
                break;
            case DbDirection.ReturnValue:
                pd = ParameterDirection.ReturnValue;
                break;
            default:
                pd = ParameterDirection.InputOutput;
                break;
        }
        return pd;
    }

    private OracleDbType getDbType(DbType dbType)
    {
        OracleDbType odt;
        switch (dbType)
        {
            case DbType.BFile:
                {
                    odt = OracleDbType.BFile;
                }
                break;
            case DbType.Blob:
                {
                    odt = OracleDbType.Blob;
                }
                break;
            case DbType.Byte:
                {
                    odt = OracleDbType.Byte;
                }
                break;
            case DbType.Char:
                {
                    odt = OracleDbType.Char;
                }
                break;
            case DbType.Clob:
                {
                    odt = OracleDbType.Clob;
                }
                break;
            case DbType.Date:
                {
                    odt = OracleDbType.Date;
                }
                break;
            case DbType.Decimal:
                {
                    odt = OracleDbType.Decimal;
                }
                break;
            case DbType.Double:
                {
                    odt = OracleDbType.Double;
                }
                break;
            case DbType.Int16:
                {
                    odt = OracleDbType.Int16;
                }
                break;
            case DbType.Int32:
                {
                    odt = OracleDbType.Int32;
                }
                break;
            case DbType.Int64:
                {
                    odt = OracleDbType.Int64;
                }
                break;
            case DbType.IntervalDS:
                {
                    odt = OracleDbType.IntervalDS;
                }
                break;
            case DbType.IntervalYM:
                {
                    odt = OracleDbType.IntervalYM;
                }
                break;
            case DbType.Long:
                {
                    odt = OracleDbType.Long;
                }
                break;
            case DbType.LongRaw:
                {
                    odt = OracleDbType.LongRaw;
                }
                break;
            case DbType.NChar:
                {
                    odt = OracleDbType.NChar;
                }
                break;
            case DbType.NClob:
                {
                    odt = OracleDbType.NClob;
                }
                break;
            case DbType.NVarchar2:
                {
                    odt = OracleDbType.NVarchar2;
                }
                break;
            case DbType.RefCursor:
                {
                    odt = OracleDbType.RefCursor;
                }
                break;
            case DbType.Single:
                {
                    odt = OracleDbType.Single;
                }
                break;
            case DbType.TimeStamp:
                {
                    odt = OracleDbType.TimeStamp;
                }
                break;
            case DbType.TimeStampLTZ:
                {
                    odt = OracleDbType.TimeStampLTZ;
                }
                break;
            case DbType.TimeStampTZ:
                {
                    odt = OracleDbType.TimeStampTZ;
                }
                break;
            case DbType.Varchar2:
                {
                    odt = OracleDbType.Varchar2;
                }
                break;
            default:
                odt = OracleDbType.Varchar2;
                break;
        }
        return odt;
    }

    public int ExecuteStroedProcedure(string procedureName, string[] args, string[] argValues)
    {
        OracleCommand command = new OracleCommand(procedureName, con);
        command.CommandTimeout = 600;
        command.CommandType = CommandType.StoredProcedure;

        for (int i = 0; i < args.Length; i++)
        {
            OracleParameter parameter = command.Parameters.Add(args[i], OracleDbType.Varchar2);
            parameter.Value = argValues[i];
        }

        try
        {
            con.Open();
            int result = command.ExecuteNonQuery();
            return result;
        }
        catch (Exception ex)
        {
            writeLog(ex.Source + "\t" + ex.Message + "\r\n\t\t\t" + procedureName + "\r\n");
            throw ex;
        }
        finally
        {
            command.Dispose();
            con.Close();
        }
    }

    public int ExecuteNonQuery(string SQL)
    {
        writeLog(SQL);
        OracleCommand command = new OracleCommand(SQL, con);
        OracleTransaction trans = null;
        try
        {
            con.Open();
            trans = con.BeginTransaction();
            int result = command.ExecuteNonQuery();
            trans.Commit();
            return result;
        }
        catch (Exception ex)
        {
            trans.Rollback();
            writeLog(ex.Source + "\t" + ex.Message + "\r\n\t\t\t" + SQL + "\r\n");
            throw ex;
        }
        finally
        {
            command.Dispose();
            con.Close();
        }
    }

    public void ExecuteBatchNonQuery(string[] SQL)
    {
        writeLog(String.Join("\r\n", SQL));
        OracleCommand command = new OracleCommand();
        command.Connection = con;
        con.Open();
        OracleTransaction trans = con.BeginTransaction();

        int index = 0;
        try
        {
            foreach(string sql in SQL)
            {
                command.CommandText = sql;
                command.ExecuteNonQuery();
                index++;
            }
            trans.Commit();
        }
        catch (Exception ex)
        {
            trans.Rollback();
            writeLog("Error Index:" + index.ToString() + "\r\n" + ex.Source + "\t" + 
                ex.Message + "\r\n\t\t\t" + command.CommandText + "\r\n");
            throw ex;
        }
        finally
        {
            command.Dispose();
            con.Close();
        }
    }

    public DataSet ExecuteBatchQuery(string[] SQL)
    {
        OracleCommand command = new OracleCommand();
        int index = 0;
        try
        {
            DataSet ds = new DataSet();
            command.Connection = con;
            foreach (string sql in SQL)
            {
                command.CommandText = sql;
                OracleDataAdapter da = new OracleDataAdapter(command);
                da.Fill(ds, index.ToString());
                index++;
            }

            return ds;
        }
        catch (Exception ex)
        {
            writeLog("Error Index:" + index.ToString() + "\r\n" + ex.Source + "\t" + 
                ex.Message + "\r\n\t\t\t" + command.CommandText + "\r\n");
            throw ex;
        }
        finally
        {
            command.Dispose();
        }
    }

    public DataSet ExecuteQuery(string SQL)
    {
        DataSet ds = new DataSet();
        OracleCommand command = new OracleCommand();
        try
        {
            string[] temp = SQL.Split(";".ToCharArray());
            command.Connection = con;
            for (int i = 0; i < temp.Length; i++)
            {
                command.CommandText = temp[i];
                OracleDataAdapter da = new OracleDataAdapter(command);
                da.Fill(ds, i.ToString());
            }
            return ds;
        }
        catch (Exception ex)
        {
            writeLog(ex.Source + "\t" + ex.Message + "\n" + SQL + "\r\n");
            throw ex;
        }
        finally
        {
            command.Dispose();
        }
    }

    //單個SQL，及變量參數
    public DataSet ExecuteParameterQuery(string SQL, string[] parameterValues)
    {
        DataSet ds = new DataSet();
        OracleCommand command = new OracleCommand(SQL, con);
        foreach (string data in parameterValues)
        {
            OracleParameter parameter = new OracleParameter();
            parameter.Value = data;
            command.Parameters.Add(parameter);
        }
        try
        {
            OracleDataAdapter da = new OracleDataAdapter(command);
            da.Fill(ds, "0");
            return ds;
        }
        catch (Exception ex)
        {
            writeLog(ex.Source + "\t" + ex.Message + "\r\n\t\t\t" + SQL + "\r\n");
            throw ex;
        }
        finally
        {
            command.Dispose();
        }
    }

    //多個SQL，及變量參數
    public DataSet ExecuteBatchParameterQuery(string[] SQL, string[][] parameterValues)
    {
        DataSet ds = new DataSet();
        OracleCommand command = new OracleCommand();

        int index = 0;
        try
        {
            command.Connection = con;
            foreach (string sql in SQL)
            {
                command.CommandText = sql;
                command.Parameters.Clear();
                foreach (string data in parameterValues[index])
                {
                    OracleParameter parameter = new OracleParameter();
                    parameter.Value = data;
                    command.Parameters.Add(parameter);
                }
                OracleDataAdapter da = new OracleDataAdapter(command);
                da.Fill(ds, index.ToString());
                index++;
            }
            return ds;
        }
        catch (Exception ex)
        {
            writeLog("Error Index:" + index.ToString() + "\r\n" + ex.Source + "\t" +
                ex.Message + "\r\n\t\t\t" + command.CommandText + "\r\n");
            throw ex;
        }
        finally
        {
            command.Dispose();
        }
    }

    public string[] GetFieldData(string SQL)
    {
        List<string> list = new List<string>();
        OracleCommand command = new OracleCommand(SQL, con);
        try
        {
            con.Open();
            OracleDataReader dr = command.ExecuteReader();
            while (dr.Read())
            {
                list.Add(dr[0].ToString());
            }
            dr.Close();
            return list.ToArray();
        }
        catch (Exception ex)
        {
            writeLog(ex.Source + "\t" + ex.Message + "\r\n\t\t\t" + SQL + "\r\n");
            throw ex;
        }
        finally
        {
            command.Dispose();
            con.Close();
        }
    }

    public string GetSingleData(string SQL)
    {
        object temp = null;
        OracleCommand command = new OracleCommand(SQL, con);
        try
        {
            con.Open();
            temp = command.ExecuteScalar();
            return temp == null ? null : temp.ToString();
        }
        catch (Exception ex)
        {
            writeLog(ex.Source + "\t" + ex.Message + "\r\n\t\t\t" + SQL + "\r\n");
            throw ex;
        }
        finally
        {
            command.Dispose();
            con.Close();
        }
    }

    private object convertParameterValue(OracleParameter op, object strValue)
    {
        object temp = null;
        switch (op.OracleDbType)
        {
            case OracleDbType.Date:
                temp = Convert.ToDateTime(strValue.ToString());
                break;
            case OracleDbType.TimeStamp:
                temp = Convert.ToDateTime(strValue.ToString());
                break;
            case OracleDbType.Blob:
                temp = Convert.FromBase64String(strValue.ToString());
                break;
            default:
                temp = strValue;
                break;
        }
        return temp;
    }

    public int ExecuteUpdate(string tableName, string[] fields, string[] fieldValues, string[] whereFields, string[] whereValues)
    {
        StringBuilder sb = new StringBuilder();
        bool isSbOk = toStringBuilder(sb, "UPDATE", tableName, fields, fieldValues, whereFields, whereValues);
        writeLog(isSbOk.ToString() + "\r\n" + sb.ToString());

        List<string> listTemp = new List<string>();
        listTemp.AddRange(fields);
        listTemp.AddRange(whereFields);
        string[] allFields = listTemp.ToArray();
        listTemp.Clear();

        listTemp.AddRange(fieldValues);
        listTemp.AddRange(whereValues);
        string[] allValues = listTemp.ToArray();
        listTemp.Clear();

        string sql = null;
        OracleCommand command = null;
        try
        {
            addTableSchema(tableName);

            con.Open();
            sql = getUpdateString(tableName, fields, whereFields, whereValues);
            command = new OracleCommand(sql, con);
            List<OracleParameter> list = getUpdateParameters(tableName, allFields);
            for (int i = 0; i < list.Count; i++)
            {
                OracleParameter op = list[i];
                if (i < fields.Length)
                {
                    //要更新的欄位
                    op.Value = String.IsNullOrWhiteSpace(allValues[i]) ? 
                        (object)DBNull.Value : convertParameterValue(op, allValues[i]);
                    command.Parameters.Add(op);
                }
                else
                {
                    //where條件欄位
                    if (!String.IsNullOrWhiteSpace(allValues[i]))
                    {
                        op.Value = convertParameterValue(op, allValues[i]);
                        command.Parameters.Add(op);
                    }
                }
            }
            return command.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            writeLog(ex.Source + "\t" + ex.Message + "\r\n\t\t\t" + sql + "\r\n");
            throw ex;
        }
        finally
        {
            command.Dispose();
            con.Close();
        }
    }

    public int ExecuteInsert(string tableName, string[] fields, string[] fieldValues)
    {
        StringBuilder sb = new StringBuilder();
        bool isSbOk = toStringBuilder(sb, "INSERT", tableName, fields, fieldValues, null, null);
        writeLog(isSbOk.ToString() + "\r\n" + sb.ToString());


        string sql = null;
        OracleCommand command = null;
        try
        {
            addTableSchema(tableName);

            con.Open();
            sql = getInsertString(tableName, fields);
            command = new OracleCommand(sql, con);
            List<OracleParameter> list = getInsertParameters(tableName, fields);
            for (int i = 0; i < list.Count; i++)
            {
                OracleParameter op = list[i];
                op.Value = String.IsNullOrWhiteSpace(fieldValues[i]) ? 
                    (object)DBNull.Value : convertParameterValue(op, fieldValues[i]);
                command.Parameters.Add(op);
            }
            return command.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            writeLog(ex.Source + "\t" + ex.Message + "\r\n\t\t\t" + sql + "\r\n");
            throw ex;
        }
        finally
        {
            command.Dispose();
            con.Close();
        }
    }

    public int ExecuteDelete(string tableName, string[] whereFields, string[] whereValues)
    {
        StringBuilder sb = new StringBuilder();
        bool isSbOk = toStringBuilder(sb, "DELETE", tableName, null, null, whereFields, whereValues);
        writeLog(isSbOk.ToString() + "\r\n" + sb.ToString());


        string sql = null;
        OracleCommand command = null;
        try
        {
            addTableSchema(tableName);

            con.Open();
            sql = getDeleteString(tableName, whereFields, whereValues);
            command = new OracleCommand(sql, con);
            List<OracleParameter> list = getDeleteParameters(tableName, whereFields);
            for (int i = 0; i < list.Count; i++)
            {
                if (!String.IsNullOrWhiteSpace(whereValues[i]))
                {
                    OracleParameter op = list[i];
                    op.Value = convertParameterValue(op, whereValues[i]);
                    command.Parameters.Add(op);
                }
            }
            return command.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            writeLog(ex.Source + "\t" + ex.Message + "\r\n\t\t\t" + sql + "\r\n");
            throw ex;
        }
        finally
        {
            command.Dispose();
            con.Close();
        }
    }

    public DataSet ExecuteSelect(string tableName, string[] fields, string[] whereFields, string[] whereValues)
    {
        string sql = null;
        OracleCommand command = null;
        try
        {
            addTableSchema(tableName);

            con.Open();
            sql = getSelectString(tableName, fields, whereFields, whereValues);
            command = new OracleCommand(sql, con);
            List<OracleParameter> list = getSelectParameters(tableName, whereFields);
            for (int i = 0; i < list.Count; i++)
            {
                if (!String.IsNullOrWhiteSpace(whereValues[i]))
                {
                    OracleParameter op = list[i];
                    op.Value = convertParameterValue(op, whereValues[i]);
                    command.Parameters.Add(op);
                }
            }
            OracleDataAdapter da = new OracleDataAdapter(command);
            DataSet ds = new DataSet();
            da.Fill(ds, "0");
            return ds;
        }
        catch (Exception ex)
        {
            writeLog(ex.Source + "\t" + ex.Message + "\r\n\t\t\t" + sql + "\r\n");
            throw ex;
        }
        finally
        {
            command.Dispose();
            con.Close();
        }
    }

    public void ExecuteBatchUpdate(DbUpdate[] dbUpdate)
    {
        StringBuilder sb = new StringBuilder();
        bool isSbOk = toStringBuilder(sb, dbUpdate);
        writeLog(isSbOk.ToString() + "\r\n" + sb.ToString());

        OracleTransaction trans = null;
        string sql = null;
        int index = 0;
        try
        {
            con.Open();
            trans = con.BeginTransaction();
            List<string> listTemp = new List<string>();

            for (int i = 0; i < dbUpdate.Length; i++)
            {
                addTableSchema(dbUpdate[i].TableName);

                listTemp.AddRange(dbUpdate[i].Fields);
                listTemp.AddRange(dbUpdate[i].WhereFields);
                string[] allFields = listTemp.ToArray();
                listTemp.Clear();

                listTemp.AddRange(dbUpdate[i].FieldValues);
                listTemp.AddRange(dbUpdate[i].WhereValues);
                string[] allValues = listTemp.ToArray();
                listTemp.Clear();

                sql = getUpdateString(dbUpdate[i].TableName, dbUpdate[i].Fields, dbUpdate[i].WhereFields, dbUpdate[i].WhereValues);
                OracleCommand command = new OracleCommand(sql, con);
                try
                {
                    List<OracleParameter> list = getUpdateParameters(dbUpdate[i].TableName, allFields);
                    for (int j = 0; j < list.Count; j++)
                    {
                        OracleParameter op = list[j];
                        if (j < dbUpdate[i].Fields.Length)
                        {
                            //要更新的欄位
                            op.Value = String.IsNullOrWhiteSpace(allValues[j]) ?
                                (object)DBNull.Value : convertParameterValue(op, allValues[j]);
                            command.Parameters.Add(op);
                        }
                        else
                        {
                            //where條件欄位
                            if (!String.IsNullOrWhiteSpace(allValues[j]))
                            {
                                op.Value = convertParameterValue(op, allValues[j]);
                                command.Parameters.Add(op);
                            }
                        }
                    }
                    command.ExecuteNonQuery();
                }
                finally
                {
                    command.Dispose();
                }
                index++;
            }
            trans.Commit();
        }
        catch (Exception ex)
        {
            trans.Rollback();
            writeLog("Error Index:" + index.ToString() + "\r\n" + ex.Source + "\t" + 
                ex.Message + "\r\n\t\t\t" + sql + "\r\n");
            throw ex;
        }
        finally
        {
            con.Close();
        }
    }

    public void ExecuteBatchInsert(DbInsert[] dbInsert)
    {
        StringBuilder sb = new StringBuilder();
        bool isSbOk = toStringBuilder(sb, dbInsert);
        writeLog(isSbOk.ToString() + "\r\n" + sb.ToString());

        OracleTransaction trans = null;
        string sql = null;
        int index = 0;
        try
        {
            con.Open();
            trans = con.BeginTransaction();
            for (int i = 0; i < dbInsert.Length; i++)
            {
                addTableSchema(dbInsert[i].TableName);

                sql = getInsertString(dbInsert[i].TableName, dbInsert[i].Fields);
                OracleCommand command = new OracleCommand(sql, con);

                try
                {
                    List<OracleParameter> list = getInsertParameters(dbInsert[i].TableName, dbInsert[i].Fields);
                    for (int j = 0; j < list.Count; j++)
                    {
                        OracleParameter op = list[j];
                        op.Value = String.IsNullOrWhiteSpace(dbInsert[i].FieldValues[j]) ?
                            (object)DBNull.Value : convertParameterValue(op, dbInsert[i].FieldValues[j]);
                        command.Parameters.Add(op);
                     }
                    command.ExecuteNonQuery();
                }
                finally
                {
                    command.Dispose();
                }
                index++;
            }
            trans.Commit();
        }
        catch (Exception ex)
        {
            trans.Rollback();
            writeLog("Error Index:" + index.ToString() + "\r\n" + ex.Source + "\t" + 
                ex.Message + "\r\n\t\t\t" + sql + "\r\n");
            throw ex;
        }
        finally
        {
            con.Close();
        }
    }

    public void ExecuteBatchDelete(DbDelete[] dbDelete)
    {
        StringBuilder sb = new StringBuilder();
        bool isSbOk = toStringBuilder(sb, dbDelete);
        writeLog(isSbOk.ToString() + "\r\n" + sb.ToString());

        OracleTransaction trans = null;
        string sql = null;
        int index = 0;
        try
        {
            con.Open();
            trans = con.BeginTransaction();
            for (int i = 0; i < dbDelete.Length; i++)
            {
                addTableSchema(dbDelete[i].TableName);

                sql = getDeleteString(dbDelete[i].TableName, dbDelete[i].WhereFields, dbDelete[i].WhereValues);
                OracleCommand command = new OracleCommand(sql, con);

                try
                {
                    List<OracleParameter> list = getDeleteParameters(dbDelete[i].TableName, dbDelete[i].WhereFields);
                    for (int j = 0; j < list.Count; j++)
                    {
                        if (!String.IsNullOrWhiteSpace(dbDelete[i].WhereValues[j]))
                        {
                            OracleParameter op = list[j];
                            op.Value = convertParameterValue(op, dbDelete[i].WhereValues[j]);
                            command.Parameters.Add(op);
                        }
                    }
                    command.ExecuteNonQuery();
                }
                finally
                {
                    command.Dispose();
                }
                index++;
            }
            trans.Commit();
        }
        catch (Exception ex)
        {
            trans.Rollback();
            writeLog("Error Index:" + index.ToString() + "\r\n" + ex.Source + "\t" + 
                ex.Message + "\r\n\t\t\t" + sql + "\r\n");
            throw ex;
        }
        finally
        {
            con.Close();
        }
    }

    protected OracleCommand getInsertCommand(string tableName, string[] fields)
    {
        string[] insertFields = (string[])fields.Clone();
        for (int i = 0; i < insertFields.Length; i++)
        {
            int idx = insertFields[i].IndexOf(‘=‘);
            insertFields[i] = (idx == -1) ? insertFields[i] : insertFields[i].Substring(0, idx).Trim();
        }

        OracleCommand command = new OracleCommand();
        string sql = getInsertString(tableName, insertFields);
        List<OracleParameter> list = getInsertParameters(tableName, insertFields);
        command.Parameters.AddRange(list.ToArray());

        command.CommandText = sql;
        command.Prepare();
        return command;
    }

    protected OracleCommand getUpdateCommand(string tableName, string[] fields, string[] whereFields)
    {
        OracleCommand command = new OracleCommand();
        string sql = getUpdateString(tableName, fields, whereFields);

        List<OracleParameter> list = getUpdateParameters(tableName, fields);
        list.AddRange(getUpdateParameters(tableName, whereFields));
        command.Parameters.AddRange(list.ToArray());

        command.CommandText = sql;
        command.Prepare();
        return command;
    }

    protected OracleCommand getDeleteCommand(string tableName, string[] whereFields)
    {
        OracleCommand command = new OracleCommand();
        string sql = getDeleteString(tableName, whereFields);
        List<OracleParameter> list = getDeleteParameters(tableName, whereFields);
        command.Parameters.AddRange(list.ToArray());
        command.CommandText = sql;
        command.Prepare();
        return command;
    }

    protected void setInsertParameterValue(OracleCommand command, string[] fieldValues)
    {
        for (int i = 0; i < fieldValues.Length; i++)
        {
            OracleParameter op = command.Parameters[i];
            op.Value = String.IsNullOrWhiteSpace(fieldValues[i]) ?
                (object)DBNull.Value : convertParameterValue(op, fieldValues[i]);   
        }
    }

    protected void setUpdateParameterValue(OracleCommand command, string[] fieldValues, string[] whereValues)
    {
        for (int i = 0; i < fieldValues.Length; i++)
        {
            OracleParameter op = command.Parameters[i];
            op.Value = String.IsNullOrWhiteSpace(fieldValues[i]) ? 
                (object)DBNull.Value : convertParameterValue(op, fieldValues[i]);           
        }
        int count = 0;
        for (int i = 0; i < whereValues.Length; i++)
        {
            if (String.IsNullOrWhiteSpace(whereValues[i]))
            {
                command.Parameters.RemoveAt(count + fieldValues.Length);
            }
            else
            {
                OracleParameter op = command.Parameters[count + fieldValues.Length];
                op.Value = convertParameterValue(op, whereValues[i]);
                count++;
            }
        }
    }

    protected void setDeleteParameterValue(OracleCommand command, string[] whereValues)
    {
        int count = 0;
        for (int i = 0; i < whereValues.Length; i++)
        {
            if (String.IsNullOrWhiteSpace(whereValues[i]))
            {
                command.Parameters.RemoveAt(count);
            }
            else
            {
                OracleParameter op = command.Parameters[count];
                op.Value = convertParameterValue(op, whereValues[i]);
                count++;
            }
        }
    }

    public void ExecuteBatch(DbTreatment[] treatments)
    {
        StringBuilder sb = new StringBuilder();
        bool isSbOk = toStringBuilder(sb, treatments);
        writeLog(isSbOk.ToString() + "\r\n" + sb.ToString());

        OracleTransaction trans = null;
        OracleCommand commandI = null;
        OracleCommand commandU = null;
        OracleCommand commandD = null;
        int[] pkIdx = null;
        int[] nonPkIdx = null;
        int idx1 = 0;
        int idx2 = 0;
        string sql = null;

        try
        {
            con.Open();
            trans = con.BeginTransaction();
            for (int i = 0; i < treatments.Length; i++)
            {
                if (treatments[i].DMLType != DmlType.UserDefinition)
                {
                    addTableSchema(treatments[i].TableName);
                    addTablePK(treatments[i].TableName);
                }

                idx1 = i;
                switch (treatments[i].DMLType)
                {
                    case DmlType.UserDefinition:
                        commandD = new OracleCommand(treatments[i].CommandText);
                        commandD.Connection = con;
                        sql = commandD.CommandText;
                        break;
                    case DmlType.InsertNoConstraint:
                    case DmlType.Insert:
                        commandI = getInsertCommand(treatments[i].TableName, treatments[i].Fields);
                        commandI.Connection = con;
                        sql = commandI.CommandText;
                        break;
                    case DmlType.Update:
                        if (treatments[i].FieldValues.Length != treatments[i].WhereValues.Length)
                        {
                            throw new Exception("The FieldValues‘ length must equal to WhereValues");
                        }
                        commandU = getUpdateCommand(treatments[i].TableName, treatments[i].Fields, treatments[i].WhereFields);
                        commandU.Connection = con;
                        sql = commandU.CommandText;
                        break;
                    case DmlType.Delete:
                        commandD = getDeleteCommand(treatments[i].TableName, treatments[i].WhereFields);
                        commandD.Connection = con;
                        sql = commandD.CommandText;
                        break;
                    case DmlType.InsertUpdate:
                        commandI = getInsertCommand(treatments[i].TableName, treatments[i].Fields);
                        commandI.Connection = con;
                        sql = commandI.CommandText;

                        DataTable pkTable = pkTables[treatments[i].TableName];
                        string[] pks = new string[pkTable.Rows.Count];
                        for (int j = 0; j < pkTable.Rows.Count; j++)
                        {
                            pks[j] = pkTable.Rows[j]["COLUMN_NAME"].ToString();
                        }
                        pkIdx = new int[pks.Length];
                        nonPkIdx = new int[treatments[i].Fields.Length - pks.Length];
                        string[] nonPkFields = new string[treatments[i].Fields.Length - pks.Length];
                        int idx = 0;
                        for (int j = 0; j < treatments[i].Fields.Length; j++)
                        {
                            bool isFound = false;
                            for (int k = 0; k < pks.Length; k++)
                            {
                                if (treatments[i].Fields[j].ToUpper() == pks[k].ToUpper())
                                {
                                    pkIdx[k] = j;
                                    isFound = true;
                                    break;
                                }
                            }
                            if (isFound == false)
                            {
                                nonPkIdx[idx] = j;
                                nonPkFields[idx] = treatments[i].Fields[j];
                                idx++;
                            }
                        }

                        commandU = getUpdateCommand(treatments[i].TableName, nonPkFields, pks);
                        commandU.Connection = con;
                        sql = commandU.CommandText;
                        break;
                }


                switch (treatments[i].DMLType)
                {
                    case DmlType.UserDefinition:
                        try
                        {
                            commandD.ExecuteNonQuery();
                        }
                        finally
                        {
                            commandD.Dispose();
                        }
                        break;
                    case DmlType.InsertNoConstraint:
                    case DmlType.Insert:
                        try
                        {
                            for (int j = 0; j < treatments[i].FieldValues.Length; j++)
                            {
                                idx2 = j;
                                setInsertParameterValue(commandI, treatments[i].FieldValues[j]);
                                try
                                {
                                    commandI.ExecuteNonQuery();
                                }
                                catch (OracleException ex)
                                {
                                    if (treatments[i].DMLType == DmlType.Insert)
                                    {
                                        throw ex;
                                    }
                                }
                            }
                        }
                        finally
                        {
                            commandI.Dispose();
                        }
                        break;
                    case DmlType.Update:
                        try
                        {
                            OracleParameter[] ops = new OracleParameter[commandU.Parameters.Count];
                            commandU.Parameters.CopyTo(ops, 0);
                            for (int j = 0; j < treatments[i].FieldValues.Length; j++)
                            {
                                idx2 = j;
                                commandU.Parameters.Clear();
                                commandU.Parameters.AddRange(ops);
                                setUpdateParameterValue(commandU, treatments[i].FieldValues[j], treatments[i].WhereValues[j]);

                                commandU.CommandText = treatments[i].WhereValues[j].Any(x => String.IsNullOrWhiteSpace(x) == true) ?
                                    getUpdateString(treatments[i].TableName, treatments[i].Fields, treatments[i].WhereFields, treatments[i].WhereValues[j]) : sql;
                                commandU.ExecuteNonQuery();
                            }
                        }
                        finally
                        {
                            commandU.Dispose();
                        }
                        break;
                    case DmlType.Delete:
                        try
                        {
                            OracleParameter[] ops = new OracleParameter[commandD.Parameters.Count];
                            commandD.Parameters.CopyTo(ops, 0);
                            for (int j = 0; j < treatments[i].WhereValues.Length; j++)
                            {
                                idx2 = j;
                                commandD.Parameters.Clear();
                                commandD.Parameters.AddRange(ops);
                                setDeleteParameterValue(commandD, treatments[i].WhereValues[j]);

                                commandD.CommandText = treatments[i].WhereValues[j].Any(x => String.IsNullOrWhiteSpace(x) == true) ?
                                    getDeleteString(treatments[i].TableName, treatments[i].WhereFields, treatments[i].WhereValues[j]) : sql;
                                commandD.ExecuteNonQuery();
                            }
                        }
                        finally
                        {
                            commandD.Dispose();
                        }
                        break;

                    case DmlType.InsertUpdate:
                        try
                        {
                            for (int j = 0; j < treatments[i].FieldValues.Length; j++)
                            {
                                idx2 = j;
                                int affectRows = 0;
                                try
                                {
                                    if (pkIdx.Length > 0)   //避免因為沒有主鍵造成表的所有row內容被改為相同
                                    {
                                        string[] fieldValues = new string[nonPkIdx.Length];
                                        string[] whereValues = new string[pkIdx.Length];

                                        for (int k = 0; k < fieldValues.Length; k++)
                                        {
                                            fieldValues[k] = treatments[i].FieldValues[j][nonPkIdx[k]];
                                        }
                                        for (int k = 0; k < whereValues.Length; k++)
                                        {
                                            whereValues[k] = treatments[i].FieldValues[j][pkIdx[k]];
                                        }
                                        //insertupdate是根據pk欄位來更新，pk欄位不會有空值，故不需要針對where條件再處理
                                        setUpdateParameterValue(commandU, fieldValues, whereValues);
                                        affectRows = commandU.ExecuteNonQuery();
                                    }
                                    if (affectRows == 0)
                                    {
                                        setInsertParameterValue(commandI, treatments[i].FieldValues[j]);
                                        commandI.ExecuteNonQuery();
                                    }
                                }
                                catch (Exception oex)
                                {
                                    throw oex;
                                }
                            }
                        }
                        finally
                        {
                            commandI.Dispose();
                            commandU.Dispose();
                        }
                        break;
                }
            }
            trans.Commit();
        }
        catch (Exception ex)
        {
            trans.Rollback();
            writeLog("Error Index:" + idx1.ToString() + " / " + idx2.ToString() + "\r\n" + ex.Source + "\t" + 
                ex.Message + "\r\n\t\t\t" + sql + "\r\n");
            throw ex;
        }
        finally
        {
            con.Close();
        }
    }

    public void DealWithSingleTable(DbDML dbDML)
    {
        StringBuilder sb = new StringBuilder();
        bool isSbOk = toStringBuilder(sb, dbDML);
        writeLog(isSbOk.ToString() + "\r\n" + sb.ToString());

        OracleTransaction trans = null;
        string sql = null;

        List<string> lstPk = new List<string>();
        List<string> lstPkFields = new List<string>();
        List<string> lstNonPkFields = new List<string>();

        List<string> lstPkValues = new List<string>();
        List<string> lstNonPkValues = new List<string>();

        List<string> lstAllFields = new List<string>();
        List<string> lstAllValues = new List<string>();

        List<int> lstPkIdx = new List<int>();
        List<int> lstNonPkIdx = new List<int>();

        try
        {
            addTableSchema(dbDML.TableName);
            addTablePK(dbDML.TableName);

            foreach (DataRow row in pkTables[dbDML.TableName].Rows)
            {
                lstPk.Add(row["COLUMN_NAME"].ToString());
            }

            bool isPK = false;
            for (int i = 0; i < dbDML.Fields.Length; i++)
            {
                isPK = false;
                for (int j = 0; j < lstPk.Count; j++)
                {
                    if (dbDML.Fields[i].ToUpper() == lstPk[j].ToUpper())
                    {
                        isPK = true;
                        lstPkFields.Add(dbDML.Fields[i]);
                        lstPkIdx.Add(i);
                        break;
                    }
                }
                if (isPK == false)
                {
                    lstNonPkFields.Add(dbDML.Fields[i]);
                    lstNonPkIdx.Add(i);
                }
            }

            lstAllFields.AddRange(lstNonPkFields);
            lstAllFields.AddRange(lstPkFields);

            string sqlI = getInsertString(dbDML.TableName, dbDML.Fields);
            string sqlU = getUpdateString(dbDML.TableName, lstNonPkFields.ToArray(), lstPkFields.ToArray());

            con.Open();
            trans = con.BeginTransaction();
            OracleCommand command = null;

            List<OracleParameter> list = null;
            for (int i = 0; i < dbDML.FieldValues.Length; i++)
            {
                lstPkValues.Clear();
                lstNonPkValues.Clear();
                lstAllValues.Clear();

                foreach (int idx in lstPkIdx)
                {
                    lstPkValues.Add(dbDML.FieldValues[i][idx]);
                }

                foreach (int idx in lstNonPkIdx)
                {
                    lstNonPkValues.Add(dbDML.FieldValues[i][idx]);
                }

                lstAllValues.AddRange(lstNonPkValues);
                lstAllValues.AddRange(lstPkValues);

                try
                {
                    int affectRows = 0;
                    if (lstPkFields.Count > 0)
                    {
                        command = new OracleCommand(sqlU, con);
                        list = getUpdateParameters(dbDML.TableName, lstAllFields.ToArray());
                        for (int j = 0; j < list.Count; j++)
                        {
                            OracleParameter op = list[j];
                            op.Value = String.IsNullOrWhiteSpace(lstAllValues[j]) ? 
                                (object)DBNull.Value : convertParameterValue(op, lstAllValues[j]);
                            command.Parameters.Add(op);
                        }
                        affectRows = command.ExecuteNonQuery();
                    }
                    //更新rowcount==0 則採insert
                    if (affectRows == 0)
                    {
                        command = new OracleCommand(sqlI, con);
                        list = getInsertParameters(dbDML.TableName, dbDML.Fields);
                        for (int j = 0; j < list.Count; j++)
                        {
                            OracleParameter op = list[j];
                            op.Value = String.IsNullOrWhiteSpace(dbDML.FieldValues[i][j]) ?
                                (object)DBNull.Value : convertParameterValue(op, dbDML.FieldValues[i][j]);
                            command.Parameters.Add(op);
                        }
                        command.ExecuteNonQuery();
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (command != null)
                    {
                        command.Dispose();
                    }
                }
            }

            trans.Commit();
        }
        catch (Exception ex)
        {
            trans.Rollback();
            writeLog(ex.Source + "\t" + ex.Message + "\r\n\t\t\t" + sql + "\r\n");
            throw ex;
        }
        finally
        {
            con.Close();
        }
    }

    private List<OracleParameter> getParameters(string tableName, string[] fields)
    {
        List<OracleParameter> list = new List<OracleParameter>();
        DataView dataView = schemaTables[tableName].DefaultView;

        bool isFieldMatched = false;
        OracleParameter param = null;

        for (int i = 0; i < fields.Length; i++)
        {
            isFieldMatched = false;
            for (int j = 0; j < dataView.Count; j++)
            {
                if (fields[i].ToUpper() == dataView[j].Row["COLUMN_NAME"].ToString().ToUpper())
                {
                    //如果資料型別為內建，例如 Integer，則沒有必要包含大小，但您也可以指定預設大小。
                    isFieldMatched = true;
                    switch (dataView[j].Row["DATA_TYPE"].ToString())
                    {
                        case "DATE":
                            param = new OracleParameter(fields[i], OracleDbType.Date);
                            break;
                        case "TIMESTAMP":
                            param = new OracleParameter(fields[i], OracleDbType.TimeStamp);
                            break;
                        case "VARCHAR2":
                            param = new OracleParameter(fields[i], OracleDbType.Varchar2);
                            break;
                        case "NUMBER":
                            param = new OracleParameter(fields[i], OracleDbType.Decimal);
                            break;
                        case "XMLTYPE":
                            param = new OracleParameter(fields[i], OracleDbType.XmlType);
                            break;
                        case "BLOB":
                            param = new OracleParameter(fields[i], OracleDbType.Blob);
                            break;
                        default:
                            param = new OracleParameter();
                            break;
                    }
                    list.Add(param);
                    break;
                }
            }
            if (isFieldMatched == false)
            {
                list.Add(new OracleParameter());
            }
        }
        return list;
    }

    private List<OracleParameter> getInsertParameters(string tableName, string[] fields)
    {
        return getParameters(tableName, fields);
    }

    private List<OracleParameter> getDeleteParameters(string tableName, string[] whereFields)
    {
        return getParameters(tableName, whereFields);
    }

    private List<OracleParameter> getUpdateParameters(string tableName, string[] allFields)
    {
        return getParameters(tableName, allFields);
    }

    private List<OracleParameter> getSelectParameters(string tableName, string[] whereFields)
    {
        return getParameters(tableName, whereFields);
    }

    protected string getInsertString(string tableName, string[] fields)
    {
        StringBuilder sb = new StringBuilder();
        sb.Append(" INSERT INTO ");
        sb.Append(tableName);
        sb.Append(" (" + String.Join(",", fields) + ") ");
        sb.Append(" VALUES(");
        int count = 0;
        for (int i = 0; i < fields.Length; i++)
        {
            count++;
            sb.Append((i == 0 ? ":" : ",:") + count.ToString());
        }
        sb.Append(")");
        return sb.ToString();
    }

    protected string getUpdateString(string tableName, string[] fields, string[] whereFields)
    {
        return getUpdateString(tableName, fields, whereFields, null);
    }

    protected string getUpdateString(string tableName, string[] fields, string[] whereFields, string[] whereValues)
    {
        StringBuilder sb = new StringBuilder();
        sb.Append(" UPDATE ");
        sb.Append(tableName);
        sb.Append(" SET ");
        int count = 0;
        for (int i = 0; i < fields.Length; i++)
        {
            sb.Append(i == 0 ? "" : ",");
            count++;
            bool hasEqMark = fields[i].Contains(‘=‘);
            bool hasQuestionMark = fields[i].Contains(‘?‘) ;
            string temp = (hasEqMark ? ":" : "=:") + count.ToString();
            sb.Append(hasQuestionMark ? fields[i].Replace("?", temp) : fields[i] + temp);
        }

        if (whereFields.Length > 0)
        {
            for (int i = 0; i < whereFields.Length; i++)
            {
                sb.Append((i == 0 ? " WHERE " : " AND "));

                bool isNullValue = (whereValues == null ? false : String.IsNullOrWhiteSpace(whereValues[i]));
                if (!isNullValue)
                {
                    count++;
                }

                bool hasQuestionMark = whereFields[i].Contains(‘?‘) ;
                if(hasQuestionMark)
                {
                    sb.Append(whereFields[i].Replace("?", ":" + count.ToString()));
                }
                else
                {
                    bool hasOperator = hasSupportOperator(whereFields[i]);
                    sb.Append(whereFields[i] + (isNullValue ? " IS NULL " : (hasOperator ? ":" : "=:") + count.ToString()));
                }
            }
        }
        return sb.ToString();
    }

    protected string getDeleteString(string tableName, string[] whereFields)
    {
        return getDeleteString(tableName, whereFields, null);
    }

    protected string getDeleteString(string tableName, string[] whereFields, string[] whereValues)
    {
        StringBuilder sb = new StringBuilder();
        sb.Append(" DELETE ");
        sb.Append(tableName);

        int count = 0;
        if (whereFields.Length > 0)
        {
            for (int i = 0; i < whereFields.Length; i++)
            {
                sb.Append((i == 0 ? " WHERE " : " AND "));

                bool isNullValue = (whereValues == null ? false : String.IsNullOrWhiteSpace(whereValues[i]));
                if (!isNullValue)
                {
                    count++;
                }

                bool hasQuestionMark = whereFields[i].Contains(‘?‘);
                if (hasQuestionMark)
                {
                    sb.Append(whereFields[i].Replace("?", ":" + count.ToString()));
                }
                else
                {
                    bool hasOperator = hasSupportOperator(whereFields[i]);
                    sb.Append(whereFields[i] + (isNullValue ? " IS NULL " : (hasOperator ? ":" : "=:") + count.ToString()));
                }
            }
        }
        return sb.ToString();
    }

    protected string getSelectString(string tableName, string[] fields, string[] whereFields)
    {
        return getSelectString(tableName, fields, whereFields, null);
    }

    protected string getSelectString(string tableName, string[] fields, string[] whereFields, string[] whereValues)
    {
        StringBuilder sb = new StringBuilder();
        sb.Append(" SELECT " + String.Join(",", fields) + " FROM " + tableName);

        int count = 0;
        if (whereFields.Length > 0)
        {
            for (int i = 0; i < whereFields.Length; i++)
            {
                sb.Append((i == 0 ? " WHERE " : " AND "));

                bool isNullValue = (whereValues == null ? false : String.IsNullOrWhiteSpace(whereValues[i]));
                if (!isNullValue)
                {
                    count++;
                }

                bool hasQuestionMark = whereFields[i].Contains(‘?‘);
                if (hasQuestionMark)
                {
                    sb.Append(whereFields[i].Replace("?", ":" + count.ToString()));
                }
                else
                {
                    bool hasOperator = hasSupportOperator(whereFields[i]);
                    sb.Append(whereFields[i] + (isNullValue ? " IS NULL " : (hasOperator ? ":" : "=:") + count.ToString()));
                }
            }
        }
        return sb.ToString();
    }

    private bool hasSupportOperator(string target)
    {
        string[] operators = new string[] { ">", "<", "LIKE" };
        bool isFound = false;
        foreach (string op in operators)
        {
            if (target.IndexOf(op, StringComparison.CurrentCultureIgnoreCase) >= 0)
            {
                isFound = true;
                break;
            }
        }
        return isFound;
    }

    //"如果要傳回所有table的table schema，請傳入null")]
    public DataSet GetTableSchema(string tableName)
    {
        string schema = null;
        string dblink = null;

        if (String.IsNullOrEmpty(tableName) == false)
        {
            tableName = tableName.ToUpper();
            int idx = tableName.IndexOf(‘@‘);
            if (idx >= 0)
            {
                tableName = tableName.Substring(0, idx);
                dblink = tableName.Substring(idx);
            }
            idx = tableName.IndexOf(‘.‘);
            if (idx >= 0)
            {
                schema = tableName.Substring(0, idx);
                tableName = tableName.Substring(idx + 1);
            }
        }

        try
        {
            string sql = "SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, NULLABLE, COLUMN_ID ";
            if (String.IsNullOrEmpty(schema))
            {
                sql += " FROM USER_TAB_COLUMNS" + (dblink == null ? "" : dblink);
                if (String.IsNullOrEmpty(tableName) == false)
                {
                    sql += " WHERE TABLE_NAME=‘" + tableName + "‘";
                }
            }
            else
            {
                sql += " FROM DBA_TAB_COLUMNS" + (dblink == null ? "" : dblink);
                sql += " WHERE OWNER=‘" + schema + "‘ ";
                if (String.IsNullOrEmpty(tableName) == false)
                {
                    sql += " AND TABLE_NAME=‘" + tableName + "‘";
                }
            }

            sql += " ORDER BY TABLE_NAME, COLUMN_ID ";

            return ExecuteQuery(sql);
        }
        catch (Exception ex)
        {
            writeLog(ex.Source + "\t" + ex.Message + "\r\n");
            throw ex;
        }
    }

    public DataSet GetTablePK(string tableName)
    {
        string schema = null;
        string dblink = null;

        if (String.IsNullOrEmpty(tableName) == false)
        {
            tableName = tableName.ToUpper();
            int idx = tableName.IndexOf(‘@‘);
            if (idx >= 0)
            {
                tableName = tableName.Substring(0, idx);
                dblink = tableName.Substring(idx);
            }
            idx = tableName.IndexOf(‘.‘);
            if (idx >= 0)
            {
                schema = tableName.Substring(0, idx);
                tableName = tableName.Substring(idx + 1);
            }
        }

        try
        {
            string sql = "SELECT B.COLUMN_NAME, B.POSITION, A.TABLE_NAME ";
            if (String.IsNullOrEmpty(schema))
            {
                sql += " FROM USER_CONSTRAINTS" + (dblink == null ? "" : dblink) + " A, " +
                    " USER_CONS_COLUMNS" + (dblink == null ? "" : dblink) + " B ";
            }
            else
            {
                sql += " FROM DBA_CONSTRAINTS" + (dblink == null ? "" : dblink) + " A, " +
                    " DBA_CONS_COLUMNS" + (dblink == null ? "" : dblink) + " B ";
            }
            sql += " WHERE A.OWNER=B.OWNER AND A.CONSTRAINT_NAME=B.CONSTRAINT_NAME ";
            if (String.IsNullOrEmpty(schema) == false)
            {
                sql += " AND A.OWNER=‘" + schema + "‘ AND B.OWNER=‘" + schema + "‘";
            }
            if (String.IsNullOrEmpty(tableName) == false)
            {
                sql += " AND A.TABLE_NAME=‘" + tableName + "‘";
            }
            sql += " AND A.CONSTRAINT_TYPE=‘P‘ ORDER BY POSITION";

            return ExecuteQuery(sql);
        }
        catch (Exception ex)
        {
            writeLog(ex.Source + "\t" + ex.Message + "\r\n");
            throw ex;
        }
    }

    protected void addTableSchema(string tableName)
    {
        if (schemaTables == null)
        {
            schemaTables = new Dictionary<string, DataTable>();
        }

        if (!schemaTables.ContainsKey(tableName))
        {
            schemaTables[tableName] = this.GetTableSchema(tableName).Tables[0];
        }
    }

    protected void addTablePK(string tableName)
    {
        if (pkTables == null)
        {
            pkTables = new Dictionary<string, DataTable>();
        }

        if (!pkTables.ContainsKey(tableName))
        {
            pkTables[tableName] = this.GetTablePK(tableName).Tables[0];
        }
    }

    public void SendMail(MailInformation mail)
    {
        string smtpHost = "nhsmtp.cminl.oa";
        MailAddress ma = null;
        try
        {
            System.Net.Mail.MailMessage message = new System.Net.Mail.MailMessage();
            message.Subject = HttpUtility.UrlDecode(mail.Subject);
            message.IsBodyHtml = mail.IsBodyHtml;
            message.Body = HttpUtility.UrlDecode(mail.Body) + (mail.IsBodyHtml ? "<br><br><br>" : "\r\n\r\n");
            message.SubjectEncoding = Encoding.GetEncoding(mail.CodePage);
            message.BodyEncoding = Encoding.GetEncoding(mail.CodePage);

            ma = new MailAddress(mail.From.Address, mail.From.Name);
            message.From = ma;

            if (mail.Object.ToAddresses != null)
            {
                foreach (string address in mail.Object.ToAddresses)
                {
                    if (address.Contains("@"))
                    {
                        message.To.Add(address.Trim());
                    }
                }
            }

            if (mail.Object.CcAddresses != null)
            {
                foreach (string address in mail.Object.CcAddresses)
                {
                    if (address.Contains("@"))
                    {
                        message.CC.Add(address.Trim());
                    }
                }
            }

            if (mail.Object.BccAddresses != null)
            {
                foreach (string address in mail.Object.BccAddresses)
                {
                    if (address.Contains("@"))
                    {
                        message.Bcc.Add(address.Trim());
                    }
                }
            }

            if (mail.AttachFiles != null)
            {
                foreach (AttachFile file in mail.AttachFiles)
                {
                    byte[] bytes = Convert.FromBase64String(file.Base64Content);
                    MemoryStream ms = new MemoryStream(bytes);
                    Attachment attach = new Attachment(ms, file.FileName, getMediaType(file.FileName));
                    attach.NameEncoding = Encoding.GetEncoding(mail.CodePage);
                    ContentDisposition disposition = attach.ContentDisposition;
                    disposition.Inline = (file.Inline ? true : false);
                    message.Attachments.Add(attach);
                }
            }

            System.Net.Mail.SmtpClient smtp = new System.Net.Mail.SmtpClient(smtpHost);
            smtp.Send(message);
        }
        catch (Exception ex)
        {
            writeLog("Unable to send mail.\r\n\t" + ex.GetBaseException().Message + "\r\n");
        }
    }

    private string getMediaType(string fileName)
    {
        string ext = fileName.Substring(fileName.LastIndexOf(‘.‘) + 1);
        string mediaType = MediaTypeNames.Application.Octet;
        switch (ext.ToLower())
        {
            case "pdf":
                mediaType = MediaTypeNames.Application.Pdf;
                break;
            case "rtf":
                mediaType = MediaTypeNames.Application.Rtf;
                break;
            case "zip":
                mediaType = MediaTypeNames.Application.Zip;
                break;
            case "gif":
                mediaType = MediaTypeNames.Image.Gif;
                break;
            case "jpg":
            case "jpeg":
                mediaType = MediaTypeNames.Image.Jpeg;
                break;
            case "tif":
            case "tiff":
                mediaType = MediaTypeNames.Image.Tiff;
                break;
            case "htm":
            case "html":
                mediaType = MediaTypeNames.Text.Html;
                break;
            case "xml":
                mediaType = MediaTypeNames.Text.Xml;
                break;
            case "txt":
                mediaType = MediaTypeNames.Text.Plain;
                break;
        }
        return mediaType;
    }

    protected void writeLog(string detailDesc)
    {
        string fullText = null;

        using (var mutex = new Mutex(false, this.GetType().Name))
        {
            if (!mutex.WaitOne(TimeSpan.FromSeconds(3), false))
            {
                return;
            }

            try
            {
                string logFile = HostingEnvironment.MapPath("~/Log/" + this.GetType().Name + "_" + DateTime.Now.ToString("yyyyMMdd") + ".txt");
                fullText = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "\t" + clientAddress + "\t" + detailDesc;
                checkFile(logFile);
                using (System.IO.StreamWriter sw = System.IO.File.AppendText(logFile))
                {
                    sw.WriteLine(fullText);
                    sw.Flush();
                    sw.Close();
                    sw.Dispose();
                }
            }
            catch
            { }
            finally
            {
                try
                {
                    mutex.ReleaseMutex();
                }
                catch
                {
                }
            }
        }
    }

    private void checkFile(string fileName)
    {
        if (!System.IO.File.Exists(fileName))
        {
            System.IO.StreamWriter sw = System.IO.File.CreateText(fileName);
            sw.Close();
            sw.Dispose();
        }
    }

    protected bool toStringBuilder(StringBuilder sb, DbDML dbDML)
    {
        try
        {
            sb.Append("DbDML \t TableName: " + dbDML.TableName + "\r\n");
            sb.Append(String.Join("^", dbDML.Fields) + "\r\n");
            for (int i = 0; i < dbDML.FieldValues.GetLength(0); i++)
            {
                sb.Append(String.Join("^", dbDML.FieldValues[i]) + "\r\n");
            }
            return true;
        }
        catch (Exception ex)
        {
            writeLog(ex.Message + "\r\n" + ex.TargetSite.Name + "\r\n");
            return false;
        }
    }

    protected bool toStringBuilder(StringBuilder sb, DbTreatment[] dbTreatments)
    {
        try
        {
            foreach (DbTreatment dbTreatment in dbTreatments)
            {
                sb.Append("DbTreatment \t TableName: " + dbTreatment.TableName + "\t DML:" + dbTreatment.DMLType.ToString() + "\r\n");
                sb.Append("CommandText: \t" + (String.IsNullOrEmpty(dbTreatment.CommandText) ? "" : dbTreatment.CommandText) + "\r\n");
                if (dbTreatment.Fields != null && dbTreatment.Fields.Length > 0)
                {
                    sb.Append(String.Join("^", dbTreatment.Fields) + "\r\n");
                    for (int i = 0; i < dbTreatment.FieldValues.GetLength(0); i++)
                    {
                        sb.Append(String.Join("^", dbTreatment.FieldValues[i]) + "\r\n");
                    }
                }
                if (dbTreatment.WhereFields != null && dbTreatment.WhereFields.Length > 0)
                {
                    sb.Append(String.Join("^", dbTreatment.WhereFields) + "\r\n");
                    for (int i = 0; i < dbTreatment.WhereValues.GetLength(0); i++)
                    {
                        sb.Append(String.Join("^", dbTreatment.WhereValues[i]) + "\r\n");
                    }
                }
            }
            return true;
        }
        catch (Exception ex)
        {
            writeLog(ex.Message + "\r\n" + ex.TargetSite.Name + "\r\n");
            return false;
        }
    }

    protected bool toStringBuilder(StringBuilder sb, DbInsert[] dbInserts)
    {
        try
        {
            foreach (DbInsert dbInsert in dbInserts)
            {
                sb.Append("DbInsert \t TableName: " + dbInsert.TableName + "\r\n");
                sb.Append(String.Join("^", dbInsert.Fields) + "\r\n");
                sb.Append(String.Join("^", dbInsert.FieldValues) + "\r\n");
            }
            return true;
        }
        catch (Exception ex)
        {
            writeLog(ex.Message + "\r\n" + ex.TargetSite.Name + "\r\n");
            return false;
        }
    }

    protected bool toStringBuilder(StringBuilder sb, DbUpdate[] dbUpdates)
    {
        try
        {
            foreach (DbUpdate dbUpdate in dbUpdates)
            {
                sb.Append("DbUpdate \t TableName: " + dbUpdate.TableName + "\r\n");
                sb.Append(String.Join("^", dbUpdate.Fields) + "\r\n");
                sb.Append(String.Join("^", dbUpdate.FieldValues) + "\r\n");
                sb.Append(String.Join("^", dbUpdate.WhereFields) + "\r\n");
                sb.Append(String.Join("^", dbUpdate.WhereValues) + "\r\n");
            }
            return true;
        }
        catch (Exception ex)
        {
            writeLog(ex.Message + "\r\n" + ex.TargetSite.Name + "\r\n");
            return false;
        }
    }

    protected bool toStringBuilder(StringBuilder sb, DbDelete[] dbDeletes)
    {
        try
        {
            foreach (DbDelete dbDelete in dbDeletes)
            {
                sb.Append("DbDelete \t TableName: " + dbDelete.TableName + "\r\n");
                sb.Append(String.Join("^", dbDelete.WhereFields) + "\r\n");
                sb.Append(String.Join("^", dbDelete.WhereValues) + "\r\n");
            }
            return true;
        }
        catch (Exception ex)
        {
            writeLog(ex.Message + "\r\n" + ex.TargetSite.Name + "\r\n");
            return false;
        }
    }

    protected bool toStringBuilder(StringBuilder sb, string dmlType, string tableName, string[] fields, string[] fieldValues, string[] whereFields, string[] whereValues)
    {
        try
        {
            sb.Append("DML Type: " + dmlType + "\t TableName: " + tableName + "\r\n");
            if (fields != null && fields.Length > 0)
            {
                sb.Append(String.Join("^", fields) + "\r\n");
                sb.Append(String.Join("^", fieldValues) + "\r\n");
            }

            if (whereFields != null && whereFields.Length > 0)
            {
                sb.Append(String.Join("^", whereFields) + "\r\n");
                sb.Append(String.Join("^", whereValues) + "\r\n");
            }
            return true;
        }
        catch (Exception ex)
        {
            writeLog(ex.Message + "\r\n" + ex.TargetSite.Name + "\r\n");
            return false;
        }
    }

    private string clientAddress
    {
        get
        {
            RemoteEndpointMessageProperty clientEndpoint =
                OperationContext.Current.IncomingMessageProperties[RemoteEndpointMessageProperty.Name] as RemoteEndpointMessageProperty;
            return clientEndpoint.Address;
        }
    }



    
}