如何在C#中读取SQLite的文本类型为DateTime与Boolean？

Got it, let's break down how to map your SQLite TEXT columns (IsModified and StartTime) to C# bool and DateTime types using SQLite-net. You have two clean approaches to choose from:

Approach 1: In-Class Property Conversion (Simple, No Extra Setup) #

If you want a straightforward solution without adding custom converters, use private backing fields mapped directly to the SQLite columns, then expose public bool/DateTime properties that handle the conversion logic.

Here's how to update your MyMessage class:

public class MyMessage
{
    [PrimaryKey]
    public string Id { get; set; }

    // Private field mapped to the SQLite TEXT column
    private string _isModifiedDb;
    [Column("IsModified")]
    public string IsModifiedDb
    {
        get => _isModifiedDb;
        set => _isModifiedDb = value ?? "false"; // Handle null values from the database
    }

    // Public bool property for your application logic
    public bool IsModified
    {
        get => bool.TryParse(_isModifiedDb, out bool result) ? result : false;
        set => _isModifiedDb = value.ToString().ToLower(); // Match the DB's "true"/"false" format
    }

    // Repeat the pattern for DateTime
    private string _startTimeDb;
    [Column("StartTime")]
    public string StartTimeDb
    {
        get => _startTimeDb;
        set => _startTimeDb = value;
    }

    public DateTime StartTime
    {
        get => DateTime.TryParse(_startTimeDb, out DateTime result) ? result : DateTime.MinValue;
        // Store in ISO 8601 format for reliable, culture-agnostic parsing later
        set => _startTimeDb = value.ToString("o");
    }
}

Your existing query code works exactly as is—SQLite-net will map the IsModifiedDb and StartTimeDb fields to the database columns, and your public properties handle the type conversion automatically.

Approach 2: Custom Type Converters (Clean, Reusable) #

If you need this conversion logic across multiple classes, custom type converters are the way to go. They keep your MyMessage class clean and let you reuse the logic everywhere.

Step 1: Create the Converters #

Implement SQLite-net's ITypeConverter interface for both types:

// Converter for bool ↔ SQLite TEXT
public class BoolFromStringConverter : ITypeConverter
{
    public object ConvertFromDb(object value)
    {
        if (value is string strValue)
        {
            return bool.TryParse(strValue, out bool result) ? result : false;
        }
        return false; // Fallback for null or unexpected values
    }

    public object ConvertToDb(object value)
    {
        if (value is bool boolValue)
        {
            return boolValue.ToString().ToLower(); // Match the DB's lowercase "true"/"false" format
        }
        return "false"; // Default to false if the value isn't a bool
    }

    public Type StorageType => typeof(string);
}

// Converter for DateTime ↔ SQLite TEXT
public class DateTimeFromStringConverter : ITypeConverter
{
    public object ConvertFromDb(object value)
    {
        if (value is string strValue)
        {
            // Use TryParse for safety, or ParseExact if you know your exact date format
            return DateTime.TryParse(strValue, out DateTime result) ? result : DateTime.MinValue;
        }
        return DateTime.MinValue;
    }

    public object ConvertToDb(object value)
    {
        if (value is DateTime dateValue)
        {
            // Store in ISO 8601 format to avoid parsing ambiguity across systems
            return dateValue.ToString("o");
        }
        return string.Empty;
    }

    public Type StorageType => typeof(string);
}

Step 2: Register Converters with Your SQLite Connection #

When creating your SQLiteConnection, register the converters so SQLite-net knows to use them:

SQLiteConnection DbConn = new SQLiteConnection(GetDatabaseFileName());
// Register converters for bool and DateTime types
DbConn.RegisterTypeConverter<bool>(new BoolFromStringConverter());
DbConn.RegisterTypeConverter<DateTime>(new DateTimeFromStringConverter());

Step 3: Simplify Your MyMessage Class #

Now your class can use native C# types directly—no extra backing fields needed:

public class MyMessage
{
    [PrimaryKey]
    public string Id { get; set; }

    public bool IsModified { get; set; }

    public DateTime StartTime { get; set; }
}

Your original query code remains unchanged:

string qry = "Select * from MyMessage";
List<MyMessage> messageList = DbConn.Query<MyMessage>(qry).ToList();

Key Notes to Avoid Issues #

• For IsModified: Ensure your database stores lowercase "true"/"false" (your DEFAULT false already does this). The converters use ToLower() when writing to the DB to match this format.
• For StartTime: Always store dates in a standard format like ISO 8601 (ToString("o")) to avoid parsing errors across different cultures or systems.
• Use TryParse instead of Parse in conversion logic to handle unexpected values gracefully (no runtime crashes if the DB has invalid data).

内容的提问来源于stack exchange，提问作者spspli