SQLite in a Navisworks plugin?

[luca_vezza]

Hi,

DId anybody ever try and succed linking SQLite (its .NET wrapper of course) in a NW plugin?

If yes, any hint on how to link it and make it visible as a valid DbProviderFactory?

Thanks,

   Luca

[ulski1]

Hi Luca
We have used SQLite from a Naviswork 2016 plugin for quite some time.
We have added the SQLite dlls to the dependency folder. It is the nuget version of SQLite we use.
Br
Ulrik

[luca_vezza]

Hi Ulrik,

great to know, so that means that there must be a solution; we're on NW 2017 but I guess things should work the same way.

Let me dig a bit into the details, then.

- NuGet: did you get just the core package or the full one (with Linq and Entity Framework)?

- did you explicitly add the DbProviderFactory to your app config file?

- about deployment, what and where did you have to copy together with your code? There are a few DLLs in that packaged and not sure if they go under your plugin folder or under the main Dependencies one.

Thanks a bunch, really.

   Luca

[ulski1 · Accepted solution]

I opened the project now to check. I see I included the whole thing SQlite, Core, EF6 and SQLite.Linq and ms EntityFramework. I'm not sure you need all that.

My Post-Build xcopy commands look like this:

IF EXIST "C:\Program Files\Autodesk\Navisworks Simulate 2016\Plugins\$(TargetName)\" rmdir /S /Q "C:\Program Files\Autodesk\Navisworks Simulate 2016\Plugins\$(TargetName)\"
xcopy /Y "$(TargetPath)" "C:\Program Files\Autodesk\Navisworks Simulate 2016\Plugins\$(TargetName)\"
IF EXIST "C:\Program Files\Autodesk\Navisworks Simulate 2016\Dependencies\System.Data.SQLite\" rmdir /S /Q "C:\Program Files\Autodesk\Navisworks Simulate 2016\Dependencies\System.Data.SQLite\"
xcopy /Y "$(TargetDir)EntityFramework.*" "C:\Program Files\Autodesk\Navisworks Simulate 2016\Dependencies\System.Data.SQLite\"
xcopy /Y "$(TargetDir)System.Data.SQLite.*" "C:\Program Files\Autodesk\Navisworks Simulate 2016\Dependencies\System.Data.SQLite\"
xcopy /Y "$(TargetDir)x64\SQLite.Interop.dll" "C:\Program Files\Autodesk\Navisworks Simulate 2016\Dependencies\System.Data.SQLite\x64\"
xcopy /Y "$(TargetDir)x86\SQLite.Interop.dll" "C:\Program Files\Autodesk\Navisworks Simulate 2016\Dependencies\System.Data.SQLite\x86\"

to do a query I do this:

try
{
using (System.Data.SQLite.SQLiteConnection dbConn = new System.Data.SQLite.SQLiteConnection())
{
dbConn.ConnectionString = SQLiteConnString;
dbConn.Open();
using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand())
{
cmd.CommandText = SQLCommand;
using (DataTable dt = new DataTable(DataTableName))
{
using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
{
dt.Load(reader);
}
ds.Tables.Add(dt);
}
}
}
}
}
catch (Exception ex)
{
//do something
}

[luca_vezza]

That's great, looks like it's working!

Thanks a lot, really,

     Luca

[luca_vezza]

As a side not, I did try what is suggested in the SQLite.Net guide, which is basically to add in your app config something like the following:

<configuration>
  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite"/>
      <add name="SQLite Data Provider" invariant="System.Data.SQLite"
           description=".NET Framework Data Provider for SQLite"           type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
    </DbProviderFactories>
  </system.data>
</configuration>

And that did NOT work. I've tried any possible combination (NuGet packages, libs downloaded directly in many different flavors, different deployment folders, ...) with no luck. No idea why, but I gave up as the approach above works just great.

   Luca

[Anonymous]

I guess, you created your own config file, but when Naviswork starts, it reads not your file, but its own "Roamer.exe.config" or so.

This is how .NET Framework works - you cannot have a config file for a dll, but only for the application.

You may try this (not completely relevant to your issue, as you are not using Entity Framework, though):

EF6, SQLite won't work without App.confg
https://stackoverflow.com/questions/43615926/ef6-sqlite-wont-work-without-app-confg/43688403#4368840...

