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);
}
}
