473,385 Members | 1,343 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,385 software developers and data experts.

One-to-many relationship in asp 3.0 object design

Hi All,

I need some help optimising my object design. Since one article has 1+
authors, I assume the logical way to represent this is for my Article object
to have a Authors property, of type "array", capable of representing any
number of authors.

The code I have written (below) works, but is very inefficient; if I want
the asp page to fetch a list of 30 authors using GetAuthors(30), there will
be a total of *31* calls to the database - one call in GetArticles(), and
one call to GetAuthors() for EACH article!

This must be a common situation, and I'd be grateful for any strategies for
dealing with this. Any help much appreciated!

TIA,

JON

PS I have actually been programming asp.net for a while, and for internal
reasons my company has just taken a step backwards in technologies (don't
ask!). It's possible my questions above show that I am thinking in a .net
way, and that I will have to compromise my OOP principles when using asp 3.0
(though I hope not!).
**************
Article object
**************

function article(objR)
{
if (objR != null)
{
this.articleID = "" + objR("art_id");
this.title = "" + objR("art_titulo");
this.authors = GetAuthors(this.articleID);
}
}
function GetArticles(howMany...)
{
//prepare return Array
var result = new Array()

sql = "SELECT ................ "
objArticlesRS = Server.CreateObject("ADODB.Recordset")
objArticlesRS.Open(sql, objC)

while(!objArticlesRS.EOF)
{
result.push(new article(objArticlesRS));
objArticlesRS.MoveNext();
}

objArticlesRS.Close()
return result
}
**************
Author object
**************
function author(objR)
{
if (objR != null)
{
this.authorID = "" + objR("aut_id");
this.name = "" + objR("Aut_Nombre");
this.nickname = "" + objR("aut_nick");
this.email = "" + objR("aut_mail");
}
}

function GetAuthors(articleID)
{

//prepare return Array
var result = new Array()

sql = "SELECT * FROM Authors ..... WHERE a.art_id = " + articleID

objAuthorsRS = Server.CreateObject("ADODB.RecordSet")
objAuthorsRS.Open(sql, objC)

while(!objAuthorsRS.EOF)
{
result.push(new author(objAuthorsRS));
objAuthorsRS.MoveNext();
}

objAuthorsRS.Close()
return result

}


Jul 19 '05 #1
7 1375
CJM

"Jon Maz" <jo****@surfeuNOSPAM.de> wrote in message
news:uP**************@TK2MSFTNGP12.phx.gbl...
Hi All,
[snip]
The code I have written (below) works, but is very inefficient; if I want
the asp page to fetch a list of 30 authors using GetAuthors(30), there will be a total of *31* calls to the database - one call in GetArticles(), and
one call to GetAuthors() for EACH article!

This must be a common situation, and I'd be grateful for any strategies for dealing with this. Any help much appreciated!

I'm not au fait with Javascript (is there a reason for this server-side
JS?), so forgive me if my interpretation is wrong, but I didnt think you
were making 31 calls to the DB. To achieve what you want, you need to one
call to return a single Article, and one call to return 30 Author records -
which is what I thought you seem to be aiming for.

Interestingly, your use of 'Select *...' well add extra round-trips, since
ADO must first determine which fields are available. It is always better to
explicitly specify the fields to be returned: 'Select A.Firstname, A.Surname
From Authors as A'

[snip]
PS I have actually been programming asp.net for a while, and for internal
reasons my company has just taken a step backwards in technologies (don't
ask!). It's possible my questions above show that I am thinking in a .net
way, and that I will have to compromise my OOP principles when using asp 3.0 (though I hope not!).


You dont have to compromise your OOP principles. I'm not sure about JScript,
but you can use classes in VBScript if you so wish. This doesnt hold any
real benefits over standard procedural code.

cheers

Chris

PS. ASP developers might resent being part of a 'backward step'! :)
Jul 19 '05 #2
You could read all authors for which you previously read articles (by using
a single select in the authors table with a where clause that includes all
the keys that were previously returned for the articles recordset and filter
this when the authors for a particular article are asked).

This is always a tradeoff anyway with exposing SQL data as objects (SQL
Server 2005 should provides enhanced support for this)...

Patrice

--

"Jon Maz" <jo****@surfeuNOSPAM.de> a écrit dans le message de
news:uP**************@TK2MSFTNGP12.phx.gbl...
Hi All,

I need some help optimising my object design. Since one article has 1+
authors, I assume the logical way to represent this is for my Article object to have a Authors property, of type "array", capable of representing any
number of authors.

The code I have written (below) works, but is very inefficient; if I want
the asp page to fetch a list of 30 authors using GetAuthors(30), there will be a total of *31* calls to the database - one call in GetArticles(), and
one call to GetAuthors() for EACH article!

