none
Save images to SQL CE RRS feed

  • Question

  •  I could use some help.  I have been trying to resolve this for weeks via searching the net & forums.  The answer is probably there but I don't understand it.  I am new to visual basic as you will see but I am able to use the Visual Studio 2008 GUI to make programs.  I don't know much code and found that using the GUI and inserting code snippets when needed is fine for me. I'm sure I'll learn code eventually.
    I developed a program using Access 2007 Database with no problems but now I am using SQL Compact Edition 3.5.
    My problem is this: In Access 2007, I used images and pointed the image which was in a folder in the directory.  I switched to SQL CE because I want the images in the Database without their own folder (mostly for security reasons). I have been unable to do this.  What I need is a short program to let me select the image and put it into the appropriate row of the database.
    Tuesday, June 24, 2008 2:56 PM

Answers

  • Hi jliller,

    1. Here is code sample: How to connect to 
    SQL Server Compact database (*.sdf)
       Trackback: 
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2682997&SiteID=1
    Imports System.Data.SqlServerCe  
     
    Public Class Form1  
     
        Private Sub Form1_Load(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles MyBase.Load  
            Dim scon As SqlCeConnection = New SqlCeConnection("Data Source=C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Samples\Northwind.sdf")  
            Dim ceda As SqlCeDataAdapter = New SqlCeDataAdapter("Select * from shippers", scon)  
            Dim ds As New DataSet  
            ceda.Fill(ds, "MyTable")  
            DataGridView1.DataSource = ds.Tables("MyTable").DefaultView  
        End Sub 
     
    End Class 

    connection strings to SQL Server 2005 Compact Edition (*.sdf)

    http://www.connectionstrings.com/?carrier=sqlserver2005ce

    2. Suggestion:

    Storing binary objects (such as image, office document etc.) into database is a little cumbersome when it comes to retrieval. Hence the most commonly accepted way is only store the file path in the database, and to store the documents on a file share.
    Trackback:
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2240217&SiteID=1


    3. You can convert an image object between Image and Byte Array by means of MemoryStream object like this:
    Imports System.IO     
    Public Class Form1     
        
        Private Sub Button1_Click(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles Button1.Click     
            Dim myImage As Image = Image.FromFile("D:\1.jpg")     
        
            'Image to byte[]     
            Dim imgMemoryStream As MemoryStream = New MemoryStream()     
            Dim imgByteArray As Byte() = Nothing    
            myImage.Save(imgMemoryStream, System.Drawing.Imaging.ImageFormat.Jpeg)     
            imgByteArray = imgMemoryStream.GetBuffer()     
        
            'Byte[] to image     
            imgMemoryStream = New IO.MemoryStream(imgByteArray)     
            myImage = Drawing.Image.FromStream(imgMemoryStream)     
        
            'Show it and check if the image is accordant with E:\VBproject\1.jpg.     
            PictureBox1.Image = myImage       
        End Sub    
        
    End Class   


    Thus, if you insist on storing images into SQL Server Compact database, you can try applying above code to your scenario like this: 
    I presume
    D:\myDB.sdf database has a table named UserTable which has the following fields:
    Field Name    Data Type
    Username      VarChar(20)
    Picture            Image

    Then you can store user Martin's picture into database:
    Imports System.Data.SqlServerCe     
        
    Public Class Form1     
        
        Private Sub Form1_Load(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles MyBase.Load     
            'Image to byte[]      
            Dim myImage As Image = Image.FromFile("D:\1.jpg")          
            Dim imgMemoryStream As IO.MemoryStream = New IO.MemoryStream()     
            myImage.Save(imgMemoryStream, System.Drawing.Imaging.ImageFormat.Jpeg)     
            Dim imgByteData As Byte() = imgMemoryStream.GetBuffer()     
            'Store image bytes data into database     
            Dim scon As SqlCeConnection = New SqlCeConnection("Data Source=D:\myDB.sdf")     
            Dim cmd As SqlCeCommand = New SqlCeCommand("Update UserTable Set Picture=@myPicture Where Username='Martin'", scon)     
            cmd.Parameters.Add("@myPicture", SqlDbType.Image).Value = imgByteData  
            ' Or cmd.Parameters.Add("@myPicture", SqlDbType.Binary).Value = imgByteData   
            scon.Open()     
            cmd.ExecuteNonQuery()     
            scon.Close()     
        
        End Sub    
        
    End Class   

    And you can retrieve user Martin's picture from database:

    Imports System.Data.SqlServerCe  
    Imports System.IO  
     
    Public Class Form1  
     
        Private Sub Button1_Click(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles Button1.Click  
            Dim scon As SqlCeConnection = New SqlCeConnection("Data Source=D:\myDB.sdf")  
            Dim cmd As SqlCeCommand = New SqlCeCommand("Select * From UserTable Where Username='Martin'", scon)  
            scon.Open()  
            Dim sdr As SqlCeDataReader = cmd.ExecuteReader  
            Dim imgByteData As Byte() = CType(sdr.Item("Picture"), Byte())  
            Dim imgMemoryStream As New IO.MemoryStream(imgByteData)  
            Dim bitmap As Bitmap = New Bitmap(imgMemoryStream)  
            ' or Dim bitmap As Bitmap = Drawing.Image.FromStream(imgMemoryStream)  
            PictureBox1.Image = bitmap  
    End Sub 
     
    End Class 

    I hope that can help you.

    Best regards,
    Martin Xie
    Wednesday, June 25, 2008 9:32 AM

All replies

  • Hi jliller,

    1. Here is code sample: How to connect to 
    SQL Server Compact database (*.sdf)
       Trackback: 
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2682997&SiteID=1
    Imports System.Data.SqlServerCe  
     
    Public Class Form1  
     
        Private Sub Form1_Load(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles MyBase.Load  
            Dim scon As SqlCeConnection = New SqlCeConnection("Data Source=C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Samples\Northwind.sdf")  
            Dim ceda As SqlCeDataAdapter = New SqlCeDataAdapter("Select * from shippers", scon)  
            Dim ds As New DataSet  
            ceda.Fill(ds, "MyTable")  
            DataGridView1.DataSource = ds.Tables("MyTable").DefaultView  
        End Sub 
     
    End Class 

    connection strings to SQL Server 2005 Compact Edition (*.sdf)

    http://www.connectionstrings.com/?carrier=sqlserver2005ce

    2. Suggestion:

    Storing binary objects (such as image, office document etc.) into database is a little cumbersome when it comes to retrieval. Hence the most commonly accepted way is only store the file path in the database, and to store the documents on a file share.
    Trackback:
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2240217&SiteID=1


    3. You can convert an image object between Image and Byte Array by means of MemoryStream object like this:
    Imports System.IO     
    Public Class Form1     
        
        Private Sub Button1_Click(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles Button1.Click     
            Dim myImage As Image = Image.FromFile("D:\1.jpg")     
        
            'Image to byte[]     
            Dim imgMemoryStream As MemoryStream = New MemoryStream()     
            Dim imgByteArray As Byte() = Nothing    
            myImage.Save(imgMemoryStream, System.Drawing.Imaging.ImageFormat.Jpeg)     
            imgByteArray = imgMemoryStream.GetBuffer()     
        
            'Byte[] to image     
            imgMemoryStream = New IO.MemoryStream(imgByteArray)     
            myImage = Drawing.Image.FromStream(imgMemoryStream)     
        
            'Show it and check if the image is accordant with E:\VBproject\1.jpg.     
            PictureBox1.Image = myImage       
        End Sub    
        
    End Class   


    Thus, if you insist on storing images into SQL Server Compact database, you can try applying above code to your scenario like this: 
    I presume
    D:\myDB.sdf database has a table named UserTable which has the following fields:
    Field Name    Data Type
    Username      VarChar(20)
    Picture            Image

    Then you can store user Martin's picture into database:
    Imports System.Data.SqlServerCe     
        
    Public Class Form1     
        
        Private Sub Form1_Load(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles MyBase.Load     
            'Image to byte[]      
            Dim myImage As Image = Image.FromFile("D:\1.jpg")          
            Dim imgMemoryStream As IO.MemoryStream = New IO.MemoryStream()     
            myImage.Save(imgMemoryStream, System.Drawing.Imaging.ImageFormat.Jpeg)     
            Dim imgByteData As Byte() = imgMemoryStream.GetBuffer()     
            'Store image bytes data into database     
            Dim scon As SqlCeConnection = New SqlCeConnection("Data Source=D:\myDB.sdf")     
            Dim cmd As SqlCeCommand = New SqlCeCommand("Update UserTable Set Picture=@myPicture Where Username='Martin'", scon)     
            cmd.Parameters.Add("@myPicture", SqlDbType.Image).Value = imgByteData  
            ' Or cmd.Parameters.Add("@myPicture", SqlDbType.Binary).Value = imgByteData   
            scon.Open()     
            cmd.ExecuteNonQuery()     
            scon.Close()     
        
        End Sub    
        
    End Class   

    And you can retrieve user Martin's picture from database:

    Imports System.Data.SqlServerCe  
    Imports System.IO  
     
    Public Class Form1  
     
        Private Sub Button1_Click(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles Button1.Click  
            Dim scon As SqlCeConnection = New SqlCeConnection("Data Source=D:\myDB.sdf")  
            Dim cmd As SqlCeCommand = New SqlCeCommand("Select * From UserTable Where Username='Martin'", scon)  
            scon.Open()  
            Dim sdr As SqlCeDataReader = cmd.ExecuteReader  
            Dim imgByteData As Byte() = CType(sdr.Item("Picture"), Byte())  
            Dim imgMemoryStream As New IO.MemoryStream(imgByteData)  
            Dim bitmap As Bitmap = New Bitmap(imgMemoryStream)  
            ' or Dim bitmap As Bitmap = Drawing.Image.FromStream(imgMemoryStream)  
            PictureBox1.Image = bitmap  
    End Sub 
     
    End Class 

    I hope that can help you.

    Best regards,
    Martin Xie
    Wednesday, June 25, 2008 9:32 AM
  •  

    Hi Martin,

    Thank you for your quick response.

    The following is what I’m working on.

     

    Public Class Form1

        Private Sub FaultCodesBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FaultCodesBindingNavigatorSaveItem.Click

            Me.Validate()

            Me.FaultCodesBindingSource.EndEdit()

            Me.TableAdapterManager.UpdateAll(Me.ATCDataSet)

        End Sub

     

        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

            Me.FaultCodesTableAdapter.Fill(Me.ATCDataSet.FaultCodes)

        End Sub

     

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

            With OpenFileDialog1

                .InitialDirectory = "C:\"

                .Filter = "All Files|*.*|Bitmaps|*.bmp|GIFs|*.gif|JPEGs|*.jpg"

                .FilterIndex = 2

            End With

            If OpenFileDialog1.ShowDialog() = DialogResult.OK Then

                With PictureBox1

                    .Image = Image.FromFile(Me.OpenFileDialog1.FileName)

                    .SizeMode = PictureBoxSizeMode.CenterImage

                End With

            End If

            Me.Label1.Text = Me.OpenFileDialog1.FileName.ToString

        End Sub


     'The above code works fine.

     'Below is where I tried your samples


        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

            'Image to byte[]  

            Dim myImage As Image = Image.FromFile(Me.OpenFileDialog1.FileName)

            Dim imgMemoryStream As IO.MemoryStream = New IO.MemoryStream()

            myImage.Save(imgMemoryStream, System.Drawing.Imaging.ImageFormat.Jpeg)

            Dim imgByteData As Byte() = imgMemoryStream.GetBuffer()

            'Store image bytes data into database 

            Dim scon As SqlCeConnection = New SqlCeConnection("Data Source=D:\myDB.sdf")

            Dim cmd As SqlCeCommand = New SqlCeCommand("Update UserTable Set Picture=@myPicture", scon)

            cmd.Parameters.Add("@myPicture", imgByteData)

            scon.Open()

            cmd.ExecuteNonQuery()

            scon.Close()

        End Sub

     

    End Class

     Following your example:

    I created a database-  D:MyDB.sdf

    It has one table named- UserTable

    UserTable has 2 columns- UserName  varchar(10)

                                              - Picture       image

    Trying your sample as above (Button2_Click) cmd.ExecuteNonQuery() causes following error:

    InvalidOperationException was unhandled

    @myPicture : Byte array truncation to a length of 8000.

    If you could give me a few lines of code to store the image selected and converted  to byte[] (Button2_Click)(imgByteData)to my existing ATCDataSet.FaultCodes column Picture

    I would be very grateful.

    Wednesday, June 25, 2008 7:10 PM

  • jliller said:
      
    Dim cmd As SqlCeCommand = New SqlCeCommand("Update UserTable Set Picture=@myPicture", scon) 
     
    cmd.Parameters.Add("@myPicture", imgByteData)

    InvalidOperationException was unhandled
    @myPicture : Byte array truncation to a length of 8000.


    Hi jliller,

    Please change this code:
    cmd.Parameters.Add("@myPicture", imgByteData)
     to:

            cmd.Parameters.Add("@myPicture", SqlDbType.Image).Value = imgByteData

    or:
            cmd.Parameters.Add("@myPicture", SqlDbType.Binary).Value = imgByteData


    I have corrected my previous post.


    1. SqlDbType Enumeration
    http://msdn.microsoft.com/en-us/library/system.data.sqldbtype.aspx

    Member name

    Description

    Binary

    Array of type Byte. A fixed-length stream of binary data ranging between 1 and 8,000 bytes.

    Image

    Array of type Byte. A variable-length stream of binary data ranging from 0 to 2 31 -1 (or 2,147,483,647) bytes.



    2. This similar issue may be helpful to you. Please look through it.
    http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic63638.aspx

    3. If one image is quite large and thus may consume significant system memory when written as a single value, you can consider writing the large image to the database in "chunks".
    http://msdn.microsoft.com/en-us/library/3517w44b.aspx
    Chunking BLOB Updates (ADO.NET)

     
        Public Shared Sub StorePhoto(ByVal fileName As StringByVal pointer As Byte(), ByVal connection As SqlConnection)  
     
            ' The size of the "chunks" of the image.  
            Dim bufferLen As Integer = 128  
            Dim appendToPhoto As SqlCommand = New SqlCommand("UPDATE Employees Set Photo=@Bytes", connection)  
            Dim photoParm As SqlParameter = appendToPhoto.Parameters.Add("@Bytes", SqlDbType.Image, bufferLen)  
       
            '' Read the image in and write it to the database 128 (bufferLen) bytes   
            '' at a time. Tune bufferLen for best performance. Larger values   
            '' write faster, but use more system resources.  
            Dim fs As FileStream = New FileStream(fileName, FileMode.Open, FileAccess.Read)  
            Dim br As BinaryReader = New BinaryReader(fs)  
     
            Dim buffer() As Byte = br.ReadBytes(bufferLen)  
     
            Do While buffer.Length > 0  
                photoParm.Value = buffer  
                appendToPhoto.ExecuteNonQuery()  
                buffer = br.ReadBytes(bufferLen)  
            Loop 
     
            br.Close()  
            fs.Close()  
        End Sub 



    Best regards,
    Martin Xie

    Thursday, June 26, 2008 5:07 AM
  •  Hi Martin
    I want to thank you for trying to help.
    I still cannot save the picture into my current database.  I don't need the "MyDb database" but I can't make it work with that either.
    I spent the rest of the day searching for what I need to no avail.
    I believe that I'll just go back to using Access.
    Again, thanks for responding anyway.
    Thursday, June 26, 2008 3:25 PM
  • Thanks Martin.It's realy helpfull for me.i can save an image by ur code. but when i retrive that image it gives an error message.it endicate error on the below line

    Dim imgByteData As Byte() = CType(sdr.Item("picture"), Byte())

    And the Error message is below

    Invalid attempt to read when no data is present.
    plz help me
    Monday, August 4, 2008 9:40 AM
  • thanks, it helped me.
    Sunday, June 21, 2009 6:32 PM
  •  

     

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

            Dim connString As String = "Data Source=D:\myDB.sdf;"
            Dim conn As SqlCeConnection = New SqlCeConnection(connString)
            conn.Open()
            Dim cmd As SqlCeCommand = conn.CreateCommand()
            cmd.CommandText = "Select * From UserTable Where Username='Martin'"

            Dim loca As SqlCeDataReader
            loca = cmd.ExecuteReader
            Dim PictureCol As Integer = 1 ' the column # of the BLOB field
            loca.Read()

            Dim b(loca.GetBytes(PictureCol, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte
            loca.GetBytes(PictureCol, 0, b, 0, b.Length)

            loca.Close()
            conn.Close()

            Dim ms As New System.IO.MemoryStream(b)
            Dim objImage As Bitmap = System.Drawing.Image.FromStream(ms)
            Me.PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
            Me.PictureBox1.Image = objImage



        End Sub

    • Edited by Hyddharam Saturday, September 10, 2011 6:03 AM
    Saturday, September 10, 2011 6:03 AM
  • Hi there, I'm hoping Martin Xie picks this up as his answer helped me about 3 years ago.

    The topic is about saving and loading images to and from database's

    I used his code like this to save a photo image to a database with columns like this

    ID integer, description varchar(100), PhotoImage (image)

     

     

        Private Sub CmdSave_Click(sender As System.Object, e As System.EventArgs) Handles CmdSave.Click
            'Image to byte[]      
            Dim myImage As Image = Image.FromFile(OpenFileDialog1.FileName)
            Dim imgMemoryStream As IO.MemoryStream = New IO.MemoryStream()
            myImage.Save(imgMemoryStream, System.Drawing.Imaging.ImageFormat.Jpeg)
            Dim imgByteData As Byte() = imgMemoryStream.GetBuffer()
    
            Try
                'Store image bytes data into database     
                'Drc is used here for demo only cos the absolute path is to long
                Dim scon As SqlCeConnection = New SqlCeConnection(Drc)
                Dim cmd As SqlCeCommand = New SqlCeCommand("Update photoTable Set photoimage=@photoimage Where id=4", scon)
    
                cmd.Parameters.Add("@photoimage", SqlDbType.Image).Value = imgByteData
                ' or cmd.Parameters.Add("@photoimage", SqlDbType.Binary).Value = imgByteData
    
                scon.Open()
                cmd.ExecuteNonQuery()
                scon.Close()
                MsgBox("saved")
            Catch
                MsgBox("Not Saved")
    
            End Try
    
    
        End Sub
    
    

    I'm pretty sure it works because I can see a small piece of the image when i populate a datagrid also the sequal qry shows binary data for the column.

    However when I try to retrieve the image from the DB I get an exeption System.InvalidOperationException was unhandled
      "Message=No data exists for the row/column."

    the code is below

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    
            Dim scon As SqlCeConnection = New SqlCeConnection(Drc)
            Dim cmd As SqlCeCommand = New SqlCeCommand("Select * From phototable Where id=4", scon)
            scon.Open()
            Dim sdr As SqlCeDataReader = cmd.ExecuteReader
            Dim imgByteData As Byte() = CType(sdr.Item("photoimage"), Byte()) 'exeption at this line 
            Dim imgMemoryStream As New IO.MemoryStream(imgByteData)
            Dim bitmap As Bitmap = New Bitmap(imgMemoryStream)
            ' or Dim bitmap As Bitmap = Drawing.Image.FromStream(imgMemoryStream)  
            PictureBox1.Image = bitmap
        End Sub
    End Class
    


    thank you in advance for any help

     

    regards

    Paul


    paul
    Tuesday, October 4, 2011 4:36 AM
  • hello there, I seem to have to same problem on the same line as you. Did you manage to resolve your problems?

    If you did can you share your experience.

     

    thanks

    paul


    paul
    Thursday, October 6, 2011 3:33 AM
  • hello there, I seem to have to same problem on the same line as you. Did you manage to resolve your problems?

    If you did can you share your experience.

     

    thanks

    paul


    paul


    Paul,

    Start a NEW question and I promise you'll have answers to your query. It's not that difficult but until you've done it, it can be daunting to try to figure out.

    Thursday, October 6, 2011 3:39 AM