Extracting Image datatype content with SQL2005?

[Joe Cool]

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?

 

[Arne Vajhøj]

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?

Have you tried the obvious: SqlConnection, SqlCommand, SELECT,
ExecuteReader, SqlDataReader og læse kolonnen som byte[] ?

Arne

 

[Joe Cool]

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?

Have you tried the obvious: SqlConnection, SqlCommand, SELECT,
ExecuteReader, SqlDataReader og læse kolonnen som byte[] ?

Here is what I am using that works with varbinary(max) datatype
columns.

SqlCommand cmd;
SqlDataReader rdr;
DataTable dataTable;

cmd = new SqlCommand("select imagecolumn, linkcolumn from images",
cn)'
rdr = cmd.ExecuteReader();
dataTable.Load(rdr);

 

[Joe Cool]

On 31-07-2010 19:36, Joe Cool wrote:

Have you tried the obvious: SqlConnection, SqlCommand, SELECT,
ExecuteReader, SqlDataReader og læse kolonnen som byte[] ?

Here is what I am using that works with varbinary(max) datatype
columns.

SqlCommand cmd;
SqlDataReader rdr;
DataTable dataTable;

cmd = new SqlCommand("select imagecolumn, linkcolumn from images",
cn)'
rdr = cmd.ExecuteReader();
dataTable.Load(rdr);

I should mention I write this out to an external JPG file by using a
StreamWriter and casting column 0 of the datatable to a byte array.

 

[Arne Vajhøj]

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?

Have you tried the obvious: SqlConnection, SqlCommand, SELECT,
ExecuteReader, SqlDataReader og læse kolonnen som byte[] ?

Here is what I am using that works with varbinary(max) datatype
columns.

SqlCommand cmd;
SqlDataReader rdr;
DataTable dataTable;

cmd = new SqlCommand("select imagecolumn, linkcolumn from images",
cn)'
rdr = cmd.ExecuteReader();
dataTable.Load(rdr);

Drop the data table and try:

while(rdr.Read())
{
byte[] image = (byte[])rdr[0];
string link = (string)rdr[1];
// process image and link
}

Arne

 

[Arne Vajhøj]

On 31-07-2010 19:36, Joe Cool wrote:
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?

Have you tried the obvious: SqlConnection, SqlCommand, SELECT,
ExecuteReader, SqlDataReader og læse kolonnen som byte[] ?

Here is what I am using that works with varbinary(max) datatype
columns.

SqlCommand cmd;
SqlDataReader rdr;
DataTable dataTable;

cmd = new SqlCommand("select imagecolumn, linkcolumn from images",
cn)'
rdr = cmd.ExecuteReader();
dataTable.Load(rdr);

I should mention I write this out to an external JPG file by using a
StreamWriter and casting column 0 of the datatable to a byte array.

StreamWriter is for text data.

Use a simple Stream/FileStream for binary data.

Arne

 

[Joe Cool]

On 31-07-2010 19:36, Joe Cool wrote:
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?
Have you tried the obvious: SqlConnection, SqlCommand, SELECT,
ExecuteReader, SqlDataReader og læse kolonnen som byte[] ?

Here is what I am using that works with varbinary(max) datatype
columns.

SqlCommand cmd;
SqlDataReader rdr;
DataTable dataTable;

cmd = new SqlCommand("select imagecolumn, linkcolumn from images",
cn)'
rdr = cmd.ExecuteReader();
dataTable.Load(rdr);

Drop the data table and try:

while(rdr.Read())
{
     byte[] image = (byte[])rdr[0];
     string link = (string)rdr[1];
     // process image and link

}

Umm, so are you saying that while the datatable works with a
varbinary(max) datatype, for an image datatype I should drop the
datatable and just use a data reader?

 

[Arne Vajhøj]

On 31-07-2010 19:36, Joe Cool wrote:

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?

Have you tried the obvious: SqlConnection, SqlCommand, SELECT,
ExecuteReader, SqlDataReader og læse kolonnen som byte[] ?

Here is what I am using that works with varbinary(max) datatype
columns.

SqlCommand cmd;
SqlDataReader rdr;
DataTable dataTable;

cmd = new SqlCommand("select imagecolumn, linkcolumn from images",
cn)'
rdr = cmd.ExecuteReader();
dataTable.Load(rdr);

Drop the data table and try:

while(rdr.Read())
{
byte[] image = (byte[])rdr[0];
string link = (string)rdr[1];
// process image and link

}

