Universal CRUD Sqlite

Foreword

SQLite is a lightweight relational database management system that supports cross-platform operations. It can be embedded into the program without the need for a separate server process or configuration file, reducing the burden of database maintenance and operational complexity. SQLite data is stored in a single file, which is convenient for backup, transmission and sharing, and also easy for version management. SQLite has good performance, reliable stability and rich functions, making it the preferred database solution in many applications and operating systems.
Without further ado, let’s get to the point
Let me take you through adding, deleting, modifying and checking step by step.

Install

Install-Package DbCRUD.SqlLite installation package

Database connection and initialization

 //Database connection
 IDbCRUD testdb = new SqlLiteCRUD($@"Data Source=sqlitedb.db; Cache=Shared");
 

Insert data

int dbcount =testdb.TableExists(tb_custormer)? testdb?.Count(tb_custormer)??0: 0;
 //**Insert object data synchronously
 var customer = new CrudTestModel
 {
    ID = dbcount + 1, ////Entity class, ID is not assigned, defaults to automatic integer numbering based on data type, supports int, long, objectid
    Name = "Object Insert",
    Phones = new string[] { "80000", "90000" },
    Dic = new Dictionary
    {
        { "Name", "Nested Data" },
        { "DDate", DateTime.Now }
    },
    IsActive = true,
 };
 

//**Insert dictionary data synchronously
 var dic1 = new Dictionary
 {
    //{ "ID", 1 }, //***If no ID is specified, a unique ID of int will be automatically compiled during insertion.
    { "Name", "Automatic number insertion" },
    { "Qty", DateTime.Now.Minute},
    { "DDate", DateTime.Now }
 };
 var result11 = testdb.Insert(autoIDData, dic1);
 //**Batch insert list
 List<Dictionary> listdata = new List<Dictionary>();
 int maxid = testdb.Max(dictable);
 for (int i = 0; i < 10; i++)
 {
    maxid++;
    var dic2 = new Dictionary
    {
        { "ID",maxid },
        { "Name", $"Batch Insert{i}" },
        { "Qty", 19+maxid},
        { "DDate", DateTime.Now }
    };
    listdata.Add(dic2);
 }
 var listResult= testdb.Insert(dictable, listdata);
 

Update data

//Before updating
 var updatepre = testdb.Find<Dictionary>(dictable, "ID=2")?.FirstOrDefault();
          
 var updata = new Dictionary
 {
     { "Name", "Update specified field data" },
     { "Qty", 600}
 };
 var upresult = testdb.UpDate(dictable, updata, "ID=2"); //Update the data with ID=2
 Assert.IsTrue(upresult.Stutas);
 //Updated
 var getupdata = testdb.Find<Dictionary>(dictable, "ID=2")?.FirstOrDefault();
 Assert.AreEqual(300, getupdata.GetValueOrDefault("Qty", 0));
 

Update and insert data (data exists and updates, and there is no insertion)

 //** Update or insert data
    var dic1 = new Dictionary
    {
        { "ID", 2 },
        { "Name", "Insert or update a single piece of data" },
        { "Qty", 200},
        { "DDate", DateTime.Now }
    };
    var result= testdb.Upsert(dictable, dic1);
    //** Batch insert or update
    var dic3 = new Dictionary
    {
        { "ID", 3 },
        { "Name", "Batch insert or update" },
        { "Qty", 300},
        { "DDATE", DateTime.Now }
    };
    List<Dictionary> listdata=new List<Dictionary> { dic3,dic1};
    var listresult = testdb.Upsert(dictable, listdata);
 

Query data

 //Find data with id=2
    var databyid = testdb.FindByID<Dictionary>(dictable,2);
    //Find data with Qty>10
    var wheredata = testdb.Find<Dictionary>(dictable, "Qty>10");
    //Data searched by sql statement
    string sqlcmd = $"select * from {dictable}";
    var sqldata = testdb.Find<Dictionary>(sqlcmd);
    //Data to be searched in pages
    var pagedata = testdb.GetPagingData<Dictionary>(dictable, "Qty>10",pageindex:1,pagenumber:10);
 

Delete data

 //**Delete the data with _id=3
    var result = testdb.Delete(dictable,3);
    //**Delete data with qty<30
    var wherresult = testdb.Delete(dictable, "Qty<30");
    //**Use sql statement to delete data with _id=30
    string sql = $"delete {dictable} where _id=30";
    var sqlresult = testdb.Delete(sql);
 

For more usage methods, please go to the warehouse https://gitee.com/lzcode/db-crud

I hope my sharing can help you. Your reward is the motivation for me to continue sharing with you.

It’s possible if you do it!