在C＃中为SQL连接正确使用Try Catch [英] Correct use of Try Catch for the SQL connection in C#

问题描述

此代码在尝试/捕获方式中是否正确？我需要评估操作是否完成，以通知用户有关结果，因此我将获得bool值，以了解连接是否成功。

Is this code correct in means of Try/Catch? I need to value whether the action is done or not to inform the user about the result, so I will then get the bool value to know if connection was successful.

    public static bool CreateSQLDatabaseTable()
    {
        var connString = "Server=localhost\\SQLEXPRESS;Integrated Security = SSPI; database = MyDB";
        string cmdText = "SELECT count(*) as Exist from INFORMATION_SCHEMA.TABLES where table_name =@Product";
        try
        {
            using (var sqlConnection = new SqlConnection(connString))
            {
                using (var sqlCmd = new SqlCommand(cmdText, sqlConnection))
                {
                    sqlCmd.Parameters.Add("@Product", System.Data.SqlDbType.NVarChar).Value = "Product";
                    sqlConnection.Open();
                    sqlCmd.ExecuteScalar();
                    if ((int)sqlCmd.ExecuteScalar() != 1)
                    {
                        using (SqlCommand command = new SqlCommand("CREATE TABLE Product (Id INT, UserId TEXT, CreationDate TEXT, Name TEXT)", sqlConnection))
                        {
                            command.ExecuteNonQuery();
                            return true;
                        }
                    }
                }

            }
            return false;
        }
        catch
        {
            return false;
        }
    }

推荐答案

您的方法实际上可以有3个结果：

Your method can actually have 3 outcomes:

1. 表已成功创建（方法返回true）


2. 该表已存在（方法返回false）


3. 尝试创建该表时出错（引发了异常）

因此，您应该在此方法之外处理异常，并且不要盲目地捕获所有异常。您应该只处理 SqlException ，以便不会处理其他异常。此外，您还应该将异常记录在一个好的地方。

So you should handle the exception outside of this method and you should not blindly catch all Exceptions. You should only handle the SqlException so that other exceptions will not be handled. Also you should be logging the exception somewhere as a good practice.

有关 SqlException
的更多信息
https ：//msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqlexception（v = vs.110）.aspx

public static bool CreateSQLDatabaseTable()
{
    var connString = "Server=localhost\\SQLEXPRESS;Integrated Security = SSPI; database = MyDB";
    string cmdText = "SELECT count(*) as Exist from INFORMATION_SCHEMA.TABLES where table_name =@Product";

    using (var sqlConnection = new SqlConnection(connString))
    {
        using (var sqlCmd = new SqlCommand(cmdText, sqlConnection))
        {
            sqlCmd.Parameters.Add("@Product", System.Data.SqlDbType.NVarChar).Value = "Product";
            sqlConnection.Open();
            sqlCmd.ExecuteScalar();
            if ((int)sqlCmd.ExecuteScalar() != 1)
            {
                using (SqlCommand command = new SqlCommand("CREATE TABLE Product (Id INT, UserId TEXT, CreationDate TEXT, Name TEXT)", sqlConnection))
                {
                    command.ExecuteNonQuery();
                    return true;
                }
            }
        }
    }

    return false;
}

public static void Main()
{
    try
    {
        bool wasCreated = CreateSQLDatabaseTable();
    }
    catch (SqlException ex)
    {
        // Handle the SQL Exception as you wish
        Console.WriteLine(ex.ToString());
    }
}

这篇关于在C＃中为SQL连接正确使用Try Catch的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！