Monthly Archives: February 2016

How to Programmatically Retrieve The Physical Path from an IIS Site

While looking for a way to automate some IIS tasks, I discovered this, Microsoft Produced, NuGet package. It appears to give almost full control over IIS to your code. I’ll try and post more about its features and limitations in further posts.

The Microsoft NuGet package Microsoft.Web.Administration:

NugetWebAdmin

The following code will trawl all the sites hosted on the local machine, and print some key information about each application within, including the physical path.

            ServerManager sm = new ServerManager();
            foreach (var s in sm.Sites)
            {
                Console.WriteLine($"Name: {s.Name}, state: {s.State}");
                
                foreach(var app in s.Applications)
                {
                    Console.WriteLine($"\t{app.ToString()}, path: {app.Path}");

                    foreach(var vd in app.VirtualDirectories)
                    {
                        Console.WriteLine($"\t\tPhysical path: {vd.PhysicalPath}");
                    }
                }
            }

Output:

WebAdminOutput

The “GenerateResource” task failed unexpectedly

I recently started getting this error on a solution:

GenerateResource

It didn’t actually report any errors, other than this and, no matter what I tried (deleting the suo file, rebuilding, cleaning, etc.), it wouldn’t go away.

Finally, I ran up a command prompt and tried to compile it there. I immediately got this error:

MyViewModel.cs(63,28): error CS1056: Unexpected character ‘$’ [C:\Users\Paul\Desktop\MyApp\MyApp\kqlonrzo.tmp_proj]

Okay, so, the project definitely did use the new $ character. Here’s the thing, whilst I was trying to compile using VS2015, the version of the command prompt that I had run was 2012. The next thing was to try running the command prompt for VS2015.

This gave a less than useful error regarding `MyProject.csproj.metadata` file. So, next I tried compiling the project directly from the command line.

Finally, that worked. Unfortunately, I was unable to break the compile again. However, it isn’t the first time that I’ve encountered this error: hence this post.

So, whilst not proven, for next time, try compiling the solution using the command line and, if that doesn’t work, try compiling the individual project file first.

List the Installed Instances of SQL Server

The following code will list all the SQL Server instances on the local machine.

            RegistryView rv = Environment.Is64BitOperatingSystem ? RegistryView.Registry64 : RegistryView.Registry32;

            using (RegistryKey hklm = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, rv))
            {
                RegistryKey rk = hklm.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL", false);
                if (rk == null) return;

                foreach(var v in rk.GetValueNames())
                {
                    Console.WriteLine($"{v} : {rk.GetValue(v)}");
                }
            }

            Console.ReadLine();

… assuming the registry is correct

Get the executable location of SQLPackage

In my recent escapades into the murky world of deploying dacpac files into a SQL DB, it occurred to me that the location of the SQLPackage file has largely been a cut-and-paste from the web. Apparently, the registry key that holds this information is: HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Data-Tier Application Framework\InstallLocation. Here’s some code to retrieve it using C#:

        private static void GetSQLDacTools()
        {
            using (RegistryKey hklm = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, _registryView))
            {
                RegistryKey rk = hklm.OpenSubKey(
                    @"SOFTWARE\Microsoft\Microsoft SQL Server\Data-Tier Application Framework\InstallLocation");
                if (rk == null)
                {
                    Console.WriteLine("Sql Data Tools Not Installed");
                    return;
                }

                string path = rk.GetValue(string.Empty).ToString();

                Console.WriteLine(path);
            }            
        }

Insert a Stream into SQL

I have written a couple of articles around this; relating to transmitting large files over WCF and enabling filestream in SQL. This article deals with actually inserting one of those large files into the DB and retrieving it back out again.

The following method does not use FILESTREAM; that requires a slightly different syntax.

The Database

If you have a look at the linked articles, you’ll already have seen how the data that I’m dealing with is arranged; however, here’s a create statement for the table; just in case you want to try this:

