public void GetSqlStatements(ref ExcelToSql.ExcelToSqlService ExcelToSql) { ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters(); parameters.SqlStatement.SqlScript = "select * from dbo.tbl_SqlStatements order by Command, ProcessOrder"; parameters.SqlStatement.WorksheetName = rngp_SqlStatements.WorksheetName; parameters.SqlStatement.RangeName = rngp_SqlStatements.RangeName; var rn = rngp_SqlStatements.GetRange(); if (rn != null) { rn.Cells.ClearContents(); } ExcelToSql.GetCommand(parameters); }
private void TeamFile_UploadPlanningArea() { if (!_ConnectionOK) { return; } try { //Validate("DuplicateCombo"); Validate("DuplicateCombo"); Validate("InvalidRow"); if (!ValidationResult()) { return; }; ExcelToSql.ExcelToSqlService excelToSql = new ExcelToSql.ExcelToSqlService(); excelToSql.ExcelApplictaion = this.ExcelApplication; excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW); ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters(); //runtime parameterek létrehozása MyParameters myparams = GetMyParameters(); parameters.RuntimeParameters = myparams; var statements = _SqlSatementModule.GetSqlStatementList(); var query = CreateUploadAreaList(); excelToSql.CommandRunner<UploadAreaClass>(parameters, statements, "TeamFile_UploadPlanningArea", _tblTeamFileTempDefinition, query, null); System.Windows.Forms.MessageBox.Show("Done!"); rngp_LastUpload.SetValue(DateTime.Now); //rngp_PlanningAreaChanged.SetValue("FALSE"); } catch (Exception ex) { ExceptionHandler.HandleException(ex); } }
private void TeamFile_DownloadPlanningArea() { if (!_ConnectionOK) { return; } ExcelToSql.ExcelToSqlService excelToSql = new ExcelToSql.ExcelToSqlService(); excelToSql.ExcelApplictaion = this.ExcelApplication; try { excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW); ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters(); MyParameters myparams = GetMyParameters(); parameters.RuntimeParameters = myparams; var statements = _SqlSatementModule.GetSqlStatementList(); this.ExcelApplication.ScreenUpdating = false; this.ExcelApplication.EnableEvents = false; var query = CreateUploadAreaList(); excelToSql.CommandRunner<UploadAreaClass>(parameters, statements, "TeamFile_DownloadPlanningArea",_tblTeamFileTempDefinition,query,null); //ClearCellsWithTypeWhereColumnEmpty(rngcl_PlanningAreaFixed.GetRange(),InteropExcel.XlCellType.xlCellTypeConstants, 2); rngp_LastDownload.SetValue(DateTime.Now); //rngp_PlanningAreaChanged.SetValue("FALSE"); } catch (Exception ex) { ExceptionHandler.HandleException(ex); } finally { this.ExcelApplication.ScreenUpdating = true; this.ExcelApplication.EnableEvents = true; } }
private void DownloadMasterData() { ExcelToSql.ExcelToSqlService _excelToSql = new ExcelToSql.ExcelToSqlService(); _excelToSql.ExcelApplictaion = this.ExcelApplication; _excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW); ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters(); try { MyParameters myparams = GetMyParameters(); parameters.RuntimeParameters = myparams; if (_SqlSatementModule.TestQuery(ref _excelToSql)) { _ConnectionOK = true; _SqlSatementModule.GetSqlStatements(ref _excelToSql); var statements = _SqlSatementModule.GetSqlStatementList(); _excelToSql.CommandRunner<object>(parameters, statements, "TeamFile_DownloadMasterData", null, null); } else { _ConnectionOK = false; } } catch (Exception ex) { _ConnectionOK = false; ExceptionHandler.HandleException(ex); } }
private void UploadSummaryArea() { if (!_ConnectionOK) { return; } ExcelToSql.ExcelToSqlService excelToSql = new ExcelToSql.ExcelToSqlService(); excelToSql.ExcelApplictaion = this.ExcelApplication; try { excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW); ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters(); var statements = _SqlSatementModule.GetSqlStatementList(); excelToSql.CommandRunner<object>(parameters, statements, "SummaryFile_UploadSummaryArea", _tblSummaryFileTempDefinition, null); System.Windows.Forms.MessageBox.Show("Done!"); } catch (Exception ex) { ExceptionHandler.HandleException(ex); } finally { } }
private void DownloadSummaryReport() { if (!_ConnectionOK) { return; } ExcelToSql.ExcelToSqlService excelToSql = new ExcelToSql.ExcelToSqlService(); excelToSql.ExcelApplictaion = this.ExcelApplication; this.ExcelApplication.ScreenUpdating = false; this.ExcelApplication.EnableEvents = false; this.ExcelApplication.DisplayAlerts = false; try { excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW); ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters(); MyParameters myparams = GetMyParameters(); parameters.RuntimeParameters = myparams; this.ExcelApplication.TryGetSheet("TeamApproval").Delete(); CreateModuleSheets(this.ActiveWorkbook); InitModuleObjectRanges(this.ActiveWorkbook, true); var statements = _SqlSatementModule.GetSqlStatementList(); excelToSql.CommandRunner<object>(parameters, statements, "SummaryFile_DownloadSummaryReport", null, null); FormatReport(); } catch (Exception ex) { ExceptionHandler.HandleException(ex); } finally { this.ExcelApplication.ScreenUpdating = true; this.ExcelApplication.EnableEvents = true; this.ExcelApplication.DisplayAlerts = true; } }
public bool TestQuery(ref ExcelToSql.ExcelToSqlService ExcelToSql) { bool blOK; ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters(); parameters.SqlStatement.SqlScript = "select top (1) Id from dbo.tbl_SqlStatements"; try { ExcelToSql.ExecuteCommand(parameters); blOK = true; } catch (Exception) { blOK = false; throw; } return blOK; }
private void DownloadMasterData() { ExcelToSql.ExcelToSqlService _excelToSql = new ExcelToSql.ExcelToSqlService(); _excelToSql.ExcelApplictaion = this.ExcelApplication; _excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW); ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters(); try { if (_SqlSatementModule.TestQuery(ref _excelToSql)) { _ConnectionOK = true; _SqlSatementModule.GetSqlStatements(ref _excelToSql); } else { _ConnectionOK = false; } } catch (Exception ex) { _ConnectionOK = false; ExceptionHandler.HandleException(ex); } }
private void CurrentTeamFile_Upload() { if (!_ConnectionOK) { return; } ExcelToSql.ExcelToSqlService excelToSql = new ExcelToSql.ExcelToSqlService(); excelToSql.ExcelApplictaion = this.ExcelApplication; try { InteropExcel.Worksheet ws = ActiveWorkbook.TryGetSheet("CurrentTeam"); ws.Activate(); excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW); ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters(); var statements = _SqlSatementModule.GetSqlStatementList(); this.ExcelApplication.ScreenUpdating = false; this.ExcelApplication.EnableEvents = false; var query = CreateUploadAreaList(); if (query.Count > 0) { excelToSql.CommandRunner<CurrentTeam>(parameters, statements, "CurrentTeamFile_Upload", _tblCurrentTeam, query, null); rngp_UploadArea.GetRange().ClearContents(); } System.Windows.Forms.MessageBox.Show("Done!"); } catch (Exception ex) { ExceptionHandler.HandleException(ex); } finally { this.ExcelApplication.ScreenUpdating = true; this.ExcelApplication.EnableEvents = true; } }
private void CurrentTeamFile_DownloadReport() { if (!_ConnectionOK) { return; } ExcelToSql.ExcelToSqlService _excelToSql = new ExcelToSql.ExcelToSqlService(); _excelToSql.ExcelApplictaion = this.ExcelApplication; _excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW); ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters(); try { InteropExcel.Worksheet ws = ActiveWorkbook.TryGetSheet("Report"); ws.Activate(); var statements = _SqlSatementModule.GetSqlStatementList(); this.ExcelApplication.ScreenUpdating = false; this.ExcelApplication.EnableEvents = false; _excelToSql.CommandRunner<object>(parameters, statements, "CurrentTeamFile_DownloadReport", null, null); } catch (Exception ex) { ExceptionHandler.HandleException(ex); } finally { this.ExcelApplication.ScreenUpdating = true; this.ExcelApplication.EnableEvents = true; } }