Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have been fiddling with this code for 4 hours
I am trying to create a SQLite Database
I will Post the article I have been following and my code

What I have tried:

Using SQLite in C#/VB.Net[^]

namespace Contacts
{
    public partial class frmStart : Form
    {
        string connStr = "Data Source={0};Version=3;";
        SQLiteConnection conn = new SQLiteConnection("Data Source=database.sqlite;Version=3;");
        
        public frmStart()
        {
            InitializeComponent();
        }
        public void btnCreateDB_Click(object sender, EventArgs e)
        {
            makeDB();
        }
       
        public void makeDB()
        {
            if (!File.Exists("Person.db"))
            {
                try
                {
                    connStr = $"Data Source = '{"Person.db"}';Version=3;";
                    conn.Open();
                   
                }
                catch (Exception)
                {
                    tbMessage.Text = "DB and Tables NOT Created";
                }
            }
        }
Posted
Updated 18-Jan-23 22:16pm

I have written, IMHO, a better implementation for Working with SQLite in C# & VB[^] that does a lot more. It's been used in a number of commercial apps.

With the library from my article, the DbContext has an Exists method for checking if the DB exists and a CreateDB method for creation. Or you can use the DbRegistry wrapper class that has an AutoCreate option when attaching one or more DbContexts - ref: Other Database Functions section[^].

However, these days, I like to use Entity Framework Core[^] and it is quite easy to use.

Here is a sample for EF CORE in a winforms app with auto DB creation:

Part A - Models

A-1. Base EntityObject
C#
public interface IEntityObject
{
    long Id { get; set; }
}

public abstract class EntityObject : IEntityObject
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public virtual long Id { get; set; }
}

A-2 PersonDTO model:
C#
public class PersonDTO : EntityObject
{
    public PersonDTO
    (
        string firstName,
        string lastName,
        string? email = null,
        string? gender = null,
        string? phone = null)
    {
        FirstName = firstName;
        LastName = lastName;

        Email = email ?? "";
        Gender = gender ?? "";
        Phone = phone ?? "";
    }

    [JsonPropertyName("first_name")]
    public string FirstName { get; set; }

    [JsonPropertyName("last_name")]
    public string LastName { get; set; }

    [JsonPropertyName("email")]
    public string Email { get; set; }

    [JsonPropertyName("gender")]
    public string Gender { get; set; }

    [JsonPropertyName("phone")]
    public string Phone { get; set; }
}

NOTE: I am pulling data from a JSON file, so all properties have property name mappings.

Part B - DataBase using EF CORE

B-1. DTO Model Configuration

B-1a. EntityTypeConfiguration<T> Base Configuration - for use with any DTO configuration:
C#
public class EntityTypeConfiguration<TEntity> : IEntityTypeConfiguration<TEntity>
    where TEntity : class
{
    #region Constructor

    public EntityTypeConfiguration() { /* skip */ }

    #endregion

    #region Properties

    #endregion

    #region Methods

    public virtual void Configure(EntityTypeBuilder<TEntity> builder)
    {
        const string DefaultSql = "DATETIME('now')";

        builder.Property<DateTime>("Created")
            .IsRequired()
            .ValueGeneratedOnAdd()
            .HasDefaultValueSql(DefaultSql);

        builder.Property<DateTime>("LastUpdated")
            .IsRequired()
            .ValueGeneratedOnAddOrUpdate()
            .HasDefaultValueSql(DefaultSql);
    }

    #endregion
}

Here we are automatically adding Created & LastUpdated columns to every table.

B-1b. PersonConfiguration - adding indexes to tables
C#
public class PersonConfiguration : EntityTypeConfiguration<PersonDTO>
{
    public override void Configure(EntityTypeBuilder<PersonDTO> builder)
    {
        builder.HasIndex(person => person.FirstName);
        builder.HasIndex(person => person.LastName);
        builder.HasIndex(person => person.Email);

        base.Configure(builder);
    }
}


B-2. Setting up the DbContext - represents a session with the database
C#
public class AppDbContext : DbContext
{
    public AppDbContext()
    {
        Database.EnsureCreated();
    }

