public void RunScriptFile(string ScriptFile) { if (!System.IO.File.Exists(ScriptFile)) { throw new Exception("File '" + ScriptFile + "' does not exist."); } System.IO.StreamReader sr = null; try { sr = new System.IO.StreamReader(ScriptFile, false); using (FirebirdSql.Data.FirebirdClient.FbConnection conn = new FirebirdSql.Data.FirebirdClient.FbConnection(this.ConnStr)) { conn.Open(); using (FirebirdSql.Data.FirebirdClient.FbCommand cmd = new FirebirdSql.Data.FirebirdClient.FbCommand()) { cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = sr.ReadToEnd(); cmd.ExecuteNonQuery(); } conn.Close(); } } catch (Exception ex) { throw ex; } finally { sr.Close(); sr.Dispose(); } }
private void btnOK_Click(object sender, EventArgs e) { Object obj = null; string commandText = string.Empty; if (txtDataSetName.Text == string.Empty) { MessageBox.Show("Please input a valid dataset name."); return; } if (lstDataSetVariable.Items.Count == 0) { MessageBox.Show("Please load a variable datafile."); return; } ESIL.DBUtility.FireBirdHelperBase fb = new ESIL.DBUtility.ESILFireBirdHelper(); if (CommonClass.Connection.State != ConnectionState.Open) { CommonClass.Connection.Open(); } FirebirdSql.Data.FirebirdClient.FbConnection fbconnection = CommonClass.getNewConnection(); fbconnection.Open(); FirebirdSql.Data.FirebirdClient.FbTransaction fbtra = fbconnection.BeginTransaction(); FirebirdSql.Data.FirebirdClient.FbCommand fbCommand = new FirebirdSql.Data.FirebirdClient.FbCommand(); fbCommand.Connection = fbconnection; fbCommand.CommandType = CommandType.Text; fbCommand.Transaction = fbtra; DataSet ds = new DataSet(); DataTable dt; int rowCount; string variableDatasetID = string.Empty; int variableID = 0; try { lblProgressBar.Visible = true; progBarVariable.Visible = true; progBarVariable.Value = 0; progBarVariable.Minimum = 0; progBarVariable.Maximum = 0; progBarVariable.Refresh(); commandText = string.Format("select SETUPVARIABLEDATASETID from SetUpVariableDataSets where SETUPVARIABLEDATASETNAME='{0}' and SetupID={1}", txtDataSetName.Text, CommonClass.ManageSetup.SetupID); obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText); if (_datasetName == string.Empty) { if (obj != null) { MessageBox.Show("The dataset name has already been defined. Please enter a different name."); return; } commandText = "select max(SETUPVARIABLEDATASETID) from SETUPVARIABLEDATASETS"; obj = Convert.ToInt32(fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText)) + 1; variableDatasetID = obj.ToString(); commandText = string.Format("insert into SetUpVariableDataSets values({0},{1},'{2}')", variableDatasetID, CommonClass.ManageSetup.SetupID, txtDataSetName.Text); fbCommand.CommandText = commandText; fbCommand.ExecuteNonQuery(); commandText = string.Format("select GridDefinitionID from GridDefinitions where GridDefinitionName='{0}' and SetupID={1}", txtGridDefinition.Text, CommonClass.ManageSetup.SetupID); obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText); string gridDefinationID = obj.ToString(); int count = _dsSelectedData.Tables.Count; foreach (DataTable dtcount in _dsSelectedData.Tables) { progBarVariable.Maximum += dtcount.Rows.Count; } commandText = "select max(SETUPVARIABLEID) from SetUpVariables"; obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText); if (obj != null) { variableID = Convert.ToInt32(obj); } for (int i = 0; i < count; i++) { dt = _dsSelectedData.Tables[i].Clone(); dt = _dsSelectedData.Tables[i].Copy(); string variableName = dt.TableName; commandText = string.Format("select SETUPVARIABLEID from SETUPVARIABLES where SETUPVARIABLENAME='{0}' and SETUPVARIABLEDATASETID='{1}'", variableName, variableDatasetID); obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText); if (obj == null) { variableID++; commandText = string.Format("insert into SetUpVariables values({0},{1},'{2}','{3}')", variableID, variableDatasetID, variableName, gridDefinationID); fbCommand.CommandText = commandText; fbCommand.ExecuteNonQuery(); rowCount = dt.Rows.Count; for (int j = 0; j < (rowCount / 125) + 1; j++) { commandText = "execute block as declare SetupVariableID int;" + " BEGIN "; for (int k = 0; k < 125; k++) { if (j * 125 + k < dt.Rows.Count) { commandText = commandText + string.Format(" select SetupVariableID from SetupVariables where SetupVariableDataSetID={0} and SetupVariableName='{1}' and GridDefinitionID={2} into :SetupVariableID;", variableDatasetID, variableName, gridDefinationID); progBarVariable.Value++; progBarVariable.Refresh(); lblProgressBar.Text = Convert.ToString((int)((double)progBarVariable.Value * 100 / progBarVariable.Maximum)) + "%"; lblProgressBar.Refresh(); } else { continue; } commandText = commandText + string.Format(" insert into SETUPGEOGRAPHICVARIABLES values (:SetupVariableID,{0},{1},{2});", dt.Rows[j * 125 + k][0], dt.Rows[j * 125 + k][1], dt.Rows[j * 125 + k][2]); } commandText = commandText + "END"; fbCommand.CommandText = commandText; fbCommand.ExecuteNonQuery(); } } } } else { commandText = string.Format("select SETUPVARIABLEDATASETID from SetUpVariableDataSets where SETUPVARIABLEDATASETNAME='{0}' and SetupID={1}", _datasetName, CommonClass.ManageSetup.SetupID); obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText); variableDatasetID = obj.ToString(); commandText = string.Format("update SETUPVARIABLEDATASETS set SETUPVARIABLEDATASETNAME='{0}' where setupid={1} and SETUPVARIABLEDATASETID={2}", txtDataSetName.Text, CommonClass.ManageSetup.SetupID, variableDatasetID); fbCommand.CommandText = commandText; fbCommand.ExecuteNonQuery(); commandText = string.Format("select GridDefinitionID from GridDefinitions where GridDefinitionName='{0}' and SetupID={1}", txtGridDefinition.Text, CommonClass.ManageSetup.SetupID); obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText); string gridDefinationID = obj.ToString(); int count = _dsSelectedDataTemp.Tables.Count; foreach (DataTable dtcount in _dsSelectedDataTemp.Tables) { progBarVariable.Maximum += dtcount.Rows.Count; } commandText = "select max(SETUPVARIABLEID) from SetUpVariables"; obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText); if (obj != null) { variableID = Convert.ToInt32(obj); } for (int i = 0; i < count; i++) { dt = _dsSelectedDataTemp.Tables[i].Clone(); dt = _dsSelectedDataTemp.Tables[i].Copy(); string variableName = dt.TableName; commandText = string.Format("select SETUPVARIABLEID from SETUPVARIABLES where SETUPVARIABLENAME='{0}' and SETUPVARIABLEDATASETID='{1}'", variableName, variableDatasetID); obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText); variableID++; commandText = string.Format("insert into SetUpVariables values({0},{1},'{2}','{3}')", variableID, variableDatasetID, variableName, gridDefinationID); fbCommand.CommandText = commandText; fbCommand.ExecuteNonQuery(); rowCount = dt.Rows.Count; for (int j = 0; j < (rowCount / 125) + 1; j++) { commandText = "execute block as declare SetupVariableID int;" + " BEGIN "; for (int k = 0; k < 125; k++) { if (j * 125 + k < dt.Rows.Count) { commandText = commandText + string.Format(" select SetupVariableID from SetupVariables where SetupVariableDataSetID={0} and SetupVariableName='{1}' and GridDefinitionID={2} into :SetupVariableID;", variableDatasetID, variableName, gridDefinationID); progBarVariable.Value++; progBarVariable.Refresh(); lblProgressBar.Text = Convert.ToString((int)((double)progBarVariable.Value * 100 / progBarVariable.Maximum)) + "%"; lblProgressBar.Refresh(); } else { continue; } commandText = commandText + string.Format(" insert into SETUPGEOGRAPHICVARIABLES values (:SetupVariableID,{0},{1},{2});", dt.Rows[j * 125 + k][0], dt.Rows[j * 125 + k][1], dt.Rows[j * 125 + k][2]); } commandText = commandText + "END"; fbCommand.CommandText = commandText; fbCommand.ExecuteNonQuery(); } } } fbtra.Commit(); fbCommand.Connection.Close(); progBarVariable.Visible = false; lblProgressBar.Visible = false; this.DialogResult = DialogResult.OK; this.Close(); } catch (Exception ex) { MessageBox.Show("Failed to load variable dataset."); fbtra.Rollback(); progBarVariable.Value = 0; progBarVariable.Visible = false; lblProgressBar.Text = ""; lblProgressBar.Visible = false; Logger.LogError(ex.Message); } }