[luca_vezza]

Ah, yes, that's probably why my config file was not found/loaded then.

So, at this point, to recap, can anybody suggest what is the cleanest approach to integrate SQLite? I mean, is is still the one suggested above, or is there a better one?

   Luca

[luca_vezza]

Still about SQLite and the internal NW DB...

Is there a way to know the schema of the internal database? What tables and what columns it has, I mean.

May sound like a dumb question, but I don't seem to be able to find any doc about that...

Thanks!

   Luca

[ulski1]

hi,

See https://www.sqlite.org/faq.html

(7) How do I list all tables/indices contained in an SQLite database

br

Ulrik

[ulski1]

my answer above was for SQLite - for the internal db there is properly a different way

Ulrik

[luca_vezza]

Yep, I was going to say that 🙂

Yes, I need to know the schema of the NW database. I have tried the GetSchema() method on the NavisworksConnection, but it raises an exception (method not implemented)...

Alternatively, if you know of an easy way to duplicate the internal DB to my SQLite one, without knowing its schema, well that would make it of course!

   Luca

[dgorsman]

No, the data in Navisworks files is not a "table and record" deal that you can access generically.  It's more of a tree structure (that's how it functions so quickly with large numbers of objects), and the only means to work with it is through the methods and properties exposed in the API.

[luca_vezza]

I knew that for the ModelItem standard params. But I though that the DB info (the one coming from CAD directly, in the form of a DB) is actually stored as an internal DB. And this seems to confirm that: http://adndevblog.typepad.com/aec/2013/07/document-database-part-1.html

So, are you saying that all the classes in Autodesk.Navisworks.Api.Data, which derives from the standard SQL stuff, are not intended to read those properties in any way?

Thanks.

    Luca

[Anonymous]

You can create tables within the DocumentDatabase.  I've created apps where I store things like SQL data connection information etc. When the model is saved the data is saved.  You can query the DocumentDatabase with Visual Studio or similar using the following: SELECT * FROM sqlite_master which will only return user created tables (from my experience).  You can also query temp tables using: SELECT * FROM sqlite_temp_master.  This, by default, only appears to return NW_undo and NW_redo temp tables.

I've been racking my brain and searching the net for any other information on this to no avail.

All the best,

Craig

[ulski1]

We use this to check for the existance of a table in sqlite and it works for normal sqlite tables - note that we do this while we have added sqlite from nuget into the solution and added the sqlite dll's to the Navisworks dependencies subfolder:
try
{
using (System.Data.SQLite.SQLiteCommand cmd = connection.CreateCommand())
{
cmd.CommandText = @"SELECT name FROM sqlite_master WHERE type = 'table' AND name = @Anonymous";
cmd.Parameters.Add("@name", DbType.String).Value = MyTableName;
if (cmd.ExecuteScalar() != null)
{
RetBool = true;
}
}
}
catch (Exception)

br
Ulrik

[alexisDVJML]

I use Navisworks Embedded SQLite database to store various things that I want to be saved into the file.
Navisworks extract this as temp files in C:\Users\(user)\AppData\Local\Temp\FileDatabase_xxxxx_xxxxxxxx.db"

To find actual file name you can use example code below:

		static internal string GetDBPath(Document doc)
		{
			using (NavisworksConnection nwDbConnection = doc.Database.Value)
			{
				if (nwDbConnection.State != System.Data.ConnectionState.Open) return string.Empty;
				using (NavisworksCommand nwCmd = new NavisworksCommand("PRAGMA database_list;", nwDbConnection))
				using (NavisWorksDataReader nwDataReader = nwCmd.ExecuteReader())
				{
					if (!nwDataReader.HasRows) return string.Empty;
					nwDataReader.Read();
					Debug.Assert(nwDataReader.GetString(1) == "main");
					return nwDataReader.GetString(2);
				}
			}
		}

You can then directly use a tool like DB Browser for SQLite 😉
But basically this database is empty except if you put things into it.
System tables seem to be kept in memory and used only for redo/undo.

PS: Now that I think about it,  we can even extend above piece of code to programmatically open the correct file into DB Browser for SQLite 😁  ... I will add this to my development plug-in...