SQLite implementation

This forum is meant for examples of X# code.

User avatar
OhioJoe
Posts: 131
Joined: Wed Nov 22, 2017 12:51 pm
Location: United States

SQLite implementation

Post by OhioJoe »

Trying to compile in XIDE. Getting the following build error:

error XS0009: Metadata file 'C:XIDEAssembliesSQLite.Interop.dll' could not be opened -- PE image doesn't contain managed metadata.

I'm acting upon the advice offered by Wolfgang in another post. In which he said I need to include the following:

SQLite.Interop.dll
System.Data.SQLite.dll

As the error above indicates, I've copied them into the XIDEAssemblies folder

The ultimate goal here is to build an app that works with all SQL dialects. Buf first I'm trying to get it working in an environment that leaves the user free of having to install a separate database server. Hence the choice for SQLite.

Any advice out there? Thank you.
Joe Curran
Ohio USA
User avatar
robert
Posts: 4292
Joined: Fri Aug 21, 2015 10:57 am
Location: Netherlands

SQLite implementation

Post by robert »

Joe,
You only need to include System.Data.SQLite as a reference. The other DLL must be copied to the EXE folder but is not a .Net DLL but a "normal" C++ DLL.

Robert
XSharp Development Team
The Netherlands
robert@xsharp.eu
User avatar
OhioJoe
Posts: 131
Joined: Wed Nov 22, 2017 12:51 pm
Location: United States

SQLite implementation

Post by OhioJoe »

I eliminated the reference to SQLite.Interop.dll. Now it compiles
When I run the application, it says that I need an ODBC connection, which is what I'm trying to avoid. (To achieve the fewest possible user-setup requirements.)
Here's my connection syntax, originally written in VO:

Code: Select all

	
oDatabase := cDBFile  // "joe.db"
SQLConnectErrorMsg( TRUE )  
oConnection := SQLConnection{"SQLite3", "", "" }	    
oConnection:connect()

// and then later on:
cStatement := "ATTACH DATABASE '" + cDatabase+ "' AS joedata" 
oStatement := SQLStatement{cStatement , oConnection}
oStatement:Execute()
oStatement:FreeStmt( SQL_DROP )

What is the correct way to connect?
Joe Curran
Ohio USA
User avatar
robert
Posts: 4292
Joined: Fri Aug 21, 2015 10:57 am
Location: Netherlands

SQLite implementation

Post by robert »

Joe,
This code seems to use the VO compatible SqlConnection and SqlStatement.
You will have to use the Ado.Net compatible classes:

Code: Select all

VAR db := SQLiteConnection{"Data Source="+cDatabase+";Version=3;"}
db:Open()  
USING VAR cmd := SQLiteCommand{"SELECT * from Table", oConn}
USING VAR rdr := cmd:ExecuteReader()
DO WHILE rdr:Read()
   ....
ENDDO
XSharp Development Team
The Netherlands
robert@xsharp.eu
User avatar
OhioJoe
Posts: 131
Joined: Wed Nov 22, 2017 12:51 pm
Location: United States

SQLite implementation

Post by OhioJoe »

Robert, you also mentioned that SQLite.Interop.dll is to be placed in the EXE folder. Is there a line where this is to be referenced in the app, such as
LoadLibrary("SQLite.Interop.dll")
?
Thank you, Robert.
Joe Curran
Ohio USA
User avatar
Chris
Posts: 4599
Joined: Thu Oct 08, 2015 7:48 am
Location: Greece

SQLite implementation

Post by Chris »

Joe, no need at all to load it in your code manually yourself. I assume this dll is used automatically from inside the code in the System.Data.SQLite.dll
Chris Pyrgas

XSharp Development Team test
chris(at)xsharp.eu
User avatar
lumberjack
Posts: 723
Joined: Fri Sep 25, 2015 3:11 pm
Location: South Africa

SQLite implementation

Post by lumberjack »

Hi Joe,
OhioJoe wrote: The ultimate goal here is to build an app that works with all SQL dialects. Buf first I'm trying to get it working in an environment that leaves the user free of having to install a separate database server. Hence the choice for SQLite.
Any advice out there? Thank you.
If you want to save yourself a lot of effort you need to go the DbProviderFactories route. Here is some code that should get you on track:

