public SelectStatement Where(SelectColumn selectColumn, Operator @operator, string value1, string value2)
{
WhereColumn whereColumn = new WhereColumn(selectColumn.Join, selectColumn.ColumnDef, selectColumn.Statement, @operator, value1, value2);
WhereColumns.Add(whereColumn);
return(this);
}
public DataSet Execute()
{
DataSet dataSet = new DataSet();
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand("sp_executesql", connection)
{
CommandType = CommandType.StoredProcedure
})
{
command.Parameters.Add(new SqlParameter("@statement", SqlDbType.NVarChar, -1)
{
Value = Statement
});
int parameterId = 1;
string parameters = "";
foreach (WhereColumn wc in WhereColumns.Where(IsParamRequired))
{
parameters += parameters == "" ? "" : ", ";
parameters += "@p" + parameterId++ + @" " + wc.ColumnDef.Type;
switch (wc.ColumnDef.Type)
{
case "nvarchar":
parameters += String.Format(" ({0})", wc.ColumnDef.Length);
break;
}
}
command.Parameters.Add(new SqlParameter("@params", SqlDbType.NVarChar, -1)
{
Value = parameters
});
parameterId = 1;
foreach (WhereColumn wc in WhereColumns.Where(IsParamRequired))
{
command.Parameters.Add(new SqlParameter("@p" + parameterId++, SqlDbType.NVarChar, -1)
{
Value = wc.Value1
});
}
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
{
dataAdapter.Fill(dataSet);
}
}
}
return(dataSet);
}
public SelectStatement Where(string column, Operator @operator, string value1, string value2)
{
GetJoinAndColumnDef(column, (join, columnDef) =>
{
WhereColumn whereColumn = new WhereColumn(@join, columnDef, null, @operator, value1, value2);
WhereColumns.Add(whereColumn);
});
return(this);
}
private bool IsJoinRequired(Join join)
{
//are any of the select query columns for this join Visible or Ordered
bool selectColumns = SelectColumns
.Any(item => (item.Join == join || (item.DependentOnAliases != null && item.DependentOnAliases.Contains(join.Alias))) &&
(item.IsVisible || item.OrderByIndex != 0));
bool whereColumns = WhereColumns.Any(item => item.Join == join);
//are any of the sub joins required
bool subJoinsRequired = Joins
.Where(item => item.ParentJoin == join)
.Any(IsJoinRequired);
return(selectColumns || whereColumns || subJoinsRequired);
}
