Prev: Simple (?) SQL problem
Next: SQL server messed up after installing Foxpro 9
From: Arne Vajhøj on 31 Jul 2010 20:39
On 31-07-2010 20:36, Joe Cool wrote:
> On Jul 31, 8:29 pm, Joe Cool<joecool1...(a)live.com> wrote:
>> On Jul 31, 8:02 pm, Arne Vajh�j<a...(a)vajhoej.dk> 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);
>
> 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

From: Joe Cool on 31 Jul 2010 20:39
On Jul 31, 8:37 pm, Arne Vajhøj <a...(a)vajhoej.dk> wrote:
> On 31-07-2010 20:29, Joe Cool wrote:
>
>
>
>
>
> > On Jul 31, 8:02 pm, Arne Vajhøj<a...(a)vajhoej.dk>  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?
From: Arne Vajhøj on 31 Jul 2010 20:41
On 31-07-2010 20:39, Joe Cool wrote:
> On Jul 31, 8:37 pm, Arne Vajh�j<a...(a)vajhoej.dk> wrote:
>> On 31-07-2010 20:29, Joe Cool wrote:
>>
>>
>>
>>
>>
>>> On Jul 31, 8:02 pm, Arne Vajh�j<a...(a)vajhoej.dk> 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.

Arne
From: Joe Cool on 31 Jul 2010 20:48
On Jul 31, 8:39 pm, Arne Vajhøj <a...(a)vajhoej.dk> wrote:
> On 31-07-2010 20:36, Joe Cool wrote:
>
>
>
>
>
> > On Jul 31, 8:29 pm, Joe Cool<joecool1...(a)live.com>  wrote:
> >> On Jul 31, 8:02 pm, Arne Vajhøj<a...(a)vajhoej.dk>  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);
>
> > 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.
From: Joe Cool on 31 Jul 2010 20:50
On Jul 31, 8:41 pm, Arne Vajhøj <a...(a)vajhoej.dk> wrote:
> On 31-07-2010 20:39, Joe Cool wrote:
>
>
>
>
>
> > On Jul 31, 8:37 pm, Arne Vajhøj<a...(a)vajhoej.dk>  wrote:
> >> On 31-07-2010 20:29, Joe Cool wrote:
>
> >>> On Jul 31, 8:02 pm, Arne Vajhøj<a...(a)vajhoej.dk>    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.
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Simple (?) SQL problem
Next: SQL server messed up after installing Foxpro 9