/// <summary>
/// SQL Accessorのインスタンスを取得する
/// </summary>
/// <param name="mode"></param>
/// <param name="server"></param>
/// <param name="userId"></param>
/// <param name="password"></param>
/// <param name="database"></param>
/// <returns></returns>
public static SqlAccessor GetSqlAccessor(AuthenticateMode mode, string server, string userId, string password, string database)
{
if (mode == AuthenticateMode.SqlServer)
{
return(SqlAccessor.CreateInstance(server, userId, password, database, Config.ConnectTimeout, Config.CommandTimeout));
}
else
{
return(SqlAccessor.CreateInstance(server, database, Config.ConnectTimeout, Config.CommandTimeout));
}
}
internal LoginInfo GetLoginInfo(int userId)
{
var param = new ParameterCollection();
param.Add("@Id", DbType.Int32, userId);
var result = SqlAccessor.ExecuteQuery(DatabaseSetting.Authentication.ConnectionString, DatabaseSetting.Authentication.StoredProcedures.UsersSelectByUserName,
param);
return((result != null && result.Rows.Count > 0) ? ToLoginInfo(result.Rows[0]) : null);
}
private void InitSpType()
{
using (SqlAccessor sqlAccessor = Accessor.AccessorFactory.GetSqlAccessor())
{
DataTable dtt = sqlAccessor.GetCodeList(CodeType.ListType);
this.cmbSPType.Items.Clear();
this.cmbSPType.DisplayMember = "DISPLAY_NAME";
this.cmbSPType.ValueMember = "SP_TYPE";
this.cmbSPType.DataSource = dtt;
}
}
public User Get(string userName)
{
var param = new ParameterCollection();
param.Add("@UserName", DbType.AnsiString, userName);
var result = SqlAccessor.ExecuteQuery(DatabaseSetting.Authentication.ConnectionString, DatabaseSetting.Authentication.StoredProcedures.UsersSelectByUserName,
param);
return((result != null && result.Rows.Count > 0) ? ToUser(result.Rows[0]) : null);
}
private bool InitDatabaseList(SqlAccessor sqlAccessor)
{
cmbDataBase.Items.Clear();
if (!sqlAccessor.TryConnect())
{
return(false);
}
List <string> dbs = sqlAccessor.GetDatabases();
cmbDataBase.Items.AddRange(dbs.ToArray());
return(true);
}
/// <summary>
/// 取得
/// </summary>
/// <param name="id">ID</param>
/// <returns>取得できた場合、テーブルのレコード（取得できなかった場合はnull）</returns>
private DataRowView Get(string id)
{
using (var accessor = new SqlAccessor())
using (var statement = new SqlStatement("Sample", "Get"))
{
var ht = new Hashtable
{
{ "id", id }
};
var dv = statement.SelectSingleStatementWithOC(accessor, ht);
return((dv.Count != 0) ? dv[0] : null);
}
}
/// <summary>
/// ログイン処理時にユーザ情報を取得する
/// </summary>
/// <param name="loginId"></param>
/// <returns></returns>
private DataRowView Get()
{
using (var accessor = new SqlAccessor())
using (var statement = new SqlStatement("BBSUser", "Get"))
{
var ht = new Hashtable
{
{ BBSConst.SQL_PRAM_LOGIN_ID, inpLoginId.Value },
{ BBSConst.SQL_PRAM_PASSWORD, inpPassword.Value }
};
var dv = statement.SelectSingleStatementWithOC(accessor, ht);
return((dv.Count != 0) ? dv[0] : null);
}
}
/// <summary>
/// 画面初期化
/// </summary>
private void InitControls()
{
//Notes設定
this.txtNotesPassword.Text = Config.NotesPassword;
NotesAccessor nsAccessor = Accessor.AccessorFactory.GetNotesAccessor();
if (!string.IsNullOrEmpty(Config.NotesPassword) && nsAccessor.TryConnect(Config.NotesPassword))
{
this.lblUserId.Text = nsAccessor.GetCurrentUser();
}
//出力フォルダを
this.txtOutputFolder.Text = Config.ExportFolder;
//Sharepoint設定
this.txtSPWebSite.Text = Config.SPDefaultWebSite;
this.txtSPUser.Text = Config.SPUserId;
this.txtSPPassword.Text = Config.SPPassword;
//SqlServer
this.txtSqlServer.Text = Config.SqlServer;
this.txtSqlServer.Tag = Config.SqlServer;
AuthenticateMode mode = (AuthenticateMode)Config.DBAuthenticateMode;
if (mode == AuthenticateMode.SqlServer)
{
this.txtDbUserId.Enabled = true;
this.txtDBPassword.Enabled = true;
this.txtDbUserId.Text = Config.DBUserId;
this.txtDbUserId.Tag = Config.DBUserId;
this.txtDBPassword.Text = Config.DBPassWord;
this.txtDBPassword.Tag = Config.DBPassWord;
}
else
{
this.txtDbUserId.Enabled = false;
this.txtDbUserId.Text = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
this.txtDBPassword.Clear();
this.txtDBPassword.Enabled = false;
}
InitAuthModeComb();
//Detabase List
if (!string.IsNullOrEmpty(Config.DataBaseName) && !string.IsNullOrEmpty(Config.SqlServer))
{
using (SqlAccessor sqlAccessor = Accessor.AccessorFactory.GetSqlAccessor())
{
if (this.InitDatabaseList(sqlAccessor))
{
this.cmbDataBase.SelectedItem = Config.DataBaseName;
}
}
}
}
/// <summary>
/// すべてフィールドリストを取得する
/// </summary>
/// <returns></returns>
private List <IFieldRef> GetAllFields(string taskId)
{
List <IFieldRef> retList = null;
using (SqlAccessor accessor = Accessor.AccessorFactory.GetSqlAccessor())
{
retList = accessor.GetAllFieldRefs(taskId);
}
if (retList == null)
{
retList = new List <IFieldRef>();
}
return(retList);
}
/// <summary>
/// 更新
/// </summary>
/// <param name="id">更新対象ID</param>
/// <param name="text">テキスト</param>
private bool Update(string id, string text)
{
using (var accessor = new SqlAccessor())
using (var statement = new SqlStatement("Sample", "Update"))
{
var ht = new Hashtable
{
{ "id", id },
{ "text", text }
};
var result = statement.ExecStatementWithOC(accessor, ht);
return(result > 0);
}
}
private void button1_Click(object sender, EventArgs e)
{
var isSuccess = false;
if (techlinkErrorProvider1.Validate(this))
{
if (cbDatabase.Text.Trim().Length == 0)
{
cbDatabase.Focus();
return;
}
if (rbIntergrated.Checked)
{
SqlAccessor.Instance().SetConnection(txtServer.Text, cbDatabase.Text, "", "");
}
else
{
SqlAccessor.Instance().SetConnection(txtServer.Text, cbDatabase.Text, txtUser.Text, txtPassword.Text);
}
var listDb = SqlAccessor.Instance().GetDatabases();
if (listDb.Any(x => x.Name == cbDatabase.Text))
{
isSuccess = true;
}
if (isSuccess)
{
//Save config
var config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
var connectionStringsSection = (ConnectionStringsSection)config.GetSection("connectionStrings");
var cnn = string.Format("metadata=res://*/ModelEcusDeclaration.csdl|res://*/ModelEcusDeclaration.ssdl|res://*/ModelEcusDeclaration.msl;provider=System.Data.SqlClient;provider connection string='{0}'", SqlAccessor.Instance().ConnectionString);
if (connectionStringsSection.ConnectionStrings["dbEcusDeclaration"] == null)
{
connectionStringsSection.ConnectionStrings.Add(new ConnectionStringSettings("dbEcusDeclaration", Utilities.Common.Encrypt(cnn, true)));
}
else
{
connectionStringsSection.ConnectionStrings["dbEcusDeclaration"].ConnectionString = Utilities.Common.Encrypt(cnn, true);
}
config.Save();
ConfigurationManager.RefreshSection("connectionStrings");
this.Close();
}
else
{
MessageBox.Show("Kết nối cơ sở dữ liệu không thành công");
}
}
}
/// <summary>
/// CSVファイルを出力
/// </summary>
/// <param name="type"></param>
/// <param name="sdate"></param>
/// <param name="edate"></param>
private void SaveCsvFile(LogType type, DateTime?sdate, DateTime?edate)
{
this.saveFileDialog.FileName = DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".csv";
if (this.saveFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
DataTable logData = null;
using (SqlAccessor accessor = Accessor.AccessorFactory.GetSqlAccessor())
{
logData = accessor.GetLogCsv(type, sdate, edate);
}
string filePath = this.saveFileDialog.FileName;
string csvData = GetCsvFile(logData);
System.Text.Encoding Encoder = System.Text.Encoding.Default;
System.IO.File.WriteAllText(filePath, csvData, Encoder);
}
}
private DataView Get(int pno)
{
// ページ番号をクラス内で管理する
hdnPageNo.Value = pno.ToString();
using (var accessor = new SqlAccessor())
using (var statement = new SqlStatement("Thread", "Get"))
{
var ht = new Hashtable
{
{ BBSConst.SQL_PARAM_THREAD_FROM, (pno - 1) * threadPerPage },
{ BBSConst.SQL_PARAM_THREAD_TO, pno *threadPerPage }
};
var dv = statement.SelectSingleStatementWithOC(accessor, ht);
return((dv.Count != 0) ? dv : null);
}
}
/// <summary>
/// ログインユーザを登録する
/// </summary>
/// <returns></returns>
private bool UpsertUser()
{
// 対象のテーブルはLOGINIDにユニークIndexを設定し重複を弾く
using (var accessor = new SqlAccessor())
using (var statement = new SqlStatement("BBSUser", "Upsert"))
{
var ht = new Hashtable
{
{ BBSConst.SQL_PRAM_USER_ID, Session[BBSConst.SESSION_NAME_TEMP_USERID].ToString() },
{ BBSConst.SQL_PRAM_LOGIN_ID, Session[BBSConst.SESSION_NAME_TEMP_LOGINID].ToString() },
{ BBSConst.SQL_PRAM_PASSWORD, Session[BBSConst.SESSION_NAME_TEMP_PASSWORD].ToString() },
{ BBSConst.SQL_PRAM_USER_NAME, Session[BBSConst.SESSION_NAME_TEMP_USER_NAME].ToString() }
};
var result = statement.ExecStatementWithOC(accessor, ht);
return(result > 0);
}
}
/// <summary>
/// ログWriterのインスタンスを取得する
/// データベース接続できない場合、NULLで返す
/// </summary>
/// <returns></returns>
public static ILogWriter GetLogWriter()
{
SqlAccessor sqlAccessor = null;
try
{
sqlAccessor = GetSqlAccessor();
}
catch
{
return(null);
}
SqlEventWriter logWriter = new SqlEventWriter();
logWriter.Init(GetSqlAccessor());
return(logWriter);
}
private async void InitDatabaseListAsync(SqlAccessor sqlAccessor)
{
cmbDataBase.Items.Clear();
List <string> dbs = await Task.Run(() =>
{
if (!sqlAccessor.TryConnect())
{
return(null);
}
return(sqlAccessor.GetDatabases());
});
if (dbs == null)
{
return;
}
cmbDataBase.Items.AddRange(dbs.ToArray());
}
/// <summary>
/// DB接続入力情報をチェックする(非同期で呼び出す)
/// </summary>
private void ValidDatabaseAsync()
{
//Serverが未入力の場合
if (string.IsNullOrEmpty(this.txtSqlServer.Text))
{
return;
}
if (this.cmbAuthMode.SelectedItem == null)
{
return;
}
AuthenticateMode mode = ((ListItem <AuthenticateMode>) this.cmbAuthMode.SelectedItem).Value;
if (mode == AuthenticateMode.SqlServer)
{
//DB UserIDが未入力の場合
if (string.IsNullOrEmpty(this.txtDbUserId.Text))
{
return;
}
//DB Passwordが未入力の場合
if (string.IsNullOrEmpty(this.txtDbUserId.Text))
{
return;
}
}
string database = "";
if (cmbDataBase.SelectedItem != null)
{
database = (string)cmbDataBase.SelectedItem;
}
string server = this.txtSqlServer.Text;
string userId = this.txtDbUserId.Text;
string psw = this.txtDBPassword.Text;
//SQL Serve接続テスト
SqlAccessor sqlAccessor = Accessor.AccessorFactory.GetSqlAccessor(mode, server, userId, psw, database);
//データベースリスト更新
InitDatabaseListAsync(sqlAccessor);
}
/// <summary>
/// 掲示板の登録更新処理を行う
/// </summary>
/// <returns></returns>
private bool UpsertThread()
{
// 対象のテーブルはLOGINIDにユニークIndexを設定し重複を弾く
using (var accessor = new SqlAccessor())
using (var statement = new SqlStatement("Thread", "Upsert"))
{
var ht = new Hashtable
{
{ BBSConst.SQL_PRAM_USER_ID, Session[BBSConst.SESSION_NAME_USERID].ToString() },
{ BBSConst.SQL_PRAM_TITLE, tbTitle.Text },
{ BBSConst.SQL_PRAM_POST_TEXT, tbPostText.Text },
{ BBSConst.SQL_PRAM_THREAD_ID, hdnTreadId.Value == "0" ? 0 : int.Parse(hdnTreadId.Value) },
{ BBSConst.SQL_PRAM_IS_DELETED, hdnIsDeleteFlg.Value == "0" ? 0 : 1 },
{ BBSConst.SQL_PRAM_ORIGIN_THREAD_ID, hdnOrigenTreadId.Value == "0" ? 0 : int.Parse(hdnOrigenTreadId.Value) }
};
var result = statement.ExecStatementWithOC(accessor, ht);
return(result > 0);
}
}
public bool SaveRequeueFile(RequeueFile theRequeueFile)
{
bool success = true;
SqlCommand cmd = BuildSaveCommand(theRequeueFile);
try
{
object[] returnObject = null;
int result = SqlAccessor.ExecuteNonQuery(Config.GetAppSettingsValue("DBConnectionString", ""),
cmd, CommandType.StoredProcedure, out returnObject);
}
catch (Exception ex)
{
_ErrorMessage = "DbTransaction-RequeueFileDALC-SaveRequeueFile()|" + ex.Message.ToString();
}
return(success);
}
public DataTable GetTrdpCode(string connectionString, string username)
{
DataTable dt = null;
try
{
SqlParameter[] parameters =
{
new SqlParameter("@username", username),
SqlAccessor.SqlParameterBuilder("@returnValue", SqlDbType.Int, ParameterDirection.Output)
};
dt = SqlAccessor.ExecuteDataSet(connectionString, SqlAccessor.SqlCommandBuilder(new SqlCommand(PL_GETTRDPCODE_SP), parameters), CommandType.StoredProcedure, "").Tables[0];
}
catch (Exception exc)
{
//ErrorLog.Log(APLU_NAME, "GetAll():" + exc.Message);
}
return(dt);
}
/// <summary>
/// 設定を保存する
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void SaveButton_Click(object sender, EventArgs e)
{
try
{
if (!ValidateInputs())
{
return;
}
using (SqlAccessor sqlAccessor = Accessor.AccessorFactory.GetSqlAccessor())
{
sqlAccessor.UpdateMigrateForm(this.TargetForm);
}
Log.Write(RSM.GetMessage(RS.Informations.FormSetted, this.TargetForm.Name));
this.DialogResult = System.Windows.Forms.DialogResult.OK;
this.Close();
}
catch (Exception ex)
{
Log.Write(ex);
RSM.ShowMessage(this, ex);
}
}
public void ProcessLog(string connectionString, string processSource, bool isSuccess, string description, string technicalError)
{
DataTable dt = null;
try
{
SqlParameter[] parameters =
{
new SqlParameter("@prlgProcessSource", processSource),
new SqlParameter("@prlgIsSuccess", isSuccess),
new SqlParameter("@prlgDescription", description),
new SqlParameter("@prlgTechnicalErrDesc", technicalError),
SqlAccessor.SqlParameterBuilder("@returnValue", SqlDbType.Int, ParameterDirection.Output)
};
dt = SqlAccessor.ExecuteDataSet(connectionString, SqlAccessor.SqlCommandBuilder(new SqlCommand(PL_PROCESSLOG_SP), parameters), CommandType.StoredProcedure, "").Tables[0];
}
catch (Exception exc)
{
//ErrorLog.Log(APLU_NAME, "GetAll():" + exc.Message);
}
}
public List <RequeueFile> GetEveryRequeueFile()
{
string tableName = "RequeueFile";
SqlCommand cmd = BuildGetEveryRequeueFileCommand();
DataSet ds = SqlAccessor.ExecuteDataSet(Config.GetAppSettingsValue("DBConnectionString", ""),
cmd, CommandType.StoredProcedure, tableName);
List <RequeueFile> entities = new List <RequeueFile>();
if (ds != null)
{
DataTable dt = ds.Tables[0];
foreach (DataRow drow in dt.Rows)
{
RequeueFile theRequeueFile = new RequeueFile();
theRequeueFile.RequeueFileId = Convert.ToInt32(drow["RequeueFileId"]);
theRequeueFile.trdpCode = Convert.ToString(drow["trdpCode"]);
theRequeueFile.MsgCode = Convert.ToString(drow["MsgCode"]);
theRequeueFile.SourceFileName = Convert.ToString(drow["SourceFileName"]);
theRequeueFile.OutputFileName = Convert.ToString(drow["OutputFileName"]);
theRequeueFile.CreateDate = Convert.ToDateTime(drow["CreateDate"]);
theRequeueFile.UpdateDate = (drow["UpdateDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(drow["UpdateDate"]));
theRequeueFile.IsActive = Convert.ToBoolean(drow["IsActive"]);
theRequeueFile.ERP = Convert.ToString(drow["ERP"]);
theRequeueFile.MsetBackUpFolder = Convert.ToString(drow["MsetBackUpFolder"]);
theRequeueFile.MessageFileDestinationId = Convert.ToInt32(drow["MessageFileDestinationId"]);
theRequeueFile.TransmissionTypeCode = Convert.ToString(drow["TransmissionTypeCode"]);
theRequeueFile.TempExtension = Convert.ToString(drow["TempExtension"]);
entities.Add(theRequeueFile);
}
}
return(entities);
}
/// <summary>
/// ワーカーを実行する
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
Accessor.Convertor convert = new Accessor.Convertor();
List <string> taskIds = (List <string>)e.Argument;
MigrateTask task = null;
using (SqlAccessor sqlAccessor = Accessor.AccessorFactory.GetSqlAccessor())
{
foreach (string id in taskIds)
{
//開始時間
DateTime sdate = DateTime.Now;
try
{
task = sqlAccessor.GetMigrateTaskById(id, SqlAccessor.DataKind.All);
ProgressReporter reporter = new ProgressReporter(task.TaskName, OnReported);
convert.DoConvert(task, reporter);
//終了時間
DateTime edate = DateTime.Now;
sqlAccessor.UpdateMigrate(task, true);
Log.Write(task, RSM.GetMessage(RS.Informations.ConvertCompleted, task.TaskName), true, sdate, edate);
}
catch (Exception)
{
if (task != null)
{
sqlAccessor.UpdateMigrate(task, false);
}
//終了時間
DateTime edate = DateTime.Now;
Log.Write(task, RSM.GetMessage(RS.Informations.ConvertFailed, task.TaskName), true, sdate, edate);
throw;
}
}
}
}
private void groupBox2_Leave(object sender, EventArgs e)
{
cbDatabase.DataSource = null;
if (rbIntergrated.Checked)
{
txtUser.Tag = txtPassword.Tag = "";
if (techlinkErrorProvider1.Validate(this))
{
SqlAccessor.Instance().SetConnection(txtServer.Text, "", "");
cbDatabase.DataSource = SqlAccessor.Instance().GetDatabases();
cbDatabase.DisplayMember = "Name";
}
}
else
{
txtUser.Tag = txtPassword.Tag = "required";
if (techlinkErrorProvider1.Validate(this))
{
SqlAccessor.Instance().SetConnection(txtServer.Text, txtUser.Text, txtPassword.Text);
cbDatabase.DataSource = SqlAccessor.Instance().GetDatabases();
cbDatabase.DisplayMember = "Name";
}
}
}
/// <summary>
/// DB接続入力情報をチェックする
/// </summary>
private bool ValidDatabase()
{
//Serverが未入力の場合
if (string.IsNullOrEmpty(this.txtSqlServer.Text))
{
RSM.ShowMessage(this, RS.Exclamations.NotServer);
FocusControl(this.txtSqlServer);
return(false);
}
if (this.cmbAuthMode.SelectedItem == null)
{
RSM.ShowMessage(this, RS.Exclamations.NotAuthMode);
FocusControl(this.cmbAuthMode);
return(false);
}
AuthenticateMode mode = ((ListItem <AuthenticateMode>) this.cmbAuthMode.SelectedItem).Value;
if (mode == AuthenticateMode.SqlServer)
{
//DB UserIDが未入力の場合
if (string.IsNullOrEmpty(this.txtDbUserId.Text))
{
RSM.ShowMessage(this, RS.Exclamations.NotDBUserID);
FocusControl(this.txtDbUserId);
return(false);
}
//DB Passwordが未入力の場合
if (string.IsNullOrEmpty(this.txtDBPassword.Text))
{
RSM.ShowMessage(this, RS.Exclamations.NotDBPassword);
FocusControl(this.txtDBPassword);
return(false);
}
}
string database = "";
if (cmbDataBase.SelectedItem != null)
{
database = (string)cmbDataBase.SelectedItem;
}
if (string.IsNullOrEmpty(database))
{
RSM.ShowMessage(this, RS.Exclamations.NotDataBase);
return(false);
}
string server = this.txtSqlServer.Text;
string userId = this.txtDbUserId.Text;
string psw = this.txtDBPassword.Text;
//SQL Serve接続テスト
using (SqlAccessor sqlAccessor = Accessor.AccessorFactory.GetSqlAccessor(mode, server, userId, psw, database))
{
if (!sqlAccessor.TryConnect())
{
RSM.ShowMessage(this, RS.Exclamations.DbConnectFailed);
FocusControl(this.txtSqlServer);
return(false);
}
}
return(true);
}