Umm, so are you saying that while the datatable works with a
varbinary(max) datatype, for an image datatype I should drop the
datatable and just use a data reader?

I don't know if the data table is the problem.

I know that the reader will work.

And according to your description, then you don't need
the data table.

So it is worth a try.

Arne

 

[Joe Cool]

On 31-07-2010 19:36, Joe Cool wrote:
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?
Have you tried the obvious: SqlConnection, SqlCommand, SELECT,
ExecuteReader, SqlDataReader og læse kolonnen som byte[] ?
Here is what I am using that works with varbinary(max) datatype
columns.
SqlCommand cmd;
SqlDataReader rdr;
DataTable dataTable;
cmd = new SqlCommand("select imagecolumn, linkcolumn from images",
cn)'
rdr = cmd.ExecuteReader();
dataTable.Load(rdr);

I should mention I write this out to an external JPG file by using a
StreamWriter and casting column 0 of the datatable to a byte array.

StreamWriter is for text data.

Use a simple Stream/FileStream for binary data.

Sorry, misspoke, I am using a FileStream object.

 

[Joe Cool]

On 31-07-2010 20:29, Joe Cool wrote:
On 31-07-2010 19:36, Joe Cool wrote:
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?
Have you tried the obvious: SqlConnection, SqlCommand, SELECT,
ExecuteReader, SqlDataReader og læse kolonnen som byte[] ?
Here is what I am using that works with varbinary(max) datatype
columns.
SqlCommand cmd;
SqlDataReader rdr;
DataTable dataTable;
cmd = new SqlCommand("select imagecolumn, linkcolumn from images",
cn)'
rdr = cmd.ExecuteReader();
dataTable.Load(rdr);
Drop the data table and try:
while(rdr.Read())
{
      byte[] image = (byte[])rdr[0];
      string link = (string)rdr[1];
      // process image and link
}

Umm, so are you saying that while the datatable works with a
varbinary(max) datatype, for an image datatype I should drop the
datatable and just use a data reader?

I don't know if the data table is the problem.

I know that the reader will work.

And according to your description, then you don't need
the data table.

So it is worth a try.

The only reason I was trying to use a data table is because I am using
a support class library (that I also wrote) that returns data using
just a datatable. I suppose I can enhance it to also support returning
data with a data reader.

 

[Arne Vajhøj]

On 31-07-2010 20:29, Joe Cool wrote:

On 31-07-2010 19:36, Joe Cool wrote:

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?

Have you tried the obvious: SqlConnection, SqlCommand, SELECT,
ExecuteReader, SqlDataReader og læse kolonnen som byte[] ?

Here is what I am using that works with varbinary(max) datatype
columns.

SqlCommand cmd;
SqlDataReader rdr;
DataTable dataTable;

cmd = new SqlCommand("select imagecolumn, linkcolumn from images",
cn)'
rdr = cmd.ExecuteReader();
dataTable.Load(rdr);

Drop the data table and try:

while(rdr.Read())
{
byte[] image = (byte[])rdr[0];
string link = (string)rdr[1];
// process image and link

}

Umm, so are you saying that while the datatable works with a
varbinary(max) datatype, for an image datatype I should drop the
datatable and just use a data reader?

I don't know if the data table is the problem.

I know that the reader will work.

And according to your description, then you don't need
the data table.

So it is worth a try.

The only reason I was trying to use a data table is because I am using
a support class library (that I also wrote) that returns data using
just a datatable. I suppose I can enhance it to also support returning
data with a data reader.

What is the problem when you try getting the image with the
current code?

Arne

 

[Joe Cool]

On 31-07-2010 20:39, Joe Cool wrote:
On 31-07-2010 20:29, Joe Cool wrote:
On 31-07-2010 19:36, Joe Cool wrote:
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 tablein
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 extractthe
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 seethat
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?
Have you tried the obvious: SqlConnection, SqlCommand, SELECT,
ExecuteReader, SqlDataReader og læse kolonnen som byte[] ?
Here is what I am using that works with varbinary(max) datatype
columns.
SqlCommand cmd;
SqlDataReader rdr;
DataTable dataTable;
cmd = new SqlCommand("select imagecolumn, linkcolumn from images",
cn)'
rdr = cmd.ExecuteReader();
dataTable.Load(rdr);
Drop the data table and try:
while(rdr.Read())
{
       byte[] image = (byte[])rdr[0];
       string link = (string)rdr[1];
       // process image and link
}
Umm, so are you saying that while the datatable works with a
varbinary(max) datatype, for an image datatype I should drop the
datatable and just use a data reader?
I don't know if the data table is the problem.
I know that the reader will work.
And according to your description, then you don't need
the data table.
So it is worth a try.

