public void SetAssetProperties(PropertyGridEx.PropertyGridEx pgProperties, bool bIsModal)
{
pgProperties.Item.Clear();
try
{
DataSet columnNamesAndTypes = DBMgr.GetTableColumnsWithTypes("ASSETS");
foreach (DataRow columnNameAndType in columnNamesAndTypes.Tables[0].Rows)
{
m_columnNames.Add(columnNameAndType["column_name"].ToString());
pgProperties.Item.Add(columnNameAndType["column_name"].ToString(), "", true, "General", "", true);
}
}
catch (Exception exc)
{
Global.WriteOutput("Error: Could not get ASSETS table column names or types. " + exc.Message);
}
string query = "SELECT ASSET, DATE_CREATED, CREATOR_NAME, CREATOR_ID, LAST_MODIFIED, SERVER, DATASOURCE, USERID, PASSWORD_, PROVIDER, NATIVE_ FROM ASSETS WHERE ASSET = '" + m_AssetName + "'";
try
{
DataSet assetTableInfo = DBMgr.ExecuteQuery(query);
pgProperties.Item["ASSET"].Value = assetTableInfo.Tables[0].Rows[0]["ASSET"].ToString();
pgProperties.Item["DATE_CREATED"].Value = assetTableInfo.Tables[0].Rows[0]["DATE_CREATED"].ToString();
pgProperties.Item["CREATOR_NAME"].Value = assetTableInfo.Tables[0].Rows[0]["CREATOR_NAME"].ToString();
pgProperties.Item["CREATOR_ID"].Value = assetTableInfo.Tables[0].Rows[0]["CREATOR_ID"].ToString();
pgProperties.Item["LAST_MODIFIED"].Value = assetTableInfo.Tables[0].Rows[0]["LAST_MODIFIED"].ToString();
pgProperties.Item["SERVER"].Value = assetTableInfo.Tables[0].Rows[0]["SERVER"].ToString();
pgProperties.Item["DATASOURCE"].Value = assetTableInfo.Tables[0].Rows[0]["DATASOURCE"].ToString();
pgProperties.Item["USERID"].Value = assetTableInfo.Tables[0].Rows[0]["USERID"].ToString();
pgProperties.Item["PASSWORD_"].Value = assetTableInfo.Tables[0].Rows[0]["PASSWORD_"].ToString();
pgProperties.Item["PROVIDER"].Value = assetTableInfo.Tables[0].Rows[0]["PROVIDER"].ToString();
pgProperties.Item["NATIVE_"].Value = assetTableInfo.Tables[0].Rows[0]["NATIVE_"].ToString();
}
catch (Exception exc)
{
Global.WriteOutput("Error: Can not get asset information from ASSETS. " + exc.Message);
}
try
{
ConnectionParameters cp = DBMgr.GetAssetConnectionObject(m_AssetName);
DataSet columnNamesAndTypes = DBMgr.GetTableColumnsWithTypes(m_AssetName, cp);
foreach (DataRow columnNameAndType in columnNamesAndTypes.Tables[0].Rows)
{
m_columnNames.Add(columnNameAndType["column_name"].ToString());
pgProperties.Item.Add(columnNameAndType["column_name"].ToString(), "", true, "General", "", true);
pgProperties.Item[columnNameAndType["column_name"].ToString()].Value = columnNameAndType["data_type"].ToString();
}
}
catch (Exception exc)
{
Global.WriteOutput("Error: Could not get asset column names or types. " + exc.Message);
}
pgProperties.Refresh();
}
private void AssetMapQuery(MouseEventArgs e)
{
PointF ptF = new PointF((float)e.X, (float)e.Y);
SharpMap.Geometries.Point pt = MainMapImage.Map.ImageToWorld(ptF);
BoundingBox bbox = pt.GetBoundingBox().Grow(MainMapImage.Map.PixelSize * 10);
ConnectionParameters cp;
String strLayerName = MainMapImage.Map.Layers[0].LayerName;
if (strLayerName.Contains("("))
{
strLayerName = strLayerName.Substring(0, strLayerName.IndexOf("("));
}
cp = DBMgr.GetAssetConnectionObject(strLayerName);
String strSelect = "SELECT * FROM ASSETS WHERE ASSET = '" + strLayerName + "'";
DataSet ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count > 0)
{
// The results of this query are fairly small, so we can use ADO to store the data in a table for use later.
strSelect = "SELECT * FROM " + strLayerName + " WHERE ";
strSelect += bbox.Left.ToString() + " < EnvelopeMinX AND EnvelopeMaxX < " + bbox.Right.ToString() + " AND EnvelopeMinY < " + bbox.Top.ToString() + " AND EnvelopeMaxY > " + bbox.Bottom.ToString();
ds = DBMgr.ExecuteQuery(strSelect, cp);
ds.Tables[0].TableName = strLayerName;
AssetTab assetTab;
if (FormManager.IsAssetTabOpen(out assetTab))
{
AttributeTab attributeTab;
if (FormManager.IsAttributeTabOpen(out attributeTab))
{
attributeTab.Hide();
}
if (assetTab.IsHidden)
{
assetTab.Show();
}
assetTab.UpdateAssetData(ds.Tables[0]);
}
//FormAssetViewer formAssetViewer;
//if (FormManager.IsFormAssetViewerOpen(out formAssetViewer))
//{
// formAssetViewer.EnableMapQuery("tabPageASSET", -1);
// formAssetViewer.UpdateAssetData(ds.Tables[0]);
//}
}
}
public void LoadDataGridFromAssetView(String geoID, String assetType)
{
dgvAssetSelection.Rows.Clear();
String query = "SELECT * FROM " + assetType + " WHERE GEO_ID = " + geoID;
try
{
ConnectionParameters cp = DBMgr.GetAssetConnectionObject(assetType);
DataSet assetInfo = DBMgr.ExecuteQuery(query, cp);
DataRow assetDataRow = assetInfo.Tables[0].Rows[0];
assetInfo.Tables[0].TableName = assetType;
UpdateAssetData(assetInfo.Tables[0]);
}
catch (Exception exc)
{
Global.WriteOutput("Error: Could not get asset information from " + assetType + " table. " + exc.Message);
}
}
private void UpdateAssetFieldValues()
{
listBoxValues.Items.Clear();
String attribute = treeViewAttribute.SelectedNode.Text;
String strSelect = "SELECT DISTINCT " + attribute + " FROM " + m_strAsset;
ConnectionParameters cp = DBMgr.GetAssetConnectionObject(m_strAsset);
try
{
DataReader dr = new DataReader(strSelect, cp);
while (dr.Read())
{
listBoxValues.Items.Add(dr["attribute"].ToString());
}
dr.Close();
}
catch (Exception exception)
{
Global.WriteOutput("Error: Connecting to ASSET = " + attribute + "." + exception.Message);
}
}
public void Delete()
{
// Completely remove an asset and all of its references from the RoadCare database and UI.
ConnectionParameters cp = DBMgr.GetAssetConnectionObject(m_assetToDelete);
List <string> transactions = new List <string>();
if (cp.IsNative)
{
string dropAssetTable = "DROP TABLE " + m_assetToDelete;
transactions.Add(dropAssetTable);
dropAssetTable = "DROP TABLE " + m_assetToDelete + "_CHANGELOG";
transactions.Add(dropAssetTable);
}
else
{
string dropAssetView = "DROP VIEW " + m_assetToDelete;
transactions.Add(dropAssetView);
}
DBMgr.ExecuteBatchNonQuery(transactions, cp);
string deleteAsset = "DELETE FROM ASSETS WHERE ASSET = '" + m_assetToDelete + "'";
DBMgr.ExecuteNonQuery(deleteAsset);
}
private void EditConnectionPropertyGrid()
{
pgProperties.Item["Asset Name"].IsReadOnly = true;
// Get all the columns from the database for this asset that are not
// and load them into the property grid if they are not Database Information columns
ConnectionParameters cp = DBMgr.GetAssetConnectionObject(m_assetName);
DataSet assetColumnsAndTypes = DBMgr.GetTableColumnsWithTypes(m_assetName, cp);
foreach (DataRow columnAndType in assetColumnsAndTypes.Tables[0].Rows)
{
if (pgProperties.Item[columnAndType["column_name"].ToString()] == null)
{
pgProperties.Item.Add(columnAndType["column_name"].ToString(), columnAndType["data_type"].ToString(), true, "Asset Properties", "", true);
if (!cbRemoveProperty.Items.Contains(columnAndType["column_name"].ToString()))
{
cbRemoveProperty.Items.Add(columnAndType["column_name"].ToString());
}
}
}
// Remove ID and entry date fields
if (pgProperties.Item["ID"] != null)
{
pgProperties.Item.Remove("ID");
}
if (pgProperties.Item["GEO_ID"] != null)
{
pgProperties.Item.Remove("GEO_ID");
}
if (pgProperties.Item["ENTRY_DATE"] != null)
{
pgProperties.Item.Remove("ENTRY_DATE");
}
pgProperties.Refresh();
}
private void FormAssetFilter_Load(object sender, EventArgs e)
{
m_AssetTypes = DBOp.GetRawAssetNames();
m_AttributeTypes = DBOp.GetAttributeNames();
List <String> years;
foreach (String assetType in m_AssetTypes)
{
ConnectionParameters cp = DBMgr.GetAssetConnectionObject(assetType);
TreeNode tnAssetType = tvFilterCriteria.Nodes.Add(assetType);
tnAssetType.Tag = "ASSET";
List <String> assetAttributes = DBOp.GetAssetAttributes(assetType, cp);
foreach (String assetAttribute in assetAttributes)
{
tnAssetType.Nodes.Add(assetAttribute);
}
}
foreach (String attributeType in m_AttributeTypes)
{
TreeNode tnRoadAttribute = tvFilterCriteria.Nodes.Add(attributeType);
tnRoadAttribute.Tag = "ATTRIBUTE";
try
{
years = Global.GetAttributeYears(attributeType, m_attributeYears);
foreach (String year in years)
{
tnRoadAttribute.Nodes.Add(year);
}
tnRoadAttribute.Nodes.Add(attributeType);
}
catch // (Exception exc)
{
//Global.WriteOutput("Error: Problem encountered while filling road attribute node. Check for valid attribute/asset rollup. " + exc.Message);
}
}
}
public void DoAssetRollup()
{
// Loop through each asset and fill the ASSET_SECTION_<network_id> table according to the rollup logic.
AssetRollupMessaging.AddMessage("Begin asset rollup in network: " + m_networkID + " at " + DateTime.Now.ToString("HH:mm:ss"));
String query = "";
StreamWriter tw = null;
if (DBOp.IsTableInDatabase("ASSET_SECTION_" + m_networkID))
{
// Drop the table as we are going to make a new one.
try
{
DBMgr.ExecuteNonQuery("DROP TABLE ASSET_SECTION_" + m_networkID);
}
catch (Exception exc)
{
throw exc;
}
}
// Creating the ASSET_SECTION_<networkID> table.
AssetRollupMessaging.AddMessage("Creating ASSET_SECTION table...");
List <DatabaseManager.TableParameters> listColumn = new List <DatabaseManager.TableParameters>();
listColumn.Add(new DatabaseManager.TableParameters("GEO_ID", DataType.Int, false, false));
listColumn.Add(new DatabaseManager.TableParameters("SECTIONID", DataType.Int, false, false));
listColumn.Add(new DatabaseManager.TableParameters("ASSET_TYPE", DataType.VarChar(-1), false));
listColumn.Add(new DatabaseManager.TableParameters("FACILITY", DataType.VarChar(-1), false));
listColumn.Add(new DatabaseManager.TableParameters("BEGIN_STATION", DataType.Float, true));
listColumn.Add(new DatabaseManager.TableParameters("END_STATION", DataType.Float, true));
listColumn.Add(new DatabaseManager.TableParameters("DIRECTION", DataType.VarChar(50), true));
listColumn.Add(new DatabaseManager.TableParameters("SECTION", DataType.VarChar(-1), true));
listColumn.Add(new DatabaseManager.TableParameters("AREA", DataType.Float, true));
listColumn.Add(new DatabaseManager.TableParameters("UNITS", DataType.VarChar(50), true));
String strTable = "ASSET_SECTION_" + m_networkID;
try
{
DBMgr.CreateTable(strTable, listColumn);
}
catch (Exception exc)
{
throw exc;
}
// Get a text writer and file ready to do a bulk copy.
String strMyDocumentsFolder = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
strMyDocumentsFolder += "\\RoadCare Projects\\Temp";
Directory.CreateDirectory(strMyDocumentsFolder);
// LRS, Get the LRS data from each asset table...
foreach (String assetName in m_assetNames)
{
AssetRollupMessaging.AddMessage("Rolling up LRS based asset " + assetName + "...");
ConnectionParameters cp = DBMgr.GetAssetConnectionObject(assetName);
List <String> assetColumnNames = DBMgr.GetTableColumns(assetName, cp);
if (assetColumnNames.Contains("BEGIN_STATION"))
{
switch (cp.Provider)
{
case "MSSQL":
query = "SELECT GEO_ID, FACILITY, DIRECTION, BEGIN_STATION, END_STATION FROM " + assetName + " WHERE (FACILITY <> '' AND FACILITY IS NOT NULL) ORDER BY FACILITY, DIRECTION, BEGIN_STATION";
break;
case "ORACLE":
query = "SELECT GEO_ID, FACILITY, DIRECTION, BEGIN_STATION, END_STATION FROM " + assetName + " WHERE (FACILITY LIKE '_%' AND FACILITY IS NOT NULL) ORDER BY FACILITY, DIRECTION, BEGIN_STATION";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
String strOutFile = strMyDocumentsFolder + "\\" + assetName + ".txt";
tw = new StreamWriter(strOutFile);
DataSet sectionSet;
DataReader assetReader;
try
{
// Get the Segmented network data from the SECTION_<networkID> table.
// sectionSet will hold the section data, and assetReader will loop through each asset.
sectionSet = DBMgr.ExecuteQuery("SELECT SECTIONID, FACILITY, BEGIN_STATION, END_STATION, DIRECTION FROM SECTION_" + m_networkID + " WHERE BEGIN_STATION IS NOT NULL ORDER BY FACILITY, DIRECTION, BEGIN_STATION");
assetReader = new DataReader(query, cp);
}
catch (Exception exc)
{
throw exc;
}
// If there is data to read, start reading it.
if (assetReader.Read())
{
DataPoint assetInfo;
DataRow sectionRow;
DataPoint sectionInfo;
bool bMoreData = true;
int iCurrentSection = 0;
// bMoreData is true while there is more data to read, and false when the dataReader is finished.
// we then go back to the foreach loop (outside the while) and start rolling up the next asset.
while (bMoreData)
{
// AssetInfo is going to hold this particular row of asset data.
assetInfo = new DataPoint(-1, (int)assetReader["GEO_ID"], assetReader["FACILITY"].ToString(), assetReader["BEGIN_STATION"].ToString(), assetReader["END_STATION"].ToString(), assetReader["DIRECTION"].ToString());
// SectionInfo is going to hold this particular row of sections data.
sectionRow = sectionSet.Tables[0].Rows[iCurrentSection];
sectionInfo = new DataPoint((int)sectionRow["SECTIONID"], sectionRow["FACILITY"].ToString(), sectionRow["BEGIN_STATION"].ToString(), sectionRow["END_STATION"].ToString(), sectionRow["DIRECTION"].ToString());
// We increment the section if
// We increment the asset if
// AssetInSection returns:
// -1 increments asset
// 0 adds asset to Asset Rollup Table
// 1 increments section
bool bIncrementSection = false;
bool bIncrementAsset = false;
int assetSectionComparison = AssetInSection(assetInfo, sectionInfo);
// Based on the result from AssetInSection we are going to increment something. Here its the asset
if (assetSectionComparison < 0)
{
bIncrementAsset = true;
}
// Here, we have a match and we need to look ahead to see how many sections a linear asset might belong to
// before moving on to the next asset. In either case, point or linear, we add the asset to the Rollup table.
else if (assetSectionComparison == 0)
{
AddAssetToRollupTable(assetInfo, sectionInfo, assetName, tw);
if (assetInfo.m_ptsExtent.Y != -1) //don't bother with looking ahead if we're using point assets
{
// Keep looping through the sections and checking to see if this asset is still valid for each
// consecutive section. When it fails on a section, we are done with the linear asset, otherwise
// we add the asset to the new section. (This is why we needed the sections in a DataSet, as a
// dataReader would not allow this type of operation...easily).
for (int iSectionLookAhead = 1; iSectionLookAhead + iCurrentSection < sectionSet.Tables[0].Rows.Count; iSectionLookAhead++)
{
sectionRow = sectionSet.Tables[0].Rows[iCurrentSection + iSectionLookAhead];
sectionInfo = new DataPoint((int)sectionRow["SECTIONID"], sectionRow["FACILITY"].ToString(), sectionRow["BEGIN_STATION"].ToString(), sectionRow["END_STATION"].ToString(), sectionRow["DIRECTION"].ToString());
if (AssetInSection(assetInfo, sectionInfo) == 0)
{
AddAssetToRollupTable(assetInfo, sectionInfo, assetName, tw);
}
else
{
break;
}
}
}
// Point asset match...we assigned the section already so just tell the loop to move to the next asset.
bIncrementAsset = true;
}
// AssetInSection returned non-zero, and was not negative. Which is a long way of saying, it returned positive.
// so we need to increment the section on a positive result.
else
{
bIncrementSection = true;
}
if (bIncrementAsset)
{
if (bIncrementSection)
{
// This can't happen logically, but was useful during debugging.
throw new Exception();
}
else
{
// Read in the new data if we are incrementing the asset
bMoreData = assetReader.Read();
}
}
else
{
// Increment the section row in the section data set. (Assuming there are sections remaining)
// If there arent any sections remaining, then we can't assign any more assets can we?
// so that means we are done.
if (bIncrementSection)
{
if (iCurrentSection + 1 < sectionSet.Tables[0].Rows.Count)
{
iCurrentSection++;
bMoreData = true;
}
else
{
bMoreData = false;
}
}
else
{
// Again, impossible, but useful for debugging.
throw new Exception();
}
}
}
}
tw.Close();
assetReader.Close();
AssetRollupMessaging.AddMessage("Bulk loading rolled up LRS asset data...");
// Now try to load all that beautifully segmented data into an Asset Rollup table. (tab delimited).
try
{
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
DBMgr.SQLBulkLoad("ASSET_SECTION_" + m_networkID, strOutFile, '\t');
break;
case "ORACLE":
throw new NotImplementedException("TODO: Figure out tables for DoAssetRollup()");
//DBMgr.OracleBulkLoad( DBMgr.NativeConnectionParameters, "ASSET_SECTION_" + m_networkID, strOutFile,
//break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
}
catch (Exception exc)
{
throw exc;
}
}
}
AssetRollupMessaging.AddMessage("Finished LRS asset data rollup...");
//foreach (String assetName in m_assetNames)
//{
// AssetRollupMessaging.AddMessge("Rolling up SRS asset " + assetName + "...");
// ConnectionParameters cp = DBMgr.GetAssetConnectionObject(assetName);
// List<String> assetColumnNames = DBMgr.GetTableColumns(assetName, cp);
// if (assetColumnNames.Contains("SECTION"))
// {
// query = "SELECT GEO_ID, FACILITY, SECTION FROM " + assetName + " WHERE (SECTION <> '' AND SECTION IS NOT NULL) ORDER BY FACILITY, SECTION";
// String strOutFile = strMyDocumentsFolder + "\\" + assetName + ".txt";
// tw = new StreamWriter(strOutFile);
// DataReader sectionReader = null;
// DataReader assetReader = null;
// try
// {
// // Get the Segmented network data from the SECTION_<networkID> table.
// // sectionSet will hold the section data, and assetReader will loop through each asset.
// //sectionSet = DBMgr.ExecuteQuery("SELECT SECTIONID, FACILITY, SECTION SECTION_" + m_networkID + " WHERE SECTION IS NOT NULL ORDER BY FACILITY, SECTION");
// sectionReader = new DataReader("SELECT SECTIONID, FACILITY, SECTION FROM SECTION_" + m_networkID + " WHERE SECTION IS NOT NULL ORDER BY FACILITY, SECTION");
// assetReader = new DataReader(query, cp);
// }
// catch (Exception exc)
// {
// throw exc;
// }
// bool bContinue = true;
// String strFacility = "";
// String strSection = "";
// String strSectionID = "";
// String strAssetFacility = "";
// String strAssetSection = "";
// String strGeoID = "";
// while (bContinue)
// {
// if (strFacility == "")
// {
// if (!sectionReader.Read())
// {
// bContinue = false;
// continue;
// }
// strFacility = sectionReader["FACILITY"].ToString();
// strSection = sectionReader["SECTION"].ToString();
// strSectionID = sectionReader["SECTIONID"].ToString();
// //if (strSectionID == "1006136")
// //{ }
// //strFacility = strFacility.Replace(" ", "");
// //strSection = strSection.Replace(" ", "");
// }
// if (strAssetFacility == "")
// {
// if (!assetReader.Read())
// {
// bContinue = false;
// continue;
// }
// strAssetFacility = assetReader["FACILITY"].ToString();
// strAssetSection = assetReader["SECTION"].ToString();
// strGeoID = assetReader["GEO_ID"].ToString();
// //if (strAssetFacility == "NW - Connecticut Ave")
// //{ }
// //strAssetFacility = strAssetFacility.Replace(" ", "");
// //strAssetSection = strAssetSection.Replace(" ", "");
// }
// if (CompareInfo.GetCompareInfo("en-US").Compare(strFacility, strAssetFacility) < 0)
// {
// strFacility = "";
// }
// else if (CompareInfo.GetCompareInfo("en-US").Compare(strFacility, strAssetFacility) == 0)
// {
// if (CompareInfo.GetCompareInfo("en-US").Compare(strSection, strAssetSection) < 0)
// {
// strFacility = "";
// }
// else if (CompareInfo.GetCompareInfo("en-US").Compare(strSection, strAssetSection) == 0)
// {
// //Write out to file
// tw.WriteLine(strGeoID
// + "\t" + strSectionID
// + "\t" + assetName
// + "\t" + sectionReader["FACILITY"].ToString()
// + "\t" //+ a.m_ptsExtent.X.ToString()
// + "\t" //+ ((a.m_ptsExtent.Y == -1) ? "" : a.m_ptsExtent.Y.ToString())
// + "\t" //+ a.m_strDirection
// + "\t" + sectionReader["SECTION"].ToString()
// + "\t" //+ a.m_strArea
// + "\t"); //+ a.m_strUnit);
// strAssetFacility = "";
// }
// else
// {
// strAssetFacility = "";
// }
// }
// else
// {
// strAssetFacility = "";
// }
// }
// tw.Close();
// assetReader.Close();
// sectionReader.Close();
// AssetRollupMessaging.AddMessge("Bulk loading rolled up SRS asset data...");
// // Now try to load all that beautifully segmented data into an Asset Rollup table. (tab delimited).
// try
// {
// switch (cp.Provider)
// {
// case "MSSQL":
// //query = "SELECT GEO_ID, FACILITY, SECTION FROM " + assetName + " WHERE (SECTION <> '' AND SECTION IS NOT NULL) ORDER BY FACILITY, SECTION";
// DBMgr.SQLBulkLoad("ASSET_SECTION_" + m_networkID, strOutFile, '\t');
// break;
// case "ORACLE":
// query = "SELECT GEO_ID, FACILITY, SECTION FROM " + assetName + " WHERE (SECTION LIKE '_%' AND SECTION IS NOT NULL) ORDER BY FACILITY, SECTION";
// break;
// default:
// throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
// break;
// }
// }
// catch (Exception exc)
// {
// throw exc;
// }
// }
//}
AssetRollupMessaging.AddMessage("Asset Rollup complete.");
}
public FormAssets(String strAsset)
{
InitializeComponent();
m_strAsset = strAsset;
m_assetCP = DBMgr.GetAssetConnectionObject(m_strAsset);
}
public void LoadDataGridView()
{
String assetType = cbAssetTypes.Text;
String query = "";
String geoID;
if (assetType != "")
{
ConnectionParameters cp = DBMgr.GetAssetConnectionObject(assetType);
dgvAssetsByType.Rows.Clear();
dgvAssetsByType.Columns.Clear();
List <String> assetAttributes = DBOp.GetAssetAttributes(assetType, cp);
DataGridViewColumn dgvColumnToAdd;
DataGridViewCell dgvCellTemplate = new DataGridViewTextBoxCell();
//need to add GEO_ID or the columns don't match when we do the
//SELECT * below
dgvColumnToAdd = new DataGridViewColumn(dgvCellTemplate);
dgvColumnToAdd.Name = "GEO_ID";
dgvColumnToAdd.HeaderText = "GEO_ID";
dgvAssetsByType.Columns.Add(dgvColumnToAdd);
foreach (String assetAttribute in assetAttributes)
{
dgvColumnToAdd = new DataGridViewColumn(dgvCellTemplate);
dgvColumnToAdd.Name = assetAttribute;
dgvColumnToAdd.HeaderText = assetAttribute;
dgvAssetsByType.Columns.Add(dgvColumnToAdd);
}
List <string> facilities = new List <string>();
if (m_formAssetView.AssetSectionInfo != null)
{
foreach (DataRow assetSectionRow in m_formAssetView.AssetSectionInfo.Tables[0].Rows)
{
string facility = assetSectionRow["FACILITY"].ToString();
if (!facilities.Contains(facility))
{
facilities.Add(facility);
}
}
query = "SELECT * FROM " + assetType + " WHERE (FACILITY = '";
string whereFacilities = "";
foreach (string facility in facilities)
{
whereFacilities += facility + "' OR FACILITY = '";
}
if (whereFacilities != "")
{
whereFacilities = whereFacilities.Substring(0, whereFacilities.Length - 17);
query += whereFacilities + "') AND (GEO_ID = ";
}
String whereClause = "";
if (m_formAssetView.AssetSectionInfo != null)
{
foreach (DataRow assetSectionRow in m_formAssetView.AssetSectionInfo.Tables[0].Rows)
{
geoID = assetSectionRow["GEO_ID"].ToString();
whereClause += geoID + " OR GEO_ID = ";
}
}
if (whereClause != "")
{
whereClause = whereClause.Substring(0, whereClause.Length - 13);
query += whereClause + ")";
try
{
DataSet assetInfo = DBMgr.ExecuteQuery(query, cp);
foreach (DataRow assetInfoRow in assetInfo.Tables[0].Rows)
{
dgvAssetsByType.Rows.Add(assetInfoRow.ItemArray);
}
}
catch (Exception exc)
{
Global.WriteOutput("Error: Could not query asset table " + assetType + ". " + exc.Message);
}
}
}
}
}