Create a simple WCF connector for DBTextFinder

WCF (Windows Communication Foundation) is a framework for creating distributed applications whose components communicate with each other through services, using a wide range of network protocols. In this article I will comment on the fundamentals of creating and configuring one of these services through a data connector for the DBTextFinder application, a program to search for texts in a database that can be extended to connect to any data management system.

In this article you can download the DBTextFinder application to search text in databases, as well as find information on its basic use.

In this other article you can download the DBTextFinder connectors sample code for different databases, SQL Server, Oracle and MySQL, as well as a summary of the basic structure of these connectors, which can be taken as a basis for developing another ones to access data from other database management systems.

In this link you can download the source code of the DBTFWCFServiceSqlServer project, written in csharp using Visual Studio 2013.

In this article, I will use the Sql Server connector as the basis for building a WCF service hosted on IIS (Internet Information Server), with which the application will be able to connect to a remote Sql Server database through the http protocol.

Basic components of a WCF service

To build a WCF service, you first need a service contract; this is nothing more than an interface decorated with a series of attributes that make it valid to define the service. The interface that DBTextFinder uses is IDBTFConnection, defined in the DBTFCommons class library as follows:

[ServiceContract(Namespace = " http://tempuri.org/IDBTFConnection",
SessionMode = SessionMode.Required,
CallbackContract = typeof(IDBTFSearchCallback))]
public interface IDBTFConnection
{
IDBTFSearchCallback Callback { get; set; }
[OperationContract(Name = "GetConnectionOptions",
Action = ".../GetConnectionOptions",
ReplyAction = ".../GetConnectionOptionsResponse")]
ConnectionOptions GetConnectionOptions();
[OperationContract(Name = "GetConnectionOptionsAsync",
Action = "…/GetConnectionOptionsAsync",
ReplyAction = ".../GetConnectionOptionsAsyncResponse")]
Task<ConnectionOptions> GetConnectionOptionsAsync();
[OperationContract(Name = "ParseConnectionString",
Action = ".../ParseConnectionString",
ReplyAction = ".../ParseConnectionStringResponse")]
ConnectionOptions ParseConnectionString(string strconn);
[OperationContract(Name = "ParseConnectionStringAsync",
Action = "…/ParseConnectionStringAsync",
ReplyAction = ".../ParseConnectionStringResponse")]
Task<ConnectionOptions> ParseConnectionStringAsync(string strconn);
[OperationContract(Name = "ValidateConnectionOptions",
Action = ".../ValidateConnectionOptions",
ReplyAction = ".../ValidateConnectionOptionsResponse")]
ConnectionOptions ValidateConnectionOptions(ConnectionOptions conntest);
[OperationContract(Name = "ValidateConnectionOptionsAsync",
Action = ".../ValidateConnectionOptionsAsync",
ReplyAction = ".../ValidateConnectionOptionsAsyncResponse")]
Task<ConnectionOptions> ValidateConnectionOptionsAsync(
ConnectionOptions conntest);
[OperationContract(Name = "GetSchemas",
Action = ".../GetSchemas",
ReplyAction = ".../GetSchemasResponse")]
string[] GetSchemas(ConnectionData connection);
[OperationContract(Name = "GetSchemasAsync",
Action = ".../GetSchemasAsync",
ReplyAction = ".../GetSchemasAsyncResponse")]
Task<string[]> GetSchemasAsync(ConnectionData connection);
[OperationContract(Name = "GetTables",
Action = ".../GetTables",
ReplyAction = ".../GetTablesResponse")]
string[] GetTables(ConnectionData connection, string schema);
[OperationContract(Name = "GetTablesAsync",
Action = ".../GetTablesAsync",
ReplyAction = ".../GetTablesAsyncResponse")]
Task<string[]> GetTablesAsync(ConnectionData connection, string schema);
[OperationContract(Name = "GetViews",
Action = ".../GetViews",
ReplyAction = ".../GetViewsResponse")]
string[] GetViews(ConnectionData connection, string schema);
[OperationContract(Name = "GetViewsAsync",
Action = ".../GetViewsAsync",
ReplyAction = ".../GetViewsAsyncResponse")]
Task<string[]> GetViewsAsync(ConnectionData connection, string schema);
[OperationContract(Name = "GetProcedures",
Action = ".../GetProcedures",
ReplyAction = ".../GetProceduresResponse")]
string[] GetProcedures(ConnectionData connection, string schema);
[OperationContract(Name = "GetProceduresAsync",
Action = ".../GetProceduresAsync",
ReplyAction = ".../GetProceduresAsyncResponse")]
Task<string[]> GetProceduresAsync(ConnectionData connection,
string schema);
[OperationContract(Name = "Search",
Action = ".../Search",
ReplyAction = ".../SearchResponse")]
ObjectResult[] Search(ConnectionData connection,
string tables,
string views,
string procedures,
string searchexpr,
bool ignorecase);
[OperationContract(Name = "SearchAsync",
Action = ".../SearchAsync",
ReplyAction = ".../SearchAsyncResponse")]
Task SearchAsync(ConnectionData connection,
string tables,
string views,
string procedures,
string searchexpr,
bool ignorecase);
[OperationContract(Name = "Replace",
Action = ".../Replace",
ReplyAction = ".../ReplaceResponse")]
[ServiceKnownType(typeof(TableResult))]
[ServiceKnownType(typeof(ProcResult))]
void Replace(ConnectionData connection,
ObjectResult result,
string searchexpr,
bool ignorecase);
[OperationContract(Name = "ReplaceAsync",
Action = ".../ReplaceAsync",
ReplyAction = ".../ReplaceAsyncResponse")]
[ServiceKnownType(typeof(TableResult))]
[ServiceKnownType(typeof(ProcResult))]
Task ReplaceAsync(ConnectionData connection,
ObjectResult result,
string searchexpr,
bool ignorecase);
[OperationContract(Name = "Delete",
Action = ".../Delete",
ReplyAction = ".../DeleteResponse")]
[ServiceKnownType(typeof(TableResult))]
[ServiceKnownType(typeof(ProcResult))]
void Delete(ConnectionData connection, ObjectResult result);
[OperationContract(Name = "DeleteAsync",
Action = ".../DeleteAsync",
ReplyAction = ".../DeleteAsyncResponse")]
[ServiceKnownType(typeof(TableResult))]
[ServiceKnownType(typeof(ProcResult))]
Task DeleteAsync(ConnectionData connection, ObjectResult result);
}

