May, 2010

 /* Add anadditional column to the Categories table to store the image */

ALTER TABLE dbo.Categories ADD
      CategoryPicture VARBINARY(MAX) NULL
GO

/* update thetable to insert some images */

UPDATE Categories
SET CategoryPicture =
      (SELECT * FROMOPENROWSET(BULK N'C:\Temp\Beverages.jpg', SINGLE_BLOB) AS CategoryImage)
WHERE CategoryID = 1

UPDATE Categories
SET CategoryPicture =
      (SELECT * FROMOPENROWSET(BULK N'C:\Temp\Condiments.jpg', SINGLE_BLOB) AS CategoryImage)
WHERE CategoryID = 2

GO

/* Insert a new category with an image */

INSERT INTO Categories(CategoryName, CategoryPicture)
Values ('Another Category', (SELECT * FROM OPENROWSET(BULK N'C:\Temp\AnotherCategory.jpg', SINGLE_BLOB) AS CategoryImage))

Imports System.Reflection
Imports System.Globalization
Imports System.Threading
Imports System.Linq
Imports System.IO

Class MainWindow

    Private Sub btnOpenFolder_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnOpenFolder.Click

        ' Hook up the AssemblyResolve Event - which occurs when the 
        ' resolution of an assembly fails - to the method AssemblyResolve
        ' This is important if your assemblies reference others which are
        ' located in a different path and can't be found
        ' http://msdn.microsoft.com/en-us/library/system.appdomain.assemblyresolve.aspx
        AddHandler AppDomain.CurrentDomain.AssemblyResolve, AddressOf AssemblyResolve

        ' Extract all the embedded resources from a given path
        Dim AssemblyInfos = ExtractResourceFromAssembly(GetFiles(txtPath.Text))

        ' and bind the extracted strings to a DataGrid
        DataGrid1.AutoGenerateColumns = True
        DataGrid1.ItemsSource = AssemblyInfos

    End Sub

    ''' <summary>
    ''' Walks over a path and returns a list of files ending with .dll
    ''' </summary>
    ''' <param name="path">The path containing the assemblies.</param>
    ''' <returns>All list of files ending with .dll</returns>
    ''' <remarks></remarks>
    Private Function GetFiles(ByVal path As String) As List(Of String)

        If path.Length = 0 Then
            MessageBox.Show("You have to enter a path.")
            Return Nothing
        End If

        Dim query = From file In My.Computer.FileSystem.GetFiles(path, FileIO.SearchOption.SearchAllSubDirectories)
                    Where (file.EndsWith(".dll"))
                    Order By file

        Return query.ToList

    End Function

    ''' <summary>
    ''' Extracts all string resources from a list of assemblies and returns them as a list of AssemblyInfo-Objects holding the extracted strings.
    ''' </summary>
    ''' <param name="fileNames">The list of assemblies.</param>
    ''' <returns>A List of AssemblyInfo-Objects holding the extracted strings.</returns>
    ''' <remarks></remarks>
    Private Function ExtractResourceFromAssembly(ByVal fileNames As List(Of String)) As List(Of AssemblyInfo)

        Dim ci As CultureInfo = Thread.CurrentThread.CurrentCulture
        Dim assemblyInfos As New List(Of AssemblyInfo)
        Dim rm As System.Resources.ResourceManager

        For Each file In fileNames

            ' Load the assembly
            Dim _assembly = Assembly.LoadFrom(file)

            ' Retrieve all the resources ending with ".resources"
            Dim _resourceNames = _assembly.GetManifestResourceNames.Where(Function(n) n.EndsWith(".resources"))

            For Each resourceName In _resourceNames

                ' remove the ".resources" from the end of the string
                resourceName = resourceName.Remove(resourceName.Length - ".resources".Length, ".resources".Length)

                ' create the respective ResourceManager
                Try
                    rm = New System.Resources.ResourceManager(resourceName, _assembly)

                    ' and get the the ResourceSet
                    Dim resourceSet = rm.GetResourceSet(ci, True, False)

                    If resourceSet IsNot Nothing Then

                        Dim id As IDictionaryEnumerator = resourceSet.GetEnumerator()

                        While id.MoveNext()

                            If id.Value.GetType.ToString = "System.String" Then
                                assemblyInfos.Add(
                                        New AssemblyInfo With {
                                            .Path = file,
                                            .Assembly = _assembly.ManifestModule.ScopeName,
                                            .Resource = resourceName,
                                            .Name = id.Key.ToString,
                                            .Value = id.Value.ToString}
                                        )
                            End If

                        End While

                        resourceSet.Close()

                    End If

                Catch ex As Exception
                    assemblyInfos.Add(
                        New AssemblyInfo With {
                            .Path = file,
                            .Assembly = "Error",
                            .Resource = ex.Message}
                        )

                End Try

            Next

        Next

        Return assemblyInfos

    End Function

    Private Function AssemblyResolve(ByVal o As Object, ByVal e As ResolveEventArgs) As Assembly
        Dim aName As New AssemblyName(e.Name)

        Dim path1 = "The path where referenced assemblies are located."
        Dim p1 = Path.Combine(path1, aName.Name + ".dll")
        If (File.Exists(p1)) Then
            Return Assembly.LoadFile(p1)
        End If

        Return Nothing

    End Function

End Class

Public Class AssemblyInfo
    Public Property Path As String
    Public Property Assembly As String
    Public Property Resource As String
    Public Property Name As String
    Public Property Value As String
End Class