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.
Post Reply
aspmaker_fan
User
Posts: 46

Dynamic List from multiple databases

Post by aspmaker_fan »

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


Webmaster
User
Posts: 9425

Post by Webmaster »

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.


aspmaker_fan
User
Posts: 46

Post by aspmaker_fan »

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


Webmaster
User
Posts: 9425

Post by Webmaster »

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:
  2. Period (e.g. 201710)
  3. etc...
  4. Sales Table
  5. Period
  6. sales data
  7. 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.


Post Reply