Extracting Image datatype content with SQL2005?

  • Thread starter Thread starter Joe Cool
  • Start date Start date
Joe Cool said:
I am cross posting this request for help in both the C#,NET and the
SQLServer newsgroups in he hope that some reader of one these
newsgroups can offer some help.

I am well aware that the image datatype (as well as others) are being
phased out in some future version of SQL Server. For that reason, in
all of my personal C#.NET projects that store images use the
varbinary(max) datatype.

I am in the process of converting a VB.NET project that I use for
consulting work with a former (and hopefully soon to be repeat)
employer tp C#.NET. When originally designed, their software was based
on SQL7. Currently their software supports SQL2005. One key table in
their database is a table of images and (not sure if it still does)
but legacy databases utilize the image datatype to store images. With
SQL7 I used a SQL command line utility called TEXTCOPY to extract the
binary content of image columns to a JPG file.

This utility needs to be able to extract these images to external
files even for legacy databases that may still use the image datatype.
But I find that the technique I use to extract images from a
varbinary(max) column doesn't work for image datatypes. And I see that
SQL2005, while it still supports image datatype, the TEXTCOPY utility
is nowhere to be found.

Any ideas on how to extract image data types from a SQL2005 database
with C#.NET?
Click to expand...

Try like this

using System.IO;
using System.Drawing;


...............................
private PictureBox myPictureBox1;
.................................

DBCon dbcon = new DBCon(); //defined by me
dbcon.Open(); //database open

string sSql = "SELECT [ImageCol] FROM [ImageDataBase] ";

DataSet ds = dbcon.GetDataSet(sSql); //Get DataSet
dbcon.Close(); //database close

if (ds != null && ds.Tables.Count>0 )
{
byte[] image = (byte[])ds.Tables[0].Rows[0]["ImageCol"];
MemoryStream ms = new MemoryStream(image, 0, image.Length);
Bitmap bitmap = new Bitmap(ms);
ms.Close();
ms.Dispose();

myPictureBox1.Picture = bitmap; //show bitmap
myPictureBox1.FitToPictureBox(); //fit image to control
}
 
That is what the docs says.

I am somewhat skeptical about how soon it will happen.

Using *var*(max) for new stuff absolutely makes sense.

But I would hurry slowly to convert existing code.
I am in the process of converting a VB.NET project that I use for
consulting work with a former (and hopefully soon to be repeat)
employer tp C#.NET. When originally designed, their software was based
on SQL7. Currently their software supports SQL2005. One key table in
their database is a table of images and (not sure if it still does)
but legacy databases utilize the image datatype to store images. With
SQL7 I used a SQL command line utility called TEXTCOPY to extract the
binary content of image columns to a JPG file.

This utility needs to be able to extract these images to external
files even for legacy databases that may still use the image datatype.
But I find that the technique I use to extract images from a
varbinary(max) column doesn't work for image datatypes. And I see that
SQL2005, while it still supports image datatype, the TEXTCOPY utility
is nowhere to be found.

Any ideas on how to extract image data types from a SQL2005 database
with C#.NET?
Click to expand...

Try like this

using System.IO;
using System.Drawing;


...............................
private PictureBox myPictureBox1;
.................................

DBCon dbcon = new DBCon(); //defined by me
dbcon.Open(); //database open

string sSql = "SELECT [ImageCol] FROM [ImageDataBase] ";

DataSet ds = dbcon.GetDataSet(sSql); //Get DataSet
dbcon.Close(); //database close

if (ds != null&& ds.Tables.Count>0 )
{
byte[] image = (byte[])ds.Tables[0].Rows[0]["ImageCol"];
MemoryStream ms = new MemoryStream(image, 0, image.Length);
Bitmap bitmap = new Bitmap(ms);
ms.Close();
ms.Dispose();

myPictureBox1.Picture = bitmap; //show bitmap
myPictureBox1.FitToPictureBox(); //fit image to control
}
Click to expand...


Using DataSet should work, but it really does not provide
any value here that a more lightweight data reader would
not provide.

Arne
 
Arne said:
That is what the docs says.

I am somewhat skeptical about how soon it will happen.

Using *var*(max) for new stuff absolutely makes sense.

But I would hurry slowly to convert existing code.
Click to expand...

Given how much simpler the MAX data types are over text/image/ntext I see
all reason to change existing code. At least if you run into one of the
many quirks with the old data types.
--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
 
Given how much simpler the MAX data types are over text/image/ntext I see
all reason to change existing code. At least if you run into one of the
many quirks with the old data types.
Click to expand...

That is an OK reason to change. It can sometimes be frustrating.

I am a lot more skeptical about the datatypes disappearing
in SQLServer 2012 (or 2015 for that matter). It would just
create too much chaos.

Arne
 
Back
Top