Adding images to SQL server


Method for quickly adding images to SQL server database

I was struggling hard to add images to a database, one option was presented as a code snippet by Mr. Karthik in dotnetspider. It was using Memorystream. A newer method is to use ReadAllBytes.

let us say we have a database field which is used to store image as of type Varbinary(MAX).

Now we ask the user to use file dialog box to select an image, which is then populated in a picture box control, which was dragged from a Table data source. Then the file is opened using 'Readallbytes' and pushed to the database. We can also drag a datagridview by dragging from the same Table data source.


Dim SomBytAry() As Byte ' this variable holds the image data

'ImageTable is the name of the SQL server table
' which contains two fields slno as primary key and imagename as varbinary

'ImageDataSet is the name of the Data set

SlNoCnt = ImageDataSet.ImageTable.Rows.Count + 1
SlnoTextBox.Text = SlNoCnt

OpenFileDialog1.InitialDirectory = "C:\Users\Administrator\Pictures"
OpenFileDialog1.ShowDialog()
ImageNamePictureBox.ImageLocation = OpenFileDialog1.FileName
' to update the image box

SomBytAry = my.Computer.FileSystem.ReadAllBytes(OpenFileDialog1.FileName)
ImageDataSet.ImageTable.AddImageTableRow(SlNoCnt, SomBytAry)

Me.Validate()
Me.ImageTableBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.ImageDataSet)
Me.ImageTableTableAdapter.Fill(Me.ImageDataSet.ImageTable)


The advantage of this method is it takes just fraction of second to update the database because of two things, one is My.computer.filesystem which is faster than fileio.filesystem and Readallbytes is faster than memory stream.

In case users disagree with me, please let me know. Thanks


Comments

Author: srinivas kalagara11 Jun 2010 Member Level: Silver   Points : 2

Hi, first of all you required to upload the image/word doc or what ever file type to sql server.And you can use FileUpload control from toolbox.
Here I'm providing a brief code snippet in performing this task.

UPLOADING
------------------------------------------
Dim myFile As HttpPostedFile
myFile = txtUploadLetter.PostedFile
If Not (txtUploadLetter.FileName = "") Then
sFileName = txtUploadLetter.PostedFile.FileName
fileSize = txtUploadLetter.PostedFile.ContentLength
sFileExtension = Path.GetExtension(txtUploadLetter.PostedFile.FileName).ToLower()
len = txtUploadLetter.PostedFile.ContentLength
If (len / 1024 > 30720) Then
ClientScript.RegisterClientScriptBlock(Me.GetType, "FileSize Exceeded", "")
Exit Sub
End If
contentType = txtUploadLetter.PostedFile.ContentType
fs = txtUploadLetter.PostedFile.InputStream
fileBinaryData = New Byte(fileSize) {}
n = fs.Read(fileBinaryData, 0, fileSize)
Select Case sFileExtension
Case ".gif"
strMimeType = "image/gif"
Case ".jpg", ".jpeg", ".jpe"
strMimeType = "image/jpeg"
Case ".png"
strMimeType = "image/png"
Case ".excel"
strMimeType = "application/vnd.ms-excel"
Case ".doc"
strMimeType = "application/msword"
Case ".docx"
strMimeType = "application/vnd.openxmlformats-officedocument.wordprocessingml.document"
Case ".txt"
strMimeType = "application/txt"
Case ".pdf"
strMimeType = "application/pdf"
Case Else
strMimeType = "text/plain"

End Select
End If

Dim sqlCommand As String = "proc_InsCaseTasksActivityByCaseID"
Dim dbCommand As DbCommand = db.GetStoredProcCommand(sqlCommand)
Dim iReturn As Integer
Try
db.AddInParameter(dbCommand, "@MimeType", DbType.String, contentType)
db.AddInParameter(dbCommand, "@FileExtension", DbType.String, sFileExtension)
db.AddInParameter(dbCommand, "@pbtDocumentsUploaded", DbType.Binary, pFileBinaryData)
iReturn = db.ExecuteNonQuery(dbCommand)


Catch oExc As Exception
Dim rethrow As Boolean = ExceptionPolicy.HandleException(oExc, "BLPolicy")
oBLExcep = oExc.Message
If (rethrow) Then
Throw oExc
End If
End Try
End Sub
-----------------------------------------------
Database Table format
----------------------------
FileName char(50)
Document varBinary(Max)
MimeType varchar(200)
FileExtension nvarchar(10)

Write an SP to perform Insert Operation

----------------------------------------------
Retrieving the Saved Doc from Sql server
------------------------------------------

Dim oRedr As System.Data.IDataReader
oRedr = oNew.GetDocumentfromsqlserver() ''Call Business layer to interact with DB layer inorder to execute and fetch data from table using "Select" statement
If oRedr.Read Then
With Page.Response
.Clear()
.ClearHeaders()
.ClearContent()
.ContentType = CType(oRedr.Item("MimeType"), String)
Response.AddHeader("Content-Disposition", "attachment; filename=" + oRedr.Item("FileName").ToString.Trim + oRedr.Item("FileExtension").ToString.Trim)
.OutputStream.Write(CType(oRedr.Item("Document"), Byte()), 0, CType(oRedr.Item("Document"), Byte()).Length)
.End()
End With
End If
oRedr.Close()



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: