The following class was designed to help create a more detailed error message than what's provided by the repository when a user tries to insert text into a column that is > the column max length. The users would like to be able to track down exactly which column(s) were the issue. Therefore, I created the following, and I would like to know if this is a good approach or not.
This class defines the parameters needed and is the context object for each respective strategy class.
public class QueryBuilder
{
public StringBuilder ColumnNames{get;private set;}
public StringBuilder ColumnValues{get;private set;}
public string TableName{get;private set;}
public Database.FactoryType FactoryType {get;private set;}
private static Dictionary<Database.FactoryType, IQueryBuilderStrategy> queryBuilderStrategies;
public QueryBuilder(StringBuilder columnNames,StringBuilder columnValues,string tableName, Database.FactoryType factoryType)
{
TableName = tableName;
ColumnNames = columnNames;
ColumnValues = columnValues;
FactoryType = factoryType;
LoadStrategies();
}
private void LoadStrategies()
{
queryBuilderStrategies = new Dictionary<Database.FactoryType,IQueryBuilderStrategy>();
queryBuilderStrategies.Add(Database.FactoryType.SqlClient, new SQLErrorQueryStrategy() );
queryBuilderStrategies.Add(Database.FactoryType.OleDb, new AccessErrorQueryStrategy());
}
public string Create()
{
return queryBuilderStrategies[FactoryType].CreateSQLQueryString(this);
}
public string Read(IDataReader reader)
{
return queryBuilderStrategies[FactoryType].ParseDataReader(this, reader);
}
}
I created the following interface to decouple all the different strategies needed to communicate with multiple databases that we support.
public interface IQueryBuilderStrategy
{
string CreateSQLQueryString(QueryBuilder queryBuilder);
string ParseDataReader(QueryBuilder queryBuilder, IDataReader reader);
}
I then took it a step further and defined an abstract class because there was alot of duplication of code that only needed to change slightly
public abstract class ErrorQueryStrategyBase : IQueryBuilderStrategy
{
protected Func<DataRow,bool> IsTextField;
protected virtual string[] GetFieldNames(QueryBuilder builder)
{
return builder.ColumnNames.ToString().Replace("\"", string.Empty).Replace("'", string.Empty).Split(',');
}
protected virtual string[] GetFieldValues(QueryBuilder builder)
{
return builder.ColumnValues.ToString().Replace("'", string.Empty).Split(',');
}
protected virtual string GetErrorMessage(string fieldName, int currentFieldLength, string dbColumnName, int columnMaxSize)
{
string indexErrorMessage = String.Format("Field {0} length is {1}. However Data storage max length for column {2} is {3}{4}");
return string.Format(indexErrorMessage,
fieldName, currentFieldLength, dbColumnName, columnMaxSize, System.Environment.NewLine);
}
public virtual string CreateSQLQueryString(QueryBuilder queryBuilder)
{
return string.Format("SELECT {0} FROM {1}", queryBuilder.ColumnNames.ToString(), queryBuilder.TableName);
}
public virtual string ParseDataReader(QueryBuilder builder, IDataReader reader)
{
string[] userProvidedValues = GetFieldValues(builder);
string[] columnNamesSeparated = GetFieldNames(builder);
string errorMessage = string.Empty;
DataTable dt = reader.GetSchemaTable();
int index = 0;
foreach (DataRow row in dt.Rows)
{
string columnName = row[0].ToString();
int columnSize = (int)row[2];
int len = userProvidedValues[index].Length;
if (IsTextField(row) )
{
if (len > columnSize)
{
string fieldName = columnNamesSeparated[index];
errorMessage += GetErrorMessage(fieldName, len, columnName, columnSize);
}
}
index++;
}
return errorMessage;
}
}
public class SQLErrorQueryStrategy : ErrorQueryStrategyBase
{
private readonly int textFieldIndex;
public SQLErrorQueryStrategy()
{
this.textFieldIndex = 12;
this.IsTextField = delegate(DataRow row)
{
Type dataType = (Type)row[textFieldIndex];
return dataType.Name == "String";
};
}
}
public class AccessErrorQueryStrategy : ErrorQueryStrategyBase
{
private readonly int textFieldIndex;
public AccessErrorQueryStrategy()
{
this.textFieldIndex = 5;
this.IsTextField = delegate(DataRow row)
{
Type dataType = (Type)row[textFieldIndex];
return dataType.Name == "String";
};
}
}
I instantiate and call the class here:
QueryBuilder errorQuery = new QueryBuilder(fieldList,valueList,tableName,capDatabase.Type);
message += GetDetailedErrorMessage(capDatabase, errorQuery);
private string GetDetailedErrorMessage(IDatabase database, QueryBuilder queryBuilder)
{
string message = string.Empty;
try
{
using (DbCommand errorQueryCommand =
database.CreateCommand(queryBuilder.Create(), CommandType.Text))
{
errorQueryCommand.Connection = databaseConnection;
using (DbDataReader reader = errorQueryCommand.ExecuteReader(CommandBehavior.SchemaOnly))
{
message += queryBuilder.Read(reader);
}
}
}
catch (Exception ex)
{
message += ex.Message;
}
return message;
}
Using this approach, I was able to isolate the changes out. It also allows for future changes and additional db support.
Please provide constructive comments.