Brian On Software

If you’re looking for a solution to store and retrieve a picture from a table in a SQL Server 2005 database, I hope that these two functions (StoreImageInDatabase at line 4 and RetrieveImageFromDatabase at line 72) will satisfy your requirement. I had to implement such functionality in a project and decided to keep it here for further reference. It’s heavily commented for those developers that are taking their first steps with ADO.NET. Feel free to share your own implementation too. There are so many ways to do this.

/// 

/// Stores an image to the database
/// 
private static void StoreImageInDatabase( string imageFilename, ImageFormat imageFormat )
{
    // Make sure the image file exists prior to proceeding with the storage.
    // Note: The File.Exists method will return false whether the imageFilename parameter is null or empty.
    if ( !File.Exists( imageFilename ) ) throw new FileNotFoundException( “The image file was not found”, imageFilename );

    // Load the image from the filename received as a parameter.
    Image image = Image.FromFile( imageFilename );

    // Holds the byte representation of the image.
    Byte[] content;

    // Use a MemoryStream to store the image representation as stream of bytes.
    using ( var stream = new MemoryStream() )
    {
        image.Save( stream, imageFormat );

        // Convert the stream into an array of bytes because that’s the value we’ll pass to our SQL parameter.
        content = stream.ToArray();
    }

    // Our connection string (put it in your configuration file if needed)
    const string connectionString = “INSERT YOUR DATABASE’S CONNECTION STRING HERE”;

    // Create an SQL connection based on a connection string pre-defined (the using statement will close/dispose the connection on our behalf even if an exception should occur).
    using ( var connection = new SqlConnection( connectionString ) )
    {
        // The SQL statement/stored procedure used to store our image.
        // Just make sure that you have a parameter named ‘@PICTURE’ of type Image as well as a column named ‘PICTURE’ of type Image too in the table.
        const string sqlStatement = “UPDATE SOME_TABLE SET PICTURE = @PICTURE WHERE SOME_CONDITION”;

        // Create an SQL command based on our stored procedure and SQL connection.
        using ( var command = new SqlCommand( sqlStatement, connection ) )
        {
            // Explicitly state that we’ll be using a SQL statement or a stored procedure (CommandType.StoredProcedure).
            command.CommandType = CommandType.Text;

            // Define the SQL parameter which will hold our image to store.
            var parameter = new SqlParameter( “@PICTURE”, SqlDbType.Image )
                            {
                                    Value = content
                            };
            command.Parameters.Add( parameter );

            try
            {
                // Open the connection.
                connection.Open();

                // Execute the stored procedure to store our image in the database.
                command.ExecuteNonQuery();

                MessageBox.Show( “Image stored successfully”, “Image storage”, MessageBoxButtons.OK,
                                 MessageBoxIcon.Information );
            }

            catch ( SqlException ex )
            {
                MessageBox.Show( ex.Message, “SQLException”, MessageBoxButtons.OK, MessageBoxIcon.Error );
                // Other error handling, logging, etc.
            }
        }
    }
}

/// 

/// Retrieves an image from the database
/// 
private static Image RetrieveImageFromDatabase()
{
    // The image that will be retrieved
    Image image = null;

    // Our connection string (put it in your configuration file if needed)
    const string connectionString = “INSERT YOUR DATABASE’S CONNECTION STRING HERE”;

    // Create an SQL connection based on a connection string pre-defined (the using statement will close/dispose the connection on our behalf even if an exception should occur).
    using ( var connection = new SqlConnection( connectionString ) )
    {
        // The SQL statement/stored procedure used to retrieve our image.
        // Just make sure that you have a column named ‘PICTURE’ of type Image.
        const string sqlStatement = “SELECT PICTURE FROM SOME_TABLE WHERE SOME_CONDITION”;

        // Create an SQL command based on our stored procedure and SQL connection.
        using ( var command = new SqlCommand( sqlStatement, connection ) )
        {
            try
            {
                // Explicitly state that we’ll be using a SQL statement or a stored procedure (CommandType.StoredProcedure).
                command.CommandType = CommandType.Text;

                // Open the connection.
                connection.Open();

                // Execute the query and build a DataReader with the results.
                var reader = command.ExecuteReader();

                Debug.Assert( reader != null );

                // Since we only have one image stored in the table (this is an example after all…), we’ll only have one image to retrieve from our table.
                // If you have to retrieve one specific image (or more) from a collection of images in the table, then you can precise which one to retrieve via a parameter.
                reader.Read();

                // Since the only column of our table is used to store/retrieve an image, its column index is zero.
                // We simply cast that column into an array of bytes.
                var content = (byte[])reader[0];

                // Create a stream based on the entire content of our buffer.
                using ( Stream stream = new MemoryStream( content, 0, content.Length ) )
                {
                    // Write the content (array of bytes) to our stream.
                    stream.Write( content, 0, content.Length );

                    // Create an image object based on the stream.
                    image = Image.FromStream( stream );
                }
            }

            catch ( SqlException ex )
            {
                MessageBox.Show( ex.Message, “SQLException”, MessageBoxButtons.OK, MessageBoxIcon.Error );
                // Other error handling, logging, etc.
            }
        }
    }

    return image;
}

Microsoft released tons of good stuff (the kind your parents will let you have) today.

First of all, you can now download Microsoft .NET Framework 3.5 Service Pack 1.  According to Microsoft,

Microsoft .NET Framework 3.5 Service Pack 1 is a full cumulative update that contains many new features building incrementally upon .NET Framework 2.0, 3.0, 3.5, and includes cumulative servicing updates to the .NET Framework 2.0 and .NET Framework 3.0 subcomponents.

Also as stated by Microsoft,

.NET Framework version 3.5 Service Pack 1 provides the following new features and improvements:

