时间戳

首页 > 解决方案 > SqlConnection 有什么问题？

c# - SqlConnection 有什么问题？

问题描述

我有一个代码来检查是否已经部署了具有某些指定名称的存储过程。代码是

protected virtual async Task<bool?> IsProcedureDeployed(string storedProcedureName)
        {
            try
            {
                SqlCommand sqlCommand = new SqlCommand
                {
                    CommandText = "select count(*) from sysobjects where type = 'P' and name = @storedProcedureName",
                    CommandType = CommandType.Text,
                    CommandTimeout = this.CommandTimeout
                };

                await this.EnsureConnectionOpened();
                int count = (int)(await sqlCommand.ExecuteScalarAsync());

                return count > 0;
            }
            catch (Exception exception)
            {
                this.SqlConnection.Close();
                ExceptionDispatchInfo.Capture(exception).Throw();
                return null;
            }
        }

this.EnsureConnectionOpened 看起来像这样：

protected async Task EnsureConnectionOpened()
        {
            if (SqlConnection.State == ConnectionState.Closed && SqlTransaction == null)
            {
                await SqlConnection.OpenAsync();
            }
        }

当涉及到它的执行时，int count = (int)(await sqlCommand.ExecuteScalarAsync());它总是抛出一个异常，上面写着......“无效的操作。连接已关闭。”。我检查了 SqlConnection 的状态，它是 Open！我到底做错了什么？

编辑。每个 SqlCommand 都必须有一个 SqlConnection 和正确设置的参数（如果需要）。最终版本是

protected virtual async Task<bool?> IsProcedureDeployed(string storedProcedureName)
        {
            try
            {
                SqlCommand sqlCommand = new SqlCommand("select count(*) from sysobjects where type = 'P' and name = @storedProcedureName", this.SqlConnection)
                {
                    CommandType = CommandType.Text,
                    CommandTimeout = this.CommandTimeout
                };

                SqlParameter sqlParameter = new SqlParameter
                {
                    ParameterName = "@storedProcedureName",
                    Value = storedProcedureName
                };

                sqlCommand.Parameters.Add(sqlParameter);

                await this.EnsureConnectionOpened();
                int count = (int)(await sqlCommand.ExecuteScalarAsync());

                return count > 0;
            }
            catch (Exception exception)
            {
                this.SqlConnection.Close();
                ExceptionDispatchInfo.Capture(exception).Throw();
                return null;
            }
        }

标签： c#sql.netsql-serverado.net

解决方案

我会稍微修改一下这个方法。将所有连接的东西放在同一个地方，而不是分散在各处。此外，由于您发布的代码仅使用 try/catch 来关闭连接（并重新引发异常），因此我将其完全删除。您在这里不需要任何错误处理。让异常发生并冒泡到调用方法。我猜这个逻辑在你的数据层中？

像这样的东西包含得很好而且很整洁。

protected virtual async Task<bool?> IsProcedureDeployed(string storedProcedureName)
{
    bool IsDeployed = false;
    using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["YourConnectionString"].ConnectionString))
    {
        conn.Open();
        using (var cmd = new SqlCommand("select count(*) from sysobjects where type = 'P' and name = @storedProcedureName", conn))
        {
            cmd.Parameters.Add("@storedProcedureName", SqlDbType.NVarChar, 128).Value = storedProcedureName; //using nvarchar(128) because object names use sysname which is a synonym for nvarchar(128)
            var result = await cmd.ExecuteScalarAsync();
            bool.TryParse(result.ToString(), out IsDeployed);
        }
    }
    return IsDeployed;
}

推荐阅读