C# SQLiteCommand.ExecuteReader方法代码示例

 /** public void createUc()  - create user controls (display reminders)         * 
  */
 public void createUc() 
 {
     int i = 0;
     UserControl1[] uc = new UserControl1[200];
     SQLiteConnection new_con = new SQLiteConnection(customwindow.Properties.Settings.Default.ConnectionString.ToString());
     new_con.Open();
     SQLiteCommand get = new SQLiteCommand("SELECT * FROM  reminder LIMIT 0 , 30", new_con);
     String header;
     SQLiteDataReader reader;
     reader = get.ExecuteReader();
     while (reader.Read())
     {
         //data[i,0] = new ArrayList();
         string[] str = new string[3];
         str[0]=reader[0].ToString();
         str[1]=DateTime.Parse(reader[1].ToString()).ToShortDateString();
         str[2]=DateTime.Parse(reader[5].ToString()).ToShortTimeString();
         reminderList.Add(str);
         header = String.Format("{1,-20}   {0,5}", reader[2].ToString(), DateTime.Parse(reader[1].ToString()).ToShortDateString());
         uc[i] = new UserControl1();
         uc[i].setContent(Convert.ToInt16(reader[0].ToString()), reader[3].ToString(), reader[4].ToString(), DateTime.Parse(reader[5].ToString()).ToShortTimeString() , header);
         WrapPanel1.Children.Add(uc[i]);
         i++;   
     }
     reminderlistarray = reminderList.ToArray();
 }

        public List<Drop> GetDrops(int mobId)
        {
            // create a new SQL command:
            sqlite_cmd = sqlite_conn.CreateCommand();

            // But how do we read something out of our table ?
            // First lets build a SQL-Query again:
            sqlite_cmd.CommandText = "SELECT * FROM droplist where mobId = " + mobId;

            // Now the SQLiteCommand object can give us a DataReader-Object:
            sqlite_datareader = sqlite_cmd.ExecuteReader();

            List<Drop> droplist = new List<Drop>(10);

            // The SQLiteDataReader allows us to run through the result lines:
            while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
            {
                int itemId = Convert.ToInt32(sqlite_datareader["itemId"]);
                int min = Convert.ToInt32(sqlite_datareader["min"]);
                int max = Convert.ToInt32(sqlite_datareader["max"]);
                int category = Convert.ToInt32(sqlite_datareader["category"]);
                int chance = Convert.ToInt32(sqlite_datareader["chance"]);

                droplist.Add(new Drop(mobId, itemId, min, max, category, chance));
            }
            return droplist;
        }

 public bool GetSchool()
 {
     bool bReturn = false;
     sqlite_cmd = new SQLiteCommand("Select * from ProgramConfig where id = 1", sqlite_conn);
     // Now the SQLiteCommand object can give us a DataReader-Object:
     sqlite_datareader = sqlite_cmd.ExecuteReader();
     // The SQLiteDataReader allows us to run through the result lines:
     while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
     {
         Console.WriteLine(sqlite_datareader["sLatitude"].ToString());
         school = new School(sqlite_datareader["sPassword"].ToString(), sqlite_datareader["sEmail"].ToString(), sqlite_datareader["sSchoolName"].ToString(),
             sqlite_datareader["sAddress"].ToString(), sqlite_datareader["sCity"].ToString(), sqlite_datareader["sState"].ToString(), sqlite_datareader["sZip"].ToString(),
             sqlite_datareader["sLatitude"].ToString(), sqlite_datareader["sLongitude"].ToString(), sqlite_datareader["sImageFile"].ToString());
         try
         {
             string sExistingDatabaseVersion = sqlite_datareader["sDatabaseVersion"].ToString();
             if(OldDBVersion(sExistingDatabaseVersion,DatabaseVersion))
             {
                 UpgradeDB(sExistingDatabaseVersion, DatabaseVersion);
             }
         }
         catch (Exception e)
         {
             Console.WriteLine(e.ToString());
             Console.WriteLine("No database version available.  Need to upgrade to DB Version 1.0 after backing things up.");
         }
         bReturn = true;
     }
     return bReturn;
 }

        void LoadUserSelect()
        {
            comboUser.Items.Clear();
            SQLiteCommand sqlite_cmd = new SQLiteCommand("SELECT * FROM People", sqlite_conn);

            // Now the SQLiteCommand object can give us a DataReader-Object:
            sqlite_datareader = sqlite_cmd.ExecuteReader();

            // The SQLiteDataReader allows us to run through the result lines:
            while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
            {
                //Console.WriteLine(String.Format("{0}, {1}", sqlite_datareader["stxtLname"], sqlite_datareader["stxtFname"]));
                comboUser.Items.Add(String.Format("{0}: {1}, {2}", sqlite_datareader["id"], sqlite_datareader["stxtLname"], sqlite_datareader["stxtFname"]));
            }
        }

 /// <summary>
 /// return last channel connected saved in database
 /// </summary>
 /// <returns></returns>
 public string GetLastChannelUsedDataBase()
 {
     string temp = string.Empty;
     string file = AppDomain.CurrentDomain.BaseDirectory +
                 "Database\\Shamia.db";
     if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
     Connection = new SQLiteConnection("Data Source=" + file + ";Version=3" +
                                       ";New=False;Compress=True");
     try
     {
         Connection.Open();
         C = Connection.CreateCommand();
         C.CommandText = @"SELECT channel FROM users TOP1";
         DataReader = C.ExecuteReader();
         while (DataReader.Read())
         {
             temp = DataReader["channel"].ToString();
         }
     }
     catch (Exception ex)
     {
         MyDelegates.OnDebugMessageCallBack(ex.ToString());
     }
     finally
     {
         if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
     }
     return temp;
 }

        /// <summary>
        /// list top 1 user => used for Shamia connect
        /// </summary>
        /// <returns></returns>
        public List<TemplateUserDataBase> EnumUserDataBase()
        {
            string file = AppDomain.CurrentDomain.BaseDirectory +
                         "Database\\Shamia.db";
            if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
            Connection = new SQLiteConnection("Data Source=" + file + ";Version=3" +
                                              ";New=False;Compress=True");
            List<TemplateUserDataBase> t = new List<TemplateUserDataBase>();
            try
            {
                Connection.Open();
                C = Connection.CreateCommand();
                C.CommandText = @"SELECT *FROM users TOP1";
                DataReader = C.ExecuteReader();
                while (DataReader.Read())
                {
                   TemplateUserDataBase temp = new TemplateUserDataBase()
                   {
                       Nick = DataReader["nick"].ToString(),
                       Password = DataReader["password"].ToString(),
                       Auth = DataReader["auth"].ToString(),
                       Owner = DataReader["owner"].ToString(),
                       Port = Convert.ToInt32(DataReader["port"].ToString())

                   };
                    t.Add(temp);
                }
            }
            catch (Exception ex)
            {
                MyDelegates.OnDebugMessageCallBack(ex.ToString());
            }
            finally
            {
                if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
            }
            return t;
        }

        public string BuildTotalAttendenceReport()
        {
            string rString = String.Format("Name and number of classes attended.\r\n");
            SQLiteCommand sqlite_cmd = new SQLiteCommand("SELECT PersonID, COUNT(*) from Attendence GROUP BY PersonID ORDER BY PersonID", sqlite_conn);
            try
            {
                sqlite_cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            SQLiteDataReader sqlite_datareader = sqlite_cmd.ExecuteReader();

            // The SQLiteDataReader allows us to run through the result lines:
            while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
            {
                rString += String.Format("{0}, {1}\r\n", GetUserName(Int32.Parse(String.Format("{0}", sqlite_datareader[0])), 3), sqlite_datareader[1]);
                //comboGuardians.Items.Add(String.Format("{0}: {1} {2}, {3}", sqlite_datareader2["id"], sqlite_datareader2["stxtFname"], sqlite_datareader2["stxtLname"], sqlite_datareader3["sRelationship"]));
            }
            return rString;
        }

 /** private void MenuViewContacts_Click(object sender, System.Windows.RoutedEventArgs e)
  *  view Contacts
  */
 public void createUserControlContact()
 {
     int i = 0;
     UserControlExpanderContact[] uc = new UserControlExpanderContact[200];
     SQLiteConnection new_con = new SQLiteConnection(customwindow.Properties.Settings.Default.ConnectionString.ToString());
     new_con.Open();
     SQLiteCommand  get = new SQLiteCommand("SELECT * FROM  contact_detail ORDER BY firstname ASC LIMIT 0 , 30", new_con);
     SQLiteDataReader reader;
     reader = get.ExecuteReader();
     String header;
     while (reader.Read())
     {
         header = reader[1].ToString() + "\t\t\t" + reader[2].ToString();
         uc[i] = new UserControlExpanderContact();
         uc[i].setContent(Convert.ToInt32(reader[0].ToString()), reader[2].ToString(), reader[3].ToString(), reader[5].ToString(), reader[4].ToString(), header);
         WrapPanel1.Children.Add(uc[i]);
         i++;
     }
     new_con.Close();
 }

        /// <summary>
        /// metodo cria ou acessa banco de dados sqlite carregar configs
        /// </summary>
        public void CreateOrAccessDataBase()
        {
            try
            {
                string file = AppDomain.CurrentDomain.BaseDirectory +
                              "Database\\Shamia.db";
                if (!ExistDataBase())
                {
                    Connection = new SQLiteConnection("Data Source=" + file + ";Version=3;New=True;Compress=True");
                    Connection.Open();
                    string[] config = new string[] {string.Empty, string.Empty, string.Empty};
                    // create table(s)
                    // config   => contais configurations
                    config[0] = @"create table config (port interger(4)" +
                                @",server varchar(30) primary key" +
                                @",language varchar(20))";
                    // channels => contais channels
                    config[1] = @"create table channels (channel varchar(30) primary key)";
                    // user(s)  => my login accont user(s) used login auth SSL
                    config[2] = @"create table users (nick varchar(20) primary key" +
                                @",password varchar(50))";

                    foreach (string i in config)
                    {
                        SQLiteCommand c = new SQLiteCommand(i, Connection);
                        c.ExecuteNonQuery();
                    }
                    IsDatabase = true;
                }
                else
                {
                    Connection = new SQLiteConnection("Data Source=" + file + ";Version=3" +
                                                      ";New=False;Compress=True");
                    Connection.Open();
                    IsDatabase = true;

                    // select database to list all channels and servers
                    C = Connection.CreateCommand();
                    C.CommandText = @"SELECT channel FROM channels";
                    DataReader = C.ExecuteReader();
                    while (DataReader.Read())
                    {
                        MainWindow.Configuration.Channels.Add(new TemplateChannels
                        {
                            Channels = DataReader["channel"].ToString()
                        });
                    }
                }
            }
            catch (Exception ex)
            {
                MyDelegates.OnDebugMessageCallBack(ex.ToString());
                IsDatabase = false;
            }
            finally
            {
                if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
            }
        }

        /// <summary>
        /// retorna enum games in database
        /// </summary>
        /// <returns></returns>
        public List<Games> EnumGamesSaved()
        {
            List<Games> t = new List<Games>();
            string file = AppDomain.CurrentDomain.BaseDirectory +
                     "Database\\Shamia.db";
            if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
            Connection = new SQLiteConnection("Data Source=" + file + ";Version=3" +
                                              ";New=False;Compress=True");

            try
            {
                Connection.Open();
                C = Connection.CreateCommand();
                C.CommandText = @"select * from games";
                DataReader = C.ExecuteReader();
                

                while (DataReader.Read())
                {
                    if (DataReader["IsEnabled"].ToString() == @"true")
                    {
                        Enabled = true;
                    }
                    else
                    {
                        Enabled = false;
                    }
                    Games g = new Games()
                    {
                        Name = DataReader["Name"].ToString(),
                        //DisplayMessageCount = Convert.ToInt32(DataReader["DisplayMessaCount"].ToString()),
                        IsEnabled = Enabled
                    };
                    t.Add(g);
                }
            }
            catch (Exception ex)
            {
                MyDelegates.OnDebugMessageCallBack(ex.StackTrace);
            }
            finally
            {
                if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
            }
            return t;
        }

        // afficher tout les voiture

        public List<Voiture> getLseVoitures()
        {
            sqlite_conn.Open();
            sqlite_cmd = sqlite_conn.CreateCommand();
            List<Voiture> voitures = new List<Voiture>();
            string req = "select * from Automobile where AutoMoto='True';";

            // Lets insert something into our new table:
            sqlite_cmd.CommandText = req;
            sqlite_datareader = sqlite_cmd.ExecuteReader();
            while (sqlite_datareader.Read())
            {
                Voiture v = new Voiture();
                v.Annee = Int32.Parse(sqlite_datareader["Annee"].ToString());
                v.Immatriculation = sqlite_datareader["Immatriculation"].ToString();
                v.Coulour = sqlite_datareader["Coulour"].ToString();
                v.Marque = sqlite_datareader["Marque"].ToString();
                v.TypeV = sqlite_datareader["TypeV"].ToString();
                voitures.Add(v);
            }
            sqlite_conn.Close();
                return voitures;

        }

        public Moto getUnMoto(string immatriculation)
        {
            Moto m = new Moto();
            try {
            sqlite_conn.Open();
            sqlite_cmd = sqlite_conn.CreateCommand();
              string req = "select * from Automobile where Immatriculation='" + immatriculation + "' and AutoMoto='False';";

            // Lets insert something into our new table:
            sqlite_cmd.CommandText = req;
            sqlite_datareader = sqlite_cmd.ExecuteReader();
            while (sqlite_datareader.Read())
            {
             
                m.Annee = Int32.Parse(sqlite_datareader["Annee"].ToString());
                m.Immatriculation = sqlite_datareader["Immatriculation"].ToString();
                m.Cylindre = Int32.Parse(sqlite_datareader["Cylindre"].ToString());
                m.VitesseMax = Int32.Parse(sqlite_datareader["VitesseMax"].ToString());

                
            }
            sqlite_conn.Close();
  }
            catch (Exception)
            {
                Console.WriteLine("Erreur dans la requette");
            }

       
            return m;
        }

        public Voiture getUneVoiture(string immatriculation)
        {
            Voiture v = new Voiture();

            try
            {
                sqlite_conn.Open();
                sqlite_cmd = sqlite_conn.CreateCommand();
                string req = "select * from Automobile where Immatriculation='" + immatriculation + "' and AutoMoto='True';";


                sqlite_cmd.CommandText = req;
                sqlite_datareader = sqlite_cmd.ExecuteReader();
                while (sqlite_datareader.Read())
                {
                    
                    v.Annee = Int32.Parse(sqlite_datareader["Annee"].ToString());
                    v.Immatriculation = sqlite_datareader["Immatriculation"].ToString();
                    v.Coulour = sqlite_datareader["Coulour"].ToString();
                    v.Marque = sqlite_datareader["Marque"].ToString();
                    v.TypeV = sqlite_datareader["TypeV"].ToString();

                }
                sqlite_conn.Close();
            }
            catch (Exception)
            {
                Console.WriteLine("Erreur dans la requette");
            }

            return v;
        }

        //afficher les motos
        public List<Moto> getLesMotos()
        {
            List<Moto> motos = new List<Moto>();


            sqlite_conn.Open();
            sqlite_cmd = sqlite_conn.CreateCommand();
            string req = "select * from Automobile where AutoMoto='False';";

            // Lets insert something into our new table:
            sqlite_cmd.CommandText = req;
            sqlite_datareader = sqlite_cmd.ExecuteReader();
            while (sqlite_datareader.Read())
            {
                Moto v = new Moto();
                v.Annee = Int32.Parse(sqlite_datareader["Annee"].ToString());
                v.Immatriculation = sqlite_datareader["Immatriculation"].ToString();
                v.Cylindre = Int32.Parse(sqlite_datareader["Cylindre"].ToString());
                v.VitesseMax = Int32.Parse(sqlite_datareader["VitesseMax"].ToString());
           
                motos.Add(v);
            }
            sqlite_conn.Close();
            return motos;

        }

        public string BuildEODAttendenceReport(int iDayOffSet)
        {
            string rString = String.Format("Name and number of classes attended for {0}.\r\n", DateTime.Today.AddDays(iDayOffSet).ToShortDateString());
            SQLiteCommand sqlite_cmd = new SQLiteCommand("SELECT PersonID, COUNT(*) from Attendence Where Date = @Date GROUP BY PersonID ORDER BY PersonID",sqlite_conn);
            sqlite_cmd.Parameters.Add("@Date", SqlDbType.DateTime).Value = DateTime.Now.AddDays(iDayOffSet).ToLongDateString();
            try
            {
                sqlite_cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            SQLiteDataReader sqlite_datareader = sqlite_cmd.ExecuteReader();

            // The SQLiteDataReader allows us to run through the result lines:
            while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
            {
                rString += String.Format("{0}, {1}\r\n", GetUserName(Int32.Parse(String.Format("{0}",sqlite_datareader[0])),3), sqlite_datareader[1]);
                //comboGuardians.Items.Add(String.Format("{0}: {1} {2}, {3}", sqlite_datareader2["id"], sqlite_datareader2["stxtFname"], sqlite_datareader2["stxtLname"], sqlite_datareader3["sRelationship"]));
            }
            return rString;
        }