一个分页的dataGridViewDEMO 用sqlite数据库实现

/*
* 分页参考：https://www.cnblogs.com/swjian/p/9889789.html
* 填充数据参考：https://blog.csdn.net/qiuyu6958334/article/details/104929265
* 更新2021年1月4日 11:22:00：https://www.cnblogs.com/xe2011/p/14228899.html
*/

直接粘贴源码 不解释 数据库用的是SQLITE

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

namespace dgv分页
{

    /*
     * 分页参考：https://www.cnblogs.com/swjian/p/9889789.html
     * 填充数据参考：https://blog.csdn.net/qiuyu6958334/article/details/104929265
     * 更新2021年1月4日 11:22:00：https://www.cnblogs.com/xe2011/p/14228899.html
     */
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

 
        /// <summary>
        /// 每页记录数
        /// </summary>
        public int pageSize = 11;

        /// <summary>
        /// 总记录数
        /// </summary>
        public int recordCount = 0;

        /// <summary>
        /// 总页数
        /// </summary>
        public int pageCount = 0;

        /// <summary>
        /// 当前页
        /// </summary>
        public int currentPage = 0;


        /// <summary>
        ///  dt = new DataTable(); 用于过滤数据用    dgv1.DataSource = dt;
        /// </summary>
        DataTable dt = new DataTable();
        /// <summary>
        ///  相当于重新绑定数据了 刷新数据了
        /// </summary>
        public void getData() {
            SQLiteHelper sqlite = new SQLiteHelper("Data Source = test.db; Version=3;");
            dt = sqlite.DataAdapter($"Select * From Data","Data").Tables[0];

            //dgv1.DataSource = dt;//

            currentPage = 1;
            LoadPage();//调用加载数据的方法

            HideColumns();
        }

