473,322 Members | 1,241 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,322 software developers and data experts.

Best approach to use different datasources - ?

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)

Nov 2 '07 #1
9 1600

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*******@gmail.comwrote in message
news:11********************@z9g2000hsf.googlegroup s.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)

Nov 2 '07 #2
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_Start 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*******@gmail.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)

Nov 2 '07 #3
Ok, thanks... But what total number of records in dataset it makes
sense to store in cache? Hundreds? More?

Nov 5 '07 #4
<al*******@gmail.comwrote in message
news:11*********************@19g2000hsx.googlegrou ps.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

Nov 5 '07 #5
Thanks, guys.
But topic is going further....
Ok, i tried to create cache dataset objects in application_start
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(connectionString);
DB2Command cmduserTypes = new DB2Command("SELECT NAME AS ID, NAME
FROM Wiz_UserTypes", conn);
DB2Command cmdAdmins = new DB2Command("SELECT ID, NAME FROM
Wiz_Admins", conn);
DB2Command cmdPartners = new DB2Command("SELECT ID, NAME FROM
Wiz_Partners", conn);
DB2Command cmdEmployees = new DB2Command("SELECT ID, NAME FROM
Wiz_Employees", conn);
DB2Command cmdContracts = new DB2Command("SELECT ID, NAME FROM
Wiz_Contracts", conn);

try
{
conn.Open();
da.SelectCommand = cmduserTypes;
da.Fill(ds);
Cache["userTypes"] = ds;
da.SelectCommand = cmdAdmins;
da.Fill(ds);
Cache["admins"] = ds;
da.SelectCommand = cmdPartners;
da.Fill(ds);
Cache["partners"] = ds;
da.SelectCommand = cmdEmployees;
da.Fill(ds);
Cache["employees"] = ds;
da.SelectCommand = 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_start seemed a best place...

Nov 5 '07 #6
<al*******@gmail.comwrote in message
news:11*********************@o80g2000hse.googlegro ups.com...
Cache["contracts"] = ds;
Application["contracts"] = ds;
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

Nov 5 '07 #7
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?

Nov 6 '07 #8
<al*******@gmail.comwrote in message
news:11**********************@d55g2000hsg.googlegr oups.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_Start 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

Nov 6 '07 #9
Thanks. One more question (going further and further... :) )

I thought about considering some 'reference table' data as enum types
(i.e. using userTypes.PARTNER instead of "PARTNER" string in switch
condition).
I even tried function for type generation via
ModuleBuilder.DefineEnum, 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?

Nov 6 '07 #10

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

Similar topics

136
by: Matt Kruse | last post by:
http://www.JavascriptToolbox.com/bestpractices/ I started writing this up as a guide for some people who were looking for general tips on how to do things the 'right way' with Javascript. Their...
0
by: Greg | last post by:
Anyone having issues with their object datasources appearing and vanishing from the datasource browser? I create an object for binding as a datasource in my App_Code folder, compile my web...
4
by: markrush | last post by:
if i have 2 datasources with different table names and column headers that i want to merge i.e. "ptitle" and "name" whats the best way of doing this? are there any standard routines or should i use...
3
by: cbrown | last post by:
I am rebuilding an existing application that relies on an SQL DB. The app is a scheduling/employee management program. My question pertains to best practices in dotnet and database. I use a 3...
16
by: Rex | last post by:
Hi All - I have a question that I think MIGHT be of interest to a number of us developers. I am somewhat new to VIsual Studio 2005 but not new to VB. I am looking for ideas about quick and...
3
by: jobs | last post by:
I've got a gridview that does not have a datasourceid assigned in the markup. I'd like to switch between two datasources in the codebehind. when I do switch, I first reset the the...
19
by: cpnet | last post by:
I'm using VS2005, C#, ASP.NET 2.0. I'm trying to create a report using SQL Reporting Services (to be used in local mode so I don't have to deal with SQL Server). When I create a new report in my...
5
by: gw7rib | last post by:
I'm writing a program which has "notes" - these can appear on the screen as windows with text in. It is possible to create an "index note" - at present, this will contain a list of the titles (or...
0
by: RN | last post by:
All, I have a datagridview control displaying two columns(part number and revision). Both columns are read only. the revision colum is a datagridviewcombobox colum. I would like for the items...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
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: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
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...

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.