Friday, July 29, 2011

C# - Finding SQL Server Data Sources and Databases

For those exploring how to finding data sources, database names, etc - using C#, you can do the following:
// Retrieve the enumerator instance, and then retrieve the data sources.
SqlDataSourceEnumerator instance = SqlDataSourceEnumerator.Instance;
DataTable dtDatabaseSources = instance.GetDataSources();
  
// Populate the data sources into DropDownList.            
foreach (DataRow row in dtDatabaseSources.Rows)
    if (!string.IsNullOrWhiteSpace(row["InstanceName"].ToString()))
        cboDatasources.Items.Add(row["ServerName"].ToString() 
            + "\\" + row["InstanceName"].ToString());
The code above however, did not show the local instances of SQL Server 2008 R2. So, to acquire your local instances as well, you will need to use the SMO ManagedComputer object, which provides an interface to the WMI Provider for Configuration Management.

You will need to add the following references to the project (using VS2010), in C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\: Microsoft.SqlServer.Management.Sdk.Sfc, Microsoft.SqlServer.Smo, and Microsoft.SqlServer.SqlWmimanagement.

In your project, you will need to add the following using statements:
using System.Data.Sql;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo.Wmi;
The ManagedComputer object has many functions. It can access client protocols, connection settings, server aliases, instances of SQL Server, as well as SQL Server services. It is versatile enough to pick up different versions of SQL Server, including 2008 and 2008 R2.
// Determine local system name
string hostName = System.Net.Dns.GetHostName().ToString();
ManagedComputer mc = new ManagedComputer(hostName);
  
// Check to see if 64-bit architecture and add to list
mc.ConnectionSettings.ProviderArchitecture = ProviderArchitecture.Use64bit;
foreach (ServerInstance si in mc.ServerInstances)
    cboDatasources.Items.Add(hostName + "\\" + si.Name);
  
// if not on list, and 32-bit add to list
mc.ConnectionSettings.ProviderArchitecture = ProviderArchitecture.Use32bit;
foreach (ServerInstance si in mc.ServerInstances)
    if (!cboDatasources.Items.Contains(hostName + "\\" + si.Name))
        cboDatasources.Items.Add(hostName + "\\" + si.Name);
In the code above, once a data source is selected in the drop down lists, the SelectedIndexChanged event would execute the following code:
// Connect to Data Source selected in DropDownList
using (SqlConnection sqlConn = new SqlConnection("Data Source = " +
    cboDatasources.SelectedText.ToString() + "; Integrated Security = True;"))
{
    // Open Connection
    sqlConn.Open();
    // Retrieve Database Schema
    DataTable dtDatabaseNames = sqlConn.GetSchema("Databases");
    // Close Connection
    sqlConn.Close();
  
    // Retrieve List of Database Names
    foreach (DataRow row in dtDatabaseNames.Rows)
        cboDatabaseNames.Items.Add(row["Database_Name"].ToString());
}

IIS - Error - PageHandlerFactory-Integrated

If you've ever installed .NET Framework 4.0 (version 4.0.30319) and enabling the Internet Information Services (IIS) Manager, and encounter the following error when trying to visit the web page you are trying to deploy:

Handler "PageHandlerFactory-Integrated" has a bad module "ManagedPipelineHandler" in its module list.

This error is caused due to a glitch in the installation of .NET Framework 4.0. To fix, simply open the run dialog on your system and run the following commands.

On 32-bit System:

%windir%\Microsoft.NET\Framework\v4.0.30319\SetupCache\Client\setup.exe /repair /x86 /x64 /ia64 /parameterfolder Client /q /norestart

On 64-bit System:

%windir%\Microsoft.NET\Framework64\v4.0.30319\SetupCache\Client\setup.exe /repair /x86 /x64 /ia64 /parameterfolder Client /q /norestart

The .NET Framework 4.0 installation will be repaired, and after a few minutes make another attempt to browser your page. The repair will happen silently, in the background, but should not present any conflicting issues.

Book Recommendation - Working Effectively with Legacy Code

On my Google+ account, I recently saw news on my feed from a friend of mine Robert Dailey, seeking a good book on implementing automated testing in existing, complex projects not initially designed for it. It was recommended that he check out a book titled, Working Effectively with Legacy Code (ISBN-13:978-0131177055), by Michael Feathers. It has a wealth of knowledge on bridging the gap between legacy (test-less and/or untestable) code and newer, testable code. Definitely, worth the read.

Wednesday, July 27, 2011

SQL - Inserting Images

If there is ever a need to store images into SQL Server, it can be done in a few simple steps; you will need to create the database, the table names and insert your images.
CREATE TABLE ImageResource (                         
             ID INT(5) NOT NULL AUTO_INCREMENT,
             NAME NVARCHAR(255) NULL,          
             IMAGE IMAGE NULL,                                         
             PRIMARY KEY  ('ID')                   
);
Once the table has been created, you simply do the insert via the following:
INSERT INTO dbo.ImageResource (NAME, IMAGE)                         
             SELECT 'LOGO_NAME' AS NAME, *
             FROM OPENROWSET (BULK N'C:\FILENAME.PNG', SINGLE_BLOB) AS IMAGE
If the image needs to be updated, use the following:
UPDATE dbo.ImageResource SET IMAGE = (
             SELECT * FROM OPENROWSET(BULK N'C:\FILENAME.PNG', SINGLE_BLOB) AS IMAGE 
             ) WHERE NAME = 'LOGO_NAME'

SQL Server Business Intelligence - Lost Report Data Window

In an unlikely circumstance that you happen to lose the Report Data Window, in SQL Server Business Intelligence, you won't simply find it in your VIEW menu options. There is a simple keyboard shortcut to re-create this window, which is CTRL+ALT+D.