Dynamic List from multiple databases

This public forum is for user-to-user discussions of ASP.NET Maker. Note that this is not support forum.

Dynamic List from multiple databases

Postby aspmaker_fan » Sat Oct 07, 2017 9:52 pm

a. Master database has a table of periods (i.e. 201710)
b. There are sales databases for each month (i.e. sales_201710, sales_201709....etc)
c. Child database list is created using a view giving fully qualified name of the database.dbo.table.

How could we link them using a select list of periods (201710,201709, etc) in the master database with the sales database tables.

Main database : companyDB
child databases and a table : companyDB_201710.dbo.sales_201710.

there will be a sales table every month. This needs to be dynamically linked using the master database sales table which contains the summary.

I have tried to build the sqlwhere part giving variable period in child database list but did not work.

List page class:

private string _SqlFrom = "";
public string SqlFrom {
get { return ew_NotEmpty(_SqlFrom) ? _SqlFrom : "[CompanyDB_"+ Period.SessionValue + "].[dbo].[Sales_"+ Period.SessionValue + "]"; }
set { _SqlFrom = value; }
}

How do we achieve this?

Kind Regards
Zam
aspmaker_fan
 
Posts: 12
Joined: Sat Apr 05, 2014 9:46 pm

Re: Dynamic List from multiple databases

Postby Webmaster » Mon Oct 09, 2017 9:46 am

Assuming the databases are of the same database type, if you want to create a master/detail relationship between a table in the main database and another table in the secondary database:
- Create a new project by loading the main database
- Add a link table to the project by loading the secondary database and select the secondary table
- Set up master/detail relationship between the two tables

Alternatively just set up a link table/view in the main database to link to the secondary database so that you can just use it as a table/view in the main database.
Webmaster
 
Posts: 10733
Joined: Fri Aug 06, 2004 5:59 pm

Re: Dynamic List from multiple databases

Postby aspmaker_fan » Mon Oct 09, 2017 5:00 pm

Thanks. is this the best way to do it? Do I have to link the child database every month for future months?

Kind regards
Zam
aspmaker_fan
 
Posts: 12
Joined: Sat Apr 05, 2014 9:46 pm

Re: Dynamic List from multiple databases

Postby Webmaster » Mon Oct 09, 2017 6:07 pm

A more simple approach is just to add the period field in the Sales Table and set up the master/detail relationship directly:

1. Periods Table
Fields:
- Period (e.g. 201710)
- etc...
2. Sales Table
- Period
- sales data
- etc..

To show the data for the current period, set up an extended filter on the Period field and assign an initial value (e.g. current period) to it, so that it will only show the data for the selected period.

If there is too much data in the Sales Table, you may consider to archive the data from time to time.
Webmaster
 
Posts: 10733
Joined: Fri Aug 06, 2004 5:59 pm


Return to General Discussion (ASP.NET Maker)