[Mono-list] Using SQLite, Mono and Monodevelop
Thomas Zoechling
thomas.zoechling at gmx.at
Wed Jun 22 11:06:45 EDT 2005
Alessandro Bottoni wrote:
>I have a few questions:
>- Why I have just Windows DLLs in my /usr/lib/mono/gac/Mono.Data.SqliteClient
>directory? Shouldn't I have a few .SO Linux libraries as well?
>
That are CIL dlls. Therefore the term "Windows" dll isnt valid anymore
(thats what mono is all about :) )
>- Do I have to install the .SO Linux libraries by hand? Which version? I'm
>unable to identify the version of SQLite used to create the existing DLL.
>
To install sqlite under debian you should apt-cache search for sqlite. I
suppose the package name will be something like libsqlite-dev.
Another way to install sqlite is to download the .so from
http://www.sqlite.org/download.html (Scroll down the list and pick the
.so.bz file !!!! -
Don't take the first file -> thats some command line tool -> that way my
first mistake :) ).
Then extract the file and symlink it to libsqlite.so or libsqlite3.so
(depending on what version you choose).
If you still have troubles with Mono.Data.Sqlite then read the following
Blog post of Chris Turchin:
http://turchin.homelinux.net/blogx/PermaLink.aspx/acbc7138-08d6-4a0f-a86d-709748a283d8
>- Where is the test suite mentioned by the SQLite docu at
>www.go-mono.com/sqlite.html? It should be in
>mcs/class/Mono.Data.SqliteTest/Test but I'm unable to find this directory on
>my machine.
>
I am one of the authors of the latest Mono.Data.Sqlite patch. And I also
provided a Test and a UnitTest program, but I just noticed that it has
been removed from the
patch :) Here are the basic code parts :
Notice that the Parameter delimeter in sqlite3 is ':' and that you have
to specify the correct version in your Connection string
//
// SqliteTest.cs
//
// Author(s): Daniel Morgan <danmorg at sc.rr.com>
// Chris Turchin <chris at turchin.net>
// Thomas Zoechling <thomas.zoechling at gmx.at>
//
using System;
using System.Data;
using System.Data.Common;
using System.IO;
using Mono.Data.SqliteClient;
namespace Test.Mono.Data.SqliteClient
{
class SqliteTest
{
//static string connectionString =
"Version=3,URI=file:SqliteTest.db";
static string connectionString = "Version=3,URI=file:SqliteTest.db";
static SqliteConnection dbcon = new SqliteConnection();
static SqliteCommand dbcmd = new SqliteCommand();
[STAThread]
static void Main(string[] args)
{
Console.WriteLine("setting ConnectionString using: " +
connectionString);
dbcon.ConnectionString = connectionString;
if (File.Exists("SqliteTest.db"))
File.Delete("SqliteTest.db");
Console.WriteLine("open the connection...");
dbcon.Open();
dbcmd.Connection = dbcon;
SetupDB();
Console.WriteLine("SELECTING DATA FROM MONO_TEST");
TestWithoutParameters();
TestSingleParameter();
TestMultipleParameters();
TestUsingDataAdapter();
TestUpdateWithParamsAndEvents();
dbcmd.Dispose();
dbcon.Close();
Console.WriteLine("Done.");
}
static void SetupDB()
{
dbcmd.CommandText = "CREATE TABLE MONO_TEST ( NID INT, NDESC
TEXT, EMAIL TEXT)";
Console.WriteLine("execute SqliteCommand to CREATE TABLE
MONO_TEST: " + dbcmd.CommandText );
dbcmd.ExecuteNonQuery();
Console.WriteLine("inserting data into MONO_TEST...");
dbcmd.CommandText = "INSERT INTO MONO_TEST (NID, NDESC,
EMAIL ) VALUES(1,'Mono 1','chris at turchin.net')";
dbcmd.ExecuteNonQuery();
dbcmd.CommandText ="INSERT INTO MONO_TEST (NID, NDESC,
EMAIL ) VALUES(2,'Mono 2','test at test')";
dbcmd.ExecuteNonQuery();
dbcmd.CommandText = "INSERT INTO MONO_TEST (NID, NDESC )
VALUES(3,'Mono 3')";
dbcmd.ExecuteNonQuery();
dbcmd.CommandText ="INSERT INTO MONO_TEST (NID, NDESC )
VALUES(4,'Mono 4')";
dbcmd.ExecuteNonQuery();
dbcmd.CommandText = "INSERT INTO MONO_TEST (NID, NDESC,
EMAIL ) VALUES(5,'Mono 5','test at test')";
dbcmd.ExecuteNonQuery();
}
static void TestUsingDataAdapter()
{
Console.WriteLine("read and display data using DataAdapter...");
SqliteDataAdapter adapter = new
SqliteDataAdapter("SELECT * FROM MONO_TEST", connectionString);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
DisplayDataSet(dataset);
Console.WriteLine("next test...");
Console.WriteLine("Insert and change data using dataset...");
DataRow dataRow = dataset.Tables[0].NewRow();
dataRow["NID"] = "6";
dataRow["NDESC"] = "New via dataset";
dataRow["EMAIL"] = "chris at turchin.net";
dataset.Tables[0].Rows.Add(dataRow);
dataset.Tables[0].Rows[0]["EMAIL"]="thomas.zoechling at gmx.at";
DisplayDataSet(dataset);
Console.WriteLine("next test...");
Console.WriteLine("Custom data adapter and data adapter
events.");
SqliteCommand dbcmd2 = new SqliteCommand("SELECT NID, NDESC,
EMAIL FROM MONO_TEST where NID > :NID",dbcon);
SqliteParameter param = new SqliteParameter();
param.ParameterName = ":NID";
param.Value = 3;
param.DbType = DbType.Int32;
dbcmd2.Parameters.Add(param);
SqliteDataAdapter custDA = new SqliteDataAdapter(dbcmd2);
/*
//FIXME SqliteCommandBuilder not yet implemented...
SqliteCommandBuilder custCB = new SqliteCommandBuilder(custDA);
Console.WriteLine(custCB.GetUpdateCommand().CommandText);
*/
DataSet custDS = new DataSet();
custDA.Fill(custDS);
//custDS.Tables[0].Rows[1]["EMAIL"]="devnull at dev.null";
DisplayDataSet(custDS);
Console.WriteLine("next test...");
Console.WriteLine("read and display data as XML");
Console.WriteLine(dataset.GetXml());
}
static void TestUpdateWithParamsAndEvents()
{
dbcmd.CommandText = "SELECT NID, NDESC, EMAIL FROM MONO_TEST";
SqliteCommand update = new SqliteCommand("UPDATE MONO_TEST
SET NID = :NID, NDESC = :NDESC, EMAIL = :EMAIL WHERE NID = :NID ");
update.Connection=dbcon;
SqliteCommand delete = new SqliteCommand("DELETE FROM
MONO_TEST WHERE NID = :NID");
delete.Connection=dbcon;
SqliteCommand insert = new SqliteCommand("INSERT INTO
MONO_TEST (NID, NDESC, EMAIL ) VALUES(:NID,:NDESC,:EMAIL)");
insert.Connection=dbcon;
SqliteDataAdapter custDA = new SqliteDataAdapter(dbcmd);
custDA.RowUpdating += new
SqliteRowUpdatingEventHandler(OnRowUpdating);
custDA.RowUpdated += new
SqliteRowUpdatedEventHandler(OnRowUpdated);
SqliteParameter nid = new SqliteParameter();
nid.ParameterName = ":NID";
nid.DbType = DbType.Int32;
nid.SourceColumn = "NID";
nid.SourceVersion = DataRowVersion.Current;
SqliteParameter ndesc = new SqliteParameter();
ndesc.ParameterName = ":NDESC";
ndesc.DbType = DbType.String;
ndesc.SourceColumn = "NDESC";
ndesc.SourceVersion = DataRowVersion.Current;
SqliteParameter email = new SqliteParameter();
email.ParameterName =":EMAIL";
email.DbType = DbType.String;
email.SourceColumn = "EMAIL";
email.SourceVersion = DataRowVersion.Current;
update.Parameters.Add(nid);
update.Parameters.Add(ndesc);
update.Parameters.Add(email);
delete.Parameters.Add(nid);
insert.Parameters.Add(nid);
insert.Parameters.Add(ndesc);
insert.Parameters.Add(email);
custDA.UpdateCommand = update;
custDA.DeleteCommand = delete;
custDA.InsertCommand = insert;
DataSet dataset = new DataSet();
custDA.Fill(dataset);
dataset.AcceptChanges();
DisplayDataSet(dataset);
DataRow dataRow = dataset.Tables[0].Rows[0];
dataRow["NDESC"] = "CHANGED";
DataRow newRow = dataset.Tables[0].NewRow();
newRow["NID"] = 999;
newRow["NDESC"] = "newDesc";
newRow["EMAIL"] = "new at Desc.at";
dataset.Tables[0].Rows.Add(newRow);
DataRow victim = dataset.Tables[0].Rows[3];
victim.Delete();
Console.WriteLine("Rows affected: " +
custDA.Update(dataset).ToString());
dataset.Clear();
custDA.Fill(dataset);
DisplayDataSet(dataset);
custDA.RowUpdating -= new
SqliteRowUpdatingEventHandler(OnRowUpdating);
custDA.RowUpdated -= new
SqliteRowUpdatedEventHandler(OnRowUpdated);
}
protected static void OnRowUpdating(object sender,
RowUpdatingEventArgs args)
{
Console.WriteLine("OnRowUpdating fired...");
}
protected static void OnRowUpdated(object sender,
RowUpdatedEventArgs args)
{
Console.WriteLine("OnRowUpdated fired...");
}
static void TestWithoutParameters()
{
dbcmd.CommandText = "SELECT * FROM MONO_TEST where NID > 2";
Console.WriteLine("TestWithoutParameters: " +
dbcmd.CommandText + "\n\nexecute reader...");
ShowData( dbcmd.ExecuteReader());
Console.WriteLine("next test...");
}
static void TestSingleParameter()
{
dbcmd.CommandText = "SELECT * FROM MONO_TEST where NID > :1";
SqliteParameter param = new SqliteParameter();
param.ParameterName = ":1";
param.Value = 1;
param.DbType = DbType.Int32;
dbcmd.Parameters.Add(param);
Console.WriteLine("TestSingleParameter: " +
dbcmd.CommandText + "\n\nexecute reader...");
ShowData( dbcmd.ExecuteReader());
dbcmd.Parameters.Clear();
Console.WriteLine("next test...");
}
static void TestMultipleParameters()
{
dbcmd.CommandText = "SELECT * FROM MONO_TEST where NID >=
:nid AND NDESC LIKE :ndesc and (EMAIL LIKE '%@test' or EMAIL = :email)";
dbcmd.Parameters.Add(new SqliteParameter(":nid" , 1) );
dbcmd.Parameters.Add(new SqliteParameter(":ndesc", "_ono%") );
dbcmd.Parameters.Insert(1,new
SqliteParameter(":email","chris at turchin.net"));
Console.WriteLine("TestMultipleParameters: " +
dbcmd.CommandText + "\n\nexecute reader...");
ShowData( dbcmd.ExecuteReader());
dbcmd.Parameters.Clear();
Console.WriteLine("next test...");
}
static void TestUnnamedParameters()
{
dbcmd.CommandText = "SELECT * FROM MONO_TEST where NID > :1
AND NDESC LIKE :2 and (EMAIL LIKE '%@test' or EMAIL = :3)";
SqliteParameter param = new SqliteParameter();
SqliteParameter param2 = new SqliteParameter();
param.Value = 1;
param.DbType = DbType.Int32;
param2.Value = "_ono 5";
dbcmd.Parameters.Add(param);
dbcmd.Parameters.Add(param2);
dbcmd.Parameters.Insert(1,new
SqliteParameter(":3","chris at turchin.net"));
Console.WriteLine("TestUnnamedParameters: " +
dbcmd.CommandText + "\n\nexecute reader...");
ShowData(dbcmd.ExecuteReader());
dbcmd.Parameters.Clear();
Console.WriteLine("next test...");
}
static void DisplayDataSet(DataSet dataset)
{
foreach(DataTable myTable in dataset.Tables)
{
foreach(DataRow myRow in myTable.Rows)
{
Console.Write("datarow:\t");
string data = myRow["NID"] + "|\t" + myRow["NDESC"]
+ "|\t" + myRow["EMAIL"] ;
Console.WriteLine(data);
}
}
}
static void ShowData(SqliteDataReader reader)
{
Console.WriteLine("read and display data...");
while(reader.Read())
{
Console.Write("datarow:\t" + reader[0].ToString());
Console.Write("|\t" + reader[1].ToString());
string email;
if (reader[2]==null)
email = "(null)";
else
email = reader[2].ToString();
Console.Write("|\t" + email + "\n");
}
reader.Close();
}
}
}
>I'm trying to use monodevelop 0.7, mono 1.1.8.1pre on a Debian 3.1 (brought to
>the latest stable version available on debian.meebey.net by apt-get) for
>building a small DB-based application with SQLite n3.2.1.
>
>I have a few questions:
>- Why I have just Windows DLLs in my /usr/lib/mono/gac/Mono.Data.SqliteClient
>directory? Shouldn't I have a few .SO Linux libraries as well?
>
>- Do I have to install the .SO Linux libraries by hand? Which version? I'm
>unable to identify the version of SQLite used to create the existing DLL.
>
>- Where is the test suite mentioned by the SQLite docu at
>www.go-mono.com/sqlite.html? It should be in
>mcs/class/Mono.Data.SqliteTest/Test but I'm unable to find this directory on
>my machine.
>
>In short: is it available any (up-to-date and reliable) tutorial about setting
>up the system and developing a small SQLite+Mono application?
>
>Many thanks for your attention
>-------------------------------
>Alessandro Bottoni
>_______________________________________________
>Mono-list maillist - Mono-list at lists.ximian.com
>http://lists.ximian.com/mailman/listinfo/mono-list
>
>
>
>
More information about the Mono-list
mailing list