I'm looking for any comments or feedback on my database access class. Security and speed are two things I'm most concerned about.
One thing to note is this class has to work in a C# .NET 2 environment, so anything that's more modern would be interesting to me, but please note in the title of your answer if the comments/feedback require a newer .NET version.
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
/// <summary>
/// This is the base class for database access classes. This is the only
/// class that should directly talk to the database. Every class or page
/// that neads to access the database should be refering to this or a
/// derived class.
/// </summary>
public class DatabaseAccess
{
static string LastDatabaseName = "";
static Database database = null;
static int errorCount = 0;
/// <summary>
/// Execute a SQL statement on the default database
/// </summary>
/// <param name="SQL">The SQL statement to execute</param>
/// <returns>DataTable of selected results</returns>
public static DataTable ExecSQL(string SQL)
{
List<SqlParameter> Parameters = new List<SqlParameter>();
return ExecSQL("", SQL, Parameters);
}
/// <summary>
/// Execute a SQL statement on the default database
/// </summary>
/// <param name="SQL">The SQL statement to execute</param>
/// <param name="Parameters">The parameters for the SQL statement</param>
/// <returns>DataTable of selected results</returns>
public static DataTable ExecSQL(string SQL, List<SqlParameter> Parameters)
{
return ExecSQL("", SQL, Parameters);
}
/// <summary>
/// Execute a SQL statement on the requested database
/// </summary>
/// <param name="DatabaseName">The database to execute the SQL on</param>
/// <param name="SQL">The SQL statement to execute</param>
/// <returns>DataTable of selected results</returns>
public static DataTable ExecSQL(string DatabaseName, string SQL)
{
List<SqlParameter> Parameters = new List<SqlParameter>();
return ExecSQL(DatabaseName, SQL, Parameters);
}
/// <summary>
/// Execute a SQL statement on the requested database
/// </summary>
/// <param name="DatabaseName">The database to execute the SQL on</param>
/// <param name="SQL">The SQL statement to execute</param>
/// <param name="Parameters">The parameters for the SQL statement</param>
/// <returns>DataTable of selected results</returns>
public static DataTable ExecSQL(string DatabaseName, string SQL, List<SqlParameter> Parameters)
{
// Database access variables
// Database database = null;
DbCommand command = null;
DataTable table = new DataTable();
if (DatabaseName != LastDatabaseName || database == null)
{
if (database != null)
database = null;
if (DatabaseName != "")
database = DatabaseFactory.CreateDatabase(DatabaseName);
else
database = DatabaseFactory.CreateDatabase();
}
LastDatabaseName = DatabaseName;
command = database.GetSqlStringCommand(SQL);
foreach (SqlParameter p in Parameters)
{
database.AddInParameter(command, p.ParameterName, p.DbType, p.Value);
}
try
{
if (!SQL.StartsWith("UPDATE") && !SQL.StartsWith("DELETE"))
table = database.ExecuteDataSet(command).Tables[0];
else
database.ExecuteNonQuery(command);
errorCount = 0;
}
catch (SystemException e)
{
errorCount++;
if (errorCount < 2)
{
CMSLog.Exception(e);
CMSLog.Info(SQL);
CMSUtil.setSession("Exception", e.Message);
CMSUtil.setSession("ExceptionExtra", e.StackTrace);
HttpContext.Current.Response.Redirect("~/CMS/SiteError.aspx");
}
else
{
HttpContext.Current.AddError(new Exception("Looping DB Error: " + e.Message));
}
}
return table;
}
}
A simple example using the class:
string strValue = "Some Untrusted Value";
List<SqlParameter> parms = new List<SqlParameter>();
parms.Add(new SqlParameter("Value", strValue));
string sql = "SELECT * FROM TableName WHERE FieldName=@Value";
DataTable tblResults = DatabaseAccess.ExecSQL(sql, parms);