How to save and pull image data to and from sql server 2000 using a webservice

http://aspalliance.com/articleViewer.aspx?aId=138
http://aspalliance.com/articleViewer.aspx?aId=140

codingbeaver, I've looked at the articles at the links you provided before I posted my question.  But these examples use a direct access to the database and not through a webservice.

Sorry.

Your web service is not going to display the image. It is the web page who calls your web service to display the image by using the return DataSet from your web service.

For the web page, you can add an Image control and set its ImageUrl="readImage.aspx?id=xxx". Then readImage.aspx will pass id to your web service to retrieve data, then use Response.Write to output the image data. Here is an example of readImage.aspx

Protected Sub Page_Load(...,...) Handles Me.Load

	Try

		Dim ds As New DataSet
		Dim arrContent As Byte()

		'Call your web service to fill the DataSet
                  'with the id in the query string

		dr = ds.Tables(0).Rows(0)

		arrContent = CType(dr.Item("imgData"), Byte()) 'Change the column name to match yours

		Dim conType As String = dr.Item("imgType").ToString()

		Response.ContentType = conType

		Response.OutputStream.Write(arrContent, 0, dr.Item("imgLength"))

		Response.End()

	Catch ex As Exception
		'Handle exception error here.
	End Try

End Sub

Select allOpen in new window

Thank you codingbeaver,

I've seen that example and have tried it with no success although the one I used had "response.binarywrite" instead of your suggestion of "response.outputstream.write".  I may try it in the morning when I am back in the office as it would definitely be better than my workaround.

The work-around I've written already for pulling back the image from the database and then being able to pull it up correctly in the browser.  It's a bit convoluted but it works for me.  Basically now when the webservice returns the dataset with the image I convert it into an xml doc then then read the image node of that doc into a filestream and save the image as a file on my webserver.  Then instead of trying response.write a image I simply write a href link to the file on the server.  When I click on the link the image comes up fine.

I've attached the code snippet for the function and the sub that does this for me now.

I'll try your suggestion in the morning and let you know how it does.

Any ideas on the second part of my issue of writing an image to the database via a webservice?

Private Shared Function readFromXml(ByVal filename As String) As Byte()
        Try
            Dim r As XmlTextReader = New XmlTextReader(filename)

            While r.Read()
                If "theimage" = r.Name Then
                    Exit While
                End If
            End While

            Dim imagefile() As Byte = New Byte((5000000) - 1) {}
            Dim totalbytes As Integer = r.ReadBase64(imagefile, 0, 5000000)
            Dim croppedImage() As Byte = New Byte((totalbytes) - 1) {}
            Array.Copy(imagefile, croppedImage, totalbytes)
            r.Close()
            Return croppedImage
        Catch ex As Exception

        End Try
    End Function

    Public Sub loadFromXmlAndSaveToFile(ByVal xmlFilename As String, ByVal imageFilename As String)
        Try
            Dim imageFile() As Byte = readFromXml(xmlFilename)
            Dim fs As FileStream = New FileStream(imageFilename, FileMode.Create)
            fs.Write(imageFile, 0, imageFile.Length)
            fs.Close()
        Catch ex As Exception

        End Try
    End Sub

Select allOpen in new window

try
 {
     DataSet ds = new DataSet();
     byte[] arrContent = null;
     dr = ds.Tables(0).Rows(0);
   
    arrContent = (byte[])dr.Item("imgData");
     //Change the column name to match yours
   
     string conType = dr.Item("imgType").ToString();
   
     Response.ContentType = conType;
   
     Response.OutputStream.Write(arrContent, 0, dr.Item("imgLength"));
   
     Response.End();
 }
catch(Exception ex)
{
throw ex;
}

jeyan10, your suggestion is the same as codingbeaver's first suggestion but in C# instead of VB.Net.  Any ideas on the second part of my issue of saving an image to sql via a webservice?

Experts,

I've arrived at the solution for both saving and retrieving images from a sql server database using a webservice.

For SAVING image to sql via a web service I have added the following method to my web service:

    <WebMethod()> _
