The Basics of Integration between Business Vision Essentials and Magento in C#

Here at Demac we have a few clients now that enjoy using Business Vision Essentials (BVE). It’s a full fledged ERP system and it’s pretty robust in what it can do. Between Business Vision and Magento we can sync inventory, price and sales. That’s just the short of it. It doesn’t sound like a lot, but there is a lot happening in those three sects of an ERP system. But before that, we need to connect to your remote Business Vision Install.

Connecting

Before writing any code you’ll need to get your hands on the Pervasive ADO.NET Provider and add the reference to your project [Pervasive.Data.SqlClient]

Once this has been referenced it’s time to start coding!

To start off – the connection string:

var connectionString = "Server Name=[ip address];Database Name=[Physical directory of database]"

In best practice it’s good to put variables such as connection string in a file that’s much more open to editing (ie app.config).  There may be times where you need to change the connection on the fly and it’s really handy to have this easy to edit without needing to edit your code and recompile.

But security!!!” I hear you scream. Don’t worry, your server has a strong firewall and ip-blocking with your staging and production server ips whitelisted, right? 😉

Pretty self explanatory.

After this, veterans of connecting to MS SQL through C# will find the rest of the code to be eerily similar.

var conn = new PsqlConnection(ConnectionString);
var cmd = new PsqlCommand();

To those new to Pervasive Sql and MS SQL this may seem a bit confusing so I’ll explain. The PSsqlConnection variable is ONLY used for opening the connection. Once the connection is open…

conn.Open();
cmd.Connection = conn;

the PSqlCommand takes over the connection. The PSqlCommand is what is used to execute any queries you want to pass. PSqlConnection is only used to open the connection. However, it’s important to be aware that the presence of this variable is still important. These two variables work in tandem with each other. If you .Close() the connection the query won’t execute.

An important argument to set on PsqlCommand is the CommandType

cmd.CommandType = CommandType.Text;

This tell Pervasive what kind of command you’re actually sending to it. You’ve got three options in this enumerable.

CommandType.Text;
CommandType.StoredProcedure;
CommandType.TableDirect;

In most cases you’re likely to use Text, which is just passing in your average query.

One other argument you may use is setting the Parameters

var exampleParameter = new PSqlParameter("@ExampleParameter", "Parameter Value");
var exampleParameterTwo = new PSqlParameter("@ExampleParameter2", "Another Value");
cmd.Parameters.Add(exampleParameter);
cmd.Parameters.Add(exampleParameterTwo);

The order in which you add your parameters is crucial, I’ll explain why below.

Let’s say you need to insert these two values into a new row in a table called ExampleTable:

var psqlQuery = "Insert Into ExampleTable ( ExampleColumn, ASecondExampleColumn ) Values (?,?)"

Whaaaaaaat? What? What is this… this trickery?

Yes this looks extremely confusing, but it’s actually quite simple. To see the bigger picture let’s recap all our code that we’ve used so far. I’m also going to add one more line of code that will complete the picture.

var connectionString = "Server Name=[ip address];Database Name=[Physical directory of database]"
conn.Open();
cmd.Connection = conn;
var conn = new PsqlConnection(connectionString);
var cmd = new PsqlCommand();
cmd.CommandType = CommandType.Text;

var exampleParameter = new PSqlParameter("@ExampleParameter", "Parameter Value");
var exampleParameterTwo = new PSqlParameter("@ExampleParameter2", "Another Value");
cmd.Parameters.Add(exampleParameter);
cmd.Parameters.Add(exampleParameterTwo);

var psqlQuery = "Insert Into ExampleTable ( ExampleColumn, ASecondExampleColumn ) Values (?,?)"

cmd.CommandText = psqlQuery;

As you can see PSqlCommand is the control center for your query. In this class we have:
1) The connection class (already opened)
2) All your parameters you’re passing through
3) Your Sql Query

To explain the odd question marks used in the sql Query, the ?’s actually represent what PSqlCommand is holding in its Parameters argument. This is why it’s so crucial to make sure you add your parameters in the correct order.

What’s left to do?
Execute the query!

var result = cmd.ExecuteNonQuery();

This returns a datatype of int.
Now that all that hard work is done, it’s time to let our PSql Classes take a rest.

cmd.Dipose();
conn.Close();
conn.Dispose();

This is actually a very crucial and often overlooked step. If you never close the connection, it remains open, if your connections are remaining open… well… find out for yourself if you’d like, but it’s nothing short of trouble.

Now what if a big error occurs during the preparation or execution of your query? Will the connection remain open?? Yes. It will. Don’t worry, I’ve got your back.

var connectionString = "Server Name=[ip address];Database Name=[Physical directory of database]"
var conn = new PsqlConnection(connectionString);
try
{
	conn.Open();
	cmd.Connection = conn;
	var cmd = new PsqlCommand();
	cmd.CommandType = CommandType.Text;

	var exampleParameter = new PSqlParameter("@ExampleParameter", "Parameter Value");
	var exampleParameterTwo = new PSqlParameter("@ExampleParameter2", "Another Value");
	cmd.Parameters.Add(exampleParameter);
	cmd.Parameters.Add(exampleParameterTwo);

	var psqlQuery = "Insert Into ExampleTable ( ExampleColumn, ASecondExampleColumn ) Values (?,?)"
	cmd.CommandText = psqlQuery;
}
finally
{
	cmd.Dipose();
	conn.Close();
	conn.Dispose();
}

This is only the beginnings of learning PSql. PSql has very set and defined tables and columnnames that interact with its frontend to deliver the data. We here at Demac, have built out robust methods and classes that help us efficiently getting integration up and running efficiently between our systems. Every client has a different need however, and we’re always ready to tackle the next big challenge.