public void DeleteFromImageView()
{
// First call the FormManager and have it close all open tabs, except NETWORK_DEFINITION, ASSETS, CONSTRUCTION_HISTORY
// other ATTRIBUTE (RAW) tabs and Calculated fields.
ConnectionParameters cp = DBMgr.GetAttributeConnectionObject(m_strAttributeToDelete);
List <String> listCommandText = new List <String>();
m_strAttributeToDelete = m_strAttributeToDelete.Replace("_", "!_");
String strDelete = "DELETE FROM ATTRIBUTES_ WHERE ATTRIBUTE_ = '" + m_strAttributeToDelete + "'";
listCommandText.Add(strDelete);
DeleteFromNetworks(listCommandText);
DBMgr.ExecuteBatchNonQuery(listCommandText);
if (cp.IsNative)
{
strDelete = "DROP TABLE " + m_strAttributeToDelete;
DBMgr.ExecuteNonQuery(strDelete);
}
else
{
String dropView = "DROP VIEW " + m_strAttributeToDelete;
DBMgr.ExecuteNonQuery(dropView, cp);
}
}
private bool CheckAttributeQuery()
{
ConnectionParameters cp = DBMgr.GetAttributeConnectionObject(m_strAttribute);
bool bGoodQuery = false;
String strWhere = textBoxSearch.Text.ToString();
strWhere = strWhere.Replace("[" + m_strAttribute + "]", " DATA_ "); //.Replace( "DATE", " to_char( DATE_, 'MM/DD/YYYY' ) " );
String strQuery;
String strTable = m_strAttribute;
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
if (m_bLinear)
{
strQuery = "SELECT COUNT(*) FROM " + strTable + " WHERE ROUTES<>'' AND (" + strWhere + ")";
}
else
{
strQuery = "SELECT COUNT(*) FROM " + strTable + " WHERE FACILITY<>'' AND (" + strWhere + ")";
}
break;
case "ORACLE":
if (m_bLinear)
{
strQuery = "SELECT COUNT(*) FROM " + strTable + " WHERE ROUTES LIKE '_%' AND (" + strWhere + ")";
}
else
{
strQuery = "SELECT COUNT(*) FROM " + strTable + " WHERE FACILITY LIKE '_%' AND (" + strWhere + ")";
}
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
try
{
DataSet ds = DBMgr.ExecuteQuery(strQuery, cp);
if (ds.Tables.Count == 1)
{
String strOut = ds.Tables[0].Rows[0].ItemArray[0].ToString();
strOut += " entries returned.";
labelReturn.Text = strOut;
labelReturn.Visible = true;
bGoodQuery = true;
}
}
catch (Exception sqlE)
{
string[] str = sqlE.ToString().Split('\n');
String strError = str[0].Replace("DATA_", "[" + m_strAttribute + "]");
Global.WriteOutput("Error: Could not get attribute count from data table. " + sqlE.Message);
}
return(bGoodQuery);
}
private void UpdateCalculatedFieldsValues()
{
listBoxValues.Items.Clear();
String attribute = treeViewAttribute.SelectedNode.Text;
String strSelect = "SELECT DISTINCT DATA_ FROM " + attribute;
ConnectionParameters cp = DBMgr.GetAttributeConnectionObject(attribute);
try
{
DataReader dr = new DataReader(strSelect, cp);
while (dr.Read())
{
listBoxValues.Items.Add(dr["DATA_"].ToString());
}
dr.Close();
}
catch (Exception exception)
{
Global.WriteOutput("Error: Connecting to RAW ATTRIBUTE = " + attribute + "." + exception.Message);
}
}
/// <summary>
/// Retrieves a typical list of values for a given raw attribute.
/// </summary>
/// <param name="strAttribute">Raw attribute table</param>
/// <param name="bShowAll">True if show all unique</param>
/// <returns></returns>
public static List <String> GetRawAttributeValue(String strAttribute, bool bShowAll)
{
List <String> listValue = new List <String>();
int nCount = GetRawAttributeCount(strAttribute);
int nMode = 1;
while (nCount / nMode > 100)
{
nMode = nMode * 2;
}
ConnectionParameters cp = DBMgr.GetAttributeConnectionObject(strAttribute);
String strSelect = "SELECT DISTINCT DATA_ FROM " + strAttribute + " ";
if (!bShowAll)
{
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strSelect += "WHERE ID%" + nMode.ToString() + "=0 ";
break;
case "ORACLE":
//throw new NotImplementedException("TODO: Create ORACLE implementation for GetRawAttributeValue()");
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for GetRawAttributeValue()");
}
}
strSelect += "ORDER BY DATA";
DataSet ds = DBMgr.ExecuteQuery(strSelect, cp);
foreach (DataRow dr in ds.Tables[0].Rows)
{
listValue.Add(dr["DATA"].ToString());
}
return(listValue);
}
private void dgvRollup_SelectionChanged(object sender, EventArgs e)
{
if (dgvRollup.CurrentRow.Cells[0].Value == null)
{
return;
}
String strAttribute = dgvRollup.CurrentRow.Cells[0].Value.ToString();
ConnectionParameters cp = DBMgr.GetAttributeConnectionObject(strAttribute);
DataSet ds = DBMgr.ExecuteQuery("SELECT COUNT(*) FROM " + strAttribute, cp);
int nCount = 1;
int nMode = 1;
int.TryParse(ds.Tables[0].Rows[0].ItemArray[0].ToString(), out nCount);
//Retrieve a reasonable number of selections.
while ((float)nCount / (float)nMode > 100)
{
nMode = nMode * 2;
}
listBoxAttributes.Items.Clear();
String strSelect;
if (strAttribute != "PCI" && strAttribute != "CLIMATE_PCI" && strAttribute != "LOAD_PCI" && strAttribute != "OTHER_PCI")
{
strSelect = "SELECT DISTINCT DATA_ FROM " + strAttribute;
}
else
{
strSelect = "SELECT DISTINCT " + strAttribute + " FROM PCI";
}
ds = DBMgr.ExecuteQuery(strSelect, cp);
foreach (DataRow row in ds.Tables[0].Rows)
{
listBoxAttributes.Items.Add(row[0].ToString());
}
}
public void Delete()
{
// First call the FormManager and have it close all open tabs, except NETWORK_DEFINITION, ASSETS, CONSTRUCTION_HISTORY
// other ATTRIBUTE (RAW) tabs and Calculated fields.
FormManager.CloseSegmentationTabs();
FormManager.CloseAttributeTab(m_strAttributeToDelete);
FormManager.CloseNetworkTabs();
FormManager.CloseSimulationTabs();
ConnectionParameters cp = DBMgr.GetAttributeConnectionObject(m_strAttributeToDelete);
List <String> listCommandText = new List <String>();
//dsmelser 2009.02.08
//!!!THERE IS NO DEFAULT ESCAPE CHARACTER IN SQL, IT MUST BE EXPLICITLY DEFINED!!!
String strDelete = "DELETE FROM ATTRIBUTES_ WHERE ATTRIBUTE_ = '" + m_strAttributeToDelete + "'";
listCommandText.Add(strDelete);
String strUpdate = "";
string attributeSearch = m_strAttributeToDelete.Replace("_", "!_");
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strDelete = "DELETE FROM FEASIBILITY WHERE CRITERIA LIKE '%![" + attributeSearch + "!]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM COSTS WHERE CRITERIA LIKE '%![" + attributeSearch + "!]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM CONSEQUENCES WHERE CRITERIA LIKE '%![" + attributeSearch + "!]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM PERFORMANCE WHERE CRITERIA LIKE '%![" + attributeSearch + "!]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM PERFORMANCE WHERE EQUATION LIKE '%![" + attributeSearch + "!]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strUpdate = "UPDATE SIMULATIONS SET JURISDICTION = NULL WHERE JURISDICTION LIKE " +
"'%![" + attributeSearch + "!]%' ESCAPE '!'";
listCommandText.Add(strUpdate);
strDelete = "DELETE FROM DEFICIENTS WHERE CRITERIA LIKE '%![" + attributeSearch + "!]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM TARGETS WHERE CRITERIA LIKE '%![" + attributeSearch + "!]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM PRIORITY WHERE CRITERIA LIKE '%![" + attributeSearch + "!]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM CRITERIA_SEGMENT WHERE FAMILY_EXPRESSION LIKE '%![" + attributeSearch + "!]%' ESCAPE '!'";
listCommandText.Add(strDelete);
break;
case "ORACLE":
//we don't need to escape the brackets in ORACLE so it throws an error if we attempt it.
strDelete = "DELETE FROM FEASIBILITY WHERE CRITERIA LIKE '%[" + attributeSearch + "]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM COSTS WHERE CRITERIA LIKE '%[" + attributeSearch + "]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM CONSEQUENCES WHERE CRITERIA LIKE '%[" + attributeSearch + "]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM PERFORMANCE WHERE CRITERIA LIKE '%[" + attributeSearch + "]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM PERFORMANCE WHERE EQUATION LIKE '%[" + attributeSearch + "]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strUpdate = "UPDATE SIMULATIONS SET JURISDICTION = NULL WHERE JURISDICTION LIKE " +
"'%[" + attributeSearch + "]%' ESCAPE '!'";
listCommandText.Add(strUpdate);
strDelete = "DELETE FROM DEFICIENTS WHERE CRITERIA LIKE '%[" + attributeSearch + "]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM TARGETS WHERE CRITERIA LIKE '%[" + attributeSearch + "]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM PRIORITY WHERE CRITERIA LIKE '%[" + attributeSearch + "]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM CRITERIA_SEGMENT WHERE FAMILY_EXPRESSION LIKE '%[" + attributeSearch + "]%' ESCAPE '!'";
listCommandText.Add(strDelete);
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
DeleteFromNetworks(listCommandText);
try
{
DBMgr.ExecuteBatchNonQuery(listCommandText);
}
catch (Exception exc)
{
Global.WriteOutput("Error: Failed to remove attribute. " + exc.Message);
return;
}
if (!m_IsAttributeCalculated)
{
if (cp.IsNative)
{
strDelete = "DROP TABLE " + m_strAttributeToDelete;
try
{
DBMgr.ExecuteNonQuery(strDelete);
}
catch (Exception exc)
{
Global.WriteOutput("Error: Could not drop attribute table " + m_strAttributeToDelete + ". " + exc.Message);
}
}
else
{
String dropView = "DROP VIEW " + m_strAttributeToDelete;
try
{
DBMgr.ExecuteNonQuery(dropView, cp);
}
catch (Exception exc)
{
Global.WriteOutput("Error: Could not drop non native view. " + exc.Message);
}
}
}
else
{
try
{
DBMgr.ExecuteNonQuery("DELETE FROM ATTRIBUTES_CALCULATED WHERE ATTRIBUTE_ = '" + m_strAttributeToDelete + "'");
}
catch (Exception ex)
{
Global.WriteOutput("Error: Could not delete calculated attribute formulas: " + ex.Message);
}
}
Global.SecurityOperations.RemoveAction(new RoadCareAction("ATTRIBUTE", m_strAttributeToDelete));
}
private void LoadAttribute()
{
ConnectionParameters cp = DBMgr.GetAttributeConnectionObject(m_strAttribute);
if (m_bLinear)
{
listBoxField.Items.Add("ROUTES");
listBoxField.Items.Add("BEGIN_STATION");
listBoxField.Items.Add("END_STATION");
listBoxField.Items.Add("DIRECTION");
listBoxField.Items.Add("YEARS");
listBoxField.Items.Add("DATE_");
listBoxField.Items.Add(m_strAttribute);
}
else
{
listBoxField.Items.Add("FACILITY");
listBoxField.Items.Add("SECTION");
listBoxField.Items.Add("SAMPLE_");
listBoxField.Items.Add("YEARS");
listBoxField.Items.Add("DATE_");
listBoxField.Items.Add(m_strAttribute);
}
listBoxField.Text = m_strAttribute;
this.Text = "Query " + m_strAttribute;
String strQuery;
DataSet ds;
if (m_bLinear)
{
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strQuery = "SELECT DISTINCT DATA_ FROM " + m_strAttribute + " WHERE (ABS(CAST((BINARY_CHECKSUM(DATA_, NEWID())) as int))% 100) < 1 ORDER BY DATA_";
break;
case "ORACLE":
strQuery = "SELECT DISTINCT DATA_ FROM " + m_strAttribute + " SAMPLE(10) ORDER BY DATA_";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for LoadAttribute()");
//break;
}
}
else
{
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strQuery = "SELECT DISTINCT DATA_ FROM " + m_strAttribute + " WHERE (ABS(CAST((BINARY_CHECKSUM(DATA_, NEWID())) as int))% 100) < 1 ORDER BY DATA_";
break;
case "ORACLE":
strQuery = "SELECT DISTINCT DATA_ FROM " + m_strAttribute + " SAMPLE(10) ORDER BY DATA_";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for LoadAttribute()");
//break;
}
}
ds = DBMgr.ExecuteQuery(strQuery, cp);
foreach (DataRow row in ds.Tables[0].Rows)
{
listBoxValue.Items.Add(row.ItemArray[0].ToString());
}
hashDataSet.Add(m_strAttribute, ds);
if (m_bLinear)
{
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strQuery = "SELECT DISTINCT year(DATE_) AS YEARS FROM " + m_strAttribute + " WHERE ROUTES<>'' ORDER BY YEARS";
break;
case "ORACLE":
strQuery = "SELECT DISTINCT TO_CHAR(\"DATE_\",'YYYY') AS YEARS FROM " + m_strAttribute.Replace("[", "").Replace("]", "") + " WHERE ROUTES LIKE '_%' ORDER BY YEARS";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for LoadAttribute()");
//break;
}
}
else
{
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strQuery = "SELECT DISTINCT year(DATE_) AS YEARS FROM " + m_strAttribute + " WHERE FACILITY<>'' ORDER BY YEARS";
break;
case "ORACLE":
strQuery = "SELECT DISTINCT TO_CHAR(\"DATE_\",'YYYY') AS YEARS FROM " + m_strAttribute.Replace("[", "").Replace("]", "") + " WHERE FACILITY LIKE '_%' ORDER BY YEARS";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for LoadAttribute()");
//break;
}
}
ds = DBMgr.ExecuteQuery(strQuery, cp);
hashDataSet.Add("YEARS", ds);
hashDataSet.Add("DATE_", new DataSet());
if (m_bLinear)
{
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strQuery = "SELECT DISTINCT ROUTES FROM " + m_strAttribute + " WHERE ROUTES<>'' ORDER BY ROUTES";
break;
case "ORACLE":
strQuery = "SELECT DISTINCT ROUTES FROM " + m_strAttribute.Replace("[", "").Replace("]", "") + " WHERE ROUTES LIKE '_%' ORDER BY ROUTES";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
ds = DBMgr.ExecuteQuery(strQuery, cp);
hashDataSet.Add("ROUTES", ds);
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strQuery = "SELECT DISTINCT DIRECTION FROM " + m_strAttribute + " WHERE ROUTES<>'' ORDER BY DIRECTION";
break;
case "ORACLE":
strQuery = "SELECT DISTINCT DIRECTION FROM " + m_strAttribute.Replace("[", "").Replace("]", "") + " WHERE ROUTES LIKE '_%' ORDER BY DIRECTION";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
ds = DBMgr.ExecuteQuery(strQuery, cp);
hashDataSet.Add("DIRECTION", ds);
hashDataSet.Add("BEGIN_STATION", new DataSet());
hashDataSet.Add("END_STATION", new DataSet());
}
else
{
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strQuery = "SELECT DISTINCT FACILITY FROM " + m_strAttribute + " WHERE FACILITY<>'' ORDER BY FACILITY";
break;
case "ORACLE":
strQuery = "SELECT DISTINCT FACILITY FROM " + m_strAttribute.Replace("[", "").Replace("]", "") + " WHERE FACILITY LIKE '_%' ORDER BY FACILITY";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
ds = DBMgr.ExecuteQuery(strQuery, cp);
hashDataSet.Add("FACILITY", ds);
hashDataSet.Add("SECTION", new DataSet());
hashDataSet.Add("SAMPLE_", new DataSet());
}
}
private void FillListBoxes(string strAttribute)
{
hashDataSet.Clear();
listBoxField.Items.Clear();
listBoxValue.Items.Clear();
listBoxField.Items.Add("ROUTES");
listBoxField.Items.Add("BEGIN_STATION");
listBoxField.Items.Add("END_STATION");
listBoxField.Items.Add("DIRECTION");
listBoxField.Items.Add("DATE_");
listBoxField.Items.Add("YEARS");
listBoxField.Items.Add("[" + strAttribute + "]");
ConnectionParameters cp = DBMgr.GetAttributeConnectionObject(strAttribute);
String strQuery;
switch (cp.Provider)
{
case "MSSQL":
strQuery = "SELECT COUNT(*) FROM " + strAttribute + " WHERE ROUTES<> ''";
break;
case "ORACLE":
strQuery = "SELECT COUNT(*) FROM " + strAttribute + " WHERE ROUTES LIKE '_%'";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
try
{
DataSet ds = DBMgr.ExecuteQuery(strQuery, cp);
int nCount = 0;
if (ds.Tables[0].Rows.Count > 0)
{
nCount = int.Parse(ds.Tables[0].Rows[0].ItemArray[0].ToString());
}
int nMode = 1;
while (nCount / nMode > 100)
{
nMode = nMode * 2;
}
switch (cp.Provider)
{
case "MSSQL":
strQuery = "SELECT DISTINCT DATA_ FROM " + strAttribute + " WHERE ID_%" + nMode.ToString() + "='0' AND ROUTES<>'' ORDER BY DATA_";
break;
case "ORACLE":
strQuery = "SELECT DISTINCT DATA_ FROM " + strAttribute + " WHERE MOD(ID_," + nMode.ToString() + ")='0' AND ROUTES LIKE '_%' ORDER BY DATA_";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for FillListBoxes()");
//break;
}
ds = DBMgr.ExecuteQuery(strQuery, cp);
foreach (DataRow row in ds.Tables[0].Rows)
{
listBoxValue.Items.Add(row.ItemArray[0].ToString());
}
hashDataSet.Add(strAttribute, ds);
switch (cp.Provider)
{
case "MSSQL":
strQuery = "SELECT DISTINCT year(DATE_) AS YEARS FROM " + strAttribute + " WHERE ROUTES<>'' ORDER BY YEARS";
break;
case "ORACLE":
strQuery = "SELECT DISTINCT TO_CHAR(DATE_,'YYYY') AS YEARS FROM " + strAttribute + " WHERE ROUTES LIKE '_%' ORDER BY YEARS";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for FillListBoxes()");
//break;
}
ds = DBMgr.ExecuteQuery(strQuery, cp);
hashDataSet.Add("YEARS", ds);
hashDataSet.Add("DATE_", new DataSet());
switch (cp.Provider)
{
case "MSSQL":
strQuery = "SELECT DISTINCT ROUTES FROM " + strAttribute + " WHERE ROUTES<>'' ORDER BY ROUTES";
break;
case "ORACLE":
strQuery = "SELECT DISTINCT ROUTES FROM " + strAttribute + " WHERE ROUTES LIKE '_%' ORDER BY ROUTES";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
ds = DBMgr.ExecuteQuery(strQuery, cp);
hashDataSet.Add("ROUTES", ds);
switch (cp.Provider)
{
case "MSSQL":
strQuery = "SELECT DISTINCT DIRECTION FROM " + strAttribute + " WHERE ROUTES<>'' ORDER BY DIRECTION";
break;
case "ORACLE":
strQuery = "SELECT DISTINCT DIRECTION FROM " + strAttribute + " WHERE ROUTES LIKE '_%' ORDER BY DIRECTION";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
ds = DBMgr.ExecuteQuery(strQuery, cp);
hashDataSet.Add("DIRECTION", ds);
hashDataSet.Add("BEGIN_STATION", new DataSet());
hashDataSet.Add("END_STATION", new DataSet());
}
catch (Exception exc)
{
Global.WriteOutput("Error: A problem occured while trying to fill the attribute list box. " + exc.Message);
}
}
private bool CheckQuery()
{
String strWhere = textBoxSearch.Text.ToString();
String strAny = strWhere.ToUpper();
String strTable = "";
String strQuery;
bool bGoodQuery = false;
bool bAny = false;
if (strAny.Contains("ANYRECORD") || strAny.Contains("ANYCHANGE") || strAny.Contains("ANYYEAR"))
{
bAny = true;
}
m_strAttribute = "[" + comboBoxAttribute.Text + "]";
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strWhere = strWhere.Replace(m_strAttribute, " DATA_ ");
strTable = m_strAttribute.Replace("[", " ");
strTable = strTable.Replace("]", " ");
break;
case "ORACLE":
strWhere = strWhere.Replace(m_strAttribute, " \"DATA_\" ");
strTable = m_strAttribute.Replace("[", "");
strTable = strTable.Replace("]", "");
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
ConnectionParameters cp = DBMgr.GetAttributeConnectionObject(strTable);
if (bAny)
{
switch (cp.Provider)
{
case "MSSQL":
strQuery = "SELECT COUNT(*) FROM " + strTable + " WHERE ROUTES<>'' OR ROUTES IS NOT NULL";
break;
case "ORACLE":
//if our goal is to select all non-null, non-empty strings in the routes column
//we should be using AND and not OR here (and in the other cases too...)
//the empty string ('') fails "LIKE '_%' (and "<>''") but passes "IS NOT NULL"
//that means that empty strings pass with an OR
//I'm leaving it as is to keep consistent functionality for the time being, but when
//these debugging checks are removed (and the contents moved to functions),
//this should be examined.
strQuery = "SELECT COUNT(*) FROM " + strTable + " WHERE ROUTES LIKE '_%' OR ROUTES IS NOT NULL";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
}
else
{
switch (cp.Provider)
{
case "MSSQL":
strQuery = "SELECT COUNT(*) FROM " + strTable + " WHERE (ROUTES<>'' OR ROUTES IS NOT NULL) AND (" + strWhere + ")";
break;
case "ORACLE":
//if our goal is to select all non-null, non-empty strings in the routes column
//we should be using AND and not OR here (and in the other cases too...)
//the empty string ('') fails "LIKE '_%' (and "<>''") but passes "IS NOT NULL"
//that means that empty strings pass with an OR
//I'm leaving it as is to keep consistent functionality for the time being, but when
//these debugging checks are removed (and the contents moved to functions),
//this should be examined.
strQuery = "SELECT COUNT(*) FROM " + strTable + " WHERE (ROUTES LIKE '_%' OR ROUTES IS NOT NULL) AND (" + strWhere + ")";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
}
try
{
strTable = strTable.Trim();
switch (cp.Provider)
{
case "MSSQL":
strWhere = strWhere.Replace("YEARS", " year(DATE_) ");
break;
case "ORACLE":
strWhere = strWhere.Replace("YEARS", " TO_CHAR(\"DATE_\",'YYYY') ");
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for FillListBoxes()");
//break;
}
DataSet ds = DBMgr.ExecuteQuery(strQuery, cp);
if (ds.Tables.Count == 1)
{
String strOut = ds.Tables[0].Rows[0].ItemArray[0].ToString();
strOut += " entries returned.";
labelResult.Text = strOut;
labelResult.Visible = true;
bGoodQuery = true;
}
}
catch (Exception sqlE)
{
string[] str = sqlE.ToString().Split('\n');
String strError = str[0].Replace("DATA", m_strAttribute);
MessageBox.Show(strError);
}
//catch (Exception ex)
//{
// Global.WriteOutput("Error attempting to check query: " + ex.Message);
//}
return(bGoodQuery);
}
private void FormAttributeDocument_Load(object sender, EventArgs e)
{
SecureForm();
// If the attribute is on a data server, then we need to check if the view created was a LRS or SRS based.
// It is possible to have both, in which case we need not change the current (LRS) radio button.
m_cp = DBMgr.GetAttributeConnectionObject(m_strAttribute);
m_strLinearYearFilter = "";
m_strLinearRouteFilter = "";
m_strSectionYearFilter = "";
m_strSectionRouteFilter = "";
this.TabText = m_strAttribute;
this.Text = m_strAttribute;
if (m_cp == null)
{
Global.WriteOutput("Error:Could not connect to datasource.");
return;
}
if (!m_cp.IsNative)
{
checkAllowEdit.CheckState = CheckState.Unchecked;
checkAllowEdit.Enabled = false;
dgvAttribute.ReadOnly = true;
}
List <String> attributeColumnHeaders = DBMgr.GetTableColumns(m_strAttribute, m_cp);
if (m_cp.IsNative == false)
{
if (attributeColumnHeaders.Contains("SECTION"))
{
if (!attributeColumnHeaders.Contains("ROUTES"))
{
// Disable LRS
rbLinearRef.Checked = false;
rbSectionRef.Checked = true;
}
}
}
try
{
int iNumRecords;
iNumRecords = DBMgr.GetTableCount(m_strAttribute, m_cp);
LoadAttributeFilters();
// Check to see if the number of records in the database is greater than 10k.
// If it is, we dont want to display that many records on the screen unless we have to,
// so we will default to the first ROUTE in the combo box as a filter.
if (iNumRecords > 10000)
{
// Make sure there is more than just the "All" item in the combo box.
// If there isnt, then there isnt much we can do about filtering the data anyway so...
if (cbRoutes.Items.Count > 1)
{
cbRoutes.Text = cbRoutes.Items[1].ToString();
}
}
}
catch (Exception exc)
{
Global.WriteOutput("Error: Couldn't get record count for attribute " + m_strAttribute + ". " + exc.Message);
}
CreateDataGridView();
// Check for a native attribute in m_cp, then execute the proper execute query.
String strSelect = "SELECT TYPE_ FROM ATTRIBUTES_ WHERE ATTRIBUTE_='" + m_strAttribute + "'";
DataSet ds = null;
ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows[0].ItemArray[0].ToString() != "STRING")
{
m_bString = false;
}
// Set default LRS/SRS radio button
// TODO: Should be remembered from previous attributes. Perhaps, placing a "global" variable in the DBManager
// is a fantastic solution to this problem. OR we could create a static class called GLOBAL that would hold
// global variables for us. <---This is the answer.
tsbDeleteAll.ToolTipText = "Query based delete.";
}