.NET Dataset

[NickRH]

Hi all,

Can anyone tell me how to pull the data from a `Table` within a `Dataset` in .NET (System.Data(4.0.0.0))

I can see the data is loaded into the dataset but can`t find a method or property to retrieve it.

Thanks.

[wiebe@CARYA]

Post what you have. Will save everyone precious minutes... 

[NickRH]

Hello wiebe@CARYA,

I`ve attached the VI. Its from Labview 2017 64bit.

Also, I have just had a bit of success. You will see in the code that i've managed to output the data to an xml file, at least I can see the data now...

Just don`t want to save out to file then read back in .....

[ikaiser]

Hello Nick an Wiebe,

Here is example code I am currently working on. It retrieves all information stored in a DataTable that came from an SQL query.

This code is working, however it's work in progress. I am currently installing a new MySQL server to be able to wireshark the connection to verify it is actually doing exactly what I am expecting.

[wiebe@CARYA]

Hmm. IIRC, the ADO .NET  API returns a DbDataReader object, or at least that's what I'm using. So I don't get (or didn't use) a dataset object. So I can't provide a dataset solution OOTB.

I went through some effort to avoid the conversion to string. I convert to variants, so the binary data stays valid. This is a huge benefit, as it avoids all those problems with quotes in strings, "," or "." and resolution in floats, and formatting of data\time data. Now we have .vims the VIs will eventually get a type input, so the To Variant phase can be skipped, and the data is returned\entered as intended.

[NickRH]

Hi Ingo,

Looks very promising, I was originally looking at using Datasets and tables to pull the data in a decoupled fashion for my system. I only ever read data for configuration at start of test, this does not change during test, so i`m not too concerned with having a live data stream. At the end of test the results are collated and passed back to the database in one write.

I will also try to use the dataset in my application. Its going to be interesting to update the dataset if required.

Cheers,

Nick

[wiebe@CARYA]

---

@ikaiser wrote:

---

BTW. what assembly is that? I have a DB class with specializations for SQLServer, Access and ODBC, but wouldn't mind a MySQL implementation.

[NickRH]

Try here ;

https://dev.mysql.com/doc/index-connectors.html

[ikaiser · Accepted solution]

So,

Here is the promised full code. I created this example for Nick. What is special about this code is, that it does not rely on LabVIEW Database Connectivity Toolkit; due to MS drivers used in that code that only exist in 32-bit, this toolkit is not available for LabVIEW 64-bit. Therefore using e.g. .NET is necessary to connect to a database with LabVIEW 64-bit.

Nick was using a MySQL database (see his VI attached above), however my example code should work with MS SQL and other SQL database systems in a similar fashion.

For this example, I was using MySQL Community Server 8.0.12, MySql.data.dll from MySQL Connector/NET 8.0.12, and the example database "Sakila" that comes with the server.

My examples convert all data to strings, as this way it is possible to dynamically define a query. Only using a predefined query with known result column types one can define the type(s) to use during edit-time of the VI. To come around this limitation, one can of course use the variant data type.

Attached are two different versions; one that iterates through all results on the server, and one that writes the query results to a DataTable and transfers this table to the client all at once. Both are attached as LabVIEW 2017 and 2014 versions.

Keep in mind that this is example code I created, so it's mostly untested. Do not use it in production environment or with sensitive/important data before testing it thoroughly!

[ikaiser]

---

wiebe@CARYA wrote:
BTW. what assembly is that? I have a DB class with specializations for SQLServer, Access and ODBC, but wouldn't mind a MySQL implementation.

I just notice I had only answered your question via a comment in my VI, but not explicitly. It's probably easier for everyone finding this thread later to have it here: I am using MySql.data.dll from MySQL Connector/NET. I used version 8.0.12.