Images in SQL Server

I'm architecting an application that is going to contain large amounts of
image files and display them on the web, with role-based security for the
images. From everything that I understand is that this should be done by
storing the image on the filesystem and links to that data in a database
table instead of storing these images to the database table. Does anyone
here have experience with this, and can give me some advice as to if this is
the correct direction I should go?
Thanks,
Jason WalravenCheck out: http://www.aspfaq.com/2149
Anith|||Thanks
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23PaWXNzLFHA.4028@.tk2msftngp13.phx.gbl...
> Check out: http://www.aspfaq.com/2149
> --
> Anith
>|||I don't quite share the same opinion as the author of this article.
Images in database with SQL 2000 and a proper designed file group system are
great. We use it and would never go to the nightmare of reconciling file
system with database.
You backup a database, you have everything your app needs, even if you send
it to China.
Thanks
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23PaWXNzLFHA.4028@.tk2msftngp13.phx.gbl...
> Check out: http://www.aspfaq.com/2149
> --
> Anith
>|||>> I don't quite share the same opinion as the author of this article.
Well, the article is pretty reasonable with generic arguments for both sides
with a few pointers to different issues. Performance requirements are often
at odds with ease of maintenance. Readers can always choose implement
solutions depending on their specific business needs.
Anith

Image Storing In SQL Server 2000

HI,

Kindly Guide me how to store picture files in SQL server 2000 using Visual studio 2005

In your database design, make space for a field of type text.

When you work with the images, serialize them into a string and insert the string into the DB. When you retrieve the string, you have to convert it from string to an image.

Have a look at those two methods:

public static string ImageToString(System.Drawing.Image img) { MemoryStream ms =new MemoryStream(); img.Save(ms, img.RawFormat);byte[] array = ms.ToArray();return Convert.ToBase64String(array); }public static System.Drawing.Image StringToImage(string imageString) {if (imageString ==null)throw new ArgumentNullException("imageString");byte[] array = Convert.FromBase64String(imageString); System.Drawing.Image img = System.Drawing.Image.FromStream(new MemoryStream(array));return img; }

|||

Public Shared Function ImageToString(ByVal imgAs System.Drawing.Image)As String Dim msAs MemoryStream =New MemoryStream() img.Save(ms, img.RawFormat)Dim array()As Byte = ms.ToArray()Return Convert.ToBase64String(array)End Function Public Shared Function StringToImage(ByVal imageStringAs String)As System.Drawing.ImageIf imageStringIs Nothing Then Throw New ArgumentNullException("imageString")End If Dim array()As Byte = Convert.FromBase64String(imageString)Dim imgAs System.Drawing.Image = System.Drawing.Image.FromStream(New MemoryStream(array))Return imgEnd Function

In VB.NET this should do the trick:

I found this language conversion tool, in case you need it for later use:http://www.dotnetspider.com/convert/

Please mark my post as anwer if it solves your issue.

|||

Thanks

Reagrds

Fakhruddin

Image size

I have a SQL database that stores image files (pdf, doc, tif etc). Is it
possible to write a query that shows the size of the file being stored ?
Any help would be great.
Thanks
SiYou can get the bytes used with DATALENGTH -
SELECT DATALENGTH(YourImageColumn)
FROM YourTable
You can find more information on DATALENGTH in Books Online.
-Sue
On Mon, 31 Jul 2006 08:36:01 -0700, Simon
<Simon@.discussions.microsoft.com> wrote:
>I have a SQL database that stores image files (pdf, doc, tif etc). Is it
>possible to write a query that shows the size of the file being stored ?
>Any help would be great.
>Thanks
>Si

Image size

I have a SQL database that stores image files (pdf, doc, tif etc). Is it
possible to write a query that shows the size of the file being stored ?
Any help would be great.
Thanks
SiYou can get the bytes used with DATALENGTH -
SELECT DATALENGTH(YourImageColumn)
FROM YourTable
You can find more information on DATALENGTH in Books Online.
-Sue
On Mon, 31 Jul 2006 08:36:01 -0700, Simon
<Simon@.discussions.microsoft.com> wrote:

>I have a SQL database that stores image files (pdf, doc, tif etc). Is it
>possible to write a query that shows the size of the file being stored ?
>Any help would be great.
>Thanks
>Si

Image files and SQL Server

Hello,
What's the best way to store files of an arbitrary size in a SQL
Server / MSDE database? Some files will be text, but most should be
binary (images, audio, and video). I'd like to be able to store all
the files directly in the database so that backing everything up is a
single operation (I can do this, right?). Data retrieval speed is not
overly critical, as the code in question will be running on the same
machine as the database and will only have a single instance of the
client application. Any advice and/or hyperlinks to whitepapers on the
subject would be appreciated.

Thanks,
Will Gant
foehammer@.hotmail.comHi

Check out:
http://www.aspfaq.com/show.asp?id=2149

http://msdn.microsoft.com/library/d...amples_2isp.asp
http://msdn.microsoft.com/library/d...asp?frame=true

John