The only reason I was trying to use a data table is because I am using
a support class library (that I also wrote) that returns data using
just a datatable. I suppose I can enhance it to also support returning
data with a data reader.

What is the problem when you try getting the image with the
current code?

The extracted files are smaller than the JPGs extracted by the VB.NET
version and are not valid JPG files (their content is not the same).

 

[Arne Vajhøj]

On 31-07-2010 20:39, Joe Cool wrote:
On 31-07-2010 20:29, Joe Cool wrote:

On 31-07-2010 19:36, Joe Cool wrote:

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?

Have you tried the obvious: SqlConnection, SqlCommand, SELECT,
ExecuteReader, SqlDataReader og læse kolonnen som byte[] ?

Here is what I am using that works with varbinary(max) datatype
columns.

SqlCommand cmd;
SqlDataReader rdr;
DataTable dataTable;

cmd = new SqlCommand("select imagecolumn, linkcolumn from images",
cn)'
rdr = cmd.ExecuteReader();
dataTable.Load(rdr);

Drop the data table and try:

while(rdr.Read())
{
byte[] image = (byte[])rdr[0];
string link = (string)rdr[1];
// process image and link

}

Umm, so are you saying that while the datatable works with a
varbinary(max) datatype, for an image datatype I should drop the
datatable and just use a data reader?

I don't know if the data table is the problem.

I know that the reader will work.

And according to your description, then you don't need
the data table.

So it is worth a try.

The only reason I was trying to use a data table is because I am using
a support class library (that I also wrote) that returns data using
just a datatable. I suppose I can enhance it to also support returning
data with a data reader.

What is the problem when you try getting the image with the
current code?

The extracted files are smaller than the JPGs extracted by the VB.NET
version and are not valid JPG files (their content is not the same).

Is the size in the DB correct?

If yes - is the size in the data table correct?

Arne

 

[Joe Cool]

On 31-07-2010 20:50, Joe Cool wrote:
On 31-07-2010 20:39, Joe Cool wrote:
On 31-07-2010 20:29, Joe Cool wrote:
On 31-07-2010 19:36, Joe Cool wrote:
I am cross posting this request for help in both the C#,NET andthe
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) arebeing
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?
Have you tried the obvious: SqlConnection, SqlCommand, SELECT,
ExecuteReader, SqlDataReader og læse kolonnen som byte[] ?
Here is what I am using that works with varbinary(max) datatype
columns.
SqlCommand cmd;
SqlDataReader rdr;
DataTable dataTable;
cmd = new SqlCommand("select imagecolumn, linkcolumn from images",
cn)'
rdr = cmd.ExecuteReader();
dataTable.Load(rdr);
Drop the data table and try:
while(rdr.Read())
{
        byte[] image = (byte[])rdr[0];
        string link = (string)rdr[1];
        // process image and link
}
Umm, so are you saying that while the datatable works with a
varbinary(max) datatype, for an image datatype I should drop the
datatable and just use a data reader?
I don't know if the data table is the problem.
I know that the reader will work.
And according to your description, then you don't need
the data table.
So it is worth a try.
The only reason I was trying to use a data table is because I am using
a support class library (that I also wrote) that returns data using
just a datatable. I suppose I can enhance it to also support returning
data with a data reader.
What is the problem when you try getting the image with the
current code?

The extracted files are smaller than the JPGs extracted by the VB.NET
version and are not valid JPG files (their content is not the same).

Is the size in the DB correct?

If yes - is the size in the data table correct?

In the FileStream object's Write mthod, I am using
((byte[])dataTable.Rows[0]).Length as the number of bytes to write.

I assume thelength is the DB is correct since I am using the same DB
to test the C#.NET program to test with that I originally used the
VB.NET programs to originally extract the JPs with.

 

[Arne Vajhøj]

On 31-07-2010 20:50, Joe Cool wrote:
On 31-07-2010 20:39, Joe Cool wrote:
On 31-07-2010 20:29, Joe Cool wrote:

On 31-07-2010 19:36, Joe Cool wrote:

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?

Have you tried the obvious: SqlConnection, SqlCommand, SELECT,
ExecuteReader, SqlDataReader og læse kolonnen som byte[] ?

Here is what I am using that works with varbinary(max) datatype
columns.

SqlCommand cmd;
SqlDataReader rdr;
DataTable dataTable;

