【无标题】

最新推荐文章于 2026-06-17 17:13:25 发布

原创最新推荐文章于 2026-06-17 17:13:25 发布 · 411 阅读

7 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#jvm #java #开发语言

C# sqlite 中DateTime的插入及读取。

用sqlite来保存数据，
1），创建了C#程序，并用NuGet 添加了”System.Data.SQLite" 包。
2），测试内容是：创建sqlite 连接，创建sqlite表（包含日期），测试日期数据的插入/读取。

结果，出现异常。
DateTime 类型老是报错误。:“String ‘2024-01-03 16:31:03’ was not recognized as a valid DateTime.”

查询了文档，https://stackoverflow.com/questions/11414399/sqlite-throwing-a-string-not-recognized-as-a-valid-datetime，说是字段的格式问题。

于是尝试将格式修改，可以读取，保存数据了。
测试界面简单，将读出来的数据显示
其中“插入数据” 是button1, ”读取"是button2，”显示数据“是textBox1.

代码如下：

   public partial class Form1 : Form
    {
        SQLiteConnection connection;
        string timformat = "yyyy-MM-dd HH:mm:ss.fff";
        public Form1()
        {
            InitializeComponent();
            SQLiteConnection.CreateFile("MyDatabase.sqlite");
            string connectionString = "Data Source=MyDatabase.sqlite;Version=3";
            connection = new SQLiteConnection(connectionString);
            connection.Open();

        }

        private void button1_Click(object sender, EventArgs e)
        {
            string sql = "Create table if not exists person(time_column timestamp not null default (datetime('now','localtime')), id integer, name text, addr text);;";
            SQLiteCommand command = new SQLiteCommand(sql, connection);
            command.ExecuteNonQuery();

            //DateTime currentTime = DateTime.Now; // 获取当前日期和时间
            string currentTime = DateTime.Now.ToString(timformat);

            command.CommandText = "INSERT INTO person(time_column,id,name,addr) VALUES(" + "\"" + currentTime + "\"" + ",1,'Abc1','Addr1')";
            //command.CommandText = string.Format("INSERT INTO person(time_column,id,name,addr) VALUES({0},1,'Abc1','Addr1')", currentTime);

            command.ExecuteNonQuery();

            currentTime = DateTime.Now.ToString(timformat);
            command.CommandText = "INSERT INTO person(time_column,id,name,addr) VALUES(" + "\"" + currentTime + "\"" + ",2,'Abc2','Addr2')";
            command.ExecuteNonQuery();

        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void button2_Click(object sender, EventArgs e)
        {
            string sql = "SELECT * FROM person";
            SQLiteCommand command = new SQLiteCommand(sql, connection);
            SQLiteDataReader reader = command.ExecuteReader();
            string str = "";
            while (reader.Read())
            {
                str = str + " " + Convert.ToDateTime(reader["time_column"]).ToString(timformat) + " " + reader["id"] + " " + reader["name"] + " " + reader["addr"];
                //str = str + " " + reader["time_column"] + " " + reader["id"] + " " + reader["name"] + " " + reader["addr"];
            }
            textBox1.Text = str;

        }
    }

读取结果正确.
在这里插入图片描述