C# SQLiteDataReader.Read方法代码示例

        public List<UserInfo> ReadUserInfo()
        {
            // Create a list for the user info.
            List<UserInfo> listUserInfo = new List<UserInfo>();

            // Open the database.
            dbConnection.Open();

            // Retrieve all records from the table called "mailaddresses".
            dbCommand.CommandText = "SELECT * FROM mailaddresses;";

            // Execute the newly created command.
            dbQuery = dbCommand.ExecuteReader();

            // Read the retrieved query, and write the results to the newly created list.
            while (dbQuery.Read())
                listUserInfo.Add(new UserInfo
                {
                    userMail = dbQuery["address"].ToString(),
                    password = dbQuery["password"].ToString(),
                    autoLogin = dbQuery["autologin"].ToString()
                });

            // Close the query-reader again.
            dbQuery.Close();

            // Close the database again.
            dbConnection.Close();

            // Return the created list.
            return listUserInfo;
        }

        public void selectData(List<String> where1, List<String> where2, String tableName, String[] orderBy)
        {
            command.CommandText = "SELECT * from ";
            command.CommandText += tableName;
            if (where1.Count > 0)
            {
                command.CommandText += " WHERE";
                for (int i = 0; i < where1.Count; i++)
                {
                    command.CommandText += " " + where1.ElementAt(i) + "=" + where2.ElementAt(i) + " AND";
                }
                command.CommandText = command.CommandText.Substring(0, command.CommandText.Length - 3);
                where1.RemoveRange(0, where1.Count);
                where2.RemoveRange(0, where2.Count);
            }
            command.CommandText += " ORDER BY ";
            command.CommandText += orderBy;
            dataReader = command.ExecuteReader();

            while (dataReader.Read())
            {
                Console.WriteLine("ID           : " + DB_PrintToConsole(dataReader, 0) + " ");
                Console.WriteLine("Source Site  : " + DB_PrintToConsole(dataReader, 1) + " ");
                Console.WriteLine("Source ID    : " + DB_PrintToConsole(dataReader, 2) + " ");
                Console.WriteLine("Circle Name  : " + DB_PrintToConsole(dataReader, 3) + " ");
                Console.WriteLine("Date         : " + DB_PrintToConsole(dataReader, 4) + " ");
                Console.WriteLine("Title        : " + DB_PrintToConsole(dataReader, 5) + " ");
                Console.WriteLine("Title Altern : " + DB_PrintToConsole(dataReader, 6) + " ");
                Console.WriteLine("Language     : " + DB_PrintToConsole(dataReader, 7) + " ");
                Console.WriteLine("Data Type    : " + DB_PrintToConsole(dataReader, 8));
                Console.WriteLine();
            }
        }

        public List<Tuple<string, string, string, string, string>> GetAllBooks()
        {
            var books = new List<Tuple<string, string, string, string, string>>();

            m_dbConnection = new SQLiteConnection("Data Source=" + db + ";Version=3;");
            m_dbConnection.Open();

            string sql = "SELECT * FROM books";

            command = new SQLiteCommand(sql, m_dbConnection);
            reader = command.ExecuteReader();

            string id = "";
            string title = "";
            string auteur = "";
            string picture = "";
            string synopsis = "";
            while (reader.Read())
            {
                id = reader["id"].ToString();
                title = reader["title"].ToString();
                auteur = reader["auteur"].ToString();
                picture = reader["picture"].ToString();
                synopsis = reader["synopsis"].ToString();
                books.Add(new Tuple<string, string, string, string, string>(id, title, auteur, picture, synopsis));
            }

            reader.Close();
            m_dbConnection.Close();

            return books;
        }

        public static Statistic GetUserStatistic(int userId)
        {
            Statistic statistic;
            connection.Open();
            command = new SQLiteCommand("SELECT * FROM 'Statistic' WHERE user_id = @param1;", connection);
            command.Parameters.Add(new SQLiteParameter("@param1", userId));
            reader = command.ExecuteReader();

            List<ExerciseResultInfo> results = new List<ExerciseResultInfo>();
            ExerciseResultInfo resultInfo;
            while (reader.Read())
            {
                int level = Convert.ToInt32(reader["level"]);
                int exerciseId = Convert.ToInt32(reader["exercise_id"]);
                int assesment = Convert.ToInt32(reader["assesment"]);
                DateTime dateOfPassing = Convert.ToDateTime(reader["dateOfPass"]);
                int speed = Convert.ToInt32(reader["speed"]);
                int errors = Convert.ToInt32(reader["errors"]);
                resultInfo = new ExerciseResultInfo(exerciseId, dateOfPassing, errors, assesment, speed);
                results.Add(resultInfo);
            }

            statistic = new Statistic(userId, results);
            connection.Close();

            return statistic;
        }

        public Report()
        {
            InitializeComponent();

            // Connect to database file
            sql_con = new SQLiteConnection("Data Source=" + applicationPath + "\\ExpenseTracker.db;Version=3;New=False;Compress=True;");
            sql_cmd = new SQLiteCommand();
            sql_con.Open();
            sql_cmd.Connection = sql_con;
            sql_cmd.CommandText = "SELECT * FROM Month";
            sql_reader = sql_cmd.ExecuteReader();
            while (sql_reader.Read())
            {
                dataGridView.Rows.Add(
                    sql_reader.GetInt32(0),
                    CustomDate.GetThaiMonth(sql_reader.GetInt32(1)),
                    sql_reader.GetDecimal(2).ToString("#,#0.00#"),
                    sql_reader.GetDecimal(3).ToString("#,#0.00#"),
                    sql_reader.GetDecimal(4).ToString("#,#0.00#")
                );
            }
            sql_reader.Close();

            dataGridView.ClearSelection();
        }

        public string GetIconName(UInt16 RcpId)
        {
            string ret = "";
            try
            {
                _SQLiteConnection.Open();
                string qury = string.Format("select * from tb_recipe where ID={0}", RcpId);
                SQLiteCommand command = new SQLiteCommand(qury, _SQLiteConnection);
                _reader = command.ExecuteReader();
                while (_reader.Read())
                    ret = _reader["IconName"].ToString();
                Console.ReadLine();

            }
            catch (Exception e)
            {
                Console.WriteLine(e);

            }
            finally
            {
                _reader.Close();
                _SQLiteConnection.Close();

            }
            return ret;
        }

 internal void Sync(long rowid)
 {
     IsValid = false;
     _command.Parameters[0].Value = rowid;
     _reader = _command.ExecuteReader();
     _reader.Read();
 }

        public SendAlertForm()
        {
            InitializeComponent();

            // Open
            sqlite_conn.Open();

            // 要下任何命令先取得該連結的執行命令物件
            sqlite_cmd = sqlite_conn.CreateCommand();

            // 查詢Status表單，取告警代號、告警說明
            sqlite_cmd.CommandText = "SELECT * FROM Status";

            // 執行查詢Status塞入 sqlite_datareader
            sqlite_datareader = sqlite_cmd.ExecuteReader();

            RDSStsDescList = new List<string>();

            // 一筆一筆列出查詢的資料
            while (sqlite_datareader.Read())
            {
                // Print out the content of the RDSStsID field:
                RDSStsID = sqlite_datareader["RDSStsID"].ToString();
                RDSStsDesc = sqlite_datareader["RDSStsDesc"].ToString();
                cboWarn.Items.Add(RDSStsID);
                RDSStsDescList.Add(RDSStsDesc);

                //MessageBox.Show(s);
            }
            sqlite_datareader.Close();
            cboWarn.SelectedIndex = 0;
            //結束，關閉資料庫連線
            sqlite_conn.Close();
        }

        public static List<Course> generateAllRelatedPreRequisites(Course course)
        {
            List<Course> preRequisiteCourses = new List<Course>();
            String queryStatement = @"WITH RECURSIVE
              pre_req_courses(n) AS (
            VALUES('" + course.id + @"')
            UNION
            SELECT COURSEID FROM PreRequisite, pre_req_courses
             WHERE PreRequisite.FOLLOWID=pre_req_courses.n
              )
            SELECT COURSEID FROM PreRequisite
             WHERE PreRequisite.FOLLOWID IN pre_req_courses";

            try
            {
                dbConnection.Open();
                sqlCommand = new SQLiteCommand(queryStatement, dbConnection);
                dataReader = sqlCommand.ExecuteReader();

                while (dataReader.Read())
                {
                    String preReqCourseId = dataReader["COURSEID"].ToString();
                    String readPreReqCourse = "SELECT * FROM Course WHERE ID = '" + preReqCourseId + "'";

                    SQLiteCommand sqlCommandTemp = new SQLiteCommand(readPreReqCourse, dbConnection);
                    SQLiteDataReader dataReaderTemp = sqlCommandTemp.ExecuteReader();
                    Course preReq = null;
                    while (dataReaderTemp.Read())
                    {
                        preReq = new Course(dataReaderTemp["ID"].ToString(),
                                (int)dataReaderTemp["YR"],
                                (int)dataReaderTemp["SEM"],
                                dataReaderTemp["NAME"].ToString(),
                                dataReaderTemp["DESC"].ToString(),
                                (int)dataReaderTemp["POINTS"],
                                dataReaderTemp["ACADEMICORG"].ToString(),
                                dataReaderTemp["ACADEMICGROUP"].ToString(),
                                dataReaderTemp["COURSECOMP"].ToString(),
                                dataReaderTemp["GRADINGBASIS"].ToString(),
                                dataReaderTemp["TYPOFFERED"].ToString(),
                                dataReaderTemp["REMARKS"].ToString(),
                                dataReaderTemp["CAREERID"].ToString());
                    }
                    if (preReq != null)
                    {
                        Logger.Info("[DatabaseConnection::getPrerequisiteCourse()] PreRequisite Course Information:");
                        Logger.Info("[DatabaseConnection::getPrerequisiteCourse()]" + "\nID: " + preReq.id + "\nNAME: " + preReq.name + "\nDESC: " + preReq.description);
                        preRequisiteCourses.Add(preReq);
                    }
                }
                dbConnection.Close();
            }
            catch (Exception e)
            {
                Logger.Error("DatabaseConnection::generateAllRelatedPreRequisites() " + e.Message);
            }

            return preRequisiteCourses;
        }

 public int getIdByUsername(string _username)
 {
     this.clearCmd();
     cmd.CommandText = "Select id from client where username='" + _username + "';";
     reader = cmd.ExecuteReader();
     reader.Read();
     return reader.GetInt16(0);
 }

        public JsonTable(SQLiteDataReader results, string tableName)
        {
            tableData = new JArray();
            this.tableName = tableName;

            while (results.Read())
                tableData.Add(new JsonRow(results).toJsonObject());
        }

 private Guest[] HydrateGuests(SQLiteDataReader reader) {
     var guests = new List<Guest>();
     while (reader.Read()) {
         var guest = new Guest();
         guest.Name = reader.GetString(1);
         guest.ReservationDate = DateTime.Parse(reader.GetString(2));
         guest.Attending = bool.Parse(reader.GetString(3));
         guest.Underage = bool.Parse(reader.GetString(4));
         guests.Add(guest);
     }
     return guests.ToArray();
 }

        private void FrmAddGroup_Load(object sender, EventArgs e)
        {
            btnAdd.Enabled = false;
            lbError.ForeColor = Color.DarkRed;
            pnMain.BackColor = Color.White;

            cmdSearch = new SQLiteCommand("SELECT `group` FROM groups WHERE `id`[email protected]", Gib.con);
            cmdSearch.Parameters.AddWithValue("@id", Gib.curGroup);
            reader = cmdSearch.ExecuteReader();
            reader.Read();
            tbGroup.Text = reader["group"].ToString();
        }

        public string[] getLatestMsgLog()
        {
            this.clearCmd();
            List<string> msgLogList=new List<string>();
            int AllowedMsgAmount = (int)ServerConfigManager.MyConfigs["MaxPullMsgLogAmount"];
            cmd.CommandText = "Select * from msgLog ORDER BY sendTimestamp ASC LIMIT "+AllowedMsgAmount.ToString()+";";
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                msgLogList.Add( System.Uri.EscapeDataString(this.getUsernameById(reader.GetInt16(1)))+","+System.Uri.EscapeDataString(Convert.ToString(reader.GetDateTime(3)))+","+System.Uri.EscapeDataString(reader.GetString(2)));
            }

            return msgLogList.ToArray();
        }

 private List<Model.Task> GetTasks(SQLiteDataReader reader)
 {
     List<Model.Task> list = new List<Model.Task>();
     while (reader.Read())
     {
         var task = new Model.Task();
         task.ID = reader[0].ToString();
         task.Title = reader[1].ToString();
         task.Description = reader[2].ToString();
         task.CreationDate = DateTime.Parse(reader[3].ToString());
         list.Add(task);
     }
     return list;
 }