private clsResponse _SelectToFile(string pSQL)
{
clsResponse response = new clsResponse();
string msg = "";
string fileName = Path.Combine(
clsSystemInfo.xTempDirectory, "./file.xml");
try
{
////OleDbDataAdapter da = new OleDbDataAdapter(pSQL, this.myConnection_);
SqlDataAdapter da = new SqlDataAdapter(pSQL, this.myConnection_);
DataSet ds = new DataSet();
da.Fill(ds);
ds.WriteXml(fileName, XmlWriteMode.IgnoreSchema);
response.xSetReturn("FileName", fileName);
}
catch (Exception ex)
{
msg = "SELECTに失敗しました。";
msg += "(" + myDBInfo_.xDBName + ")" + "(" + pSQL + ")" + Environment.NewLine;
msg += ex.Message + Environment.NewLine;
response.xSetError(msg);
msg += ex.StackTrace;
////////clsTextLogger.xWriteTextLog(
//////// MethodBase.GetCurrentMethod().DeclaringType.FullName + "." +
//////// MethodBase.GetCurrentMethod().Name, msg);
}
return(response);
}
private clsResponse _ReaderOpen(string pSQL)
{
clsResponse response = new clsResponse();
try
{
////OleDbCommand command = new OleDbCommand(pSQL, this.myConnection_, this.myTransaction_);
NpgsqlCommand command = new NpgsqlCommand(pSQL, this.myConnection_, this.myTransaction_);
////myReader_ = (OleDbDataReader)command.ExecuteReader();
myReader_ = (NpgsqlDataReader)command.ExecuteReader();
}
catch (Exception ex)
{
string msg = "DataReaderのOpenに失敗しました。";
msg += "(" + myDBInfo_.xDBName + ")" + "(" + pSQL + ")" + Environment.NewLine;
msg += ex.Message + Environment.NewLine;
response.xSetError(msg);
msg += ex.StackTrace;
////////clsTextLogger.xWriteTextLog(
//////// MethodBase.GetCurrentMethod().DeclaringType.FullName + "." +
//////// MethodBase.GetCurrentMethod().Name, msg);
}
finally
{
myTable_ = null;
}
return response;
}
private clsResponse _ExecuteSQL(string pSQL, bool pWithTransaction, int pCommandTimeout)
{
clsResponse response = new clsResponse();
string msg = "";
////OleDbCommand command = new OleDbCommand();
NpgsqlCommand command = new NpgsqlCommand();
int count = 0;
try
{
if (pWithTransaction)
{
myTransaction_ = myConnection_.BeginTransaction();
}
if (pCommandTimeout > 0)
{
command.CommandTimeout = pCommandTimeout;
}
else
{
// 2018/01/13
command.CommandTimeout = 300;
}
command.Connection = myConnection_;
command.Transaction = myTransaction_;
command.CommandText = pSQL;
command.CommandType = CommandType.Text;
count = command.ExecuteNonQuery();
response.xSetReturn("-- ResultCount", count.ToString());
if (pWithTransaction)
{
myTransaction_.Commit();
}
}
catch (Exception ex)
{
if (pWithTransaction) myTransaction_.Rollback();
msg = "SQLの実行に失敗しました。" + Environment.NewLine;
msg += "(" + myDBInfo_.xDBName + ")" + "(" + pSQL + ")" + Environment.NewLine;
msg += ex.Message + Environment.NewLine;
response.xSetError(msg);
msg += ex.StackTrace;
////////clsTextLogger.xWriteTextLog(
//////// MethodBase.GetCurrentMethod().DeclaringType.FullName + "." +
//////// MethodBase.GetCurrentMethod().Name, msg);
}
finally
{
if (command != null)
{
command.Dispose();
command = null;
}
}
return response;
}
private clsResponse _ReaderRead()
{
clsResponse response = new clsResponse();
string msg = "";
DataTable dt = null;
try
{
if (myReader_.Read())
{
if (myTable_ == null)
{
myTable_ = new DataTable();
dt = myReader_.GetSchemaTable();
DataColumn column = null;
for (int i = 0; i < dt.Rows.Count; i++)
{
column = new DataColumn();
column.ColumnName = dt.Rows[i]["ColumnName"].ToString();
column.DataType = Type.GetType(dt.Rows[i]["DataType"].ToString());
myTable_.Columns.Add(column);
}
}
dt = myTable_.Clone();
DataRow row = dt.NewRow();
for (int i = 0; i < myReader_.FieldCount; i++)
{
row[i] = myReader_[i];
}
dt.Rows.Add(row);
response.xAddDataTable(dt);
}
else
{
response.xSetReturn("EOF", "True");
_ReaderClose();
return response;
}
}
catch (Exception ex)
{
msg = "DataReaderからの読み込みに失敗しました。";
msg += ex.Message + Environment.NewLine;
response.xSetError(msg);
msg += ex.StackTrace;
////////clsTextLogger.xWriteTextLog(
//////// MethodBase.GetCurrentMethod().DeclaringType.FullName + "." +
//////// MethodBase.GetCurrentMethod().Name, msg);
_ReaderClose();
return response;
}
return response;
}
private clsResponse _DBOpen()
{
clsResponse response = new clsResponse();
string msg = "";
string host = myDBInfo_.xDBServer;
string dbName = myDBInfo_.xDBName;
string userID = myDBInfo_.xDBUserID;
string password = myDBInfo_.xDBPassword;
string timeOutSeconds = myDBInfo_.xDBTimeoutSeconds.ToString();
////string connectionString = "";
////connectionString = "Provider=SQLOLEDB.1;";
////connectionString += "User ID=" + userID + ";";
////connectionString += "Password="******";";
////connectionString += "Persist Security Info=True;";
////connectionString += "Initial Catalog=" + dbName + ";";
////connectionString += "Data Source=" + host + ";";
////connectionString += "Connect Timeout=" + timeOutSeconds + ";";
string connectionString = "";
connectionString += "Persist Security Info=False;";
connectionString += "User ID=" + userID + ";";
connectionString += "Password="******";";
connectionString += "Initial Catalog=" + dbName + ";";
connectionString += "Server=" + host + ";";
if (myDBInfo_.xDBReadOnly)
{
connectionString += "ApplicationIntent=ReadOnly;";
}
//myConnection_ = new OleDbConnection();
myConnection_ = new SqlConnection();
try
{
myConnection_.ConnectionString = connectionString;
myConnection_.Open();
}
catch (Exception ex)
{
msg = "ＤＢのオープンに失敗しました。";
msg += ex.Message + Environment.NewLine;
response.xSetError(msg);
msg += ex.StackTrace;
////////clsTextLogger.xWriteTextLog(
//////// MethodBase.GetCurrentMethod().DeclaringType.FullName + "."
//////// + MethodBase.GetCurrentMethod().Name, msg);
}
return(response);
}
internal override clsResponse xRollBack()
{
clsResponse response = new clsResponse();
try
{
myTransaction_.Rollback();
}
catch (Exception ex)
{
response.xSetError(ex.Message + ex.StackTrace);
}
return response;
}
internal override clsResponse xBeginTrans()
{
clsResponse response = new clsResponse();
try
{
myTransaction_ = myConnection_.BeginTransaction();
}
catch (Exception ex)
{
response.xSetError(ex.Message + ex.StackTrace);
}
return response;
}
internal override clsResponse xCommit()
{
clsResponse response = new clsResponse();
try
{
myTransaction_.Commit();
}
catch (Exception ex)
{
response.xSetError(ex.Message + ex.StackTrace);
}
return(response);
}
private clsResponse _DBOpen()
{
clsResponse response = new clsResponse();
string msg = "";
NpgsqlConnectionStringBuilder sb = new NpgsqlConnectionStringBuilder();
sb.Host = myDBInfo_.xDBServer;
sb.Port = Convert.ToInt32(myDBInfo_.xDBPort);
sb.Database = myDBInfo_.xDBName;
sb.Username = myDBInfo_.xDBUserID;
sb.Password = myDBInfo_.xDBPassword;
if (myDBInfo_.xDBSslMode == "true")
{
sb.SslMode = SslMode.Require;
}
sb.Timeout = 20;
sb.CommandTimeout = 20;
myConnection_ = new NpgsqlConnection(sb.ConnectionString);
if (myDBInfo_.xDBReadOnly)
{
myConnection_.ConnectionString += "ApplicationIntent=ReadOnly;";
}
try
{
myConnection_.Open();
}
catch (Exception ex)
{
msg = "ＤＢのオープンに失敗しました。";
msg += ex.Message + Environment.NewLine;
response.xSetError(msg);
msg += ex.StackTrace;
////clsTextLogger.xWriteTextLog(
//// MethodBase.GetCurrentMethod().DeclaringType.FullName + "."
//// + MethodBase.GetCurrentMethod().Name, msg);
}
return response;
}
private clsResponse _ToTabString(string pTableName, string pOutDirName, string pOutFileName)
{
clsResponse response = new clsResponse();
string msg = "";
string SQL = "";
string items = "";
string values = "";
long count = 0;
string fileName = "";
StreamWriter sw = null;
try
{
SQL = "Select * from " + pTableName;
////OleDbCommand command = new OleDbCommand();
NpgsqlCommand command = new NpgsqlCommand();
command.Connection = this.myConnection_;
command.CommandText = SQL;
////OleDbDataReader dr = command.ExecuteReader();
NpgsqlDataReader dr = command.ExecuteReader();
fileName = Path.GetFullPath(Path.Combine(pOutDirName, pOutFileName));
sw = new StreamWriter(fileName, false, Encoding.Default);
count = 0;
while (dr.Read())
{
Thread.Sleep(0);
count++;
if (count == 1)
{
items = "";
for (int i = 0; i < dr.FieldCount; i++)
{
if (i > 0) items += "\t";
items += dr.GetName(i);
}
sw.WriteLine(items);
}
values = "";
for (int i = 0; i < dr.FieldCount; i++)
{
if (i > 0) values += "\t";
values += dr.GetValue(i).ToString();
}
sw.WriteLine(values);
}
}
catch (Exception ex)
{
msg = "テーブルのバックアップに失敗しました。";
msg += "(" + pTableName + ")" + Environment.NewLine;
msg += ex.Message + Environment.NewLine;
response.xSetError(msg);
msg += ex.StackTrace;
////////clsTextLogger.xWriteTextLog(
//////// MethodBase.GetCurrentMethod().DeclaringType.FullName + "." +
//////// MethodBase.GetCurrentMethod().Name, msg);
}
finally
{
if (sw != null)
{
sw.Close();
sw = null;
}
}
return response;
}
private clsResponse _Select(string pSQL)
{
clsResponse response = new clsResponse();
////OleDbDataAdapter da = null;
NpgsqlDataAdapter da = null;
DataTable dt = null;
string msg = "";
int count = 0;
////OleDbCommand command = new OleDbCommand();
NpgsqlCommand command = new NpgsqlCommand();
command.Connection = this.myConnection_;
command.Transaction = this.myTransaction_;
command.CommandText = pSQL;
command.CommandType = CommandType.Text;
// 2018/01/13
command.CommandTimeout = 300;
try
{
////da = new OleDbDataAdapter(command);
da = new NpgsqlDataAdapter(command);
dt = new DataTable();
count = da.Fill(dt);
response.xAddDataTable(dt);
// 2017/12/13 K.Nakamura
////////msg = "SQL=" + pSQL;
////////clsTextLogger.xWriteTextLog(
//////// MethodBase.GetCurrentMethod().DeclaringType.FullName + "." +
//////// MethodBase.GetCurrentMethod().Name, msg);
////////msg = "取得件数=" + count.ToString("#,##0 件");
////////clsTextLogger.xWriteTextLog(
//////// MethodBase.GetCurrentMethod().DeclaringType.FullName + "." +
//////// MethodBase.GetCurrentMethod().Name, msg);
}
catch (Exception ex)
{
msg = "SELECTに失敗しました。";
msg += "(" + myDBInfo_.xDBName + ")" + "(" + pSQL + ")" + Environment.NewLine;
msg += ex.Message + Environment.NewLine;
response.xSetError(msg);
msg += ex.StackTrace;
////////clsTextLogger.xWriteTextLog(
//////// MethodBase.GetCurrentMethod().DeclaringType.FullName + "." +
//////// MethodBase.GetCurrentMethod().Name, msg);
}
finally
{
response.xSetReturn("-- ResultCount", count.ToString());
if (command != null)
{
command.Connection = null;
command.Transaction = null;
command.Dispose();
command = null;
}
if (da != null)
{
da.Dispose();
da = null;
}
if (dt != null)
{
dt.Rows.Clear();
dt.Columns.Clear();
dt.Dispose();
dt = null;
}
}
return response;
}
//####################################################################
//
// private function
//
//####################################################################
private clsResponse _WriteDBJobLog(string pDomain, string[] pPara)
{
clsResponse response = new clsResponse();
string msg = "";
string 利用者コード = pPara[0];
string 端末名 = pPara[1];
string ジョブID = pPara[2];
string セッションID = pPara[3];
string 発信元 = pPara[4];
string ログメッセージ = pPara[5];
string IPアドレス = pPara[6];
DateTime dateTime = clsSystemInfo.xNow;
StringBuilder SQL = new StringBuilder();
SQL.Append("insert into TBLログ (");
SQL.Append("処理日時,");
SQL.Append("ジョブID,");
SQL.Append("PID,");
SQL.Append("セッションID,");
SQL.Append("ログメッセージ,");
SQL.Append("発信元,");
SQL.Append("利用者コード,");
SQL.Append("端末名,");
SQL.Append("IPアドレス)");
SQL.Append(" values(");
SQL.Append("N'" + clsUtil.xSanitizeSQL(dateTime.ToString("yyyy/MM/dd HH:mm:ss")) + "',");
SQL.Append("N'" + clsUtil.xSanitizeSQL(ジョブID) + "',");
SQL.Append("N'" + clsUtil.xSanitizeSQL(System.Diagnostics.Process.GetCurrentProcess().Id.ToString()) + "',");
SQL.Append("N'" + clsUtil.xSanitizeSQL(セッションID) + "',");
SQL.Append("N'" + clsUtil.xSanitizeSQL(ログメッセージ) + "',");
SQL.Append("N'" + clsUtil.xSanitizeSQL(発信元) + "',");
SQL.Append("N'" + clsUtil.xSanitizeSQL(利用者コード) + "',");
SQL.Append("N'" + clsUtil.xSanitizeSQL(端末名) + "',");
SQL.Append("N'" + clsUtil.xSanitizeSQL(IPアドレス) + "')");
clsDB db = null;
string kindOfDB = pDomain + "_" + cnstDBKind.Log;
try
{
db = new clsDB(clsDBInfoPool.xGetInstance().xGetDBInfo(kindOfDB));
response = db.xDBOpen();
if (response.xHasError)
{
msg = "DBLogの書き込みに失敗しました！(DBOpen)";
msg += Environment.NewLine + response.xMessage;
clsTextLogger.xWriteTextLog(
MethodBase.GetCurrentMethod().DeclaringType.FullName + "." +
MethodBase.GetCurrentMethod().Name, msg);
return(response);
}
response = db.xExecuteSQL(SQL.ToString());
if (response.xHasError)
{
msg = "DBLogの書き込みに失敗しました！(ExecuteSQL)";
msg += Environment.NewLine + response.xMessage;
clsTextLogger.xWriteTextLog(
MethodBase.GetCurrentMethod().DeclaringType.FullName + "." +
MethodBase.GetCurrentMethod().Name, msg);
return(response);
}
}
catch (Exception ex)
{
msg = "DBログ出力中にエラーが発生しました！";
msg += ex.Message + Environment.NewLine;
response.xSetError(msg);
msg += ex.StackTrace;
clsTextLogger.xWriteTextLog(
MethodBase.GetCurrentMethod().DeclaringType.FullName + "."
+ MethodBase.GetCurrentMethod().Name, msg);
return(response);
}
finally
{
if (db != null)
{
db.xDBClose();
db.Dispose();
db = null;
}
}
return(response);
}