Storing a bmp in a 2005 sql database.

[JonnyPoet]

Hi friends!
I'm in need to store pictures of up to 300 dpi resolution in a database. In the moment the pictures are read into the program from a path and loaded into pictureboxes. From here they should be stored into my SQL 2005 server database. I have used 'image' as the dataformat in my database, but in the DataTable object this seems me to be byte[]
The question is: How to convert from Image class to byte[] and back, how to calculate size of needed bytes for a maybe needed buffer of bytes; also how to reload the picture from database back to application.

Any ideas where to find information about this ? Any codesamples?

[crackersixx]

To store images you can do something like:

byte[] bytes = File.ReadAllBytes(fullPathToImage);

// insert image into table
SqlCommand insert = new SqlCommand("insert into sometable (imagedata) values (@image)", connection);
SqlParameter imageParameter = insert.Parameters.Add("@image", SqlDbType.Binary);
imageParameter.Value = bytes;
imageParameter.Size = bytes.Length;
insert.ExecuteNonQuery();

and to retrieve:

byte[] content;
MemoryStream stream;
Bitmap image;
try
{
     content = (byte[])dsImages.Tables[0].Rows[0]["fieldname"];
     stream = new MemoryStream(content);
     image = new Bitmap(stream);
}

Then do whatever you want with the Bitmap, like putting it in a picbox.

[PaulMdx]

What size image in pixels will you be storing? (DPI is irrelevent unless you're printing the image.)

Generally speaking you don't want to be storing masses of blob data in SQL Server, and BMPs are particularly large.

[nabeelisnabeel]

it is very easy as crackersixx told.

[nabeelisnabeel]

just a side question... you don't know this simple task.. and how did you obtained these so many reputation points.

[JonnyPoet]

What size image in pixels will you be storing? (DPI is irrelevent unless you're printing the image.)

Generally speaking you don't want to be storing masses of blob data in SQL Server, and BMPs are particularly large.

Its pictures of Products, later needed to be shown in the webpage maybe, so I want to hae them on the server. Most of the jpgs are of size 100k that for printing are tiffs with 2.5 MB approx.
Amount of pictures are maybe 1000 of the jpgs and 100 of the printing pics.

Thx to crackersixx the points will follow as soon as possible. It works great.


Storing pathes only in my older (VB and Access mdb system has brought troubles as people moved path or removed pictures without deleting the path and some thing like that.
Is there any problem with having lots of pictures stored in sql server together with data ?

[JonnyPoet]

just a side question... you don't know this simple task.. and how did you obtained these so many reputation points.

Curious hmm.. I'm not working with graphics and all that stuff very much. And most of the rep points are done in old VB. I have changed to C# approx 2 years ago, but still working most of the time in old VB.

I'm basically a selftraining person getting my knowledge autodidactic from books. So whats the shame to ask ' simple questions' ?
IMHO using this forum is and should be a full exchange. You are allowed to ask questions and you should give your knowledge as best as you can. If you look at the amount of my posts and how much are requests in relation tothe amount of answering posts you will easily find the answer of how to get that amount of reputation in all the years I'm there and have fun

I'm sure, there are lots of people there, much more trained and with a real high level of knowledge, but only looking for that sort of stupid question asked here. NOooo forget it. This is the wrong viewpoint.
If you can add this tiny bit of knowledge to another one who needs it. give it ... If you dont know something, or if you dislike to help someone, simple keep reading, reading, reading, but for get it to blame people for asking 'simple' questions. All in the world is simple,... as long as you ar knowing the answer. Knowing the answer needs to be able to learn it.
Being able to learn needs to know where t get your answeres.

Sorry but about pictures I only know that there are different formats like jpg, bmp Tiff ... and they have to do with compression and the order of data in this files
No more, no less. In SQL2005 there is a format named image, but it is obviously nothing else then byte[]. I dont know whats the max size for one single picture allowed in the database or if there are no restrictons.

For coming to an end with it. If you are interested in which way I'm sharing my knowledge, you are invited to read my posts or to simple have a look into my article series where you will find the link in the bottom of my posts. But be careful, they are written for beginners only, so maybe they are too simple for you.

[Arjay]

just a side question... you don't know this simple task.. and how did you obtained these so many reputation points.

JohnnyPoet has obtained his reputation points the same way everyone else gets them - by answering questions correctly well enough that other folks award him points.

At this time he has over 3360 posts, but only has started 122 threads. So less than 4% are originating questions (not that there's anything wrong with asking questions - it's what the site is all about). If you look at the quality of his replies, it's pretty obvious why he has so many points.

As a final comment... Personally I would rather see someone ask a question on a topic rather than acting like they know it.

[PaulMdx]

Its pictures of Products, later needed to be shown in the webpage maybe, so I want to hae them on the server. Most of the jpgs are of size 100k that for printing are tiffs with 2.5 MB approx.
Amount of pictures are maybe 1000 of the jpgs and 100 of the printing pics.

Thx to crackersixx the points will follow as soon as possible. It works great.

Storing pathes only in my older (VB and Access mdb system has brought troubles as people moved path or removed pictures without deleting the path and some thing like that.
Is there any problem with having lots of pictures stored in sql server together with data ?

In general terms a database isn't designed for reading large chunks of data, it's designed for relatively small tuples (rows) linked together. In SQL Server 2008 they use filestreams to get around this by treating files on disk as if they're part of the schema.

That said, 1000 100k images isn't as bad as I thought. I was assuming you might have several thousand 2.5 MB bitmaps being served a lot.

Considerations that you need to make are:

1) What overhead will there be reading photos out of the database when serving pages?

2) How often are the large TIFFs being served?

3) How much with the database grow?

4) How big a problem is it storing paths instead?

[JonnyPoet]

Thx Paul for this very informative answer.

....2) How often are the large TIFFs being served?

Very seldom. They are needed aprrox twice a year when we do new printing promos

3) How much with the database grow?

The number I give is the maximum, which may be needed to be stored along several years. Most of the products are changing very quick and then the tiffs are deleted again as they are no longer needed. The smaller pictures which are only needed for a picture representation in the products administartion program and may have a size of 128x128 pixel will grow during time, because when a product is no longer in stock, I'm not sure if the staff working on the program will not forget to delete the no longer needed pictures. There are other pictures ( service schemas ) which we want to store for the future and they are pretty big stuff as a manual has size of A0 and needs to be scanned in parts of size A4 (This may be some 1000 pictures all together each approx 2.5 MB and this amount will grow in a slow rate every year. ( Max 50 pictures every year added ) But maybe I'm storing them in a total other schema different from all my other data.

4) How big a problem is it storing paths instead?

I had it in pathes all the time but this caused troubles because the staff didn't think that this is referenced by database and changes the location of the files by having 'new (idiotic ) ideas' for a 'better' order system and then you could never find them again, because the links are broken.
Some others added pictures to the folder and then being astonished that the database cannot find them. because they forget to enter the pathes to the database. The database is the solution, they didn't know where they are stored and also didn't know how the database arranges them in its space, so they can only work using the database and search there. If they move or delete them then it is done and all data connected to this pictures are updoated or deleted and there will no longer be invalid links which causes errormessages like ' picture not found' Thats the main reason why I want to have all this documents in one sql server.

[nabeelisnabeel]

I did not object you. I was curious and now I know the answer.