Display varbinary Image in Gridview

The following will show how to retrieve an image from SQL Server and display it in a GridView on an ASP.NET web page.

Create a table in the database:

CREATE TABLE Surplus([Surplus Id] int not null, 
Department nchar(50),
Category nchar(25),
Item nchar(75),
Visible bit,
TransferableImage varbinary(max),
CONSTRAINT PK_Surplus_SurplusId PRIMARY KEY([Surplus Id]));

Note: If a table column name contains a space, it’s necessary to surround it with []. I prefer to create database column names without spaces.

---

Before starting, ensure that the appropriate Visual Studio Workloads/Individual Components are installed.

VS 2017:

• Open Visual Studio Installer
• Click Modify
• Click Workloads tab
• Ensure the following are checked: .NET desktop development, ASP.NET and web development, Data storage and processing
• Click Individual Components
• Under .NET, check: .NET Framework 4.7.2 SDK and .NET Framework 4.7.2 targeting pack
• Under “Code Tools”, check ClickOnce Publishing and NuGet package manager
• Select Download all, then install
• Click Modify

---

VS 2017:

Create a new project

• Open Visual Studio
• Click File
• Select New
• Select Project
• On left, click Visual Basic
• On left, click Web
• Select ASP.NET Web Application (.NET Framework); For “Framework”, select .NET Framework 4.7.2
• Click OK
• Select Empty
• Click OK

Note: Ensure Option Strict is turned on.

Find your Windows server name:

• Open a cmd window
• Type: hostname

Find your SQL Server instance name:

• Open a cmd window
• type: sc query | find /i "SQL Server"

Note: You’ll see something like the following: DISPLAY_NAME: SQL Server (SQLEXPRESS). The SQL Server instance name is within (). In this case, the SQL Server instance name is: SQLEXPRESS

We’ll use the following:

• Windows server name: ICTSQL
• SQL Server instance name: ICTSQL
• Database name: ICTSQL
• Authentication type: Windows authentication

Note: I wouldn’t recommend naming the Windows server, the database instance, and the database name the same name, as it can lead to confusion. However, my understanding is that as of the time of this posting, that they all currently have the name ICTSQL.

Open Solution Explorer

• In VS menu, click View
• Select Solution Explorer

Add connection string to Web.config

• In Solution Explorer, double-click Web.config

In code below, modify the code within <connectionStrings>...</connectionStrings> for your environment. See SQL Server connection strings for more information.

Web.config

<?xml version="1.0" encoding="utf-8"?>

<!--
  For more information on how to configure your ASP.NET application, please visit
  https://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
  <connectionStrings>
    <add name="ictsqlConnection" connectionString="Data Source=.\SQLEXPRESS;Database=ICTSQL;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False" providerName="System.Data.SqlClient"/>
  </connectionStrings>
  <system.web>
    <compilation debug="true" strict="false" explicit="true" targetFramework="4.7.2"/>
    <httpRuntime targetFramework="4.7.2"/>
  </system.web>
  <system.codedom>
    <compilers>
      <compiler language="c#;cs;csharp" extension=".cs"
        type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
        warningLevel="4" compilerOptions="/langversion:default /nowarn:1659;1699;1701"/>
      <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb"
        type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
        warningLevel="4" compilerOptions="/langversion:default /nowarn:41008 /define:_MYTYPE=\&quot;Web\&quot; /optionInfer+"/>
    </compilers>
  </system.codedom>
</configuration>

In the code below, I’ve adapted the code for the menu from this post

Open Properties Window

• In VS menu, click View
• Select Properties Window

Add XML file to project (name: surplusMenu.xml)

• In VS menu, click Project
• Select Add New Item…
• On left side, click Data
• Click XML File (name: surplusMenu.xml)
• In Properties Window, set Copy to Output Directory to Copy Always

surplusMenu.xml:

<?xml version="1.0" encoding="utf-8" ?>
<surplusMenu text="Home" url="./default.aspx">
    <main text="Surplus" url="./addDatabaseRecord.aspx">
        <page text="Add Record" url ="./addDatabaseRecord.aspx" />
    </main>
