public static List<AvgSpeedReportItem> GetReportData() { string query = @"WITH RankedLink AS ( SELECT ROW_NUMBER() OVER (ORDER BY Link, SegEndC) [Number], * FROM dbo.Olaine_LINK_EVAL), MiddleLink AS ( SELECT AVG(Number) [MiddleLink] FROM RankedLink GROUP BY Link), AvgSpeed AS ( SELECT AVG(v__0_) [speed], Link FROM dbo.Olaine_LINK_EVAL GROUP BY Link) SELECT RankedLink.Link, 0 [Lane], (SegStX + SegEndX)/2 [center.x], (SegStY + SegEndY)/2 [center.y], AvgSpeed.speed FROM RankedLink JOIN MiddleLink ON RankedLink.Number=MiddleLink.MiddleLink JOIN AvgSpeed ON RankedLink.Link=AvgSpeed.Link"; var list = new List<AvgSpeedReportItem>(); try { var sb = new OleDbConnectionStringBuilder(vissim.Instance.Evaluation.Wrap().GetConnectionString()); if (sb.ContainsKey("Password")) { using (var conn = new OleDbConnection(sb.ConnectionString)) { conn.Open(); try { using (var reader = new OleDbCommand(query, conn).ExecuteReader()) { while (reader.Read()) { list.Add(new AvgSpeedReportItem() { Link = reader.GetInt32(0), Lane = reader.GetInt32(1), Center = new Point(reader.GetDouble(2), reader.GetDouble(3)), AvgSpeed = reader.GetDouble(4) }); } } } finally { conn.Close(); } } } } catch { } return list; }
void caricaDaDb() { string sconn; sconn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db.mdb"; OleDbConnection conn = new OleDbConnection(sconn); try { conn.Open(); N_NODI = getint32("SELECT valore FROM parametri WHERE parametro='n' ", 0, conn); settaNnodi(N_NODI); m = getint32("SELECT valore FROM parametri WHERE parametro='m' ", 0, conn); //leggo nodi OleDbDataReader myreader = new OleDbCommand("select x,y,nome from nodi", conn).ExecuteReader(); for (int i = 0; i < N_NODI; i++) { nodi[i].x = nodi[i].y = 0; archi[i] = new ArrayList(); d[i] = new ArrayList(); } while (myreader.Read()) { int x; int y; int nome; //int a, b, c; //myreader.Read(); x = myreader.GetInt32(0); y = myreader.GetInt32(1); nome = Convert.ToInt32(myreader.GetString(2)); nodi[nome].x = x; nodi[nome].y = y; nodi[nome].name = Convert.ToString(nome); archi[nome] = new ArrayList(); d[nome] = new ArrayList(); } //leggo archi myreader = new OleDbCommand("select x,y,dist from archi", conn).ExecuteReader(); //for (int j = 0; j < m; j++) // per ogni arco... while (myreader.Read()) { int from; int dest; int dist; //myreader.Read(); from = myreader.GetInt32(0); dest = myreader.GetInt32(1); dist = myreader.GetInt32(2); //aggiungo arco if (archi[from] == null) archi[from] = new ArrayList(); if (d[from] == null) d[from] = new ArrayList(); archi[from].Add(dest); d[from].Add(dist); } DrawGraph(); conn.Close(); } catch (OleDbException e) { MessageBox.Show("Errore DB" + e.Message); } }
int getint32(String query, int num_colonn, OleDbConnection conn) { OleDbDataReader myreader = new OleDbCommand(query, conn).ExecuteReader(); myreader.Read(); return myreader.GetInt32(num_colonn); }