Starting out with vb.net and sqlite3...

[zehd]

Well, I'm starting using VB .net. One of the things that I think I will like is that the code is translatable to C# and back. (I figure going from VB6 to C# would hurt too much)

I'm terrible at starting all over, tonnes of questions and a bit lazy ("man, I already know how to do that, why do I have to learn THAT again!!!!)

Anyway, I'd like to hook into the System.Data.SQLite.dll from the gbpvr folder. (Finally I can use the same stuff as you guys)

BUT

I checked out the creator's web site and can't seem to find docs for hooking into it.

By the way, is that actually called 'binding'.... (I'm so embarrassed. I'm such a hacker)

[zehd]

Never mind

[sub]

I dont have a convenient ready to use bit of code to cut and paste for you, but here is some bits that might piece together for something useful:

Setup DBProviderFactory (useful if you intend to introduce support for different DB types in future)

Code:

DbProviderFactory dbProviderFactory = new System.Data.SQLite.SQLiteFactory();

Create a database connection:

Code:

DbConnection connection = dbProviderFactory.CreateConnection();
connection.ConnectionString = "Data Source=".\gbpvr.db3";Version=3;New=True;";
connection.Open();

Query some data:

Code:

                // load list of genre codes
                DbCommand command = dbProviderFactory.CreateCommand();
                command.Connection = connection;
                command.CommandText = "SELECT * from GENRE";                
                DbDataReader dataReader = command.ExecuteReader();
                while (dataReader.Read())
                {
                    string key = dataReader.GetString(dataReader.GetOrdinal("genre_name"));
                }
                dataReader.Close();

Close connection

Code:

connection.Close();

[zehd]

I dont have a convenient ready to use bit of code to cut and paste for you, but here is some bits that might piece together for something useful:

Setup DBProviderFactory (useful if you intend to introduce support for different DB types in future)

Code:

DbProviderFactory dbProviderFactory = new System.Data.SQLite.SQLiteFactory();

Create a database connection:

Code:

DbConnection connection = dbProviderFactory.CreateConnection();
connection.ConnectionString = "Data Source=".\gbpvr.db3";Version=3;New=True;";
connection.Open();

Query some data:

Code:

                // load list of genre codes
                DbCommand command = dbProviderFactory.CreateCommand();
                command.Connection = connection;
                command.CommandText = "SELECT * from GENRE";                
                DbDataReader dataReader = command.ExecuteReader();
                while (dataReader.Read())
                {
                    string key = dataReader.GetString(dataReader.GetOrdinal("genre_name"));
                }
                dataReader.Close();

Close connection

Code:

connection.Close();

Thanks. Now to get a good handle on this, that was C code right?. So I could in theory pop each snippet into a converter and add it to my code...

I'm using the new VB 2008 express. and I don't think I'm going to need more (I don't think so)

I know sharp Dev can convert back and forth.

Would anyone know if I would have better cross platform conversion if I downloaded the C# express too?

[zehd]

In trying to take your code, I'm starting to suspect that the docs for my versiin I downloaded are not the same as yours. I'm still not sure, but I think there are a lot of 'shadow' declarations for backward compatibility.

Code:

Public Shadows ReadOnly Property Connection As SQLiteConnection

Would you happen to have the chm for the version you have implemented?

[zehd]

I just read this from the Help:

Express Edition Limitations
All Express Editions (except Visual Web Developer) are hard-coded to only allow you to design for Jet and Sql Server Database Files. The only way for SQLite to install its designer is to temporarily replace one of the existing "approved" designers. Therefore, when you install the SQLite designer for one of these express editions, it will temporarily replace the Microsoft Access designer. You can revert back to the Access designer simply by re-running the install.exe program and un-checking the boxes.

Now from another thread, I read that sub doesn't use the sqlite syntax but rather the generic data access stuff. Is that killed by this substitution?

[zehd]

Thanks. You got me going all right. Stirred me rather... (the down under meaning of stirred)

I'm off

[zehd]

A couple of questions. As I start to use .net, I'm trying to unlearn bad habits. Certainly it's forcing me to define everything.

Code:

SQL_connection.ConnectionString = "Data Source=" & GBPVRdb3 & ";Version=3;New=True;"
        SQL_connection.Open()

        Dim command As DbCommand = DbProviderFactory.CreateCommand()
        command.Connection = SQL_connection
        command.CommandText = "SELECT * from GENRE"
        Dim dataReader As DbDataReader = command.ExecuteReader()
        While dataReader.Read()
            Dim key0 As String = dataReader.GetValue(dataReader.GetOrdinal("oid"))
            Dim key1 As String = dataReader.GetValue(dataReader.GetOrdinal("genre_name"))
            Dim key2 As String = dataReader.GetValue(dataReader.GetOrdinal("argb_color"))
            MsgBox("add to array here: " & key0 & key1 & key2)
        End While
        dataReader.Close()

In the above example, I use a generic .getvalue that will take whatever type the data is and then it goes into a string. I suppsoe I could start to use specific datatypes. I know that VB6 is notorious for being slow when using variants. What do you all suggest?

Also, I'd like to pump the results into an array. Is it correct to grab each column of each line, like this and add to array, or is there a way to get the query results already in a two dimensional array...

THanks

[zehd]

A couple of questions. As I start to use .net, I'm trying to unlearn bad habits. Certainly it's forcing me to define everything.

Code:

SQL_connection.ConnectionString = "Data Source=" & GBPVRdb3 & ";Version=3;New=True;"
        SQL_connection.Open()

        Dim command As DbCommand = DbProviderFactory.CreateCommand()
        command.Connection = SQL_connection
        command.CommandText = "SELECT * from GENRE"
        Dim dataReader As DbDataReader = command.ExecuteReader()
        While dataReader.Read()
            Dim key0 As String = dataReader.GetValue(dataReader.GetOrdinal("oid"))
            Dim key1 As String = dataReader.GetValue(dataReader.GetOrdinal("genre_name"))
            Dim key2 As String = dataReader.GetValue(dataReader.GetOrdinal("argb_color"))
            MsgBox("add to array here: " & key0 & key1 & key2)
        End While
        dataReader.Close()

In the above example, I use a generic .getvalue that will take whatever type the data is and then it goes into a string. I suppsoe I could start to use specific datatypes. I know that VB6 is notorious for being slow when using variants. What do you all suggest?

Also, I'd like to pump the results into an array. Is it correct to grab each column of each line, like this and add to array, or is there a way to get the query results already in a two dimensional array...

THanks

Oh, and I suppose that if I keep the datatypes specific, I might not be able to use a array...

[sub]

Thanks. Now to get a good handle on this, that was C code right?. So I could in theory pop each snippet into a converter and add it to my code...

I'm using the new VB 2008 express. and I don't think I'm going to need more (I don't think so)

This code was C#. Its very similar to VB.NET, just a few minor syntax differences.