Nice solution BFG. Very elegant doing it as a XMLPackage.
I had gone another route and wrote this utility to live in the admin section of the website as a traditional .aspx/.aspx.cs file.
In the .aspx page, I set up two panels, one that contains a form to ask admin what size photos he is looking for, what type of photos and other limiters and the second to display the results.
C#/VB.NET Code:
<body>
<asp:Label ID="lblErrorMessage" runat="server"></asp:Label><br />
<form id="frmxaTools" runat="server">
<asp:Panel ID="pnlDefault" runat="server" Width="100%"> <!-- Default Panel -->
<!--You are logged in as: <asp:Label ID="lblWhoAreYou" runat="server"></asp:Label>-->
<asp:Label ID="lblActionInfo" runat="server"></asp:Label>
</asp:Panel>
<asp:Panel ID="pnlCrawlResults" runat="Server" Width="100%" CssClass="CrawlResultsPanel">
<h3>Crawl for missing images</h3>
<asp:DropDownList ID="ddlImageSize" runat="server">
<asp:ListItem Value="Large" Selected="True">Select size to check</asp:ListItem>
<asp:ListItem Value="Large">Large</asp:ListItem>
<asp:ListItem Value="Medium">Medium</asp:ListItem>
<asp:ListItem Value="Icon">Icon</asp:ListItem>
</asp:DropDownList>
<asp:DropDownList ID="ddlEntity" runat="server">
<asp:ListItem Value="Product" Selected="True">Products</asp:ListItem>
<asp:ListItem Value="Section">Sections</asp:ListItem>
<asp:ListItem Value="Category">Category</asp:ListItem>
</asp:DropDownList>
<br />
<asp:Label ID="lblManufacturerID" AssociatedControlID="txtManufacturerID" runat="server" >Only show products from this manufacturer</asp:Label> <asp:Textbox ID="txtManufacturerID" runat="server" /> *<i>(product search only)</i><br />
<asp:DropDownList ID="ddlShowPublished" runat="server">
<asp:ListItem Value="All" Selected="True">Show regardless of Published Status</asp:ListItem>
<asp:ListItem Value="Published">Published Only</asp:ListItem>
<asp:ListItem Value="Unpublished">Unpublished Only</asp:ListItem>
</asp:DropDownList>
<asp:Button ID="btnStartCrawl" runat="server" Text="Begin Crawl"
onclick="btnStartCrawl_Click" />
<h3>Results:</h3>
<asp:Label ID="lblCrawlResults" runat="server"></asp:Label>
</asp:Panel>
</form>
</body>
Then the .cs file reads as follows:
C#/VB.NET Code:
using System;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Net.Mail;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using AspDotNetStorefrontCommon;
namespace AspDotNetStorefrontAdmin
{
public partial class TBTools : AspDotNetStorefront.SkinBase
{
bool debug = false;
private int timeout;
private void Page_Init(object sender, System.EventArgs e)
{
timeout = Server.ScriptTimeout;
Server.ScriptTimeout = 3600;
}
protected void Page_Load(object sender, System.EventArgs e)
{
// Set up Panel visibility
pnlDefault.Visible = true;
pnlCrawlResults.Visible = true;
}
protected void btnStartCrawl_Click(object sender, EventArgs e)
{
string LookFor = "";
string path = "";
string dbo = string.Empty;
string ShowPublished = string.Empty;
string ShowUnpublished = string.Empty; ;
SectionTitle = "XA-Tools | Crawl Products For No Images";
path = HttpContext.Current.Server.MapPath(String.Format("/images/{0}/", ddlEntity.SelectedValue));
path += ddlImageSize.SelectedValue + "\\";
SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["DBConn"]);
try
{
// Open the connection and Execute the SP
conn.Open();
if (debug)
lblErrorMessage.Text = "Connection opened successfully <br />";
SqlCommand rdrCmd = conn.CreateCommand();
if (txtManufacturerID.Text != String.Empty)
rdrCmd.CommandText = String.Format("SELECT p.ProductID, p.ImageFileNameOverRide FROM {2}Product p INNER JOIN {2}ProductVariant pv ON p.ProductID=pv.ProductID INNER JOIN {2}ProductManufacturer pm ON p.ProductID=pm.ProductID WHERE p.Deleted=0 AND pv.Deleted=0 {0} AND pm.ManufacturerID = {1} ORDER BY p.ProductID DESC",
ShowPublished,
txtManufacturerID.Text,
dbo);
else
{
switch (ddlEntity.SelectedValue) {
case ("Product"):
switch (ddlShowPublished.SelectedValue)
{
case ("All"):
break;
case ("Published"):
ShowPublished = " AND p.Published=1 and pv.Published=1";
break;
case ("Unpublished"):
ShowUnpublished = "AND p.Published=0 and pv.Published=0";
break;
}
rdrCmd.CommandText = String.Format("SELECT p.ProductID, p.ImageFileNameOverRide FROM {1}Product p INNER JOIN {1}ProductVariant pv ON p.ProductID=pv.ProductID WHERE p.Deleted=0 AND pv.Deleted=0 {0} ORDER BY p.ProductID DESC",
ShowPublished,
dbo);
break;
case ("Section"):
switch (ddlShowPublished.SelectedValue)
{
case ("All"):
break;
case ("Published"):
ShowPublished = " AND Published=1";
break;
case ("Unpublished"):
ShowUnpublished = "AND Published=0";
break;
}
rdrCmd.CommandText = String.Format("SELECT SectionID, ImageFileNameOverRide FROM {1}Section WHERE Deleted=0 {0} ORDER BY SectionID DESC",
ShowPublished,
dbo);
break;
case ("Category"):
switch (ddlShowPublished.SelectedValue)
{
case ("All"):
break;
case ("Published"):
ShowPublished = " AND Published=1";
break;
case ("Unpublished"):
ShowUnpublished = "AND Published=0";
break;
}
rdrCmd.CommandText = String.Format("SELECT CategoryID, ImageFileNameOverRide FROM {1}Category WHERE Deleted=0 {0} ORDER BY CategoryID DESC",
ShowPublished,
dbo);
break;
}
}
SqlDataReader dbReader = rdrCmd.ExecuteReader();
lblErrorMessage.Text += "<br />Following is a comma-delimited string of ProductIDs without images:<br />";
while (dbReader.Read())
{
switch (ddlEntity.SelectedValue) {
case "Product":
if (string.IsNullOrEmpty(dbReader["ImageFilenameOverRide"].ToString()))
LookFor = path + dbReader["ProductID"].ToString() + ".jpg";
else
LookFor = path + dbReader["ImageFilenameOverRide"].ToString();
if (!File.Exists(LookFor))
lblErrorMessage.Text += String.Format(" {0},", dbReader["ProductID"].ToString());
break;
case "Section":
if (string.IsNullOrEmpty(dbReader["ImageFilenameOverRide"].ToString()))
LookFor = path + dbReader["SectionID"].ToString() + ".jpg";
else
LookFor = path + dbReader["ImageFilenameOverRide"].ToString();
if (!File.Exists(LookFor))
// lblErrorMessage.Text += String.Format("{0}: Unable to find {1}<br />", dbReader["ProductID"].ToString(), LookFor);
lblErrorMessage.Text += String.Format(" {0},", dbReader["SectionID"].ToString());
break;
case "Category":
if (string.IsNullOrEmpty(dbReader["ImageFilenameOverRide"].ToString()))
LookFor = path + dbReader["CategoryID"].ToString() + ".jpg";
else
LookFor = path + dbReader["ImageFilenameOverRide"].ToString();
if (!File.Exists(LookFor))
// lblErrorMessage.Text += String.Format("{0}: Unable to find {1}<br />", dbReader["ProductID"].ToString(), LookFor);
lblErrorMessage.Text += String.Format(" {0},", dbReader["CategoryID"].ToString());
break;
}
}
dbReader.Close();
// Close and dispose of db stuff
rdrCmd.Dispose();
}
catch (Exception ex) // Some sort of failure occurred in opening connection or running Stored Procedure
{
lblErrorMessage.Text = String.Format("Whoops, something screwed up!<br />Database operations failed to perform properly. [{0}]<br />", ex.ToString());
}
finally
{
conn.Close();
conn.Dispose();
}
}
}
}
Senior Member
SQL search for products without an image?
Copyright © 2012, AspDotNetStorefront. All rights reserved.