Integrating with SQL Server

This How To will describe how to utilize Neuron as a Web services façade for SQL Server.

Concepts

Users will often create web services that are nothing more than a façade for executing queries against SQL Server.  When this approach is used that façade must be built, tested, deployed and maintained.

An alternative to such an approach is to leverage Neuron’s Client Connector and SQL Connecter to create the façade with almost zero coding.

Step 1 Create and Configure a new ESB

Connect to Neuron in Create Mode and accomplish the following

1. Create Topic named SQLFacade
2. Create Sub- Topic under SQLFacade named Execute and click Apply
3. Create a Sub-Topic under SQLFacade named Query and click Apply
4. Create a Publisher named SQLOnRamp with a Subscription to SQLFacade.* (You create a .* subscription by manually appending the “.*” to the subscription in the Topic Subscriptions dialog box) and click Apply
5. Create a Subscriber named SQLQuerySubscriber and give them a subscription to SQLFacade.Query and click Apply
6. Create a Subscriber and named SQLExecuteSubscriber and give them a subscription to SQLFacade.Execute and Click Apply
7. Click the Save button already done so. A file dialog should appear. Save the .esb as SQLWebServicesHowTo.esb

At this point you have the messaging necessary to accomplish the scenario configured.

Step 2 Create the Sample Database

Use the following script to create the sample database in SQL server we will use for this How-To

CREATE DATABASE [SQLAdapterServiceHowTo]
 
USE[SQLAdapterServiceHowTo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PhoneBook](
      [Id] [int] IDENTITY(1,1) NOT NULL,
      [FirstName] [varchar](50) NULL,
      [LastName] [varchar](50) NULL,
      [PhoneNumber] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[PhoneBook] ON
INSERT [dbo].[PhoneBook] ([Id], [FirstName], [LastName], [PhoneNumber]) VALUES (1, N'Fred', N'Norris', N'6023456789')
INSERT [dbo].[PhoneBook] ([Id], [FirstName], [LastName], [PhoneNumber]) VALUES (2, N'Parsa', N'Rohani', N'5553331234')
INSERT [dbo].[PhoneBook] ([Id], [FirstName], [LastName], [PhoneNumber]) VALUES (3, N'Murray', N'Walensky', N'8675309')
SET IDENTITY_INSERT [dbo].[PhoneBook] OFF
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[uspUpdatePhoneNumber]
(
      @Id int,
      @PhoneNumber varchar(50)
)
as
begin
 update PhoneBook set PhoneBook.PhoneNumber = @PhoneNumber where PhoneBook.Id = @Id
 end
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[uspGetEntryById]
(
      @Id int
)
as
begin
 select * from PhoneBook where PhoneBook.Id = @Id
end
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[uspGetAllEntriesAsXml]
as
begin
 select * from PhoneBook for xml auto, elements, root('PhoneBook')
end
GO 

Make sure to configure security on your new database if you plan to use Windows Integrated security. This usually means setting up a user mapping for NT AUTHORITY\NETWORK SERVICE if Neuron is installed as Local System.

Step 3 Configure SQL Adapter and SQL Adapter Endpoints

Navigate to Connections>Connection Methods Adapters and add the SQL Adapter to your .esb

After your adapter is set up, navigate to Connections > Endpoints > Adapter Endpoints.

Next, set up an Adapter for your Query subscriber. This Subscriber will be used by clients who wish to execute stored procedures that return data. It will use the Query Mode.

You may use Windows Integrated or SQL Server security.

Now set up and endpoint for the for your Execute subscriber. This subscriber will be used by clients who wish to execute a stored procedure but do not need a response. This Adapter Endpoint will use Execute mode.

Step 4 Configure a Process for Dispatching

Create a new process and change the name to Dispatch.

Accomplish the following steps

1. Drag a Decision shape onto the design surface. Click on the “If” branch then click the ellipses in the property grid in the lower right hand corner to bring up the editor.
   
   Enter the following code
   returncontext.Data.Header.Action.EndsWith("NonQuery");
2. Drag a Publish Shape into the “If” branch and Configure it to Publish to SQLFacade.Execute with a Semantic of Multicast

3. Drag a Publish shape onto the “Else” branch. Configure it to publish to SQLFacade.Query and change the Semantic to Request.
4. Next, drag a Cancel shape directly beneath the Publish Step.

At this point we have a process that looks for a pattern in the action and depending on that action either publishes a multicast message or a request message. We will use this process in the publishing Party which we will in turn host in a Client connector.

Step 5 Attach the Dispatching Process to the Publishing Party

Navigate to Messaging > Publishers and add the Dispatch Process to the On Client Publish event. When you’re done it should look something like this:

Step 6 Configure the Client Connectors

Now that you have prepared your publishing Party with a Dispatching Process it is time to expose 2 Client Connectors so that Web Service Clients can call your service.

The first one will be our Datagram Endpoint we’ll call SQLExecuteService and requires configuration of the General tab, Binding tab and Client Connector tab as folllows

Notice the Messaging pattern above has been set to Datagram.

Notice we manually remove the “.*” appended to the Topic subscription and use a URL of http://localhost/execute

Next we configure our Request Reply on ramp we’ll call SQLQueryService. You only need to configure two tabs for this connection.

Notice the only difference in this configuration for the Client Connector tab is the URL http://localhost/query

We need two URLS because you cannot mix messaging patterns in onramps and we are going to use different binding to allow for synchronous and asynchronous Web Service calls.

Step 7 Activate your Configuration and Restart

Save everything if you haven’t already.

Use Configure server and select SQLWebServicesHowTo.esb

Save and Restart.

You can Explorer and reconnect in Connect mode to verify you are now running the correct .esb

At this point you are done with Neuron configuration and your service is ready to go.

Now all that remains is to create a client and test your configuration.

Create a Client Proxy and Call Your Procedures.

Create a Console application or use a Test project and add the following class. You will need to add references to System.ServiceModel, System.Xml and System.Runtime.Serialization

    [ServiceContract]
    public interface IDataService
    {
        [OperationContract(IsOneWay=true)]
        [XmlSerializerFormat]
        void ExecuteNonQuery(ExecuteArguments arg);
 
        [OperationContract]
        [XmlSerializerFormat]
        StoredProcResults Execute(ExecuteArguments arg);
    }
 
    [MessageContract(IsWrapped=false)]
    public class ExecuteArguments
    {
        public ExecuteArguments()
        {
            this.Execute = new Execute();
        }
       
        [MessageBodyMember(Namespace="")]
        public Execute Execute;
    }
 
    public class Execute
    {
        public Execute()
        {
            Parameters = new List<Parameter>();
        }
        public List<Parameter> Parameters;
 
        [XmlAttribute(AttributeName = "sp")]
        public string Procedure;
    }
 
    public class Parameter
    {
        [XmlAttribute(AttributeName="name")]
        public string Name;
        [XmlAttribute(AttributeName="value")]
        public string Value;
    }
 
    [MessageContract(IsWrapped=false)]
    public class StoredProcResults
    {
        public StoredProcResults()
        {
            this.Data = new Data();
        }
        [MessageBodyMember(Namespace="")]
        public Data Data;
    }
 
    public class Data
    {
        [XmlElement("Table")]
        public Table Table;
    }
 
    public class Table
    {
        [XmlElement("Row")]
        public List<Row> Rows;
    }
 
    public class Row
    {
 
        public Row()
        {
            this.Items = new List<Column>();
        }
        [XmlElement("Column")]
        public List<Column> Items;
    
    }
 
    public class Column
    {
        [XmlAttribute(AttributeName = "name")]
        public string Name;
       
        [XmlText]
        public string Value;
    }
} 

