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 <[EMAIL PROTECTED]>
// Chris Turchin <[EMAIL PROTECTED]>
// Thomas Zoechling <[EMAIL PROTECTED]>
//
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','[EMAIL PROTECTED]')";
dbcmd.ExecuteNonQuery();
dbcmd.CommandText ="INSERT INTO MONO_TEST (NID, NDESC,
EMAIL ) VALUES(2,'Mono 2','[EMAIL PROTECTED]')";
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','[EMAIL PROTECTED]')";
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"] = "[EMAIL PROTECTED]";
dataset.Tables[0].Rows.Add(dataRow);
dataset.Tables[0].Rows[0]["EMAIL"]="[EMAIL PROTECTED]";
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"]="[EMAIL PROTECTED]";
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"] = "[EMAIL PROTECTED]";
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 '[EMAIL PROTECTED]' or EMAIL = :email)";
dbcmd.Parameters.Add(new SqliteParameter(":nid" , 1) );
dbcmd.Parameters.Add(new SqliteParameter(":ndesc", "_ono%") );
dbcmd.Parameters.Insert(1,new
SqliteParameter(":email","[EMAIL PROTECTED]"));
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 '[EMAIL PROTECTED]' 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","[EMAIL PROTECTED]"));
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@lists.ximian.com
http://lists.ximian.com/mailman/listinfo/mono-list
_______________________________________________
Mono-list maillist - Mono-list@lists.ximian.com
http://lists.ximian.com/mailman/listinfo/mono-list
