Topic: SQLite for C# ~ Gareth Isaac

[Kerry]

Worth a visit.

http://www.csharphacker.com/technicalblog/index.php/2009/06/16/sqlite-for-c-part-1-am-i-allowed-to-use-it/

http://www.csharphacker.com/technicalblog/index.php/2009/06/17/sqlite-for-c-part-2-how-do-i-setup-a-sqlite-db-without-coding/

http://www.csharphacker.com/technicalblog/index.php/2009/06/28/sqlite-for-c-%e2%80%93-part-3-%e2%80%93-my-first-c-app-using-sqlite-aka-hello-world/

http://www.csharphacker.com/technicalblog/index.php/2009/06/30/sqlite-for-c-%e2%80%93-part-4-%e2%80%93-so-how-does-sqlite-stack-up-against-other-dbs/

http://www.csharphacker.com/technicalblog/index.php/2009/07/01/sqlite-for-c-%e2%80%93-part-5-%e2%80%93-sqlite-features-or-quirks/

http://www.csharphacker.com/technicalblog/index.php/2009/07/02/sqlite-for-c-%e2%80%93-part-6-%e2%80%93-sqlite-connection-string-definitions/

http://www.csharphacker.com/technicalblog/index.php/2009/07/05/sqlite-for-c-part-7%e2%80%93building-sqlite-net-from-source/

http://www.csharphacker.com/technicalblog/index.php/2009/09/19/sqliteforcsharppart8loadingcsvpipeintosqliteviacommandline/


... just be aware that the html displays  " in place of the quote character ".
added:
and change &lt;  to <
and change &gt;  to >