The interface is decorated with the ServiceContractAttribute attribute, whereas interface members that are service operations must be decorated with the OperationContractAttribute attribute. These attributes have no special meaning outside the context of WCF services, so the interface can be used as an ordinary interface in any other type of classes.

You can see that in this contract there are two versions for each operation, one synchronous (the operation blocks the execution of the program until it has finished processing), and another asynchronous (runs in parallel with the program, without blocking it). This is not always necessary, it is an optional mechanism, but in this way the example is more complete.

In the ServiceContract attribute you can see that the CallbackContract = typeof (IDBTFSearchCallback) property is configured, which makes this contract a duplex one. Normally, the application calls the service and the service returns the results of the operation, but with a duplex contract, the service can also call the application. This will be used for the SearchAsync asynchronous search operation, which is the one the DBTextFinder application uses, to pass the results of the search to the program, while the user interface continues to respond to events.

To complete a duplex contract, another interface must be defined, to be implemented by the application, so that the service can call it. In this case it is IDBTFSearchCallback, defined as follows:

public interface IDBTFSearchCallback
{
bool SearchResult(ObjectResult result);
[OperationContract(Name = "SearchResultNC",
IsOneWay = true,
Action = "http://tempuri.org/IDBTFConnection/SearchResultNC")]
[ServiceKnownType(typeof(TableResult))]
[ServiceKnownType(typeof(ProcResult))]
void SearchResultNC(ObjectResult result);
}

The SearcResult member of the interface is not decorated with OperationContractAttribute, this is because, in a duplex contract, the operations involved must be unidirectional, that is, they should not return any value. However, DBTextFinder uses this method to allow you to cancel the search, returning false. In the WCF service we lose this possibility, and we must define the SearchResultNC operation to use instead. You can see that the OperacionContract attribute is configured with the IsOneWay = true property, which states that this call does not expect the service to return anything. Otherwise, the application would be blocked waiting for a response that will never come.

In the code, to use the callback in a given operation, it must be obtained as follows:

Callback = OperationContext.Current.GetCallbackChannel<IDBTFSearchCallback>();

It is also necessary to decorate with attributes the classes that are used for data exchange. These are the ones that use this service, defined in the DBTFCommons.Data namespace:

