这是防御性代码还是有可能遇到问题? [英] Is this defensive code or are there possibilities that it could encouter problems?

问题描述

我有以下代码可以连接到数据库 > 运行存储过程 > 然后继续.

I've got the following code which makes a connection to a db > runs a stored proc > and then moves on.

我相信数据库编程很容易出错，所以防御性很重要:以下是防御性的吗?(或者可以改进吗?)

I believe it is easy to get db programming wrong so it is important to be defensive: is the following defensive? (or can it be improved?)

public int RunStoredProc()
{
SqlConnection conn = null;
SqlCommand dataCommand = null;
SqlParameter param = null;
int myOutputValue;

try
{
    conn = new SqlConnection(ConfigurationManager.ConnectionStrings["IMS"].ConnectionString);                  
    conn.Open();
    dataCommand = conn.CreateCommand();
    dataCommand.CommandType = CommandType.StoredProcedure;
    dataCommand.CommandText = "pr_blahblah";
    dataCommand.CommandTimeout = 200; //seconds
    param = new SqlParameter();
    param = dataCommand.Parameters.Add("@NumRowsReturned", SqlDbType.Int);
    param.Direction = ParameterDirection.Output;
    dataCommand.ExecuteNonQuery();
    myOutputValue = (int)param.Value;

    return myOutputValue;
}
catch (SqlException ex)
{
    MessageBox.Show("Error:" + ex.Number.ToString(), "Error StoredProcedure");
    return 0;
}
finally
{
    if (conn != null)
    {
        conn.Close();
        conn.Dispose();
    }
}
}

代码现在看起来像以下内容

我已经尝试使用每个人提供的所有帮助，上面的代码现在已修改为以下内容，我希望现在足够防御:

I've tried to use all the help offered by everyone and the above code has now been amended to the following which I hope is now sufficiently defensive:

public SqlConnection CreateConnection()
{
    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["IMS"].ConnectionString);
    return conn;
}
public int RunStoredProc()
{
    using (var conn = CreateConnection())
    using (var dataCommand = conn.CreateCommand()) 
    {
            conn.Open();
            dataCommand.CommandType = CommandType.StoredProcedure;
            dataCommand.CommandText = "pr_BankingChargebacks";
            dataCommand.CommandTimeout = 200; //5 minutes
            SqlParameter param = new SqlParameter();
            param = dataCommand.Parameters.Add("@NumRowsReturned", SqlDbType.Int);
            param.Direction = ParameterDirection.Output;
            dataCommand.ExecuteNonQuery();
            int myOutputValue = (int)param.Value;

            return myOutputValue;

    } 
}

推荐答案

尝试使用 using 结构来处理这些事情.

Try using, well, the using construct for such things.

using(var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["IMS"].ConnectionString)
{
}

一旦你这样做，我认为你将处于正确的防御"水平.同样，尝试对必须处理的任何内容(如命令)执行相同的操作

Once you do that, I think you will be at the right level of "defense". Similarly try to do the same for anything that has to be disposed ( like the command)

这篇关于这是防御性代码还是有可能遇到问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！