ADO vs. ADO.NET vs. Database Toolkit vs. LabSQL

[10Degree]

Gang,

I am working through some initial design issues with database access in LabVIEW.  I have found and messed with ADO.NET, the NI Database Toolkit (which uses ActiveX Data Object - ADO), and the LabSQL vi's (also ADO) provided by Chris Roth and Jeffrey Travis (http://jeffreytravis.com/lost/labsql.html).  I am looking for any advice on selection or use of database access tools.

I am accessing databases hosted on SQL server 2000.  (LV8.5.1, XP, Vista, Windows)

My initial run with ADO.NET seems to be functional but very s..l.....o..w.  I am using the system.data.sqlclient namespace.  The basic code goes like this:

1. Create and open SqlConnection
2. Create SqlCommand
3. ExecuteReader
4. Use Read() method to traverse the SqlDataReader record-by-record
5. Extract each field value in the data set.

My code appears in ADONET.png.  (I have left out some of the garbage collection stuff - cleanup).  When this code runs, it takes about 14-sec to compile the string array.  When I execute similar code using the Database Toolkit, it takes about 2 seconds, and with LabSQL, it takes less than a second.

I would like to continue to work with .NET in LabVIEW, but is seems that the interop between LV and .NET is clunky and slow.  The lack of automatic garbage collection and ramifications (memory leaks and orphaned references) are not always obvious, and the interface between data types and casting issues seems to be problematic as well.  Unfortunately, I believe that ADO will eventually go away and Microsoft will continue to support .NET rather than ADO.

Any opinions / observations / comments on code?  Your professional insite would be much appreciated!

Thanks,  -cb

[smercurio_fc]

"clunky and slow" is a pretty good way to describe the LabVIEW <-> .NET interface. I'm sorry, NI, but it just is. LabVIEW is not a .NET language, so this is bound to be a side-effect. The part in your code that's probably a good chunk of your time is the for-loop. Each call into the "get_item" method is expensive in terms of time. I also have code that interfaces to SQL Server 2000, but in my case I wrote a C# class that acts as an interface between SQL Server and other programs. This is primarily to provide business objects more than anything else. Thus, you can create a C# class to handle the database transactions, and return simple datatype to LabVIEW.

As for future support from Microsoft on ADO, I can't really say what's going to happen there, other than the history that Microsoft has of supporting old technologies until long after they've been tagged as "obsolete". Jet, anyone?

[Wiebe@CARYA]

I'd use ADO, and rewrite it if it ever becomes obsolete. Like smercurio
said, once it's obsolete, it will be supported for years and years (, so
you'll have plenty of time to rewrite it). And even then, someone will
probably write a .net wrapper to simulate the existing ADO functions.

Btw. you can also access ODBC though API calls to ODBC32.dll... Always nice
to have options. I wouldn't recommend it though.

I have very good experiences with LabSQL. I started using it years ago, and
found it a great benefit that I could remove bugs myself. By now all the
bugs are gone.

Regards,

Wiebe.

[10Degree]

Thanks for the advice.  I think I was starting to lean toward LabSQL - seems like a nice interface to ADO and open-source as well.  I am curious about the speed issue in .NET and wonder if I am using it wrong.  That will be another spike some other time.

Thanks, -cb

[smercurio_fc]

The only "wrong" thing that you're doing in your code is that you're not closing the references to the objects that get created. For example, in the for-loop the individual objects that are created by the "get_item" method are not closed. The same holds for just about all the references that you create. This would cause a memory leak.

The speed issue has been brought up before. This is likely to improve with subsequent LabVIEW versions. I hope.

[MrQuestion]

I see that this thread is very old, but I wanted to toss this out there,,,

ADO.net in labview is very fast and efficient. 

Looking at your code I see that you are only using the 'connection' and 'dataAdapter' within the ado.net framework.

Technically I don't think you are even using ADO.net in your code.

Instead you are using the data adapter that ADO.net would use in it's framework.

You are iterating reads via a data adapter command.

This is a very bad idea, and will produce horrible results.

The real power behind ado.net is the usage of the ADO Dataset and ADO Datatables.

What you should do is create an ADO dataset and then execute that command with a fill operation to a local dataset.

From that local dataset you then perform your data reader operation. 

In your code i'm not really sure what the SQlDataReader would do,,,

I think it will query the SQL server, but the results will not be stored on your machine. 

So, as you iterate through each line of the reader it will send a request back to the SQL server for the next line in the data stream.

ADO.net has many cool background buffering services that just works without you really paying attention to the details.

For example you can create an ADO table reader and then execute a table command. 

From my experiences ADO has the data ready faster than what LabVIEW can process it.