First we need add a Database project to our existing solution which was created in previous posts (apologies to anyone arriving late but you'll need to read the old postings to reach this point). We create this project by selecting the solution file in Visual Studio 2008. right click "Add" and select "New Project..". Browse to the "Other Project Types/Database" branch on tree in the dialogue and select "Database Project" from the Templates. enter "JokeGenDB" in the name field and set the location as required. The dialogue should look something like the following:
Enter the server name for the SQL Sever 2008 instance you want to use, I'm simply going to use a local installation i.e. "(local)". Select the database as "master", I'm assuming here that you have DBO access. Finally Click OK.
Following all of this you should see a new project added within your solution on the Solution Explorer window in Visual Studio 2008.
Database Scripts
I'm a big fan of scripting the database, table, index and test data within my Database project, so the following instructions focus on that. If you are more inclined to just using SQL Server Management Console, can you can probably by pass most of these settings.
Browse to the "Create Scripts" folder in your JokeGenDB project and create a number of folders. These will hold all the various scripts so we can quickly regenerate the entire database or tables. Right click the "Create Scripts" folder and select "New Folder", enter "Database" and click OK. Repeat these steps for folders named Tables, Indexes, Data. When complete it should look something like the following:
Within each of the folders create and run the following SQL Scripts:
Database\Database.sql Note the file locations as you may want to change these.
USE master
GO
IF EXISTS (select name from sysdatabases where name='JokeGenDB')
BEGIN
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'JokeGenDB'
DROP DATABASE [JokeGenDB]
END
GO
CREATE DATABASE [JokeGenDB] ON PRIMARY
( NAME = N'JokeGenDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\JokeGenDB.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'JokeGenDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\JokeGenDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [JokeGenDB] SET COMPATIBILITY_LEVEL = 100
GO
ALTER DATABASE [JokeGenDB] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [JokeGenDB] SET ANSI_NULLS OFF
GO
ALTER DATABASE [JokeGenDB] SET ANSI_PADDING OFF
GO
ALTER DATABASE [JokeGenDB] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [JokeGenDB] SET ARITHABORT OFF
GO
ALTER DATABASE [JokeGenDB] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [JokeGenDB] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [JokeGenDB] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [JokeGenDB] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [JokeGenDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [JokeGenDB] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [JokeGenDB] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [JokeGenDB] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [JokeGenDB] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [JokeGenDB] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [JokeGenDB] SET DISABLE_BROKER
GO
ALTER DATABASE [JokeGenDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [JokeGenDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [JokeGenDB] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [JokeGenDB] SET READ_WRITE
GO
ALTER DATABASE [JokeGenDB] SET RECOVERY FULL
GO
ALTER DATABASE [JokeGenDB] SET MULTI_USER
GO
ALTER DATABASE [JokeGenDB] SET PAGE_VERIFY CHECKSUM
GO
USE [JokeGenDB]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [JokeGenDB] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
Tables\JokeCategory.sql
USE [JokeGenDB]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JokeCategory]') AND type in (N'U'))
DROP TABLE [dbo].[JokeCategory]
GO
CREATE TABLE [dbo].[JokeCategory](
[Category] [int] IDENTITY(1,1) NOT NULL,
[Description] [varchar](50) NOT NULL,
CONSTRAINT [PK_JokeCategory] PRIMARY KEY CLUSTERED
(
[Category] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Tables\Joke.sql
USE [JokeGenDB]
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Joke_JokeCategory]') AND parent_object_id = OBJECT_ID(N'[dbo].[Joke]'))
ALTER TABLE [dbo].[Joke] DROP CONSTRAINT [FK_Joke_JokeCategory]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Joke]') AND type in (N'U'))
DROP TABLE [dbo].[Joke]
GO
CREATE TABLE [dbo].[Joke](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Category] [int] NOT NULL,
[Text] [varchar](2000) NOT NULL,
[Source] [varchar](200) NULL,
[Graphic] [varchar](200) NULL,
CONSTRAINT [PK_Joke] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Indexes\FK_Joke_JokeCategory.sql
USE [JokeGenDB]
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Joke_JokeCategory]') AND parent_object_id = OBJECT_ID(N'[dbo].[Joke]'))
ALTER TABLE [dbo].[Joke] DROP CONSTRAINT [FK_Joke_JokeCategory]
GO
ALTER TABLE [dbo].[Joke] WITH CHECK ADD CONSTRAINT [FK_Joke_JokeCategory] FOREIGN KEY([Category])
REFERENCES [dbo].[JokeCategory] ([Category])
GO
ALTER TABLE [dbo].[Joke] CHECK CONSTRAINT [FK_Joke_JokeCategory]
GO
Data\JokeCategory.sql
USE JokeGenDB
DELETE FROM [JokeGenDB].[dbo].[JokeCategory]
GO
SET IDENTITY_INSERT [JokeGenDB].[dbo].[JokeCategory] ON
GO
INSERT INTO [JokeGenDB].[dbo].[JokeCategory]
([Category], [Description])
VALUES
(1, 'Knock, Knock')
GO
INSERT INTO [JokeGenDB].[dbo].[JokeCategory]
([Category], [Description])
VALUES
(2, 'Doctor, Doctor')
GO
INSERT INTO [JokeGenDB].[dbo].[JokeCategory]
([Category], [Description])
VALUES
(3, 'Irish')
GO
INSERT INTO [JokeGenDB].[dbo].[JokeCategory]
([Category], [Description])
VALUES
(4, 'General')
GO
SET IDENTITY_INSERT [JokeGenDB].[dbo].[JokeCategory] OFF
GO
Data\Joke.sql
USE JokeGenDB
GO
DELETE FROM [JokeGenDB].[dbo].[Joke]
GO
SET IDENTITY_INSERT [JokeGenDB].[dbo].[Joke] ON
GO
INSERT INTO [JokeGenDB].[dbo].[Joke]
( [ID], [Category], [Text], [Source], [Graphic])
VALUES
(1, 1,
'"Knock, Knock". "Who''s there?", "Sarah", "Sarah Who?", "Sarah doctor in the house."'
,null,null )
GO
INSERT INTO [JokeGenDB].[dbo].[Joke]
( [ID], [Category], [Text], [Source], [Graphic])
VALUES
(2, 1,
'"Knock, Knock". "Who''s there?", "Orange", "Orange Who?", "Orange you glad to came-a-knocking?"'
,'http://www.knock-knock-joke.com',null )
GO
INSERT INTO [JokeGenDB].[dbo].[Joke]
( [ID], [Category], [Text], [Source], [Graphic])
VALUES
(3, 2,
'"Doctor, doctor I swallowed a bone.". "Are you choking?", "No, I really did!"'
,'http://www.ahajokes.com',null )
GO
INSERT INTO [JokeGenDB].[dbo].[Joke]
( [ID], [Category], [Text], [Source], [Graphic])
VALUES
(4, 3,
'"An Irish man is eating in a restaurant, when the waiter throws a prawn cocktail over him. "What''s that for?" he says shocked. The waiter replies "Thats just for starters!".'
,'http://irishjokes.com',null )
GO
INSERT INTO [JokeGenDB].[dbo].[Joke]
( [ID], [Category], [Text], [Source], [Graphic])
VALUES
(5, 4,
'Two zebras are talking and one asks the other, "Am I black with white stripes or white with black stripes?" The other replies, "Well I don''t know. You should pray to God about that and ask him." So that night he did and God replied, "You are what you are." The next day he said to the other zebra, "I still don''t understand what I am because God just said, You are what you are." The second zebra responds, "You must be white with black stripes or else God would have said, Yo is what yo is." '
,'http://www.coolfunnyjokes.com',null )
GO
SET IDENTITY_INSERT [JokeGenDB].[dbo].[Joke] OFF
GO
This will create a very simple database of 5 entires which we will use for testing. The database looks like the following:
Entity Framework Design
I've decided to use the Microsoft ADO.Net Entity Framework for no other reason other than its available and will eventually take over from Linq. You will not need to download this if you already have Service Pack 1 for .Net3.5 running on your machine. I'd also recommend downloading the ADO.Net Entity Framework Tools
First we create a Entity Data model within Visual Studio 2008 on the JokeGen project (i.e. our WCF application). We do this by right clicking the APP_CODE folder and selecting "Add New Item", then selecting "ADO.NET Entity Data Model" from the list of templates. Give it the name JokeGenDB and click Add.
Select Generate from database and click Next. the wizard will probably select the "master" database as this was the database selected previously, however we want to choose the recently created JokeGenDb database instead. Do this by clicking "New Connection" and enter the server name (local) and database name JokeGenDB. Again you should choose your own database name and location if you chose a different installation option. Click OK in the properties window and Next in the Entity Data Model Wizard window.
Select the tables we wish to use (joke and JokeCategory), we are not using Views or Stored Procedures at this stage. Then click "Finish" keeping all the standard defaults.
After creation of the file you should see something like the following:
Re-factoring the Service
Now we create three new classes which will at as the Request/Response contracts for our new web methods. I'm going to do this within the root folder of the project for ease of creation however I'd probably recommend creating a specific folder for these classes. Add two new class files and enter the following code to each:
WSGetAllRequest.cs
USE [JokeGenDB]
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Joke_JokeCategory]') AND parent_object_id = OBJECT_ID(N'[dbo].[Joke]'))
ALTER TABLE [dbo].[Joke] DROP CONSTRAINT [FK_Joke_JokeCategory]
GO
ALTER TABLE [dbo].[Joke] WITH CHECK ADD CONSTRAINT [FK_Joke_JokeCategory] FOREIGN KEY([ID])
REFERENCES [dbo].[JokeCategory] ([Category])
GO
ALTER TABLE [dbo].[Joke] CHECK CONSTRAINT [FK_Joke_JokeCategory]
GO
WSGetAllResponse.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Runtime.Serialization;
/// <summary>
/// Summary description for WSGetAllResponse
/// </summary>
[DataContract(Namespace = "http://tempri.org/")]
public class WSGetAllResponse
{
private List<JokeGenDBModel.Joke> _jokes = new List<JokeGenDBModel.Joke>();
[DataMember]
public List<JokeGenDBModel.Joke> Jokes { get {return _jokes;}
set {_jokes = value;}}
}
WSGetAllCategoriesResponse.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Runtime.Serialization;
/// <summary>
/// Summary description for WSGetAllJokeCategoriesResponse
/// </summary>
[DataContract(Namespace = "http://tempri.org/")]
public class WSGetAllCategoriesResponse
{
private List
[DataMember]
public List
get { return _jokes; }
set {_jokes = value;}}
}
Also we need to modify the Interface and create the method:
IJoke.cs
[OperationContract]
WSGetAllResponse GetJokes(WSGetAllRequest wsJokeRequest);
[OperationContract]
WSGetAllCategoriesResponse GetJokeCategories();
Joke.cs
public WSGetAllResponse GetJokes(WSGetAllRequest wsJokeRequest)
{
WSGetAllResponse wsGetAllResponse = new WSGetAllResponse();
using(JokeGenDBModel.JokeGenDBEntities jokeGenDBEntities = new JokeGenDBEntities())
{
ObjectQuery<JokeGenDBModel.Joke> jokeQuery = jokeGenDBEntities.Joke;
wsGetAllResponse.Jokes = jokeQuery.ToList();
}
return wsGetAllResponse;
}
public WSGetAllCategoriesResponse GetJokeCategories()
{
WSGetAllCategoriesResponse wsGetAllCategoriesResponse = new WSGetAllCategoriesResponse();
using (JokeGenDBModel.JokeGenDBEntities jokeGenDBEntities = new JokeGenDBEntities())
{
ObjectQueryjokeQuery = jokeGenDBEntities.JokeCategory;
wsGetAllCategoriesResponse.JokeCategories = jokeQuery.ToList();
}
return wsGetAllCategoriesResponse;
}
Compile the JokeGen project and it should build without error.
Re-factoring the Test Harness
Now we want to make sure that the method is working correctly and the easiest way to do this is to use our trusty Test Harness, which we've been using in previous articles.
First we update the service reference so it takes into account all our recent changes. Do this by right clicking the "JokeGen" file in the "Service Reference" folder and select "Update Service Reference".
Next we add a button to the form (I've really been lazy with this) and just dragged it onto the form and set the name and text).
Then we add the following code on the background:
private void btnGetAll_Click(object sender, EventArgs e)
{
using (JokeClient wcfJokeGen = new JokeClient())
{
WSGetAllRequest wsGetAllRequest = new WSGetAllRequest();
wsGetAllRequest.RefreshDate = DateTime.Now;
WSGetAllResponse wsGetAllResponse = wcfJokeGen.GetJokes(wsGetAllRequest);
MessageBox.Show(String.Format("Records returned = {0}", wsGetAllResponse.Jokes.Length), "Record Count", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
Compile and run the application. When the form appears press the "Get All" button and you should see an information box showing the number of records.
Check the message formats
Checking the SOAP messages sent to and from the application using the Service Trace Viewer gives us the format of the messages we have the send and decode on the iPhone.
WSGetAllRequest
<s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<GetJokes xmlns="http://tempuri.org/">
<wsJokeRequest>
<RefreshDate>2008-12-09T16:18:36.5706144 08:00</RefreshDate>
</wsJokeRequest>
</GetJokes>
</s:Body>
WSGetAllResponse
<s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<GetJokesResponse xmlns="http://tempuri.org/">
<GetJokesResult>
<Jokes>
<Joke>
<EntityKey>
<EntitySetName>Joke</EntitySetName>
<EntityContainerName>JokeGenDBEntities</EntityContainerName>
<EntityKeyValues>
<EntityKeyMember>
<Key>ID</Key>
<Value xsi:type="xsd:int">1</Value>
</EntityKeyMember>
</EntityKeyValues>
</EntityKey>
<ID>1</ID>
<Text>"Knock, Knock". "Who's there?", "Sarah", "Sarah Who?", "Sarah doctor in the house."</Text>
<JokeCategoryReference>
<EntityKey>
<EntitySetName>JokeCategory</EntitySetName>
<EntityContainerName>JokeGenDBEntities</EntityContainerName>
<EntityKeyValues>
<EntityKeyMember>
<Key>Category</Key>
<Value xsi:type="xsd:int">1</Value>
</EntityKeyMember>
</EntityKeyValues>
</EntityKey>
</JokeCategoryReference>
</Joke>
... clipped ....
</Jokes>
</GetJokesResult>
</GetJokesResponse>
</s:Body>
Next update we'll modify the iPhone application to store this locally within the applications memory.
