T-SQL Help : import file into IMAGE field in SQL Server 2000

if i get time later i will try and look up some code i have that works with sql 2005.

should work with 2000 also i think... in my case it was photos but i have an asp.net "hanlder" and a class that put binary /jpeg type files into a table.

all i recall at the moment was that i used a memory stream to create the insertion data, i think there is a sql / data method that takes a stream as an argument for the insert/update statements.

i don not recall if i did this raw or base64 right now....

hah the code is right in front of me, it's also in a current project....

so i have a table that has a column named bytes of type image.

in sql there is an update statement that looks plain ordnary update xxx set bytes = @paramname

where ....

in .net i take the data from a stream or from a file and use a memory stream.

like this:

public static byte[] FileToBytes(string FileName)

{ 

FileInfo f = new FileInfo(FileName); 

FileStream stream = f.OpenRead(); 

byte[] buffer = new byte[stream.Length]; 

stream.Read(buffer, 0, (int)stream.Length); 

stream.Close(); 

stream.Dispose();

 stream = null; 

f = null; 

return buffer; 

}

the byte array then becomes a param to a sql statement.

reverse the logic to pull it back out.

i suspect that .net handles some details of how this data gets tranfered due to the use of parameters

the c# param is just a simple:

now to see if this can be read.. after c9 mangels it.

 

using (SqlCommand command = new SqlCommand(strProcedureName, connection)) {

 command.CommandType = CommandType.StoredProcedure; 

...

command.Parameters.Add(new SqlParameter("@Bytes", Bytes)); 

command.Parameters.Add(new SqlParameter("@MimeType", MimeType)); 

connection.Open(); 

command.ExecuteNonQuery();

 }

code insertion here is hosed i think....  i used the [insertcode] button and it trashed it.

Dr. Herbie: then use the code to do a test and attach a sql trace and see what .net is sending to the sql server.

copy that sql and use it.

are you trying to read the file from tsql ? that might be a problem .... not sure with sql 2000 if it has the right xtd procs or if you might need to make a dll to read the file and register it with the server to do that bit...

not sure your going to be faster, i could have my code working in less than a day I know that for sure.... i have used versions of it several times....

but if you can't get the file in from sql right now why spend more time when a few lines of .net get it done w/o the hassles ??

use the right tool for the job.... imho the job is to make it work not to write a whole bunch of low level plumbing.

Yeah good catch, at the time i probably missed that option.... i was doing a bunch of things at the time that was wrote about 2 years back. i had to take images from a .net upload and scale them, store them , serve them back to the page etc...

so i have a copule of classes doing a number of things with image data from streams, from files to streams, to the sql db and so on...

take ing a 4meg image an making thumbnails and web-display versions in sm,med,large and orignal size.

so it was not just storing from a file that was in that code...