Topic: SQLite for C# ~ Gareth Isaac

[It's Alive!]

This essentially what I did for the lisp wrappers

Code: [Select]

public class Commands
 {
  [LispFunction("doit")]
  static public ResultBuffer doit(ResultBuffer args)
  {
   return getQueryBuffer("C:\\MySQLite.db", "SELECT * FROM MyTable;");
  }

  static ResultBuffer getQueryBuffer(String database, String query)
  {
   ResultBuffer buffer = new ResultBuffer();
   using (ManagedSQLite.Database db = new ManagedSQLite.Database(database))
   {
    using (ManagedSQLite.Query q = db.ExecuteQuery(query))
    {
     buffer.Add(new TypedValue((short)LispDataType.ListBegin));
     while (!q.IsEndOfFile) // is now a property
     {
      buffer.Add(new TypedValue((short)LispDataType.ListBegin));
      for (int i = 0; i < q.NumberOfFields; i++)
      {
       switch (q.FieldDataType(i))
       {
        case ManagedSQLite.SQLiteType.Integer:
         buffer.Add(new TypedValue((short)LispDataType.Int32, q.GetIntField(i)));
         break;
        case ManagedSQLite.SQLiteType.Double:
         buffer.Add(new TypedValue((short)LispDataType.Double, q.GetFloatField(i)));
         break;
        case ManagedSQLite.SQLiteType.Null:
         break;
        default:
         buffer.Add(new TypedValue((short)LispDataType.Text, q.GetStringField(i)));
         break;
       }
      }
      q.NextRow();
      buffer.Add(new TypedValue((short)LispDataType.ListEnd));
     }
     buffer.Add(new TypedValue((short)LispDataType.ListEnd));
    }
   }
   return buffer;
  }
}


[Kerry]

Thanks Daniel !!



[Kerry is on vacation; this posting is a pre-saved script.]

[It's Alive!]

another sample

Code: [Select]

  [CommandMethod("doit")]
  public static void doit()
  {
   string database = "C:\\MySQLite.db";
   using (ManagedSQLite.Database db = new ManagedSQLite.Database(database))
   {
    db.ExecuteDML("create table Test1(No int, Name char(64));");
    //db.BeginTransaction() bug
    db.ExecuteDML("BEGIN TRANSACTION;");

    for (int i = 0; i < 10000; i++)
    {
     db.ExecuteDML(String.Format("insert into Test1 values ({0} , 'Welcome To TheSwamp');", i));
    }
    //db.CommitTransaction() bug
    db.ExecuteDML("COMMIT TRANSACTION;");
   }
  }


[It's Alive!]

[Kerry is on vacation; this posting is a pre-saved script.]

Have Fun : )

[Kerry]

[Kerry is on vacation; this posting is a pre-saved script.]

Have Fun : )

If you insist

[It's Alive!]

All the string statements now accept  'param object[]' arguments  

Code: [Select]

 [CommandMethod("doit")]
  public static void doit()
  {
   string database = "C:\\MySQLite.db";
   using (ManagedSQLite.Database db = new ManagedSQLite.Database(database))
   {
    db.ExecuteDML("create table Test2(No int, Name char(64));");
    db.BeginTransaction();
    for (int i = 0; i < 10000; i++)
    {
     db.ExecuteDML("insert into Test2 values ({0} , '{1}');", i, "Welcome To TheSwamp");
    }
    db.CommitTransaction();
   }
  }


[It's Alive!]

ExecuteScalar has a bug don't use it yet

[It's Alive!]

ExecuteScalar sample

Code: [Select]

  [CommandMethod("doit")]
  public static void doit()
  {
   Editor ed =
    AcAp.Application.DocumentManager.MdiActiveDocument.Editor;
   string database = "C:\\MySQLite.db";
   using (ManagedSQLite.Database db = new ManagedSQLite.Database(database))
   {
    int res =  db.ExecuteScalar("select count(*) from Test2;");
    ed.WriteMessage("\nselect count {0}", res);
   }
  }


[It's Alive!]

It looks like all the functions are working and there don't seem to be anymore leaks. I don't really like the method the original author uses to covert System::String to wchar_t, I might go in and optimize this a bit better. Once the code is stabilized, I will go in and add comments for Doxygen 

[Kerry]

Code: [Select]

    [LispFunction("DOIT1227")]
    static public ResultBuffer MyLispFunction_DOIT1227(ResultBuffer args)
    {
        return getQueryBuffer("K:\\KDUBPro2010\\PipeData.db3",
            "SELECT * FROM FittingsReducer WHERE nb = 100 AND nb_reduced = 90;");

    }
Command: netload
Command: (doit1227)
((100 114.3 90 101.6 105.0 102.0 102.0))

[Kerry]

Dan, 'cause I'm too lazy to look it up at the moment :
Can you remind me how to get a listing of colulmn headers ?

[It's Alive!]

have a look

Code: [Select]

namespace CsMgdAcad2
{

 public class SqlRow : List<object> { }
 public class SqlTable : List<SqlRow> { }

 public class Commands
 {

  [CommandMethod("addit")]
  public static void addit()
  {
   string database = "C:\\MySQLite.db";
   using (ManagedSQLite.Database db = new ManagedSQLite.Database(database))
   {
    db.ExecuteDML("create table Test4(No int, Name char(64));");
    db.BeginTransaction();
    for (int i = 0; i < 10; i++)
    {
     db.ExecuteDML(String.Format("insert into Test4 values ({0} , 'Welcome To TheSwamp');", i));
    }
    db.CommitTransaction();
   }
  }


  [CommandMethod("getit")]
  public static void getit()
  {
   Editor ed = AcAp.Application.DocumentManager.MdiActiveDocument.Editor;
   string database = "C:\\MySQLite.db";
   string query = "SELECT * FROM Test4;";
   SqlTable table = getQueryTable(database, query);

   foreach (SqlRow row in table)
   {
    ed.WriteMessage("\n(");
    foreach (object item in row)
    {
     ed.WriteMessage("{0} ", item);
    }
    ed.WriteMessage(")");
   }
  }

  [LispFunction("doit")]
  static public ResultBuffer doit(ResultBuffer args)
  {
   return getQueryBuffer("C:\\MySQLite.db", "SELECT * FROM Test4;");
  }


  static SqlTable getQueryTable(String database, String query)
  {
   SqlTable table = new SqlTable();
   using (ManagedSQLite.Database db = new ManagedSQLite.Database(database))
   {
    using (ManagedSQLite.Query q = db.ExecuteQuery(query))
    {
     // get the column names
     SqlRow names = new SqlRow();
     for (int i = 0; i < q.NumberOfFields; i++)
     {
      names.Add(q.FieldName(i));
     }
     table.Add(names);

     while (!q.IsEndOfFile) // is now a property
     {
      SqlRow row = new SqlRow();
      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);
     }
    }
   }
   return table;
  }

  static ResultBuffer getQueryBuffer(String database, String query)
  {
   ResultBuffer buffer = new ResultBuffer();
   using (ManagedSQLite.Database db = new ManagedSQLite.Database(database))
   {
    using (ManagedSQLite.Query q = db.ExecuteQuery(query))
    {

     //get columns
     buffer.Add(new TypedValue((short)LispDataType.ListBegin));
     buffer.Add(new TypedValue((short)LispDataType.ListBegin));

     for (int i = 0; i < q.NumberOfFields; i++)
     {
      buffer.Add(new TypedValue((short)LispDataType.Text, q.FieldName(i)));
     }
     buffer.Add(new TypedValue((short)LispDataType.ListEnd));
     while (!q.IsEndOfFile) // is now a property
     {
      buffer.Add(new TypedValue((short)LispDataType.ListBegin));
      for (int i = 0; i < q.NumberOfFields; i++)
      {
       switch (q.FieldDataType(i))
       {
        case ManagedSQLite.SQLiteType.Integer:
         buffer.Add(new TypedValue((short)LispDataType.Int32, q.GetIntField(i)));
         break;
        case ManagedSQLite.SQLiteType.Double:
         buffer.Add(new TypedValue((short)LispDataType.Double, q.GetFloatField(i)));
         break;
        case ManagedSQLite.SQLiteType.Null:
         break;
        default:
         buffer.Add(new TypedValue((short)LispDataType.Text, q.GetStringField(i)));
         break;
       }
      }
      q.NextRow();
      buffer.Add(new TypedValue((short)LispDataType.ListEnd));
     }
     buffer.Add(new TypedValue((short)LispDataType.ListEnd));
    }
   }
   return buffer;
  }
 }
}


[It's Alive!]

Query.FieldName is the function to get the column name

[Kerry]

brilliant !
I'll send Lorraine off to Darrell Lea tomorrow

[It's Alive!]

brilliant !
I'll send Lorraine off to Darrell Lea tomorrow

Let's see, one Jelly Belly per line of code .... carry the 4...

Ha! I still owe you!