This must be a common situation, and I'd be grateful for any strategies for dealing with this. Any help much appreciated!

TIA,

JON

PS I have actually been programming asp.net for a while, and for internal
reasons my company has just taken a step backwards in technologies (don't
ask!). It's possible my questions above show that I am thinking in a .net
way, and that I will have to compromise my OOP principles when using asp 3.0 (though I hope not!).
**************
Article object
**************

function article(objR)
{
if (objR != null)
{
this.articleID = "" + objR("art_id");
this.title = "" + objR("art_titulo");
this.authors = GetAuthors(this.articleID);
}
}
function GetArticles(howMany...)
{
//prepare return Array
var result = new Array()

sql = "SELECT ................ "
objArticlesRS = Server.CreateObject("ADODB.Recordset")
objArticlesRS.Open(sql, objC)

while(!objArticlesRS.EOF)
{
result.push(new article(objArticlesRS));
objArticlesRS.MoveNext();
}

objArticlesRS.Close()
return result
}
**************
Author object
**************
function author(objR)
{
if (objR != null)
{
this.authorID = "" + objR("aut_id");
this.name = "" + objR("Aut_Nombre");
this.nickname = "" + objR("aut_nick");
this.email = "" + objR("aut_mail");
}
}

function GetAuthors(articleID)
{

//prepare return Array
var result = new Array()

sql = "SELECT * FROM Authors ..... WHERE a.art_id = " + articleID

objAuthorsRS = Server.CreateObject("ADODB.RecordSet")
objAuthorsRS.Open(sql, objC)

while(!objAuthorsRS.EOF)
{
result.push(new author(objAuthorsRS));
objAuthorsRS.MoveNext();
}

objAuthorsRS.Close()
return result

}

Jul 19 '05 #3
Did you mean you want to get 30 "Articles" (you say "authors")?

If that is the case then the individual database statements for author
retrieval are the price of the data abstraction you get from your objects.

You might consider a new "articleList" object in which you get all of the
data for all articles at once by joining the articles and authors tables.
Then add a method "articleList.getNextArticle()" to retrieve the articles
one at a time. You will need a different article constructor that allows the
authors to be passed in as an array to do this.

--
Mark Schupp
Head of Development
Integrity eLearning
www.ielearning.com
"Jon Maz" <jo****@surfeuNOSPAM.de> wrote in message
news:uP**************@TK2MSFTNGP12.phx.gbl...
Hi All,

I need some help optimising my object design. Since one article has 1+
authors, I assume the logical way to represent this is for my Article object to have a Authors property, of type "array", capable of representing any
number of authors.

The code I have written (below) works, but is very inefficient; if I want
the asp page to fetch a list of 30 authors using GetAuthors(30), there will be a total of *31* calls to the database - one call in GetArticles(), and
one call to GetAuthors() for EACH article!

This must be a common situation, and I'd be grateful for any strategies for dealing with this. Any help much appreciated!

TIA,

JON

PS I have actually been programming asp.net for a while, and for internal
reasons my company has just taken a step backwards in technologies (don't
ask!). It's possible my questions above show that I am thinking in a .net
way, and that I will have to compromise my OOP principles when using asp 3.0 (though I hope not!).
**************
Article object
**************

function article(objR)
{
if (objR != null)
{
this.articleID = "" + objR("art_id");
this.title = "" + objR("art_titulo");
this.authors = GetAuthors(this.articleID);
}
}
function GetArticles(howMany...)
{
//prepare return Array
var result = new Array()

sql = "SELECT ................ "
objArticlesRS = Server.CreateObject("ADODB.Recordset")
objArticlesRS.Open(sql, objC)

while(!objArticlesRS.EOF)
{
result.push(new article(objArticlesRS));
objArticlesRS.MoveNext();
}

objArticlesRS.Close()
return result
}
**************
Author object
**************
function author(objR)
{
if (objR != null)
{
this.authorID = "" + objR("aut_id");
this.name = "" + objR("Aut_Nombre");
this.nickname = "" + objR("aut_nick");
this.email = "" + objR("aut_mail");
}
}

function GetAuthors(articleID)
{

//prepare return Array
var result = new Array()

sql = "SELECT * FROM Authors ..... WHERE a.art_id = " + articleID

objAuthorsRS = Server.CreateObject("ADODB.RecordSet")
objAuthorsRS.Open(sql, objC)

while(!objAuthorsRS.EOF)
{
result.push(new author(objAuthorsRS));
objAuthorsRS.MoveNext();
}

objAuthorsRS.Close()
return result

}

Jul 19 '05 #4
Hi Mark,

Beg pardon, you're quite right, I did mean to say "fetch a list of 30
articles using GetArticles(30)".

