private void btnOK_Click(object sender, EventArgs e)
{
IApplication theApplication = (IApplication)ArcMap.Application;
ArcMapFunctions myArcMapFuncs = new ArcMapFunctions(theApplication);
this.Cursor = Cursors.WaitCursor;
// Run the query. Everything else is allowed to be null.
string sDefaultSchema = myConfig.GetDatabaseSchema();
string sTableName = lstTables.Text;
string sColumnNames = txtColumns.Text;
string sWhereClause = txtWhere.Text;
string sGroupClause = txtGroupBy.Text;
string sOrderClause = txtOrderBy.Text;
string sOutputFormat = cmbOutFormat.Text;
string sOutputFile;
string sUserID = Environment.UserName;
// Do some basic checks and fix as required.
// User ID should be something at least
if (string.IsNullOrEmpty(sUserID))
{
sUserID = "Temp";
}
// Table name should always be selected
if (string.IsNullOrEmpty(sTableName))
{
MessageBox.Show("Please select a table to query from");
return;
}
// Decide whether or not there is a geometry field in the returned data.
// Select the stored procedure accordingly
string strCheck = "sp_geometry";
bool blSpatial = sColumnNames.ToLower().Contains(strCheck);
// If "*" is used check for the existence of a SP_GEOMETRY in the table.
if (sColumnNames == "*")
{
blSpatial = myArcMapFuncs.FieldExists(myConfig.GetSDEName(), sTableName, "SP_GEOMETRY");
}
// Set the temporary table names and the stored procedure names. Adjust output formats if required.
bool blFlatTable = !blSpatial; // to start with
string strStoredProcedure = "AFSelectSppSubset"; // Default for all data
string strPolyFC = sTableName + "_poly_" + sUserID; ;
string strPointFC = sTableName + "_point_" + sUserID;
string strTable = sTableName + "_" + sUserID;
bool blSplit = false;
if (blSpatial)
{
blSplit = true;
if (sOutputFormat == "Geodatabase") sOutputFormat = "Geodatabase FC";
}
else
{
if (sOutputFormat == "Geodatabase") sOutputFormat = "Geodatabase Table";
if (sOutputFormat == "Shapefile") sOutputFormat = "DBASE file";
}
// Get the output file name taking account of adjusted output formats.
sOutputFile = myArcMapFuncs.GetOutputFileName(sOutputFormat, myConfig.GetDefaultExtractPath());
if (sOutputFile == "None")
{
// User has pressed Cancel. Bring original menu to the front.
MessageBox.Show("Please select an output file");
this.Cursor = Cursors.Default;
this.Show();
return;
}
// Now we are all set to go - do the process.
// Set up all required parameters.
SqlConnection dbConn = myADOFuncs.CreateSQLConnection(myConfig.GetConnectionString());
SqlCommand myCommand = myADOFuncs.CreateSQLCommand(ref dbConn, strStoredProcedure, CommandType.StoredProcedure); // Note pass connection by ref here.
myADOFuncs.AddSQLParameter(ref myCommand, "Schema", sDefaultSchema);
myADOFuncs.AddSQLParameter(ref myCommand, "SpeciesTable", sTableName);
myADOFuncs.AddSQLParameter(ref myCommand, "ColumnNames", sColumnNames);
myADOFuncs.AddSQLParameter(ref myCommand, "WhereClause", sWhereClause);
myADOFuncs.AddSQLParameter(ref myCommand, "GroupByClause", sGroupClause);
myADOFuncs.AddSQLParameter(ref myCommand, "OrderByClause", sOrderClause);
myADOFuncs.AddSQLParameter(ref myCommand, "UserID", sUserID);
myADOFuncs.AddSQLParameter(ref myCommand, "Split", blSplit); // Calls overloaded method that takes int.
// Open ADO connection to database
dbConn.Open();
// Run the stored procedure.
try
{
string strRowsAffect = myCommand.ExecuteNonQuery().ToString();
}
catch (Exception ex)
{
MessageBox.Show("Could not execute stored procedure. System returned the following message: " +
ex.Message);
this.Cursor = Cursors.Default;
this.Show();
return;
}
// convert the results to the designated output file.
//string strtargetWorkspaceName = myFileFuncs.GetDirectoryName(sOutputFile);
string strPointOutTab = myConfig.GetSDEName() + @"\" + strPointFC; //sTableName + "_Point_" + sUserID;
string strPolyOutTab = myConfig.GetSDEName() + @"\" + strPolyFC; // sTableName + "_Poly_" + sUserID;
string strOutTab = myConfig.GetSDEName() + @"\" + strTable; // sTableName + "_" + sUserID;
string strOutPoints = "";
string strOutPolys = "";
bool blResult = false;
if (blSpatial)
{
// export points and polygons
// How is the data to be exported?
if (sOutputFormat == "Geodatabase")
{
// Easy, export without further ado.
strOutPoints = sOutputFile + "_Point";
strOutPolys = sOutputFile + "_Polys";
MessageBox.Show("About to export points to " + strOutPoints);
blResult = myArcMapFuncs.CopyFeatures(strPointOutTab, strOutPoints);
if (!blResult)
{
MessageBox.Show("Error exporting point geodatabase file");
return;
}
blResult = myArcMapFuncs.CopyFeatures(strPolyOutTab, strOutPolys);
if (!blResult)
{
MessageBox.Show("Error exporting polygon geodatabase file");
return;
}
}
else if (sOutputFormat == "Shapefile")
{
// Create file names first.
sOutputFile = myFileFuncs.ReturnWithoutExtension(sOutputFile);
strOutPoints = sOutputFile + "_Point.shp";
strOutPolys = sOutputFile + "_Poly.shp";
MessageBox.Show(strOutPoints);
blResult = myArcMapFuncs.CopyFeatures(strPointOutTab, strOutPoints);
if (!blResult)
{
MessageBox.Show("Error exporting point shapefile");
return;
}
blResult = myArcMapFuncs.CopyFeatures(strPolyOutTab, strOutPolys);
if (!blResult)
{
MessageBox.Show("Error exporting polygon shapefile");
return;
}
}
else
{
// Not a spatial export, but it is a spatial layer so there are two files.
blFlatTable = true;
sOutputFile = myFileFuncs.ReturnWithoutExtension(sOutputFile);
string strExtension = sOutputFile.Substring(sOutputFile.Length - 4, 4);
strOutPoints = sOutputFile + "_Point" + strExtension;
strOutPolys = sOutputFile + "_Poly" + strExtension;
blResult = myArcMapFuncs.CopyTable(strPointOutTab, strOutPoints);
if (!blResult)
{
MessageBox.Show("Error exporting output table");
return;
}
blResult = myArcMapFuncs.CopyTable(strPolyOutTab, strOutPolys);
if (!blResult)
{
MessageBox.Show("Error exporting output table");
return;
}
}
}
else
{
// We are exporting a non-spatial output.
blResult = myArcMapFuncs.CopyTable(strOutTab, sOutputFile);
if (!blResult)
{
MessageBox.Show("Error exporting output table");
return;
}
}
// Add the results to the screen.
if (blSpatial && !blFlatTable)
{
myArcMapFuncs.AddGroupLayerFromString("Test", strOutPoints, strOutPolys);
//myArcMapFuncs.AddFeatureLayerFromString(strOutPoints);
//myArcMapFuncs.AddFeatureLayerFromString(strOutPolys);
}
else if (blSpatial)
{
myArcMapFuncs.AddTableLayerFromString(strOutPoints);
myArcMapFuncs.AddTableLayerFromString(strOutPolys);
// Open table views.
}
else
{
myArcMapFuncs.AddTableLayerFromString(strOutTab);
// Open table view.
}
this.Cursor = Cursors.Default;
MessageBox.Show("Process complete");
}
private void btnOK_Click(object sender, EventArgs e)
{
// Let's get a save file first.
IApplication theApplication = (IApplication)ArcMap.Application;
ArcMapFunctions myArcMapFuncs = new ArcMapFunctions(theApplication);
this.Cursor = Cursors.WaitCursor;
// Run the query. Everything else is allowed to be null.
string sDefaultSchema = myConfig.GetDatabaseSchema();
string sTableName = lstTables.Text;
string sColumnNames = txtColumns.Text;
string sWhereClause = txtWhere.Text;
string sGroupClause = txtGroupBy.Text;
string sOrderClause = txtOrderBy.Text;
string sOutputFormat = cmbOutFormat.Text;
string sOutputFile;
string sUserID = Environment.UserName;
// Do some basic checks and fix as required.
// User ID should be something at least
if (string.IsNullOrEmpty(sUserID))
{
sUserID = "Temp";
}
// Table name should always be selected
if (string.IsNullOrEmpty(sTableName))
{
MessageBox.Show("Please select a table to query from");
return;
}
//if (string.IsNullOrEmpty(sOutputFile))
//{
// MessageBox.Show("Please specify an output file");
// return;
//}
// Decide whether or not there is a geometry field in the returned data.
// Select the stored procedure accordingly
string strCheck = "sp_geometry";
bool blSpatial = sColumnNames.ToLower().Contains(strCheck);
// TO DO: IF "*" IS USED CHECK FOR THE EXISTENCE OF THE SPATIAL FIELD IN THE TABLE.
// IF IT EXISTS SET blSpatial TO TRUE.
bool blFlatTable = !blSpatial; // to start with
string strStoredProcedure = "AFSelectSppSubset"; // Default for all data.
string strPolyFC = "";
string strPointFC = "";
string strTable = sTableName + "_" + sUserID;
if (blSpatial)
{
strPolyFC = sTableName + "_Poly_" + sUserID;
strPointFC = sTableName + "_Point_" + sUserID;
if (sOutputFormat == "Geodatabase") sOutputFormat = "Geodatabase FC";
}
else
{
if (sOutputFormat == "Geodatabase") sOutputFormat = "Geodatabase Table";
if (sOutputFormat == "Shapefile") sOutputFormat = "DBASE file";
}
// Get the output file name.
sOutputFile = myArcMapFuncs.GetOutputFileName(sOutputFormat, myConfig.GetDefaultExtractPath());
if (sOutputFile == "None")
{
MessageBox.Show("Please select an output file");
this.Cursor = Cursors.Default;
this.Show();
return;
}
// Now we are all set to go - do the process.
SqlConnection dbConn = myADOFuncs.CreateSQLConnection(myConfig.GetConnectionString());
SqlCommand myCommand = myADOFuncs.CreateSQLCommand(ref dbConn, strStoredProcedure, CommandType.StoredProcedure); // Note pass connection by ref here.
myADOFuncs.AddSQLParameter(ref myCommand, "Schema", sDefaultSchema);
myADOFuncs.AddSQLParameter(ref myCommand, "SpeciesTable", sTableName);
myADOFuncs.AddSQLParameter(ref myCommand, "ColumnNames", sColumnNames);
myADOFuncs.AddSQLParameter(ref myCommand, "WhereClause", sWhereClause);
myADOFuncs.AddSQLParameter(ref myCommand, "GroupByClause", sGroupClause);
myADOFuncs.AddSQLParameter(ref myCommand, "OrderByClause", sOrderClause);
myADOFuncs.AddSQLParameter(ref myCommand, "UserID", sUserID);
if (blSpatial)
{
myADOFuncs.AddSQLParameter(ref myCommand, "Split", "1");
}
else
{
myADOFuncs.AddSQLParameter(ref myCommand, "Split", "0");
}
dbConn.Open();
// Run the stored procedure.
string strRowsAffect = myCommand.ExecuteNonQuery().ToString();
// convert the results to the designated output file.
//string strtargetWorkspaceName = myFileFuncs.GetDirectoryName(sOutputFile);
string strPointOutTab = myConfig.GetSDEName() + @"\" + sTableName + "_Point_" + sUserID;
string strPolyOutTab = myConfig.GetSDEName() + @"\" + sTableName + "_Poly_" + sUserID;
string strOutTab = myConfig.GetSDEName() + @"\" + sTableName + "_" + sUserID;
string strOutPoints = "";
string strOutPolys = "";
bool blResult = false;
if (blSpatial)
{
// export points and polygons
// How is the data to be exported?
if (sOutputFormat == "Geodatabase")
{
// Easy, export without further ado.
strOutPoints = sOutputFile + "_Point";
strOutPolys = sOutputFile + "_Polys";
MessageBox.Show("About to export points to " + strOutPoints);
blResult = myArcMapFuncs.CopyFeatures(strPointOutTab, strOutPoints);
if (!blResult)
{
MessageBox.Show("Error exporting point geodatabase file");
return;
}
blResult = myArcMapFuncs.CopyFeatures(strPolyOutTab, strOutPolys);
if (!blResult)
{
MessageBox.Show("Error exporting polygon geodatabase file");
return;
}
}
else if (sOutputFormat == "Shapefile")
{
// Create file names first.
sOutputFile = myFileFuncs.ReturnWithoutExtension(sOutputFile);
strOutPoints = sOutputFile + "_Point.shp";
strOutPolys = sOutputFile + "_Poly.shp";
blResult = myArcMapFuncs.CopyFeatures(strPointOutTab, strOutPoints);
if (!blResult)
{
MessageBox.Show("Error exporting point shapefile");
return;
}
blResult = myArcMapFuncs.CopyFeatures(strPolyOutTab, strOutPolys);
if (!blResult)
{
MessageBox.Show("Error exporting polygon shapefile");
return;
}
}
else
{
// Not a spatial export, but it is a spatial layer so there are two files.
blFlatTable = true;
sOutputFile = myFileFuncs.ReturnWithoutExtension(sOutputFile);
string strExtension = sOutputFile.Substring(sOutputFile.Length - 4, 4);
strOutPoints = sOutputFile + "_Point" + strExtension;
strOutPolys = sOutputFile + "_Poly" + strExtension;
blResult = myArcMapFuncs.CopyTable(strPointOutTab, strOutPoints);
if (!blResult)
{
MessageBox.Show("Error exporting output table");
return;
}
blResult = myArcMapFuncs.CopyTable(strPolyOutTab, strOutPolys);
if (!blResult)
{
MessageBox.Show("Error exporting output table");
return;
}
}
}
else
{
// We are exporting a non-spatial output.
blResult = myArcMapFuncs.CopyTable(strOutTab, sOutputFile);
if (!blResult)
{
MessageBox.Show("Error exporting output table");
return;
}
}
// Add the results to the screen.
if (blSpatial && !blFlatTable)
{
myArcMapFuncs.AddFeatureLayerFromString(strOutPoints);
myArcMapFuncs.AddFeatureLayerFromString(strOutPolys);
}
else
{
//ITable theTable = theFWS.OpenTable(strTable);
//myArcMapFuncs.AddLayerFromTable(theTable, "Test");
myArcMapFuncs.AddTableLayerFromString(strOutTab);
}
this.Cursor = Cursors.Default;
MessageBox.Show("Process complete");
}
private void btnOK_Click(object sender, EventArgs e)
{
this.Cursor = Cursors.WaitCursor;
IApplication theApplication = (IApplication)ArcMap.Application;
ArcMapFunctions myArcMapFuncs = new ArcMapFunctions(theApplication);
// Run the query. Everything else is allowed to be null.
string sDefaultSchema = myConfig.GetDatabaseSchema();
string sTableName = lstTables.Text;
string sColumnNames = txtColumns.Text;
string sWhereClause = txtWhere.Text;
string sGroupClause = txtGroupBy.Text;
string sOrderClause = txtOrderBy.Text;
string sOutputFormat = cmbOutFormat.Text;
string sOutputFile;
string sUserID = Environment.UserName;
string strLogFile = myConfig.GetLogFilePath() + @"\DataSelector_" + sUserID + ".log";
if (chkLogFile.Checked)
{
bool blDeleted = myFileFuncs.DeleteFile(strLogFile);
if (!blDeleted)
{
MessageBox.Show("Cannot delete log file. Please make sure it is not open in another window");
return;
}
//myFileFuncs.CreateLogFile(strLogFile);
}
myFileFuncs.WriteLine(strLogFile, "-----------------------------------------------------------------------");
myFileFuncs.WriteLine(strLogFile, "Process started");
myFileFuncs.WriteLine(strLogFile, "-----------------------------------------------------------------------");
myArcMapFuncs.ToggleDrawing();
myArcMapFuncs.ToggleTOC();
// Do some basic checks and fix as required.
// User ID should be something at least
if (string.IsNullOrEmpty(sUserID))
{
sUserID = "Temp";
myFileFuncs.WriteLine(strLogFile, "Please note user ID is 'Temp'");
}
if (string.IsNullOrEmpty(sColumnNames))
{
myArcMapFuncs.ToggleDrawing();
myArcMapFuncs.ToggleTOC();
MessageBox.Show("Please specify which columns you wish to select");
this.BringToFront();
this.Cursor = Cursors.Default;
return;
}
// Table name should always be selected
if (string.IsNullOrEmpty(sTableName))
{
myArcMapFuncs.ToggleDrawing();
myArcMapFuncs.ToggleTOC();
MessageBox.Show("Please select a table to query from");
this.BringToFront();
this.Cursor = Cursors.Default;
return;
}
SqlConnection dbConn = mySQLServerFuncs.CreateSQLConnection(myConfig.GetConnectionString());
// Decide whether or not there is a geometry field in the returned data.
// Select the stored procedure accordingly
string[] strGeometryFields = {"SP_GEOMETRY", "Shape" }; // Expand as required.
bool blSpatial = false;
foreach (string strField in strGeometryFields)
{
if (sColumnNames.ToLower().Contains(strField.ToLower()))
blSpatial = true;
}
// If "*" is used check for the existence of a geometry field in the table.
if (sColumnNames == "*")
{
string strCheckTable = myConfig.GetDatabaseSchema() + "." + sTableName;
dbConn.Open();
foreach (string strField in strGeometryFields)
{
if (mySQLServerFuncs.FieldExists(ref dbConn, strCheckTable, strField))
blSpatial = true;
}
dbConn.Close();
}
// Set the temporary table names and the stored procedure names. Adjust output formats if required.
bool blFlatTable = !blSpatial; // to start with
string strStoredProcedure = "AFSelectSppSubset"; // Default for all data
string strPolyFC = sTableName + "_poly_" + sUserID; ;
string strPointFC = sTableName + "_point_" + sUserID;
string strTable = sTableName + "_" + sUserID;
string strSplit = "0";
if (blSpatial)
{
strSplit = "1";
if (sOutputFormat == "Geodatabase") sOutputFormat = "Geodatabase FC";
}
else
{
if (sOutputFormat == "Geodatabase") sOutputFormat = "Geodatabase Table";
if (sOutputFormat == "Shapefile") sOutputFormat = "dBASE file";
myFileFuncs.WriteLine(strLogFile, "Output is not spatial. Output file type is " + sOutputFormat);
}
// Get the output file name taking account of adjusted output formats.
sOutputFile = "None";
bool blDone = false;
bool blHasExtension = false;
while (!blDone)
{
sOutputFile = myArcMapFuncs.GetOutputFileName(sOutputFormat, myConfig.GetDefaultExtractPath());
if (sOutputFile != "None")
{
// firstly check extensions are as should be.
string strExtensionTest = sOutputFile.Substring(sOutputFile.Length - 4, 4).Substring(0, 1);
if (strExtensionTest == ".") blHasExtension = true;
// if there isn't, put one one.
if (sOutputFormat == "CSV file" && !blHasExtension)
{
sOutputFile = sOutputFile + ".csv";
}
else if (sOutputFormat == "dBASE file" && !blHasExtension)
{
sOutputFile = sOutputFile + ".dbf";
}
else if (sOutputFormat.Contains("Text file") & !blHasExtension)
{
sOutputFile = sOutputFile + ".txt";
}
else if (sOutputFormat == "Shapefile" && !blHasExtension)
{
sOutputFile = sOutputFile + ".shp";
}
else if ((sOutputFormat.Contains("Geodatabase")) && (blHasExtension || !sOutputFile.Contains(".gdb"))) // It is a geodatabase file and should not have an extension.
{
MessageBox.Show("Please select a file geodatabase output file");
myArcMapFuncs.ToggleDrawing();
myArcMapFuncs.ToggleTOC();
this.Cursor = Cursors.Default;
this.BringToFront();
return;
}
else if ((!sOutputFormat.Contains("Geodatabase")) && (sOutputFile.Contains(".gdb"))) // Trying to store a non-geoDB in a gdb
{
MessageBox.Show("Cannot store " + sOutputFormat + " inside a geodatabase. Please choose a different output location");
myArcMapFuncs.ToggleDrawing();
myArcMapFuncs.ToggleTOC();
this.Cursor = Cursors.Default;
this.BringToFront();
return;
}
}
else
blDone = true; // user pressed cancel.
if (blSpatial && blDone != true)
{
// Check if the outputfile_point or outputfile_poly already exists. For dBase and text output the dialog does its own check.
string sTest1 = "";
string sTest2 = "";
if (sOutputFormat.Contains("Geodatabase"))
{
sTest1 = sOutputFile + "_Point";
sTest2 = sOutputFile + "_Poly";
}
else if (sOutputFormat == "Shapefile")
{
string strExtensionTest1 = sOutputFile.Substring(sOutputFile.Length - 4, 4).Substring(0, 1);
if (strExtensionTest1 == ".")
{
sTest1 = sOutputFile.Substring(0, sOutputFile.Length - 4) + "_Point.shp";
sTest2 = sOutputFile.Substring(0, sOutputFile.Length - 4) + "_Poly.shp";
}
else
{
sTest1 = sOutputFile + "_Point.shp";
sTest2 = sOutputFile + "_Poly.shp";
}
}
if (sOutputFormat.Contains("Geodatabase") || sOutputFormat == "Shapefile")
{
if (myArcMapFuncs.FeatureclassExists(sTest1) || myArcMapFuncs.FeatureclassExists(sTest2))
{
DialogResult dlResult1 = MessageBox.Show("The output file already exists. Do you want to overwrite it?", "Data Selector", MessageBoxButtons.YesNo);
if (dlResult1 == System.Windows.Forms.DialogResult.Yes)
blDone = true;
}
else
blDone = true;
}
else
{
// Check for dBase and CSV
if (sOutputFormat == "dBASE file" || ((sOutputFormat == "CSV file" || sOutputFormat.Contains("Text file")))) //& !blHasExtension
// Basically if the user chose a text file with an extension, the dialog will already have given her feedback and we don't need to do this again.
{
if (myFileFuncs.FileExists(sOutputFile))
{
DialogResult dlResult1 = MessageBox.Show("The output file already exists. Do you want to overwrite it?", "Data Selector", MessageBoxButtons.YesNo);
if (dlResult1 == System.Windows.Forms.DialogResult.Yes)
blDone = true;
}
else
blDone = true;
}
else
blDone = true; // Text file; already checked by dialog.
}
}
else if (blDone != true) // non-spatial, not done yet.
{
// Test for the types of flat output.
if (sOutputFormat.Contains("Geodatabase"))
{
if (myArcMapFuncs.TableExists(sOutputFile))
{
DialogResult dlResult1 = MessageBox.Show("The output file already exists. Do you want to overwrite it?", "Data Selector", MessageBoxButtons.YesNo);
if (dlResult1 == System.Windows.Forms.DialogResult.Yes)
blDone = true;
}
else
blDone = true;
}
else if (sOutputFormat == "dBASE file" || ((sOutputFormat == "CSV file" || sOutputFormat.Contains("Text file")) & !blHasExtension))
// Basically if the user chose a text file, the dialog will already have given her feedback and we don't need to do this again.
{
if (myFileFuncs.FileExists(sOutputFile))
{
DialogResult dlResult1 = MessageBox.Show("The output file already exists. Do you want to overwrite it?", "Data Selector", MessageBoxButtons.YesNo);
if (dlResult1 == System.Windows.Forms.DialogResult.Yes)
blDone = true;
}
else
blDone = true;
}
else
blDone = true; // Text file; already checked by dialog.
}
}
this.BringToFront();
if (sOutputFile == "None")
{
// User has pressed Cancel. Bring original menu to the front.
MessageBox.Show("Please select an output file");
myArcMapFuncs.ToggleDrawing();
myArcMapFuncs.ToggleTOC();
this.Cursor = Cursors.Default;
return;
}
this.Focus();
myFileFuncs.WriteLine(strLogFile, "Output format is " + sOutputFormat);
myFileFuncs.WriteLine(strLogFile, "Output file is " + sOutputFile);
myFileFuncs.WriteLine(strLogFile, "Note that spatial output (Shapefile, Geodatabase) is split into _point and _poly components");
////////////////////////////////////////////////////// INPUT ALL CHECKED AND OK, START PROCESS ////////////////////////////////////////////////////////
string strLayerName = myFileFuncs.GetFileName(sOutputFile);
if (!sOutputFormat.Contains("Geodatabase"))
{
strLayerName = myFileFuncs.ReturnWithoutExtension(strLayerName);
}
// Now we are all set to go - do the process.
// Set up all required parameters.
//SqlConnection dbConn = myADOFuncs.CreateSQLConnection(myConfig.GetConnectionString());
SqlCommand myCommand = mySQLServerFuncs.CreateSQLCommand(ref dbConn, strStoredProcedure, CommandType.StoredProcedure); // Note pass connection by ref here.
mySQLServerFuncs.AddSQLParameter(ref myCommand, "Schema", sDefaultSchema);
mySQLServerFuncs.AddSQLParameter(ref myCommand, "SpeciesTable", sTableName);
mySQLServerFuncs.AddSQLParameter(ref myCommand, "ColumnNames", sColumnNames);
mySQLServerFuncs.AddSQLParameter(ref myCommand, "WhereClause", sWhereClause);
mySQLServerFuncs.AddSQLParameter(ref myCommand, "GroupByClause", sGroupClause);
mySQLServerFuncs.AddSQLParameter(ref myCommand, "OrderByClause", sOrderClause);
mySQLServerFuncs.AddSQLParameter(ref myCommand, "UserID", sUserID);
mySQLServerFuncs.AddSQLParameter(ref myCommand, "Split", strSplit);
myFileFuncs.WriteLine(strLogFile, "Species table is " + sTableName);
myFileFuncs.WriteLine(strLogFile, "Column names are " + sColumnNames.Replace("\r\n", " "));
if (sWhereClause.Length > 0)
myFileFuncs.WriteLine(strLogFile, "Where clause is " + sWhereClause.Replace("\r\n", " "));
else
myFileFuncs.WriteLine(strLogFile, "No where clause was used");
if (sGroupClause.Length > 0)
myFileFuncs.WriteLine(strLogFile, "Group by clause is " + sGroupClause.Replace("\r\n", " "));
else
myFileFuncs.WriteLine(strLogFile, "No group by clause was used");
if (sOrderClause.Length > 0)
myFileFuncs.WriteLine(strLogFile, "Order by clause is " + sOrderClause.Replace("\r\n", " "));
else
myFileFuncs.WriteLine(strLogFile, "No order by clause was used");
if (strSplit == "1")
myFileFuncs.WriteLine(strLogFile, "Data is spatial and will be split into a point and a polygon layer");
else
myFileFuncs.WriteLine(strLogFile, "Data is not spatial and will not be split");
// Open SQL connection to database and
// Run the stored procedure.
bool blSuccess = true;
int intCount = 0;
int intPolyCount = 0;
int intPointCount = 0;
try
{
myFileFuncs.WriteLine(strLogFile, "Opening SQL Connection");
dbConn.Open();
myFileFuncs.WriteLine(strLogFile, "Executing stored procedure");
string strRowsAffect = myCommand.ExecuteNonQuery().ToString();
if (blSpatial)
{
blSuccess = mySQLServerFuncs.TableHasRows(ref dbConn, strPointFC);
if (!blSuccess)
blSuccess = mySQLServerFuncs.TableHasRows(ref dbConn, strPolyFC);
}
else
blSuccess = mySQLServerFuncs.TableHasRows(ref dbConn, strTable);
if (blSuccess && blSpatial)
{
intPolyCount = mySQLServerFuncs.CountRows(ref dbConn, strPolyFC);
intPointCount = mySQLServerFuncs.CountRows(ref dbConn, strPointFC);
myFileFuncs.WriteLine(strLogFile, "Procedure returned " + intPointCount.ToString() + " point and " + intPolyCount.ToString() +
" polygon records");
}
else if (blSuccess)
{
intCount = mySQLServerFuncs.CountRows(ref dbConn, strTable);
myFileFuncs.WriteLine(strLogFile, "Procedure returned " + intCount.ToString() + " records");
}
myFileFuncs.WriteLine(strLogFile, "Closing SQL Connection");
dbConn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Could not execute stored procedure. System returned the following message: " +
ex.Message);
myFileFuncs.WriteLine(strLogFile, "Could not execute stored procedure. System returned the following message: " +
ex.Message);
this.Cursor = Cursors.Default;
dbConn.Close();
myArcMapFuncs.ToggleDrawing();
myArcMapFuncs.ToggleTOC();
this.BringToFront();
return;
}
// convert the results to the designated output file.
string strPointOutTab = myConfig.GetSDEName() + @"\" + strPointFC;
string strPolyOutTab = myConfig.GetSDEName() + @"\" + strPolyFC;
string strOutTab = myConfig.GetSDEName() + @"\" + strTable;
string strOutPoints = "";
string strOutPolys = "";
bool blResult = false;
if (blSpatial && blSuccess)
{
// export points and polygons
// How is the data to be exported?
if (sOutputFormat == "Geodatabase FC")
{
// Easy, export without further ado.
strOutPoints = sOutputFile + "_Point";
strOutPolys = sOutputFile + "_Poly";
if (intPointCount > 0)
{
myFileFuncs.WriteLine(strLogFile, "Copying point results to point geodatabase file");
blResult = myArcMapFuncs.CopyFeatures(strPointOutTab, strOutPoints);
if (!blResult)
{
MessageBox.Show("Error exporting point geodatabase file");
myFileFuncs.WriteLine(strLogFile, "Error exporting point geodatabase file");
this.Cursor = Cursors.Default;
myArcMapFuncs.ToggleDrawing();
myArcMapFuncs.ToggleTOC();
this.BringToFront();
return;
}
}
if (intPolyCount > 0)
{
blResult = myArcMapFuncs.CopyFeatures(strPolyOutTab, strOutPolys);
myFileFuncs.WriteLine(strLogFile, "Copying polygon results to polygon geodatabase file");
if (!blResult)
{
MessageBox.Show("Error exporting polygon geodatabase file");
myFileFuncs.WriteLine(strLogFile, "Error exporting polygon geodatabase file");
this.Cursor = Cursors.Default;
myArcMapFuncs.ToggleDrawing();
myArcMapFuncs.ToggleTOC();
this.BringToFront();
return;
}
}
}
else if (sOutputFormat == "Shapefile" & blSuccess)
{
// Create file names first.
sOutputFile = myFileFuncs.ReturnWithoutExtension(sOutputFile);
strOutPoints = sOutputFile + "_Point.shp";
strOutPolys = sOutputFile + "_Poly.shp";
if (intPointCount > 0)
{
myFileFuncs.WriteLine(strLogFile, "Copying point results to point shapefile");
blResult = myArcMapFuncs.CopyFeatures(strPointOutTab, strOutPoints);
if (!blResult)
{
MessageBox.Show("Error exporting point shapefile");
myFileFuncs.WriteLine(strLogFile, "Error exporting point shapefile");
this.Cursor = Cursors.Default;
myArcMapFuncs.ToggleDrawing();
myArcMapFuncs.ToggleTOC();
this.BringToFront();
return;
}
}
if (intPolyCount > 0)
{
myFileFuncs.WriteLine(strLogFile, "Copying polygon results to polygon shapefile");
blResult = myArcMapFuncs.CopyFeatures(strPolyOutTab, strOutPolys);
if (!blResult)
{
MessageBox.Show("Error exporting polygon shapefile");
myFileFuncs.WriteLine(strLogFile, "Error exporting polygon shapefile");
this.Cursor = Cursors.Default;
myArcMapFuncs.ToggleDrawing();
myArcMapFuncs.ToggleTOC();
this.BringToFront();
return;
}
}
}
else if (sOutputFormat.Contains("Text file"))
{
// Not a spatial export, but it is a spatial layer so there are two files.
// Function pulls them back together again.
// if schema.ini file exists delete it.
string strIniFile = myFileFuncs.GetDirectoryName(sOutputFile) + "\\schema.ini";
if (myFileFuncs.FileExists(strIniFile))
{
bool blDeleted = myFileFuncs.DeleteFile(strIniFile); // Not checking for success at the moment.
}
blFlatTable = true;
bool blAppend = false;
if (intPointCount > 0)
{
myFileFuncs.WriteLine(strLogFile, "Copying point results to text file");
blResult = myArcMapFuncs.CopyToTabDelimitedFile(strPointOutTab, sOutputFile, true, false, true);
if (!blResult)
{
MessageBox.Show("Error exporting output table to text file " + sOutputFile);
myFileFuncs.WriteLine(strLogFile, "Error exporting output table to text file " + sOutputFile);
this.Cursor = Cursors.Default;
myArcMapFuncs.ToggleDrawing();
myArcMapFuncs.ToggleTOC();
this.BringToFront();
return;
}
blAppend = true;
}
// Also export the second table - append if necessary
if (intPolyCount > 0)
{
myFileFuncs.WriteLine(strLogFile, "exporting polygon results to text file");
blResult = myArcMapFuncs.CopyToTabDelimitedFile(strPolyOutTab, sOutputFile, true, blAppend, true);
if (!blResult)
{
MessageBox.Show("Error appending output table to text file " + sOutputFile);
myFileFuncs.WriteLine(strLogFile, "Error appending output table to text file " + sOutputFile);
this.Cursor = Cursors.Default;
myArcMapFuncs.ToggleDrawing();
myArcMapFuncs.ToggleTOC();
this.BringToFront();
}
}
// Add the output to ArcMap
myFileFuncs.WriteLine(strLogFile, "Adding output to ArcMap view");
myArcMapFuncs.AddTableLayerFromString(sOutputFile, strLayerName);
}
else if (sOutputFormat == "CSV file" || sOutputFormat == "dBASE file")
{
// Not a spatial export, but it is a spatial layer so there are two files.
// Function pulls them back together again.
// if schema.ini file exists delete it.
string strIniFile = myFileFuncs.GetDirectoryName(sOutputFile) + "\\schema.ini";
if (myFileFuncs.FileExists(strIniFile))
{
bool blDeleted = myFileFuncs.DeleteFile(strIniFile); // Not checking for success at the moment.
}
blFlatTable = true;
string sFinalFile = "";
if (sOutputFormat == "dBASE file")
{
sFinalFile = sOutputFile;
sOutputFile = myFileFuncs.GetDirectoryName(sOutputFile) + "\\Temp.csv";
}
bool blAppend = false;
if (intPointCount > 0)
{
myFileFuncs.WriteLine(strLogFile, "Copying point results to CSV file");
blResult = myArcMapFuncs.CopyToCSV(strPointOutTab, sOutputFile, true, false, true);
if (!blResult)
{
MessageBox.Show("Error exporting output table to CSV file " + sOutputFile);
myFileFuncs.WriteLine(strLogFile, "Error exporting output table to CSV file " + sOutputFile);
this.Cursor = Cursors.Default;
myArcMapFuncs.ToggleDrawing();
myArcMapFuncs.ToggleTOC();
this.BringToFront();
return;
}
blAppend = true;
}
// Also export the second table - append if necessary.
if (intPolyCount > 0)
{
myFileFuncs.WriteLine(strLogFile, "Appending polygon results to CSV file");
blResult = myArcMapFuncs.CopyToCSV(strPolyOutTab, sOutputFile, true, blAppend, true);
if (!blResult)
{
MessageBox.Show("Error appending output table to CSV file " + sOutputFile);
myFileFuncs.WriteLine(strLogFile, "Error appending output table to CSV file " + sOutputFile);
this.Cursor = Cursors.Default;
myArcMapFuncs.ToggleDrawing();
myArcMapFuncs.ToggleTOC();
this.BringToFront();
}
}
// If the end output is a dBASE file, export the resulting csv to dBASE.
if (sOutputFormat == "dBASE file")
{
myFileFuncs.WriteLine(strLogFile, "Converting text file to dBASE file");
blResult = myArcMapFuncs.CopyTable(sOutputFile, sFinalFile);
// Delete csv file.
try
{
myFileFuncs.WriteLine(strLogFile, "Deleting temporary CSV file");
File.Delete(sOutputFile);
}
catch (Exception ex)
{
MessageBox.Show("Error deleting temporary CSV file: " + ex.Message);
myFileFuncs.WriteLine(strLogFile, "Error deleting temporary CSV file: " + ex.Message);
this.Cursor = Cursors.Default;
myArcMapFuncs.ToggleDrawing();
myArcMapFuncs.ToggleTOC();
this.BringToFront();
}
if (!blResult)
{
MessageBox.Show("Error exporting output table to dBASE file " + sFinalFile);
myFileFuncs.WriteLine(strLogFile, "Error exporting output table to dBASE file " + sFinalFile);
this.Cursor = Cursors.Default;
myArcMapFuncs.ToggleDrawing();
myArcMapFuncs.ToggleTOC();
this.BringToFront();
return;
}
sOutputFile = sFinalFile;
}
else
{
myFileFuncs.WriteLine(strLogFile, "Adding output to ArcMap view");
myArcMapFuncs.AddTableLayerFromString(sOutputFile, strLayerName);
}
}
}
else if (blSuccess) // Non-spatial query, successfully run.
{
if (sOutputFormat == "CSV file")
{
// We are exporting a non-spatial output to CSV file.
myFileFuncs.WriteLine(strLogFile, "Copying results to CSV file");
blResult = myArcMapFuncs.CopyToCSV(strOutTab, sOutputFile, false, false, true);
if (!blResult)
{
MessageBox.Show("Error exporting output table to CSV file " + sOutputFile);
myFileFuncs.WriteLine(strLogFile, "Error exporting output table to CSV file " + sOutputFile);
this.Cursor = Cursors.Default;
myArcMapFuncs.ToggleDrawing();
myArcMapFuncs.ToggleTOC();
this.BringToFront();
return;
}
myFileFuncs.WriteLine(strLogFile, "Adding output to ArcMap view");
myArcMapFuncs.AddTableLayerFromString(sOutputFile, strLayerName);
}
else if (sOutputFormat.Contains("Text file"))
{
// We are exporting a non-spatial output to text file.
myFileFuncs.WriteLine(strLogFile, "Copying results to text file");
blResult = myArcMapFuncs.CopyToTabDelimitedFile(strOutTab, sOutputFile, false, false, true);
if (!blResult)
{
MessageBox.Show("Error exporting output table to text file " + sOutputFile);
myFileFuncs.WriteLine(strLogFile, "Error exporting output table to text file " + sOutputFile);
this.Cursor = Cursors.Default;
myArcMapFuncs.ToggleDrawing();
myArcMapFuncs.ToggleTOC();
this.BringToFront();
return;
}
myFileFuncs.WriteLine(strLogFile, "Adding output to ArcMap view");
myArcMapFuncs.AddTableLayerFromString(sOutputFile, strLayerName);
}
else
{
// We are exporting any non-spatial output to dbf or geodatabase.
blResult = myArcMapFuncs.CopyTable(strOutTab, sOutputFile);
if (!blResult)
{
MessageBox.Show("Error exporting output table");
myFileFuncs.WriteLine(strLogFile, "Error exporting output table");
this.Cursor = Cursors.Default;
myArcMapFuncs.ToggleDrawing();
myArcMapFuncs.ToggleTOC();
this.BringToFront();
return;
}
}
}
else if (!blSuccess)
{
if (sOutputFormat == "CSV file")
{
if (sColumnNames == "*")
{
dbConn.Open();
string[] strColumnNames = mySQLServerFuncs.GetFieldNames(ref dbConn, sTableName);
dbConn.Close();
sColumnNames = "";
foreach (string strField in strColumnNames)
{
sColumnNames = sColumnNames + strField + ",";
}
// Remove last comma
sColumnNames = sColumnNames.Substring(0, sColumnNames.Length - 1);
myArcMapFuncs.WriteEmptyCSV(sOutputFile, sColumnNames);
MessageBox.Show("There were no results for the query. An empty text file has been created");
myFileFuncs.WriteLine(strLogFile, "There were no results for the query. An empty text file has been created");
}
}
else
{
MessageBox.Show("There were no results for this query. No output has been created");
myFileFuncs.WriteLine(strLogFile, "There were no results for the query. No output has been created");
}
}
// Delete the temporary tables in the SQL database
strStoredProcedure = "AFClearSppSubset";
SqlCommand myCommand2 = mySQLServerFuncs.CreateSQLCommand(ref dbConn, strStoredProcedure, CommandType.StoredProcedure); // Note pass connection by ref here.
mySQLServerFuncs.AddSQLParameter(ref myCommand2, "Schema", sDefaultSchema);
mySQLServerFuncs.AddSQLParameter(ref myCommand2, "SpeciesTable", sTableName);
mySQLServerFuncs.AddSQLParameter(ref myCommand2, "UserId", sUserID);
try
{
myFileFuncs.WriteLine(strLogFile, "Opening SQL connection");
dbConn.Open();
myFileFuncs.WriteLine(strLogFile, "Deleting temporary tables");
string strRowsAffect = myCommand2.ExecuteNonQuery().ToString();
myFileFuncs.WriteLine(strLogFile, "Closing SQL connection");
dbConn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Could not execute stored procedure. System returned the following message: " +
ex.Message);
myFileFuncs.WriteLine(strLogFile, "Could not execute stored procedure. System returned the following message: " +
ex.Message);
this.Cursor = Cursors.Default;
myArcMapFuncs.ToggleDrawing();
myArcMapFuncs.ToggleTOC();
this.BringToFront();
return;
}
// Move the results to the right location or show as appropriate.
if (!blFlatTable && blSuccess) // Only truly spatial output has two files.
{
myFileFuncs.WriteLine(strLogFile, "Adding output to ArcMap project in group layer " + strLayerName);
if (intPointCount > 0)
{
ILayer lyrPoints = myArcMapFuncs.GetLayer(strLayerName + "_Point");
myArcMapFuncs.MoveToGroupLayer(strLayerName, lyrPoints);
}
if (intPolyCount > 0)
{
ILayer lyrPolys = myArcMapFuncs.GetLayer(strLayerName + "_Poly");
myArcMapFuncs.MoveToGroupLayer(strLayerName, lyrPolys);
}
}
else if (blSuccess)
{
myFileFuncs.WriteLine(strLogFile, "Showing table output on screen");
myArcMapFuncs.ShowTable(strLayerName);
}
myFileFuncs.WriteLine(strLogFile, "---------------------------------------------------------------------------");
myFileFuncs.WriteLine(strLogFile, "Process complete");
myFileFuncs.WriteLine(strLogFile, "---------------------------------------------------------------------------");
this.Cursor = Cursors.Default;
myArcMapFuncs.ToggleTOC();
if (!blFlatTable && blSuccess)
{
myArcMapFuncs.ZoomToFullExtent();
}
myArcMapFuncs.ToggleDrawing();
DialogResult dlResult = MessageBox.Show("Process complete. Do you wish to close the form?", "Data Selector", MessageBoxButtons.YesNo);
if (dlResult == System.Windows.Forms.DialogResult.Yes)
this.Close();
else this.BringToFront();
Process.Start("notepad.exe", strLogFile);
// Tidy up
myCommand.Dispose();
myCommand2.Dispose();
dbConn.Dispose();
}
