VS2022(C#) net6 SQL/SQLite 透過 DataTable & DataRow 資料在記憶體內進行二次SQL語法 搜尋(search)/過濾(filter)

資料來源:https://www.youtube.com/watch?v=ayp3tHEkRc0

https://einboch.pixnet.net/blog/post/248187728
https://docs.microsoft.com/zh-tw/dotnet/api/system.data.datatable.select?view=net-6.0

https://sqlitebrowser.org/dl/ [軟體:SQLiteDatabaseBrowserPortable]

GITHUB: https://github.com/jash-git/VS2022-net6-CS_SQLite_test [這不是本次專案，但可以使用該專案來新增對應測試程式碼]

功能: 減少SQL存取次數&將一開始要用的的所有資料全部放在記憶體中(Embedded System 慣用技巧)

測試VS2022 C#的.net6 使用NuGet安裝 System.Data.SQLite.Core(1.0.115.5)，Dapper(2.0.123)元件

System.Data.SQLite.Core
版本1.0.115.5
作者: SQLite Development Team
專案URL: https://system.data.sqlite.org/

Dapper
版本2.0.123
作者:Sam Saffron,Marc Gravell,Nick Craver
專案URL: https://github.com/DapperLib/Dapper

Code

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

using System.Data.SQLite;//SQLiteConnection
using Dapper;//DynamicParameters
using System.Data;//IDbConnection,DataTable,DataRow
using System.Configuration;//ConfigurationManager

namespace CS_VPOS
{
    public partial class test_buf : Form
    {
        public test_buf()
        {
            InitializeComponent();
        }

        public static string ConnectionStringLoad(string id = "Default")
        {
            return ConfigurationManager.ConnectionStrings[id].ConnectionString;
        }

        //---
        //C# 通用操作語法 ~ for System.Data.SQLite.Core & Finisar.SQLite 元件 
        //https://einboch.pixnet.net/blog/post/248187728
        public SQLiteConnection OpenConn(string Database)//資料庫連線程式
        {
            string cnstr = string.Format("Data Source=" + Database + ";Version=3;New=False;Compress=True;");
            SQLiteConnection icn = new SQLiteConnection();
            icn.ConnectionString = cnstr;
            if (icn.State == ConnectionState.Open) icn.Close();
            icn.Open();
            return icn;
        }
        public void CreateSQLiteDatabase(string Database)//建立資料庫程式
        {
            string cnstr = string.Format("Data Source=" + Database + ";Version=3;New=True;Compress=True;");
            SQLiteConnection icn = new SQLiteConnection();
            icn.ConnectionString = cnstr;
            icn.Open();
            icn.Close();
        }
        public void CreateSQLiteTable(string Database, string CreateTableString)//建立資料表程式
        {
            SQLiteConnection icn = OpenConn(Database);
            SQLiteCommand cmd = new SQLiteCommand(CreateTableString, icn);
            SQLiteTransaction mySqlTransaction = icn.BeginTransaction();
            try
            {
                cmd.Transaction = mySqlTransaction;
                cmd.ExecuteNonQuery();
                mySqlTransaction.Commit();
            }
            catch (Exception ex)
            {
                mySqlTransaction.Rollback();
                throw (ex);
            }
            if (icn.State == ConnectionState.Open) icn.Close();
        }

        public void SQLiteInsertUpdateDelete(string Database, string SqlSelectString)//新增資料程式
        {
            SQLiteConnection icn = OpenConn(Database);
            SQLiteCommand cmd = new SQLiteCommand(SqlSelectString, icn);
            SQLiteTransaction mySqlTransaction = icn.BeginTransaction();
            try
            {
                cmd.Transaction = mySqlTransaction;
                cmd.ExecuteNonQuery();
                mySqlTransaction.Commit();
            }
            catch (Exception ex)
            {
                mySqlTransaction.Rollback();
                throw (ex);
            }
            if (icn.State == ConnectionState.Open) icn.Close();
        }

        public DataTable GetDataTable(string Database, string SQLiteString)//讀取資料程式
        {
            DataTable myDataTable = new DataTable();
            SQLiteConnection icn = OpenConn(Database);
            SQLiteDataAdapter da = new SQLiteDataAdapter(SQLiteString, icn);
            DataSet ds = new DataSet();
            ds.Clear();
            da.Fill(ds);
            myDataTable = ds.Tables[0];
            if (icn.State == ConnectionState.Open) icn.Close();
            return myDataTable;
        }

        /*
            //應用
            //產生SQLite的資料庫文件，副檔名為.db
            CreateSQLiteDatabase("data.db");

            //建立資料表test
            string createtablestring = "create table test (speed double, dist double);";
            CreateSQLiteTable("data.db", createtablestring);

            //插入資料到test表中
            string insertstring = "insert into test (speed,dist) values ('10','100');insert into test (speed,dist) values ('20','200');";
            SQLiteInsertUpdateDelete("data.db", insertstring);

            //讀取資料
            DataTable dt = GetDataTable("data.db", "select * from test");
            dataGridView1.DataSource = dt;  
         */
        //---C# SQLite通用操作語法 ~ for System.Data.SQLite.Core & Finisar.SQLite 元件 

        private void test_buf_Load(object sender, EventArgs e)
        {
            //---
            //C# SQL/SQLite 透過 DataTable & DataRow 資料在記憶體二次SQL語法搜尋
            //https://docs.microsoft.com/zh-tw/dotnet/api/system.data.datatable.select?view=net-6.0
            SQLiteConnection cnn = new SQLiteConnection(ConnectionStringLoad());
            cnn.Open();

            SQLiteDataAdapter da = new SQLiteDataAdapter("select a.sort as product_sort,a.SID as product_sid,a.product_code,a.barcode,a.product_type,a.product_name,a.product_shortname,a.price_mode,a.product_price,a.unit_sid,a.tax_sid,b.category_sid,c.tax_rate,c.tax_type,IFNULL(e.spec_sid,0) as spec_sid,d.spec_name,d.init_product_sid from product_data a JOIN product_category_relation b ON b.product_sid=a.SID LEFT JOIN tax_data c ON c.SID=a.tax_sid LEFT JOIN product_spec_data d ON d.init_product_sid=a.SID AND d.del_flag='N' LEFT JOIN product_spec_relation e ON e.product_sid=a.SID where a.del_flag='N' and a.stop_flag='N' order by a.sort,a.SID ;", cnn);
            DataSet ds = new DataSet();
            ds.Clear();
            da.Fill(ds);

            DataTable myDataTable = new DataTable();
            myDataTable = ds.Tables[0];

            //ds.Clear();
            cnn.Close();

            string expression;
            expression = "spec_sid >0";
            DataRow[] foundRows;
            foundRows = myDataTable.Select(expression);
            // Print column 0 of each returned row.
            for (int i = 0; i < foundRows.Length; i++)
            {
                //Console.WriteLine(foundRows[i][0]);
            }
            //---C# SQL/SQLite 透過 DataTable & DataRow 資料在記憶體二次SQL語法搜尋
        }
    }
}