Let's see if I follow you: my current Article function is passed one row of
a recordset and creates one Article with it. You are suggesting crossing
the Article and Authors tables; the resulting recordset can contain >1 rows
pertaining to just one article object.

This means I would need a different Article constructor, your
articleList.getNextArticle() method, which would iterate through this more
complex recordset, processing out the necessary data from different rows to
create an Article object complete with Author info.

Is that right?

Thanks to all for the help so far,

JON
Jul 19 '05 #5
Hi Jon,

You might also consider a method on your Article object - GetAuthors() -
which returns a recordset of Author records that you can just iterate
through. I use this technique quite often but it tends to be quick and
dirty - exposing a recordset to the higher layers is sometimes not ideal but
it means you can use GetRows, Sort, etc., directly if required. If you
wanted to strengthen the typing of your object model you could implement a
collection class called Authors on your Article object, and populate this
with individual Author objects. I haven't really looked into this myself
because I never seem to find the time. It is something I intend to look into
though.

Article (1) -> Authors (1) -> Author (0,m)

Interested to hear how you go.....

Alan

"Jon Maz" <jo****@surfeuNOSPAM.de> wrote in message
news:uP**************@TK2MSFTNGP12.phx.gbl...
Hi All,

I need some help optimising my object design. Since one article has 1+
authors, I assume the logical way to represent this is for my Article object to have a Authors property, of type "array", capable of representing any
number of authors.

The code I have written (below) works, but is very inefficient; if I want
the asp page to fetch a list of 30 authors using GetAuthors(30), there will be a total of *31* calls to the database - one call in GetArticles(), and
one call to GetAuthors() for EACH article!

This must be a common situation, and I'd be grateful for any strategies for dealing with this. Any help much appreciated!

TIA,

JON

PS I have actually been programming asp.net for a while, and for internal
reasons my company has just taken a step backwards in technologies (don't
ask!). It's possible my questions above show that I am thinking in a .net
way, and that I will have to compromise my OOP principles when using asp 3.0 (though I hope not!).
**************
Article object
**************

function article(objR)
{
if (objR != null)
{
this.articleID = "" + objR("art_id");
this.title = "" + objR("art_titulo");
this.authors = GetAuthors(this.articleID);
}
}
function GetArticles(howMany...)
{
//prepare return Array
var result = new Array()

sql = "SELECT ................ "
objArticlesRS = Server.CreateObject("ADODB.Recordset")
objArticlesRS.Open(sql, objC)

while(!objArticlesRS.EOF)
{
result.push(new article(objArticlesRS));
objArticlesRS.MoveNext();
}

objArticlesRS.Close()
return result
}
**************
Author object
**************
function author(objR)
{
if (objR != null)
{
this.authorID = "" + objR("aut_id");
this.name = "" + objR("Aut_Nombre");
this.nickname = "" + objR("aut_nick");
this.email = "" + objR("aut_mail");
}
}

function GetAuthors(articleID)
{

//prepare return Array
var result = new Array()

sql = "SELECT * FROM Authors ..... WHERE a.art_id = " + articleID

objAuthorsRS = Server.CreateObject("ADODB.RecordSet")
objAuthorsRS.Open(sql, objC)

while(!objAuthorsRS.EOF)
{
result.push(new author(objAuthorsRS));
objAuthorsRS.MoveNext();
}

objAuthorsRS.Close()
return result

}

Jul 19 '05 #6
Yes, Just be sure to either disconnect the recordset or dump the raw data
into an array so that you can close the recordset and connection as soon as
possible.

To be completely honest about it however, I would probably stay with your
current approach unless it was demonstrably too inefficient. The additional
complexity could easily bite you in the behind.

--
Mark Schupp
Head of Development
Integrity eLearning
www.ielearning.com
"Jon Maz" <jo****@surfeuNOSPAM.de> wrote in message
news:em**************@TK2MSFTNGP12.phx.gbl...
Hi Mark,

Beg pardon, you're quite right, I did mean to say "fetch a list of 30
articles using GetArticles(30)".

Let's see if I follow you: my current Article function is passed one row of a recordset and creates one Article with it. You are suggesting crossing
the Article and Authors tables; the resulting recordset can contain >1 rows pertaining to just one article object.

This means I would need a different Article constructor, your
articleList.getNextArticle() method, which would iterate through this more
complex recordset, processing out the necessary data from different rows to create an Article object complete with Author info.

Is that right?

Thanks to all for the help so far,

JON

Jul 19 '05 #7
Hi All,

In case anyone's interested, here's my solution.

Thanks to all for the help!

JON

---------------------------------------------------