Now add the following code to either a test method or your main method.

 var efac = new ChannelFactory<IDataService>(new BasicHttpBinding(),new EndpointAddress("http://localhost/execute"));
            var echan = efac.CreateChannel();
            echan.ExecuteNonQuery(new ExecuteArguments
            {
                Execute = new Execute
                {
                    Procedure = "uspUpdatePhoneNumber",
                    Parameters = new List<Parameter>
                    {
                        new Parameter
                        {
                            Name="@Id",
                            Value = "2"
                        },
                        new Parameter
                        {
                            Name="@PhoneNumber",
                            Value = "5555678309"
                        }
                    }
                }
            });
            efac.Close();
 
            var qfac = new ChannelFactory<IDataService>(new BasicHttpBinding(), new EndpointAddress("http://localhost/query"));
            var qchan = qfac.CreateChannel();
            StoredProcResults results = qchan.Execute(new ExecuteArguments
                                        {
                                            Execute = new Execute
                                            {
                                                Procedure = "uspGetEntryById",
                                                Parameters = new List<Parameter>
                                                {
                                                    new Parameter
                                                    {
                                                        Name="@Id",
                                                        Value = "2"
                                                    },
                                                }
                                            }
                                        });
 
            foreach(Row r in results.Data.Table.Rows)
            {
                foreach(Column c in r.Items)
                {
                    Console.Write("Column Name:{0} , Column Value:{1} ", c.Name, c.Value);
                }
                Console.WriteLine();
            }
 
            qfac.Close(); 

Conclusion

This configuration and code can be used to rapidly create SQL Server based solutions. It spares you the unnecessary code and maintenance of a separate data tier and can be enhanced easily by adding your own validation, rules etc into the Dispatch Process or Processes attached to your SQL Adapter parties.

Finally, you may have noticed the inclusion of an extra stored procedure that we didn’t call. That procedure can be called from the XML Query Step in place of Publishing to the SQL Adapter. You may want to do this if you want to return more domain specific objects. It is recommended that if you do decide to go this route that you use Message Contract.

Appendix A:  Traditional Proxy code

 public partial class DataServiceClient : System.ServiceModel.ClientBase<IDataService>, IDataService
    {
 
        public DataServiceClient()
        {
        }
 
        public DataServiceClient(string endpointConfigurationName) :
            base(endpointConfigurationName)
        {
        }
 
        public DataServiceClient(string endpointConfigurationName, string remoteAddress) :
            base(endpointConfigurationName, remoteAddress)
        {
        }
 
        public DataServiceClient(string endpointConfigurationName, System.ServiceModel.EndpointAddress remoteAddress) :
            base(endpointConfigurationName, remoteAddress)
        {
        }
 
        public DataServiceClient(System.ServiceModel.Channels.Binding binding, System.ServiceModel.EndpointAddress remoteAddress) :
            base(binding, remoteAddress)
        {
        }
 
        public void ExecuteNonQuery(ExecuteArguments arg)
        {
            base.Channel.ExecuteNonQuery(arg);
        }
 
        public StoredProcResults Execute(ExecuteArguments arg)
        {
            return base.Channel.Execute(arg);
        }
 
       
    }