public void Append(Command command)
{
if (command == null)
{
//do nothing
return;
}
Script += " " + command.Script;
Parameters.AddRange(command.Parameters);
}
/// <summary>
/// Initializes a new instance of the SqlException class
/// </summary>
/// <param name="script">The Sql script beeing executed</param>
public SqlException(Command command)
: base()
{
this.Command = command;
}
/// <summary>
/// Initializes a new instance of the SqlException class
/// </summary>
/// <param name="script">The Sql script beeing executed</param>
/// <param name="message">The message indicating the error ocurred</param>
public SqlException(Command command, string message)
: base(message)
{
this.Command = command;
}
/// <summary>
/// Initializes a new instance of the SqlException class
/// </summary>
/// <param name="script">The Sql script beeing executed</param>
/// <param name="message">The message indicating the error ocurred</param>
/// <param name="innerException">The exception that caused this exception to be thrown</param>
public SqlException(Command command, string message, Exception innerException)
: base(message, innerException)
{
this.Command = command;
}
/// <summary> /// Executes a scalar function SQL Script and retrieves a single value /// </summary> /// <remarks> /// Usefull to execute aggregated functions that returns only one row and one value /// </remarks> /// <param name="command"> /// Sql Sentence to exectute for retrieve data /// </param> /// <returns> /// Value returned in the first row and firstn collumn /// </returns> public abstract object GetScalar(Command command);
/// <summary> /// Executes a SQL Script and retrieves all data obteined /// </summary> /// <remarks> /// You must explicity close the returned DataReader once it's not used, /// otherwise the assigned connection would stay opened and cause /// a bad use of resources /// </remarks> /// <param name="command"> /// Sql Sentence to exectute for retrieve data /// </param> /// <returns> /// A System.Data.IDataReader with all data obtained /// </returns> public abstract IDataReader GetDataReader(Command command);
/// <summary>
/// Executes a SQL Script that doesn't retun rows
/// </summary>
/// <param name="command">
/// The SQL script to be executed
/// </param>
/// <returns>
/// An int indicating the number of affected rows
/// </returns>
public virtual int Execute(Command command)
{
return Execute(new List<Command>() { command }).First();
}
/// <summary>
/// Returns a boolean value that indicates if the
/// SQL Sentence return data
/// </summary>
/// <param name="command">
/// Sentence to verify
/// </param>
/// <returns>
/// Boolean value that indicates if the
/// SQL Sentence return data
/// </returns>
public virtual bool ExistsData(Command command)
{
//Local Vars
bool result = false;
//Creating DataReader
IDataReader reader = null;
//Trying to read the data
try
{
//Query the Database
reader = GetDataReader(command);
//Validating if exists data
result = reader.Read();
}
catch
{
throw;
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
reader.Dispose();
}
}
//Returning value
return result;
}
/// <summary> /// Executes a SQL Script and retrieves all data obteined /// </summary> /// <param name="command"> /// Sql Sentence to execute for retrieve data /// </param> /// <returns> /// A System.Data.IDataTable with all data obtained /// </returns> public abstract IDataTable GetDataTable(Command command);
/// <summary>
/// Creates a Insert Sentence for the specified Table with
/// the values of the object indicated
/// </summary>
/// <param name="table">
/// Table for Insert sentence create
/// </param>
/// <param name="dobj">
/// object instance with the values to use in the Insert sentence
/// </param>
/// <returns>
/// Insert Sql Sentence
/// </returns>
public virtual Command Insert(Insert insert)
{
//Validating if table or dobj arguments are null
if (insert == null) throw new ArgumentNullException("insert");
string columnsList = string.Empty, valuesList = string.Empty;
Command command = new Command();
foreach (ColumnValue cv in insert.Values)
{
Int64 intValue = Int64.MinValue;
if (cv.Column.IsNumeric)
{
try
{
intValue = (Int64)System.Convert.ChangeType(cv.Value, typeof(Int64));
}
catch { }
}
//if this is an autoincrement primary key and the value is null, ignore this field so its autogenerated
if
(
cv.Column.IsAutoNumber &&
cv.Column.IsPrimaryKey &&
(cv.Value == null || intValue == 0)
)
{
//ignore
continue;
}
//otherwise, treat normally
else
{
CommandParameter param = CreateCommandParameter(cv);
columnsList += EncloseName(cv.Column.Name) + ", ";
valuesList += param.Name + ", ";
command.Parameters.Add(param);
}
}
//Removing last ", "
if (columnsList.EndsWith(", "))
{
columnsList = columnsList.Remove(columnsList.Length - 2, 2);
}
if (valuesList.EndsWith(", "))
{
valuesList = valuesList.Remove(valuesList.Length - 2, 2);
}
//Creating Insert Sentence and return it
command.Script =
"INSERT INTO " + EncloseName(insert.Table.Name) + " " +
EncloseOnParenthesis(columnsList) +
" VALUES " +
EncloseOnParenthesis(valuesList);
command.Script += ScriptSeparator;
return command;
}
/// <summary>
/// Creates a Sql In clause for filters
/// </summary>
/// <typeparam name="listType">
/// Type of the items in the list of values
/// </typeparam>
/// <param name="valuesList">
/// List of values for create the In sentence
/// </param>
/// <returns>
/// Sql In clause for filters
/// </returns>
protected virtual Command InClause(List<IComparable> valuesList)
{
//Initializing In clause
Command command = new Command();
command.Script = " IN (";
string randomPrefix = new Random().Next(0, 999999).ToString();
//Crossing the elements of the list
for (int i = 0; i < valuesList.Count; i++)
{
IComparable item = valuesList[i];
string paramName = randomPrefix + "_" + i;
command.Script += paramName + ", ";
command.Parameters.Add(new CommandParameter() { Name = paramName, DbType = DbTypeMapper.Parse(item.GetType()), Value = item });
}
//Removing last ", "
command.Script = command.Script.TrimEnd(',', ' ');
//Closing the clause
command.Script += ")";
//Returning the filter
return command;
}
protected virtual Command Filter(IEnumerable<FilterBase> filters, LogicalOperator logicalOperator)
{
//Local Vars
Command command = new Command();
//Validating if there are filters defined
if (filters == null || filters.Count() == 0) return null;
//Getting the logical operator for merge filters
string logicalOpr = Operator(logicalOperator);
//Crossing the filters
foreach (FilterBase f in filters)
{
//Adding filter condition
var c = Filter(f);
command.Append(c);
command.Script += Operator(logicalOperator);
}
//Removing last logic operator
command.Script = command.Script.Remove(command.Script.Length - logicalOpr.Length, logicalOpr.Length);
return command;
}
protected virtual Command Filter(ValueCompareFilter filter)
{
//Validating if there are filters defined
if (filter == null) return null;
Command command = new Command();
CommandParameter param = new CommandParameter(filter.ValueToCompare, null, filter.Column.DbType);
command.Parameters.Add(param);
command.Script =
EqualityComparison
(
ColumnFullName(filter),
param.Name,
filter.Operator
);
return command;
}
protected virtual Command Filter(RangeFilter filter)
{
//Validating if there are filters defined
if (filter == null) return null;
Command command = new Command();
CommandParameter minParam = new CommandParameter(filter.MinValue, null, filter.Column.DbType);
command.Parameters.Add(minParam);
CommandParameter maxParam = new CommandParameter(filter.MaxValue, null, filter.Column.DbType);
command.Parameters.Add(maxParam);
command.Append
(
LogicalExpression
(
EqualityComparison
(
ColumnFullName(filter),
minParam.Name,
CompareOperator.GreaterThanEqual
),
EqualityComparison
(
ColumnFullName(filter),
maxParam.Name,
CompareOperator.LessThanEqual
),
LogicalOperator.And,
false
)
);
return command;
}
protected virtual Command Filter(InFilter filter)
{
//Validating if there are filters defined
if (filter == null) return null;
Command command = new Command();
command.Append(ColumnFullName(filter));
command.Append(InClause(filter.Values.ToList()));
return command;
}