Working with H2 database from C#

2 min read 07-10-2024

Working with H2 Database from C#: A Comprehensive Guide

Problem: You need to interact with an H2 database from your C# application, but aren't sure how to set up the connection and execute queries.

Simplified: Imagine you have a handy spreadsheet for storing data, but you want to use C# to access and manipulate that data. H2 is like that spreadsheet, and you need a way to connect to it from your C# code.

This article provides a step-by-step guide on working with H2 databases from C# using the H2Driver library. We'll cover connection setup, executing queries, and handling results.

Scenario: Managing a Book Collection

Let's say you want to create a simple application to manage your book collection. You've decided to use an H2 database to store information about your books, including title, author, and genre. Here's a simplified example of how you could interact with the database using C#:

using System;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data;

public class BookManager
{
    private readonly string _connectionString;

    public BookManager(string connectionString)
    {
        _connectionString = connectionString;
    }

    public void AddBook(string title, string author, string genre)
    {
        using (DbConnection connection = new H2.Driver.Jdbc.Connection(_connectionString))
        {
            connection.Open();
            using (DbCommand command = connection.CreateCommand())
            {
                command.CommandText = "INSERT INTO Books (Title, Author, Genre) VALUES (@title, @author, @genre)";
                command.Parameters.AddWithValue("@title", title);
                command.Parameters.AddWithValue("@author", author);
                command.Parameters.AddWithValue("@genre", genre);
                command.ExecuteNonQuery();
            }
        }
    }

    // Other methods for reading, updating, and deleting books
}

Explanation:

Connection: We create a Connection object using the H2 driver and provide the connection string. The connection string includes details like the database location, username, and password.
Command: We create a DbCommand object to execute the SQL statement.
Parameters: We use parameterized queries for security and to prevent SQL injection vulnerabilities.
Execution: The ExecuteNonQuery method executes the SQL statement and inserts the new book record into the database.

Key Points:

H2Driver Library: You need to install the H2Driver library to use H2 databases with C#. You can find it on NuGet.
Connection String: The format of the connection string depends on your H2 database setup. Refer to the H2 documentation for specific details.
Data Access Technologies: While the example uses the basic DbConnection and DbCommand objects, you can also utilize other frameworks like Entity Framework for more structured data access.
Error Handling: Always include robust error handling in your code to gracefully handle any database connection or query errors.

Benefits of H2 Database:

Simplicity: H2 is easy to set up and use, especially for small to medium-sized applications.
Lightweight: It has a small footprint and minimal dependencies.
Embedding: You can embed H2 directly into your application, simplifying deployment.
Open Source: H2 is free to use and modify under the BSD license.

Additional Considerations:

Transaction Management: For complex operations, you'll need to use transactions to ensure data integrity.
Data Type Mapping: Pay attention to data type mapping between C# and H2 to avoid unexpected behavior.
Security: Secure your H2 database appropriately by setting strong passwords and limiting access if needed.

Resources:

H2 Documentation: https://h2database.com/html/
H2Driver NuGet Package: https://www.nuget.org/packages/H2.Driver.Jdbc/

By following these steps and considering these points, you'll be well-equipped to work with H2 databases from your C# applications and manage your data efficiently.

Working with H2 database from C#