</surplusMenu>

Add a Module (name: Module1.vb)

• In Solution Explorer, right-click <project name> (ex: DatabaseGridViewTest)
• Select Add
• Select New Item…
• On left side, click Code
• Select Module (name: Module1.vb)
• Click Add

Module1.vb

Imports System.Drawing
Imports System.IO

Module Module1
    Public Function ResizeImage(imageBytes As Byte(), maxWidth As Integer, maxHeight As Integer) As Byte()
        Dim modifiedImageBytes As Byte()
        Dim ratioX As Double = 0
        Dim ratioY As Double = 0
        Dim ratio As Double = 0
        Dim newWidth As Integer = 0
        Dim newHeight As Integer = 0

        Using ms As MemoryStream = New MemoryStream(imageBytes)
            Using originalImg As Bitmap = New Bitmap(ms)
                ratioX = CType(maxWidth, Double) / originalImg.Width
                ratioY = CType(maxHeight, Double) / originalImg.Height

                'set value
                ratio = Math.Min(ratioX, ratioY)

                'calculate new width and height
                newWidth = CType((CType(originalImg.Width, Double) * ratio), Integer)
                newHeight = CType((CType(originalImg.Height, Double) * ratio), Integer)

                'create new Bitmap with desired size
                Using newImg As Bitmap = New Bitmap(newWidth, newHeight)
                    Using g As Graphics = Graphics.FromImage(newImg)
                        g.DrawImage(originalImg, 0, 0, newWidth, newHeight)
                        g.Save()
                    End Using

                    Using newImgMs As MemoryStream = New MemoryStream()
                        'save in jpeg format
                        newImg.Save(newImgMs, Imaging.ImageFormat.Jpeg)

                        'save as Byte()
                        modifiedImageBytes = newImgMs.ToArray()
                    End Using
                End Using
            End Using
        End Using

        Return modifiedImageBytes
    End Function
End Module

Add WebForm (name: addDatabaseRecord.aspx)

• In Solution Explorer, right-click <project name> (ex: DatabaseGridViewTest)
• Select Add
• Select New Item…
• Select Web Form (name: addDatabaseRecord.aspx)
• Click Add