[It's Alive!]

Great Articles!

"Any column in a version 3 database, except an INTEGER PRIMARY KEY column, may be used to store any type of value."

This is one of the features I like, especially for dynamic languages like lisp. Caveat, it's like a box of chocolates, you don't what you're going to get.

example

"CREATE TABLE MyTable (No int, FirstName char(64), LastName char(64), age int);"

"INSERT INTO MyTable VALUES (1, 'Donald', 'Luck ' , 99);"
"INSERT INTO MyTable VALUES (2, 'Mickey', 'Mouse' , 101);"
"INSERT INTO MyTable VALUES (3, 'Minni' , 'Mouse' , 'Never ask a lady how old she is;);"


Select * returns (  ("No" "FirstName" "LastName" "age")
                          (1 "Donald" "Luck " 99)
                          (2 "Mickey" "Mouse" 101)
                          (3 "Minni" "Mouse" "Never ask a lady how old she is"))

so...

select AVG(age) from MyTable;"  would return an incorrect value as the 'Minni' record would    return 0. 

(("AVG(age)") (66.6667))

Just thinking out loud, it wouldn't be hard to wrap the stuff I have written for lisp for .net, it would eliminate the need for providers....

[Kerry]

< .. >
Just thinking out loud, it wouldn't be hard to wrap the stuff I have written for lisp for .net, it would eliminate the need for providers....

name your price !!

[It's Alive!]

 

[It's Alive!]

I had started wrapping the C++ code I use in SQLiteLsp http://www.codeproject.com/KB/database/CppSQLite.aspx ,

when I found it had already been done
http://code.google.com/p/managed-sqlite/

I'm going to modify a few things, link it with the latest SQLite, use .NET 3.5  etc..

hopefully samples will be coming soon

[It's Alive!]

Got it to work,  I have attached a sample solution, note the SQLiteNET.dll (32bit) is in the debug folder. it's not as heavily wrapped as the version for lisp, I also want to make a few changes.  Thoughts? suggestions?

Code: [Select]

namespace CsMgdAcad2
{
 public class Commands
 {

  // these should be an enum
  const int SQLITE_INTEGER = 1;
  const int SQLITE_FLOAT = 2;
  const int SQLITE_BLOB = 4;
  const int SQLITE_NULL = 5;
  const int SQLITE_TEXT = 3;

  [CommandMethod("doit")]
  static public void doit()
  {
   try
   {
    Editor editor = AcAp.Application.DocumentManager.MdiActiveDocument.Editor;
    List<List<object>> table = new List<List<object>>();

    // dispose or die
    using (ManagedSQLite.Database db = new ManagedSQLite.Database())
    {
     db.Open("C:\\MySQLite.db");

     // dispose or die
     using (ManagedSQLite.Query q = db.ExecuteQuery("SELECT * FROM MyTable;"))
     {
      while (!q.EndOfFile()) // I think this should be a property not a function
      {
       List<object> row = new List<object>();

       // should NumberOfFields() this be a property?
       for (int i = 0; i < q.NumberOfFields(); i++)
       {
        switch (q.FieldDataType(i))
        {
         case SQLITE_INTEGER: // these should be an enum
          row.Add(q.GetIntField(i));
          break;
         case SQLITE_FLOAT: // these should be an enum
          row.Add(q.GetFloatField(i));
          break;
         case SQLITE_NULL: // these should be an enum
          break;
         default:
          row.Add(q.GetStringField(i));
          break;
        }
       }
       q.NextRow();
       table.Add(row);
      }
     }
    }

    foreach(List<object> list in table)
    {
     editor.WriteMessage("\n(");
     foreach (object item in list)
     {
      editor.WriteMessage("{0} ",item);
     }
     editor.WriteMessage(")");
    }
   }
   catch (System.Exception e)
   {
    AcAp.Application.DocumentManager.MdiActiveDocument.
     Editor.WriteMessage("\n{0}", e.Message);
   }
  }
 }
}


[Kerry]

Thanks Dan, I'll have a look tonight.


I had this bookmarked for a look as well
http://www.devart.com/dotconnect/sqlite/docs/

[Kerry]

Dan, would we need seperate  x86, IA64 and x64 builds ??

[Kerry]

There are not enough hours in the day !!

[It's Alive!]

Dan, would we need seperate  x86, IA64 and x64 builds ??

Right, we would need two builds, one for win32 and one for x64 to keep the native SQLite code happy.
I am not adding anything that would make it version dependant, so it should run with any .NET 2.0+ assembly/exe.

[It's Alive!]

made a few changes 

Code: [Select]

   // added a new ctor to consume the path and open the db
    using (ManagedSQLite.Database db = new ManagedSQLite.Database("C:\\MySQLite.db"))
    {
     // dispose or die
     using (ManagedSQLite.Query q = db.ExecuteQuery("SELECT * FROM MyTable;"))
     {
      while (!q.IsEndOfFile) // is now a property
      {
       List<object> row = new List<object>();
       for (int i = 0; i < q.NumberOfFields; i++) // NumberOfFields is now property
       {
        switch (q.FieldDataType(i))
        {
         case ManagedSQLite.SQLiteType.Integer: // added SQLiteType enum
          row.Add(q.GetIntField(i));
          break;
         case ManagedSQLite.SQLiteType.Double:
          row.Add(q.GetFloatField(i));
          break;
         case ManagedSQLite.SQLiteType.Null:
          break;
         default:
          row.Add(q.GetStringField(i));
          break;
        }
       }
       q.NextRow();
       table.Add(row);
      }
     }
    }


[Kerry]

Yes Daniel, better I think


      while (!q.IsEndOfFile) // is now a property     
     {
       List<object> row = new List<object>();
       for (int i = 0; i < q.NumberOfFields; i++) // NumberOfFields is now property
       {
        switch (q.FieldDataType(i))
        {
         case ManagedSQLite.SQLiteType.Integer: // added SQLiteType enum

[It's Alive!]

Oh I see you snuck in IA64 : ) I don't know about that one 

[Kerry]

Oh I see you snuck in IA64 : ) I don't know about that one 

I just copied the textstring from the SQLite site .... don't think IA64 would be necessary. 

[It's Alive!]

Here is the solution with win32 and x64 builds..