Welcome Guest ( Login | Register )



All times are UTC - 7 hours [ DST ]



Post new topic Reply to topic  [ 5 posts ] 
Author Message
 Post subject: Internal SQLite communication
PostPosted: Wed Feb 17, 2016 5:46 am 

Joined: Thu Feb 11, 2016 5:11 am
Posts: 10

Offline
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:
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.
         }
      }
   }
}


Last edited by liestol on Fri Jun 10, 2016 4:58 am, edited 1 time in total.

 Profile  
 
 Post subject: Re: Internal SQLite communication
PostPosted: Wed May 04, 2016 3:04 am 

Joined: Fri Jan 27, 2012 4:20 am
Posts: 166

Offline
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


 Profile  
 
 Post subject: Re: Internal SQLite communication
PostPosted: Wed Jun 08, 2016 1:35 pm 

Joined: Thu Nov 14, 2013 12:14 pm
Posts: 14

Offline
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.


 Profile  
 
 Post subject: Re: Internal SQLite communication
PostPosted: Fri Jun 17, 2016 7:23 am 

Joined: Wed Mar 09, 2016 3:53 am
Posts: 5

Offline
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");


 Profile  
 
 Post subject: Re: Internal SQLite communication
PostPosted: Fri Jul 22, 2016 10:55 pm 

Joined: Sun Jun 28, 2015 9:18 pm
Posts: 9

Offline
Thanks for the info
วิธีเล่นไพ่บาคาร่า


 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 5 posts ] 

All times are UTC - 7 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to: