using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading; using System.Threading.Tasks; using System.IO; using System.ServiceProcess; using System.Data; namespace NewPosInstaller { public class PosDatabaseCreate : Echo, IProcess { private bool isStop = false; private SqlDB sqlDb = null; public bool Execute() { bool retValue = false; try { base.OnEcho("Database is being created."); ComLog.WriteLog(ComLog.Level.trace , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , "Start"); // MSSQL 서비스 확인 retValue = StartSQLInstance(); if (retValue == false) { if (string.IsNullOrWhiteSpace(BasicInfo.DBCreateDate)) { ComLog.SendStatus(ComLog.InstallStatus.DBCreate, ComLog.InstallResult.Fail, "MSSQL instance not found. [" + DatabaseInfo.InstanceName + "]"); } return false; } // 데이터베이스 접속 retValue = DatabaseConnection(); if (retValue == false) { if (string.IsNullOrWhiteSpace(BasicInfo.DBCreateDate)) { ComLog.SendStatus(ComLog.InstallStatus.DBCreate, ComLog.InstallResult.Fail, "Database connection failed."); } return false; } // 마스터 데이터베이스 생성 retValue = CreateDatabase("POSMST"); if (retValue == false) { if (string.IsNullOrWhiteSpace(BasicInfo.DBCreateDate)) { ComLog.SendStatus(ComLog.InstallStatus.DBCreate, ComLog.InstallResult.Fail, "Create Database failed. [POSMST]"); } return false; } // 매출 데이터베이스 생성 retValue = CreateDatabase("POSLOG"); if (retValue == false) { if (string.IsNullOrWhiteSpace(BasicInfo.DBCreateDate)) { ComLog.SendStatus(ComLog.InstallStatus.DBCreate, ComLog.InstallResult.Fail, "Create Database failed. [POSLOG]"); } return false; } // 테이블 생성 retValue = CreateTable(); if (retValue == false) { if (string.IsNullOrWhiteSpace(BasicInfo.DBCreateDate)) { ComLog.SendStatus(ComLog.InstallStatus.DBCreate, ComLog.InstallResult.Fail, "Create Table failed."); } return false; } // 정산프로시저 // 메인포스 판단여부 불가 !!! // 데이터베이스 생성 일시 기록 & 상태정보 전송 if (string.IsNullOrWhiteSpace(BasicInfo.DBCreateDate)) { BasicInfo.DBCreateDate = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"); ComLog.SendStatus(ComLog.InstallStatus.DBCreate, ComLog.InstallResult.Success, "Create Database & Table success [" + BasicInfo.DBCreateDate + "]"); } //// 마스터 다운로드 일시 초기화 //if (string.IsNullOrWhiteSpace(BasicInfo.MSTDownDate)) // BasicInfo.MSTDownDate = "20000101000000"; return retValue; } catch (Exception ex) { ComLog.WriteLog(ComLog.Level.Exception , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , " => Exception : " + ex.Message); return retValue = false; } finally { DatabaseClose(); ComLog.WriteLog(ComLog.Level.trace , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , string.Format("End, Result={0}", retValue ? "Success" : "Failed")); } } public void Stop() { this.isStop = true; } private List GetSQLInstance() { List lstSQLInstance = new List(); try { ServiceController[] sc = ServiceController.GetServices(); foreach (var s in from x in sc where x.ServiceName.Contains("MSSQL") select x) { lstSQLInstance.Add(s.ServiceName); } } catch (Exception ex) { ComLog.WriteLog(ComLog.Level.Exception , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , " => Exception : " + ex.Message); } return lstSQLInstance; } private bool StartSQLInstance() { ServiceController sqlService = null; try { base.OnEcho("MSSQL database service start"); //ServiceController sqlService = new ServiceController(serviceName); ServiceController[] sc = ServiceController.GetServices(); foreach (var s in from x in sc where x.ServiceName == DatabaseInfo.InstanceName select x) { sqlService = s; break; } if (sqlService == null) { ComLog.WriteLog(ComLog.Level.Error , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , " => MSSQL instance not found. [" + DatabaseInfo.InstanceName + "]", true); return false; } else { ComLog.WriteLog(ComLog.Level.trace , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , " => MSSQL instance found. [" + DatabaseInfo.InstanceName + "]"); } bool isRuning = false; bool isCommand = false; DateTime nowTime = DateTime.Now; DateTime waitTime = nowTime.Add(TimeSpan.FromMinutes(20)); // 대기시간 (20분) while (waitTime > nowTime) { sqlService.Refresh(); if (sqlService.Status == ServiceControllerStatus.Running) { isRuning = true; break; } if (isCommand == false) { switch (sqlService.Status) { case ServiceControllerStatus.StopPending: case ServiceControllerStatus.Stopped: isCommand = true; sqlService.Start(); break; } } if (this.isStop) return false; // 5초 대기 Thread.Sleep(5 * 1000); } ComLog.WriteLog(ComLog.Level.trace , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , " => MSSQL service status [" + GetServiceStatusName(sqlService.Status) + "]"); return isRuning ? true : false; } catch (Exception ex) { ComLog.WriteLog(ComLog.Level.Exception , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , " => Exception : " + ex.Message); return false; } finally { if (sqlService != null) { sqlService.Close(); sqlService.Dispose(); } } } private bool StopSQLInstance() { try { bool isStoped = false; bool isCommand = false; using (ServiceController sqlService = new ServiceController(DatabaseInfo.InstanceName)) { DateTime nowTime = DateTime.Now; DateTime waitTime = nowTime.Add(TimeSpan.FromMinutes(20)); // 대기시간 (20분) while (waitTime > nowTime) { sqlService.Refresh(); if (sqlService.Status == ServiceControllerStatus.Stopped || sqlService.Status == ServiceControllerStatus.StopPending) { isStoped = true; break; } if (isCommand == false) { switch (sqlService.Status) { case ServiceControllerStatus.StopPending: case ServiceControllerStatus.Stopped: break; default: isCommand = true; sqlService.Stop(); break; } } // 5초 대기 Thread.Sleep(5 * 1000); } ComLog.WriteLog(ComLog.Level.trace , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , " => MSSQL service status [" + GetServiceStatusName(sqlService.Status) + "]"); } return isStoped ? true : false; } catch (Exception ex) { ComLog.WriteLog(ComLog.Level.Exception , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , " => Exception : " + ex.Message); return false; } } private bool DatabaseConnection() { bool retValue = false; string connStr = string.Empty; try { base.OnEcho("Connecting to database"); ComLog.WriteLog(ComLog.Level.trace , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , " => MSSQL Connection Start"); if (sqlDb != null) { if (sqlDb.IsDBOpen()) sqlDb.DBClose(); } sqlDb = new SqlDB(DatabaseInfo.DataSource, "master", DatabaseInfo.UserId, DatabaseInfo.Password); connStr = sqlDb.SqlConnectionString; for (var con = 0; con < 5; con++) { for (var cnt = 0; cnt < 10; cnt++) { //retValue = sqlDb.IsDBOpen(); //if (retValue == false) return false; // 데이터베이스 연결 테스트 DataTable dt = null; string sql = "SELECT name FROM master..sysdatabases"; int ret = sqlDb.DBDataTableSelect(sql, CommandType.Text, null, out dt); if (ret == ComLib.OK) { retValue = true; break; } ComLog.WriteLog(ComLog.Level.Error , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , string.Format(" => MSSQL Connection Failed. TryCount=[{0:00}/{1:00}]", con, cnt), true); Thread.Sleep(1000); } if (retValue) break; sqlDb.DBClose(); ChangeSqlService(true); // Service restart ComLog.WriteLog(ComLog.Level.Error , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , " => MSSQL Service restart", true); StopSQLInstance(); StartSQLInstance(); sqlDb.DBOpen(); } return retValue; } catch (Exception ex) { ComLog.WriteLog(ComLog.Level.Exception , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , " => Exception : " + ex.Message); return retValue = false; } finally { ComLog.WriteLog(ComLog.Level.trace , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , string.Format(" => MSSQL Connection Result={0} ConnectionString={1}", retValue ? "Success" : "Failed", connStr)); } } private bool DatabaseClose() { bool retValue = false; try { if (sqlDb == null) return false; base.OnEcho("Disconnectiong to database"); sqlDb.Dispose(); retValue = true; ComLog.WriteLog(ComLog.Level.trace , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , string.Format(" => MSSQL Disconnection Result={0} ", retValue ? "Success" : "Failed")); return retValue; } catch (Exception ex) { ComLog.WriteLog(ComLog.Level.Exception , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , ex.Message); return false; } finally { sqlDb = null; } } /// /// SQL 서버 로컬 권한으로 접속 설정 변경(WIN7) /// private void ChangeSqlService(bool bLocal) { try { string path = string.Format(@"SYSTEM\CurrentControlSet\services\{0}\", DatabaseInfo.InstanceName); if (bLocal == true) { Microsoft.Win32.RegistryKey regKey = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(path, Microsoft.Win32.RegistryKeyPermissionCheck.ReadWriteSubTree); if (regKey.GetValue("ObjectName").ToString() == "LocalSystem" && regKey.GetValue("Type").ToString() == "272") return; regKey.SetValue("ObjectName", "LocalSystem", Microsoft.Win32.RegistryValueKind.String); regKey.SetValue("Type", 0x110, Microsoft.Win32.RegistryValueKind.DWord); ComLog.WriteLog(ComLog.Level.trace , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , " => MSSQL Server service set change > [LocalSystem]"); } else { Microsoft.Win32.RegistryKey regKey = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(path, Microsoft.Win32.RegistryKeyPermissionCheck.ReadWriteSubTree); if (regKey.GetValue("ObjectName").ToString() == @"NT AUTHORITY\NetworkService" && regKey.GetValue("Type").ToString() == "16") return; regKey.SetValue("ObjectName", @"NT AUTHORITY\NetworkService", Microsoft.Win32.RegistryValueKind.String); regKey.SetValue("Type", 0x10, Microsoft.Win32.RegistryValueKind.DWord); ComLog.WriteLog(ComLog.Level.trace , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , @" => MSSQL Server service set change > [NT AUTHORITY\NetworkService]"); } } catch (Exception ex) { ComLog.WriteLog(ComLog.Level.Exception , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , " => Exception : " + ex.Message); } } /// /// 데이터베이스 생성 /// /// POSMST/POSLOG /// private bool CreateDatabase(string databaseName) { bool retValue = false; string errMsg = string.Empty; try { base.OnEcho(string.Format("Database is being created. ({0})", databaseName)); int ret = -9; int retryCnt = 5; string sql = string.Empty; DataTable dt = null; string dbPath = string.Empty; string datFile = string.Empty; string logFile = string.Empty; switch (databaseName) { case "POSMST": dbPath = Path.Combine(DirInfo.InstallDir, @"MST\"); datFile = Path.Combine(dbPath, @"POSMST.mdf"); logFile = Path.Combine(dbPath, @"POSMST_log.ldf"); break; case "POSLOG": dbPath = Path.Combine(DirInfo.InstallDir, @"DATA\"); datFile = Path.Combine(dbPath, @"POSLOG.mdf"); logFile = Path.Combine(dbPath, @"POSLOG_log.ldf"); break; default: return false; } // 디렉토리 생성 확인 ComLib.CreateDirectory(dbPath); // 데이터베이스 접속 및 존재여부 확인 for (var i = 0; i < retryCnt; i++) { // 1:OK, -1:Connection 정보 에러, -2:SQL 에러, -3:기타 에러, 0:Select 결과 없음 sql = string.Format("SELECT name FROM {0}..sysobjects ", databaseName); ret = sqlDb.DBDataTableSelect(sql, CommandType.Text, null, out dt); if (ret == ComLib.OK) { // already exists ComLog.WriteLog(ComLog.Level.trace , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , string.Format(" => {0} database already exists.", databaseName)); return retValue = true; } else { // 데이터베이스 파일이 없으면, 신규생성. if (File.Exists(datFile) == false) break; // 데이터베이스 파일이 존재하면, Attatch 시도 ComLog.WriteLog(ComLog.Level.Error , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , string.Format(" => Sysobjects object select failed. DatabaseName={0}, Retry={1}", databaseName, i)); ComLog.WriteLog(ComLog.Level.Error , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , string.Format(" => Database attatch, DatabaseName={0}, File={1})", databaseName, datFile), true); sql = string.Format("ALTER DATABASE {0} SET OFFLINE WITH ROLLBACK IMMEDIATE", databaseName); ret = sqlDb.DBExecuteNonQuery(sql, CommandType.Text, null); sql = string.Format("EXEC sp_detach_db '{0}', 'true'", databaseName); ret = sqlDb.DBExecuteNonQuery(sql, CommandType.Text, null); System.Threading.Thread.Sleep(1000); // 재시도 횟수가 넘어서면 중지 if (i == retryCnt -1) break; sql = string.Format("EXEC sp_attach_db @dbname = N'{0}', @filename1 = N'{1}', @filename2 = N'{2}'" , databaseName, datFile, logFile); ret = sqlDb.DBExecuteNonQuery(sql, CommandType.Text, null); System.Threading.Thread.Sleep(1000); } ret = ComLib.NG; } for (var i = 0; i < retryCnt; i++) { // 데이터베이스 파일이 존재하면 백업 if (File.Exists(datFile)) { string bck = DateTime.Now.ToString("yyyyMMddHHmmss"); ComLib.FileCopy(datFile, datFile + bck, true); ComLib.FileCopy(logFile, logFile + bck, true); } sql = "USE [master] \n" + "CREATE DATABASE [{0}] \n" + "ON PRIMARY (NAME = N'{0}', FILENAME = N'{1}' , SIZE = 100MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%) \n" + "LOG ON (NAME = N'{2}', FILENAME = N'{3}' , SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) \n"; sql = string.Format(sql, databaseName, datFile, databaseName + "_log", logFile); ret = sqlDb.DBExecuteNonQuery(sql, CommandType.Text, null); if (ret == ComLib.OK || ret == ComLib.NG4) { ComLog.WriteLog(ComLog.Level.trace , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , string.Format(" => {0} database creation succeeded", databaseName), true); //// 마스터 다운일시 //if (databaseName == "POSMST") //{ // BasicInfo.MSTDownDate = "20000101000000"; //} retValue = true; break; } else { ComLog.WriteLog(ComLog.Level.trace , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , string.Format(" => {0} database creation failed", databaseName), true); retValue = false; } } return retValue; } catch (Exception ex) { ComLog.WriteLog(ComLog.Level.Exception , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , " => Exception : " + ex.Message); return false; } finally { ComLog.WriteLog(ComLog.Level.trace , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , string.Format(" => {0} CreateDatabase Result={1}", databaseName, retValue ? "Success" : "Failed")); } } private bool CreateTable() { try { // 작업리스트 List scriptList = new List(); scriptList.Add(Path.Combine(DirInfo.InstallDir, @"BIN\CreateMST.sql")); scriptList.Add(Path.Combine(DirInfo.InstallDir, @"BIN\AlterMST.sql")); scriptList.Add(Path.Combine(DirInfo.InstallDir, @"BIN\CreateLOG.sql")); scriptList.Add(Path.Combine(DirInfo.InstallDir, @"BIN\AlterLOG.sql")); foreach (string sqlPath in scriptList) { base.OnEcho(string.Format("Create and Modify Tables\n{0}", sqlPath)); if (File.Exists(sqlPath)) { ComLog.WriteLog(ComLog.Level.trace , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , string.Format(" => Table script execute. ScriptFile={0}", sqlPath)); } else { ComLog.WriteLog(ComLog.Level.Error , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , string.Format(" => Table script execute error. File not found={0}", sqlPath), true); continue; } string script = string.Empty; using (StreamReader sr = new StreamReader(sqlPath, System.Text.Encoding.Default)) { sr.BaseStream.Seek(0, SeekOrigin.Begin); script = sr.ReadToEnd(); sr.Close(); } if (string.IsNullOrWhiteSpace(script)) { ComLog.WriteLog(ComLog.Level.Error , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , string.Format(" => Table script execute error. Script file Error (length=0)"), true); continue; } string[] temp = script.Split(new string[] { "\n\r", "\r\n" , "\n" }, StringSplitOptions.None); string sql = string.Empty; foreach (string line in temp) { if (line.Trim().ToUpper() == "GO") { int ret = sqlDb.DBExecuteNonQuery(sql, CommandType.Text, null); if (ret != ComLib.OK && ret != ComLib.NG4) { ComLog.WriteLog(ComLog.Level.Error , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , string.Format(" => Table script execute error.\n{0}", sql)); } sql = string.Empty; } else { sql += line + Environment.NewLine; } } if (string.IsNullOrWhiteSpace(sql) == false) { int ret = sqlDb.DBExecuteNonQuery(sql, CommandType.Text, null); if (ret != ComLib.OK && ret != ComLib.NG4) { ComLog.WriteLog(ComLog.Level.Error , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , string.Format(" => Table script execute error.\n{0}", sql)); } } } return true; } catch (Exception ex) { ComLog.WriteLog(ComLog.Level.Exception , System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + "()" , " => Exception : " + ex.Message); return false; } } private string GetServiceStatusName(ServiceControllerStatus status) { string statusText = string.Empty; switch (status) { case ServiceControllerStatus.ContinuePending: statusText = "ContinuePending"; break; case ServiceControllerStatus.StartPending: statusText = "StartPending"; break; case ServiceControllerStatus.PausePending: statusText = "PausePending"; break; case ServiceControllerStatus.StopPending: statusText = "StopPending"; break; case ServiceControllerStatus.Stopped: statusText = "Stopped"; break; case ServiceControllerStatus.Running: statusText = "Running"; break; case ServiceControllerStatus.Paused: statusText = "Paused"; break; default: statusText = "none"; break; } return statusText; } } }