[Imported] C# How to get a list of the SQL tables

[sbeadle]

>>Message imported from previous forum - Category:Scripts and Tips<<
User: geoffpatton, originally posted: 2019-01-17 20:40:57 Id:350
We are making a C# program to retrieve some history using SQL Queries through a ODBC connection.
We want to be able to read into our program the Historic View tables so the user can make a selection of which View to use.

We have the Select statements working so the connection is good. We have not gotten anywhere figuring out getting the list of tables.

QueryPad will give us this list when we put this in it
?TABLES HistoricView %

Anyone done this before?

[sbeadle · Accepted solution]

>>Responses imported from previous forum

Reply From User: tfranklin, posted: 2019-01-17 21:32:25
I've never successfully pulled these from a query against ClearSCADA, but you can definitely get the information if you pull it out of the registry using something like reg query.

Reply From User: geoffpatton, posted: 2019-01-17 21:32:29
We found what we needed. Just the first time we tried this we did something wrong.
_conn.GetSchema("Tables")

public IList ListTables()
{
List tables = new List();
DataTable dt = _connection.GetSchema("Tables");
foreach (DataRow row in dt.Rows)
{
string tablename = (string)row[2];
tables.Add(tablename);
}
return tables;
}

Edit: I should give credit to where we found this.
https://stackoverflow.com/questions/3005095/can-i-get-name-of-all-tables-of-sql-server-database-in-c...

Reply From User: BevanWeiss, posted: 2019-01-21 20:25:23
If you're using an ODBC connection then you can also pass in filters to the GetSchema call..
.GetSchema("Tables", new string[] { null, "HistoricView" }) will retrieve just the historic views.

If anyone knows a way to do this using the .NET API instead of the ODBC connections I would be very greatful to hear about it.

Reply From User: geoffpatton, posted: 2019-01-22 00:19:13
Thanks Bevan, I'll pass this on and we will see if we like one way over the other. For this program we intend on using it with more than one version so we specifically did not want to use the API. For the API you usually have to update the dlls and recompile to match the version of ClearSCADA. That can be a pain especially now with the monthly updates.

Reply From User: BevanWeiss, posted: 2019-01-22 20:24:33
Hi Geoff,
There's only really two versions (for both the .NET API and the Automation Interface)... Pre-2014R1, and =2014R1

So for the first version, you just use 2013R2.2 DLLs and it supports pretty much everything until 2017R2 when the Secure Connection was enabled by default.
For the later versions you use the latest DLLs (2017R3) to ensure optimum support.

We have a product we sell (an Add-In for Excel which allows easy access to ClearSCADA data via Excel formulas) which uses the .NET API and haven't had any issues with compatibility (beyond those listed at the start of this message).

Reply From User: geoffpatton, posted: 2019-01-23 02:00:03
Bevan,
Hum I know we have some programs and drivers that we always have to update. We must be using some other dlls also or something.
Our main programmer passed away last year so while we have the code he created (well most of it) we still lost the knowledge of why he did some things. Also his commenting was about zero.

I only dabble in the C# programming I mostly stay in the SCADA systems we work with. Our current main programmer is still learning some of those things and we have a few products he works on other than ClearSCADA programs. He also does all our heavy lifting SQL stuff.

Reply From User: BevanWeiss, posted: 2019-01-24 19:41:36
Things that use the Simple DDK are a different story, and annoyingly they do need to be recompiled with each version.
But the 'client access' DLLs are as I described above.

Who needs code comments anyway 😛
We find that C# is the easiest language to develop in, the very minor performance impact isn't really important anymore, and the ease of coding and reduction in memory management 'bugs' is awesome. It is a great programming language.

Reply From User: geoffpatton, posted: 2019-01-25 18:15:15
Ah yes the simple DDK rings a bell.
I try to comment code but I sometimes forget to. When your up against a deadline commenting becomes less important.