Anyone Using the ADODB .NET Framework?

[smarlow]

Hello,

I am mucking around with the ADODB(7.0.3300.0) .NET library and was wondering if anyone else had tried it. I tried to write a simple snippet of code to return a list of tables in an Access database using the ConnectionClass constructor without much luck. I have attached a png of the diagram and an error message. The Open Method executes just fine, but the OpenSchema method, which is used to return a record set reference for the list of tables, fails. The OpenSchema method expects the Restrictions and SchemaID arguments as .NET object references, whereas in the ADO activeX library they are variants, and can be left unwired without errors. After researching the OpenSchema method a bit, it seems that the newer .NET version possibly requires a SchemaID other than NULL, but I have not idea where to obtain it. There is a CreateObjRef method in the ConnectionClass, but it too requires an Object ref as an input, which seems like a strange catch 22.

sm

[jdunham]

I am mucking around with the ADODB(7.0.3300.0) .NET library and was wondering if anyone else had tried it. I tried to write a simple snippet of code to return a list of tables in an Access database using the ConnectionClass constructor without much luck. I have attached a png of the diagram and an error message. The Open Method executes just fine, but the OpenSchema method, which is used to return a record set reference for the list of tables, fails. The OpenSchema method expects the Restrictions and SchemaID arguments as .NET object references, whereas in the ADO activeX library they are variants, and can be left unwired without errors. After researching the OpenSchema method a bit, it seems that the newer .NET version possibly requires a SchemaID other than NULL, but I have not idea where to obtain it. There is a CreateObjRef method in the ConnectionClass, but it too requires an Object ref as an input, which seems like a strange catch 22.

We use a .NET contructor for System.Data.SqlClient.SqlConnection. All our database transactions use that.

Jason

Edited May 17, 2011 by jdunham

[smarlow]

We use a .NET contructor for System.Data.SqlClient.SqlConnection. All our database transactions use that.

Jason

Thanks, Jason. Do you use this constructor with MS Access?

[John Lokanis]

Here are a few VIs and some C# code that might help you out. I do not recommend using LabVIEW to retrieve a record-set directly. There is a large penalty for using an enumerator across the LabVIEW<->.NET boundary. That is what the DLL fixes for you. There is also a fix in the VI for a bug in .NET 3.0 and earlier (it was fixed in 4.0 but we have to wait for LV2011 to use it). This involves strings that cross a memory boundary.

Open:

SQL.NET Open SQL Server Connection.vi

Execute SQL:

SQL.NET Execute Database Call.vi

Close:

SQL.NET Close Connection.vi

C# DLL and source:

C# SQL DLL.zip

The source of the C# solution to moving data from .NET to LabVIEW came from Brian Tyler (the former .NET guru at NI, now at MS). Take a look at his blog posts:

http://detritus.blog..._vs_arrayl.html

http://detritus.blog...ng_the_arr.html

Hope that helps!

-John

[smarlow]

Here are a few VIs and some C# code that might help you out. I do not recommend using LabVIEW to retrieve a record-set directly. There is a large penalty for using an enumerator across the LabVIEW<->.NET boundary. That is what the DLL fixes for you. There is also a fix in the VI for a bug in .NET 3.0 and earlier (it was fixed in 4.0 but we have to wait for LV2011 to use it). This involves strings that cross a memory boundary.

Open:

SQL.NET Open SQL Server Connection.vi

Execute SQL:

SQL.NET Execute Database Call.vi

Close:

SQL.NET Close Connection.vi

C# DLL and source:

C# SQL DLL.zip

The source of the C# solution to moving data from .NET to LabVIEW came from Brian Tyler (the former .NET guru at NI, now at MS). Take a look at his blog posts:

http://detritus.blog..._vs_arrayl.html

http://detritus.blog...ng_the_arr.html

Hope that helps!

-John

Thanks much, John

We use a .NET contructor for System.Data.SqlClient.SqlConnection. All our database transactions use that.

Jason

I believe that constructor may be for use with SQL Server only. I tried it and the "Data Source" keyword is not supported in the connection string, so you cannot specify the MS Jet driver. In the MS documentation I read, there are only references to MS SQL Server, and the error message I received said the Open method could not find the SQL Server. If I'm mistaken, and there is a way to connect to Access, let me know. Thanks again for your help.

sm

Thanks for your help everybody. I'll probably stick with the ActiveX ADODB library for now, since it seems the .NET interface isn't quite up to snuff.

sm

[John Lokanis]

If you need ODBC based access, try this code. It does not solve the record-set transfer issues, however, but you could adapt it using the techniques in my SQL server code.

ADO.NET.zip

Don't give up on .NET. it does offer a lot of advantages over COM.

Edited May 17, 2011 by John Lokanis

[smarlow]

If you need ODBC based access, try this code. It does not solve the record-set transfer issues, however, but you could adapt it using the techniques in my SQL server code.

ADO.NET.zip

Don't give up on .NET. it does offer a lot of advantages over COM.

Thanks, John. I will give it a try.

[SineQuaNon]

If you need ODBC based access, try this code. It does not solve the record-set transfer issues, however, but you could adapt it using the techniques in my SQL server code.

ADO.NET.zip

Don't give up on .NET. it does offer a lot of advantages over COM.

Do you know of any .Net ADO replacement for the NI DB Toolkit? I've looked but your post is about all I can find. We've been using it for years for Access and SQl Server.It gets the job done but is a bit slow in parts. We're being asked to use a .Net ADO driver for SQL Server/Access in our current project.

Is the SQL ADO driver provided 'faster' for SQL Server based calls over the NI DB Toolkit version?

Is LabVIEW 2010 SP1 any faster with .Net 3.5 than in previous posts in 2008 with .Net?

Thanks,

--Kevin

[John Lokanis]

Is the SQL ADO driver provided 'faster' for SQL Server based calls over the NI DB Toolkit version?

Is LabVIEW 2010 SP1 any faster with .Net 3.5 than in previous posts in 2008 with .Net?

The first example I posted that uses the C# DLL is way faster than the NI toolkit (at least the version I tested it against. They might have improved it since then).

You could easily adapt the ADO version to use the techniques in the SQL Server one. You would just need to modify the C# code to use the right assemblies. The key thing to the speed improvement is the way the recordset is returned to LabVIEW from the .NET memory space. One limitation of my version is all fields are treated as strings.

I have not seen a speed change in .net calls from LV2010. They did fix some stuff between 8.2 and 8.6 I think. Not sure where the change was done but it was really messed up before.

We have many 1000's of hours on this code with no problems. Just keep in mind the workaround in my code for the .net bug. If the SQL query length causes the string in .net to cross a memory boundary, .net will throw an error because it loses the null at the end of the null terminated string. New release of .net fixes this but LV2010 uses the old version. LV2011 *should* fix this.

good luck!