欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  后端开发

C# Sqlite 本地存储的详情介绍

程序员文章站 2022-06-03 14:21:26
...
1. download sqlite dll from:

http://www.sqlite.org/download.html

windows 版本:

http://www.sqlite.org/2016/sqlite-dll-win32-x86-3130000.zip

2. 拷贝 sqlite3.dll 到 bin 目录

3. 安装nuget: Install-Package sqlite-net
安装后会在项目中找到SQLite.CS和SQLiteAsync.cs
4. demo 代码:
4.1 创建一个抽象的base model :

public abstract class BaseModel
    {
        [PrimaryKey]
        public Guid Id { get; set; }


        protected BaseModel()
        {
            Id = Guid.NewGuid();
        }
    }

4.2 测试model

 public class Book : BaseModel
    {
        public Book()
        {
            
        }
        public Book(string title, string author, int pages)
            :base()
        {
            Title = title;
            Author = author;
            Pages = pages;
        }
        public string Title { get; set; }


        public int Pages { get; set; }


        public string Author { get; set; }


        public override string ToString()
        {
            return string.Format("ID : {0} ,title : {1}, Author : {2}, Pages : {3}", Id, Title, Author, Pages);
        }
    }

4.3 创建helper class

public class SqliteHelper
    {
        private const string DBPath = "Book.db";
        private const int OPERATION_SUCCESS = 1;


        public static bool TryDropTable<T>()
        {
            using (var db = new SQLite.SQLiteConnection(DBPath))
            {
                var success = db.DropTable<T>();
                if (success != OPERATION_SUCCESS)
                {
                    // TODO log error
                }


                return success == OPERATION_SUCCESS;
            }
        }


        public static bool TryCreateTable<T>()
        {
            using (var db = new SQLite.SQLiteConnection(DBPath))
            {
                var table = db.GetTableInfo(typeof (T).Name);
                if(table != null && table.Count > 0)
                {
                    return false;
                }
                db.CreateTable<T>();
                return true;
            }
        }


        public static T Add<T>(T book)
        {
            using (var db = new SQLite.SQLiteConnection(DBPath))
            {
                var success = db.Insert(book);
                if (success != OPERATION_SUCCESS)
                {
                    //TODO error log
                }
            }
            return book;
        }


        public static T DeleteById<T>(Guid id) where T : BaseModel, new()
        {
            using (var db = new SQLite.SQLiteConnection(DBPath))
            {
                var record = db.Table<T>().FirstOrDefault(x => x.Id == id);
                if (record != null)
                {
                    var success = db.Delete(record);
                    if (success != OPERATION_SUCCESS)
                    {
                        // TODO error log
                        return null;
                    }
                    return record;
                }
                else
                {
                    // TODO log : failed to find record with id 'XXX'
                    return null;
                }
            }
        }


        public static T UpdateById<T>(Guid id, T updating) where T: BaseModel, new ()
        {
            using (var db = new SQLite.SQLiteConnection(DBPath))
            {
                var singleRecord = db.Table<T>().FirstOrDefault(x => x.Id == id);
                if (singleRecord != null)
                {
                    singleRecord = updating;
                    singleRecord.Id = id; //restore the id


                    var success = db.Update(singleRecord);
                    if (success != OPERATION_SUCCESS)
                    {
                        // TODO error log
                        return null;
                    }
                    return updating;
                }
                else
                {
                    // TODO log  failed to find record with id 'XXX'
                    return null;
                }
            }
        }


        public static T UpdateBy<T>(Expression<Func<T, bool>> where, T updating) where T:BaseModel, new ()
        {
            using (var db = new SQLite.SQLiteConnection(DBPath))
            {
                var records = db.Table<T>().Where(where).ToList();
                if (records.Count > 0)
                {
                    for (int i = 0; i < records.Count; i++)
                    {
                        var id = records[i].Id;
                        records[i] = updating;
                        records[i].Id = id; // restore id
                    }
                    var success = db.UpdateAll(records);
                    if (success != OPERATION_SUCCESS)
                    {
                        //TODO error log here
                        return null;
                    }
                    return updating;
                }
                return null;
            }
        }


        public static IEnumerable<T> All<T>() where T: new()
        {
            using (var db = new SQLite.SQLiteConnection(DBPath))
            {
                return db.Table<T>().ToList();
            }
        }


        public static IEnumerable<T> GetBy<T>(Expression<Func<T, bool>> prediction) where T: new()
        {
            using (var db = new SQLite.SQLiteConnection(DBPath))
            {
                return Enumerable.ToList(db.Table<T>().Where(prediction));
            }
        }
    }

5. 使用过程

class Program
    {
        static void Main(string[] args)
        {
            SqliteHelper.TryDropTable<Book>();


            // create a table
            var createResult = SqliteHelper.TryCreateTable<Book>();
            Console.WriteLine("First time creating table 'Book' result : " +createResult);


            // try create it again
            createResult = SqliteHelper.TryCreateTable<Book>();
            Console.WriteLine("2nd time creating table 'Book' result : " + createResult);


            // try add record
            var firstRecord = SqliteHelper.Add(new Book("first", "aaa", 100));
            Console.WriteLine("first book added :");
            Console.WriteLine(firstRecord);
            // try update by id
            var updatedFirst = SqliteHelper.UpdateById(firstRecord.Id, new Book("updated first", "aaa_updated", 101));
            Console.WriteLine("updated first record:");
            Console.WriteLine(updatedFirst);


            // add another record 
            var secondRecord = SqliteHelper.Add(new Book("second", "bbb", 200));
            Console.WriteLine("2nd book added :");
            Console.WriteLine(secondRecord);


            // try update by 
            var updatedSecond = SqliteHelper.UpdateBy(
                x=> x.Title == secondRecord.Title && x.Author == secondRecord.Author,
                new Book("updated 2nd", "bbb_updated", 201));


            Console.WriteLine("updated 2nd record:");
            Console.WriteLine(updatedSecond);


            // try delete first
            var deleted = SqliteHelper.DeleteById<Book>(firstRecord.Id);
            Console.WriteLine("First record deleted.");


            // All records :
            Console.WriteLine("all records :");
            var all = SqliteHelper.All<Book>();
            foreach (var record in all)
            {
                Console.WriteLine(record);
            }


            Console.ReadKey();
        }
    }


以上就是C# Sqlite 本地存储的详情介绍的内容,更多相关内容请关注PHP中文网(www.php.cn)!