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 schemaTables;
protected Dictionary pkTables;
protected Circumstances SapEenvironment;

public OracleService()  
{  
    con = new OracleConnection();  
}  
public StoredProcedureParameter\[\] GetStoredProcedureValues(string procedureName, StoredProcedureParameter\[\] spps)  
{  
    OracleCommand command = new OracleCommand(procedureName, con);  
    command.CommandTimeout = ;  
    command.CommandType = CommandType.StoredProcedure;  
    command.BindByName = true;  
    for (int i = ; 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 = ; 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 = ;  
    command.CommandType = CommandType.StoredProcedure;

    for (int i = ; 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 = ;  
    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 = ;  
    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 = ; 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, "");  
        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 = ;  
    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\[\].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 = ; 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 = ; 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 = ; 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 = ; 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, "");  
        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 = ;  
    try  
    {  
        con.Open();  
        trans = con.BeginTransaction();  
        List<string> listTemp = new List<string>();

        for (int i = ; 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 = ; 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 = ;  
    try  
    {  
        con.Open();  
        trans = con.BeginTransaction();  
        for (int i = ; 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 = ; 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 = ;  
    try  
    {  
        con.Open();  
        trans = con.BeginTransaction();  
        for (int i = ; 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 = ; 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 = ; i < insertFields.Length; i++)  
    {  
        int idx = insertFields\[i\].IndexOf('=');  
        insertFields\[i\] = (idx == -) ? insertFields\[i\] : insertFields\[i\].Substring(, 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 = ; 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 = ; i < fieldValues.Length; i++)  
    {  
        OracleParameter op = command.Parameters\[i\];  
        op.Value = String.IsNullOrWhiteSpace(fieldValues\[i\]) ?  
            (object)DBNull.Value : convertParameterValue(op, fieldValues\[i\]);  
    }  
    int count = ;  
    for (int i = ; 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 = ;  
    for (int i = ; 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 = ;  
    int idx2 = ;  
    string sql = null;

    try  
    {  
        con.Open();  
        trans = con.BeginTransaction();  
        for (int i = ; 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 = ; 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 = ;  
                    for (int j = ; j < treatments\[i\].Fields.Length; j++)  
                    {  
                        bool isFound = false;  
                        for (int k = ; 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 = ; 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, );  
                        for (int j = ; 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, );  
                        for (int j = ; 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 = ; j < treatments\[i\].FieldValues.Length; j++)  
                        {  
                            idx2 = j;  
                            int affectRows = ;  
                            try  
                            {  
                                if (pkIdx.Length > )   //避免因為沒有主鍵造成表的所有row內容被改為相同  
                                {  
                                    string\[\] fieldValues = new string\[nonPkIdx.Length\];  
                                    string\[\] whereValues = new string\[pkIdx.Length\];

                                    for (int k = ; k < fieldValues.Length; k++)  
                                    {  
                                        fieldValues\[k\] = treatments\[i\].FieldValues\[j\]\[nonPkIdx\[k\]\];  
                                    }  
                                    for (int k = ; 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 == )  
                                {  
                                    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 = ; i < dbDML.Fields.Length; i++)  
        {  
            isPK = false;  
            for (int j = ; 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 = ; 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 = ;  
                if (lstPkFields.Count > )  
                {  
                    command = new OracleCommand(sqlU, con);  
                    list = getUpdateParameters(dbDML.TableName, lstAllFields.ToArray());  
                    for (int j = ; 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 == )  
                {  
                    command = new OracleCommand(sqlI, con);  
                    list = getInsertParameters(dbDML.TableName, dbDML.Fields);  
                    for (int j = ; 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 = ; i < fields.Length; i++)  
    {  
        isFieldMatched = false;  
        for (int j = ; 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 = ;  
    for (int i = ; i < fields.Length; i++)  
    {  
        count++;  
        sb.Append((i ==  ? ":" : ",:") + 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 = ;  
    for (int i = ; i < fields.Length; i++)  
    {  
        sb.Append(i ==  ? "" : ",");  
        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 > )  
    {  
        for (int i = ; i < whereFields.Length; i++)  
        {  
            sb.Append((i ==  ? " 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 = ;  
    if (whereFields.Length > )  
    {  
        for (int i = ; i < whereFields.Length; i++)  
        {  
            sb.Append((i ==  ? " 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 = ;  
    if (whereFields.Length > )  
    {  
        for (int i = ; i < whereFields.Length; i++)  
        {  
            sb.Append((i ==  ? " 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) >= )  
        {  
            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 >= )  
        {  
            tableName = tableName.Substring(, idx);  
            dblink = tableName.Substring(idx);  
        }  
        idx = tableName.IndexOf('.');  
        if (idx >= )  
        {  
            schema = tableName.Substring(, idx);  
            tableName = tableName.Substring(idx + );  
        }  
    }

    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 >= )  
        {  
            tableName = tableName.Substring(, idx);  
            dblink = tableName.Substring(idx);  
        }  
        idx = tableName.IndexOf('.');  
        if (idx >= )  
        {  
            schema = tableName.Substring(, idx);  
            tableName = tableName.Substring(idx + );  
        }  
    }

    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\[\];  
    }  
}

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

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

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('.') + );  
    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(), 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 = ; i < dbDML.FieldValues.GetLength(); 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 > )  
            {  
                sb.Append(String.Join("^", dbTreatment.Fields) + "\\r\\n");  
                for (int i = ; i < dbTreatment.FieldValues.GetLength(); i++)  
                {  
                    sb.Append(String.Join("^", dbTreatment.FieldValues\[i\]) + "\\r\\n");  
                }  
            }  
            if (dbTreatment.WhereFields != null && dbTreatment.WhereFields.Length > )  
            {  
                sb.Append(String.Join("^", dbTreatment.WhereFields) + "\\r\\n");  
                for (int i = ; i < dbTreatment.WhereValues.GetLength(); 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 > )  
        {  
            sb.Append(String.Join("^", fields) + "\\r\\n");  
            sb.Append(String.Join("^", fieldValues) + "\\r\\n");  
        }

        if (whereFields != null && whereFields.Length > )  
        {  
            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;  
    }  
}

}