Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / DevOps / automation

Joomla Remote SQL Call -VB.NET Client

0.00/5 (No votes)
9 Sep 2015CPOL2 min read 9K   46  
In this tip, I show a set of helper classes useful to prepare and send messages to the SqlXML Joomla component and execute remote SQL calls to your hosted website.

Table of Contents

Introduction

This is the second of two articles that show a method to automate Joomla tasks or applications based on it.
In the first article, I show the server part, a Joomla component that can handle XML message.

For this article, I implemented few VB.NET classes that help you work with the component. These classes allow you to prepare SQL statements with a “Joomla dbo” style, send them and manage the response.

Classes

The jsxHelper class is the main helper class used to send messages.
It has two properties, security_token and endpoint, that must be initialized with the parameters of your server, and one function SendMessage() that takes a jsxRequest object as input parameter and returns a jsxResponse object.

Image 1

Once installed, the SqlXML Component shows an administrative page, under the menu “Components”.

Image 2

This panel shows the security token and the endpoint to set the jsxHelper fields:

Image 3

The jsxRequest class is the root message container; during XML serialization, it represents the <msg-req> tag. It is only a container.

The jsxResponse represents the root object of the response message, the tag <msg-response>.

Both jsxRequest jsxResponse have a commands array field that contains one or more jsxCommand.

Points of Interest

The code provided with this article is a Console project written in VB.NET that define the helper classes.

The project shows how to use the helper classes sending different SQL commands.
It executes DDL statements that create and delete a temporary table in the underlying database, and shows how to insert, update and select some rows showing the use of DML statements.

An interesting class is the jsxSQLCommandBuilder, that is a helper class that builds SQL query with a “joomla dbo” style:

VB.NET
req = New jsxRequest
Dim cmd As jsxCommand
Dim bld As New jsxSQLCommandBuilder

bld.Action = jsxCommandActionEnum.update
bld.Save = "updated"
bld.Table = "#__sqlxml_demo"
bld.Fields("name") = "Test_Changed"
bld.Where("id", ">") = 5

req.Commands.Add(bld.BuildCommand)
resp = jsxHelper.SendMessage(req)

Conclusion

This project shows you a VB.NET client that can quickly connect to the Joomla SqlXML component published in the first article.

Then, you can automate your Joomla tasks from VB.NET program, taking advantage of transaction management.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)