C#中如何在DataGridView显示数据库Double类型数据？SQLite加载报错求方案

Hey there! Let's break down your two questions about C# DataGridView and SQLite double-type data—they’re related, but each has specific fixes to get things working smoothly.

1. Displaying Double-Type Data from a Database in C# DataGridView #

Showing double values in a DataGridView is straightforward once you ensure the data is correctly loaded and mapped. Here are the most reliable approaches:

Option 1: Use a DataAdapter to Populate a DataTable (Standard Approach) #

This is the most common method—let the DataAdapter handle the heavy lifting of fetching data and mapping types:

// Assuming you have an open SQLite connection
using (var adapter = new SQLiteDataAdapter("SELECT * FROM YourTable", yourSqliteConnection))
{
    DataTable dataTable = new DataTable();
    adapter.Fill(dataTable);
    // Bind the DataTable to the DataGridView
    dataGridView1.DataSource = dataTable;
}

The DataAdapter will automatically detect the double type from the database (if stored correctly) and render it in the grid. You can also format the column to show decimal places by setting DefaultCellStyle.Format:

dataGridView1.Columns["YourDoubleColumn"].DefaultCellStyle.Format = "N2"; // Shows 2 decimal places

Option 2: Explicitly Define DataGridView Columns (For Full Control) #

If you want to enforce the double type upfront (to avoid auto-inference issues), manually create columns and set their ValueType:

// Clear existing columns first
dataGridView1.Columns.Clear();

// Create a column for your double data
DataGridViewTextBoxColumn doubleColumn = new DataGridViewTextBoxColumn();
doubleColumn.Name = "YourDoubleColumn";
doubleColumn.HeaderText = "Double Value";
doubleColumn.ValueType = typeof(double); // Explicitly set to double
doubleColumn.DefaultCellStyle.Format = "N2";

// Add the column to the grid
dataGridView1.Columns.Add(doubleColumn);

// Now populate the grid (e.g., from a DataTable or list)
dataGridView1.DataSource = yourDataTable;

2. Fixing Exception When Loading Double Data from SQLite to DataGridView #

The fact that switching to Integer works gives us a clue: SQLite’s dynamic typing system is likely storing your "double" values as a different type (like text) instead of a numeric type. Here’s how to fix it:

First: Ensure Your SQLite Column is Defined as REAL #

SQLite’s equivalent of C# double is the REAL type. While DOUBLE is an alias, using REAL avoids ambiguity. When creating your table, use:

CREATE TABLE YourTable (
    Id INTEGER PRIMARY KEY,
    YourDoubleColumn REAL -- Use REAL instead of DOUBLE for clarity
);

Second: Use Parameterized Queries When Inserting Data #

If you’re inserting data with string concatenation (e.g., INSERT INTO ... VALUES ('" + myDouble + "')), SQLite might store the value as text instead of a numeric type. Always use parameterized queries to enforce type mapping:

using (var cmd = new SQLiteCommand("INSERT INTO YourTable (YourDoubleColumn) VALUES (@DoubleValue)", yourSqliteConnection))
{
    // Explicitly set the parameter type to Double
    cmd.Parameters.Add("@DoubleValue", DbType.Double).Value = yourDoubleVariable;
    cmd.ExecuteNonQuery();
}

This ensures SQLite stores the value as a REAL (double) instead of a string.

Third: Explicitly Convert Values When Reading (If You Can’t Fix Insert Logic) #

If you’re working with existing data that’s stored incorrectly, convert the values to double when loading the DataTable:

using (var adapter = new SQLiteDataAdapter("SELECT * FROM YourTable", yourSqliteConnection))
{
    DataTable dataTable = new DataTable();
    adapter.Fill(dataTable);

    // Convert each row's value to double
    foreach (DataRow row in dataTable.Rows)
    {
        if (row["YourDoubleColumn"] != DBNull.Value)
        {
            row["YourDoubleColumn"] = Convert.ToDouble(row["YourDoubleColumn"]);
        }
    }

    dataGridView1.DataSource = dataTable;
}

Fourth: Force the DataGridView Column’s ValueType #

Sometimes the DataGridView might auto-infer the wrong type even if the DataTable is correct. Manually set the column’s type:

dataGridView1.Columns["YourDoubleColumn"].ValueType = typeof(double);

Bonus: Use an ORM Like Dapper for Automatic Type Mapping #

ORMs like Dapper handle SQLite type mapping automatically, reducing manual work. Here’s a quick example:

// Define a model class with a double property
public class YourModel
{
    public int Id { get; set; }
    public double YourDoubleColumn { get; set; }
}

// Query data and bind to the grid
var data = yourSqliteConnection.Query<YourModel>("SELECT * FROM YourTable").ToList();
dataGridView1.DataSource = data;

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