PROVISIO DevBlog

Using the SiteRemote Database from other Applications

In case you are using your own SiteRemote Server you are able to access the valuable data of the SiteRemote database from other applications. The SiteRemote database includes a great number of information that is useful for other purposes as well, such as address information, software and hardware information etc.

The SiteRemote database utilizes the common Microsoft SQL engine, so start with having a look at the SiteRemote database tables by using the Microsoft SQL Management Studio and connect to your SiteRemote Server. You will see that most of the tables are using pretty obvious names.

You can use the default coding methods of your preferred programming language to access the database, either locally or remotely. Make sure that you have a database user with appropriate user rights and that you configured the SQL database access according to the Microsoft SQL Server documentation.

A simple C# code for such an SQL query, that finds all terminals that are in the time zone with idx 72 (GMT+01:00, you will find the time zones in the table tblTimeZone) and writes them into a text file, can look like this:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SRdbAccess
{
    class Program
    {
        static void Main(string[] args)
        {
            //Required information to connect to the database.
            SqlConnection obj_sqlconnection = new SqlConnection("Server=127.0.0.1;Database=SiteRemoteBackEndServer;User Id=TheUserName;Password=TheUserPassword;");
		    
            //Query that retrieves all terminals that are in a specific time zone.
            string str_sqlquery = "SELECT * FROM tblTerminal WHERE TimeZone_Idx = @Timezone";
		    
            //Build the SQL command.
            SqlCommand obj_sqlcommand = new SqlCommand(str_sqlquery, obj_sqlconnection);
		    
            //Look for terminals from time zone with idx 72, which is GMT+01:00. You will find the time zones in the table tblTimeZone.
            obj_sqlcommand.Parameters.AddWithValue("@Timezone", 72);
		    obj_sqlconnection.Open();

            //Use the data reader to show the display name of all terminals that are from the same time zone and write them to a file.
		    SqlDataReader obj_sqldatareader = obj_sqlcommand.ExecuteReader();
            using (System.IO.StreamWriter obj_filetowriteto = new System.IO.StreamWriter(@"C:\Users\Public\Documents\SiteRemoteSQLQueryResult.txt", true))
            {
                while (obj_sqldatareader.Read())
                    obj_filetowriteto.WriteLine(obj_sqldatareader["DisplayName"]);
            }

            //Close the data reader after the read process.
            obj_sqldatareader.Close();

            //Close the connection to the database.
		    obj_sqlconnection.Close();
        }
    }
}

The following screenshot shows you an example result of the above query. The search returns the display name of the three terminals that are in the time zone with idx 72: