The commoditization of technology has reached its pinnacle with the advent of the recent paradigm of Cloud Computing. Infosys Cloud Computing blog is a platform to exchange thoughts, ideas and opinions with Infosys experts on Cloud Computing

« Enterprise Cloud Adoption Strategic Roadmap | Main | Will the inclusion of cloud-computing in Industries may decrease the job opportunities in future? »

Along with data in on-premise database, can we also expose stored procedure using Azure building blocks to internet?

While working in some project we might have come across the requirement:

·         Migrate an existing web- application from on-premise to cloud for some of the obvious reasons. And I believe by now we know what are the different driving factors for migrating an/a application/service to cloud

·         But keep the back-end database on-premise. Quite a few reasons for this, say for instance the data is of very "high business impact" type and can't be put outside the corporate network.

We must have explored quite a few options like being in Windows Azure domain, options are:

·         Make use of Azure connect and create some kind of local area network comprising of the database server and the virtual machines having Azure roles (having the application).

·         Make use of Azure appfabric service bus (my favorite option) to expose database over http as OData interface and also support the CRUD operations.

But now how to expose the SQL Stored Procedures and functions defined in the back-end database.

One may promptly say, I would use Azure connect and hence could access the database in the same way in the code as the existing implementation. But there is one constraint in using Azure-connect, we have to install some client in the machines having the database and this specific reason of getting access and installing some client application may affect the migration process (because of concerns raised by the network guys and system adminstrator)

Another easy approach could be using the Azure appfabric service bus but this will need some code modification but if coded properly than it will be just one time effort. Let me try to provide an approach to achieve the same which could be extended to handle the requirement better.

Assumption:

The reader is aware of

·         The Azure appfabric service bus related terminologies.

·         The REST WCF implementation.

Approach:

We will leverage the basic feature of the Stored Procedure i.e. all the parameters passed needs to be of primitive type i.e. string , int, bool, etc, no user defined complex type.

We may expose REST WCF service interface through the Appfabric service as:

http://appfabric_namespace. servicebus.windows.net/SP/SP_name/SP_parameters

where SP_name is the name of the Stored procedure, SP_parameters is the list of parameters expected by the stored procedure in key-value format with some delimiter separating the parameters like:

param1=value1#param2=value2

And to achieve this we need to define the following types of service contract and service implementation:

Contract:

[WebGet(UriTemplate="/SP/{ SP_name }/{ SP_parameters }")]

 SomeObject ExecuteSP(string SP_name, string SP_parameters);

Where SomeObject is some object (DataContract) to keep the return data.

Implementation:

public SomeObject ExecuteSP(string SP_name, string SP_parameters)

{       

            SomeObject returnData = new SomeObject ();

            string[] parameterPairs = SP_parameters.Split('#');

            string connectionString = the connection string to connect the intended SQL server database

            using (var conn = new SqlConnection(connectionString))

            {

                using (var command = new SqlCommand("SP_name", conn))

                {

                    command.CommandType = System.Data.CommandType.StoredProcedure;

                    foreach (string paramPair in parameterPairs)

                    {

                        string[] param = paramPair.Split('=');

                        string paramName = param[0];

                        string paramValue = param[1];

                        command.Parameters.Add(new SqlParameter("@" + paramName, param[1]));

                    }

                    int rowsAffect  = command.ExecuteNonQuery();

                     //assign the return of stored procedure execution to some property of SomeObject

                   //e.g. "data' of type"object" for handling any return data type

                }

            }

            return returnData;

}

The code can be also enhanced to even handle the scenario where some data is returned back by the stored procedure either by modifying this interface or adding a new interface.

 

Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)

Please key in the two words you see in the box to validate your identity as an authentic user and reduce spam.