• ASP.NET Dynamic Data, which provides a rich scaffolding framework that enables rapid data driven development without writing code, and a new addition to ASP.NET AJAX that provides support for managing browser history (back button support). For more information, see What’s New in ASP.NET and Web Development.
• Core improvements to the CLR (common language runtime) that include better layout of .NET Framework native images, opting out of strong-name verification for fully trusted assemblies, improved application startup performance, better generated code that improves end-to-end application execution time, and opting managed code to run in ASLR (Address Space Layout Randomization) mode if supported by the operating system. Additionally, managed applications that are opened from network shares have the same behavior as native applications by running with full trust.
• Performance improvements to WPF (Windows Presentation Foundation), including a faster startup time and improved performance for Bitmap effects. Additional functionality for WPF includes better support for line of business applications, native splash screen support, DirectX pixel shader support, and the new WebBrowser control.
• ClickOnce application publishers can decide to opt out of signing and hashing as appropriate for their scenarios, developers can programmatically install ClickOnce applications that display a customized branding, and ClickOnce error dialog boxes support links to application-specific support sites on the Web.
• The Entity Framework is an evolution of the existing suite of ADO.NET data access technologies. The Entity Framework enables developers to program against relational databases in according to application-specific domain models instead of the underlying database models. For more information, see Getting Started with the Entity Framework. The Entity Framework introduces some additional features, including support for new SQL Server 2008 types, default graph serialization of Entities, and the Entity Data Source. This release of the Entity Framework supports the new date and file stream capabilities in SQL Server 2008. The graph serialization work helps developers who want to build Windows Communication Foundation (WCF) services that model full graphs as data contracts. The Entity Data Source provides a traditional data source experience for ASP.NET Web application builders who want to work with the Entity Framework.
• LINQ to SQL includes new support for the new date and file stream capabilities in SQL Server 2008.
• The ADO.NET Data Services Framework consists of a combination of patterns and libraries, which enable data to be exposed as a flexible REST (Representational State Transfer)-based data service that can be consumed by Web clients in a corporate network or across the Internet. The ADO.NET Data Services Framework makes data service creation over any data source. A conceptual view model of the underlying storage schema can easily be exposed through rich integration with the ADO.NET Entity Framework. Services created by using the ADO.NET Data Services Framework, and also compatible Windows Live (dev.live.com) services, can be easily accessed from any platform. For client applications that are running on Microsoft platforms, a set of client libraries are provided to make interaction with data services simple. For example, .NET Framework-based clients can use LINQ to query data services and a simple .NET Framework object layer to update data in the service.
• Windows Communication Foundation now makes the DataContract Serializer easier to use by providing improved interoperability support, enhancing the debugging experience in partial trust scenarios, and extending syndication protocol support for wider usage in Web 2.0 applications.
• The .NET Framework Data Provider for SQL Server (SqlClient) adds new support for file stream and sparse column capabilities in SQL Server 2008.

Second of all, if you have an MSDN Subscription account, you can also download SQL Server 2008 which is fully supported by Visual Studio 2008 and .NET 3.5 upon installing .NET 3.5 SP1.

Here are some links that might be of interest for you if you want to get up and running with SQL Server 2008:

• What’s new in Microsoft SQL Server 2008 (Database Engine, Analysis Services, Reporting Services, etc.)
• Features supported by each edition of Microsoft SQL Server 2008
• Backward compatibility information for SQL Server components
• Download SQL Server 2008 Essentials (Quick Facts, White Papers, Case Studies, etc.  One zip file of roughly 21MB)
• Microsoft SQL Server 2008 Virtual Labs

Finally, the ADO.NET Team at Microsoft has also released Entity Framework and Data Services.  According to today’s blog post on their site,

The Entity Framework and Data Services raise the level of abstraction for database programming and supply both a new model-based paradigm and a rich, standards-based framework for creating data-oriented Web services. Powered by rich and integrated tooling, the new ADO.NET Entity Designer lets users instantly leverage the new model-based paradigm in their applications. With the ADO.NET Entity Designer, users can generate models & mappings from an existing database and also create models with complex inheritance hierarchies & mappings in an intuitive, easy to use graphical user interface well integrated with Visual Studio 2008 SP1. The new Entity Data Source Control lets users consume models in their web applications with ease.

Check out the Data Platform site to get the latest bits and information on Entity Framework and Data Services.

I had such a hard time installing SQL Server 2005 on my Windows Vista Enterprise Edition machine running IIS 7.0 this afternoon. In fact, during the setup of SQL Server 2005, I received the following warning message next to the IIS Feature Requirement item on the System Configuration Check page, as shown in the following screenshot.

After Googling for a while, I found out that this situation was already documented by Microsoft as Knowledge Base #920201 in their Help and Support page.

The root cause for this problem is that not all of the IIS 7.0 components essential to SQL Server have been installed. The solution to this problem is (as you may have guessed it) to install those IIS components. To access the IIS configuration page on Vista, you need to go to Start->Control Panel->Programs and Features, then click on “Turn Windows features on or off” on the left pane of the window.

The following table shows you which components (there are 10) to enable with their corresponding folder. The screenshot below the table is a visual representation of the IIS components you should have enabled by ticking the checkbox next to the component.

Following this procedure, you can launch the setup for SQL Server 2005 again, and this time you should see the following results:

If you’re looking for more information on how to install/configure IIS 7.0 on Vista, I highly recommend you to read the following page: http://learn.iis.net/page.aspx/28/installing-iis7-on-vista/. If you’re looking for the same information, but for Windows Server 2008, this link will be more appropriate for you: http://learn.iis.net/page.aspx/29/installing-iis7-on-windows-server-2008/.