        private void HideColumns() {
            //全部隐藏
            for (int i = 0; i < dgv1.ColumnCount; i++) {
                dgv1.Columns[i].Visible = false;
                //dgv1.Columns[i].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            }
            dgv1.RowHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;

            //列头中对齐
            //dgv1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;

            dgv1.Columns["单词"].Visible = true;
            dgv1.Columns["单词"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
            dgv1.Columns["单词"].MinimumWidth = 160;
            dgv1.Columns["单词"].Width = 160;

            dgv1.Columns["解释"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
            dgv1.Columns["解释"].MinimumWidth = 200;
            dgv1.Columns["解释"].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;


            //显示不隐藏的部分
            //dgv1.Columns["单词"].DefaultCellStyle.Font = new Font("Arial",9,FontStyle.Regular);

            dgv1.Columns["音标"].Visible = true;
            dgv1.Columns["音标"].DefaultCellStyle.ForeColor = Color.Gray;
            dgv1.Columns["音标"].MinimumWidth = 60;
            dgv1.Columns["音标"].Width = 60;
            dgv1.Columns["解释"].Visible = true;
        }
        private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {//行号
            DataGridView dgv1 = sender as DataGridView;
            Rectangle rectangle = new Rectangle(e.RowBounds.Location.X,
                                                e.RowBounds.Location.Y,
                                                dgv1.RowHeadersWidth - 4,
                                                e.RowBounds.Height);


            TextRenderer.DrawText(e.Graphics, (e.RowIndex + 1).ToString(),
                                    dgv1.RowHeadersDefaultCellStyle.Font,
                                    rectangle,
                                    dgv1.RowHeadersDefaultCellStyle.ForeColor,
                                    TextFormatFlags.VerticalCenter | TextFormatFlags.Right);
        }

        private void Form1_Load(object sender,EventArgs e) {
            getData();
            dgv1.ScrollBars = ScrollBars.Vertical;//绑定数据 、然后隐藏列会闪现一次水平滚动条
            dgv1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            dgv1.AllowUserToAddRows = false;
            dgv1.AllowUserToDeleteRows = false;
            dgv1.ReadOnly = true;
            dgv1.AllowUserToResizeRows = false;
            dgv1.AllowUserToResizeColumns = true;    //禁止用户改变列宽
        }

        private void LoadPage( ) {

            recordCount = dt.Rows.Count;     //记录总行数
            pageCount = (recordCount / pageSize);
            if ((recordCount % pageSize) > 0) {
                pageCount++;
            }

            if (currentPage < 1) 
                currentPage = 1;

            if (currentPage > pageCount) 
                currentPage = pageCount;

            int beginRecord;    //开始指针
            int endRecord;      //结束指针

            DataTable dtTemp;
            dtTemp = dt.Clone();

            beginRecord = pageSize * (currentPage - 1);

            if (currentPage == 1)
                beginRecord = 0;

            endRecord = pageSize * currentPage;

            if (currentPage == pageCount) 
                endRecord = recordCount;

            for (int i = beginRecord; i < endRecord; i++)
            {
                dtTemp.ImportRow(dt.Rows[i]);
            } 

            dgv1.DataSource = null;
            dgv1.DataSource = dtTemp;
            HideColumns();
            labRecordCount.Text = $"总行数:  {recordCount}  行";//总记录数
            labPageIndex.Text = $"当前页: {currentPage} / {pageCount}";//当前页
        }


        //首页
        private void btnFirst_Click(object sender, EventArgs e)
        {
            if (currentPage == 1) return;
            currentPage = 1;
            LoadPage();
        }


        //上一页
        private void btnPrev_Click(object sender, EventArgs e)
        {
            if (currentPage == 1) return;
 
            currentPage--;
            LoadPage();
        }


        //下一页
        private void btnNext_Click(object sender, EventArgs e)
        {
            if (currentPage == pageCount) return;
            currentPage++;
            LoadPage();
        }

        //尾页
        private void btnLast_Click(object sender, EventArgs e)
        {
            if (currentPage == pageCount) return;
            currentPage = pageCount;
            LoadPage();
        }

        private void button1_Click(object sender,EventArgs e) {
            pageSize = (int)numericUpDown1.Value;
            LoadPage();
        }


        private void numericUpDown1_ValueChanged(object sender,EventArgs e) {
 
            //label1.Text = $"每页显示：{(int)numericUpDown1.Value}";
            pageSize = (int)numericUpDown1.Value;
            LoadPage();
        }
 

        private void dgv1_SelectionChanged(object sender,EventArgs e) {

            richTextBox1.Text = id+ "
"+ _dc_单词 + "
" + _yb_音标 + "
" + _js_解释;
            btn_第一页.Enabled = currentPage != 1;
            btn_最后一页.Enabled = currentPage != pageCount;
            btn_上一页.Enabled =  currentPage != 1;
            btn_下一页.Enabled = currentPage != pageCount;

        }


        #region 表格字段属性

        /// <summary>
        ///  dgv1.CurrentCell.RowIndex
        /// </summary>
        public int SeletedIndex {
            get {
                //dgv1.CurrentCell.RowIndex
                return dgv1.CurrentCell != null ? dgv1.CurrentRow.Index : -1;
            }

            set {
                //dgv1.Focus();
                //dgv1.MultiSelect = false;
                //dgv1.ClearSelection();
                //dgv1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
                if (dgv1.CurrentCell == null) return;
                if (value < 0) return;
                if (value < dgv1.RowCount) {
                    //dgv1.Rows[value].Selected = true;
                    dgv1.CurrentCell = dgv1.Rows[value].Cells["单词"];
                    //dgv1.CurrentRow.Cells[value].Value = true;

                }


                //dgv1_SelectionChanged(null,null);
            }
        }

        /// <summary>
        /// 当前ID dgv1.CurrentRow.Cells["id"].Value.ToString()
        /// </summary>
        public string id {
            get {
                return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["id"].Value.ToString() : "";
            }
            set {
                if (dgv1.CurrentCell != null)
                    dgv1.CurrentRow.Cells["id"].Value = value;
            }
        }


        /// <summary>
        /// 当前的单词 dgv1.CurrentRow.Cells["单词"].FormattedValue.ToString()
        /// </summary>
        public string _dc_单词 {
            get {
                return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["单词"].FormattedValue.ToString() : "";
            }
            set {
                if (dgv1.CurrentCell != null)
                    dgv1.CurrentRow.Cells["单词"].Value = SqlString.Transform(value);
            }
        }


        /// <summary>
        /// 当前解释 dgv1.CurrentRow.Cells["解释"].FormattedValue.ToString()
        /// </summary>
        public string _js_解释 {
            get {
                return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["解释"].FormattedValue.ToString() : "";
            }
            set {
                if (dgv1.CurrentCell != null)
                    dgv1.CurrentRow.Cells["解释"].Value = SqlString.Transform(value);
            }
        }

        /// <summary>
        /// 当前例句  dgv1.CurrentRow.Cells["例句"].FormattedValue.ToString()
        /// </summary>
        public string _lj_例句 {
            get {
                return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["例句"].FormattedValue.ToString() : "";
            }
            set {
                if (dgv1.CurrentCell != null)
                    dgv1.CurrentRow.Cells["例句"].Value = SqlString.Transform(value);
            }
        }

        /// <summary>
        /// 当前的音标 dgv1.CurrentRow.Cells["音标"].FormattedValue.ToString()
        /// </summary>
        public string _yb_音标 {
            get {
                return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["音标"].FormattedValue.ToString() : "";
            }
            set {
                if (dgv1.CurrentCell != null)
                    dgv1.CurrentRow.Cells["音标"].Value = SqlString.Transform(value);
            }
        }

        /// <summary>
        /// 图片路径  dgv1.CurrentRow.Cells["图片"].Value.ToString()  12321.jpg 图片放在collection_files目录下
        /// </summary>
        public string _imagefile {
            get {
                return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["图片"].Value.ToString() : "";
            }
            set {
                if (dgv1.CurrentCell != null)
                    dgv1.CurrentRow.Cells["图片"].Value = value;
            }
        }

        ///// <summary>
        ///// 图片的完整路径 db.Collection_files + "\" + _imagePath
        ///// </summary>
        //public string _image_FullFileName => db.Collection_files + "\" + _image_相对图片;


        /// <summary>
        /// 正确次数 dgv1.CurrentRow.Cells["正确次数"].Value
        /// </summary>
        public int zqCount {
            get {
                return dgv1.CurrentCell != null ? Convert.ToInt32(dgv1.CurrentRow.Cells["正确次数"].Value) : 0;
            }
            set {
                if (dgv1.CurrentCell != null)
                    dgv1.CurrentRow.Cells["正确次数"].Value = value;
            }
        }

        /// <summary>
        /// 练习次数 dgv1.CurrentRow.Cells["练习次数"].Value
        /// </summary>
        public int lxCount {
            get {
                return dgv1.CurrentCell != null ? Convert.ToInt32(dgv1.CurrentRow.Cells["练习次数"].Value) : 0;
            }
            set {
                if (dgv1.CurrentCell != null)
                    dgv1.CurrentRow.Cells["练习次数"].Value = value;
            }
        }

        ///// <summary>
        ///// 创建日期 dgv1.CurrentRow.Cells["创建日期"].Value.ToString() 
        ///// </summary>
        //public string _createTime {
        //    get {
        //        return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["创建日期"].Value.ToString() : db.Now;
        //    }
        //    set {
        //        if (dgv1.CurrentCell != null)
        //            dgv1.CurrentRow.Cells["创建日期"].Value = value;
        //    }
        //}
        ///// <summary>
        ///// 修改日期  dgv1.CurrentRow.Cells["修改日期"].Value.ToString()
        ///// </summary>
        //public string _modifyTime {
        //    get {
        //        return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["修改日期"].Value.ToString() : db.Now;
        //    }
        //    set {
        //        if (dgv1.CurrentCell != null)
        //            dgv1.CurrentRow.Cells["修改日期"].Value = value;
        //    }
        //}

        ////todo::
        //public string _nextLearn {
        //    get {
        //        return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["练习日期"].Value.ToString() : db.Now;
        //    }
        //    set {
        //        if (dgv1.CurrentCell != null)
        //            dgv1.CurrentRow.Cells["练习日期"].Value = value;
        //    }
        //}

        /// <summary>
        /// 表格中的难度等级  dgv1.CurrentRow.Cells["难度"].Value.ToString() 
        /// </summary>
        public string HardLevel {
            get {
                return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["难度"].Value.ToString() : "未学";
            }
            set {
                if (dgv1.CurrentCell != null)
                    dgv1.CurrentRow.Cells["难度"].Value = value;
            }
        }

        public string _zu {
            get {
                return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["组"].Value.ToString() : "";
            }
            set {
                if (dgv1.CurrentCell != null)
                    dgv1.CurrentRow.Cells["组"].Value = value;
            }
        }

        /// <summary>
        /// _wav_相对声音 相对 collection_files 目录下的声音 12321.wav
        /// </summary>
        public string _wavfile {
            get {
                return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["声音"].Value.ToString() : "";
            }
            set {
                if (dgv1.CurrentCell != null)
                    dgv1.CurrentRow.Cells["声音"].Value = value;
            }
        }

        ///// <summary>
        ///// wav的完整路径  db.Collection_files + "\" + _wav_相对声音;
        ///// </summary>
        //public string _wav_FullFileName => db.Collection_files + "\" + _wav_相对声音;


        /// <summary>
        /// 备注
        /// </summary>
        public string _note {
            get {
                return dgv1.CurrentCell != null ? dgv1.CurrentRow.Cells["备注"].Value.ToString() : "";
            }
            set {
                if (dgv1.CurrentCell != null)
                    dgv1.CurrentRow.Cells["备注"].Value = value;
            }
        }


        #endregion
    }
}

SQLITE HELPER

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Forms;

namespace System.Data.SQLite {



    class SqlString {



        /*
         *  [UPDATE 2019年10月9日 22:53:51]
         *      ,
         *      编码解码可以使用 HttpUtility
         *      //编码
                textBox4.Text = HttpUtility.HtmlEncode(textBox4.Text);
                //解码
                textBox4.Text = HttpUtility.HtmlDecode(textBox4.Text);
         */
        /*string.Replace 方法莫名其妙会多个 
*/
        public static string Transform(string s) {
            if (s == null)
                return "";
            //普通字符变换成转义字符                                         
            s = Regex.Replace(s,"&","&",RegexOptions.IgnoreCase);
            s = Regex.Replace(s,"<","&lt;",RegexOptions.IgnoreCase);
            s = Regex.Replace(s,">","&gt;",RegexOptions.IgnoreCase);
            s = Regex.Replace(s,"'","&apos;",RegexOptions.IgnoreCase);
            s = Regex.Replace(s,""","&quot;",RegexOptions.IgnoreCase);

            //new    2019年10月23日 20:47:40
            //我的单词是以逗号区分的所以不能出现逗号
            //s = Regex.Replace(s, ",", "&comma;", RegexOptions.IgnoreCase);
            return s;
        }

        //还原原来字符
        public static string Restore(string s) {
            //转义字符变换成普通字符
            s = Regex.Replace(s,"&lt;","<",RegexOptions.IgnoreCase);
            s = Regex.Replace(s,"&gt;",">",RegexOptions.IgnoreCase);
            s = Regex.Replace(s,"&apos;","'",RegexOptions.IgnoreCase);
            s = Regex.Replace(s,"&quot;",""",RegexOptions.IgnoreCase);
            s = Regex.Replace(s,"&amp;","&",RegexOptions.IgnoreCase);
            s = Regex.Replace(s,"&comma;",",",RegexOptions.IgnoreCase);
            return s;
        }
    }

    public class SQLiteHelper
    {
        public SQLiteHelper(string conStr)
        {
            ConnectionString = conStr;
        }
      
        /// <summary>
        /// 数组库文件的名称
        /// </summary>
        public string db = "";

        /// <summary>
        /// SQLITE连接字符串
        /// </summary>
        public string ConnectionString { get; set; }


        /*
         * SQLiteParameter 
         * DataSet 
         * ExecuteScalar
         * https://www.cnblogs.com/yukaizhao/archive/2008/12/01/sqlitehelper.html
         * 
         */

        ///<summary>
        /// 压缩体积、优化体积 ，使用此方法可以减小数据库的体积[如果文件进行了删除操作]
        /// </summary>
        public void Vacuum()
        {
            /*
             * https://blog.csdn.net/HardWorkingAnt/article/details/70667104
             */
            ExecuteNoneQuery("vacuum");
        }

        /// <summary>
        ///  string cmdText = "SELECT * FROM [单词本] ";  
        ///  private DataSet dataSet1 = DataAdapter(cmdText, "单词本");    //单词本 是数据库的 表 名
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="tablename">数据库表的名字</param>
        /// <returns></returns>
        public DataSet DataAdapter(string commandText,string tablename) {
            DataSet ds = new DataSet();
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString)) {
                conn.Open();
                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(commandText,ConnectionString)) {
                    if (string.Empty.Equals(tablename)) {
                        adapter.Fill(ds);
                    } else {
                        adapter.Fill(ds,tablename);
                    }
                }
            }
            return ds;
        }


