C# sqlite

最新推荐文章于 2022-10-21 04:37:55 发布

河北一帆

最新推荐文章于 2022-10-21 04:37:55 发布

阅读量184

点赞数

本文链接：https://blog.csdn.net/qq_41816368/article/details/119166859

版权

sqlite的安装：

http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

找到对应的.NET版本，下载bundle版。

项目中添加System.Data.SQLite引用。

创建数据库、创建表、连接数据库、增加数据、查询数据

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data.SQLite;

namespace WpfApp1
{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : Window
    {
        SQLiteConnection dbConnection = null;

        public MainWindow()
        {
            InitializeComponent();

            //CreateDatabase();
            ConnectDatabase();
            //CreatTable();
            //InsertData();
            QueryData();
        }

        private void CreateDatabase()
        {
            string fileName = ".\\database\\testDB.db";
            SQLiteConnection.CreateFile(fileName);
        }

        private void ConnectDatabase()
        {
            string databaseFileName = ".\\database\\testDB.db";
            string connectionString = "data source = " + databaseFileName;
            this.dbConnection = new SQLiteConnection(connectionString);
            dbConnection.Open();
        }

        private void CreatTable()
        {
            string[] colNames = new string[] { "ID", "Name", "Age", "Email" };
            string[] colTypes = new string[] { "INTEGER", "TEXT", "INTEGER", "TEXT" };

            string tableName = "table1";

            string queryString = "CREATE TABLE IF NOT EXISTS " + tableName + "( " + colNames[0] + " " + colTypes[0];

            for (int i = 1; i < colNames.Length; i++)
            {
                queryString += ", " + colNames[i] + " " + colTypes[i];
            }
            queryString += "  ) ";
            SQLiteCommand dbCommand = this.dbConnection.CreateCommand();
            dbCommand.CommandText = queryString;
            SQLiteDataReader dataReader = dbCommand.ExecuteReader();
        }

        private void InsertData()
        {
            for (int i = 0; i < 100; i++)
            {
                string id = i.ToString();
                string name = i.ToString();
                string age = (i + 1).ToString();
                string email = (i + 1).ToString();

                string cmd = "INSERT INTO table1" + " (ID, Name, Age, Email) " + "VALUES" + " ('" + id + "', '" + name + "', '" + age + "', '" + email + "')";
                SQLiteCommand dbCommand = this.dbConnection.CreateCommand();
                dbCommand.CommandText = cmd;
                dbCommand.ExecuteNonQuery();
            }
        }

        private void QueryData()
        {
            string startIndex = 5.ToString();
            string endIndex = 100.ToString();
            string cmd = "SELECT * FROM table1 WHERE ID>=" + startIndex + " AND " + "ID<=" + endIndex;
            SQLiteCommand dbCommand = this.dbConnection.CreateCommand();
            dbCommand.CommandText = cmd;
            SQLiteDataReader reader = dbCommand.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine(reader["Age"]);
            }
            
        }
    }
}

插入数据时判断表是否存在，若存在直接插入，若不存在则先生成表

public void InsertData()
{
    string date = DateTime.Now.ToString("yyyy_MM_dd");
    string tableName = "Data" + date;

    string[] databaseContent = new string[4];
    databaseContent[0] = "0";
    databaseContent[1] = "1";
    databaseContent[2] = "2";
    databaseContent[3] = "3";

    int res = InsertRun(databaseContent, tableName);

    if (res == 0)
    {
        SQLiteCommand creatTableCommand = this.dbConnection.CreateCommand();

        string[] colNames = new string[] { "ID", "Name", "Age", "Email" };
        string[] colTypes = new string[] { "TEXT", "TEXT", "TEXT", "TEXT" };

        string queryString = "CREATE TABLE IF NOT EXISTS " + tableName + "( " + colNames[0] + " " + colTypes[0];

        for (int i = 1; i < colNames.Length; i++)
        {
            queryString += ", " + colNames[i] + " " + colTypes[i];
        }
        queryString += "  ) ";

        creatTableCommand.CommandText = queryString;
        SQLiteDataReader dataReader = creatTableCommand.ExecuteReader();

        int res2 = InsertRun(databaseContent, tableName);
    }
}

public int InsertRun(string[] databaseContent, string tableName)
{
    string cmd = "INSERT INTO " + tableName + " (ID, Name, Age, Email) " + "VALUES" + " ('";
    for (int i = 0; i < databaseContent.Length - 1; i++)
    {
        cmd += databaseContent[i] + "', '";
    }
    cmd += databaseContent[databaseContent.Length - 1] + "')";

    SQLiteCommand dbCommand = this.dbConnection.CreateCommand();
    dbCommand.CommandText = cmd;
    int res = 0;
    try
    {
        res = dbCommand.ExecuteNonQuery();
    }
    catch(Exception ex)
    {
        Console.WriteLine(ex);
    }

    return res;
}