原文 Display Database Image using MS SQL Server 2008 Reporting Services
With the new release of
MS SQL Server 2008 Reporting Services
has introduced a new feature that will help report developers (Business Intelligence BI professionals) to
display images stored in database tables
.
Since Microsoft SQL Server Reporting Services is gaining power in covering the requirements of business intelligence developers day by day, it is gaining more acceptance from the BI professionals.
For building a Microsoft Reporting Services solution to
display database image or display image from database
, we will first create sql database image table and insert images into the sql database for populating table with sample data.
Of course you should either create a new MS Reporting Services solution or open an existing MS Reporting Services solution and add reporting services report to this solution.
Prepare SQL Database and Populate with Sample Images in order to use in Reporing Services Report
First of all by executing the below t-sql Create Table command sql database administrators can create the database table in which sql developers will store images or image files in binary format in a varbinary(max) data type column.
CREATE TABLE DBFiles (
id int IDENTITY(1,1) NOT NULL,
fname nvarchar(1000) NOT NULL,
[file] varbinary(max)
)
Save Image in SQL Database in Binary Format
After we have created the
sql database table
to store the image files, now we can begin inserting images into the newly created sql database table.
You can also refer to SQL tutorial
Save Image to Database Table in SQL Server
for an other example.
INSERT INTO DBFiles(fname, [file])
SELECT 'Reporting-Services-2008-enriched-visualization-gadgets.jpg', * FROM OPENROWSET(
BULK N'C:\Reporting-Services-2008-enriched-visualization-gadgets.jpg',
SINGLE_BLOB
) rs;
INSERT INTO DBFiles(fname, [file])
SELECT 'what-is-new-with-SQL-Server-2008.jpg', * FROM OPENROWSET(
BULK N'C:\what-is-new-with-SQL-Server-2008.jpg',
SINGLE_BLOB
) rs;
You can download the sample image files photos I've taken during TechEd Developers 2007 related with
MS SQL Server 2008
and
MS SQL Server Reporting Services 2008
from the following links :
Reporting Services 2008 enriched visualization gadgets
What is new with SQL Server 2008
Let's see how images we have inserted into the sql table is seen when we execute a SELECT statement.
SELECT * FROM Files
Now we have created our sample sql table and populate the image table with sample image files.
We can now create our MS SQL Server Reporting Services 2008 report which will display the image files stored in the SQL Server database table.
Build SQL Reporting Services DataSet for Sample Report
First of all, we will create a
dataset
for reading data from
SQL Server database image table
.
We will use this dataset for databinding to
reporting services table control
and
image control
within the table cells for each row in the sql image table.
The sql query for the dataset is :
SELECT * FROM DBFiles
This t-sql query will supply the sql data in binary format which we will display using Reporting Services.
Design Reporting Services Report in order to Display Database Image
After the dataset of the
SQL Reporting Services report
is prepared, now we can switch to the design view.
Place a table onto the Design surface of the SSSRS 2008 report.
Then set the DataSetName property of the new
Tablix component
to the dataset we have created for this sample report.
Then set field properties or the columns of the report design view tablix component as you wish.
Select the Reporting Services image component from the Toolbox and drag and drop into an empty tablix column.
This action will trigger the
Reporting Services Image Properties Wizard
.
You can later alter the properties of the image cell by a click on the
image properties
menu item on the context menu of the image cell.
Set the image properties as follows :
1) Set the
image source
as
database
, since we are selecting the image in binary format from database using reporting services dataset.
2) When
database
is chosen as the source of the image, you will select the field name as [file] field of the dataset in the
Use this field
dropdown list.
3) Also as the last step select the MIME type as
image/jpeg
(or which is approtiate for your case)
Resize Database Image and Size Properties of the SQL Database Image
You can also set the
size
properties of the database image and image component within the
tablix
component by displaying the image properties and navigating to the
Size
tab.
There you can select one of the following size properties for display database images.
Original size
Fit to size
Fit proportional
Clip
I have selected
fit proportional
option which will resize image to fit inside the item while maintaining the aspect ratio.
Display Database Image on a SQL Server Reporting Services Report
After we have finished developing the MS SQL Server 2008 Reporting Services report , if you switch to the preview tab we can see the images stores in sql table on our sample SSRS report.
Display Database Image using MS SQL Server 2008 Reporting Services
