C# SQLite.SQLiteDataReader类代码示例

        public string printToConsole(SQLiteDataReader readerDB, int index)
        {
            if (readerDB.IsDBNull(index))
            {
                //return "NULL";
                return "";
            }
            else
            {
                String dataObject = readerDB.GetFieldType(index).ToString();
                switch (dataObject)
                {
                    case "System.Int32":
                        return readerDB.GetInt32(index).ToString();
                    case "System.DateTime":
                        DateTime date = readerDB.GetDateTime(index);
                        return Convert.ToString(date);
                    case "System.String":
                        return readerDB.GetString(index);
                    default:
                        return "Unknown";
                }
            }

        }

        public string ExecuteQuery(string sql_query, SQLiteParameter[] parameters, out SQLiteDataReader reader)
        {
            string res = string.Empty;

            reader = null;

            _connection.Open();
            _command = _connection.CreateCommand();
            _command.CommandText = sql_query;

            if (parameters != null)
            {
                _command.Parameters.Clear();
                _command.Parameters.AddRange(parameters);
            }

            try
            {
                reader = _command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                res = CreateExceptionMessage(ex);
            }

            return res;
        }

 /* Returns a Set using a SQLiteDataReader and a dictionary mapping the attributes to a index.
  */
 private Set SetExtractor(SQLiteDataReader reader, Dictionary<string, int> attributeIndexDict)
 {
     return new Set(reader.GetInt32(attributeIndexDict["day"]), reader.GetInt32(attributeIndexDict["month"]), 
             reader.GetInt32(attributeIndexDict["year"]), reader.GetInt32(attributeIndexDict["setSeqNum"]),
             reader.GetInt32(attributeIndexDict["activityId"]), reader.GetDouble(attributeIndexDict["weight"]),
             reader.GetInt32(attributeIndexDict["secs"]), reader.GetInt32(attributeIndexDict["reps"]));
 }

 /// <summary>Will add GSMs from db to the given Experiment</summary>
 /// <param name="experiment">Experiment to have its GSMs property updated</param>
 public static void AddExperimentGSMToExperimentFromDb(Experiment experiment, SQLiteDataReader reader)
 {
     ExperimentGSM gsm = new ExperimentGSM(reader["gsm_id"].ToString(), reader["gsm_value"].ToString());
     gsm.Id = Convert.ToInt32(reader["id"].ToString());
     gsm.ExperimentId = reader["experiment_id"].ToString();
     experiment.GSMs.Add(gsm);
 }

    internal DbChannel(SignalSource source, SQLiteDataReader r, IDictionary<string, int> field, 
      DataRoot dataRoot, IDictionary<string,bool> encryptionInfo)
    {
      this.SignalSource = source;
      this.RecordIndex = r.GetInt32(field["channel_handle"]);

      this.Bits = r.GetInt32(field["list_bits"]);
      bool isTv = (Bits & BITS_Tv) != 0;
      bool isRadio = (Bits & BITS_Radio) != 0;
      bool isAnalog = (source & SignalSource.Analog) != 0;
      if (isAnalog && !isTv)
      {
        this.IsDeleted = true;
        return;
      }

      if (isTv) this.SignalSource |= SignalSource.Tv;
      if (isRadio) this.SignalSource |= SignalSource.Radio;
      this.Lock = (Bits & BITS_Locked) != 0;
      this.OldProgramNr = r.GetInt32(field["channel_number"]);
      this.Favorites = this.ParseFavorites(Bits);
      
      if (isAnalog)
        this.ReadAnalogData(r, field);
      else
        this.ReadDvbData(r, field, dataRoot, encryptionInfo);
    }

 protected void ReadDvbData(SQLiteDataReader r, IDictionary<string, int> field, DataRoot dataRoot, 
   IDictionary<string, bool> encryptionInfo)
 {
   string longName, shortName;
   this.GetChannelNames(r.GetString(field["channel_label"]), out longName, out shortName);
   this.Name = longName;
   this.ShortName = shortName;
   this.RecordOrder = r.GetInt32(field["channel_order"]);
   this.FreqInMhz = (decimal)r.GetInt32(field["frequency"]) / 1000;
   int serviceType = r.GetInt32(field["dvb_service_type"]);
   this.ServiceType = serviceType;
   this.OriginalNetworkId = r.GetInt32(field["onid"]);
   this.TransportStreamId = r.GetInt32(field["tsid"]);
   this.ServiceId = r.GetInt32(field["sid"]);
   int bits = r.GetInt32(field["list_bits"]);
   this.Favorites = this.ParseFavorites(bits);
   if ((this.SignalSource & SignalSource.Sat) != 0)
   {
     int satId = r.GetInt32(field["sat_id"]);
     var sat = dataRoot.Satellites.TryGet(satId);
     if (sat != null)
     {
       this.Satellite = sat.Name;
       this.SatPosition = sat.OrbitalPosition;
       int tpId = satId * 1000000 + (int)this.FreqInMhz;
       var tp = dataRoot.Transponder.TryGet(tpId);
       if (tp != null)
       {
         this.SymbolRate = tp.SymbolRate;
       }
     }
   }
   this.Encrypted = encryptionInfo.TryGet(this.Uid);      
 }

 private Person BuildUserStructure(SQLiteDataReader reader)
 {
     string name = reader.GetString(0);
     string fullName = reader.GetString(1);
     long personId = reader.GetInt64(2);
     return new Person(name, fullName, personId);
 }

 /* Returns a Activity using a SQLiteDataReader and a dictionary mapping the attributes to a index.
  */
 private Activity ActivityExtractor(SQLiteDataReader reader, Dictionary<string, int> attributeIndexDict)
 {
     return new Activity(reader.GetInt32(attributeIndexDict["activityId"]),
             reader.GetInt32(attributeIndexDict["seqNum"]), reader.GetString(attributeIndexDict["workoutName"]),
             reader.GetInt32(attributeIndexDict["numSets"]), reader.GetInt32(attributeIndexDict["workoutVersion"]),
             reader.GetString(attributeIndexDict["exerciseName"]), reader.GetInt32(attributeIndexDict["exerciseVersion"]));
 }

        public ResourceRecord(SQLiteDataReader rpReader)
        {
            Time = DateTimeUtil.FromUnixTime(Convert.ToUInt64(rpReader["time"])).LocalDateTime.ToString();

            Fuel = Convert.ToInt32(rpReader["fuel"]);
            FuelDifference = Convert.ToInt32(rpReader["fuel_diff"]);

            Bullet = Convert.ToInt32(rpReader["bullet"]);
            BulletDifference = Convert.ToInt32(rpReader["bullet_diff"]);

            Steel = Convert.ToInt32(rpReader["steel"]);
            SteelDifference = Convert.ToInt32(rpReader["steel_diff"]);

            Bauxite = Convert.ToInt32(rpReader["bauxite"]);
            BauxiteDifference = Convert.ToInt32(rpReader["bauxite_diff"]);

            InstantConstruction = Convert.ToInt32(rpReader["instant_construction"]);
            InstantConstructionDifference = Convert.ToInt32(rpReader["instant_construction_diff"]);

            Bucket = Convert.ToInt32(rpReader["bucket"]);
            BucketDifference = Convert.ToInt32(rpReader["bucket_diff"]);

            DevelopmentMaterial = Convert.ToInt32(rpReader["development_material"]);
            DevelopmentMaterialDifference = Convert.ToInt32(rpReader["development_material_diff"]);

            ImprovementMaterial = Convert.ToInt32(rpReader["improvement_material"]);
            ImprovementMaterialDifference = Convert.ToInt32(rpReader["improvement_material_diff"]);
        }

        internal SortieRecord(SQLiteDataReader rpReader)
        {
            SortieID = Convert.ToInt64(rpReader["id"]);

            var rMapID = Convert.ToInt32(rpReader["map"]);
            Map = MapService.Instance.GetMasterInfo(rMapID);

            var rEventMapDifficulty = (EventMapDifficultyEnum)Convert.ToInt32(rpReader["difficulty"]);
            IsEventMap = rEventMapDifficulty != EventMapDifficultyEnum.None;
            if (IsEventMap)
                EventMapDifficulty = rEventMapDifficulty;

            Step = Convert.ToInt32(rpReader["step"]);
            Node = Convert.ToInt32(rpReader["node"]);
            NodeWikiID = MapService.Instance.GetNodeWikiID(rMapID, Node);

            EventType = (SortieEventType)Convert.ToInt32(rpReader["type"]);
            if (EventType == SortieEventType.NormalBattle)
                BattleType = (BattleType)Convert.ToInt32(rpReader["subtype"]);

            if (EventType == SortieEventType.NormalBattle || EventType == SortieEventType.BossBattle)
                Time = DateTimeUtil.FromUnixTime(Convert.ToUInt64(rpReader["extra_info"])).LocalDateTime.ToString();

            ID = Convert.ToInt64(rpReader["extra_info"]);

            Update(rpReader);
        }

 /// <summary>Will add a DatasetTableRow from db to the given Experiment</summary>
 /// <param name="experiment">Experiment to have its GSMs property updated</param>
 public static void AddDatasetTableRowToExperimentFromDb(Experiment experiment, SQLiteDataReader reader)
 {
     DatasetTableRow dtr = new DatasetTableRow(reader["id_ref"].ToString(), reader["identifier"].ToString(), reader["value"].ToString());
     dtr.Id = Convert.ToInt32(reader["id"].ToString());
     dtr.ExperimentId = reader["experiment_id"].ToString();
     experiment.DatasetTable.Add(dtr);
 }

        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 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;
        }

        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();
        }