addDatabaseRecord.aspx

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="addDatabaseRecord.aspx.vb" Inherits="DatabaseGridViewTest.addDatabaseRecord" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>

    <body>
        <form id="form1" runat="server">
            <!-- menu -->
            <div id="menu" style="background-color:cornflowerblue"  >
                <asp:XmlDataSource runat="server" ID="xmldatasource" DataFile="surplusMenu.xml"></asp:XmlDataSource>

                <asp:Menu ID="menuNavigator" runat="server" Width="760px" DisappearAfter="0" StaticSubMenuIndent="10px" StaticEnableDefaultPopOutImage="False" Orientation="Horizontal" StaticDisplayLevels="2" DataSourceID="xmldatasource" ItemWrap="True" >
                    <StaticHoverStyle Height="34px" Width="50px" BackColor="#93C6FF" />
                    <StaticMenuItemStyle HorizontalPadding="8px" Width="50px" Height="34px" CssClass="menustyle" ForeColor="Black" VerticalPadding="2px" />
                    <DynamicMenuStyle Width="50px" />
                    <DynamicSelectedStyle BackColor="#507CD1"></DynamicSelectedStyle>
                    <DynamicHoverStyle BackColor="#6597F0" ForeColor="White" Font-Bold="True" />
                    <DynamicMenuItemStyle BackColor="#0A398D" Width="125px" HorizontalPadding="15px" VerticalPadding="6px" ForeColor="White" Font-Size="14px" />
                    <DataBindings>
                        <asp:MenuItemBinding DataMember="surplusMenu" TextField="text" NavigateUrlField="url"></asp:MenuItemBinding>
                        <asp:MenuItemBinding DataMember="main" NavigateUrlField="url" TextField="text"></asp:MenuItemBinding>
                        <asp:MenuItemBinding DataMember="page" NavigateUrlField="url" TextField="text"></asp:MenuItemBinding>
                    </DataBindings>
                </asp:Menu>  
            </div> 

            <div style="position:absolute;left:300px">
                <h2>Surplus Record Entry</h2>
            </div>

            <div>
                <!-- Surplus Id -->
                <asp:Label ID="LabelSurplusId" runat="server" Text="Surplus Id:" style="position:absolute;left:50px;top:100px;font-weight:bold"></asp:Label>
                <asp:TextBox ID="TextBoxSurplusId" runat="server" style="position:absolute;left:200px;top:100px;width:75px" ></asp:TextBox>

                <!-- Department, Category -->
                <asp:Label ID="LabelDepartment" runat="server" Text="Department:" style="position:absolute;left:50px;top:140px;font-weight:bold"></asp:Label>
                <asp:TextBox ID="TextBoxDepartment" runat="server" style="position:absolute;left:200px;top:140px;width:150px"></asp:TextBox>

                <asp:Label ID="LabelCategory" runat="server" Text="Category:" style="position:absolute;left:450px;top:140px;font-weight:bold"></asp:Label>
                <asp:TextBox ID="TextBoxCategory" runat="server" style="position:absolute;left:550px;top:140px;width:150px"></asp:TextBox>

                <!-- Item, IsVisible -->
                <asp:Label ID="LabelItem" runat="server" Text="Item:" style="position:absolute;left:50px;top:180px;font-weight:bold"></asp:Label>
                <asp:TextBox ID="TextBoxItem" runat="server" style="position:absolute;left:200px;top:180px;width:150px"></asp:TextBox>

                <asp:CheckBox ID="CheckBoxIsVisible" runat="server" style="position:absolute;left:448px;top:180px;width:125px;font-weight:bold" Text="  Is Visible?" Checked="true"/>

                <!-- Transferable Image -->
                <asp:Label ID="LabelTransferableImage" runat="server" Text="Transferable Image:" style="position:absolute;left:50px;top:220px;font-weight:bold"></asp:Label>
                <asp:FileUpload ID="FileUploadTransferableImage" runat="server" style="position:absolute;left:200px;top:220px;font-weight:bold"/>
            </div>

            <div>
                <asp:Button ID="ButtonSave" runat="server" Text="Save" style="position:absolute;left:350px;top:280px;height:40px;width:125px" OnClick="ButtonSave_Click" />
            </div>
        
            <div>
                <asp:Label ID="LabelMsg" runat="server" Text="" style="position:absolute;left:350px;top:340px"></asp:Label>
            </div>
        </form>
    </body>
</html>

In Solution Explorer, right-click addDatabaseRecord.aspx. Select View Code

addDatabaseRecord.aspx.vb

Note: If a (database) table column name contains a space, it’s necessary to surround it with [].

Imports System.Configuration
Imports System.Data.SqlClient

