How to insert images or pictures in sql 2005 table

Question

How to insert images or pictures in sql 2005 table

Rick Gzz

SSC Journeyman

Points: 80
More actions
June 26, 2013 at 8:38 am

#144424

I'm working with a database of mechanical components, need to add the pictures (.tiff) of these components, someone could help me with this ...
Nada

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

mayurkb Ten Centuries Points: 1155 More actions · Answer 1

rigonzalez.fuentes (6/26/2013)
I'm working with a database of mechanical components, need to add the pictures (.tiff) of these components, someone could help me with this ...

I'm guessing that the tiff file is less than 10 MB. You can add varbinary column to the table. Convert the image into byte stream and insert it into table.

Lowell SSC Guru Points: 323518 More actions · Answer 2

the absolute best way is via a programming language.

you'd need to first establish some sort of relationship to the data...that is, you know that C:\AutoCad\drawing_424.tif

is related to a specific record in your database.

here's a vb.net example i often post, but you'd need to provide very specific details for us to give you more than a skeleton framework for this kind of issue.

Private Sub BlobFilesToDatabase(ByVal TheFolderPath As String)

'this is a recursive function, that, after processing all the files in the current directory, digs deeper into each subfolder in the current directory.

If Directory.Exists(TheFolderPath) Then

Dim mySqlConnectionFormat As String = "data source={0};initial catalog={1};user id={2};password={3};Trusted_Connection=False;Connect Timeout=600;Workstation ID=GhostInTheMachine;Application Name=HaxxorPadPlusPlus;"

Dim MyConn As New SqlConnection

MyConn.ConnectionString = String.Format(mySqlConnectionFormat, "DEV223", "SandBox", "Noobie", "NotARealPassword")

MyConn.Open()

'assuming a table like CREATE TABLE MyImages(ImageID int IDENTITY(1,1) NOT NULL PRIMARY KEY,ImageFileName varchar(50),BlobData varbinary(max) )

Dim sql As String = "INSERT INTO MyImages VALUES(@ImageFileName,@BlobData)"

Dim MySqlCommand As New SqlCommand(sql, MyConn)

MySqlCommand.Parameters.Add("@ImageFileName", SqlDbType.VarChar)

MySqlCommand.Parameters.Add("@BlobData", SqlDbType.Image)

For Each TheFilename As String In Directory.GetFiles(TheFolderPath)

If TheFilename.EndsWith("jpg", StringComparison.CurrentCulture) OrElse TheFilename.EndsWith("ico", StringComparison.CurrentCulture) Then

If File.Exists(TheFilename) Then

Dim oFile As System.IO.FileInfo

oFile = New System.IO.FileInfo(TheFilename)

Dim oFileStream As System.IO.FileStream = oFile.OpenRead

Dim lBytes As Long = oFileStream.Length

Dim DiskBlob(lBytes - 1) As Byte

' Read the file into a byte array

oFileStream.Read(DiskBlob, 0, lBytes)

MySqlCommand.Parameters(0).Value = oFile.Name

MySqlCommand.Parameters(1).Value = DiskBlob

MySqlCommand.ExecuteNonQuery()

End If

Next

'now process any sub folders.

For Each TheSubDirectory As String In Directory.GetDirectories(TheFolderPath)

BlobFilesToDatabase(TheSubDirectory)

Next

End If

End Sub

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

You could use a SQLCLR to pull a file from any file system available to the SQL Server.

https://nclsqlclrfile.codeplex.com/[/url]

If you have any questions about it let me know..

CEWII

Rick Gzz SSC Journeyman Points: 80 More actions · Answer 4

Most of the tiff files are less than 100 kb, few reach the 700 kb. First use this instruction but insert the data without image

INSERT INTO Diseños (Id, Partnum, Diseno)

SELECT 1, '3301643-06', BulkColumn

FROM Openrowset( Bulk 'C:\3301643.TIFF', Single_Blob) as EmployeePicture

This would show it in vb.net and excel in some cases

Nada

cathyhill345 SSC Enthusiast Points: 118 More actions · Answer 5

Rick Gzz (6/26/2013)
Most of the tiff files[/url] are less than 100 kb, few reach the 700 kb. First use this instruction but insert the data without image[/url]
INSERT INTO Diseños (Id, Partnum, Diseno)
SELECT 1, '3301643-06', BulkColumn
FROM Openrowset( Bulk 'C:\3301643.TIFF', Single_Blob) as EmployeePicture
This would show it in vb.net and excel in some cases

You can see whether this old post help you a little. Sorry for unable to extract some information cuz there is too much information in that post. 😀

http://www.sqlservercentral.com/Forums/Topic364779-9-1.aspx