Code: Select all

    STATIC METHOD GetConnection(sDb AS STRING) AS DbConnection
        LOCAL cs AS ConnectionStringSettings
        LOCAL oConn AS DbConnection
        cs := ConfigurationManager.ConnectionStrings[sDb]
        oConn := DbProviderFactories.GetFactory(cs:ProviderName):CreateConnection()
        oConn:ConnectionString := cs:ConnectionString
    RETURN oConn

    STATIC METHOD GetData(sDb AS STRING, sComm AS STRING) AS STRING
        LOCAL obj AS OBJECT
        LOCAL oConn AS DbConnection
        LOCAL oComm AS DbCommand
        LOCAL oTran AS DbTransaction
        LOCAL oRead AS DbDataReader
        LOCAL ret, sDel AS STRING
        oConn := PsionUtils.GetConnection(sDb)
        oConn:Open()
        oComm := DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings[sDb]:ProviderName):CreateCommand()
        oComm:Connection := oConn
        oComm:CommandText := sComm
        oTran := oConn:BeginTransaction()
        oComm:Transaction := oTran
        oRead := oComm:ExecuteReader()
        ret := ""

        WHILE oRead:Read()
            IF ret:Length > 0
                ret += ";"
            ENDIF
            sDel := ""
            FOR LOCAL col := 0 AS INT UPTO oRead:FieldCount -1
                ret += sDel + oRead:GetValue(col):ToString()
                sDel := ","
            NEXT
        ENDDO
        oRead:Close()
        oTran:Commit()
        oConn:Close()
    RETURN ret

    STATIC METHOD DataRead(sDB AS STRING, sComm AS STRING) AS DataTable
        LOCAL oConn AS DbConnection
        LOCAL oComm AS DbCommand
        LOCAL oTran AS DbTransaction
        LOCAL oRead AS DbDataReader
        LOCAL oDT AS DataTable
        oConn := PsionUtils.GetConnection(sDB)
        oConn:Open()
        oComm := DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings[sDB]:ProviderName):CreateCommand()
        oComm:Connection := oConn
        oComm:CommandText := sComm
        oTran := oConn:BeginTransaction()
        oComm:Transaction := oTran
        oRead := oComm:ExecuteReader()
        oDT := DataTable{}
        oDT:Load(oRead)
        oConn:Close()
        oConn:Dispose()
    RETURN oDT

    STATIC METHOD PutData(sDb AS STRING, sComm AS STRING) AS INT
        LOCAL oConn AS DbConnection
        LOCAL oComm AS DbCommand
        LOCAL oTran    AS DbTransaction
        LOCAL ret := 0 AS INT
        LOCAL stmt AS STRING[]
        oConn := PsionUtils.GetConnection(sDb)
        oComm := DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings[sDb]:ProviderName):CreateCommand()
        oComm:Connection := oConn
        oConn:Open()
        oTran := oConn:BeginTransaction()
        oComm:Transaction := oTran
        stmt := sComm:Split(";":ToCharArray(), StringSplitOptions.RemoveEmptyEntries)
        FOREACH s AS STRING IN stmt
            oComm:CommandText := s
            ret += oComm:ExecuteNonQuery()
        NEXT
        oTran:Commit()
        oTran:Dispose()
        oComm:Dispose()
        oConn:Close()
        oConn:Dispose()
    RETURN ret
END CLASS

**ApplicationName.exe.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <system.data>
        <DbProviderFactories>
            <add name="Npgsql Data Provider" invariant="Npgsql" description=".Net Framework Data Provider for Postgresql" type="Npgsql.NpgsqlFactory, Npgsql" />
            <!--    /-->
        </DbProviderFactories>
    </system.data>
    <connectionStrings>
        <add name="logtrack" providerName="System.Data.OleDb" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:VIDEProjectsvisualSystemApplicationspsionlogtrackssc.mdb;"/>
        <add name="ddframework"  providerName="Npgsql" connectionString="Server=127.0.0.1;Port=5432;Database=visualframe;User Id=postgres;password=************" />
    </connectionStrings>
</configuration>
Let me know if there is something you don't understand.

HTH
______________________
Johan Nel
Boshof, South Africa
User avatar
OhioJoe
Posts: 131
Joined: Wed Nov 22, 2017 12:51 pm
Location: United States

SQLite implementation

Post by OhioJoe »

Thank you. Yes, this fits!
Just started with the reading about ADO.Net
Included there is a section about DBProviderFactories
Hopefully my experience here will help those who've been stuck in the DBF world, who want to create a version of their feature-rich application to an expanded user base, but who don't know where to start.
Joe Curran
Ohio USA
User avatar
lumberjack
Posts: 723
Joined: Fri Sep 25, 2015 3:11 pm
Location: South Africa

SQLite implementation

Post by lumberjack »

Hi Joe,
OhioJoe wrote:Thank you. Yes, this fits!
Just started with the reading about ADO.Net
Included there is a section about DBProviderFactories
Hopefully my experience here will help those who've been stuck in the DBF world, who want to create a version of their feature-rich application to an expanded user base, but who don't know where to start.
Feel free to contact me if you need any assistance.

Regards
______________________
Johan Nel
Boshof, South Africa
User avatar
OhioJoe
Posts: 131
Joined: Wed Nov 22, 2017 12:51 pm
Location: United States

SQLite implementation

Post by OhioJoe »

Robert / Johan:
(Just now getting back to this. Sorry for the delay.)
What assemblies / references are required to implement your code?
When I tried Robert's sample ...

Code: Select all

VAR db := SQLiteConnection{"Data Source=c:frfptest.db;Version=3;"}
db:Open()  
USING VAR cmd := SQLiteCommand{"SELECT * from Table", db}
USING VAR rdr := cmd:ExecuteReader()
WHILE rdr:Read()
ENDDO          
... I got the following error:

Code: Select all

error XS0246: The type or namespace name 'SQLiteConnection' could not be found (are you missing a using directive or an assembly reference?)
Thank you for your help
Joe Curran
Ohio USA
Post Reply