C# SQLiteDatabase.GetDataTable方法代码示例

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

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

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

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

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

            // Actions:
            // 0 = new mail
            // 1 = reply
            // 2 = forward

            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;
                default:
                    textBoxTo.Text = "";
                    textBoxFrom.Text = "";
                    textBoxSub.Text = "";
                    break;
            }

            //MessageBox.Show(selectedMail.ToString() + " - " + action.ToString());
        }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            int id = 0;
            if (Request["id"] != null)
            {
                int.TryParse(Request["id"], out id);

                if (id > 0)
                {
                    string sql = "select * from usercookie where infoId=" + id + " limit 200";
                    SQLiteDatabase db = new SQLiteDatabase();
                    var datatable = db.GetDataTable(sql);
                    rptCookieInfo.DataSource = datatable;
                    rptCookieInfo.DataBind();
                }
            }
        }
    }

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

        /// <summary>
        /// Confirm identity of logging in user
        /// </summary>
        /// <param name="n"></param>
        /// <param name="e"></param>
        protected void loginconf(Network n, Irc.IrcEventArgs e)
        {
            if (IsMatch("^everify (?<nonce>.*?)$", e.Data.Message.Substring(BOT_CONTROL_SEQ.Length)))
            {
                string[] temp = Matches["nonce"].Value.Split(':');
                string name = temp[0];
                SQLiteDatabase db = new SQLiteDatabase();
                System.Data.DataTable accounts;
                String query = "SELECT id \"ID\", name \"NAME\", email \"EMAIL\", gpgkey \"key\", verify \"VERIFY\" FROM accounts;";
                accounts = db.GetDataTable(query);
                bool registered = false;
                int id = 0;
                foreach (DataRow account in accounts.Rows)
                {
                    id++;
                    if (account["NAME"] as string == name)
                    {
                        Answer(n, e, "Name from verify string:   " + name);
                        Answer(n, e, "Verify string from db:     " + account["VERIFY"].ToString());
                        Answer(n, e, "Verify string from irc:    " + Matches["nonce"].Value);

                        if (account["VERIFY"].ToString().ToLower() == Matches["nonce"].Value.ToLower() + "\n")
                        {
                            User u = new User();
                            u.nick = e.Data.Nick;
                            u.user = name;
                            u.kid = account["key"] as string;
                            loggedin.Add(u);

                            //LOGIN THE USER AT THE BOT LEVEL SO OTHER PLUGINS CAN SEE
                            Bot.LoginUser(u);

                            //JUST ANOTHER WAY TO REPLY TO THE USER
                            Answer(n, e, e.Data.Nick + ": You are now logged in.");
                            registered = true;
                        }
                        else
                        {
                            Answer(n, e, e.Data.Nick + ": Invalid login.");
                        }
                    }
                }

                if (!registered)
                {
                    Answer(n, e, e.Data.Nick + ": Your not registered.");
                }
            }
        }

        //for migration purposes only. delete.
        public List<TicketResource> GetAllTickets()
        {
            SQLiteDatabase db = new SQLiteDatabase();
            string sql = "select * from Tickets";
            DataTable result = db.GetDataTable(sql);

            List<TicketResource> ticketResources = new List<TicketResource>();
            foreach (DataRow row in result.Rows)
            {
                //just do an insert here instead
                TicketResource ticketRes = ConvertDataRowToTicketResource(row);
                InsertNewTicket(ticketRes);
                ticketResources.Add(ticketRes);
            }
            return ticketResources;
        }

        void LoadedEventHandler(object sender, RoutedEventArgs e)
        {
            Logger.MonitoringLogger.Debug("Login window loaded event");
            LoadFromConfigurationFile();

            try
            {
                Logger.MonitoringLogger.Info("Populate profile list with information from the database");
                db = new SQLiteDatabase();
                DataTable resultQuery;
                String query = "select * FROM profileList;";
                resultQuery = db.GetDataTable(query);
                
                // Loop over all data
                foreach (DataRow r in resultQuery.Rows)
                {
                    ServerProfile serverProfile = new ServerProfile(r["profilName"].ToString(), r["hostname"].ToString(), Int32.Parse(r["port"].ToString()), r["password"].ToString(), Boolean.Parse(r["autoReconnect"].ToString()));
                    loginDataView.ServersProfiles.Add(serverProfile);
                    Logger.MonitoringLogger.Info(r.ToString());
                }
            }
            catch (Exception databaseException)
            {
                MessageDialog.ShowAsync(
                        "Erreur de lecture de la base de donnée",
                        "Erreur de lecture de la base de donnée ! Informations de debug :\n" + databaseException.ToString(),
                        MessageBoxButton.OK,
                        MessageDialogType.Light,
                        this);
            }

            NotifyBox.Show(
                           (DrawingImage)this.FindResource("SearchDrawingImage"),
                           "Astuce",
                           "Un clic droit permet d'ouvrir le menu application !",
                           false);
            Logger.MonitoringLogger.Debug(WINDOW_NAME + " loaded function ended");
        }