[DataContract(Name = "SearchScope",
Namespace = ".../DBTFCommons.Data")]
[Flags]
public enum SearchScope
{
[EnumMember]
Tables = 0x1,
[EnumMember]
StortedProcs = 0x2,
[EnumMember]
All = 0x3
}
[DataContract(Name = "ConnectionData",
Namespace = ".../DBTFCommons.Data")]
public class ConnectionData
{
[DataMember]
public string StringConnection { get; set; }
[DataMember]
public SearchScope Scope { get; set; }
}
[DataContract(Name = "ConnectionOptions",
Namespace = ".../DBTFCommons.Data")]
public class ConnectionOptions
{
[DataMember]
public bool SwowInitialCatalog { get; set; }
[DataMember]
public bool ShowWindowsAuthentication { get; set; }
[DataMember]
public string UserName { get; set; }
[DataMember]
public string Password { get; set; }
[DataMember]
public string DataSource { get; set; }
[DataMember]
public string InitialCatalog { get; set; }
[DataMember]
public bool WindowsAuthentication { get; set; }
[DataMember]
public string StringConnection { get; set; }
[DataMember]
public string ProviderName { get; set; }
[DataMember]
public string Error { get; set; }
[DataMember]
public string Description { get; set; }
}
[DataContract(Name = "FieldData",
Namespace = ".../DBTFCommons.Data")]
public class FieldData : IComparable<FieldData>,
IEquatable<FieldData>
{
[DataMember]
public string Name { get; set; }
[DataMember]
public string Value { get; set; }
[DataMember]
public bool Selected { get; set; }
[DataMember]
public string ReplaceEx { get; set; }
public int CompareTo(FieldData other);
public bool Equals(FieldData other);
}
[DataContract(Name = "ObjectResult",
Namespace = ".../DBTFCommons.Data")]
[KnownType(typeof(TableResult))]
[KnownType(typeof(ProcResult))]
public class ObjectResult
{
[DataMember]
public string Schema { get; set; }
[DataMember]
public string Name { get; set; }
[DataMember]
public bool Selected { get; set; }
}
[DataContract(Name = "TableResult",
Namespace = ".../DBTFCommons.Data")]
public class TableResult : ObjectResult
{
public TableResult();
public TableResult(IEnumerable<FieldData> text,
IEnumerable<FieldData> keys,
IEnumerable<FieldData> all);
[DataMember]
public FieldData[] TextFields { get; set; }
[DataMember]
public FieldData[] KeyFields { get; set; }
[DataMember]
public FieldData[] AllFields { get; set; }
public string QueryFields { get; }
public int SelectedCount { get; }
public FieldData GetField(string name);
public void SetValue(string name, string value);
public override string ToString();
}
[DataContract(Name = "ProcMatch",
Namespace = ".../DBTFCommons.Data")]
public class ProcMatch
{
[DataMember]
public int Index { get; set; }
[DataMember]
public int Length { get; set; }
[DataMember]
public bool Selected { get; set; }
[DataMember]
public string ReplaceEx { get; set; }
public override string ToString();
}
[DataContract(Name = "CodeType",
Namespace = ".../DBTFCommons.Data")]
public enum CodeType
{
[EnumMember]
View = 0x1,
[EnumMember]
Procedure = 0x2
}
[DataContract(Name = "ProcResult",
Namespace = ".../DBTFCommons.Data")]
public class ProcResult : ObjectResult
{
[DataMember]
public CodeType CodeType { get; set; }
[DataMember]
public string ProcedureCode { get; set; }
[DataMember]
public ProcMatch[] Matches { get; set; }
public int MatchCount { get; }
public int SelectedCount { get; }
public void AddMatch(ProcMatch pm);
public override string ToString();
}

Classes, structures, and enumerations must be marked with the DataContractAttribute attribute. It is an attribute similar to SerializableAttribute, which allows objects to be serialized by the service communication channel.

The data members to be serialized must be decorated with the DataMemberAttribute attribute. Members not decorated simply are not serialized. In enumerations, the EnumMemberAttribute attribute is used to decorate the members for their serialization.

Finally, there is the KnownTypeAttribute attribute. To return the search results, there is the generic base class ObjectResult, from which TableResult and ProcResult are derived. In order for the system to know how to serialize and deserialize the ObjectResult object types, all possible final types of the objects must be listed with this attribute.

