C#与Sqlite数据库操作实例

这是一个有关分页的实例,仅供参考(代码来自网络)

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;
using System.Threading;
using System.Collections;
using System.IO;

namespace ReadCardDemo
{
    public partial class Form3 : Form
    {
        Form4 form4 = new Form4();
        int pageSize = 12;     //每页显示行数
         int nMax = 0;         //总记录数
         int totalPage = 0;    //总页数＝总记录数/每页显示行数
         int currentPage = 1;   //当前页号
         int pageCount = 0;    //当前页数的记录数
         int selectRow; //当前选中的行数
         DataSet dataSet = new DataSet();
        DataTable dataTable = new DataTable();
        SQLiteDataAdapter dataAdapter;
        SQLiteConnection conn = new SQLiteConnection();
        SQLiteCommand cmd;
        SQLiteDataReader dr;
        string datasource;
        string _sql;

        public Form3()
        {
            InitializeComponent();            
        }

        //初始化数据
        public void InitializeDataSource()
        {
            dataTable.Clear();
            dataTable.AcceptChanges();
            //创建数据库
            //datasource = "/Windows/ReadCardDemo/PersonId.db";
            //SQLiteConnection.CreateFile(datasource);

            //建立数据库连接
            datasource = @"Data Source=/Windows/ReadCardDemo/PersonId.db";
            conn.ConnectionString = datasource;
             //获取当前数据库的记录数
            _sql = "select count(*) from PersonId";
            conn.Open();
            cmd = new SQLiteCommand(_sql, conn);            
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                try
                {
                    nMax = Convert.ToInt32(dr.GetValue(0));
                }
                catch(Exception e)
                {
                    MessageBox.Show("计算总页数出错！类型转换错误"+e.Message);
                }
            }
            dr.Close();
            conn.Close();
            //计算总页数、当前页数

            totalPage = (nMax / pageSize);    //计算出总页数

            if ((nMax % pageSize) > 0) totalPage++;

            //如果最大页数大于0，查询第一页数据。为1，则当前页要取的数据条数为总记录数，否则为页面行数
            if (totalPage > 0)
            {
                //计算当前页数，如果记录的临时当前页数大于零
                if (currentPage > totalPage)//临时当前页大于总页数
                {
                    currentPage = totalPage;
                }

                //如果当前页等于总页数
                if (totalPage == currentPage)
                {
                    pageCount = nMax - (currentPage - 1) * pageSize;
                }
                else
                {
                    pageCount = pageSize; ;
                }

                //查询数据
                getSelectRecord();
                //更改状态栏的页码信息
                this.label1.Text = currentPage.ToString() + " / " + totalPage.ToString();
            }
            else
            {
                //更改状态栏的页码信息
                this.label1.Text = "0 / 0";
            }
        }

        //查询数据库，获取并显示要查的指定数据
        private void getSelectRecord()
        {
            //查询数据
            _sql = "select * from PersonId limit " + (currentPage-1)*pageSize + "," + pageCount;
            cmd.Connection = conn;
            cmd.CommandText = _sql;
            dataAdapter = new SQLiteDataAdapter(cmd);
            dataAdapter.Fill(dataSet, "PersonId");
            dataTable = dataSet.Tables["PersonId"];

            //数据绑定显示            
            this.bindingSource1.DataSource = dataTable;
            this.dataGrid1.DataSource = bindingSource1;
        }

        //删除某一条记录
        private void menuItem1_Click_1(object sender, EventArgs e)
        {
            selectRow = this.dataGrid1.CurrentRowIndex;
            if (selectRow >= 0)
            {
                DialogResult dialogResult = MessageBox.Show("确认要删除该条记录吗？", "删除确认", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1);
                if (dialogResult == DialogResult.OK)
                {
                    //删除数据库记录
                    object[] datas = dataTable.Rows[selectRow].ItemArray;
                    int delR = this.deleteRecord(datas[0].ToString());//删除数据库记录，以rowId为索引
                    if (delR == 1)//如果删除成功，返回1，更新dataTable里的数据
                    {
                        dataTable.Rows[selectRow].Delete();
                        dataTable.AcceptChanges();

                        //再次初始化数据集合
                        InitializeDataSource();
                    }
                    else
                    {
                        MessageBox.Show("记录删除失败!");
                    }
                }
            }
            else
            {
                MessageBox.Show("当前无被选中记录!");
            }
        }

        //上一页
        private void bt_previous_Click(object sender, EventArgs e)
        {
            if (totalPage > 0)
            {
                if (currentPage > 1)
                {
                    currentPage--;
                    InitializeDataSource();
                }
            }
        }

        //下一页
        private void bt_next_Click(object sender, EventArgs e)
        {
            if (totalPage > 0)
            {
                if (currentPage < totalPage)
                {
                    currentPage++;
                    InitializeDataSource();
                }
            }
        }

        //执行删除数据记录操作
        private int deleteRecord(string id)
        {
            string delStr = "";
            //如果ID不为空，则删除指定ID记录
            if (id != null)
            {
                delStr = "delete from PersonId where rowId=" + id;
            }
            else//如果ID为空，则清空所有记录
            {
                delStr = "delete from PersonId";
            }
            //建立数据库连接
            conn.Open();
            cmd.CommandText = delStr;
            cmd.Connection = conn;
            int delResult = cmd.ExecuteNonQuery();
            conn.Close();
            return delResult;
        }

        //清空，删除所有记录
        private void menuItem2_Click(object sender, EventArgs e)
        {
            if (totalPage > 0)
            {
                DialogResult dialogResult = MessageBox.Show("确认要删除所有记录吗？", "删除确认", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1);
                if (dialogResult == DialogResult.OK)
                {

                    this.deleteRecord(null);//删除数据库所有记录
                    dataTable.Clear();
                    dataTable.AcceptChanges();
                    InitializeDataSource();
                }
            }
            else
            {
                MessageBox.Show("当前无记录可删除!");
            }
        }

        private void menuItem3_Click(object sender, EventArgs e)
        {
            selectRow = this.dataGrid1.CurrentRowIndex;
            if (selectRow >= 0)
            {
                //获取数据记录信息并显示
                object[] datas = dataTable.Rows[selectRow].ItemArray;
                form4.initData(datas);
                form4.Show();
            } 
        }

        private void Form3_Load(object sender, EventArgs e)
        {
            InitializeDataSource();
        }

    }
}