TheSwamp
Code Red => .NET => Topic started by: Kerry on December 25, 2009, 07:45:36 PM
-
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 < to <
and change > to >
-
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....
-
< .. >
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 !!
-
:lol:
-
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
-
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?
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);
}
}
}
}
-
Thanks Dan, I'll have a look tonight.
I had this bookmarked for a look as well
http://www.devart.com/dotconnect/sqlite/docs/
-
Dan, would we need seperate x86, IA64 and x64 builds ??
-
There are not enough hours in the day !!
-
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.
-
made a few changes :-)
// 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);
}
}
}
-
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
-
Oh I see you snuck in IA64 : ) I don't know about that one :-o
-
Oh I see you snuck in IA64 : ) I don't know about that one :-o
I just copied the textstring from the SQLite site .... don't think IA64 would be necessary. :)
-
Here is the solution with win32 and x64 builds..
-
This essentially what I did for the lisp wrappers
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;
}
}
-
Thanks Daniel !!
[Kerry is on vacation; this posting is a pre-saved script.]
-
another sample
[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;");
}
}
-
[Kerry is on vacation; this posting is a pre-saved script.]
Have Fun : )
-
[Kerry is on vacation; this posting is a pre-saved script.]
Have Fun : )
If you insist :D
-
All the string statements now accept 'param object[]' arguments 8-)
[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();
}
}
-
ExecuteScalar has a bug don't use it yet
-
ExecuteScalar sample
[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 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 8-)
-
[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))
-
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 ?
-
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;
}
}
}
-
Query.FieldName is the function to get the column name
-
brilliant !
I'll send Lorraine off to Darrell Lea tomorrow :)
-
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! :laugh:
-
write 100,000 records 8-)
[CommandMethod("addit")]
public static void addit()
{
Editor ed = AcAp.Application.DocumentManager.MdiActiveDocument.Editor;
System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
stopWatch.Start();
string database = "C:\\MySQLite.db";
using (ManagedSQLite.Database db = new ManagedSQLite.Database(database))
{
db.ExecuteDML("create table Test6(No int, Name char(64));");
db.BeginTransaction();
for (int i = 0; i < 100000; i++)
{
db.ExecuteDML(String.Format("insert into Test6 values ({0} , 'Welcome To TheSwamp');", i));
}
db.CommitTransaction();
}
stopWatch.Stop();
TimeSpan ts = stopWatch.Elapsed;
string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
ts.Hours, ts.Minutes, ts.Seconds,
ts.Milliseconds / 10);
ed.WriteMessage("\n{0}", elapsedTime);
}
00:00:01.91
-
That's not too slothful Daniel.
-
I think it's about done. Should I add a thread in the "Show your stuff" area?, it might be easier to maintain there.
-
Here is the latest build. I changed a few items internally. I think it's about done. Should I add a thread in the "Show your stuff" area?, it might be easier to maintain there.
Sounds like a good plan :)
-
We have Docs.. almost done. BTY I am going to change blobs to byte arrays. instead of some pointer C# can't read
-
bloody brilliant !!
Lorraine is packing the jellybeans and licorice as we speak :D
added:
from memory, your significant other is a tax accountant ... make sure she puts aside the correct percentage :angel:
-
from memory, your significant other is a tax accountant ... make sure she puts aside the correct percentage :angel:
HA! that would make her a Jelly Bean Counter :lol:
-
New build
Some documentation
added a Row class List<Object> still need to override the ToString method
added a Table class List<Row> still need to override the ToString method
added a function Database.GetTable("query") that get a whole table in one call (list<list<object>>)
I changed the blob to a System. Byte[] I still need to test this.
edit go here http://www.theswamp.org/index.php?topic=31469.msg370071#new
-
I added a function to return a DataTable from a query. 8-)
namespace SQLiteNetSample
{
public partial class DataGrid : Form
{
public DataGrid()
{
InitializeComponent();
BindingSource src = new BindingSource();
src.DataSource = getTable();
this.dataGridView1.DataSource = src;
}
//
DataTable getTable()
{
using(SQLiteNET.Database db = new SQLiteNET.Database("C:\\Northwind.db"))
{
return db.GetDataTable("SELECT * FROM Customers");
}
}
}
}
-
Restarting over here http://www.theswamp.org/index.php?topic=31469.msg370071#new
-
Restarting over here http://www.theswamp.org/index.php?topic=31469.msg370071#new
Just saw that.
Thanks for the effort Daniel.