Internal SQLite communication

[liestol]

Hi,

I was wondering if anyone could give a few pointers as to how I can extract values from the interal SQLite database in a script module.

Which referenced assemblies do I need, and what is the connection string syntax for the interal db?

edit: Solved this while waiting ages for the post to be approved. If anyone else is interested, here's how I solved it:

Added the System.Data.SQLite dll in referenced assemblies. See code below for an example on how to read info from the DataLogger with SQL queries to the internal db.

Code: Select all

namespace Neo.ApplicationFramework.Generated
{
	using System.Windows.Forms;
	using System;
	//import Data.SQLite, Reflection for interaction with DataLogger.
	using System.Data.SQLite;
	using System.IO;
	using System.Drawing;
	using System.Reflection;
	using Neo.ApplicationFramework.Tools;
	using Neo.ApplicationFramework.Common.Graphics.Logic;
	using Neo.ApplicationFramework.Controls;
	using Neo.ApplicationFramework.Interfaces;
    
    
	public partial class SQLiteConnection
	{
		public void readSql(){		
			try {
				bool UsbPresent = Directory.Exists("\\Hard Disk");
				if(UsbPresent) {
					//set up connection to internal SQLite database.
					using (var myDb = new SQLiteConnection())
					{
						string executingPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().ManifestModule.FullyQualifiedName);
						myDb.ConnectionString = string.Format("data source={0}", Path.Combine(executingPath, "Database.db"));
						//open connection to SQLite database.
						myDb.Open();
						//select entire database table (DataLogger1) for readout, sorted by time of entry.
						string queryS = "select * from DataLogger1 order by Time asc";
					
						using (var command = new SQLiteCommand(queryS, myDb))				
						using (SQLiteDataReader reader = command.ExecuteReader())
						{
							//loop through all rows in SQLite database.
							while (reader.Read()) {
								//do whatever you wish with the data here (using reader[x] for data posititions).
								}
							}
						//close connection to SQLite database.
						myDb.Close();
					}
				else {
					//add actions to be run when USB is not found.
				}	
			}
			catch(Exception ex) {
				//any error handling is to be placed here.
			}
		}
	}
}

[wlederer]

Thank You very much for the post.
As the Version 2.20 SP1 of iX Developer doesn't have SQL Server CE (Microsoft discontinued support) and offers to use SQLite instead, my scripts do not work anymore. Can somebody advise, how to migrate?
regards, Wal

[onedumbtrucker]

I copied the original posters code, replaced a couple names with my own, added in my streamwriter output from my existing application and it worked in minutes.

Great example, thanks so much for showing the new method.

[Lars]

You can use the iX Api to read from the iX SQLite or SQL Server CE (This works from iX 2.20)
Here is an example of a scriptmodule.

namespace Neo.ApplicationFramework.Generated
{
using System.Data;
using Core.Api.Service;
using Neo.ApplicationFramework.Interfaces.Storage;

public partial class ScriptModule1
{
public static DataSet GetResultSet(string database, string selectStatement)
{
DataSet result = new DataSet();
result.Tables.Add(GetStorageCacheService().GetStorage(database).Query.SelectQuery(CreateDataTable,selectStatement));
return result;
}

private static IStorageCacheService GetStorageCacheService()
{
return ServiceContainerCF.GetService<IStorageCacheService>();
}

private static DataTable CreateDataTable(IDataReader reader)
{
var table = new DataTable();
table.Load(reader);
return table;
}

}
}

And heres an example of how to get a dataset from a datalogger.
DataSet data = ScriptModule1.GetResultSet(Neo.ApplicationFramework.Common.Constants.ApplicationConstantsCF.DatabaseName,"select * from DataLogger1 order by Time asc");