Issue Once Setup as SaaS

And just realized I put this in the wrong category, I don't see a way to change that.

I have resolved this issue, and while this was challenging, I want to document how. I have seen this question ask several times with no answer. This was a lot of guesswork on my part.

Initially, I had the app set up stand-alone, using one database. When I updated a record, I had a process to update another table in the “After record updated” event.

dynamic data = XVar.Array();
data["tskid"] = values["Id"];
data["datetime"] = values["CreatedAt"];
data["timeinseconds"] = values["TrackedTimeInMinutes"];
data["user"] = values["UpdatedBy"];
DB.Insert ("public.tasktimetracking",data);

Worked extremely well.

I then decided to set the app up for SaaS.

So, starting from scratch, I followed the instructions here.

https://xlinesoft.com/blog/2019/10/03/saas-application-design/

This would work well, and in testing, once the user logged in, they were directed to the correct database. On the front end, they could add, edit, and delete a record without issues in the correctly assigned database.

I called the database used for authentication saasdb ; as in the instructions. You can name it whatever you want.

The database for the data I called master. I cloned the master to multiple databases that would represent each client.

In the Before Connect, I have this. (FYI I am using PostgreSQL)
GlobalVars.ConnectionStrings["master_at_localhost"] =
String.Format("Server=localhost;Database={0};User Id=xxxxxxx;Password=xxxxxxx", XSession.Session["database"]);

After Login, I have this:
XSession.Session["database"]=data["database"];

In the user table, I added a column called Database and set the user to what database they should have access to. This way, when they log in, they are connected to the correct DB.

As I said, this all worked well; however, the “After record updated” broke. You would think that since the app on the front end knows which database the users are working with, the event would also. I was trying to write to another table in the same database after the update, and it could not find it. Fighting with this for about a week, I created the same table in the saasdb database (used for authentication). When the event was fired, it was written to that table, defaulting to the authentication database instead of the assigned one.

I then found in the documentation the DB.SetConnection command, while I was still just trying to write to the associated database the user was assigned to , I presume the event had no clue which DB at that point. So, I put the session variable for the assigned DB in the “DB.SetConnection”, and was able to prove it was setting it correctly; in this case, the user was assigned the test database. However, it still could not find the table in the database and wanted to write to the saasdb.

After several days of throwing up my hands, I reread all the instructions. And decided to hardcode the DB.SetConnection to “master” Which did not work, at first. Going to the tables in ASPRunner, I noticed that when I added the tables (saasdb and master, it appended “at local” to the end of them.

saasdb at local
master at local

I renamed them back to saasdb and master, compiled the app, and logged in with the test user to the test database, and made sure existing functionality was still working. I updated the record, and the “After record updated” event was fired and wrote to the table in the test database.

So there were two issues.

The database listed in the tables section of ASPRunner were incorrectly named

And the DB.SetConnection must be the Database you are using as the master in ASPRunner. Then it magically changes it to the one assigned.

Like I said, be it right or wrong, this is working seamlessly for me now. I do feel the events should look at the session variable for the assigned database, and only if you write to another database should you have to set it using DB.SetConnection.