C# SQLiteCommand.Read方法代码示例

        public void CountItems(string Key, Action<string> y)
        {
            //Console.WriteLine("CountItems enter");
            using (var c = OpenReadOnlyConnection())
            {
                c.Open();

                using (var reader = new SQLiteCommand(
                    "select count(*) from MY_TABLEXX where XKey ='" + Key.Replace("'", "\\'") + "'", c).ExecuteReader()
                    )
                {

                    if (reader.Read())
                    {
                        var Content = (int)reader.GetInt32(0);

                        y("" + Content);

                    }
                }

                c.Close();

            }
            //Console.WriteLine("CountItems exit");
        }

 private void ReadUsersIntoList(SQLiteConnection db) {
     using (SQLiteDataReader userEntry = new SQLiteCommand("SELECT * FROM users", db).ExecuteReader()) {
         while (userEntry.Read()) {
             overlordRef.Create((int)userEntry["access"],
                 (string)userEntry["nickname"],
                 (string)userEntry["realname"],
                 DateTime.Parse((string)userEntry["seen"]),
                 false,
                 (int)userEntry["id"]);
         }
     }
 }

 public List<Toon> getAllToons(Toon t)
 {
     List<Toon> list = new List<Toon>();
     string cmd = "SELECT * FROM Toons";
     SQLiteDataReader reader = new SQLiteCommand(cmd, dbConnection).ExecuteReader();
     while (reader.Read()) {
         object[] values = new object[reader.FieldCount];
         reader.GetValues(values);
         list.Add(new Toon(values));
     }
     return list;
 }

        public static string Read(string contentRead)
        {
            using (var c = new SQLiteConnection(

                new SQLiteConnectionStringBuilder
                {
                    DataSource = "MY_DATABASE.sqlite",
                    Version = 3,
                    ReadOnly = true,

                    // cannot be set while running under .net debugger
                    // php needs to use defaults

                    //Password = "",
                    //Uri = "localhost"              
                }.ConnectionString
                ))
            {
                // Invalid ConnectionString format for parameter "password"
                c.Open();




                var reader = new SQLiteCommand("select Content from MY_TABLE", c).ExecuteReader();

                //var x = from k in MY_TABLE
                //        select new { k.Content };


                //if (reader == null)
                //    contentRead += "Reader was null";
                //else
                //{
                //var i = 6;

                while (reader.Read())
                {
                    //i--;
                    contentRead += "\n";
                    contentRead += (string)reader["Content"];

                    //if (i == 0)
                    //    break;
                }
                //}


                c.Close();

            }
            return contentRead;
        }

        /// <summary>
        /// This Method is a javascript callable method.
        /// </summary>
        /// <param name="e">A parameter from javascript.</param>
        /// <param name="y">A callback to javascript.</param>
        public void AddItem(string Key, string Content, Action<string> y)
        {
#if !DEBUG
            // should jsc do this implictly?
            try
            {
                //DriverManager.registerDriver(new AppEngineDriver());
            }
            catch
            {
                throw;
            }
#endif

            using (var c = new SQLiteConnection(

                  new SQLiteConnectionStringBuilder
                  {
                      DataSource = "MY_DATABASE.sqlite",
                      Version = 3
                  }.ConnectionString

            ))
            {
                c.Open();

                using (var cmd = new SQLiteCommand("create table if not exists MY_TABLEXX (XKey text not null, Content text not null)", c))
                {
                    cmd.ExecuteNonQuery();
                }

                new SQLiteCommand("insert into MY_TABLEXX (XKey, Content) values ('" + Key.Replace("'", "\\'") + "', '" + Content.Replace("'", "\\'") + "')", c).ExecuteNonQuery();

                using (var reader = new SQLiteCommand("select count(*) from MY_TABLEXX", c).ExecuteReader())
                {

                    if (reader.Read())
                    {
                        var count = (int)reader.GetInt32(0);

                        y("" + count);

                    }
                }


                c.Close();
            }

        }

        public void Init()
        {
            try
            {
                string path = Path.Combine(Program.Config.LauncherDir, "cards.cdb");
                m_cards = new Dictionary<int, CardInfos>();

                if (!File.Exists(path)) return;

                SQLiteConnection connection = new SQLiteConnection("Data Source=" + path);
                connection.Open();
                SQLiteDataReader reader = new SQLiteCommand("SELECT id, alias, type, level, race, attribute, atk, def FROM datas", connection).ExecuteReader();

                while (reader.Read())
                {
                    int id = reader.GetInt32(0);

                    CardInfos infos = new CardInfos(id)
                    {
                        AliasId = reader.GetInt32(1),
                        Type = reader.GetInt32(2),
                        Level = reader.GetInt32(3),
                        Race = reader.GetInt32(4),
                        Attribute = reader.GetInt32(5),
                        Atk = reader.GetInt32(6),
                        Def = reader.GetInt32(7)
                    };
                    m_cards.Add(id, infos);
                }

                reader.Close();
                reader = new SQLiteCommand("SELECT id, name, desc FROM texts", connection).ExecuteReader();

                while (reader.Read())
                {
                    int key = reader.GetInt32(0);
                    if (m_cards.ContainsKey(key))
                    {
                        m_cards[key].Name = reader.GetString(1);
                        m_cards[key].CleanedName = m_cards[key].Name.Trim().ToLower().Replace("-", " ").Replace("'", " ").Replace("   ", " ").Replace("  ", " ");
                        m_cards[key].Description = reader.GetString(2);
                    }
                }
                connection.Close();
            }
            catch (Exception)
            {
            }
        }

 public void Init()
 {
     try
     {
         m_cards = new Dictionary<int, CardInfos>();
         string str = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) ?? "";
         string str2 = Path.Combine(str, @"cards.cdb");
         if (!File.Exists(str2)) return;
         SQLiteConnection connection = new SQLiteConnection("Data Source=" + str2);
         connection.Open();
         SQLiteDataReader reader = new SQLiteCommand("SELECT id, alias, type, level, race, attribute, atk, def FROM datas", connection).ExecuteReader();
         while (reader.Read())
         {
             int id = reader.GetInt32(0);
             CardInfos infos = new CardInfos(id)
             {
                 AliasId = reader.GetInt32(1),
                 Type = reader.GetInt32(2),
                 Level = reader.GetInt32(3),
                 Race = reader.GetInt32(4),
                 Attribute = reader.GetInt32(5),
                 Atk = reader.GetInt32(6),
                 Def = reader.GetInt32(7)
             };
             m_cards.Add(id, infos);
         }
         reader.Close();
         reader = new SQLiteCommand("SELECT id, name, desc FROM texts", connection).ExecuteReader();
         while (reader.Read())
         {
             int key = reader.GetInt32(0);
             if (m_cards.ContainsKey(key))
             {
                 m_cards[key].Name = reader.GetString(1);
                 m_cards[key].CleanedName = m_cards[key].Name.Trim().ToLower().Replace("-", " ").Replace("'", " ").Replace("   ", " ").Replace("  ", " ");
                 m_cards[key].Description = reader.GetString(2);
             }
         }
         connection.Close();
         Loaded = true;
     }
     catch (Exception)
     {
         MessageBox.Show("Error loading cards.cdb");
     }
 }

        Bug IDataAccess.GetBug(int id)
        {
            using (conn = new SQLiteConnection(connectionString))
            {
                conn.Open();

                var bug = new Bug();

                var reader = new SQLiteCommand(SQLFixedQueries.SelectBug(id), conn)
                                                    .ExecuteReader();

                while (reader.Read())
                    bug = ReadBugFromSQL(reader);

                return bug;
            }
        }

        internal Action GetAction(string actionName)
        {
            var cn = new SQLiteConnection("Data Source=" + DbName + ";Version=3;");
             cn.Open();

             var reader = new SQLiteCommand("select id, command, arg1, arg2 from actions where id = '" + actionName + "'", cn).ExecuteReader();

             Action action = null;
             try
             {
            if (reader.Read())
            {
               action = new Action();
               action.Id = reader.GetString(reader.GetOrdinal("id"));
               action.Arg1 = reader.GetString(reader.GetOrdinal("arg1"));
               var arg2 = reader["arg2"];
               action.Arg2 = arg2.ToString();
               //action.Arg3 = reader.GetString(reader.GetOrdinal("arg3"));

               if (reader.GetString(reader.GetOrdinal("command")) == "pgdb-backup")
               {
                  action.Command = new PostgreSQLBackupCommand(action.Arg1, action.Arg2, action.Arg3, action.Arg4);
               }
               if (reader.GetString(reader.GetOrdinal("command")) == "execute-bat")
               {
                  action.Command = new ExecuteBatCommand(action.Arg1);
               }
               if (reader.GetString(reader.GetOrdinal("command")) == "sync-actions")
               {
                  action.Command = new SyncActionsCommand(action.Arg1);
               }
            }
             }
             finally
             {
            cn.Close();
             }
             return action;
        }

        //Clocking Log
        private void viewClocking()
        {
            var db = DBMethods.OPENDB();
            //Change query to get time properly.
            string queryString = "SELECT ID, GM, " + DBMethods.GETDATETIME("time") + ", status FROM clocking ORDER BY time DESC;";
            SQLiteDataReader query = new SQLiteCommand(queryString, db).ExecuteReader();

            SearchView.ClearList();

            while (query.Read())
            {
                //Parse datetime from database
                string time = DateTime.Parse(query["datetime"].ToString()).ToString("yyyy-MM-dd HH:mm");

                string GM = query["GM"].ToString();
                bool status = Convert.ToBoolean(int.Parse(query["status"].ToString()));

                SearchView.AddChild(GM + ':' + (status ? "IN" : "OUT"), time, 0);
            }

            db.Close();
            SearchView.Show();
        }

        public void EnumerateItems(string e, Action<string> y)
        {
            //Console.WriteLine("EnumerateItems enter");
            using (var c = OpenReadOnlyConnection())
            {
                c.Open();

                using (var reader = new SQLiteCommand("select Content from MY_TABLE", c).ExecuteReader())
                {

                    while (reader.Read())
                    {
                        var Content = (string)reader["Content"];

                        y(Content);

                    }
                }

                c.Close();

            }
            //Console.WriteLine("EnumerateItems exit");
        }

 public void menu_Syn_From_SQLite (Object Sender,EventArgs e)
 {
     OpenFileDialog ofd = new OpenFileDialog();
     ofd.FileName = "";
     ofd.DefaultExt = ".db";
     ofd.Filter = "SQLite (.DB)|*.db";
     if(ofd.ShowDialog()==DialogResult.OK)
     {
         String filepath = ofd.FileName;
         using(SQLiteConnection sqliteconn=new SQLiteConnection(String.Format("Data Source={0}",filepath)))
         {
             sqliteconn.Open();
             String sql = "SELECT name From sqlite_master Where type in('table','view') AND name Not Like 'sqlite_%' UNION ALL SELECT name From sqlite_temp_master Where type IN('table','view') ORDER BY 1";
             SQLiteDataReader dr = new SQLiteCommand(sql,sqliteconn).ExecuteReader();
             MPPFORM.ListBoxForm lbf = new MPPFORM.ListBoxForm();
             while (dr.Read())
             {
                 lbf.lb.Items.Add(dr[0].ToString());
             }
             if (lbf.ShowDialog() == DialogResult.OK)
             {
                 OdbcConnection odbcconn = new OdbcConnection(Basic_HTB_Info.Conn_Str);
                 odbcconn.Open();
                 foreach (string s in lbf.lb.CheckedItems)
                 {
                     string sql_tbl = "Select * from " + s;
                     SQLiteDataReader dr_tbl = new SQLiteCommand(sql_tbl,sqliteconn).ExecuteReader();
                     
                     String inc_sql = String.Format("insert into {0} values (?",s);
                     for (int i = 1; i < dr_tbl.FieldCount; i++)
                     {
                         inc_sql += ",?";
                     }
                     inc_sql += ");";
                     OdbcCommand inccmd = new OdbcCommand(inc_sql, odbcconn);
                     for (int i = 0; i < dr_tbl.FieldCount; i++)
                     {
                         inccmd.Parameters.Add(new OdbcParameter());
                     }
                     while (dr_tbl.Read())
                     {
                         for (int i = 0; i < dr_tbl.FieldCount; i++)
                         {
                             inccmd.Parameters[i].Value = dr_tbl[i].ToString();
                         }
                         inccmd.ExecuteNonQuery();
                     }
                 }
                 odbcconn.Close();
                 odbcconn.Dispose();
             }
         }
     }
 }

            public Point this[string id]
            {
                set
                {
                    Context.Create();

                    if (Context.Connection.SQLiteCountByColumnName(Context.Name, "id", id) == 0)
                    {
                        var sql = "insert into ";
                        sql += Context.Name;
                        sql += " (id, x, y) values (";
                        sql += "'";
                        sql += id;
                        sql += "'";
                        sql += ", ";
                        sql += "'";
                        sql += value.x;
                        sql += "'";

                        sql += ", ";
                        sql += "'";
                        sql += value.y;
                        sql += "'";

                        sql += ")";



                        new SQLiteCommand(sql, Context.Connection).ExecuteNonQuery();

                        return;
                    }

                    #region update
                    {
                        var sql = "update ";
                        sql += Context.Name;

                        sql += " set x = ";
                        sql += "'";
                        sql += value.x;
                        sql += "'";

                        sql += " set y = ";
                        sql += "'";
                        sql += value.y;
                        sql += "'";


                        sql += " where id = ";
                        sql += "'";
                        sql += id;
                        sql += "'";

                        new SQLiteCommand(sql, Context.Connection).ExecuteNonQuery();
                    }
                    #endregion

                }

                get
                {
                    Context.Create();


                    var sql = "select x, y from ";
                    sql += Context.Name;
                    sql += " where id = ";
                    sql += "'";
                    sql += id;
                    sql += "'";

                    //new SQLiteCommand(sql, Connection).ExecuteScalar();

                    var value = default(Point);
                    var reader = new SQLiteCommand(sql, Context.Connection).ExecuteReader();

                    if (reader.Read())
                    {
                        value = new Point { x = reader.GetString(0), y = reader.GetString(1) };
                    }
                    reader.Close();

                    return value;
                }
            }

        public static bool SQLiteTableExists(this SQLiteConnection c, string name)
        {
            // http://www.electrictoolbox.com/check-if-mysql-table-exists/



            //var w = "select name from sqlite_master where type='table' and name=";
            var w = "select table_name from information_schema.tables where table_name=";
            w += "'";
            w += name;
            w += "'";


            var reader = new SQLiteCommand(w, c).ExecuteReader();

            var value = reader.Read();

            reader.Close();

            return value;
        }

        public List<string> GetKeys()
        {
            var sql = "select id from ";
            sql += Name;

            var value = new List<string>();

            var reader = new SQLiteCommand(sql, Connection).ExecuteReader();

            while (reader.Read())
            {
                value.Add(reader.GetString(0));
            }

            reader.Close();

            return value;
        }