方法优化-C# [英] Method Optimisation - C#

问题描述

我已经开发出一种方法，该方法使我可以通过参数传递表(字符串)，列数组(字符串)和值数组(对象)，然后使用这些参数来创建参数化查询.尽管它可以很好地执行代码的长度以及多次for循环会散发出代码的味道，但特别是我觉得我用一种在列和值之间插入逗号的方法可以用另一种更好的方法来实现. >

I've developed a method that allows me to pass in a table (string), array of columns (string) and array of values (object) through the parameters which I then use to create a parameterized query. Although it works fine the length of the code as well as the multiple for loops gave off a code smell, in particular I feel the method I use to insert a comma between the columns and values can be done a different and better way.

public static int Insert(string source, string[] column, object[] values)
{
    int rowsAffected = 0;
    try
    {
        using (SQLiteConnection conn = new SQLiteConnection(connectionString))
        {
            StringBuilder query = new StringBuilder();
            query.Append(" INSERT INTO ");
            query.Append(source);
            query.Append("(");

            for (int i = 0; i < column.Length; i++)
            {
                query.Append(column[i]);

                if (i < values.Length - 1)
                {
                    query.Append(",");
                }
            }

            query.Append(")");
            query.Append(" VALUES ");
            query.Append("(");

            for (int i = 0; i < values.Length; i++)
            {
                query.Append("@" + values[i].ToString());

                if (i < values.Length - 1)
                {
                    query.Append(",");
                }
            }

            query.Append(")");

            conn.Open();
            using (SQLiteCommand cmd = new SQLiteCommand(query.ToString(), conn))
            {
                for (int i = 0; i < values.Length; i++)
                {
                    cmd.Parameters.AddWithValue("@" + values[i].ToString(), values[i]);
                }
                rowsAffected = cmd.ExecuteNonQuery();
            }
        }
        return rowsAffected;
    }
    catch (Exception e)
    {
        MessageBox.Show(e.Message);
    }
    return 0;
}

我正在使用 System.Data.SQLite 库与数据库进行交互.

I'm using the System.Data.SQLite library to interact with the database.

谢谢您的建议！

推荐答案

这是我惯用的使用StringBuilder附加多个带有分隔符的值的方法:

This is my idiomatic way to append multiple values with a separator using StringBuilder:

string separator = ",";
for (int i = 0; i < column.Length; i++)
{
    query.Append(column[i]);
    query.Append(separator);
}
query.Length -= separator.Length;

这假设您将拥有至少一个值，并且通常在我使用该值的情况下，没有至少一个值将是一个错误(并且看来您的情况就是这样).

This assumes you will have at least one value, and usually where I use it, it would be an error not to have at least one value (and it appears your scenario is like that).

看来您还为SQL注入保留了此代码.

您似乎正在尝试使用参数，但是我认为您没有正确地使用它.在阅读代码的方式中，您使用的是参数的实际值而不是它们的索引.我建议进行此修改(假设您的列名数组来自受信任的来源，但您的值则不行):

You seem to be trying to use parameters, but I don't think you've done it correctly. The way I read the code, you are using the actual value of the parameters instead of their index. I would suggest this modification (this assumes your array of column names comes from a trusted source, but that your values do not):

        for (int i = 0; i < values.Length; i++)
        {
            query.Append("@" + i.ToString()); // instead of query.Append("@" + values[i].ToString());

            if (i < values.Length - 1)
            {
                query.Append(",");
            }
        }

        query.Append(")");

        conn.Open();
        using (SQLiteCommand cmd = new SQLiteCommand(query.ToString(), conn))
        {
            for (int i = 0; i < values.Length; i++)
            {
                cmd.Parameters.AddWithValue("@" + i.ToString(), values[i]); // instead of cmd.Parameters.AddWithValue("@" + values[i].ToString(), values[i]);
            }
            rowsAffected = cmd.ExecuteNonQuery();
        }
    }

这篇关于方法优化-C#的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！