e6882c75
XPost: microsoft.public.dotnet.languages.csharp
From: arne@vajhoej.dk
On 31-07-2010 22:31, Joe Cool wrote:
> On Jul 31, 10:22 pm, Arne Vajhøj wrote:
>> On 31-07-2010 22:15, Joe Cool wrote:
>>> On Jul 31, 10:04 pm, Arne Vajhøj wrote:
>>>> On 31-07-2010 21:52, Joe Cool wrote:
>>>>> On Jul 31, 9:46 pm, Arne Vajhøj wrote:
>>>>>> On 31-07-2010 21:41, Joe Cool wrote:
>>
>>>>>>> On Jul 31, 9:21 pm, Arne Vajhøj wrote:
>>>>>>>> On 31-07-2010 20:50, Joe Cool wrote:
>>>>>>>>> On Jul 31, 8:41 pm, Arne Vajhøj wrote:
>>>>>>>>>> On 31-07-2010 20:39, Joe Cool wrote:
>>>>>>>>>>> On Jul 31, 8:37 pm, Arne Vajhøj wrote:
>>>>>>>>>>>> On 31-07-2010 20:29, Joe Cool wrote:
>>
>>>>>>>>>>>>> On Jul 31, 8:02 pm, Arne Vajhøj
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[i][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
--- SoupGate-Win32 v1.05
* Origin: you cannot sedate... all the things you hate (1:229/2)
|
