WPF应用连接SQLite遇文件存在报错求解决（考试需求）

开发学校考试用WPF应用，采用Microsoft.Data.Sqlite包连接SQLite，运行时出现「文件名不存在」报错但文件实际存在，应用能启动但不久后崩溃，相关代码如下：

namespace WpfApp2
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        SqliteConnection connection;
        ObservableCollection<Orszag> dataList = new();
        public MainWindow()
        {
            InitializeComponent();
        }

        private void letrehozButton_Click(object sender, RoutedEventArgs e)
        {
            connection = new($"Filename=adatok.db");
            connection.Open();
            string createTableText = "CREATE TABLE IF NOT EXISTS orszagok(id INTEGER PRIMARY KEY AUTOINCREMENT, nev VARCHAR(100), terulet INTEGER, nepesseg INTERGER, fovaros VARCHAR(100), fovarosNepesseg INTERGER)";
            SqliteCommand command = new(createTableText, connection);
            command.ExecuteNonQuery();
            foreach (var item in File.ReadAllLines("adatok-utf8.txt", Encoding.UTF8).Skip(1))
            {
                string[] parts = item.Split(';');
                string orszag = parts[0];
                int terulet = Convert.ToInt32(parts[1]);
                long nepesseg;
                if (parts[2].EndsWith('g'))
                {
                    parts[2] = parts[2].Trim('g');
                    nepesseg = Convert.ToInt64(parts[2]) * 10000;
                }
                else
                {
                    nepesseg = Convert.ToInt64(parts[2]);
                }
                string fovaros = parts[3];
                int fovarosnepesseg = Convert.ToInt32(parts[4]);
                string insertintotext = $"INSERT INTO orszagok(nev, terulet, nepesseg, fovaros, fovarosNepesseg) VALUES('{orszag}', '{terulet}', '{nepesseg}', '{fovaros}', '{fovarosnepesseg}')";
                command = new(insertintotext, connection);
                command.ExecuteNonQuery();
            }
            connection.Close();
        }

        private void readToTable_Click(object sender, RoutedEventArgs e)
        {
            connection = new($"Filename=adatok.db");
            connection.Open();
            string queryText = "SELECT * FROM orszagok";
            SqliteCommand command = new(queryText, connection);
            SqliteDataReader reader = command.ExecuteReader();
            dataList = new();
            while (reader.Read())
            {
                int id = reader.GetInt32(0);
                string nev = reader.GetString(1);
                int terulet = reader.GetInt32(2);
                long nepesseg = reader.GetInt64(3);
                string fovaros = reader.GetString(4);
                int fovarosNepesseg = reader.GetInt32(5);
                Orszag newElement = new(id, nev, terulet, nepesseg, fovaros, fovarosNepesseg);
                dataList.Add(newElement);
            }
            resultTable.ItemsSource = dataList;
            reader.Close();
        }

        private void deleteButton_Click(object sender, RoutedEventArgs e)
        {
            Orszag selected = resultTable.SelectedItem as Orszag;
            string deleteText = $"DELETE FROM orszagok WHERE id={selected.Id}";
            SqliteCommand command = new(deleteText, connection);
            command.ExecuteNonQuery();
            dataList.Remove(selected);
        }
    }
}

核心错误分析 #

• 相对路径不明确：WPF应用运行时的工作目录默认是项目输出目录（如bin/Debug/net6.0-windows），而非项目根目录，直接使用"adatok.db"会导致找不到文件。
• 连接资源未正确管理：未使用using语句自动释放连接、命令、阅读器资源，容易导致连接泄漏；deleteButton_Click中直接使用类成员connection，该连接可能已被关闭或未初始化，触发崩溃。
• SQL语句拼接风险：直接拼接字符串生成INSERT、DELETE语句，存在SQL注入风险，且若数据含特殊字符（如单引号）会导致语法错误。
• 数据类型拼写错误：CREATE TABLE语句中INTERGER拼写错误，应为INTEGER，虽SQLite有容错性，但可能引发类型处理异常。
• 文本文件路径问题：File.ReadAllLines("adatok-utf8.txt")同样存在相对路径问题，可能无法读取到文本文件。

修复后的代码 #

