For these tests, we not only want the ability to send the question/answer pair from the server to the client, but also an mp3 audio file and a jpg image file. In order to make use of the SQL-Server feature that allows access from the database to the file system for binary files, we will enable the SQL-Server FILESTREAM capabilities as follows in the instance properties:
[Testno] [int] NOT NULL
[Questionno] [int] NOT NULL
[Question] [nvarchar](80) NOT NULL
[Answer] [nvarchar](80) NOT NULL
[Audio] VARBINARY(MAX) FILESTREAM NULL
[Image] VARBINARY(MAX) FILESTREAM NULL
Next, enable FILESTREAM for the instance in the SQL-SERVER configuration manager:
We can now build the database which will house our table by using the following SQL:
Next, we want to build the table with the desired fields into the VokabelTests database and insert a row for testing purposes. This can be done by using the following SQL:
We now have a database table called vokabel_tests which contains one record that we can test with.
The first step will be to create a WCF service project in Visual Studio:
Next, edit the IService1.cs interface file and add the GetQuestion Method interface that will allow the client to request question data based on the test number and question number.
Add a stub to the Service1.svc.cs file with the GetQuestion method (it will only echo the values passed to it for now).
Modify the web.config file so that it looks as follows (you will comment out the serviceHostingEnvironment:
Finally, right-click on the Service1.svc file and select "view markup" to add the Factory= entry:
<%@ ServiceHost Language="C#" Debug="true" Service="VokabelService.Service1" CodeBehind="Service1.svc.cs"
Right-clicking on the service1.svc file and selecting View in Browser should now let you view what your service returns when you pass the testno/questionno pair
We now have a functioning web service that returns the initial parameters sent to it to a client program.
To do this, right-click on the VokabelService, click Add->New Item->Class, naming the class QuestionClass.cs
For normal applications, we would put the database connection in its own class, but since a web service will reconnect to the database at each request, we can implement the connection within the QuestionClass. The constructor receives the testno and questionno and populates the instance with the correct question/answer pair.
Since we are now getting the data through the QuestionClass class, we must also modify the Service1.svc.cs file that previously returned a canned response directly.
Running the service and accessing it via browser with parameters testno=1 and questionno=2 returns the following:
We will next create ImageClass.cs and AudioClass.cs classes by right clicking on our services and selecting Add->New Item->Code->Class.
The ImageClass will contain one FileStream variable called imagestream that will point to the location of the Image file that we will be querying from the database (passing testno and questionno as query parameters in the constructor). The processing of the binary image file is somewhat more complicated than the text question and answer pair that we returned earlier because we have to process a stream of bytes and write it to a file. Once we have created the file, we will close it and reopen it in read-only mode so that the filestream pointer that we are storing is pointing to the beginning of the file.
Audioclass.cs is almost identical except that we will call our FileStream variable audiostream.
In the final step, we will modify the Service1.svc.cs file to return the image and audio filestream to the calling clients. Note that we must specify that we are returning jpeg and mp3 files so that the calling program knows how to interpret the data.