"Foehammer" <foehammer@.hotmail.com> wrote in message
news:27d68359.0407051151.2adb94ea@.posting.google.c om...
> Hello,
> What's the best way to store files of an arbitrary size in a SQL
> Server / MSDE database? Some files will be text, but most should be
> binary (images, audio, and video). I'd like to be able to store all
> the files directly in the database so that backing everything up is a
> single operation (I can do this, right?). Data retrieval speed is not
> overly critical, as the code in question will be running on the same
> machine as the database and will only have a single instance of the
> client application. Any advice and/or hyperlinks to whitepapers on the
> subject would be appreciated.
> Thanks,
> Will Gant
> foehammer@.hotmail.com|||Well,
I guess the 2 gig limit on MSDE file size is going to be a bit of a
problem. It'll add some complications, but I think I've come up with a
slightly smarter way to do this than in my orginal design.

Thanks,
Will Gant
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<VQiGc.303$UV3.4418796@.news-text.cableinet.net>...
> Hi
> Check out:
> http://www.aspfaq.com/show.asp?id=2149
> http://msdn.microsoft.com/library/d...amples_2isp.asp
> http://msdn.microsoft.com/library/d...asp?frame=true
> John
> "Foehammer" <foehammer@.hotmail.com> wrote in message
> news:27d68359.0407051151.2adb94ea@.posting.google.c om...
> > Hello,
> > What's the best way to store files of an arbitrary size in a SQL
> > Server / MSDE database? Some files will be text, but most should be
> > binary (images, audio, and video). I'd like to be able to store all
> > the files directly in the database so that backing everything up is a
> > single operation (I can do this, right?). Data retrieval speed is not
> > overly critical, as the code in question will be running on the same
> > machine as the database and will only have a single instance of the
> > client application. Any advice and/or hyperlinks to whitepapers on the
> > subject would be appreciated.
> > Thanks,
> > Will Gant
> > foehammer@.hotmail.com

image files

I've uploaded a gif file to RS using Reports Manager, but when I go to view
the image in RS/RM, it does not display. Only a red X displays.
I'm using RS 2000 with Form based authentication.
Please Help!!!!On Mar 13, 2:56 pm, Nikki <N...@.discussions.microsoft.com> wrote:
> I've uploaded a gif file to RS using Reports Manager, but when I go to view
> the image in RS/RM, it does not display. Only a red X displays.
> I'm using RS 2000 with Form based authentication.
> Please Help!!!!
I would suggest embedding the image in the report itself. Hope this
helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||how do I embed an image?
"EMartinez" wrote:
> On Mar 13, 2:56 pm, Nikki <N...@.discussions.microsoft.com> wrote:
> > I've uploaded a gif file to RS using Reports Manager, but when I go to view
> > the image in RS/RM, it does not display. Only a red X displays.
> >
> > I'm using RS 2000 with Form based authentication.
> >
> > Please Help!!!!
> I would suggest embedding the image in the report itself. Hope this
> helps.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>

Image Column Data and Backup

I have a table (SQL Server 2000) that stores icon files in an Image
field. I have no problem storing and retrieving the images on my system.
When I back up the database however the rows in that table do not seem
to get backed up. In other words when I restore the database on another
server there are no rows in that one table.
I have used the sp_tableoption to turn "in row text" on for the table
etc. Anyone have a clue what I might be doing wrong? This table will
eventually need to be recreated at a customer site with the data intact
and I was planning on just backing the database up and then restoring it.
Bob PorterA restored database will be exactly like the original, less uncommitted
transactions. A common cause of the problem you describe is that multiple
backups exist in the same backup file and the first (oldest) is restored by
default. You can list the backup file contents with RESTORE HEADERONLY:
RESTORE HEADERONLY
FROM DISK='C:\Backups\MyDatbase.bak'
You can then specify the desired backup with the FILE specification:
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatbase.bak'
FILE=2
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Porter" <rhysliam@.noemail.nospam> wrote in message
news:ud2nToCbFHA.2128@.TK2MSFTNGP15.phx.gbl...
>I have a table (SQL Server 2000) that stores icon files in an Image field.
>I have no problem storing and retrieving the images on my system. When I
>back up the database however the rows in that table do not seem to get
>backed up. In other words when I restore the database on another server
>there are no rows in that one table.
> I have used the sp_tableoption to turn "in row text" on for the table etc.
> Anyone have a clue what I might be doing wrong? This table will eventually
> need to be recreated at a customer site with the data intact and I was
> planning on just backing the database up and then restoring it.
> Bob Porter|||Dan Guzman wrote:
> A restored database will be exactly like the original, less uncommitted
> transactions. A common cause of the problem you describe is that multiple
> backups exist in the same backup file and the first (oldest) is restored b
y
> default. You can list the backup file contents with RESTORE HEADERONLY:
> RESTORE HEADERONLY
> FROM DISK='C:\Backups\MyDatbase.bak'
> You can then specify the desired backup with the FILE specification:
> RESTORE DATABASE MyDatabase
> FROM DISK='C:\Backups\MyDatbase.bak'
> FILE=2
>
It did! Thanks, sorry for the newbie question I knew better... sigh...|||No problem. You're certainly not the first to run into this :-)
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Porter" <rhysliam@.noemail.nospam> wrote in message
news:%23J0MYlDbFHA.464@.TK2MSFTNGP15.phx.gbl...
> Dan Guzman wrote:
> It did! Thanks, sorry for the newbie question I knew better... sigh...