Once the service contract and data contracts are defined, we can create a WCF service in different ways, depending on how we plan to host it. We can use a console or Windows Forms application, use WAS (Windows Activation Services) to activate it, or even a Windows service, in addition to Windows Azure, to build a host for the service. We can also choose, as in this case, to host the service as a web application, using the http or https protocol, on a web server like IIS. This is the model chosen for this service, for which you can create a new WCF Service Application project, in the WCF folder of Visual Studio projects.

As the service contract interface is already defined in the DBTFCommons library, you can remove the default interface that is created when the project is generated, so you only need to complete the service class, SqlServerService.svc, implementing this interface. To do this, since I have already implemented this interface in the DBTFSqlServerConnection class, all I have done is a copy and paste job, making some minor changes to adapt the operation to the context of a WCF service. Basically in this case, implement its limitations.

Service configuration

Once the service is created, you have to configure it properly so that it can be used. The configuration can be done from the service code, but the most flexible way is to do it in the Web.config file of the web application.

The service configuration is done within the system.serviceModel element. The first element to configure is the type of binding. In this case, you need an appropriate http binding for a duplex contract, so the best choose is the wsDualHttpBinding binding type. You have also other binding types, such as basicHttpBinding and wsHttpBinding, for http or https connections, NetTcpBinding for local network tcp connections, netNamedPipeBinding for connections between processes on the same computer, or you can even define custom connections with the customBinding element.

In the bindings element you define the bindings available for your service:

<bindings>
<wsDualHttpBinding>
<binding name="wsDuplex"
maxReceivedMessageSize="10000000"
sendTimeout="00:30:00">
<security mode="None" />
</binding>
</wsDualHttpBinding>
<mexHttpBinding>
<binding name="mexBinding" />
</mexHttpBinding>
</bindings>

With this you have created a configuration for the binding wsDualHttpBinding, called wsDuplex, which allows messages up to 1000000 bytes and with a timeout of 30 minutes.

In this example, I completely bypassed security with the security = "None" element. Usually, an appropriate security mechanism for the connection will have to be established.

mexHttpBinding is the binding used to obtain service metadata. These metadata are used for the applications used to generate connections to the service obtain the necessary information to create the clients.

The services contained in the application are defined in the services element:

<services>
<service name="DBTFWCFServiceSqlServer.SqlServerService">
<endpoint address="http://{Server Uri}/SqlServerService.svc"
binding="wsDualHttpBinding"
bindingConfiguration="wsDuplex"
contract="DBTFCommons.Interfaces.IDBTFConnection" />
<endpoint address="http://{Server Uri}/mex"
binding="mexHttpBinding"
bindingConfiguration="mexBinding"
contract="IMetadataExchange" />
</service>
</services>

The endpoint element defines the address to connect with the service. There is an endpoint to connect to the service itself, and another to get the metadata that defines the service. The endpoint element defines the binding and the configuration to be used for the connection and the service contract. Also defined is the url with which the link to the service is made. In this example, {Server Uri} must be replaced by the appropriate address to access the IIS server in your case.

The installation of the service is done with the IIS administrator. Create a folder on your server and copy the SqlServerService.svc, Web.config and bin folder created when the solution is generated. You can then create a web application in IIS as a standalone site or a virtual directory from a preexisting site, such as Default Website.

Configuring the client

For DBTextFinder to connect to the service, it is necessary to configure the connection in the DBTextFinder.exe.Config file. This configuration is very similar to that of the server.

<system.serviceModel>
<bindings>
<wsDualHttpBinding>
<binding name="Duplex">
<security mode="None" />
</binding>
</wsDualHttpBinding>
</bindings>
<client>
<endpoint address="http://{ServerUri}/SqlServerService.svc"
binding="wsDualHttpBinding"
bindingConfiguration="Duplex"
contract="DBTFCommons.Interfaces.IDBTFConnection"
name="{Data Source name}" />
</client>
</system.serviceModel>

In the client section, the client connections with the servers are defined. The name attribute of the endpoint element is important, and must be the same as the name of the database instance. This can be the server IP or the Sql Server instance name.

To configure a connection from the application, you must select the connector type Generic WCF connector, and put in the Server text box the name of the endpoint of your service, which must match the name of the database server instance.

And with this you should be able to connect the DBTextFinder application with a remote database through a WCF service using the http protocol.