Best approach to use different datasources - ?

[alexandis]

I have a big database, a lot of tables, so I will have a lot of pages
where i create a new record.
There will be a lot of 'reference' items - let's say 'Create user' ->
'Select user type' <Dropdownlist-Admin, Partner, Client type A,
Client type B, Contract (is taken from UserTypes table)

Such 'reference' lists (based on 'reference' tables) should be used on
several pages, I will have a lot of reference tables.

So - what is the best practices of setting such things in ASP.NET?

Should I create some SPs with selecting data, where table name,
columns are parameters and then filling datasource dynamically or i
should create some 'static' datasources components, bound every to
specific table, and so on? Should i create some classes to hold data
from tables in memory for short 'reference' tables?

Please, tell me how YOU do it in your project (especially if it proved
to be effective)

[Barrie Wilson]

it's a pretty broad question ... but ... it sounds like you have a lot of
tables which will be used repeatedly in your application, which of course is
quite common. There are a lot of ways to handle this, but generally it
would not be a bad idea to create a data access layer (DAL) which can be
reused throughout the application by whatever pages might need it. You need
to think through how you will need to access the data: select data from
<some_tableby name, region, id, etc and set up the appropriate methods
which can accept parameters to return the data required

Visual Studio provides adequate tools to do all of this work ... you might
start by adding a DataSet to your web project and learning how to configure
it, which will take a little time if you haven't done it before ... what
you're doing is creating "Table Adapters," which are basically just classes
which provide data access methods

You don't have to use Visual Studio tools; nothing is stopping you from
writing your own data access classes ... if all of this sounds completely
foreign you should probably begin by reading some tutorials on the subject
.... a visit to http://www.asp.net is as good a place as any to begin

<al*******@gmai l.comwrote in message
news:11******** ************@z9 g2000hsf.google groups.com...

>I have a big database, a lot of tables, so I will have a lot of pages
where i create a new record.
There will be a lot of 'reference' items - let's say 'Create user' ->
'Select user type' <Dropdownlist-Admin, Partner, Client type A,
Client type B, Contract (is taken from UserTypes table)

Such 'reference' lists (based on 'reference' tables) should be used on
several pages, I will have a lot of reference tables.

So - what is the best practices of setting such things in ASP.NET?

Should I create some SPs with selecting data, where table name,
columns are parameters and then filling datasource dynamically or i
should create some 'static' datasources components, bound every to
specific table, and so on? Should i create some classes to hold data
from tables in memory for short 'reference' tables?

Please, tell me how YOU do it in your project (especially if it proved
to be effective)

[=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=]

if you have a number of utilty controls that will reappear on different
pages, then an appropriate way to handle this is to get all the data in the
Session_Start or Application_Sta rt handler, and store the datasets or
datatables in cache or Session with friendly names so the data can be re-used
on different pages without having to keep going back to the database every
time. Session being user-specific, Cache being application -wide.
-- Peter
// It works on my machine!
http://www.eggheadcafe.com
unBlog: http://petesbloggerama.blogspot.com
BlogMetaFinder: http://www.blogmetafinder.com

"al*******@gmai l.com" wrote:

I have a big database, a lot of tables, so I will have a lot of pages
where i create a new record.
There will be a lot of 'reference' items - let's say 'Create user' ->
'Select user type' <Dropdownlist-Admin, Partner, Client type A,
Client type B, Contract (is taken from UserTypes table)

Such 'reference' lists (based on 'reference' tables) should be used on
several pages, I will have a lot of reference tables.

So - what is the best practices of setting such things in ASP.NET?

Should I create some SPs with selecting data, where table name,
columns are parameters and then filling datasource dynamically or i
should create some 'static' datasources components, bound every to
specific table, and so on? Should i create some classes to hold data
from tables in memory for short 'reference' tables?

Please, tell me how YOU do it in your project (especially if it proved
to be effective)

[alexandis]

Ok, thanks... But what total number of records in dataset it makes
sense to store in cache? Hundreds? More?

[Mark Rae [MVP]]

<al*******@gmai l.comwrote in message
news:11******** *************@1 9g2000hsx.googl egroups.com...

