OracleService類
2024-09-27 04:11:38
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 = ;
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;
}
} }
最新文章
- cef3 获得js 返回值， 以及js 指挥delphi 函数的 总结参考
- Java Sha1 加密算法
- Visual Tracker Benchmark
- 四、maya python plugin
- final-----finalize----finally---区别
- ASP.NET MVC4 log4net
- Objective－C(面向对象的三大特性)
- 细说SQL性能优化
- location.href IE6 下不起作用的罪魁祸首
- JavaScript对滚动栏的操作
- Ionic的项目结构-工程目录
- Web自动化框架LazyUI使用手册（4）--控件抓取工具Elements Extractor详解（批量抓取）
- SQL Server使用sp_rename重命名约束注意事项
- [2019.03.22] Linux 学习心得（1）
- SyntaxError: Unexpected token ' in JSON at position 2
- 【ARC072E】Alice in linear land DP
- Apache Zeppelin 初识
- PHP中session_start 函数详解使用方法
- ZOJ - 4089 :Little Sub and Isomorphism Sequences (同构 set)
- 问题描述： fatal error: 'XCTest/XCTest.h' file not found
热门文章
- jfreechart应用2--柱状图（作者：百度 被风吹过的日子）
- Newtonsoft.Json.dll 反序列化JSON字符串
- codeforces 689E E. Mike and Geometry Problem(组合数学)
- [Usaco2013 DEC] Vacation Planning
- box-shadow 模拟border
- SqlSugar解决SQLite访问的问题:Unable to load DLL 'SQLite.Interop.dll'
- CodeForces 722A Broken Clock (水题)
- bzoj 4552: [Tjoi2016&Heoi2016]排序【二分+线段树】
- bzoj 4071: [Apio2015]巴邻旁之桥【splay】
- 原生js一行代码实现简易轮播图