Introduction
I generally publish beginner articles relating to learning the .NET environment. This time, I will cover making a forms Application that uses WCF services to retrieve data from a database using the Entity Framework and a WCF Service.
What the Code Will Do
The code is a representation of how many Games Played and Goals Scored each player of the Everton FC squad scored in the 14-15 season.
The first part of my code is to create a Form which consists of some TextBoxes, a Button and the corresponding Labeling. The Form is used to enter the Squad Number for the Player and present the data provided by the Service. The Service will use the Squad Number to query the Tables and provide the data on how many goals the player scored and how many Games the player has played. The WCF service will then return this Data to the form to populate the remaining TextBoxes.
Create Database
Before we do anything, we will need to create 2 tables, one is for the Players general information such as name and Number, the second is the Statistical Table with the data for Games Played and Goals Scored. You can create a database by doing add new item to a project and choose the Data option and choose Service-based database. If you create your DB, you will have to add all the data, so I have attached a DB with the data.
Figure 1
CREATE TABLE [dbo].[Players]
(
SquadNumber INT NOT NULL PRIMARY KEY,
LName varchar(24),
FName varchar(24)
)
Create Table [dbo].[Goals]
(
SquadNumber Int not null ,
Goals Int not null,
GamesPlayed Int not null,
PRIMARY KEY CLUSTERED ([SquadNumber] ASC),
FOREIGN KEY ([SquadNumber]) REFERENCES [dbo].[Player] ([SquadNumber])
)
Any source code blocks look like this
//
Create WCF Service
You should create a new project in Visual Studio that is a WCF Service Library. The WCF options should just show up on the Installed Templates under the Heading of WCF as shown below.
Figure 2
The Solution Explorer should look like so.
Figure 3
The Service and IService should be renamed to Player and IPlayer respectively. In changing the name, press yes to the box that will change all the reference details. The first piece of code to work on is the Interface code in IPlayer. The first part of the code will be to create my Service and Data Contracts. The different methods are used to query each of the tables using the Squad which will be provided by the Form. The two Classes are my objects the service will return to the Form with the data relating to the Player the games played and goals scored.
Interface Service & Data Contracts
namespace MCSCEFPlayers
{
[ServiceContract]
public interface IPlayers
{
[OperationContract]
Chosen GetPlayer(int SqdNum);
[OperationContract]
Goals GetGoals(int sqdNum);
}
[DataContract]
public partial class Chosen
{
[DataMember]
public int Snum {get; set;}
[DataMember]
public string Lst {get;set;}
[DataMember]
public string FSt { get; set; }
}
[DataContract]
public class Goals
{
[DataMember]
public int SquadNumber { get; set; }
[DataMember]
public int Played { get; set; }
[DataMember]
public int goals { get; set; }
}
Combining the WCF with EF
If you refer back to figure one and the data options, you should notice ADO.NET Entity Data Model. Select this option and you have the Entity Data Model Wizard, on the first Panel and choose the EF Designer from database. The next Panel is the panel to create the connection to the database previously created. Choose the database you wish to use and press next. The next panel offers you the choice over EF 5 or 6. In this, it is preferable you choose 6 and press next. The last Panel lets you choose the tables and alike you wish to utilize. In this case, choose Goals and Players.
This Entities Model representing these two tables can now be used, in the WCF service to create the Players and Goals Objects that are used in the service to create the Forms data.
The Class Players implements the two methods of the Interface IPlayers. The first method is IPlayers.GetPlayer and the second is IPlayers.GetGoals. The methods are almost identical in the fact that they use a LINQ query to check the EFModel for a result. The LINQ query checks that if there is any data where Squad Number is equal to the Integer passed in. If there is no Data, I have a default entry that produces a no Data Object.
public class Players : IPlayers
{
Chosen IPlayers.GetPlayer(int SqdNum)
{
playersEntitiesA pconv = new playersEntitiesA();
var lePlayer = (from lp in pconv.Players where lp.SquadNumber == SqdNum select lp).FirstOrDefault();
var leP = (from lp in pconv.Players where lp.SquadNumber == 55 select lp).FirstOrDefault();
if (lePlayer != null)
return translateEF(lePlayer);
else
return translateEF(leP);
}
private Chosen translateEF (Player lePlayer)
{
Chosen EvPlay = new Chosen();
EvPlay.Snum = lePlayer.SquadNumber;
EvPlay.Lst = lePlayer.LName;
EvPlay.FSt = lePlayer.FName;
return EvPlay;
}
Goals IPlayers.GetGoals(int sqdNum)
{
playersEntitiesA gconv = new playersEntitiesA();
var leGol = (from g in gconv.Goals where g.SquadNumber == sqdNum select g).FirstOrDefault();
var leG = (from g in gconv.Goals where g.SquadNumber == 55 select g).FirstOrDefault();
if (leGol != null)
return
translateEFG(leGol);
else
return translateEFG(leG);
}
private Goals translateEFG(Goal leGol)
{
Goals GolsC = new Goals();
GolsC.SquadNumber = leGol.SquadNumber;
GolsC.Played = leGol.Played;
GolsC.goals = leGol.goals;
return GolsC;
}
}
The Form
To create a form, we need to add a new project. This can be done by right clicking on the solution heading in the solution explorer as shown below, and press New Solution. For the purposes of this app, please choose a Windows Form Application as in Figure 5.
Figure 4
Figure 5
The first thing to do with the form is to add a reference to the WCF Service Library. This is done by right clicking on the Form Solution and hovering over add and the options to add reference appear. When adding the reference, you arrive at the reference manager panel (Figure 6) you will find in the Solution Section the WCFServiceLibrary. Click the checkbox and press OK.
Figure 6
Righ click the Form Solution again and add a service reference this time. You should appear on a Panel such as the one in figure 7. Press Discover, find your service name it and press OK.
Figure 7
The Form should look like Figure 8 or a design to your liking.
Figure 8
The form Code is relatively simple as an instance of the service reference is called and the methods from the IPlayers called with a squad number provided by the form. This Squad Number is then queried in the player class using the set LINQ Queries and the data provided by the LINQ query populates are TextBoxes.
private void button1_Click(object sender, EventArgs e)
{
int sqnum;
string a1 = textBox1.Text;
bool result = int.TryParse(a1,out sqnum );
if (result)
{
ServiceReference1.PlayersClient mcsc = new ServiceReference1.PlayersClient();
textBox2.Text = mcsc.GetPlayer(sqnum).Snum.ToString();
textBox3.Text = mcsc.GetPlayer(sqnum).FSt;
textBox4.Text = mcsc.GetPlayer(sqnum).Lst;
textBox7.Text = mcsc.GetGoals(sqnum).Played.ToString();
textBox8.Text = mcsc.GetGoals(sqnum).goals.ToString();
}
else
{
MessageBox.Show("Please Enter a Numeric Value", "Numeric Value Not entered");
}
}
The result for 10 and 27 are shown in figure 9 and 10.
Figure 9
Figure 10
Points of Interest
This was the first EF LINQ WCF service I have written, it is quite a powerful tool and easily put together once one has a basic grip of how these services work.
History
- 8th January, 2016: Initial version
