Hands-on with C# and SQLite: Establishing a Database Connection and Table Structure

• C# Project: Create a new C# project in your preferred IDE (e.g., Visual Studio).

Steps:

1. Reference the Library:

   • In your project, right-click on "References" and select "Add Reference...".
   • Choose ".NET Framework" or ".NET Core" (depending on your project type) and locate "System.Data.SQLite." Add it as a reference.

2. Establish Connection:

   • Import the System.Data.SQLite namespace at the top of your code file:

   using System.Data.SQLite;
   

   • Create a connection string specifying the database file path (replace "mydatabase.db" with your desired name):

   const string connectionString = @"Data Source=mydatabase.db;Version=3;";
   

3. Create the Database (if needed):

   • Use a try...catch block to handle potential errors:

   try
   {
       using (var connection = new SQLiteConnection(connectionString))
       {
           connection.Open(); // Open the connection
       }
   }
   catch (SQLiteException ex)
   {
       // Handle connection error
   }
   

   This code attempts to open a connection to the database file. If the file doesn't exist, SQLite will create it automatically.

4. Create a Table:

   • Construct a CREATE TABLE SQL statement defining your table structure:

   string createTableQuery = @"
       CREATE TABLE IF NOT EXISTS MyTable (
           ID INTEGER PRIMARY KEY AUTOINCREMENT,
           Name TEXT NOT NULL,
           Age INTEGER
       );
   ";
   

   • This statement creates a table named MyTable with three columns:

     • ID: An integer that automatically increments (primary key)
     • Name: Text data (not null)
     • Age: An integer

   using (var connection = new SQLiteConnection(connectionString))
   {
       connection.Open();
   
       using (var command = new SQLiteCommand(createTableQuery, connection))
       {
           command.ExecuteNonQuery(); // Execute the CREATE TABLE statement
       }
   }
   

Complete Example:

using System.Data.SQLite;

public class CreateSqliteDb
{
    private const string connectionString = @"Data Source=mydatabase.db;Version=3;";

    public static void CreateDatabaseAndTable()
    {
        try
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                string createTableQuery = @"
                    CREATE TABLE IF NOT EXISTS MyTable (
                        ID INTEGER PRIMARY KEY AUTOINCREMENT,
                        Name TEXT NOT NULL,
                        Age INTEGER
                    );
                ";

                using (var command = new SQLiteCommand(createTableQuery, connection))
                {
                    command.ExecuteNonQuery();
                }

                Console.WriteLine("Database and table created successfully!");
            }
        }
        catch (SQLiteException ex)
        {
            Console.WriteLine("Error creating database and table: " + ex.Message);
        }
    }

    public static void Main(string[] args)
    {
        CreateDatabaseAndTable();
    }
}

Explanation:

• This code defines a class CreateSqliteDb with a CreateDatabaseAndTable method.
• It creates a connection to the database using the connection string.
• It executes the CREATE TABLE statement using a SqliteCommand.
• The IF NOT EXISTS clause ensures the table is created only if it doesn't already exist.
• Error handling is included using a try...catch block.

Remember:

• Replace "mydatabase.db" with your desired database file name.
• You can modify the createTableQuery to create tables with different structures.

using System.Data.SQLite;

public class CreateSqliteDb
{
    private const string connectionString = @"Data Source=mydatabase.db;Version=3;";

    public static void CreateDatabaseAndTable()
    {
        try
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();  // Open the connection

                // Create table (modify this query for your specific table structure)
                string createTableQuery = @"
                    CREATE TABLE IF NOT EXISTS Users (
                        ID INTEGER PRIMARY KEY AUTOINCREMENT,
                        Username TEXT NOT NULL UNIQUE,  // Unique username constraint
                        Email TEXT NOT NULL UNIQUE,    // Unique email constraint
                        Password TEXT NOT NULL          // Password should be hashed in practice
                    );
                ";

                using (var command = new SQLiteCommand(createTableQuery, connection))
                {
                    command.ExecuteNonQuery();
                }

                Console.WriteLine("Database and table created successfully!");
            }
        }
        catch (SQLiteException ex)
        {
            Console.WriteLine("Error creating database and table: " + ex.Message);
        }
    }

    public static void Main(string[] args)
    {
        CreateDatabaseAndTable();
    }
}

Improvements:

• Table Structure: The createTableQuery now defines a Users table with:
  • ID (primary key)
  • Username (text, not null, unique)
  • Password (text, not null) (Important: Store passwords securely using hashing, not plain text)
• Unique Constraints: UNIQUE constraints are added to Username and Email to ensure no duplicate entries.
• Comments: Comments are included to explain the purpose of each code section.

• Adjust the createTableQuery to match your desired table structure.
• Implement proper password hashing for security before storing passwords in the database.

If you're using Entity Framework Core (EF Core) for data access in your C# project, you can leverage its capabilities to create the database and tables. Here's a basic example:

using Microsoft.EntityFrameworkCore;
using YourDataModelNamespace; // Replace with your data model namespace

public class MyDbContext : DbContext
{
    public DbSet<MyEntity> MyEntities { get; set; } // Replace with your entity class

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite(@"Data Source=mydatabase.db;Version=3;");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<MyEntity>(entity => // Define your table structure here
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.Name).IsRequired();
            // ... define other properties
        });
    }
}

public static void CreateDatabaseWithEFCore()
{
    using (var context = new MyDbContext())
    {
        context.Database.EnsureCreated(); // Creates the database and tables if they don't exist
    }
}

• You define a DbContext class (MyDbContext) that inherits from DbContext.
• Configure the connection string in OnConfiguring.
• Define your entity class (MyEntity) representing the table structure in OnModelCreating.
• Use context.Database.EnsureCreated() to create the database and tables if they don't exist.

Benefits:

• Enforces data model through entities.
• Simplified database interaction.

Drawbacks:

• Requires additional setup with EF Core libraries.
• Might be overkill for simple applications.

Dapper (Micro-ORM):

Dapper is a lightweight micro-ORM that allows you to execute SQL queries and map results to objects. You can use it for creating databases and tables with raw SQL:

using Dapper;
using System.Data.SQLite;

public static void CreateDatabaseWithDapper()
{
    const string connectionString = @"Data Source=mydatabase.db;Version=3;";

    string createTableQuery = @"
        CREATE TABLE IF NOT EXISTS MyTable (
            ID INTEGER PRIMARY KEY AUTOINCREMENT,
            Name TEXT NOT NULL,
            Age INTEGER
        );
    ";

    using (var connection = new SQLiteConnection(connectionString))
    {
        connection.Open();
        connection.Execute(createTableQuery);
    }
}

• Similar to the basic System.Data.SQLite approach, you use raw SQL with Dapper's connection.Execute method.

• Lightweight and less configuration compared to EF Core.
• More direct control over SQL statements.

• More manual code for complex data access.
• Less abstraction compared to ORMs.

Third-party libraries:

Several third-party libraries like FluentMigrator or SQLite.NET can simplify database migrations and table creation in C#. Explore these options if you need more advanced features or a specific framework integration.

Choosing the right method:

• For simple applications with basic database needs, the System.Data.SQLite approach is a good starting point.
• If you already use EF Core for data access, leveraging it for database creation is efficient.
• Consider Dapper for lightweight projects or if you prefer direct SQL control.
• Explore third-party libraries for advanced features or framework-specific integrations.