这个 C# 4.0 MSSQL 2008 R2 数据库连接类可以改进吗 - 专家问题

Question

这是我的通用数据库连接类。我正在使用此类通过网站执行查询。您对此有何建议以提高性能？谢谢。

MSSQL 2008 R2 SP1 - Microsoft Visual Studio 2010 SP1、C# 4.0 - ASP.net 4.0

类

using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Web;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;
using System.IO;

/// <summary>
/// Summary description for DbConnection
/// </summary>
public class DbConnection
{
    public static string srConnectionString = "server=localhost;database=myDB;uid=sa;pwd=MYPW;";

    public DbConnection()
    {

    }

    public static DataSet db_Select_Query(string strQuery)
    {
        DataSet dSet = new DataSet();

        try
        {
            using (SqlConnection connection = new SqlConnection(srConnectionString))
            {
                connection.Open();
                SqlDataAdapter DA = new SqlDataAdapter(strQuery, connection);
                DA.Fill(dSet);
            }
            return dSet;
        }

        catch (Exception)
        {
            using (SqlConnection connection = new SqlConnection(srConnectionString))
            {
                if (srConnectionString.IndexOf("select Id from tblAspErrors") != -1)
                {
                    connection.Open();
                    strQuery = strQuery.Replace("'", "''");
                    SqlCommand command = new SqlCommand("insert into tblSqlErrors values ('" + strQuery + "')", connection);
                    command.ExecuteNonQuery();
                }
            }
            return dSet;
        }
    }

    public static void db_Update_Delete_Query(string strQuery)
    {
        try
        {
            using (SqlConnection connection = new SqlConnection(srConnectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(strQuery, connection);
                command.ExecuteNonQuery();
            }
        }
        catch (Exception)
        {
            strQuery = strQuery.Replace("'", "''");
            using (SqlConnection connection = new SqlConnection(srConnectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand("insert into tblSqlErrors values ('" + strQuery + "')", connection);
                command.ExecuteNonQuery();
            }

        }
    }
}

Answer 1

1.) 如何确保传入的 strQuery 不会受到 sql 注入？

2.) 使用日志框架，如 nlog 或 log4net。这将允许您轻松地通过使用配置文件来指定存储错误日志的位置（文件、电子邮件、数据库）。

您的日志记录将如下所示：

try
{
    using (SqlConnection connection = new SqlConnection(srConnectionString))
    {
        connection.Open();
        SqlCommand command = new SqlCommand(strQuery, connection);
        command.ExecuteNonQuery();
    }
}
catch (Exception ex)
{
    log.ErrorFormat("strQry: {0}", strQuery);
    log.Error(ex);
}

3.) 使用 SecureString

public static SecureString srConnectionString = "server=localhost;database=myDB;uid=sa;pwd=MYPW;";

4.) 如果数据库关闭，您将如何将错误写入数据库？它会生成一个未捕获的异常...