Пример #1
0
 static void Main(string[] args)
 {
     Console.WriteLine("Enter path to your xml file or nothing to take xml file from \"xml//AppleSection.xml\":");
     string pathxml = Console.ReadLine();
     Section section1;
     if (pathxml == "") section1 = new Section("xml//AppleSection.xml");
     else section1 = new Section(pathxml);
     Console.WriteLine(section1.path);
     XmlTextReader xtr = new XmlTextReader("xml//xmldb.xml");
     Table [] tb = new Table[3];
     string [] tablesName = {"Apple", "Dept", "Sale_date"};
     for (int i = 0; i!=3; i++)
     {
         while (xtr.Name != tablesName[i])
             xtr.Read();
         tb[i] = new Table(ref xtr);
     }
     Datatable dt = new Datatable(ref xtr);
     SelectQueries sql = new SelectQueries(dt, tb, section1);
     SQLiteConnection appleConnection = new SQLiteConnection(String.Format("Data Source={0}", section1.path));
     appleConnection.Open();
     SQLiteCommand appleCommand = new SQLiteCommand(appleConnection);
     Console.Write("{0, -20} |", ' ');
     for (int i = 0; i!=sql.idCol.Count; i++)
         Console.Write("{0, -20} |", sql.idCol[i]);
     Console.WriteLine();
     int i2 = 0;
     for (int j = 0; j != sql.idRow.Count; j++)
     {
         Console.Write("{0, -20} |", sql.idRow[j]);
         for (int i = 0; i != sql.idCol.Count; i++)
         {
             appleCommand.CommandText = sql.queries[i2];
             SQLiteDataReader dataReader = appleCommand.ExecuteReader();
             DataTable data = new DataTable();
             data.Load(dataReader);
             dataReader.Close();
             if (data.Rows.Count != 0) Console.Write("{0, -20} |", data.Rows[0][0]);
             else Console.Write("{0, -20} |", 0);
             i2++;
         }
         Console.WriteLine();
     }
     appleConnection.Close();
     Console.ReadKey();
 }
Пример #2
0
 public SelectQueries(Datatable dt, Table[] tables, Section sec)
 {
     queries = new List<string>();
     idCol = new List<string>();
     idRow = new List<string>();
     string amount = "";
     foreach (Fact s in dt.facts)
         if (s.name == "Amount") amount = s.nameinDB;
     StringBuilder query = new StringBuilder("Select " + dt.nameDT + "." + amount + " from " + dt.nameDT);
     for (int j = 0; j!=tables.Length; j++)
     {
         string fk = "";
         foreach (Fact t in dt.facts)
             if (t.link == tables[j].table) fk = t.nameinDB;
         query.AppendFormat(" inner join {0} on {1}.{2} = {3}.{4}", tables[j].tableDB, tables[j].tableDB, tables[j].pk, dt.nameDT, fk);
     }
     Table t1 = tables[0];
     foreach (Table t in tables)
         if (t.table == sec.fixedDim) t1 = t;
     Field f1 = t1.fields[0];
     foreach (Field f in t1.fields)
         if (f.name == sec.fixedField) f1 = f;
     query.AppendFormat(" where {0}.{1}={2} and ", t1.tableDB, f1.nameinDB, sec.fixedId[0]);
     Table dimCol = tables[0];
     foreach (Table t in tables)
         if (t.table == sec.dimByColumn) dimCol = t;
     Field vis = dimCol.fields[0];
     foreach (Field f in dimCol.fields)
         if (f.visible) vis = f;
     for (int colNum = 0; colNum != sec.selectId[0].Count; colNum++)
     {
         SQLiteConnection appleConnection = new SQLiteConnection(String.Format("Data Source={0}", sec.path));
         appleConnection.Open();
         SQLiteCommand appleCommand = new SQLiteCommand(appleConnection);
         appleCommand.CommandText = "select " + vis.nameinDB + " from " + dimCol.tableDB + " where " + dimCol.pk + "=" + sec.selectId[0][colNum];
         SQLiteDataReader dataReader = appleCommand.ExecuteReader();
         DataTable data = new DataTable();
         data.Load(dataReader);
         dataReader.Close();
         idCol.Add(data.Rows[0][0].ToString());
     }
     Table dimRow = tables[0];
     foreach (Table t in tables)
         if (t.table == sec.dimByRow) dimRow = t;
     vis = dimRow.fields[0];
     foreach (Field f in dimRow.fields)
         if (f.visible) vis = f;
     for (int rowNum = 0; rowNum != sec.selectId[1].Count; rowNum++)
     {
         SQLiteConnection appleConnection = new SQLiteConnection(String.Format("Data Source={0}", sec.path));
         appleConnection.Open();
         SQLiteCommand appleCommand = new SQLiteCommand(appleConnection);
         appleCommand.CommandText = "select " + vis.nameinDB + " from " + dimRow.tableDB + " where " + dimRow.pk + "=" + sec.selectId[1][rowNum];
         SQLiteDataReader dataReader = appleCommand.ExecuteReader();
         DataTable data = new DataTable();
         data.Load(dataReader);
         dataReader.Close();
         idRow.Add(data.Rows[0][0].ToString());
     }
     for (int rowNum = 0; rowNum != sec.selectId[1].Count; rowNum++)
     {
         for (int colNum = 0; colNum != sec.selectId[0].Count; colNum++)
         {
             StringBuilder resQuery = new StringBuilder(query.ToString());
             bool prov = true;
             foreach (Table t in tables)
             {
                 if (t.table != sec.fixedDim && prov)
                 {
                     resQuery.AppendFormat("{0}.{1}={2} and ", t.tableDB, t.pk, sec.selectId[0][colNum]);
                     prov = false;
                 }
                 else if (t.table != sec.fixedDim) resQuery.AppendFormat("{0}.{1}={2};", t.tableDB, t.pk, sec.selectId[1][rowNum]);
             }
             queries.Add(resQuery.ToString());
         }
     }
 }