G
Guest
I have a question about spawning and displaying subordinate list controls
within a list control. I'm also interested in feedback about the design of
my search application. Lots of code is at the end of this message, but I
will start with an overview of the problem.
I've made a content management solution for my work with a decently
structured relational database system. The CMS stores articles. The CMS also
stores related items -- articles and other media related to the primary
articles.
I've also implemented a search in ASP.NET using Indexing Services and some
custom properties in Indexing services. The system initates an ADO.NET query
against the Indexing Services engine and then fills a dataset. The dataset
is bound to a grid, displaying the results.
What I want to do is to be able to display any "related articles" as part of
my search results. The related articles would appear beneath a search result
for a primary article. So the results page would look something like
SEARCH RESULTS:
item ID 2001 Dogs and cats can be friends, researchers say
RELATED ARTICLE: John Katz says tech writing
"for the dogs" (item ID 23003)
RELATED AUDIO: Listen to "Sit Ubu, Sit (item
ID 3030430)
item ID 2002 UW researchers find fabulous field of "black smokers"
RELATED AUDO: Harmonic tones (item ID 39395)
RELATED VIDEO: "Old Smokey" fries a crab
(item ID 43838)
I have two questions I need to answer. One relates to the best ways to make
this search performant. The second question relates to the presentation of
subordinate items.
Question #1: Data retrieval and performance question:
I have an idea for a way to design this search so that we don't kill the
database server. A search would initiate two queries and fill two datasets.
One query would fill a dataset of the search results. The second query would
fill a dataset of ALL related articles, including the database IDs of the
related articles, which are relationally linked to the parent article. On
display, the "related articles" dataset would be filtered on the parentID of
the result being iterated. This would be the equivalent of the ADO recordset
..Filter method.
I'm wondering if this a good design, and if so, the syntax and process I'd
use to successively filter a dataset in this way, once on each element in my
loop.
Question #2 is suggested by the scenario above. If related articles are
found associated with a given parentID, they need to be displayed in the
results datagrid. A hack-ey way to do this would be to embed a method in the
declarative markup, e.g.
<%#GetRelatedArticlesAsAFixedAndNonBindableStreamOfHTML(Container.Dataitem)%>.
The markup would call a function with each iteration of the list control,
and I would have more or less what I want.
A better way would be to have the listcontrol conditionally spawn a second
listcontrol within the itemtemplate if there were related articles, and bind
any "related items" associated with the parentID to that nested listcontrol.
That seems better than the hack described above.
Summarizing, I need to know three things:
+ Is my design any good, or is there a better option reflecting the
hierarchical arrangement of this data? I am using EntitySpaces ORM which
supports a hierarchical model, but that doesn't mean I know how to use it as
well as I should...
+ What's the syntax for filtering and unfiltering an ADO.NET DataSet?
+ What's the syntax/process for spawning nested Datalist controls and
binding my filtered dataset to these child controls (ideas: include an
"empty" datalist control in every instance, or dynamically replace a
placeholder...? )
The code that I'm using to do all this follows...
private void Search()
{
// Create a new DataSet and fill it.
try
{
this.dbAdapter.SelectCommand.CommandText = Command;
DataSet ds = new DataSet("Results");
this.dbAdapter.Fill(ds);
this.lblResultCount.ForeColor = Color.Black;
int rows = ds.Tables[0].Rows.Count;
this.lblResultCount.Text = String.Format("{0} document{1} found
in theXXXX archive that matched your search for <b> " +
(Request.QueryString["Search"]) + " </b>{2}",
rows, rows == 1 ? " was" : "s were", rows == 0 ? "." : ":");
// Bind the resulting DataSet.
this.dgResultsGrid.DataSource = ds;
this.dgResultsGrid.DataBind();
// If all was bound well, display the DataGrid.
this.dgResultsGrid.Visible = (rows > 0);
}
catch (Exception ex)
{
this.lblResultCount.ForeColor = Color.Red;
this.lblResultCount.Text = String.Format("Unable to retreive a
list " +
"of documents for the specified query: {0}", ex.Message);
this.dgResultsGrid.Visible = false;
}
finally
{
this.lblResultCount.Visible = true;
}
}
This is the function that builds the command query:
private string Command
{
get
{
// Construct the base query.
string query = String.Format(@"
SELECT Rank, Path, DocTitle, Filename, Characterization,
Write, MyContentIDWhichIsARetrievableCustomPropertyInIndexingServer
FROM SCOPE('SHALLOW TRAVERSAL OF
""c:\Inetpub\wwwroot\whatever""')
WHERE NOT CONTAINS(Path, '""_vti_"" OR "".config""')",
"/search/indexes/myIndex");
// Get the query string and remove all semi-colons, which should
stop
// attempt to run malicious SQL code.
// string queryFromForm =
FixInjection(Request.QueryString["Search"].ToLower());
string queryFromForm =
(Request.QueryString["Search"].ToLower());
string text = queryFromForm.Replace(";", "");
// Conditionally construct the rest of the WHERE clause.
string type = "all";
if (text.StartsWith("\"") && (text.EndsWith("\"")))
{
type = "exact";
}
string fmt = @" AND (CONTAINS('{0}') OR CONTAINS(DocTitle,
'{0}'))";
if (type == "all" || type == "any" || type == "boolean")
{
string[] words = text.Split(' ');
int len = words.Length;
for (int i = 0; i < len; i++)
{
string word = words;
words = String.Format(@"""{0}""", word);
if (i < len - 1)
{
words += " AND";
}
}
query += String.Format(fmt, String.Join(" ", words));
}
else if (type == "exact")
{
query += String.Format(fmt, text);
}
private void InitializeComponent()
{
this.dbAdapter = new System.Data.OleDb.OleDbDataAdapter();
this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();
this.dbConnection = new System.Data.OleDb.OleDbConnection();
this.dgResultsGrid.PageIndexChanged += new
System.Web.UI.WebControls.DataGridPageChangedEventHandler(this.dgResultsGrid_PageIndexChanged);
//
// dbAdapter
//
this.dbAdapter.SelectCommand = this.oleDbSelectCommand1;
//
// oleDbSelectCommand1
//
this.oleDbSelectCommand1.Connection = this.dbConnection;
//
// dbConnection
//
this.dbConnection.ConnectionString = "Provider=MSIDXS.1;Data
Source=MyIndexingServerDataSource";
this.Load += new System.EventHandler(this.Page_Load);
}
The declarative parts of this appear as follows:
<asp:datagrid id=dgResultsGrid runat="server" PageSize="25"
AllowPaging="True" AutoGenerateColumns="False" Visible="False"
GridLines="None" Font-Names="Verdana" Font-Size="9pt">
<itemstyle horizontalalign="Left" verticalalign="Top">
</ItemStyle>
<headerstyle font-bold="True">
</HeaderStyle>
<columns>
<asp:TemplateColumn HeaderText="">
<itemstyle horizontalalign="Left" verticalalign="Top">
</ItemStyle>
<itemtemplate>
<p><asp:hyperlink ID="Hyperlink1" Font-Bold=True runat="server"
NavigateUrl='<%# GetMyUrl(Container.DataItem)%>'><%#
GetTitle(Container.DataItem)%></asp:hyperlink><br>
<i>Published</i> <asp:label ID="Label3" Font-Italic = true
runat="server"><%# GetPubDate(Container.DataItem)%></asp:label><br />
<asp:label ID="Label2" runat="server"><%#
GetCharacterization(Container.DataItem)%></asp:label><i><br />
</i> </p>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
<pagerstyle mode="NumericPages">
</PagerStyle>
</asp:datagrid>
<tr>
<td colspan="4">
</td>
</tr>
</table>
Thanks in advance for any help you can provide to this (long!) question. I
surely appreciate it.
-KF
within a list control. I'm also interested in feedback about the design of
my search application. Lots of code is at the end of this message, but I
will start with an overview of the problem.
I've made a content management solution for my work with a decently
structured relational database system. The CMS stores articles. The CMS also
stores related items -- articles and other media related to the primary
articles.
I've also implemented a search in ASP.NET using Indexing Services and some
custom properties in Indexing services. The system initates an ADO.NET query
against the Indexing Services engine and then fills a dataset. The dataset
is bound to a grid, displaying the results.
What I want to do is to be able to display any "related articles" as part of
my search results. The related articles would appear beneath a search result
for a primary article. So the results page would look something like
SEARCH RESULTS:
item ID 2001 Dogs and cats can be friends, researchers say
RELATED ARTICLE: John Katz says tech writing
"for the dogs" (item ID 23003)
RELATED AUDIO: Listen to "Sit Ubu, Sit (item
ID 3030430)
item ID 2002 UW researchers find fabulous field of "black smokers"
RELATED AUDO: Harmonic tones (item ID 39395)
RELATED VIDEO: "Old Smokey" fries a crab
(item ID 43838)
I have two questions I need to answer. One relates to the best ways to make
this search performant. The second question relates to the presentation of
subordinate items.
Question #1: Data retrieval and performance question:
I have an idea for a way to design this search so that we don't kill the
database server. A search would initiate two queries and fill two datasets.
One query would fill a dataset of the search results. The second query would
fill a dataset of ALL related articles, including the database IDs of the
related articles, which are relationally linked to the parent article. On
display, the "related articles" dataset would be filtered on the parentID of
the result being iterated. This would be the equivalent of the ADO recordset
..Filter method.
I'm wondering if this a good design, and if so, the syntax and process I'd
use to successively filter a dataset in this way, once on each element in my
loop.
Question #2 is suggested by the scenario above. If related articles are
found associated with a given parentID, they need to be displayed in the
results datagrid. A hack-ey way to do this would be to embed a method in the
declarative markup, e.g.
<%#GetRelatedArticlesAsAFixedAndNonBindableStreamOfHTML(Container.Dataitem)%>.
The markup would call a function with each iteration of the list control,
and I would have more or less what I want.
A better way would be to have the listcontrol conditionally spawn a second
listcontrol within the itemtemplate if there were related articles, and bind
any "related items" associated with the parentID to that nested listcontrol.
That seems better than the hack described above.
Summarizing, I need to know three things:
+ Is my design any good, or is there a better option reflecting the
hierarchical arrangement of this data? I am using EntitySpaces ORM which
supports a hierarchical model, but that doesn't mean I know how to use it as
well as I should...
+ What's the syntax for filtering and unfiltering an ADO.NET DataSet?
+ What's the syntax/process for spawning nested Datalist controls and
binding my filtered dataset to these child controls (ideas: include an
"empty" datalist control in every instance, or dynamically replace a
placeholder...? )
The code that I'm using to do all this follows...
private void Search()
{
// Create a new DataSet and fill it.
try
{
this.dbAdapter.SelectCommand.CommandText = Command;
DataSet ds = new DataSet("Results");
this.dbAdapter.Fill(ds);
this.lblResultCount.ForeColor = Color.Black;
int rows = ds.Tables[0].Rows.Count;
this.lblResultCount.Text = String.Format("{0} document{1} found
in theXXXX archive that matched your search for <b> " +
(Request.QueryString["Search"]) + " </b>{2}",
rows, rows == 1 ? " was" : "s were", rows == 0 ? "." : ":");
// Bind the resulting DataSet.
this.dgResultsGrid.DataSource = ds;
this.dgResultsGrid.DataBind();
// If all was bound well, display the DataGrid.
this.dgResultsGrid.Visible = (rows > 0);
}
catch (Exception ex)
{
this.lblResultCount.ForeColor = Color.Red;
this.lblResultCount.Text = String.Format("Unable to retreive a
list " +
"of documents for the specified query: {0}", ex.Message);
this.dgResultsGrid.Visible = false;
}
finally
{
this.lblResultCount.Visible = true;
}
}
This is the function that builds the command query:
private string Command
{
get
{
// Construct the base query.
string query = String.Format(@"
SELECT Rank, Path, DocTitle, Filename, Characterization,
Write, MyContentIDWhichIsARetrievableCustomPropertyInIndexingServer
FROM SCOPE('SHALLOW TRAVERSAL OF
""c:\Inetpub\wwwroot\whatever""')
WHERE NOT CONTAINS(Path, '""_vti_"" OR "".config""')",
"/search/indexes/myIndex");
// Get the query string and remove all semi-colons, which should
stop
// attempt to run malicious SQL code.
// string queryFromForm =
FixInjection(Request.QueryString["Search"].ToLower());
string queryFromForm =
(Request.QueryString["Search"].ToLower());
string text = queryFromForm.Replace(";", "");
// Conditionally construct the rest of the WHERE clause.
string type = "all";
if (text.StartsWith("\"") && (text.EndsWith("\"")))
{
type = "exact";
}
string fmt = @" AND (CONTAINS('{0}') OR CONTAINS(DocTitle,
'{0}'))";
if (type == "all" || type == "any" || type == "boolean")
{
string[] words = text.Split(' ');
int len = words.Length;
for (int i = 0; i < len; i++)
{
string word = words;
words = String.Format(@"""{0}""", word);
if (i < len - 1)
{
words += " AND";
}
}
query += String.Format(fmt, String.Join(" ", words));
}
else if (type == "exact")
{
query += String.Format(fmt, text);
}
private void InitializeComponent()
{
this.dbAdapter = new System.Data.OleDb.OleDbDataAdapter();
this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();
this.dbConnection = new System.Data.OleDb.OleDbConnection();
this.dgResultsGrid.PageIndexChanged += new
System.Web.UI.WebControls.DataGridPageChangedEventHandler(this.dgResultsGrid_PageIndexChanged);
//
// dbAdapter
//
this.dbAdapter.SelectCommand = this.oleDbSelectCommand1;
//
// oleDbSelectCommand1
//
this.oleDbSelectCommand1.Connection = this.dbConnection;
//
// dbConnection
//
this.dbConnection.ConnectionString = "Provider=MSIDXS.1;Data
Source=MyIndexingServerDataSource";
this.Load += new System.EventHandler(this.Page_Load);
}
The declarative parts of this appear as follows:
<asp:datagrid id=dgResultsGrid runat="server" PageSize="25"
AllowPaging="True" AutoGenerateColumns="False" Visible="False"
GridLines="None" Font-Names="Verdana" Font-Size="9pt">
<itemstyle horizontalalign="Left" verticalalign="Top">
</ItemStyle>
<headerstyle font-bold="True">
</HeaderStyle>
<columns>
<asp:TemplateColumn HeaderText="">
<itemstyle horizontalalign="Left" verticalalign="Top">
</ItemStyle>
<itemtemplate>
<p><asp:hyperlink ID="Hyperlink1" Font-Bold=True runat="server"
NavigateUrl='<%# GetMyUrl(Container.DataItem)%>'><%#
GetTitle(Container.DataItem)%></asp:hyperlink><br>
<i>Published</i> <asp:label ID="Label3" Font-Italic = true
runat="server"><%# GetPubDate(Container.DataItem)%></asp:label><br />
<asp:label ID="Label2" runat="server"><%#
GetCharacterization(Container.DataItem)%></asp:label><i><br />
</i> </p>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
<pagerstyle mode="NumericPages">
</PagerStyle>
</asp:datagrid>
<tr>
<td colspan="4">
</td>
</tr>
</table>
Thanks in advance for any help you can provide to this (long!) question. I
surely appreciate it.
-KF