cmd = new SqlCommand("select imagecolumn, linkcolumn from images",
cn)'
rdr = cmd.ExecuteReader();
dataTable.Load(rdr);

Drop the data table and try:

while(rdr.Read())
{
byte[] image = (byte[])rdr[0];
string link = (string)rdr[1];
// process image and link

}

Umm, so are you saying that while the datatable works with a
varbinary(max) datatype, for an image datatype I should drop the
datatable and just use a data reader?

I don't know if the data table is the problem.

I know that the reader will work.

And according to your description, then you don't need
the data table.

So it is worth a try.

The only reason I was trying to use a data table is because I am using
a support class library (that I also wrote) that returns data using
just a datatable. I suppose I can enhance it to also support returning
data with a data reader.

What is the problem when you try getting the image with the
current code?

The extracted files are smaller than the JPGs extracted by the VB.NET
version and are not valid JPG files (their content is not the same).

Is the size in the DB correct?

If yes - is the size in the data table correct?

In the FileStream object's Write mthod, I am using
((byte[])dataTable.Rows[0]).Length as the number of bytes to write.

I assume thelength is the DB is correct since I am using the same DB
to test the C#.NET program to test with that I originally used the
VB.NET programs to originally extract the JPs with.

How does the VB.NET and C# code differ?

Arne

 

[Joe Cool]

On 31-07-2010 21:41, Joe Cool wrote:
On 31-07-2010 20:50, Joe Cool wrote:
On 31-07-2010 20:39, Joe Cool wrote:
On 31-07-2010 20:29, Joe Cool wrote:
On 31-07-2010 19:36, Joe Cool wrote:
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 usefor
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 stilldoes)
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 Isee that
SQL2005, while it still supports image datatype, the TEXTCOPYutility
is nowhere to be found.
Any ideas on how to extract image data types from a SQL2005 database
with C#.NET?
Have you tried the obvious: SqlConnection, SqlCommand, SELECT,
ExecuteReader, SqlDataReader og læse kolonnen som byte[] ?
Here is what I am using that works with varbinary(max) datatype
columns.
SqlCommand cmd;
SqlDataReader rdr;
DataTable dataTable;
cmd = new SqlCommand("select imagecolumn, linkcolumn from images",
cn)'
rdr = cmd.ExecuteReader();
dataTable.Load(rdr);
Drop the data table and try:
while(rdr.Read())
{
         byte[] image = (byte[])rdr[0];
         string link = (string)rdr[1];
         // process image and link
}
Umm, so are you saying that while the datatable works with a
varbinary(max) datatype, for an image datatype I should drop the
datatable and just use a data reader?
I don't know if the data table is the problem.
I know that the reader will work.
And according to your description, then you don't need
the data table.
So it is worth a try.
The only reason I was trying to use a data table is because I am using
a support class library (that I also wrote) that returns data using
just a datatable. I suppose I can enhance it to also support returning
data with a data reader.
What is the problem when you try getting the image with the
current code?
The extracted files are smaller than the JPGs extracted by the VB.NET
version and are not valid JPG files (their content is not the same).
Is the size in the DB correct?
If yes - is the size in the data table correct?

In the FileStream object's Write mthod, I am using
((byte[])dataTable.Rows[0]).Length as the number of bytes to write.

I assume thelength is the DB is correct since I am using the same DB
to test the C#.NET program to test with that I originally used the
VB.NET programs to originally extract the JPs with.

How does the VB.NET and C# code differ?

I already posted the C#.NET code. The VB.NET code (as my original post
mentioned) used the now (apparent) non-existant TEXTCOPY command line
utility.

 

[Arne Vajhøj]

On 31-07-2010 21:41, Joe Cool wrote:

On 31-07-2010 20:50, Joe Cool wrote:
On 31-07-2010 20:39, Joe Cool wrote:
On 31-07-2010 20:29, Joe Cool wrote:

On 31-07-2010 19:36, Joe Cool wrote:

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?

Have you tried the obvious: SqlConnection, SqlCommand, SELECT,
ExecuteReader, SqlDataReader og læse kolonnen som byte[] ?

Here is what I am using that works with varbinary(max) datatype
columns.

SqlCommand cmd;
SqlDataReader rdr;
DataTable dataTable;

cmd = new SqlCommand("select imagecolumn, linkcolumn from images",
cn)'
rdr = cmd.ExecuteReader();
dataTable.Load(rdr);

Drop the data table and try:

while(rdr.Read())
{
byte[] image = (byte[])rdr[0];
string link = (string)rdr[1];
// process image and link

}

