C# 使用SQLite 的基本操作

最新推荐文章于 2026-04-29 07:10:53 发布

原创最新推荐文章于 2026-04-29 07:10:53 发布 · 3.4k 阅读

12 ·

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

标签

#sqlite #c# #数据库

C# 编程同时被 2 个专栏收录

227 篇文章

订阅专栏

数据库

7 篇文章

订阅专栏

这篇博客展示了如何使用C#进行SQLite数据库的操作，包括创建内存数据库、查询版本信息、创建表、插入数据、查询数据以及更新数据。通过示例代码详细解释了每个步骤，是理解SQLite数据库操作的好资源。

该文章已生成可运行项目，

安装System.Data.SQLite.Core库，The official SQLite database engine for both x86 and x64 along with the ADO.NET provider.

1. 获取SQLite 数据库的版本

private void btnQueryDbVersion_Click(object sender, RoutedEventArgs e)
{

            string db = "Data Source=:memory:";
            string stm = "SELECT SQLITE_VERSION()";

            using (var con = new SQLiteConnection(db))
            {

                con.Open();
                using (var cmd = new SQLiteCommand(stm, con))
                {

                    var version = cmd.ExecuteScalar().ToString();
                    txtMessage.Text = $"SQLite version: {version}";
                }
            }
        }

2. 创建表

private string myDb = @"URI=file:SDDb";
static bool CreateTable(string db)
        {
            try
            {

                using (var con = new SQLiteConnection(db))
                {

                    con.Open();
                    using (var cmd = new SQLiteCommand(con))
                    {

                        cmd.CommandText = "DROP TABLE IF EXISTS PrintDataInfo";
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = @"CREATE TABLE PrintDataInfo(id INTEGER PRIMARY KEY, FullSn TEXT, MeterId INT,OrderNr TEXT,TestDate DATETIME)";
                        cmd.ExecuteNonQuery();
                        return true;
                    }
                }
            }

            catch (Exception)
            {
                return false;

            }
        }

private void btnCreateTable\_Click(object sender, RoutedEventArgs e)
{

    if (CreateTable(myDb))
    {

        txtMessage.Text = "数据表创建成功";
    }
    else
    {

        txtMessage.Text = "数据表创建失败";
    }

}

3.插入数据

static bool InsertData(string db, string fullSn, int meterId, string orderNr)
        {
            try
            {
                using (var con = new SQLiteConnection(db))
                {

                    con.Open();
                    using (var cmd = new SQLiteCommand(con))
                    {
                        cmd.CommandText = "INSERT INTO PrintDataInfo(FullSn,MeterId, OrderNr,TestDate) VALUES(@sn, @id,@orderNr,@dt)";
                       cmd.Parameters.AddWithValue("@sn", fullSn);
                       cmd.Parameters.AddWithValue("@id", meterId);
                        cmd.Parameters.AddWithValue("@orderNr", orderNr);
                        cmd.Parameters.AddWithValue("@dt", DateTime.Now);
                        cmd.Prepare();
                        int result = cmd.ExecuteNonQuery();

                        if (result == 1)
                        {
                            return true;
                        }
                        else
                        {
                            return false;
                        }
                    }
                }
            }
           catch (Exception)
            {
                return false;

            }
        }

 private void btnInsertData\_Click(object sender, RoutedEventArgs e)
    {
       if (InsertData(myDb, txtFullSN.Text, Convert.ToInt32(txtMeterID.Text), txtOrderNr.Text))
        {
            txtMessage.Text = "插入数据成功";
        }
        else
        {
            txtMessage.Text = "插入数据失败";

        }

    }

4. 查询数据

static List<string> ReadData(string db)
        {

            List<string> listData = new List<string>();
            using (var con = new SQLiteConnection(db))
            {

                con.Open();
                string stm = "SELECT * FROM PrintDataInfo LIMIT 5";
                using (var cmd = new SQLiteCommand(stm, con))
                {
                    using (SQLiteDataReader rdr = cmd.ExecuteReader())
                    {
                        // print column headers with the data from a database table.
                        Console.WriteLine($"{rdr.GetName(0),-3} {rdr.GetName(1),-8} {rdr.GetName(2),8}");
                        listData.Add($"{rdr.GetName(0)}\\t {rdr.GetName(1)}\\t\\t\\t {rdr.GetName(2)}\\t {rdr.GetName(3)} \\t {rdr.GetName(4)}");

                        while (rdr.Read())
                        {

                            //Console.WriteLine($"{rdr.GetInt32(0)} {rdr.GetString(1)} {rdr.GetInt32(2)} {rdr.GetString(3)}");

                            listData.Add($"{rdr.GetInt32(0)} \\t{rdr.GetString(1)}\\t {rdr.GetInt32(2)}\\t{rdr.GetString(3)} \\t{rdr.GetDateTime(4)}");

                       }

                    }

                }

            }

            return listData;

        }

        private void btnQueryData_Click(object sender, RoutedEventArgs e)
       {

            List<string> datas = ReadData(myDb);
            foreach (var item in datas)
            {
                txtMessage.AppendText(item + "\\r\\n");

            }
        }

5.更新数据

static bool UpdateDataById(string db, int meterId, string Sn, string OrderNr)
        {

            using (var con = new SQLiteConnection(db))
            {

                con.Open();
                using (var cmd = new SQLiteCommand(con))
                {

                    //FullSn,MeterId, OrderNr
                    cmd.CommandText = "UPDATE PrintDataInfo SET FullSn=@sn,OrderNr=@orderNr WHERE MeterId=@id";
                    cmd.Parameters.AddWithValue("@sn", Sn);
                    cmd.Parameters.AddWithValue("@id", meterId);
                    cmd.Parameters.AddWithValue("@orderNr", OrderNr);
                    cmd.Prepare();

                    int r = cmd.ExecuteNonQuery();
                    if (r == 1)
                    {

                        return true;
                    }
                    else
                    {

                        return false;

                    }
                }

            }

        }

        private void btnUpdateData\_Click(object sender, RoutedEventArgs e)
        {

            if (UpdateDataById(myDb, Convert.ToInt32(txtMeterID.Text), txtFullSN.Text, txtOrderNr.Text))
            {

                txtMessage.Text = "更新OK";
            }
            else
            {
                txtMessage.Text = "更新NG";

            }

        }