function GetArticles(howMany...)
{
var basicArticlesArray = new Array()

var createdArticleIDs = "";

sql = "SELECT ................ "

objArticlesRS = Server.CreateObject("ADODB.Recordset")
objArticlesRS.Open(sql, objC)

while(!objArticlesRS.EOF)
{
basicArticlesArray.push(new article(objArticlesRS));
createdArticleIDs += objArticlesRS("art_id") + ",";
objArticlesRS.MoveNext();
}

objArticlesRS.Close()

var articlesWithAuthorsArray = AddAuthorsToArticles(basicArticlesArray,
createdArticleIDs);
return articlesWithAuthorsArray;
}

function AddAuthorsToArticles(articlesArray, articleIDString)
{
//turn the articleIDString into an articleIDClause
re = new RegExp(",", "gi");
var articleIDClause = "AND (art_id = " + articleIDString.replace(re, " OR
art_id = ") + ") ";

//create the Sql for querying the Authors table using this articleIDClause
sql = "SELECT * FROM Autor a ";
sql += "INNER JOIN Articulo_Autor aa ";
sql += "ON a.aut_ID = aa.aut_ID ";
sql += "WHERE a.aut_activo = 1 ";
sql += articleIDClause;

//get a recordset holding this Authors information
objlocalAuthorsRS = Server.CreateObject("ADODB.RecordSet");
objlocalAuthorsRS.Open(sql, objC);

//loop through the recordset containing authors information
while(!objlocalAuthorsRS.EOF)
{
//create an author object out of current objlocalAuthorsRS record
var newAuthor = new author(objlocalAuthorsRS);

//check the art_id field of objlocalAuthorsRS
var currentArticleID_Author = "" + objlocalAuthorsRS("art_id");

//get the Article with this articleID out of articlesArray
for (var i=0; i < articlesArray.length; i++)
{
var currentArticle = articlesArray[i];
var currentArticleID_Article = currentArticle.articleID;
var currentArticleIndex = GetIndex(articlesArray, currentArticle)

if (currentArticleID_Author == currentArticleID_Article)
{
//add the author object to the article.authors array
currentArticle.authors.push(newAuthor);

//put this ArticleWithAuthor object back into articlesArray, overwriting
the original ArticleWithoutAuthor
articlesArray[currentArticleIndex] = currentArticle;
}
}

objlocalAuthorsRS.MoveNext();
}
objlocalAuthorsRS.Close();
return articlesArray;
}
function GetIndex(array, object)
{
var index = dummyInteger;

for (var i=0; i < array.length; i++)
{
currentObject = array[i];
if(currentObject == object)
{
return i;
}
}

return index;
}


----- Original Message -----
From: "Alan Howard" <Xa***********@Xparadise.net.nzX>
Newsgroups: microsoft.public.inetserver.asp.general,
microsoft.public.scripting.vbscript,
microsoft.public.scripting.jscript,microsoft.publi c.inetserver.asp.db
Sent: Tuesday, June 29, 2004 12:04 AM
Subject: Re: One-to-many relationship in asp 3.0 object design

Hi Jon,

Interested to hear how you go.....

Alan

Jul 19 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: rashika | last post by:
Hi : Can i call 2 procs within one task? I have sp_proc1 ? (and have declared one global variable as input parameter) now i have another sp_proc2 which uses same input parameter but if i...
7
by: mp | last post by:
No value given for one or more required parameters. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information...
2
by: Edward | last post by:
I've one datagrid and one frame with id ="frmdetail", in the datagrid, there is one template column which includes one HyperLink, <asp:HyperLink id="detaillnk" runat="server" text='detail'...
0
by: Ray5531 | last post by:
I have two user control ,one is getting a vertical image and the other one is getting some rows from database and show them as bullet list.How should I put these two besides each other.I put them...
8
by: Agnes | last post by:
I got one dataset, one datatable, but two datagrids. I use dataview.rowfilter = 'sex ="F" and dataview.rowfilter = 'sex ="M" to filter the information and set to two datagrids's datasource =...
1
by: teddysnips | last post by:
Crystal Report problem - HELP! The fragments below come from two fundamentally identical applications. The one at the top - PrintEASAReport - throws an exception on the final line with the...
5
by: Peter Rilling | last post by:
Okay, the other day I was talking with someone about assemblies. He said something that I am not really sure about. He said that a DLL or EXE can contain more then one assembly (although the IDE...
41
by: Chris Lasher | last post by:
A friend of mine with a programming background in Java and Perl places each class in its own separate file in . I informed him that keeping all related classes together in a single file is more in...
1
by: ramkumar533 | last post by:
Hi all.. I am designig a form. its having a checkedlistbox control in vb.net2005 v1.2 . During the execution only one item should be checked at a moment. if one value is already selected,...
7
by: somnamblst | last post by:
I am using jQuery & hideAllExcept.js from this demo tute http://enure.net/dev/hide-all-except-one/ The issue is that on a page with other content, the images I have placed in the toggleThis...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.