public static KitComponentCollection LoadForMemberProduct(int productId)
{
KitComponentCollection kitComponents = new KitComponentCollection();
//CREATE THE DYNAMIC SQL TO LOAD OBJECT
StringBuilder selectQuery = new StringBuilder();
selectQuery.Append("SELECT " + KitComponent.GetColumnNames("ac_KitComponents"));
selectQuery.Append(" FROM ac_KitComponents, ac_KitProducts");
selectQuery.Append(" WHERE ac_KitComponents.KitComponentId = ac_KitProducts.KitComponentId");
selectQuery.Append(" AND ac_KitProducts.ProductId = @ProductId");
Database database = Token.Instance.Database;
DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString());
database.AddInParameter(selectCommand, "@productId", System.Data.DbType.Int32, productId);
//EXECUTE THE COMMAND
using (IDataReader dr = database.ExecuteReader(selectCommand))
{
while (dr.Read())
{
KitComponent kitKitComponent = new KitComponent();
KitComponent.LoadDataReader(kitKitComponent, dr);
kitComponents.Add(kitKitComponent);
}
dr.Close();
}
return(kitComponents);
}
public static KitComponentCollection LoadForProduct(Int32 productId, int maximumRows, int startRowIndex, string sortExpression)
{
//CREATE THE DYNAMIC SQL TO LOAD OBJECT
StringBuilder selectQuery = new StringBuilder();
selectQuery.Append("SELECT");
if (maximumRows > 0)
{
selectQuery.Append(" TOP " + (startRowIndex + maximumRows).ToString());
}
selectQuery.Append(" " + KitComponent.GetColumnNames("ac_KitComponents"));
selectQuery.Append(" FROM ac_KitComponents, ac_ProductKitComponents");
selectQuery.Append(" WHERE ac_KitComponents.KitComponentId = ac_ProductKitComponents.KitComponentId");
selectQuery.Append(" AND ac_ProductKitComponents.ProductId = @productId");
selectQuery.Append(" AND StoreId = @storeId");
if (!string.IsNullOrEmpty(sortExpression))
{
selectQuery.Append(" ORDER BY " + sortExpression);
}
Database database = Token.Instance.Database;
DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString());
database.AddInParameter(selectCommand, "@productId", System.Data.DbType.Int32, productId);
database.AddInParameter(selectCommand, "@storeId", System.Data.DbType.Int32, Token.Instance.StoreId);
//EXECUTE THE COMMAND
KitComponentCollection results = new KitComponentCollection();
int thisIndex = 0;
int rowCount = 0;
using (IDataReader dr = database.ExecuteReader(selectCommand))
{
while (dr.Read() && ((maximumRows < 1) || (rowCount < maximumRows)))
{
if (thisIndex >= startRowIndex)
{
KitComponent kitComponent = new KitComponent();
KitComponent.LoadDataReader(kitComponent, dr);
results.Add(kitComponent);
rowCount++;
}
thisIndex++;
}
dr.Close();
}
return(results);
}
public static KitComponentCollection LoadForCriteria(string sqlCriteria, int maximumRows, int startRowIndex, string sortExpression)
{
//CREATE THE DYNAMIC SQL TO LOAD OBJECT
StringBuilder selectQuery = new StringBuilder();
selectQuery.Append("SELECT");
if (maximumRows > 0)
{
selectQuery.Append(" TOP " + (startRowIndex + maximumRows).ToString());
}
selectQuery.Append(" " + KitComponent.GetColumnNames(string.Empty));
selectQuery.Append(" FROM ac_KitComponents");
string whereClause = string.IsNullOrEmpty(sqlCriteria) ? string.Empty : " WHERE " + sqlCriteria;
selectQuery.Append(whereClause);
if (!string.IsNullOrEmpty(sortExpression))
{
selectQuery.Append(" ORDER BY " + sortExpression);
}
Database database = Token.Instance.Database;
DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString());
//EXECUTE THE COMMAND
KitComponentCollection results = new KitComponentCollection();
int thisIndex = 0;
int rowCount = 0;
using (IDataReader dr = database.ExecuteReader(selectCommand))
{
while (dr.Read() && ((maximumRows < 1) || (rowCount < maximumRows)))
{
if (thisIndex >= startRowIndex)
{
KitComponent kitComponent = new KitComponent();
KitComponent.LoadDataReader(kitComponent, dr);
results.Add(kitComponent);
rowCount++;
}
thisIndex++;
}
dr.Close();
}
return(results);
}
public static KitComponentCollection Search(string productName, string componentName, int maximumRows, int startRowIndex, string sortExpression)
{
KitComponentCollection componentList = new KitComponentCollection();
//CREATE THE SQL STATEMENT
productName = StringHelper.FixSearchPattern(productName);
componentName = StringHelper.FixSearchPattern(componentName);
StringBuilder selectQuery = new StringBuilder();
selectQuery.Append("SELECT");
if (maximumRows > 0)
{
selectQuery.Append(" TOP " + (startRowIndex + maximumRows).ToString());
}
selectQuery.Append(" " + KitComponent.GetColumnNames(string.Empty));
selectQuery.Append(" FROM ac_KitComponents WHERE KitComponentId IN (SELECT DISTINCT C.KitComponentId");
selectQuery.Append(" FROM ((ac_KitComponents C INNER JOIN ac_ProductKitComponents PC ON C.KitComponentId = PC.KitComponentId)");
selectQuery.Append(" INNER JOIN ac_Products P ON PC.ProductId = P.ProductId)");
selectQuery.Append(" WHERE P.StoreId = @storeId");
if (!string.IsNullOrEmpty(productName))
{
selectQuery.Append(" AND P.Name LIKE @productName");
}
if (!string.IsNullOrEmpty(componentName))
{
selectQuery.Append(" AND C.Name LIKE @componentName");
}
if (!string.IsNullOrEmpty(sortExpression))
{
selectQuery.Append(" ORDER BY " + sortExpression);
}
selectQuery.Append(")");
//CREATE THE COMMAND
Database database = Token.Instance.Database;
DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString());
database.AddInParameter(selectCommand, "@storeId", System.Data.DbType.Int32, Token.Instance.StoreId);
if (!string.IsNullOrEmpty(productName))
{
database.AddInParameter(selectCommand, "@productName", System.Data.DbType.String, productName);
}
if (!string.IsNullOrEmpty(componentName))
{
database.AddInParameter(selectCommand, "@componentName", System.Data.DbType.String, componentName);
}
//EXECUTE THE COMMAND
int thisIndex = 0;
int rowCount = 0;
using (IDataReader dr = database.ExecuteReader(selectCommand))
{
while (dr.Read() && ((maximumRows < 1) || (rowCount < maximumRows)))
{
if (thisIndex >= startRowIndex)
{
KitComponent component = new KitComponent();
KitComponent.LoadDataReader(component, dr);
componentList.Add(component);
rowCount++;
}
thisIndex++;
}
dr.Close();
}
return(componentList);
}
){kind=link}