SQLAuthority News – Author Visit – Toronto, Canada – Insert Image in Database

I am traveling to Toronto from Microsoft MVP Sumeet. I will be very tired as I am continuously working very hard from last 27th Feb. I am still Jet Legged from my trip from India to USA and now I am again changing time zones by visiting Canada. I get all my energy from feeling that what I am doing is helping community and I am working hard to help people who are looking for help.

Interestingly Steven Biggins, a reader of this blog was with me in same flight to Canada. He recognized me and asked me following question. As I have never done this before I had to pull out my laptop in flight and start writing code. Following T-SQL script demonstrates the code which inserts the image in database. You will need small image on your hard drive to create this example. You can download Image and T-SQL Code.

USE [AdventureWorks]
GO
--Create Table
CREATE TABLE [dbo].[ImageTest](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Image] [varbinary](MAX) NULL
)
ON [PRIMARY]
GO
--Insert Image
INSERT INTO [dbo].[ImageTest]([Image])
SELECT * FROM
OPENROWSET
(BULK N'C:\ImageCode\sa.jpg', SINGLE_BLOB) AS Document
GO
--Clean Up DB
DROP TABLE [dbo].[ImageTest]
GO

Now my question to reader is how to retrieve the image and create JPG file again. I will post the solution soon but I rather prefer that the solution provided by my reader.

Following is my travel itinerary so far:

Feb 27, 2009 – Departing Ahmedabad to Mumbai
Feb 28, 2009 – Departing Mumbai to Seattle Sheraton Hotel
March 1, 2009 – Day 1 at MVP Summit
March 2, 2009 – Day 2 at MVP Summit
March 3, 2009 – Day 3 at MVP Summit
March 4, 2009 – Day 4 at MVP Summit
March 5, 2009 – Departing Seattle to Toronto
March 6, 2009 – Tech User Group Meeting, Markham, Canada
March 7, 2009 – Departing Toronto to Mumbai
March 8, 2009 – Missing Day due to Day Line Crossing
March 9, 2009 – Arriving to Ahmedabad

Reference : Pinal Dave (http://blog.sqlauthority.com)

About these ads

10 thoughts on “SQLAuthority News – Author Visit – Toronto, Canada – Insert Image in Database

  1. I don’t know if there is any way to do this in T-SQL but the CLR procedure below should do the job.

    using System;
    using System.Data;
    using System.Data.Sql;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;
    using System.IO;

    public partial class StoredProcedures
    {
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void usp_RetrieveBlobData(string query,string outpath)
    {
    using(SqlConnection connection = new SqlConnection(“context connection=true”))
    {
    connection.Open();
    SqlCommand command = new SqlCommand(query, connection);
    SqlDataReader reader = command.ExecuteReader();

    using (reader)
    {
    while(reader.Read())
    {
    FileStream fs = File.Create(outpath);
    BinaryWriter bw = new BinaryWriter(fs);

    byte[] file = (byte[])reader.GetSqlBinary(0);

    bw.Write(file);

    bw.Close();
    fs.Close();
    }
    }
    }
    }
    };

    I threw this together real quick, so I know the code can be cleaned up.

    The first parameter is the sql query and the second is the output path. Make sure the CLR procedure’s permission level is set to external.

  2. Hi Pinal,

    Let me give you congratulations for having a great honor to take part in Microsoft Global Summit.

    I am pleased that I am taking an opportunity to answer in your blog. After reading your blog I felt to create one detail example for same. I just created it on my blog at:

    http://ritesh-shah.blogspot.com/2009/03/image-store-in-sql-server-2005-database.html

    or

    http://riteshshahwordpress.com/2009/03/07/image-store-in-sql-server-2005-database-and-retrieve-it-in-aspnet-application-with-c/

    Hope to see you soon and eager to know your experience from your own alluring words by your mouth. However, I get some of that from you blog.

    Thanks,

    Ritesh Shah

  3. – Added by Lam
    — Last modified 209.03.07
    — Using TextCopy in MSSQL$instancename\Binn for retrieving image.

    — 0. xp_cmdshell is turned on from sp_configure
    sys.sp_configure ‘xp_cmdshell’, 1
    RECONFIGURE;

    GO
    — 1 Declaration
    DECLARE @sql VARCHAR(500),
    @server sysname,
    @user sysname,
    @pwd sysname,
    @db sysname,
    @table sysname,
    @column sysname,
    @whereclause VARCHAR(500),
    @filename VARCHAR(100),
    @filepath VARCHAR(500)

    — 2. Config
    SET @server = @@SERVERNAME
    SET @user = ”– input login here
    SET @pwd = ”– and pwd
    SET @db = DB_NAME()
    SET @table = ‘ImageTest’
    SET @column = ‘Image’
    SET @whereclause = ‘”WHERE ID = 1″‘
    SET @filename = ‘ImageFromSQL.jpg’
    SET @filepath = ‘C:\tmp\’

    — 3. Set textcopy path from path environment variable(manually)
    — And set sql string
    SET @sql= ‘TextCopy ‘ +
    ‘ /S ‘ + @server +
    ‘ /U ‘ + @user +
    ‘ /P ‘ + @pwd +
    ‘ /D ‘ + @db +
    ‘ /T ‘ + @table +
    ‘ /C ‘ + @column +
    ‘ /W ‘ + @whereclause +
    ‘ /F ‘ + @filepath + @filename +
    ‘ /O ‘ +
    ‘ /Z ‘ — for debuging

    EXEC master..xp_cmdshell @sql –,no_output if wanna see the debug
    GO

  4. Pingback: SQLAuthority News - Author Visit - Complete Wrapup of Microsoft MVP Summit 2009 Trip Journey to SQL Authority with Pinal Dave

  5. Pingback: How to Upload and Download Image/BLOB Data in SQL Server Ctrl+Shift+B

  6. Pingback: SQL SERVER – BLOB – Pointer to Image, Image in Database, FILESTREAM Storage Journey to SQL Authority with Pinal Dave

  7. hello sir
    i am making window application for my college project i want to insert all primary in combobox i am using this code

    private void machineinformation_Load(object sender, EventArgs e)
    {
    //
    cn = new SqlConnection(“server=.;integrated security=True;database=kata”);
    cn.Open();
    txtcap.Text = “connection Open”;
    //for connection

    //

    string count_qr = “select count(*) as ‘Total Rows’ from machine”;
    cmd = new SqlCommand(count_qr, cn);
    dr = cmd.ExecuteReader();
    int count=0;
    if (dr.Read())
    {
    count = int.Parse(dr[0].ToString());
    }
    dr.Close();

    string combsrno_qr = “select srno from machine”;
    cmd = new SqlCommand(combsrno_qr, cn);
    dr = cmd.ExecuteReader();
    int i = 0;
    if (dr.Read())
    {

    while (count > 0)
    {

    combsrno.Items.Add(dr[i].ToString());
    i++;
    count–;
    }
    }

    //for insert srno in combsrno

    }

    this give error
    plese corret it or send me as posible as
    and also tell me that can we use sqldatareader to store rows

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #037 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s