How to connect a ready-made SQLite database to a C# WinForms project?

Question

SQLite

Good afternoon. I made a database in SQLite, but I just can’t figure out how to connect it to the project, I previously worked in SSMS, where everything was solved with a couple of clicks and all the necessary files were created, right there I just can’t figure out what and how. I went through a bunch of forums but still don't get it. Can anyone suggest how to do this? Or give a link, because I did not find it myself.

Answer 1

#region License

// Разработано: Коротенко Владимиром Николаевичем (Vladimir N. Korotenko)
// email: [email protected]
// skype:vladimir-korotenko 
// https://vkorotenko.ru
// Создано:  09.04.2019 23:04

#endregion

using SpecialForcesDirectory.Code;
using System.IO;

namespace SpecialForcesDirectory.Dbl
{
    public class DataContext
    {
        private static readonly object Lock = new object();
        private static DataContext _ctx;

        public ContentDatabase ContentData { get; private set; }
        public UserDatabase UserDatabase { get; private set; }


        private DataContext()
        {
            ContentData = new ContentDatabase();
            UserDatabase = new UserDatabase();
        }


        public static DataContext Instance
        {
            get
            {
                lock (Lock)
                {
                    if (_ctx != null) return _ctx;
                    _ctx = new DataContext();
                    return _ctx;
                }
            }
        }




      

        public static void Close()
        {
            if (_ctx == null) return;
            lock (Lock)
            {
                if (_ctx.ContentData != null)
                {
                    _ctx.ContentData.Clear().Wait();
                    _ctx.ContentData = null;
                }

                if (_ctx.UserDatabase == null) return;
                _ctx.UserDatabase.Clear().Wait();
                _ctx.UserDatabase = null;
                _ctx = null;
            }
        }

        /// <summary>
        ///     Перезаписывает базу данных при обновлении, корректно закрывая ресурсы.
        /// </summary>
        /// <param name="file"></param>
        public static void UpdateFile(byte[] file)
        {
            var fp = BaseContentDatabase.DatabasePath;
            Close();
            lock (Lock)
            {
                File.WriteAllBytes(fp, file);
                _ctx = new DataContext();
            }
        }

        public static void DropFile()
        {
            var fullPath = BaseContentDatabase.DatabasePath;
            var userDb = BaseUserDatabase.DatabasePath;
            Close();
            lock (Lock)
            {
                File.Delete(fullPath);
                File.Delete(userDb);
            }
        }
    }
}

#region License
// // Разработано: Коротенко Владимиром Николаевичем (Vladimir N. Korotenko)
// // email: [email protected]
// // skype:vladimir-korotenko
// // https://vkorotenko.ru
// // Создано:  13.08.2020 14:07
#endregion

using System.Collections.Generic;
using System.Threading.Tasks;
using SpecialForcesDirectory.Code;
using SpecialForcesDirectory.Models;
using SQLite;

namespace SpecialForcesDirectory.Dbl
{
    public class ContentDatabase : BaseContentDatabase
    {
        #region Category repository
        private async Task<SQLiteAsyncConnection> GetCategoryConn()
        {
            return await GetDatabaseConnection<Category>().ConfigureAwait(false);
        }
        public async Task<IEnumerable<Category>> GetCategories()
        {
            var conn = await GetCategoryConn();
            return await AttemptAndRetry(() => conn.Table<Category>().ToListAsync()).ConfigureAwait(false);
        }
        public async Task<Category> GetCategory(int id)
        {
            var conn = await GetCategoryConn();
            return await AttemptAndRetry(() => conn.GetAsync<Category>(id)).ConfigureAwait(false);
        } 
        #endregion

        #region Content Repository
        private async Task<SQLiteAsyncConnection> GetCtxConn()
        {
            return await GetDatabaseConnection<Content>().ConfigureAwait(false);
        }
        
        public async Task<IEnumerable<Content>> GetItems()
        {
            var conn = await GetCtxConn();
            return await AttemptAndRetry(() => conn.Table<Content>().ToListAsync()).ConfigureAwait(false);
        }

        public async Task<Content> GetItem(int id)
        {
            var conn = await GetCtxConn();
            return await AttemptAndRetry(() => conn.GetAsync<Content>(id)).ConfigureAwait(false);
        }

        public async Task<int> DeleteItem(int id)
        {
            var conn = await GetCtxConn();
            return await AttemptAndRetry(() => conn.DeleteAsync<Content>(id)).ConfigureAwait(false);
        }

        public async Task SaveItem(Content item)
        {
            var conn = await GetCtxConn();
            if (item.Id == 0) await AttemptAndRetry(() => conn.InsertAsync(item)).ConfigureAwait(false);
            await AttemptAndRetry(() => conn.UpdateAsync(item)).ConfigureAwait(false);
        }

        public async Task<IEnumerable<SRawItem>> GetItemsByQuery(string q, int lim)
        {
            var qu = q.ToUpper();
            var list = new List<SRawItem>();
            var conn = await GetCtxConn();
            var result = conn.Table<Content>()
                .Where(x => x.EnableTab1 == 1 && x.Tab1.ToUpper().Contains(qu)
                            || x.EnableTab2 == 1 && x.Tab2.ToUpper().Contains(qu)
                            || x.EnableTab3 == 1 && x.Tab3.ToUpper().Contains(qu)).Take(lim);

            var r = await result.ToListAsync();
            foreach (var content in r)
            {
                var st = GetSt(content, qu);
                var title = await conn.Table<Category>().Where(x => x.Id == content.CatId).FirstAsync();
                var ni = new SRawItem
                {
                    Body = st.Body,
                    CatId = content.CatId.ToString(),
                    Title = title.Title,
                    Id = content.Id.ToString(),
                    MType = "a",
                    Tab = st.Tab
                };
                list.Add(ni);
            }

            return list;
        }

        private static TabStruct GetSt(Content content, string q)
        {
            if (content.Tab1.ToUpper().Contains(q))
                return new TabStruct { Tab = "1", Body = content.Tab1 };
            if (content.Tab2.ToUpper().Contains(q))
                return new TabStruct { Tab = "2", Body = content.Tab2 };
            return new TabStruct { Tab = "3", Body = content.Tab3 };
        }

        private struct TabStruct
        {
            public string Body { get; set; }
            public string Tab { get; set; }
        }

        #endregion

        public async Task Clear()
        {
            await BaseContentDatabase.Close();
        }
    }
}

Insert into the project

<PackageReference Include="sqlite-net-pcl" Version="1.8.116" />

Answer 2

download the sqlite driver
further, either create an ado.net connection yourself, or use the built-in generator of all the necessary classes in the studio (create a connection, create a DataSet in it with clicks, add tables from the database, then use DataBinding to connect form components with data directly in the interface)