0

I would like to use Microsoft.Web.Administration.dll from within the SQL Server CLR in order to get the physical path of a specific web application at runtime, but the DLL is not available or tested in SQL Server, and adding it would require adding a chain of dependent DLLs (also untested), so basically NEVERMIND all of the above.

On to the issue, but first a short background. The architecture of my database application is pretty fancy... a "Database.Values" namespace (in database.dll) contains a host of C# classes, each of which subclass "RegexConstrainedString" or "ConstrainedNumber" (both custom classes) which represent regular expression and numeric value constraints for various fields in the database. Each class is marked with attributes specifying various table+fieldname combinations indicating which table and field names each rule applies to in the database.

A deployment application takes that database.dll, uses reflection to find classes with the aforementioned attributes and dynamically generates (in IL op codes) a function for each class (classname appended with "Check"), whose purpose is simply to attempt to instantiate a given string or number with the associated class and returns true or false depending on whether the instantiation was successful. Instantiation fails when the string or number doesn't match the class's defined constraints. The deployment app then uses ILMerge.exe to merge its dynamically generated functions with the original database.dll to produce a new database.dll which is deployed to SQL Server. It then opens a GUI that allows for one-click creation, enabling, and checking of all the associated SQL check constraint functions, which call the underlying CLR class-based "*Check" functions that were generated.

Anyway... one of those classes in particular called "Text" enforces ASCII encoding on a field by mapping Unicode strings to ASCII strings, and for completeness, it will map all 65536 possible two-byte (16-bit) UCS2 code point values to a specific ASCII character using a character map that is created and edited by a web application. Sure, I could have just mapped everything outside the range (32-126) to a question mark character, but I wanted greater control to the point where I mapped out every possible Unicode character to an ASCII character that it closely resembles, so it's probably one of the most complete (although subjective) visual maps in existence.

The problem is, the static constructor of the Text class that resides in database.dll in the SQL CLR needs to load that ASCII map file, which resides in the root of a particular web application. How am I supposed to get that path without hard coding it and without the ability to use the Microsoft.Web.Administration.dll's call to (new ServerManager()).Sites["Default Web Site"].Applications["/utilities"].VirtualDirectories["/"].PhysicalPath?

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
Triynko
  • 18,766
  • 21
  • 107
  • 173
  • Thinking laterally. Why not store the path in the DB during deployment? – Preet Sangha Aug 01 '13 at 00:39
  • I think long-term. Suppose in 5 years after upgrading to Windows Server 9, the virtual path changes, and when that database is attached and started, it needs to be able to find the "asciimap.txt" file simply by the name of "utilities" web application, which could have been moved anywhere. I need to be able to move the application around to different directories, not necessarily at the root, not necessarily the same physical paths, without having to rebuild and redeploy the database DLL. "Storing it in the DB" would be equivalent to hard-coding the path, since the DLL is embedded in the DB. – Triynko Aug 01 '13 at 00:41
  • I hope that people don't get too hung up on this specific problem, I only mentioned it for context. The actual question is how to acquire the physical path of a web application at runtime from within the SQL CLR, without using the ServerManager class, which is something that could be necessary for a number of reasons... for example, in order for SQL CLR to automatically persist some kinds of data to the filesystem. – Triynko Aug 01 '13 at 00:46
  • @Triynko If you have not seen it, I posted an answer, though not sure if this is still an issue given that it has been 2 years since you posted this. – Solomon Rutzky Aug 22 '15 at 20:14

2 Answers2

0

My initial thinking is that you make a call to WMI to find out the IIS information:

This question/Answer How to get IIS application info via WMI shows how you can do this.

You can then integrate this with the SQL Server CLR using this technique and http://msdn.microsoft.com/en-us/magazine/cc302051.aspx.

I don't have any concrete examples of your particular use case but the above links should give you enough of a start.

Community
  • 1
  • 1
Preet Sangha
  • 64,563
  • 18
  • 145
  • 216
  • This won't work. At the end of the "this technique" link, it suggests loading System.Management.dll, but that has the same problem I encountered before in that it requires a large chain of other untested dlls to be added to SQL server. I'm also not going to add dependencies to arbitrary command strings in windows powershell as suggested in the first link. – Triynko Aug 01 '13 at 17:59
  • There may be a way to get WMI information from within the SQLCLR, such as this: http://micatio.com/sqlwmi.aspx, but even that only goes to SQL Server 2008 R2, not 2012 which requires .NET 4.0. – Triynko Aug 01 '13 at 18:08
  • @Triynko that SQLWMI library from Micatio most likely was just using `System.Management.dll` so it wouldn't save you from any of that pain. And I suspect that they supply the `System.Management` assembly to load into SQL Server via the installer, hence why they have 32-bit and 64-bit options, but it might not be the right version of `System.Management`. Regardless, you don't need to mess with WMI to get this info, as I show in my [answer](http://stackoverflow.com/a/32153085/577765). – Solomon Rutzky Aug 23 '15 at 14:43
0

This info should be fairly easy to get by simply bypassing the ServerManager class and just reading the same config file that it is reading. The system I checked was running IIS 7.0 and the config file was in the following location:

C:\Windows\System32\inetsrv\config\applicationHost.config

Just load that XML and then use an XQuery expression along the lines of the following to get that physical path:

(/configuration/system.applicationHost/sites/site[@name="Default Web Site"]/application[@path="/utilities"]/virtualDirectory[@path="/"]/@physicalPath)[1]

Regarding the following statement from the Question (not about the specific request, but related to the background for the request):

one of those classes in particular called "Text" enforces ASCII encoding on a field by mapping Unicode strings to ASCII strings, and for completeness, it will map all 65536 possible two-byte (16-bit) UCS2 code point values

Actually, there are not 65,536 possible UCS-2 characters. There are at most 63,477 characters in UCS-2. That number comes from the 65,536 possible values when using 2 bytes, then removing:

  • the 2048 values reserved to create surrogate pairs (where the supplemental characters come from)
  • the final 2 values (0xFFFE used for Byte Order Mark, 0xFFFF not a character)
  • the 9 values from 0xFFF0 to 0xFFF8 (unassigned)
  • see the PDF chart of 0xFFF0 - 0xFFFF for details

However, the NCHAR, NVARCHAR, XML, and NTEXT (but don't use this one anymore!) datatypes can actually hold the full UTF-16 encoding. It's just that the built-in functions cannot interpret the non-UCS-2 characters correctly when using a collation whose name does not end in _SC (those were introduced in SQL Server 2012). So the true full range of characters that can be in the XML and N-prefixed types is actually 1,112,064 code points (see UTF-16 on Wikipedia for details).

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171