Umm, so are you saying that while the datatable works with a
varbinary(max) datatype, for an image datatype I should drop the
datatable and just use a data reader?

I don't know if the data table is the problem.

I know that the reader will work.

And according to your description, then you don't need
the data table.

So it is worth a try.

The only reason I was trying to use a data table is because I am using
a support class library (that I also wrote) that returns data using
just a datatable. I suppose I can enhance it to also support returning
data with a data reader.

What is the problem when you try getting the image with the
current code?

The extracted files are smaller than the JPGs extracted by the VB.NET
version and are not valid JPG files (their content is not the same).

Is the size in the DB correct?

If yes - is the size in the data table correct?

In the FileStream object's Write mthod, I am using
((byte[])dataTable.Rows[0]).Length as the number of bytes to write.

I assume thelength is the DB is correct since I am using the same DB
to test the C#.NET program to test with that I originally used the
VB.NET programs to originally extract the JPs with.

How does the VB.NET and C# code differ?

I already posted the C#.NET code. The VB.NET code (as my original post
mentioned) used the now (apparent) non-existant TEXTCOPY command line
utility.

Can you create a complete example that illustrates the problem.

Basically it should work.

Here is a copy of some old code of mine:

SqlCommand ins = new SqlCommand("INSERT INTO imgtest
VALUES(@id,@img)", con);
ins.Parameters.Add("@id", SqlDbType.Int);
ins.Parameters.Add("@img", SqlDbType.Image);
ins.Parameters["@id"].Value = 1;
ins.Parameters["@img"].Value = imgdata;
ins.ExecuteNonQuery();
....
SqlCommand sel = new SqlCommand("SELECT img FROM imgtest WHERE
id = @id", con);
sel.Parameters.Add("@id", SqlDbType.Int);
sel.Parameters["@id"].Value = 1;
byte[] imgdata2 = (byte[])sel.ExecuteScalar();

Arne

 

[Joe Cool]

On 31-07-2010 21:52, Joe Cool wrote:
On 31-07-2010 21:41, Joe Cool wrote:
On 31-07-2010 20:50, Joe Cool wrote:
On 31-07-2010 20:39, Joe Cool wrote:
On 31-07-2010 20:29, Joe Cool wrote:
On 31-07-2010 19:36, Joe Cool wrote:
I am cross posting this request for help in both the C#,NETand 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 keytable 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. AndI 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 SQL2005database
with C#.NET?
Have you tried the obvious: SqlConnection, SqlCommand, SELECT,
ExecuteReader, SqlDataReader og læse kolonnen som byte[] ?
Here is what I am using that works with varbinary(max) datatype
columns.
SqlCommand cmd;
SqlDataReader rdr;
DataTable dataTable;
cmd = new SqlCommand("select imagecolumn, linkcolumn from images",
cn)'
rdr = cmd.ExecuteReader();
dataTable.Load(rdr);
Drop the data table and try:
while(rdr.Read())
{
          byte[] image = (byte[])rdr[0];
          string link = (string)rdr[1];
          // process image and link
}
Umm, so are you saying that while the datatable works with a
varbinary(max) datatype, for an image datatype I should drop the
datatable and just use a data reader?
I don't know if the data table is the problem.
I know that the reader will work.
And according to your description, then you don't need
the data table.
So it is worth a try.
The only reason I was trying to use a data table is because I am using
a support class library (that I also wrote) that returns data using
just a datatable. I suppose I can enhance it to also support returning
data with a data reader.
What is the problem when you try getting the image with the
current code?
The extracted files are smaller than the JPGs extracted by the VB.NET
version and are not valid JPG files (their content is not the same)..
Is the size in the DB correct?
If yes - is the size in the data table correct?
In the FileStream object's Write mthod, I am using
((byte[])dataTable.Rows[0]).Length as the number of bytes to write..
I assume thelength is the DB is correct since I am using the same DB
to test the C#.NET program to test with that I originally used the
VB.NET programs to originally extract the JPs with.
How does the VB.NET and C# code differ?

I already posted the C#.NET code. The VB.NET code (as my original post
mentioned) used the now (apparent) non-existant TEXTCOPY command line
utility.

Can you create a complete example that illustrates the problem.

Basically it should work.

Here is a copy of some old code of mine:

         SqlCommand ins = new SqlCommand("INSERT INTO imgtest
VALUES(@id,@img)", con);
         ins.Parameters.Add("@id", SqlDbType.Int);
         ins.Parameters.Add("@img", SqlDbType.Image);
         ins.Parameters["@id"].Value = 1;
         ins.Parameters["@img"].Value = imgdata;
         ins.ExecuteNonQuery();
...
         SqlCommand sel = new SqlCommand("SELECT img FROM imgtest WHERE
id = @id", con);
         sel.Parameters.Add("@id", SqlDbType.Int);
         sel.Parameters["@id"].Value = 1;
         byte[] imgdata2 = (byte[])sel.ExecuteScalar();

At present I do not have access to the code that originally loads the
image data, but I assume it is similar to yours.

When I first developed my personal apps that stored images in the
database I used the image datatype and used the ExecuteScaler method
to retrieve them. When I switched to my support class library I
switched to the datatable method I have already described. So we are
at full circle, when I first tried the datatable method to retrieve an
Image datatye type column and had problems.

 

[Erland Sommarskog]

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.

Do you need to connect to the legacy databases?

On which version of SQL Server do they run on?

Note that it is only if you need to connect to them, and if they run on
older versions of SQL Server you need to bother. If they run on SQL 2005,
you can say convert(varbinary(MAX), imgcol) and your done.

--
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
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[Arne Vajhøj]

On 31-07-2010 21:52, Joe Cool wrote:
On 31-07-2010 21:41, Joe Cool wrote:

On 31-07-2010 20:50, Joe Cool wrote:
On 31-07-2010 20:39, Joe Cool wrote:
On 31-07-2010 20:29, Joe Cool wrote:

On 31-07-2010 19:36, Joe Cool wrote:

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?

Have you tried the obvious: SqlConnection, SqlCommand, SELECT,
ExecuteReader, SqlDataReader og læse kolonnen som byte[] ?

Here is what I am using that works with varbinary(max) datatype
columns.

SqlCommand cmd;
SqlDataReader rdr;
DataTable dataTable;

cmd = new SqlCommand("select imagecolumn, linkcolumn from images",
cn)'
rdr = cmd.ExecuteReader();
dataTable.Load(rdr);

Drop the data table and try:

while(rdr.Read())
{
byte[] image = (byte[])rdr[0];
string link = (string)rdr[1];
// process image and link

}

Umm, so are you saying that while the datatable works with a
varbinary(max) datatype, for an image datatype I should drop the
datatable and just use a data reader?

I don't know if the data table is the problem.

I know that the reader will work.

And according to your description, then you don't need
the data table.

So it is worth a try.

The only reason I was trying to use a data table is because I am using
a support class library (that I also wrote) that returns data using
just a datatable. I suppose I can enhance it to also support returning
data with a data reader.

What is the problem when you try getting the image with the
current code?

The extracted files are smaller than the JPGs extracted by the VB.NET
version and are not valid JPG files (their content is not the same).

Is the size in the DB correct?

If yes - is the size in the data table correct?

In the FileStream object's Write mthod, I am using
((byte[])dataTable.Rows[0]).Length as the number of bytes to write.

I assume thelength is the DB is correct since I am using the same DB
to test the C#.NET program to test with that I originally used the
VB.NET programs to originally extract the JPs with.

How does the VB.NET and C# code differ?

I already posted the C#.NET code. The VB.NET code (as my original post
mentioned) used the now (apparent) non-existant TEXTCOPY command line
utility.

Can you create a complete example that illustrates the problem.

Basically it should work.

Here is a copy of some old code of mine:

SqlCommand ins = new SqlCommand("INSERT INTO imgtest
VALUES(@id,@img)", con);
ins.Parameters.Add("@id", SqlDbType.Int);
ins.Parameters.Add("@img", SqlDbType.Image);
ins.Parameters["@id"].Value = 1;
ins.Parameters["@img"].Value = imgdata;
ins.ExecuteNonQuery();
...
SqlCommand sel = new SqlCommand("SELECT img FROM imgtest WHERE
id = @id", con);
sel.Parameters.Add("@id", SqlDbType.Int);
sel.Parameters["@id"].Value = 1;
byte[] imgdata2 = (byte[])sel.ExecuteScalar();

At present I do not have access to the code that originally loads the
image data, but I assume it is similar to yours.

When I first developed my personal apps that stored images in the
database I used the image datatype and used the ExecuteScaler method
to retrieve them. When I switched to my support class library I
switched to the datatable method I have already described. So we are
at full circle, when I first tried the datatable method to retrieve an
Image datatye type column and had problems.

I don't think we will get any further until you start working
with the code and try post some code that demonstrates the
problem.

Arne