        /*  示例
              using (SQLiteConnection conn = new SQLiteConnection(_sqlite.connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(conn))
                {
                    conn.Open();
                    cmd.CommandText = "SELECT* FROM [单词本] ORDER BY random()";
                    SQLiteDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        Application.DoEvents();
                        string word = (reader["单词"] != DBNull.Value) ? reader["单词"].ToString() : "";//单词
                        //string uk = (dataReader["uk"] != DBNull.Value) ? dataReader["uk"].ToString() : "";//英标 英式
                        //uk = SqlString.Restore(uk);
                        string js = (reader["解释"] != DBNull.Value) ? reader["解释"].ToString() : "";//解释
                        string tag = (reader["Tag"] != DBNull.Value) ? reader["Tag"].ToString() : "";//单词分类
                        int progress = (reader["熟练度"] != DBNull.Value) ? (int)reader["熟练度"] : 0;//掌握度

                        //TODO: 500个卡的要死闪的厉害
                        ClassListView.AddItem(listView1, word, js, Convert.ToString(progress));
                    }

                }
            }
             */
        public SQLiteDataReader ExecuteReader(string sql) {
            SQLiteConnection conn = new SQLiteConnection(ConnectionString);
            SQLiteCommand cmd = new SQLiteCommand(conn);
            conn.Open();
            cmd.CommandText = sql;
            SQLiteDataReader reader = cmd.ExecuteReader();
            return reader;
           // try {
           //     /*
           //* 此处语句不要使用using 否则会错
           //*/
             
           // }
           // catch {
           //     return null;
           // }
        }


