This is a question that someone familiar with ASP.NET and ADO.NET DataSets
and DataTables should be able to answer fairly easily. The basic question is
how I can efficiently match data from one dataset to data in a second
dataset, using a common key. I will first describe the problem in words and
then I will show my code, which has most of the solution done already.
I have built an ASP.NET that queries an Index Server and returns a DataSet
corresponding to a bunch of PDF files. I bind this DataSet to an ASP.NET
gridview, but I want to include more information than is in Index Server
alone (and no, I don't want to use IS custom properties, nor do I want to
use MSFT's great new free search server quite yet.) . I want to do a second
database lookup to gather more information and I have a good idea how to do
that. I have successfully manually added extra columns to my DataSet's
datatable and populated them with database data in an inefficient way which
I will describe in the next paragraph.
The filenames associated with the PDFs in the Index Server index include
database Primary Keys and my application parses those keys out. I use those
primary keys to subsequently do a second collection of database lookups.
Currently just for test purposes I have the application doing dozens of
lookups: it's querying the database on each iteration of a loop. This sucks
from a performance standpoint. In anticipation of doing a single query of
all necessary lookups, I have made it so that the system builds a functional
SQL query string that pulls all necessary records with additional metadata.
What I need to know is:
1) Syntax for creating a new dataset based on my SQL string. I normally use
the EntitySpaces ORM framework so my experience with Datasets is very weak.
2) How to match the parts of the new dataset into my existing dataset in an
EFFICIENT way. My code below will show a successful solution based on a very
INEFFICIENT way, using repeated lookups. I expect the solution will involve
repeated filterings of the single dataset based on the primary key.
Now to my existing, inefficient code. I will walk you through it.
// Filling a DataSet called results, and manually adding some additional
columns to it:
if (dbAdapter != null) dbAdapter.SelectCommand.CommandText =
GetUwitmPdfArchiveQuery;
DataSet ds = new DataSet("Results");
dbAdapter.Fill(ds);
DataColumn dcolColumn = new DataColumn("ChrisSummary",
typeof(string));
ds.Tables[0].Columns.Add(dcolColumn);
DataColumn dcolColumn2 = new DataColumn("ChrisTitle",
typeof(string));
ds.Tables[0].Columns.Add(dcolColumn2);
DataColumn dcolColumn3 = new DataColumn("PublicationTitle",
typeof(string));
ds.Tables[0].Columns.Add(dcolColumn3);
DataColumn dcolColumn4 = new DataColumn("OriginalArticleUrl",
typeof(string));
ds.Tables[0].Columns.Add(dcolColumn4);
// [empty variables are initialized]
// start looking through the datarows. There is a DB primary key in the data
one of the columns of the datarow . I grab that key and assign it the name
tempInt
foreach (DataRow dr in ds.Tables[0].Rows)
{
int tempInt =
Convert.ToInt32(dr[4].ToString().Replace(".pdf", ""));
try
{
// I start concatenating a SQL string of all of the primary keys
listOfIds = listOfIds + " OR ContentID ='" +
dr[4].ToString().Replace(".pdf", "") + "'";
// This is the inefficient part. I'm using that key along with my
EntitySpaces ORM framework to do lookups each time this loop is iterated,
and populate my manually added rows. I want instead to do a single lookup
and use a second filtered DataSet to populate my manually added rows with DB
information. So I'm going to have to loop through once to get enough info to
build my SQL string, then do something else to build the DataSet I need.
This is where I need the help.
Contentitems ci = new Contentitems();
ci.LoadByPrimaryKey(Convert.ToInt32(tempInt));
dr["OriginalArticleUrl"] = ci.ConUrl;
dr["ChrisSummary"] = ci.ConSummary;
dr["ChrisTitle"] = ci.ConTitle;
dr["PublicationTitle"] = ci.ConSource;
// I finish my SQL statement and whack off some extraneous stuff from my SQL
string
listOfIds = listOfIds.Substring(0, listOfIds.Length - 25);
string sqlQueryIds = "Select * From Contentitems WHERE
ContentID='99999999' " + listOfIds;
Would very much appreciate help making the above code efficient! Thanks!!!
-KF
