//------------------------------------------------------------------
// Retrieve data values from database and put them in an array list
// of objects, and return it. This is used when executing a SELECT
// query
//------------------------------------------------------------------
public List <Object> getValues(string queryString)
{
List <Object> results = new List <Object>();
configureConnection();
using (conn)
{
System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(queryString, conn);
try
{
conn.Open();
System.Data.OleDb.OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Object[] nextRow = new Object[reader.FieldCount];
reader.GetValues(nextRow);
results.Add(nextRow);
}
return(results);
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
return(null);
}
}
}
private void updateSpeciesFromTreeTable()
{
fieldSpcd.Clear();
using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(dbLc))
{
con.Open();
string sql = "SELECT DISTINCT PLT_CN, SPCD FROM TREE WHERE DIA >= 5 and SPCD > 0";
if (lt5)
{
sql = "SELECT DISTINCT PLT_CN, SPCD FROM TREE WHERE SPCD > 0";
}
System.Data.OleDb.OleDbCommand oleCom = new System.Data.OleDb.OleDbCommand(sql, con);
System.Data.OleDb.OleDbDataReader oleRd = oleCom.ExecuteReader();
while (oleRd.Read())
{
object[] vls = new object[2];
oleRd.GetValues(vls);
string pcn = vls[0].ToString().ToUpper();
if (fieldPltCn.Contains(pcn))
{
string spcd = vls[1].ToString();
fieldSpcd.Add(spcd);
}
}
oleRd.Close();
if (seedlings)
{
sql = "SELECT DISTINCT PLT_CN, SPCD FROM SEEDLING";
oleCom.CommandText = sql;
oleRd = oleCom.ExecuteReader();
while (oleRd.Read())
{
object[] vls = new object[2];
oleRd.GetValues(vls);
string pcn = vls[0].ToString().ToUpper();
if (fieldPltCn.Contains(pcn))
{
string spcd = vls[1].ToString();
fieldSpcd.Add(spcd);
}
}
}
con.Close();
}
populateSpeciesList();
populateDataTable();
}
private void createAndFillRegenRef()
{
using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(dbLc))
{
con.Open();
//Console.WriteLine("opened the database");
string sql = "SELECT PLT_CN, SUBP, SPCD, TREECOUNT FROM SEEDLING";
System.Data.OleDb.OleDbCommand oleCom = new System.Data.OleDb.OleDbCommand(sql, con);
System.Data.OleDb.OleDbDataReader oleRd = oleCom.ExecuteReader();
while (oleRd.Read())
{
object[] vls = new object[4];
oleRd.GetValues(vls);
string pltCn = vls[0].ToString();
string subp = vls[1].ToString();
if (SubPlotField == "" || SubPlotField == null)
{
subp = "0";
}
string linkVl = pltCn + "_" + subp;
//Console.WriteLine("Link = " + linkVl.ToString());
if (unPlots.Contains(linkVl))
{
string spcd = vls[2].ToString();
string stcd = "1";
string grpCd = spcd;
if (groupstatuscode)
{
grpCd = spcd + "_" + stcd;
}
if (grp)
{
spcd = findGrp(grpCd);
}
rUnSp.Add(spcd);
int tCnt = System.Convert.ToInt32(vls[3]);
string lk = linkVl + "_" + spcd.ToString();
int vlOut;
if (rgDic.TryGetValue(lk, out vlOut))
{
rgDic[lk] = vlOut + tCnt;
}
else
{
rgDic.Add(lk, tCnt);
}
}
}
oleRd.Close();
con.Close();
}
}
static int SilentlyReadAllRows(System.Data.OleDb.OleDbDataReader reader)
{
int rowCount = 0;
while (reader.Read())
{
++rowCount;
object[] values = new object[reader.FieldCount];
reader.GetValues(values);
}
return(rowCount);
}
private void populateSpeciesDictionary()
{
int pltCnt = fieldPltCn.Count;
using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(dbLc))
{
con.Open();
string sql = "SELECT SPCD, COMMON_NAME FROM REF_SPECIES ORDER BY SPCD";
System.Data.OleDb.OleDbCommand oleCom = new System.Data.OleDb.OleDbCommand(sql, con);
System.Data.OleDb.OleDbDataReader oleRd = oleCom.ExecuteReader();
while (oleRd.Read())
{
object[] vls = new object[2];
oleRd.GetValues(vls);
string jVl = vls[0].ToString() + " | " + vls[1].ToString();
spDic.Add(vls[0].ToString(), vls[1].ToString());
//lstSpecies.Items.Add(jVl);
}
oleRd.Close();
con.Close();
}
}
private string[] getStatusCodes()
{
HashSet <string> statusCodeHash = new HashSet <string>();
using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(dbLc))
{
con.Open();
string sql = "SELECT DISTINCT STATUSCD FROM TREE";
System.Data.OleDb.OleDbCommand oleCom = new System.Data.OleDb.OleDbCommand(sql, con);
System.Data.OleDb.OleDbDataReader oleRd = oleCom.ExecuteReader();
while (oleRd.Read())
{
object[] vls = new object[1];
oleRd.GetValues(vls);
string spcd = vls[0].ToString();
statusCodeHash.Add(spcd);
}
oleRd.Close();
con.Close();
}
return(statusCodeHash.ToArray());
}
private void createAndFillTreesRef()
{
//DataTable dtTrees = null;
using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(dbLc))
{
con.Open();
//Console.WriteLine("opened the database");
string sql = "SELECT SPCD, JENKINS_TOTAL_B1, JENKINS_TOTAL_B2, JENKINS_STEM_WOOD_RATIO_B1, JENKINS_STEM_WOOD_RATIO_B2, JENKINS_STEM_BARK_RATIO_B1, JENKINS_STEM_BARK_RATIO_B2, JENKINS_FOLIAGE_RATIO_B1, JENKINS_FOLIAGE_RATIO_B2 FROM REF_SPECIES";
System.Data.OleDb.OleDbCommand oleCom = new System.Data.OleDb.OleDbCommand(sql, con);
System.Data.OleDb.OleDbDataReader oleRd = oleCom.ExecuteReader();
while (oleRd.Read())
{
object[] vls = new object[9];
oleRd.GetValues(vls);
bmDic.Add(vls[0].ToString(), vls);
}
oleRd.Close();
sql = "SELECT CN, PLT_CN, SUBP, TREE, SPCD, DIA, HT, STATUSCD FROM TREE WHERE DIA >= 0 and SPCD > 0";
oleCom.CommandText = sql;
oleRd = oleCom.ExecuteReader();
while (oleRd.Read())
{
object[] vls = new object[8];
oleRd.GetValues(vls);
updateObjectVls(vls);
string pltCn = vls[1].ToString();
string subp = vls[2].ToString();
if (SubPlotField == "" || SubPlotField == null)
{
subp = "0";
}
string linkVl = pltCn + "_" + subp;
//Console.WriteLine(linkVl);
if (unPlots.Contains(linkVl))
{
//Console.WriteLine(linkVl);
string spcd = vls[4].ToString();
//adding in live and dead
string stcd = vls[7].ToString();
string grpCd = spcd;
if (groupstatuscode)
{
grpCd = spcd + "_" + stcd;
}
object[] bmVls;
if (bmDic.TryGetValue(spcd, out bmVls))
{
if (grp)
{
spcd = findGrp(grpCd); //findGrp(spcd);
}
double diam = System.Convert.ToDouble(vls[5]); //diameter
Dictionary <string, object[]> cDic;
if (diam < 5)
{
//Console.WriteLine("Adding to less than 5");
cDic = sVlDic;
sUnSp.Add(spcd);
}
else
{
//Console.WriteLine("greater than 5");
cDic = vlDic;
unSp.Add(spcd);
}
string lk = linkVl + "_" + spcd.ToString();
object[] treeVls;
if (cDic.TryGetValue(lk, out treeVls))
{
}
else
{
treeVls = new object[12];
treeVls[0] = pltCn;
treeVls[1] = subp;
treeVls[2] = spcd;
treeVls[3] = 0; //dbh
treeVls[4] = 0; //AGB
treeVls[5] = 0; //StemAGB
treeVls[6] = 0; //ButtAGB
treeVls[7] = 0; //FoliageAGB
treeVls[8] = 0; //TopAGB
treeVls[9] = 0; //BAA
treeVls[10] = 0; //TPA
treeVls[11] = 0; //MeanHT
cDic.Add(lk, treeVls);
}
//Console.WriteLine(System.Convert.ToDouble(vls[5]).ToString());
double ht = System.Convert.ToDouble(vls[6]); //height
treeVls[3] = System.Convert.ToDouble(treeVls[3]) + diam;
double j_tbm = Math.Exp(System.Convert.ToDouble(bmVls[1]) + System.Convert.ToDouble(bmVls[2]) * Math.Log(diam * 2.54)) * 2.2046; //AGB
double j_sbm = j_tbm * Math.Exp(System.Convert.ToDouble(bmVls[3]) + System.Convert.ToDouble(bmVls[4]) / (2.54 * diam)); //SAGB
double j_bbm = j_tbm * Math.Exp(System.Convert.ToDouble(bmVls[5]) + System.Convert.ToDouble(bmVls[6]) / (2.54 * diam)); //BAGB
double j_fbm = j_tbm * Math.Exp(System.Convert.ToDouble(bmVls[7]) + System.Convert.ToDouble(bmVls[8]) / (2.54 * diam)); //FAGB
double j_tpbm = j_tbm - j_sbm - j_bbm - j_fbm; //TPAGB
treeVls[4] = System.Convert.ToDouble(treeVls[4]) + j_tbm;
treeVls[5] = System.Convert.ToDouble(treeVls[5]) + j_sbm;
treeVls[6] = System.Convert.ToDouble(treeVls[6]) + j_bbm;
treeVls[7] = System.Convert.ToDouble(treeVls[7]) + j_fbm;
treeVls[8] = System.Convert.ToDouble(treeVls[8]) + j_tpbm;
treeVls[9] = System.Convert.ToDouble(treeVls[9]) + 0.005454 * Math.Pow(diam, 2); //BA
treeVls[10] = System.Convert.ToDouble(treeVls[10]) + 1.0; //trees
treeVls[11] = System.Convert.ToDouble(treeVls[11]) + ht;
}
}
}
oleRd.Close();
con.Close();
}
}
