WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
The .NET framework has thousands of discrete classes that are well-organized by namespaces. The .NET framework also has large chunky bits that are whole sub-systems within the framework. When you start combining these sub-systems, you get powerful solutions very quickly. In this article, you'll combine Windows Communication Foundation (WCF) introduced in .NET 3.0 and LINQ to SQL introduced in .NET 3.5 and learn how to implement a service on top of LINQ and SQL server with astonishingly few lines of code.
Using the WCF Service Application Applet
When the concept of wizards first showed up, I didn't like them that much. They seemed to produce a bunch of code that looked like magic, but this code was sometimes hard to modify and hard to reproduce. Newer wizards in Visual Studio with the .NET framework use framework code, producing lucid, and easy to follow bits of code. So, although you could create a WCF solution from scratch, let Visual Studio generate the starter code for you.
WCF uses the concept of a ServiceContract and a DataContract. The ServiceContract specifies the behaviors provided by your service and the DataContract describes data that will be introduced by the service. If you select File|New Project and pick WCF Service Library—in Visual Studio 2008—the templates and wizards will stub out all of the basic elements, demonstrating a sample ServiceContract and DataContract. This stubbed solution is ready to compile and test.
The WCF template defines an interface attributed with the ServiceContractAttribute, a class that is attributed with the DataContractAttribute, and a second a class that implements the ServiceContract interface. The DataContract class is a dummy class representing a composite type showing you how to implement custom types to be used in your service. Each behavior that you want to publish is described in the ServiceContract as a method and attributed with the OperationContractAttribute. Each property that you want to be serialized in the composite type—the DataContract—is attributed with the DataMemberAttribute. In short, OperationContractAttributes are callable by service consumers and DataMemberAttributes are serialized and accessible by those same consumers.
|If you change the name of service class,you must update the reference in the web.config and in the associated .svc file.|
Because Visual Studio stubs out all of this for you, you can move on to the code you have to write to implement a useful service.
Programming with LINQ to SQL Behind a WCF Service
Suppose now that you want to use LINQ to SQL behind the service. You will need a database (you'll use Northwind) and some basic plumbing for LINQ to SQL. The two basic LINQ to SQL elements you will need are a DataContext, representing the connection to the database, and an entity class (or DataSet) attribute with the TableAttribute. The TableAttribute indicates that the annotated class can be populated by the LINQ to SQL plumbing.
The key here is that the DataContract and the LINQ to SQL entity can be the same composite type. That is, you define one class and attribute it with WCF and LINQ to SQL attributes, resulting in both technologies being supported in the same class.
For your purposes, you will define a service that looks up a customer in the Northwind Traders database. The code that follows is bootstrapped with the WCF Visual Studio wizard template and the LINQ to SQL code was added. Listing 1 starts you off with the WCF ServiceContract.
Listing 1: The WCF ServiceContract definition (an interface).
<ServiceContract()> _ Public Interface ICustomerService <OperationContract()> _ Function GetCustomer(ByVal customerID As String) As Customer End Interface
Listing 2 implements the ServiceContract by providing an implementation of for GetCustomer.
Listing 2: A simple WCF ServiceContract that means you will be defining a service that returns customers by CustomerID.
Imports System.Data.Linq Public Class CustomerService Implements ICustomerService Public Sub New() End Sub Public Function GetCustomer(ByVal customerID As String) _ As Customer Implements ICustomerService.GetCustomer Dim northwind As Northwind = New Northwind() Dim customers As Table(Of Customer) = _ northwind.GetTable(Of Customer)() Dim customer = (From cust In customers _ Where cust.CustomerID = customerID _ Select cust).First() Return customer End Function End Class
To suport the LINQ code, a refernce to System.Data.Linq is added to the WCF library project. The LINQ code is a SQL-like query that shows (among other things) that you will need a class Northwind and a Table Customer. The rest is a LINQ query.
LINQ queries start with the From clause and end with a Select statement. You can think of this query as slecting customer by CustomerID. The .First() method call is an agrgegate operation that returns the first customer from the result set. (Because CustomerIDs are unique, this code should return just one Customer as long as the ID is valid.)
Breaking down the LINQ query, the From clause defines a range cust and the searchable collection, customers. The Where predicate filters the query, and the Select clause builds the result collection. The parentheses means to treat the LINQ query result as an object and call First on it. The return type of the LINQ query in this example is IEnumerable(Of Customer) and the extension method First is defined for IEnumerable(Of T). For all intents and purposes, this means any class that implements IEnumerable(Of T) can return from a LINQ query, which is basically any collection. (For more on LINQ, LINQ to SQL, and extension methods check out my new book LINQ Unleashed for C# from Sams, August 2008.)
The remaining two steps are to define the DataContract and entity Table representing Customer objects and implement the DataContext to work with LINQ to SQL. All of these elements are implemented in the WCF service. Listing 3 implements the DataContext.
Listing 3: The DataContext, which represents all of the SQL and ADO.NET plumbing you no longer need to write if you use LINQ to SQL.
Public Class Northwind Inherits DataContext Private Shared ReadOnly connectionString As String = _ "Data Source=BUTLER;Initial Catalog=Northwind; _ Integrated Security=True" Public Sub New() MyBase.New(connectionString) End Sub End Class
It doesn't look like much, but if you assign Console.Out to Northwind.Log you will see that the LINQ to SQL plumbing handles connections to the database and conversions of LINQ queries to SQL queries. You are not required to write the code that uses command, adapter, or connection objects.
Listing 4 defines the DataContract for WCF and the entity Table for LINQ to SQL in a single class, Customer.
Listing 4: The DataContract and LINQ to SQL entity class are defined as a basic custom class with a few additional attributes.
<DataContract()> _ <Table(Name:="Customers")> _ Class Customer Private _customerID As String <DataMember()> _ <Column()> _ Public Property CustomerID() As String Get Return _customerID End Get Set(ByVal Value As String) _customerID = Value End Set End Property Private _customerName As String <DataMember()> _ <Column(Name:="CompanyName")> _ Public Property CustomerName() As String Get Return _customerName End Get Set(ByVal Value As String) _customerName = Value End Set End Property End Class
Listing 4 contains a basic class that maps two properties to two columns in the Northwind Customers table. The TableAttribute on the class tells LINQ to SQL which table (or view or stored procedure) to get the data from. The ColumnAttribute maps properties to columns in that table. If the property types and names match the columns, no named arguments are needed for the ColumnAttribute. The DataContract on the class and DataMember on the properties are present for WCF. These tell WCF that this type needs to be represented when clients consume this web service.
This is all you need to do to get data from a SQL database and return it from a service.