c# - System.ArgumentException 值不在预期范围内，SQL 问题

Question

我正在使用 .Net Compact 3.5 Windows 7 CE。

我有一个大约有 50 个用户的应用程序，我将其设置为每次数据库事务失败时我都会收到一封电子邮件，其中包含查询。

我经常会收到一封电子邮件，其中包含这样开头的堆栈跟踪:

System.ArgumentException: Value does not fall within the expected range.
at System.Data.SqlClient.SqlParameterCollection.Validate(Int32 index, SqlParameter value)
at System.Data.SqlClient.SqlParameterCollection.AddWithoutEvents(SqlParameter value)
at System.Data.SqlClient.SqlParameterCollection.Add(SqlParameter value)
at MedWMS.Database.startSqlConnection(String query, SqlParameter[] parameters, SqlConnection connection, SqlCommand cmd)
at MedWMS.Database.<>c__DisplayClasse.b__8()
at MedWMS.Database.retry(Action action)
at MedWMS.Database.executeNonQuery(String query, SqlParameter[] parameters, String connectionString)...

导致此问题的 SQL 查询并不总是相同的。我在 SQL Server Management Studio 中收到电子邮件后运行相同的查询，没有任何问题。

我想知道为什么会这样。这是我关于 SO 的第一个问题，所以如果我做错了什么，请告诉我。我很乐意回答任何问题以提供更多详细信息。

这是会导致此错误的代码示例:

SqlParameter[] parameters = new SqlParameter[1];
parameters[0] = new SqlParameter("@salesOrder", this.salesOrderNumber);

string query = @"
                Select InvTermsOverride from SorMaster where SalesOrder = Convert(int, @salesOrder) and InvTermsOverride = '07' --07 is for COD";

DataTable dt = Database.executeSelectQuery(query, parameters, Country.getCurrent().getSysproConnectionStrReportServer());

这是实际通过的查询:

Select InvTermsOverride from SorMaster where SalesOrder = Convert(int, '000000001138325') and InvTermsOverride = '07' --07 is for COD

下面是Database类的相关方法:

public static DataTable executeSelectQuery(String query, SqlParameter[] parameters, string connectionString)
{
    DataTable dt = new DataTable();

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand cmd = null;

        try
        {
            retry(() =>
            {
                cmd = startSqlConnection(query, parameters, connection, cmd);
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    dt.Load(reader);
                }
            });
        }
        catch (Exception ex)
        {
            onDbConnectionCatch(cmd, ex);
        }
        finally
        {
            cmd.Dispose();
            connection.Close();
        }
    }

    return dt;
}

public static void executeNonQuery(String query, SqlParameter[] parameters, string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand cmd = null;

        try
        {
            retry(() =>
            {
                cmd = startSqlConnection(query, parameters, connection, cmd);
                cmd.ExecuteNonQuery();
            });
        }
        catch (Exception ex)
        {
            onDbConnectionCatch(cmd, ex);
        }
        finally
        {
            cmd.Dispose();
            connection.Close();
        }
    }
}

private static void retry(Action action)
{
    int retryCount = 3;
    int retryInterval = 1000;
    Exception lastException = null;

    for (int retry = 0; retry < retryCount; retry++)
    {
        try
        {
            if (retry > 0)
                System.Threading.Thread.Sleep(retryInterval);
            action();

            lastException = null;
            return;
        }
        catch (Exception ex)
        {
            lastException = ex;
        }
    }

    if (lastException != null)
    {
        throw lastException;
    }
}

private static SqlCommand startSqlConnection(String query, SqlParameter[] parameters, SqlConnection connection, SqlCommand cmd)
{
    if (connection.State != ConnectionState.Open)
    {
        connection.Open();
    }

    cmd = new SqlCommand(query, connection);

    if (parameters != null)
    {
        foreach (SqlParameter sp in parameters)
        {
            if (sp != null)
            {
                cmd.Parameters.Add(sp);
            }
        }
    }

    return cmd;
}

private static void onDbConnectionCatch(SqlCommand cmd, Exception ex)
{
    try
    {
        new BigButtonMessageBox("", "Unable connect to database").ShowDialog();
        sendEmailWithSqlQuery(cmd, ex);
    }
    catch
    {
    }
}

private static void sendEmailWithSqlQuery(SqlCommand cmd, Exception ex)
{
    string query2 = "cmd was null";

    if (cmd != null)
    {
        query2 = cmd.CommandText;

        foreach (SqlParameter p in cmd.Parameters)
        {
            query2 = query2.Replace(p.ParameterName, "'" + p.Value.ToString() + "'");
        }
    }

    InternetTools.sendEmail("DB ERROR", ex.ToString() + "\r\n" + query2);
}

Answer 1

我遇到了与 Can't solve "Sqlparameter is already contained by another SqlparameterCollection" 相同的问题

由于某种原因，SQL CE 有一个不同的错误。

由于我的重试方法，我无法重用 SqlParameter 对象，仍然不确定为什么不允许

反正我变了

cmd.Parameters.Add(sp);

到

cmd.Parameters.Add(sp.ParameterName, sp.Value);