ModuleUpdater - How to avoid a NullReferenceException in the CreateCommand method with pooled database connections or a middle-tier application server

Thanks Michael,

How can I also access the database name with this change? My previous method through ((XPObjectSpace)ObjectSpace).Session.Connection is null. As is connection string.

I wonder why you need the database name in the updater.

Since you can execute any SQL, the solution described in "How can you get the current database name via T-SQL?" should work for you.

Ah, thanks! Dang, so you literally just have no concept of these connections at this time. Crazy.

Just to ease your curiosity, during the update, I have to check/create FileStream file groups in the SQL database. I do this BeforeUpdateSchema. :D That's why I need the DB name…

private void UpdateFileStream()
        {
            UpdateStatus("DBUpdate", System.Reflection.MethodInfo.GetCurrentMethod().Name, "Checking FILESTREAM…");
            string databaseName = (string)ExecuteScalarCommand("SELECT db_name()", false);

string sql = "select COUNT(*) from sys.data_spaces where [type] = 'FD' AND [name] like '%attachments'";
            int fgcount = (int)ExecuteScalarCommand(sql, true);
            sql = "select COUNT(*) FROM sys.database_files where [type] = 2 AND [name] like '%attachments'";
            int fcount = (int)ExecuteScalarCommand(sql, true);
            if (fgcount == 0)
            {
                sql = string.Format("ALTER database [{0}] ADD FILEGROUP [TattooStudio.ExpressApp.Attachments] CONTAINS FileStream", databaseName);
                ExecuteNonQueryCommand(sql, true);
            }
            if (fcount == 0)
            {
                sql = "SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1) FROM master.sys.master_files WHERE database_id = 1 AND FILE_ID = 1";
                string path = ExecuteScalarCommand(sql, true).ToString();
                sql = string.Format("ALTER DATABASE [{0}] ADD FILE ( NAME = '{0}.Attachments', FileName = '" + path + "{0}.Attachments' ) TO FILEGROUP [TattooStudio.ExpressApp.Attachments]", databaseName);
                ExecuteNonQueryCommand(sql, true);
            }
        }

One more question around this change.

Is there any reason why obfuscation (via dotfuscator) wouldn't play nice with this?

Thank you very much for the explanation.

As for obfuscating the XAF/XPO code, it won't work unless you exclude at least persistent class and member names from the process, because they are used to generate database mappings and captions in the UI.

Hmm… well I always obfuscate my code and it's always been fine. I don't do string encryption on that module… however with this method the objectSpace is null on the logon screen when throwing an error. Works fine without obfuscation. Also works fine if I change it back to

args.ObjectSpaceProvider = new XPObjectSpaceProvider(args.ConnectionString, args.Connection, true);

Something about this new method its not liking. I'll investigate more.

Thanks for your reply. I'm afraid we cannot determine what went wrong based on your description. Should you need further assistance with this issue, please submit a new ticket and provide a sample project demonstrating the issue.

Just a follow up, I feel like since this is the default, you should change the implementation of ModuleUpdater around to better suit this. This renders ExecuteNonQuery, ExecuteScalar, DropColumn, DropIndex, etc… useless and all must be overridden.

Thanks for your suggestion. We already have this task on our wish list. Although we cannot simply change existing methods to use corresponding Session methods without introducing the dependency on XPO, we will research alternative approaches to support other scenarios besides the direct database connection.

public class XPModuleUpdater: ModuleUpdater { } :D

I would like to +1 for an updated implementation.  We're using ModuleUpdater methods quite a bit and switching to the default broke a bunch of code.

I ran into another issue with this new ObjectSpace provider under 17.1.3.17146

using explicit transactions (in a unit of work) causes XPO object selects (using GetObjectByKey, for instance) to hang intermittently when trying to retrieve referenced objects.  Very difficult to re-produce, however, when I went back to the previous objectspace I had this problem went away.

@Randy: I've created a separate ticket on your behalf (T525575: Application hangs intermittently when explicit transactions are used). It has been placed in our processing queue and will be answered shortly.

How would I re-create ExecuteReader with this? It relies on CreateCommand and there is no session level ExecuteReader.

Please use the Session.ExecuteQuery method to get data from a query. It uses IDbCommand.ExecuteReader internally.

Casting to an XPObjectSpace and using its session to execute SQL commands is all fine and dandy, but can you recommend a means to determine the underlying provider type in this scenario?  I believe with pooled connections in a web environment it is resolved as a DroneDataStore.  Given we support multiple database back ends (via XAF), we need to know which set of database engine specific SQL commands to execute for items the framework doesn't handle.  An example would be in the case of column length updates for which the syntax is different between MSSQL and MySQL.

@Will: The public XAF/XPO API doesn't provide a reliable way to access the connection provider from Session or determine the provider type. If you need this information, store it explicitly in a place where you can always access it. For example, in the application configuration. In fact, you can read the connection string defined in the configuration file, parse it, and get the provider type from the XpoProvider key.

Problem is the DbUpdater executable doesn't have direct access to the application configuration file of the target application.  It tries to read its own app config which doesn't have the connection strings used by the application.  I understand that I could add additional logic to go look for it or I believe I even saw a posting where a connection string could be accepted by DbUpdater.  I've already addressed this on my own  I was just hoping something built-in existed for DbUpdater purposes.  It would certainly be a nice helper function to have built-in exposing a data provider enum that worked across the board so consider this a feature request.  Thanks!

Thanks for your suggestion. We will take it into account.
The main application configuration is accessible via ConfigurationManager, regardless of the project in which the code is declared or executed. For example, you  can access the connection string using the following expression from any method:

C#
ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString  

If this doesn't it work in your project or you need additional assistance, please submit a separate ticket and describe your situation in greater detail.

I'm sorry but I disagree.  You cannot access the XAF application's connection string within Updater.cs via this method while running DbUpdater.exe.  DbUpdater is its own process therefore has access to connection strings defined within its own application scope (ie. dbupdater.exe.config).  Your recommendation does not work to access the application's connection string (web.config or appName.exe.config) while DbUpdater.exe is running as it is not the main application.