    public DbSet<PersonDTO> Persons { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // @"data source=Contact.db;version=3"
        //optionsBuilder.UseSqlite(ConfigurationManager.ConnectionStrings["SqliteDB"].ConnectionString);
        optionsBuilder.UseSqlite(@"data source=Contact.db");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        var types = modelBuilder.Model.GetEntityTypes().Select(t => t.ClrType).ToHashSet();

        modelBuilder.ApplyConfigurationsFromAssembly(
            Assembly.GetExecutingAssembly(),
            t => t.GetInterfaces()
                .Any(i => i.IsGenericType
                          && i.GetGenericTypeDefinition() == typeof(IEntityTypeConfiguration<>)
                          && types.Contains(i.GenericTypeArguments[0]))
        );
    }
}

NOTES:
* The constructor manages the creation of the DB based on the connection string in the OnConfiguring method. The OnConfiguring is also defining that we are using SQLite as our DB system.
* OnModelCreating method is using discovery to pull in all of the configuration files - eg: PersonConfiguration

Part C - Wiring up the Form1 code-behind
C#
public partial class Form1 : Form
{
    private AppDbContext dbContext = new();

    public Form1()
    {
        InitializeComponent();
        InitData();
    }

    private void InitData()
    {
        var dbPersons = dbContext.Set<PersonDTO>();

        int count = dbPersons.Count();

        if (count == 0)
        {
            IEnumerable<PersonDTO> persons = LoadData();
                
            dbPersons.AddRange(persons);
                
            dbContext.SaveChanges();
        }

        dataGridView1.DataSource = dbPersons.ToList();
    }

    private IEnumerable<PersonDTO> LoadData()
    {
        string file = Path.Combine(Environment.CurrentDirectory, "MOCK_DATA.json");

        string rawJson = File.ReadAllText(file);

        return JsonSerializer.Deserialize<IEnumerable<PersonDTO>>(rawJson)!;
    }
}

NOTES:
* InitData method gets a refernce to the Persons table, checks if there are any records. If non, will then load from a JSON file and save to the DB.
* InitData will them point the DataGrid to the table and retrieve all Persons.

Here is a link to the project: WinFormsSqliteEF.zip - Google Drive[^]

If you want to learn more about Entity Framework Core, here is a great tutorial website: Entity Framework Tutorial[^]

Enjoy!
 
Share this answer
 
v3
This code structure is not great but it does create the Database
Any suggestions on how to change the code structure would be most welcomed
I did learn how to capture the Exception which aided in the rewrite of the code

namespace Contacts
{
    public partial class frmStart : Form
    {
        static string dbConnectionString = @"Data Source=Person.db;Version=3;";
        SQLiteConnection sqlite_con = new SQLiteConnection(dbConnectionString);
        public frmStart()
        {
            InitializeComponent();
        }

        public void btnCreateDB_Click(object sender, EventArgs e)
        {
            makeDB();
        }

        public void makeDB()
        {
            if (!File.Exists("Person.db"))
            {
                btnCreateDB.Visible = true;
                btnVLink.Visible = false;
                Width = 300;
                Height = 150;
                Refresh();
                
                try
                {
                    //string dbConnectionString = @"Data Source=Person.db;Version=3;";
                    //SQLiteConnection sqlite_con = new SQLiteConnection(dbConnectionString);
                    sqlite_con.Open();
                }
                catch (Exception ex)
                {
                    // MessageBox.Show(ex.ToString());
                    tbMessage.Text = ex.ToString();
                    sqlite_con.Close();
                } 
            }
            else
            {
                Width = 400; // Was 600 400
                Height = 300;
                Refresh();
                btnCreateDB.Visible = false;
                btnVLink.Visible = true;
            }
        }
 
Share this answer
 
Comments
Graeme_Grant 19-Jan-23 5:12am    
If you do not want to use EF Core, download from my article and you will see that I have abstracted key code into classes. eg:
using (var dbCtx = new DbContext(connectionString))
{
    // wraps the database connection and automatically
    //  releases resources avoiding memory leaks
}

FYI, learn EF Core, and you have all supported databases covered: MsSql, MySql, PostgreSql, Oracle, ..., SqLite. Also beneficial if you want to migrate to another DB away from SqLite...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900