Public Function SaveImage(ByVal ImageID As String, ByVal KeyCount As Integer, ByVal Image As Byte()) As String
        Dim SqlCommand1 As New SqlCommand
        Dim SqlConnection1 As New SqlConnection
        Dim sResult As String
        Dim SqlTrans As SqlTransaction

        Try
            SqlConnection1.ConnectionString = ConfigurationManager.ConnectionStrings("DBConnectionString").ConnectionString
            SqlConnection1.Open()
            SqlTrans = SqlConnection1.BeginTransaction
            Dim SqlCommand As SqlCommand = New SqlCommand("Insert Into Images_Test (Image_ID, KeyCount, Image) Values(@ImageID, @KeyCount, @Image)", SqlConnection1)
            SqlCommand.Parameters.Add(New SqlParameter("@ImageID", ImageID))
            SqlCommand.Parameters.Add(New SqlParameter("@KeyCount", KeyCount))
            SqlCommand.Parameters.Add(New SqlParameter("@Image", CType(Image, Object)))
            SqlCommand.Transaction = SqlTrans
            SqlCommand.CommandType = CommandType.Text
            SqlCommand.ExecuteNonQuery()
            SqlTrans.Commit()
            sResult = "Success"
        Catch ex As Exception
            sResult = "Fail"
            SqlTrans.Rollback()
        Finally
            SqlConnection1.Close()
        End Try
        Return sResult
    End Function

And in my web app I use this new method in the web service like this:

    Protected Sub cmdSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdSave.Click

        Try
            'Read Image Bytes into a byte array
            Dim myWS as ImageProject.ImageWebService = New ImageProject.ImageWebService
            Dim imageData() As Byte = ReadFile(Server.MapPath("images") & "\map.gif") 'See below for ReadFile function
            'In this example I have the path to the image file hard-coded but can easily be modified to use a dynamic path via an fileupload control or the like.
            myWS.SaveImage(12345, 3, imageData)
        Catch ex As Exception

        End Try
    End Sub

Here is the ReadFile function that reads the image file into a Byte array and passes the array back:
    Public Function ReadFile(ByVal sPath As String) As Byte()

        Try
            Dim imagedata() As Byte = Nothing
            'Use FileInfo object to get image file size.
            Dim fInfo As FileInfo = New FileInfo(sPath)
            Dim numBytes As Long = fInfo.Length
            'Open FileStream to read the image file
            Dim fStream As FileStream = New FileStream(sPath, FileMode.Open, FileAccess.Read)
            'Use BinaryReader to read file stream into byte array.
            Dim br As BinaryReader = New BinaryReader(fStream)
            'When you use BinaryReader, you need to supply number of bytes to read from file.
            'In this case we want to read entire file.
            imagedata = br.ReadBytes(CType(numBytes, Integer))
            Return imagedata
        Catch ex As Exception

        End Try
    End Function

That is how an image can be saved to a sql database via a web service method.

Now to pull an image back from sql and display it on a webpage this is my preferred method.  It doesn't require you to save the image file off on the server before diplaying it.  Instead you read it into a memorystream object and then write it out to the response stream.

I added another new method to my web service called ReturnImage which excepts a sql string and returns the image as a byte array:

    <WebMethod()> _
Public Function ReturnImage(ByVal sSQL As String) As Byte()
        Dim SqlCommand1 As New SqlCommand
        Dim SqlConnection1 As New SqlConnection

        Try
            Dim sqlConnection As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnectionString").ConnectionString)
            sqlConnection.Open()
            Dim sqlCommand As SqlCommand = New SqlCommand((sSQL), sqlConnection)
            Dim imagedata() As Byte = CType(sqlCommand.ExecuteScalar, Byte())
            sqlConnection.Close()
            Return imagedata
        Catch ex As Exception

        End Try
    End Function

And I can use this new method in the web service in my web app like this (although I have a different need and a different solution which I've also outlined earlier in this thread for saving off multiple image files on the server which fulfills the user's request at this time.) for pulling back a single image and displaying it immediately:

    Protected Sub btnRetrieve_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRetrieve.Click
       
        Try
            Dim mStream As MemoryStream = New MemoryStream
            Dim myWS as ImageProject.ImageWebService = New ImageProject.ImageWebService
            Dim imagedata() As Byte = myWS.ReturnImage("Select image from images_test where image_id = 12345 and keycount = 1")
            mStream.Write(imagedata, 0, imagedata.Length)
            Dim bitmap As Drawing.Bitmap = New Drawing.Bitmap(mStream)
            Response.ContentType = "image/gif"
            bitmap.Save(Response.OutputStream, Imaging.ImageFormat.Gif)
            mStream.Close()
        Catch ex As Exception

        End Try
    End Sub

That's it.  Once it's all written out it doesn't seem that hard but it took me about 4 workdays to arrive at this.  I've taken the time to post it all here so that the next person can save themselves the agony I've felt.

Regards,
Steve.