---

If you are going to install BioStar 2 to the customer site that has a large number of users or its own ERP and payroll system, one of the questions you will most likely encounter is how they can make a connection with their existing systems to BioStar 2. The quick simple answer for this question is to make a database link between the systems, because it’s one of the easiest and common ways.

BioStar 2 uses SQLite which is known as simple and light database type that can be embedded in an application, similar to library files. So, it does not need to install independent DBMS(Database Management Server) and manage a DB instance.

If you have an experience with BioStar 1.x, you can remember that the DBMS installation itself is heavily dependent on the system environment. So, the initial installation itself could be a burden to the installers who are not very familiar with database management.

On the contrary, SQLite is quite simple embedded type of database. It does not need to control a connection between server and database, and it also does not need to download SQL patch files to troubleshoot when DBMS installation is not finished properly.

Then, how can it be connected and linked with another DBMS as we do with BioStar 1.x?

Fortunately, SQLite also has similar architectures and inherited relational database structures. Therefore, it’s possible to make a DB link middleware program, and connect it to another database.

In order to make a DB link application with SQLite, it’s necessary to take the below preparation steps.
I’ve used C# to carry out a relatively simple teat. So, please note that all the following reference images are based on the C# programming.

1. Check your programming environment and install SQLite library package according to it from the below SQLite download page.
http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

Also, please check your .Net framework version and make sure to install the right version. You can check your .Net framework version by referring to your local PC folder where Microsoft.Net framework files are located.

(e.g.) C:\Windows\Microsoft.NET\Framework64

2. Locate the package file to the right location, and add it to the project ‘References'

3. Make sure to use the right Platform to run compiler.

Now, we are ready to make a programming to implement the middleware application. In order to make a DB connection to the SQLite, it’s necessary to refer to the DB table information. From BioStar 2.1 version, BioStar.db.log.bk file is used for the log event recording. Please refer to the latest table description.

And, with the db table, the log table is changed to be split to store log files monthly basis from BioStar 2.1v.

(Log table)

(View from SQLite)

I’ve made a DB connection to the latest DB table and used SQLiteCommand to use execute reader command.

On the other side, I’ve made a DB connection to the SQL server, and made commands to insert the BioStar 2 DB log data to the SQL DB table.

And lastly, I’ve gave it Thread. Sleep with While loop. So, it can make a retry at a certain time interval.

[Console pop-up to show the update status]

[The connected SQL Table for the data base input]

Additionally, I need to make supplement logics to make the application more precisely inserting the data to the db table without overlapped event logs. I believe you can create this and the rest part and tune it for more specific use according to the project environment.