时间戳

首页 > 解决方案 > Using a dictionary to perform basic CRUD operations and getting an sqlite syntax error

c# - Using a dictionary to perform basic CRUD operations and getting an sqlite syntax error

问题描述

I'm learning SQL and decided to use a data structure I've never used before, a dictionary, to create some methods to perform basic CRUD operations. The first method that I am working on is a method to delete a person given a table name and a dictionary containing the strings to be used in the SQL statement. Here is the code.

class QueryBuilder
{
        public void DeleteMemer(string tableName, Dictionary<string, string> columns)
        {
            var sqliteCommand = new SQLiteCommand($"delete from '{tableName}' where {columns.Keys} = '{columns.Values}'", connection);
            sqliteCommand.ExecuteNonQuery();
        }
}

class Program
{
        static void Main(string[] args)
        {
            DBManagerSQLite memes = new DBManagerSQLite("SqliteDataBase.db");
            QueryBuilder queryBuilder = new QueryBuilder(memes.connection);
            Dictionary<string, string> dict = new Dictionary<string, string>();

            //------------------DELETE TEST------------------
            dict.Add("Memer", "Ryan");
            queryBuilder.DeleteMemer("Memers", dict);
        }
}

Edited for clarity. I get a run time error on the sqliteCommand.ExecuteNonQuery() line. I suspect this is because of the dictionary I am using. I previously was using all my CRUD operations with simple strings but decided to change it and use a dictionary. I think I am not understand how to use a dictionary in this way. If I remove the dictionary and just hard code strings, the methods work just fine.

标签： c#

解决方案

  1. 这可能导致 SQL 注入 - 我建议您使用SqlParameter
  2. 当像这样注入时，{columns.Keys}将转换为类似的东西System...ICollection，这绝对不是列名。
  3. 如果你能澄清这个问题会很好。假设问题是语法错误异常：

您可以遍历键值对来WHERE为您的查询创建一个子句。

示例（无需消毒！）：

var whereClause = string.Join(" OR ", columns.Select(kvp => $"{kvp.Key} = '{kvp.Value}'")); // join with AND to create more restrictive query
var sqliteCommand = new SQLiteCommand($"delete from '{tableName}' where {whereClause}", connection);

更新：

除了问题之外，您为什么尝试那样使用数据库？实现代码优先的 EF 上下文相当容易，并且除了简单性之外，您还将获得对查询的编译时检查。只有当您需要更快的速度时，您才能切换到任何微 ORM 并仔细优化性能关键型查询

推荐阅读