Before I launch into the decision I’m refering to in this particular case I’ll set the scene: I’m in the process of writing some test code (which admittedly should have been written first, but hey better late than never) and I need to create a temporary database. Instead of writing lots of lines of .NET code to drop any existing database with the same name (as this test is likely to be run thousands of times) and then create the new database, I figure I would let SQL Management Studio generate a script file which I could then use osql.exe to run. At this point I had two options; I could include the script file as a content file in which case I would need to make sure it was distributed with the test assembly; alternatively I could include the script file as an assembly resource. I chose the latter as it seemed easier at the time.
Osql is great for running sql script files and can be easily executed from .NET using something like:
Dim proc As Process = Process.Start(“C:Program FilesMicrosoft SQL Server90ToolsBinnosql.exe”, “-E -i filename.sql”)
NB: If you are planing on running multiple script files (or are relying on the script being completed – eg for the database to be create) you need to ensure you wait for the process to exit!
Now the only difficulty was that my script file was being stored as an embedded resource. This meant I needed to persist it to file. Being a VB.NET developer and knowing a bit about the My namespace I figure the easiest way to do this would be to use:
My.Computer.FileSystem.WriteAllText(filename, My.Resources.DropDatabaseSQL, False)
This works fine, except when I invoke osql I get all sorts of weird errors. I open the created sql file up in SQL Management Studio and run it from there and it works fine, yet even when I manually run osql from the commandline I get these weird errors. Investigating this further I opened the sql file in notepad, copied the contents to a new file and ran that file using osql – which worked fine??? Ok, so there must be something weird with the sql file that was generated which doesn’t show up in either SSMS or notepad. Finally I resort to using the commandline to examine the file and sure enough there is a leading character which is the culprit.
∩╗┐EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N’WebserviceTest’
Going back to my code that writes the sql file out I expand out the code to:
Dim strm as new IO.StreamWriter(filename,False)
Using this code generates the sql file which surprisingly works with osql…..Interesting so there must be a difference in the way that the My…WriteAllText works (I thought that it basically was just a simple wrapper for the expanded code I just wrote). If you have done much work with files you will understand that there is an encoding (eg ASCII, UTF8) which determines how information is written/read from the file. The StreamWriter has multiple overloads, some of which allows you to omit the encoding. Using reflector you can see that this uses a default encoding:
Public Sub New(ByVal path As String, ByVal append As Boolean)
Me.New(path, append, StreamWriter.UTF8NoBOM, 1024)
And if you further drill into the “UTF8NoBOM” you will see that it is UTF8 with out the UTF8 Identifier (the first parameter of the UTF8Encoding constructor is entitled encoderShouldEmitUTF8Identifier as it determines if this identifier should be emitted or not):
Friend Shared ReadOnly Property UTF8NoBOM As Encoding
If (StreamWriter._UTF8NoBOM Is Nothing) Then
Dim encoding1 As New UTF8Encoding(False, True)
StreamWriter._UTF8NoBOM = encoding1
If we do the same with the My…WriteAllText we see that it works slightly differently – this is where the “stupid decision” comes into play:
Public Shared Sub WriteAllText(ByVal file As String, ByVal [text] As String, ByVal append As Boolean)
FileSystem.WriteAllText(file, [text], append, Encoding.UTF8)
So in this case they have chosen to use the standard UTF8 encoding, which of course emits the identifier that was causing me grief. I have got no idea why this decision was made but consistency is something that is paramount in a good application framework and for this type of mistake to be in the My namespace just give me one more reason not to use it. That said, there is a fairly simple work around which is to add the optional encoding parameter:
My.Computer.FileSystem.WriteAllText(filename, My.Resources.DropDatabaseSQL, False,Text.Encoding.ASCII)
In my case the ASCII encoding works fine, but there are other default encodings that might be more appropriate to what you are doing.