CREATE TABLE [dbo].[BinaryDataTest](
	[ROWGUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[DataName] [nchar](10) NOT NULL,
	[Data] [varbinary](max) FILESTREAM  NULL,
	[Data2] [varbinary](max) NULL,
UNIQUE NONCLUSTERED 
(
	[ROWGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] FILESTREAM_ON [fs_fg_filestream]

For completeness, my DB is called TestDB.

You’ll notice that `Data` uses FILESTREAM. However, I won’t cover that in this post.

The Service

Here’s an example of how you would write the insert statement in your service (the same method should work whether or not a service is used):

        public void InsertData(Stream value)
        {            
            string connectionString = ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString;
            using (SqlConnection cn = new SqlConnection(connectionString))
            using (SqlCommand cmd = cn.CreateCommand())
            {
                cn.Open();                

                cmd.CommandText = $"INSERT INTO [dbo].[BinaryDataTest] (" +
                    "[ROWGUID],[DataName],[Data2] ) " +
                    "VALUES (NEWID(), 'test', @DataVarBinary)";
                cmd.CommandType = System.Data.CommandType.Text;

                MemoryStream newStream = new MemoryStream();
                value.CopyTo(newStream);
                SqlParameter sqlParameterBin = new
                    SqlParameter("@DataVarBinary", SqlDbType.VarBinary);
                sqlParameterBin.Value = new SqlBytes(newStream);
                cmd.Parameters.Add(sqlParameterBin);

                cmd.ExecuteNonQuery();
            }
        }

As you can see, I have a connection string called “TestDB”; other than that, I think the only remarkable thing (that is: thing worthy of remark – not astounding) is the SqlParameter set-up. Use the VarBinary SQL type, and the ADO.NET SQL function SqlBytes(), and you’re good to go.

Next, there’s the data retrieval:


        public Stream GetData(string dataName)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString;
            using (SqlConnection cn = new SqlConnection(connectionString))
            using (SqlCommand cmd = cn.CreateCommand())
            {
                cn.Open();
                cmd.CommandText = "SELECT [ROWGUID],[DataName],[Data],[Data2]" +
                    " FROM [dbo].[BinaryDataTest]" +
                    " WHERE DataName = @DataName";
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("DataName", dataName);
                using (var rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        Stream str = rdr.GetStream(rdr.GetOrdinal("Data2"));

                        return str;
                    }
                }
            }

            throw new Exception("Invalid data");
        }

A familiar looking idea. You’ll see that I’m only returning that `Data2` as stated earlier, and am using the SqlDataReader.GetStream() function.

The Client

I’m deliberately missing out the configuration that enables you to send these files, and which is documented here.

Here’s the Main() function of a client console app:


        static void Main(string[] args)
        {
            ServiceReference1.Service1Client svc = new ServiceReference1.Service1Client();
            Stream stream = File.OpenRead(@"c:\tmp\test.bmp");

            svc.InsertData(stream);

            Stream strDest = File.OpenWrite(@"c:\tmp2\testdestination.bmp");
            Stream str2 = svc.GetData("test");                        
            str2.CopyTo(strDest);

        }

Summary

So, we’re reading a file from c:\tmp into a stream, and sending that, via WCF into the SQL DB. Then, we’re reading that back out of the SQL DB, and sending it back over to the client. The client then writes this out to a file.

I fully intend to cover how this differs in a FILESTREAM column in a later post.

Setting up SQL Server to use the FILESTREAM feature

Whilst playing about with this feature of SQL Server, I encountered the above error. This post should lead you around the error. It does not make any claims as to whether using the FILESTREAM feature is a good, or bad idea.

The error:

Msg 1969, Level 16, State 1, Line 14
Default FILESTREAM filegroup is not available in database ‘TestDB’.

The table create statement that caused this was:

CREATE TABLE [dbo].[BinaryDataTest2](
	[DataName] [nchar](10) NOT NULL,
	[Data] [varbinary](max) FILESTREAM NULL,
	[Data2] [nchar](10) NULL
) ON [PRIMARY] 

I have to be honest, and say that I did, initially try to create this through the UI designer. this thread put me straight on that.

So, the next stage was to create a filegroup with the FILESTREAM enabled, but if you do that now, you’ll (likely) get the following error:

Msg 5591, Level 16, State 3, Line 1
FILESTREAM feature is disabled.

This is a property of the SQL Server instance, not the DB:

filestream1

https://msdn.microsoft.com/en-us/library/cc645923.aspx

Next, run SQL Configuration Manager and enable FILESTREAM here as well.

filestream2

(found here on MSDN)

Finally, add a file group with FILESTREAM enabled:

ALTER DATABASE TestDB
ADD FILEGROUP fs_fg_filestream CONTAINS FILESTREAM  
GO  
ALTER DATABASE TestDB
ADD FILE  
(  
    NAME= 'filestream',  
    FILENAME = 'C:\db\fs' 
)  
TO FILEGROUP fs_fg_filestream  
GO  

Obviously, replace “C:\db\fs” with an actual location on your hard-drive.

The next error I got was:

A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column.

Okay, so you need to assign a field at a unique identifier:

CREATE TABLE [dbo].[BinaryDataTest](
	[ROWGUID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
	[DataName] [nchar](10) NOT NULL,
	[Data] [varbinary](max) FILESTREAM NULL,
	[Data2] [nchar](10) NULL
) ON [PRIMARY]

Finally, insert some data into your table:

INSERT INTO BinaryDataTest(ROWGUID, DataName, Data, Data2) 
Values (NEWID()
      , 'test'
      , Convert(varbinary,'test')
	  , null
      );

If you have a look at your (equivalent of) “c:\db”, you’ll see exactly what the effect of this was:

filestream3

Basically, you are now storing the data from the DB in the file system. I re-iterate, I make no claims that this is a good or bad thing, just that it is a thing.