        /// <summary>
        ///  string cmdText = "SELECT * FROM [单词本] ";  
        ///  private DataSet dataSet1 = DataAdapter(cmdText, "单词本");    //单词本 是数据库的 表 名
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="tablename">数据库表的名字</param>
        /// <returns></returns>
        public DataSet GetDataSet(string commandText, string tablename)
        {
            DataSet ds = new DataSet();

            ////con = new SQLiteConnection(ConnectionString);
            ////da = new SQLiteDataAdapter("select * from [单词本]", con);
            ////da.Fill(ds, "单词本");
            ////bs1.DataSource = ds.Tables["单词本"];

            //SQLiteConnection con = new SQLiteConnection(ConnectionString);
            //con.Open();
            //SQLiteDataAdapter da = new SQLiteDataAdapter(commandText, con);
            //da.Fill(ds, tablename);


            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(commandText, conn))
                {
                    conn.Open();

                    //if (string.Empty.Equals(tablename))
                    //{
                    //    adapter.Fill(ds);
                    //}
                    //else
                    {
                        adapter.Fill(ds, tablename);
                    }
                }
            }
            return ds;
        }

        /// <summary>
        /// 通过查询SQL语句得到指定字段的值
        ///    string sql = "SELECT * FROM 单词本 WHERE 单词 = '" + word + "'";
        ///    string yb = GetSectionValue(sql, "音标");
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sectionName"></param>
        /// <returns></returns>
        public string GetSectionValue(string sql, string sectionName)
        {
            DataTable dt = GetDataTable(sql);
            return (dt.Rows.Count > 0) ? dt.Rows[0][sectionName].ToString() : "0";
        }

        /*
         * 判断值是不是存在
         *   string sql = $"select * from [配置] where 组名= '12'";
         *    DataTable dt = sqlite.GetDataTable(sql);
         *  if (dt.Rows.Count == 0) 不存在 else 存在
         */
        public bool exists(string sql)
        {
            DataTable dt = GetDataTable(sql);
            return (dt.Rows.Count > 0) ? true : false;
        }

        //public void SetSectionValue(string sql, string sectionName,object value)
        //{
        //    sql = $"";
        //    ExecuteNoneQuery(sql);
        //}



        public DataTable GetDataTable(string sql)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql, conn))
                {
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);
                    return dt;
                }
            }
        }

        /// <summary>
        ///0=执行失败，大于0表示执行成功了 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int ExecuteNoneQuery(string sql)
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
                {
                    using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
                    {
                        conn.Open();
                        return cmd.ExecuteNonQuery();
                    }
                }
            }
            catch
            {
                return -1;

            }
        }

        public int ExecuteNoneQuery(string sqlStr, params SQLiteParameter[] p)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand())
                {
                    try
                    {
                        PrepareCommand(command, conn, sqlStr, p);
                        return command.ExecuteNonQuery();
                    }
                    catch /*(Exception ex)*/
                    {
                        return -99;
                    }
                }
            }
        }

 
        /// <summary>
        /// 参数设置
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="conn"></param>
        /// <param name="sqlStr"></param>
        /// <param name="p"></param>
        private void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string sqlStr, params SQLiteParameter[] p)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                cmd.Parameters.Clear();
                cmd.Connection = conn;
                cmd.CommandText = sqlStr;
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 30;
                if (p != null)
                {
                    foreach (SQLiteParameter parm in p)
                    {
                        cmd.Parameters.AddWithValue(parm.ParameterName, parm.Value);
                    }
                }
            }
            catch /*(Exception ex)*/
            {
                return;
            }
        }


        //public int RecordLength
        //{
        //    get
        //    {
        //        //https://www.w3school.com.cn/sql/sql_func_count.asp
        //        string sql = "select count(单词) from [单词本]";
        //        return (int)SqlQuery(sql);
        //    }
        //}

        /// <summary>
        /// query 直接返回sql查询语句的值 执行只返回一个值的SQL命令
        /// ----------------------------------------------------------- 
        /// 注意： cmd.ExecuteScalar() 返回可能是非int 类型的object类型
        /// 使用下面这种方法要出现异常的
        /// int i =(int)sqlite.ExecuteScalar(sql);  出现异常：System.InvalidCastException: 指定的转换无效。
        /// 正确的写法是
        /// int i = Convert.ToInt32(sqlite.ExecuteScalar(sql));
        /// </summary>
        /// <param name="sql">SQLCommandText</param>
        /// <param name="i">  > 0 执行成功，i=0语句执行失败</param>
        public object ExecuteScalar(string sql)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
                {
                    conn.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// 转换后的SQL格式语句，事物 执行语句语句    这个语句是瞬间完成的即使是1万条也是这样
        /// 一句一句转换成SQL语句是相当的慢的
        /// INSERT INTO 表2 (Keyword,Trans,Phrase) VALUES ('undoubtedly','解释','短语');
        /// </summary>
        /// <param name="sqlArr">每一行都是完整的 INSERT INTO语句，每插入一条</param>
        /// <returns>返回异常的SQL语句</returns>
        public string BatchExecute(string[] sqlArr)
        {
            StringBuilder sb = new StringBuilder();
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                SQLiteCommand cmd = new SQLiteCommand(conn);
                {
                    conn.Open();
                    using (DbTransaction trans = conn.BeginTransaction())
                    {
                        for (int i = 0; i < sqlArr.Length; i++)
                        {
                            try
                            {
                                cmd.CommandText = sqlArr[i];
                                cmd.ExecuteNonQuery();
                            }
                            catch
                            {
                                //下面是插入失败的内容
                                sb.Append(sqlArr[i] + "
");
                                //trans.Rollback();//回滚事务
                                //MessageBox.Show(EX.Message);
                            }
                        }
                        trans.Commit();
                        //richTextBox2.Text = sb.ToString();
                    }
                }
            }
            return sb.ToString();
        }



    
    }

 
}

下载地址：https://download.csdn.net/download/u012663700/14020674

百度网盘：

链接：https://pan.baidu.com/s/1d3bzVfU-JmHq667we6bK7Q
提取码：6e9t