namespace WpfApp2
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        ObservableCollection<Orszag> dataList = new();
        // 获取应用程序所在目录
        private readonly string _appDirectory = AppDomain.CurrentDomain.BaseDirectory;

        public MainWindow()
        {
            InitializeComponent();
        }

        private void letrehozButton_Click(object sender, RoutedEventArgs e)
        {
            string dbPath = Path.Combine(_appDirectory, "adatok.db");
            string txtPath = Path.Combine(_appDirectory, "adatok-utf8.txt");

            // 使用using自动释放连接
            using (SqliteConnection connection = new SqliteConnection($"Filename={dbPath}"))
            {
                connection.Open();
                // 修正数据类型拼写错误
                string createTableText = "CREATE TABLE IF NOT EXISTS orszagok(id INTEGER PRIMARY KEY AUTOINCREMENT, nev VARCHAR(100), terulet INTEGER, nepesseg INTEGER, fovaros VARCHAR(100), fovarosNepesseg INTEGER)";
                using (SqliteCommand command = new SqliteCommand(createTableText, connection))
                {
                    command.ExecuteNonQuery();
                }

                foreach (var item in File.ReadAllLines(txtPath, Encoding.UTF8).Skip(1))
                {
                    string[] parts = item.Split(';');
                    string orszag = parts[0];
                    int terulet = Convert.ToInt32(parts[1]);
                    long nepesseg;
                    if (parts[2].EndsWith('g'))
                    {
                        parts[2] = parts[2].Trim('g');
                        nepesseg = Convert.ToInt64(parts[2]) * 10000;
                    }
                    else
                    {
                        nepesseg = Convert.ToInt64(parts[2]);
                    }
                    string fovaros = parts[3];
                    int fovarosnepesseg = Convert.ToInt32(parts[4]);

                    // 使用参数化查询避免注入和语法错误
                    string insertintotext = "INSERT INTO orszagok(nev, terulet, nepesseg, fovaros, fovarosNepesseg) VALUES(@nev, @terulet, @nepesseg, @fovaros, @fovarosNepesseg)";
                    using (SqliteCommand command = new SqliteCommand(insertintotext, connection))
                    {
                        command.Parameters.AddWithValue("@nev", orszag);
                        command.Parameters.AddWithValue("@terulet", terulet);
                        command.Parameters.AddWithValue("@nepesseg", nepesseg);
                        command.Parameters.AddWithValue("@fovaros", fovaros);
                        command.Parameters.AddWithValue("@fovarosNepesseg", fovarosnepesseg);
                        command.ExecuteNonQuery();
                    }
                }
            } // 连接自动关闭
        }

        private void readToTable_Click(object sender, RoutedEventArgs e)
        {
            string dbPath = Path.Combine(_appDirectory, "adatok.db");
            using (SqliteConnection connection = new SqliteConnection($"Filename={dbPath}"))
            {
                connection.Open();
                string queryText = "SELECT * FROM orszagok";
                using (SqliteCommand command = new SqliteCommand(queryText, connection))
                using (SqliteDataReader reader = command.ExecuteReader())
                {
                    dataList.Clear(); // 清空旧数据而非重新实例化
                    while (reader.Read())
                    {
                        int id = reader.GetInt32(0);
                        string nev = reader.GetString(1);
                        int terulet = reader.GetInt32(2);
                        long nepesseg = reader.GetInt64(3);
                        string fovaros = reader.GetString(4);
                        int fovarosNepesseg = reader.GetInt32(5);
                        Orszag newElement = new(id, nev, terulet, nepesseg, fovaros, fovarosNepesseg);
                        dataList.Add(newElement);
                    }
                    resultTable.ItemsSource = dataList;
                }
            }
        }

        private void deleteButton_Click(object sender, RoutedEventArgs e)
        {
            Orszag selected = resultTable.SelectedItem as Orszag;
            if (selected == null) return; // 增加空值判断，避免空引用崩溃

            string dbPath = Path.Combine(_appDirectory, "adatok.db");
            using (SqliteConnection connection = new SqliteConnection($"Filename={dbPath}"))
            {
                connection.Open();
                // 参数化删除语句
                string deleteText = "DELETE FROM orszagok WHERE id=@id";
                using (SqliteCommand command = new SqliteCommand(deleteText, connection))
                {
                    command.Parameters.AddWithValue("@id", selected.Id);
                    command.ExecuteNonQuery();
                }
            }
            dataList.Remove(selected);
        }
    }
}

关键注意事项 #

• 文件复制设置：在解决方案资源管理器中，右键adatok.db和adatok-utf8.txt，设置「复制到输出目录」为如果较新则复制，确保文件能被复制到运行目录。
• 资源管理：所有实现IDisposable的对象（如SqliteConnection、SqliteCommand、SqliteDataReader）必须用using语句包裹，确保资源自动释放，避免内存泄漏和连接占用。
• 路径验证：可在代码中加入Debug.WriteLine(Environment.CurrentDirectory)输出当前工作目录，确认文件是否存在于该路径下。
• 空值判断：对用户选择的项（如deleteButton_Click中的selected）增加空值判断，避免空引用异常导致崩溃。

内容的提问来源于stack exchange，提问作者Kornél Géczi