Ok, thanks... But what total number of records in dataset it makes
sense to store in cache? Hundreds? More?

That will depend on the amount of RAM your webserver can spare... Each case
is different...

However, generally speaking, datatables don't bloat very much - if you've
got 1,000 records of 200 bytes each, that won't represent very much more
than 200k on the server - chances are, you can spare that quite easily...
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[alexandis]

Thanks, guys.
But topic is going further....
Ok, i tried to create cache dataset objects in application_sta rt
method, but...
cache object is still unavailable there?? Error says: cache is a type,
but used as a variable...
//=============== ====
DataSet ds = new DataSet();
DB2DataAdapter da = new DB2DataAdapter( );

DB2Connection conn = new DB2Connection(c onnectionString );
DB2Command cmduserTypes = new DB2Command("SEL ECT NAME AS ID, NAME
FROM Wiz_UserTypes", conn);
DB2Command cmdAdmins = new DB2Command("SEL ECT ID, NAME FROM
Wiz_Admins", conn);
DB2Command cmdPartners = new DB2Command("SEL ECT ID, NAME FROM
Wiz_Partners", conn);
DB2Command cmdEmployees = new DB2Command("SEL ECT ID, NAME FROM
Wiz_Employees", conn);
DB2Command cmdContracts = new DB2Command("SEL ECT ID, NAME FROM
Wiz_Contracts", conn);

try
{
conn.Open();
da.SelectComman d = cmduserTypes;
da.Fill(ds);
Cache["userTypes"] = ds;
da.SelectComman d = cmdAdmins;
da.Fill(ds);
Cache["admins"] = ds;
da.SelectComman d = cmdPartners;
da.Fill(ds);
Cache["partners"] = ds;
da.SelectComman d = cmdEmployees;
da.Fill(ds);
Cache["employees"] = ds;
da.SelectComman d = cmdContracts;
da.Fill(ds);
Cache["contracts"] = ds;
//=============== ====

If i do the same assignment in page_load method of some page - it's
ok.

But Peter was right, i guess: there are _several_ places I would
access some my tables from, so I'd better cache all datasets in _one_
place once and change this data explicitly on the pages where this
data could change: usually these are admin pages... So
application_sta rt seemed a best place...

[Mark Rae [MVP]]

<al*******@gmai l.comwrote in message
news:11******** *************@o 80g2000hse.goog legroups.com...

Cache["contracts"] = ds;

Application["contracts"] = ds;
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[alexandis]

Application[] is what i used for 'tiny' settings like connection
string. Is it really a good idea to use application[] for storing
datasets instead of cache?

Btw, i got exception in global.asax file and put breakpoint to trace
it next time I run debug, but somehow breakpoint has not fired (right
away i've seen my login page, bypassing global.asax code) :( Why it
could be?

[Mark Rae [MVP]]

<al*******@gmai l.comwrote in message
news:11******** **************@ d55g2000hsg.goo glegroups.com.. .

Application[] is what i used for 'tiny' settings like connection
string. Is it really a good idea to use application[] for storing
datasets instead of cache?

Unless you're using a web farm, there's not much difference for the data
that you want to cache...

The main difference between Application and Cache is that Cache has the
concepts of timeout and priority, but Application doesn't:
http://www.codeproject.com/aspnet/ca...ntinaspnet.asp

Btw, i got exception in global.asax file and put breakpoint to trace
it next time I run debug, but somehow breakpoint has not fired (right
away i've seen my login page, bypassing global.asax code) :( Why it
could be?

Application_Sta rt fires only when the application starts... If your
application hasn't stopped, it won't fire as a result of the next session...

Simply recycle your application and it will fire again...
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[alexandis]

Thanks. One more question (going further and further... :) )

I thought about considering some 'reference table' data as enum types
(i.e. using userTypes.PARTN ER instead of "PARTNER" string in switch
condition).
I even tried function for type generation via
ModuleBuilder.D efineEnum, but... i totally forgot, that it's a DYNAMIC
type and VS won't let me compile it...
There's still a save method for making dll, so i can't put this into
pre_event, but maybe it's getting too complicated and slow, what would
you say?

So... what is a best approach for using error-proof & neat
representation of 'enum-like' db data amongst the pages?