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_table> by 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

 

[Guest]

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

 

[alexandis]

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

 

[Mark Rae [MVP]]

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...

 

[alexandis]

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...

 

[Mark Rae [MVP]]

Cache["contracts"] = ds;

Application["contracts"] = ds;

 

[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]]

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/cachemanagementinaspnet.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...

 

[alexandis]

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?

 

[Mark Rae [MVP]]

maybe it's getting too complicated and slow

Complicated? Maybe....

Slow? Probably not...

Worthwhile? No, IMO...

If you're interested, I tend to to the following:

1) Build a method to persist your cached data, and call it from
Application_Start

void Application_Start(object sender, EventArgs e)
{
try
{
CApplication.SetupEnvironment();
}
catch (Exception ex)
{
CApplication.GlobalExceptionHandler(ex);
}
}

2) Create the methods for storing the datatables and retrieving individual
values out of them later

public sealed class CApplication
{

private static SqlParameter mobjSqlParameter;
private static List<SqlParameter> mlstSqlParameters = new
List<SqlParameter>();

/// <summary>
/// Initialises the system at startup
/// </summary>
public static void SetupEnvironment()
{
try
{
HttpContext.Current.Application["Global.tlkpCurrency"] =
LoadGlobalTable("Global.tlkpCurrency", "strCurrencyCode");
// etc
}
catch (Exception)
{
throw;
}
}

/// <summary>
/// Loads the DataTable into the Application object
/// </summary>
/// <param name="pstrTable">The table to be loaded</param>
public static DataSet LoadGlobalTable(string pstrTable, string pstrOrderBy)
{
try
{
mlstSqlParameters.Clear();

mobjSqlParameter = new SqlParameter("@pstrTable",
SqlDbType.VarChar);
mobjSqlParameter.Size = 40;
mobjSqlParameter.Value = pstrTable;
mlstSqlParameters.Add(mobjSqlParameter);

mobjSqlParameter = new SqlParameter("@pstrOrderBy",
SqlDbType.VarChar);
mobjSqlParameter.Size = 40;
mobjSqlParameter.Value = pstrOrderBy;
mlstSqlParameters.Add(mobjSqlParameter);
return CSqlClient.GetDataSet("Global.usp_Select",
mlstSqlParameters);
}
catch (Exception)
{
throw;
}
}

/// <summary>
/// Gets an ID out of an Application DataSet object from its associated
value
/// </summary>
/// <param name="pstrTable">The DataSet to search</param>
/// <param name="pstrValue">The value to find</param>
/// <returns>The ID associated with the value</returns>
public static byte GetGlobal(string pstrTable, string pstrValue)
{
try
{
byte bytID = 0;

using (DataSet objDS =
(DataSet)HttpContext.Current.Application[pstrTable])
{
foreach (DataRow dr in objDS.Tables[0].Rows)
{
if (dr[1].ToString() == pstrValue)
{
bytID = Convert.ToByte(dr[0]);
break;
}
}
}
return bytID;
}
catch (Exception)
{
throw;
}
}

/// <summary>
/// Gets a value out of an Application DataSet object from its associated ID
/// </summary>
/// <param name="pstrTable">The DataSet to search</param>
/// <param name="pbytID">The ID to find</param>
/// <returns>The ID associated with the value</returns>
public static string GetGlobal(string pstrTable, byte pbytID)
{
try
{
string strValue = String.Empty;

using (DataSet objDS =
(DataSet)HttpContext.Current.Application[pstrTable])
{
foreach (DataRow dr in objDS.Tables[0].Rows)
{
if (Convert.ToByte(dr[0]) == pbytID)
{
strValue = dr[1].ToString();
break;
}
}
}
return strValue;
}
catch (Exception)
{
throw;
}
}

}


3) Then you can use the methods anywhere you like throughout the rest of
your app with just one line of code:

string strCurrencyFullName = CApplication.GetGlobal("Global.tlkpCurrency",
"USD").ToString();


[In the above code, CSqlClient is part of my DAL...]