Can this C# 4.0 MSSQL 2008 R2 database connection class be improved - EXPERT Question [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

This is my general database connection class. I am using this class to execute my queries through website. What would your suggestions about this to improve performance. Thank you.

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

Class

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
        {
            u开发者_如何学运维sing (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();
            }

        }
    }
}

---

1.) How are you making sure the strQuery passed in isn't subjected to sql injection?

2.) Use a logging framework like nlog or log4net. This will allow you to easily dictate where to store the error logs (file, email, db) just by using a config file.

your logging would be something like this instead:

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.) Use SecureString

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

4.) How are you going to write the error to the DB if the DB is down? It'll generate an uncaught exception...