public static RiderMessage[] GetMessages(string userId,string raceId)
        {
            List<RiderMessage> messages = new List<RiderMessage>();
              SQLiteDatabase db = new SQLiteDatabase();
              string sql = "select m.RaceId, u.userid,u.username, m.message, m.SendTime " +
                    "from cycli_rider_messages m, cycli_riders u " +
                    "where m.SenderId = u.UserId and " +
                    "m.RaceId = @r " +
                      "order by m.SendTime";

              DataTable dt = db.GetDataTable(sql,"@r",raceId, "@t", "fromTime");
              db.Close();
              foreach (DataRow dr in dt.Rows)
              {
            RiderMessage message = new RiderMessage();
            message.SenderId = (string)dr["userid"];
            message.Sender = (string)dr["username"];
            message.RaceId = (string)dr["RaceId"];
            message.Message = (string)dr["Message"];
            message.SendTime = (long)(int)dr["SendTime"];
            messages.Add(message);
              }

              return messages.ToArray();
        }

 public SQLiteTransaction(SQLiteDatabase database, IsolationLevel level, SQLiteSettings settings)
 {
   _database = database;
   _settings = settings;
   _connection = _database.ConnectionPool.GetConnection();
   _transaction = _connection.BeginTransaction(level);
 }

        public itempage1()
        {
            InitializeComponent();
            try
            {
                var db = new SQLiteDatabase();
                DataTable recipe;
                String query = "select ID \"id\", NAME \"Description\",";
                query += "CLIP \"Text\"";
                query += "from CLIPBOARD;";
                recipe = db.GetDataTable(query);
                // The/ results can be directly applied to a DataGridView control
                //dataGrid.DataContext = recipe;
                /*
                // Or looped through for some other reason
                foreach (DataRow r in recipe.Rows)
                {
                    MessageBox.Show(r["Name"].ToString());
                    MessageBox.Show(r["Description"].ToString());
                    MessageBox.Show(r["Prep Time"].ToString());
                    MessageBox.Show(r["Cooking Time"].ToString());
                }

                */
            }
            catch (Exception fail)
            {
                String error = "The following error has occurred:\n\n";
                error += fail.Message.ToString() + "\n\n";
                MessageBox.Show(error);
                this.Close();
            }
        }

        static void DumpSqlite(string filename)
        {
            Serializer s = new Serializer();
            try
            {
                var db = new SQLiteDatabase(filename);
                List <String> tableList = db.GetTables();
                List<SerializableDictionary<string, string>> dictList = new List<SerializableDictionary<string, string>>();
                foreach (string table in tableList)
                    {
                        String query = string.Format("select * from {0};", table);
                        DataTable recipe = db.GetDataTable(query);
                        foreach (DataRow r in recipe.Rows)
                            {
                                SerializableDictionary<string, string> item = new SerializableDictionary<string, string>();
                                foreach (DataColumn c in recipe.Columns)
                                    {
                                        item[c.ToString()] = r[c.ToString()].ToString();
                                    }
                                dictList.Add(item);
                            }
                        s.Serialize(string.Format("{0}.xml", table), dictList, table);
                        dictList.Clear();
                    }

            }
            catch (Exception fail)
            {
                String error = "The following error has occurred:\n\n";
                error += fail.Message + "\n\n";
            }
        }

        public static Rider LoadAny(string userId)
        {
            Rider thisRider = null;
              SQLiteDatabase db = new SQLiteDatabase();
              string sql = @"select r.UserId as UserId, r.Username as Username, r.BikeWheelSizeMm as BikeWheelSizeMm, r.Turbo as Turbo, " +
            "r.TurboIsCalibrated as TurboIsCalibrated, r.EstimatedPower as EstimatedPower " +
            "From cycli_riders r " +
            "where [email protected] and AccountStatus='Active'" +
            "union " +
            "select r.UserId as UserId, r.Username as Username, 700 as BikeWheelSizeMm, null as Turbo, " +
            "'False' as TurboIsCalibrated, 'False' as EstimatedPower " +
            "From cycli_virtual_riders r " +
            "where [email protected] and Status='Active'";

             // Only load active accounts
               DataTable dtUser = db.GetDataTable(sql, "@u1", userId, "@u2",userId);
              if (dtUser.Rows.Count > 0)
              {
            DataRow dr = dtUser.Rows[0];
            thisRider = new Rider();
            thisRider.UserName = dr.Field<string>("Username");
            thisRider.UserId= dr.Field<string>("UserId");
            thisRider.BikeWheelSizeMm = (int)dr.Field<long>("BikeWheelSizeMm");
            thisRider.CurrentTurbo = dr.Field<string>("Turbo");
            thisRider.TurboIsCalibrated = (dr.Field<string>("TurboIsCalibrated") == bool.TrueString);
            thisRider.EstimatedPower = (dr.Field<string>("EstimatedPower") == bool.TrueString);

              }
              db.Close();
              return thisRider;
        }

 public static Friend[] Load(string userId)
 {
     List<Friend> friends = new List<Friend>();
       SQLiteDatabase db = new SQLiteDatabase();
       string sql = @"select * from (select r.UserId as UserId, r.UserName as UserName, " +
       "r.Turbo, t.Power_Model_C1, t.Power_Model_C2, t.Power_Model_C3, " +
       "f.Status as Status " +
               "from cycli_riders r, cycli_friends f, cycli_turbos t " +
           "where f.UserId = '" + userId + "' and r.UserId=f.FriendId " +
           "and r.Turbo = t.Type " +
           "union " +
           "select r.UserId as UserId, r.UserName as UserName, "+
           "r.Turbo, t.Power_Model_C1, t.Power_Model_C2, t.Power_Model_C3, " +
           "f.Status as Status " +
               "from cycli_riders r, cycli_friends f, cycli_turbos t " +
           "where f.FriendId= '" + userId + "' and r.UserId=f.UserId "+
           "and r.Turbo = t.Type " +
           ") order by UserName";
       DataTable dtFriends = db.GetDataTable(sql);
       foreach (DataRow dr in dtFriends.Rows)
       {
     Friend f = new Friend()
     {
       UserId = dr.Field<string>("UserId"),
       UserName = dr.Field<string>("UserName"),
       Turbo = dr.Field<string>("Turbo"),
       Coefficients = new double[]{dr.Field<double>("Power_Model_C1"),dr.Field<double>("Power_Model_C2"),dr.Field<double>("Power_Model_C3")},
       Status = (string)dr["Status"]
     };
     friends.Add(f);
       }
       return friends.ToArray();
 }

        /// <summary>
        /// Creates new instance of SQLiteVdbe class by compiling a statement
        /// </summary>
        /// <param name="query"></param>
        /// <returns>Vdbe</returns>
        public SQLiteVdbe(SQLiteDatabase db, String query)
        {
            vm = null;

            // prepare and compile
            Sqlite3.sqlite3_prepare_v2(db.Connection(), query, query.Length, ref vm, 0);
        }

 private void InitializeTables(SQLiteDatabase db)
 {
     db.ExecuteNonQuery("BEGIN EXCLUSIVE");
     for(int i = 0; i < CREATE_Commands.Length; i++)
     {
         db.ExecuteNonQuery(CREATE_Commands[i]);
     }
 }

 public static SQLiteDatabase Instance()
 {
     if (instance == null)
     {
         instance = new SQLiteDatabase();
     }
     return instance;
 }

        public frm_Main()
        {
            connect = new ConnectProlog();
            InitializeComponent();

            m_resources = new Resources();
            m_database = new SQLiteDatabase("laptop.s3db");
        }

 public static bool EmployeeExists(string employeeID, SQLiteDatabase sql)
 {
     // notify user if card wasn't found
     if (sql.GetDataTable("select * from employees where employeeID=" + employeeID.Trim() + ";").Rows.Count == 1)
         return true;
     else
         return false;
 }

 public FormWarmup(SQLiteDatabase db)
 {
     InitializeComponent();
     dbsqlite = db;
     loadconfig();
     //load from DB
     LoadPhysical();
 }

        public void ResetDatabases()
        {
            string password = Settings.Default.Password;

            if (password.Length > 0)
                password = SecurityExtensions.DecryptString(password, Encoding.Unicode.GetBytes(Settings.Default.Entropy)).ToInsecureString();

            worldDatabase = new WorldDatabase(Settings.Default.Host, Settings.Default.Port, Settings.Default.User, password, Settings.Default.Database);
            sqliteDatabase = new SQLiteDatabase("Resources/sqlite_database.db");
        }

 public DataAccess(string filename)
 {
     this.filename = filename;
     database = new SQLiteDatabase(filename);
     //Create database structure for new files
     if (!File.Exists(filename))
     {
         CreateNewRunFile(filename);
     }
 }

 protected void Page_Load(object sender, EventArgs e)
 {
     if (!IsPostBack)
     {
         string sql = "select * from userInfo order by id desc limit 200";
         SQLiteDatabase db = new SQLiteDatabase();
         var datatable = db.GetDataTable(sql);
         rptUserInfo.DataSource = datatable;
         rptUserInfo.DataBind();
     }
 }

        /// <summary>
        ///     Checks whether a specified account exists and whether the password supplied
        ///     is correct
        /// </summary>
        /// <param name="id">The ID of the student/teacher - Corresponds to the id field
        /// in the DB</param>
        /// <param name="pass">The password to check as a string</param>
        /// <param name="stuteach">A string containing "students" or "teachers"</param>
        /// <returns>true when account is correct, false otherwise</returns>
        public static bool checkAccount(int id, string pass, string stuteach)
        {
            SQLiteDatabase db = new SQLiteDatabase();
            string dbPass = db.ExecuteScalar(string.Format("SELECT hash FROM {0} WHERE id={1};", stuteach, id));
            string passHash = Password.hashAsString(pass);

            if (dbPass == passHash)
                return true;

            return false;
        }

        static void Main()
        {
            //Needed for log4net to read from the app.config
            XmlConfigurator.Configure();

            var db = new SQLiteDatabase(@"D:\Fun\Code\GitHub\Logger\Database\MyLogs.db;");
            //Clearing previous test data
            db.ClearTable("Log");

            AddFakeData();
            RetrieveAndDisplayData(db);
        }

        public Form1(int selectedMail, int action)
        {
            InitializeComponent();

            // action:
            // 0 = new mail
            // 1 = reply
            // 2 = forward
            // 3 = send public key
            string selectedSender = "unknown";
            string selectedSubject = "unknown";
            string selectedBody = "unknown";
            if (action == 1 || action == 2)
            {
                SQLiteDatabase db = new SQLiteDatabase();
                DataTable Mail;
                String query = "select Sender \"Sender\", Subject \"Subject\",";
                query += "Body \"Body\", Timestamp \"Timestamp\"";
                query += "from Mails ";
                query += "where ID = " + selectedMail + ";";
                Mail = db.GetDataTable(query);
                foreach (DataRow r in Mail.Rows)
                {
                    selectedSender = r["Sender"].ToString();
                    selectedSubject = r["Subject"].ToString();
                    selectedBody = r["Body"].ToString();
                }
            }

            switch (action)
            {
                case 1: // Reply
                    textBoxTo.Text = selectedSender;
                    textBoxSub.Text = "RE: " + selectedSubject;
                    break;
                case 2: // Forward
                    textBoxTo.Text = "";
                    textBoxSub.Text = "FW: " + selectedSubject;
                    break;
                case 3:
                    textBoxSub.Text = "My public key";
                    textBoxBody.Text = Properties.Settings.Default.RSAPublic;
                    textBoxPublicKey.Visible = false;
                    label1.Visible = false;
                    checkBoxEncrypt.Visible = false;
                    checkBoxRSA.Visible = false;
                    break;
                default:
                    textBoxTo.Text = "";
                    textBoxSub.Text = "";
                    break;
            }
        }

        //
        // PRIVATE and INTERNAL
        //
        internal Vault()
        {
            databaseFileName = PLUGIN_FOLDER + Path.DirectorySeparatorChar + "vault.db";
            debug("database file location:" + databaseFileName);

            Dictionary<string, string > options = new Dictionary<string, string>();
            options["Data Source"] = databaseFileName;
            options["New"] = "True";
            database = new SQLiteDatabase(options);

            setup();
        }

        private static void RetrieveAndDisplayData(SQLiteDatabase db)
        {
            var results = db.GetDataTable("SELECT * From Log");

            //TODO: Use generics instead. Please don't go out there kicking a cute puppy because of this code :(
            foreach (DataRow item in results.Rows)
            {
                Console.WriteLine("Level: {0} - Location: {1} - Date and Time: {2}",
                                item["Level"].ToString().ToUpper(), item["Location"], item["TimeStamp"]);
                Console.WriteLine("Message: \n{0} \n", item["Message"]);
            }
            Console.ReadLine();
        }