have a look
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;
}
}
}