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

Experts,

I've looked far and wide for a solution to this problem before I decided to post a new question.

Basically I have a website build in Visual Studio 2005 and written in VB.NET.  There is a page where a user can upload an image file using an asp.net FileUpload control.  What I want to do is once the file has been uploaded save it to a database table in sql server 2000.

I don't have direct access to the database so I can't use a sql command with parameters to do this (I wish I could as it looks hella easy) but instead I need to pass a sql string to a webservice that has 2 methods (SelectSQL - returns a databaset / and ExecuteSQL - returns a string (success/fail)).

The datatypes of the 3 columns in the table where the images are stored are Integer, Image, Varchar(3).  Can someone please post a solution to this so that I can build a sql string like (insert into image_table (image_id, image, image_type) values (12345, 0x49492A00942A..., 'TIF') and pass it to the ExecuteSQL method in the webservice.  Also how to pull this image back using the SelectSQL method of the webservice and display the image on the page.

I have attached the methods in the webservice in case you wanted to check it out or suggest some changes.

Thanks in advance for you help.
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
Imports System.Data.SqlClient

<System.Web.Services.WebService(Namespace:="http://demo.com/")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class DemoWebService
    Inherits System.Web.Services.WebService

    <WebMethod()> _
    Public Function SelectSQL(ByVal sSQL As String) As DataSet
        Dim ReturnDataset As DataSet = New DataSet("ReturnDataSet")
        Dim SqlCommand1 As New SqlCommand
        Dim SqlConnection1 As New SqlConnection

        Try
            SqlConnection1.ConnectionString = ConfigurationManager.ConnectionStrings("DBConnectionString").ConnectionString
            SqlConnection1.Open()

            Dim shipmentDataAdapter As SqlDataAdapter = New SqlDataAdapter()

            shipmentDataAdapter.TableMappings.Add("Table", "Results")

            Dim shipmentsCommand As SqlCommand = New SqlCommand(sSQL, SqlConnection1)
            shipmentsCommand.CommandType = CommandType.Text

            shipmentDataAdapter.SelectCommand = shipmentsCommand

            shipmentDataAdapter.Fill(ReturnDataset)

            'for returning dataset with multiple tables see below...

            'Dim customersAdapter As SqlDataAdapter = New SqlDataAdapter()
            'customersAdapter.TableMappings.Add("Table", "Customers")

            'Dim customersCommand As SqlCommand = New SqlCommand("SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID BETWEEN 10000 AND 10010", SqlConnection1)
            'customersAdapter.SelectCommand = customersCommand

            'customersAdapter.Fill(ReturnDataset)

            SqlConnection1.Close()

            Return ReturnDataset

        Catch ex As Exception

        End Try

    End Function

    <WebMethod()> _
Public Function ExecSQL(ByVal sSQL As String) 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 shipmentsCommand As SqlCommand = New SqlCommand(sSQL, SqlConnection1)
            shipmentsCommand.Transaction = SqlTrans
            shipmentsCommand.CommandType = CommandType.Text
            shipmentsCommand.ExecuteNonQuery()
            SqlTrans.Commit()
            sResult = "Success"
        Catch ex As Exception
            sResult = "Fail"
            SqlTrans.Rollback()
        Finally
            SqlConnection1.Close()
        End Try
        Return sResult
    End Function

End Class

Open in new window

sjmorenoAsked:
Who is Participating?
 
sjmorenoAuthor Commented:
codingbeaver, I tried your suggestion and it did work.  However I will stick to my workaround for the retrieval of images for reasons I've stated earlier.  I'm surprised no one has stepped up and offered a suggestion on how to save the image via a web service though.
0
 
sjmorenoAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
codingbeaverCommented:
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

Open in new window

0
 
sjmorenoAuthor Commented:
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

Open in new window

0
 
jeyan10Commented:


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;
}
0
 
sjmorenoAuthor Commented:
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?
0
 
codingbeaverCommented:
It should work, but allow me to clarify something first.
You will need two web pages to display images. The first page, say called displayImage.aspx, contains an Image control, whose ImageUrl points to the second web page, say called readImage.aspx:
ImageUrl="readImage.aspx?id=xxx". Then you put the code I sent to you in readImage.aspx, it should work.
Let me know if you are using this way to display image.
0
 
sjmorenoAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.