Public Class addDatabaseRecord
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        SetSurplusId()
    End Sub

    Protected Sub ClearPage()
        TextBoxSurplusId.Text = String.Empty
        TextBoxDepartment.Text = String.Empty
        TextBoxCategory.Text = String.Empty
        TextBoxItem.Text = String.Empty
        CheckBoxIsVisible.Checked = True

        'dispose
        FileUploadTransferableImage.Dispose()

        'create new instance
        FileUploadTransferableImage = New FileUpload()
    End Sub

    Private Function GetNextSurplusId() As Integer
        Dim nextSurplusId As Integer = 0
        Dim connectionStr As String = ConfigurationManager.ConnectionStrings("ictsqlConnection").ConnectionString
        Dim sqlText As String = "SELECT Max([Surplus Id]) from Surplus;"

        Using con As SqlConnection = New SqlConnection(connectionStr)
            'open
            con.Open()

            Using cmd As SqlCommand = New SqlCommand(sqlText, con)
                'get last surplus id from database and increment it by 1
                nextSurplusId = (DirectCast(cmd.ExecuteScalar(), Integer)) + 1
            End Using
        End Using

        Return nextSurplusId
    End Function

    Protected Function SaveSurplusRecord(surplusId As Integer, department As String, category As String, item As String, visible As Boolean, transferableImageBytes As Byte()) As Integer
        Dim rowsAffected As Integer = 0
        Dim connectionStr As String = ConfigurationManager.ConnectionStrings("ictsqlConnection").ConnectionString
        Dim sqlText As String = "INSERT INTO Surplus([Surplus Id], Department, Category, Item, Visible, TransferableImage) VALUES(@surplusId, @department, @category, @item, @visible, @transferableImage);"

        Using con As SqlConnection = New SqlConnection(connectionStr)
            'open
            con.Open()

            Using cmd As SqlCommand = New SqlCommand(sqlText, con)

                cmd.Parameters.Add("@surplusId", SqlDbType.Int).Value = surplusId

                If String.IsNullOrEmpty(department) Then
                    cmd.Parameters.Add("@department", SqlDbType.NChar).Value = DBNull.Value
                Else
                    cmd.Parameters.Add("@department", SqlDbType.NChar).Value = department
                End If

                If String.IsNullOrEmpty(category) Then
                    cmd.Parameters.Add("@category", SqlDbType.NChar).Value = DBNull.Value
                Else
                    cmd.Parameters.Add("@category", SqlDbType.NChar).Value = category
                End If

                If String.IsNullOrEmpty(item) Then
                    cmd.Parameters.Add("@item", SqlDbType.NChar).Value = DBNull.Value
                Else
                    cmd.Parameters.Add("@item", SqlDbType.NChar).Value = item
                End If

                'size = -1 is needed to exceed 8000 bytes; it maps to varbinary(max)
                cmd.Parameters.Add("@transferableImage", SqlDbType.VarBinary, -1).Value = transferableImageBytes

                'execute
                rowsAffected = cmd.ExecuteNonQuery()
            End Using
        End Using

        Return rowsAffected
    End Function

    Private Sub SetSurplusId()
        Dim nextSurplusId As Integer = GetNextSurplusId()

        If nextSurplusId > 0 Then
            TextBoxSurplusId.Text = nextSurplusId.ToString()
        End If
    End Sub

    Protected Sub ButtonSave_Click(sender As Object, e As EventArgs)
        If FileUploadTransferableImage.HasFile() Then
            LabelMsg.Text = "Filename: " & FileUploadTransferableImage.FileName & " File bytes: " & FileUploadTransferableImage.FileBytes.Length

            Dim surplusIdInt As Integer = 0

            If Int32.TryParse(TextBoxSurplusId.Text, surplusIdInt) Then
                'save record to database
                Dim rowsAffected As Integer = SaveSurplusRecord(surplusIdInt, TextBoxDepartment.Text, TextBoxCategory.Text, TextBoxItem.Text, CheckBoxIsVisible.Checced, FileUploadTransferableImage.FileBytes())

                If rowsAffected > 0 Then
                    LabelMsg.Text = String.Format("Record saved (Surplus Id: {0}; Item: {1})", surplusIdInt.ToString(), TextBoxItem.Text)

                    ClearPage()
                    SetSurplusId()

                    'System.Threading.Thread.Sleep(1000)
                    'Response.Redirect("addDatabaseRecord.aspx")
                Else
                    LabelMsg.Text = String.Format("Error: Record not saved (Surplus Id: {0}; Item: {1})", surplusIdInt.ToString(), TextBoxItem.Text)
                End If
            Else
                LabelMsg.Text = String.Format("Error: Surplus Id must be an integer. (Surplus Id: '{0}')", TextBoxSurplusId.Text)
            End If
        Else
            LabelMsg.Text = "Error: Transferable image has not been selected."
        End If
    End Sub
End Class

Add WebForm (name: default.aspx)

