Storing Picture in MS-SQLServer

[zqtqzq]

how do i store pictures in sqlserver database? i mean storing the picture in the database not storing picture pathname in the database?

 

[RiverGuy]

Use Image datatype in the DB. Convert your picture to a byte array in VB. There are numerous tutorials on the web for this.

 

[zqtqzq]

pls how can i Convert picture to a byte array in VB?
help me with sample code or site reference

 

[chrissie1]

http://www.google.com

or

http://www.vbcity.com/forums/faq.asp?fid=44&cat=Database&#TID74665

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]

 

[RustyAfro]

zqtqzq (interesting name). I feel your pain, this took a long time for me to figure out (2 weeks). Here is a module I made. It has a function that convert a file to an array, a function that uploads the file to the server, and a function that takes a file already on the server and converts it back to it's original format.

I do not have enough time to write out a full tutorial, but I plan on it in the near future. Hopefully this code may help you get on the right path.

What I can tell you is that the "GetBlob" function is the most important to you. It allows you to convert a file into an array that a field on the SQL server will accept if it is of the type "Image". You can use what GetBlob() returns to pass into the SQL server field that would hold the file.

Imports System.Data.SqlClient
Imports System.IO
Module ModFiles
    
    Function GetBLOB(ByVal Path As String) As Array
        'Path: The path the file should be uploaded from, ie "C:\autoexec.bat"

        Try
            'TheFile is a FileStream that streams the file from it's original path
            Dim TheFile As New FileStream(Path, FileMode.OpenOrCreate, FileAccess.Read)
            'FileData is the data in the file.
            Dim FileData(CInt(TheFile.Length)) As Byte

            'Read the Data in the file
            TheFile.Read(FileData, 0, CInt(TheFile.Length))
            'Close the TheFile object
            TheFile.Close()

            'Return the file as a Blob to the calling procedure
            GetBLOB = FileData
        Catch ex As Exception
            MessageBox.Show("Image Conversion Failed!" & ControlChars.CrLf & ex.ToString, "File conversion", MessageBoxButtons.AbortRetryIgnore, MessageBoxIcon.Error)
        Finally

        End Try

    End Function

    Function UpdateBlob_TblLetters(ByVal TheBlob As Array, ByVal Path As String, ByVal LetterID As String) As Boolean

        Dim cnn As New SqlConnection(SQL_Connection)
        Dim cmd As New SqlCommand

        'Try to update the record
        Try
            With cmd
                .Connection = cnn
                .CommandText = "modFiles_UpdateBlobLetterID_Upd"
                .CommandType = CommandType.StoredProcedure

                'Pass the LetterID that the template should be updated for.
                .Parameters.Add("@LetterID", SqlDbType.VarChar, 6)
                .Parameters("@LetterID").Direction = ParameterDirection.Input
                .Parameters("@LetterID").Value = LetterID

                'Pass the BLOB (file) object
                .Parameters.Add("@TheBlob", SqlDbType.Image)
                .Parameters("@TheBlob").Direction = ParameterDirection.Input
                .Parameters("@TheBlob").Value = TheBlob
            End With
            cnn.Open()
            'Perform the update on the server
            cmd.ExecuteNonQuery()
            cnn.Close()
            MessageBox.Show("Successfully added the file to the template")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Function

    Function ExportBlob_TblLetters(ByVal Path As String, ByVal LetterID As String) As Boolean

        Dim cnn As New SqlConnection(SQL_Connection)
        Dim cmd As New SqlCommand
        Dim da As New SqlDataAdapter
        'Dim da As New SqlDataAdapter("Select * From tbl_Letters Where LetterID = '" & LetterID & "'", cnn)
        Dim MyCB As SqlCommandBuilder = New SqlCommandBuilder(da)
        Dim ds As New DataSet

        With cmd
            .Connection = cnn
            .CommandText = "modFiles_ExportBlobLetterID_Sel"
            .CommandType = CommandType.StoredProcedure

            .Parameters.Add("@LetterID", SqlDbType.VarChar, 6)
            .Parameters("@LetterID").Direction = ParameterDirection.Input
            .Parameters("@LetterID").Value = LetterID
        End With

        da.SelectCommand = cmd
        Try
            cnn.Open()
            da.Fill(ds, "TheFile")

            Dim MyRow As DataRow
            MyRow = ds.Tables("TheFile").Rows(0)

            Dim FileData() As Byte
            FileData = MyRow("Template")

            Dim K As Long
            K = UBound(FileData)

            Dim fs As New FileStream _
             (Path, FileMode.OpenOrCreate, _
              FileAccess.Write)
            fs.Write(FileData, 0, K)
            fs.Close()

            fs = Nothing
            MyCB = Nothing
            ds = Nothing
            da = Nothing

            cnn.Close()
            cnn = Nothing
            MsgBox("Image retrieved")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Function

End Module

 

[RustyAfro]

One thing to note, my code deals with any files. There is code to do this just for actual images as well that is a little easier as I understand it.