• In Solution Explorer, right-click <project name> (ex: DatabaseGridViewTest)
• Select Add
• Select New Item…
• Select Web Form (name: default.aspx)
• Click Add

default.aspx

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="default.aspx.vb" Inherits="DatabaseGridViewTest._default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <!-- menu -->
            <div id="menu" style="background-color:cornflowerblue"  >
                <asp:XmlDataSource runat="server" ID="xmldatasource" DataFile="surplusMenu.xml"></asp:XmlDataSource>

                <asp:Menu ID="menuNavigator" runat="server" Width="760px" DisappearAfter="0" StaticSubMenuIndent="10px" StaticEnableDefaultPopOutImage="False" Orientation="Horizontal" StaticDisplayLevels="2" DataSourceID="xmldatasource" ItemWrap="True" >
                    <StaticHoverStyle Height="34px" Width="50px" BackColor="#93C6FF" />
                    <StaticMenuItemStyle HorizontalPadding="8px" Width="50px" Height="34px" CssClass="menustyle" ForeColor="Black" VerticalPadding="2px" />
                    <DynamicMenuStyle Width="50px" />
                    <DynamicSelectedStyle BackColor="#507CD1"></DynamicSelectedStyle>
                    <DynamicHoverStyle BackColor="#6597F0" ForeColor="White" Font-Bold="True" />
                    <DynamicMenuItemStyle BackColor="#0A398D" Width="125px" HorizontalPadding="15px" VerticalPadding="6px" ForeColor="White" Font-Size="14px" />
                    <DataBindings>
                        <asp:MenuItemBinding DataMember="surplusMenu" TextField="text" NavigateUrlField="url"></asp:MenuItemBinding>
                        <asp:MenuItemBinding DataMember="main" NavigateUrlField="url" TextField="text"></asp:MenuItemBinding>
                        <asp:MenuItemBinding DataMember="page" NavigateUrlField="url" TextField="text"></asp:MenuItemBinding>
                    </DataBindings>
                </asp:Menu>  
            </div>    

            <div>
                <asp:Label ID="LabelMsg" runat="server" Text="" style="position:absolute;left:50px; top:60px"></asp:Label>
            </div>

            <div style="position:absolute;left:50px; top:100px">
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"  DataKeyNames="Surplus Id" GridLines="Both">
                    <Columns>
                        <asp:BoundField DataField="Surplus Id" HeaderText="Surplus Id" ItemStyle-HorizontalAlign="Center" Visible="True" />
                        <asp:BoundField DataField="Department" HeaderText="Department" ItemStyle-HorizontalAlign="Center" />
                        <asp:BoundField DataField="Category" HeaderText="Category" ItemStyle-HorizontalAlign="Center" />
                        <asp:BoundField DataField="Item" HeaderText="Item" ItemStyle-HorizontalAlign="Left" />
                        <asp:BoundField DataField="Visible" HeaderText="Visible" ItemStyle-HorizontalAlign="Center" />

                        <asp:TemplateField HeaderText="Transferable Image" ItemStyle-HorizontalAlign="Center">
                            <ItemTemplate>
                                <asp:Image ID="TransferableImg" runat="server" ImageUrl="<%# Eval("TransferableImageBase64", "{0}") %>" />
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                </asp:GridView>
            </div>
        </form>
    </body>
</html>

In Solution Explorer, right-click default.aspx. Select View Code

default.aspx.vb

Note: If a (database) table column name contains a space, it’s necessary to surround it with []. Also, in the code below the images are resized when they are loaded. It may be preferrable to resize each image when saving it to the database so it doesn’t have to resized each time it’s loaded.

Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Drawing
Imports System.IO

Public Class _default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim connectionStr As String = ConfigurationManager.ConnectionStrings("ictsqlConnection").ConnectionString

        Using con As SqlConnection = New SqlConnection(connectionStr)
            'open
            con.Open()

            Using cmd As SqlCommand = New SqlCommand("SELECT [Surplus Id], Department, Category, Item, Visible, TransferableImage FROM Surplus", con)
                Using da As SqlDataAdapter = New SqlDataAdapter(cmd)

                    Dim dt As DataTable = New DataTable()

                    'fill DataTable with data from database
                    da.Fill(dt)

                    'add column that will store the image as a base64 string
                    dt.Columns.Add("TransferableImageBase64", GetType(System.String))

                    For i As Integer = 0 To dt.Rows.Count - 1
                        'convert image Byte() from database to base64 string and store in a new column in the DataTable
                        'dt(i)("TransferableImageBase64") = "data:image/jpg;base64," & Convert.ToBase64String(CType(dt(i)("TransferableImage"), Byte()))

                        'resize image to desired size and convert image Byte() to base64 string, and store in a new column in the DataTable
                        dt(i)("TransferableImageBase64") = "data:image/jpg;base64," & Convert.ToBase64String(ResizeImage(CType(dt(i)("TransferableImage"), Byte()), 50, 50))
                    Next

                    'remove column that contains Byte() from DataTable
                    dt.Columns.Remove("TransferableImage")

                    GridView1.DataSource = dt
                    GridView1.DataBind()
                End Using
            End Using
        End Using
    End Sub
End Class

Here’s a demo:

---

Find name of IIS application pool

Win 10:

• Open Control Panel (View by: Small icons)
• Double-click Administrative Tools
• Double-click Internet Information Services (IIS) Manager
• Expand <server name>
• Expand Sites
• Right-click desired website
• Select Manage Website
• Select Advanced Settings…
• Write down property value for Application Pool (ex: ICTSQL)

---

Below shows how to add the IIS user (NT AUTHORITY\IUSR) to SQL Server, how to add it to the database, and how to grant it permissions for a table. (This assumes that both SQL Server and IIS (the web server) are running on the same server. You’ll want to repeat this process for the IIS APPPOOL user (ex: IIS APPPOOL\ICTSQL) as well.

Download/install SQL Server Management Studio (SSMS)

Create a Database User

• Open Microsoft SQL Server Management Studio
• Expand Security
• Right-click Logins
• Select New Login
• Select Windows authentication
• Login name: NT AUTHORITY\IUSR
• Select the desired default database (ex: ICTSQL)
• Click OK

Add User to Database

• Open Microsoft SQL Server Management Studio
• Expand Databases
• Expand <desired database> (ex: ICTSQL)
• Expand Security
• Right-click Users
• Select New User…
• User name: NT AUTHORITY\IUSR
• For “Login name”, Click ...
• Click Browse
• Select NT AUTHORITY\IUSR
• Click OK
• Click OK
• Leave “Default schema”, blank.
• Click OK

Grant User Permissions on Table

• Open Microsoft SQL Server Management Studio
• Expand Databases
• Expand <desired database> (ex: ICTSQL)
• Expand Tables
• Right-click <desired table> (ex: dbo.Surplus)
• Select Properties
• Under “Select a page”, click Permissions
• Click Search
• Click Browse
• Check desired user (ex: NT AUTHORITY\IUSR)
• Click OK
• Click OK
• Under Grant, check the following: Delete, Insert, Select, Update, References (you may also want to grant: View change tracking, View definition)
• Click OK

Note: “With Grant” allows the user to grant the permissions to another user.

Repeat the above process for the “IIS APPPOOL” user. (ex: IIS APPPOOL\ICTSQL)

Resources:

• Store Connection String in Web.config
• SQL Server Connection Strings
• How to display Base64 images in HTML
• Displaying Image in gridview with RowDataBound in ASP.NET
• Assign Image URL in ASP.Net GridView based on column value in C# and VB.Net
• Bind GridView In ASP.NET Step by Step
• Resize image proportionally with MaxHeight and MaxWidth constraints
• How to control ASP.NET controls location in the page
• creating menu bar in asp.net
• Option Strict
• Add IIS 7 AppPool Identities as SQL Server Logons