From 1c28d8d27fc177060fb6e38ec1d2d77e2267f9f8 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?J=C3=A9r=C3=A9mie=20Laval?= Date: Thu, 27 Jan 2011 13:13:25 +0000 Subject: Leave db connection opened until requested to close it and add more unit tests --- .../Test/WebMatrix.Data/DatabaseTests.cs | 27 +++++++++- .../WebMatrix.Data/WebMatrix.Data/Database.cs | 58 +++++++++++++++------- 2 files changed, 66 insertions(+), 19 deletions(-) (limited to 'mcs/class/WebMatrix.Data') diff --git a/mcs/class/WebMatrix.Data/Test/WebMatrix.Data/DatabaseTests.cs b/mcs/class/WebMatrix.Data/Test/WebMatrix.Data/DatabaseTests.cs index 12f3cf6264f..135d4de8ca0 100644 --- a/mcs/class/WebMatrix.Data/Test/WebMatrix.Data/DatabaseTests.cs +++ b/mcs/class/WebMatrix.Data/Test/WebMatrix.Data/DatabaseTests.cs @@ -42,12 +42,13 @@ namespace MonoTests.WebMatrix.Data public class DatabaseTests { Database database; + string dbPath; [SetUp] public void Setup () { - string path = Path.Combine ("Test", "testsqlite.db"); - database = Database.OpenConnectionString ("Data Source="+path+";Version=3;", "Mono.Data.Sqlite"); + dbPath = Path.Combine ("Test", "testsqlite.db"); + database = Database.OpenConnectionString ("Data Source="+dbPath+";Version=3;", "Mono.Data.Sqlite"); } [Test] @@ -79,6 +80,28 @@ namespace MonoTests.WebMatrix.Data } } + [Test] + public void InsertTest () + { + string newPath = dbPath + ".tmp"; + File.Copy (dbPath, newPath, true); + + database = Database.OpenConnectionString ("Data Source="+newPath+";Version=3;", "Mono.Data.Sqlite"); + database.Execute ("insert into memos values ('foo', @0);", 42); + + Assert.AreEqual (42, database.QueryValue ("select Priority from memos where Text='foo'")); + Assert.AreEqual (6, database.GetLastInsertId ()); + + File.Delete (newPath); + } + + [Test] + public void QueryValueTest () + { + var res = database.QueryValue ("select Priority from memos where Text='Webmatrix'"); + Assert.AreEqual (10, res); + } + [Test] public void ConnectionOpenedTest () { diff --git a/mcs/class/WebMatrix.Data/WebMatrix.Data/Database.cs b/mcs/class/WebMatrix.Data/WebMatrix.Data/Database.cs index f9956ea4d9f..be7751902a2 100644 --- a/mcs/class/WebMatrix.Data/WebMatrix.Data/Database.cs +++ b/mcs/class/WebMatrix.Data/WebMatrix.Data/Database.cs @@ -29,6 +29,7 @@ using System; using System.Linq; +using System.Threading; using System.Dynamic; using System.Data.Common; using System.Configuration; @@ -41,11 +42,25 @@ namespace WebMatrix.Data { public static event EventHandler ConnectionOpened; + bool opened; DbConnection connection; + readonly string providerName; - private Database (DbConnection connection) + static readonly Dictionary lastInsertedIdStmts = new Dictionary (); + + static Database () + { + // Add your own DB-specific way to do so + lastInsertedIdStmts.Add ("System.Data.SqlClient", "select @@IDENTITY;"); + lastInsertedIdStmts.Add ("Mono.Data.Sqlite", "select last_insert_rowid ();"); + lastInsertedIdStmts.Add ("MySql.Data", "SELECT LAST_INSERT_ID();"); + lastInsertedIdStmts.Add ("Npgsql", "SELECT lastval();"); + } + + private Database (DbConnection connection, string providerName) { this.connection = connection; + this.providerName = providerName; } public static Database Open (string name) @@ -68,12 +83,13 @@ namespace WebMatrix.Data var conn = factory.CreateConnection (); conn.ConnectionString = connectionString; - return new Database (conn); + return new Database (conn, providerName); } public void Close () { - Dispose (); + opened = false; + connection.Close (); } public void Dispose () @@ -83,8 +99,11 @@ namespace WebMatrix.Data protected virtual void Dispose (bool disposing) { - if (disposing) + if (disposing) { + if (opened) + connection.Close (); connection.Dispose (); + } } public int Execute (string commandText, params object[] args) @@ -92,12 +111,10 @@ namespace WebMatrix.Data var command = PrepareCommand (commandText); PrepareCommandParameters (command, args); - connection.Open (); - TriggerConnectionOpened (this, connection); + EnsureConnectionOpened (); var result = command.ExecuteNonQuery (); - connection.Close (); command.Dispose (); return result; @@ -119,14 +136,13 @@ namespace WebMatrix.Data List> QueryInternal (string commandText, object[] args, bool unique) { + EnsureConnectionOpened (); + var command = PrepareCommand (commandText); PrepareCommandParameters (command, args); string[] columnsNames; var rows = new List> (); - connection.Open (); - TriggerConnectionOpened (this, connection); - using (var reader = command.ExecuteReader ()) { if (!reader.Read () || !reader.HasRows) return null; @@ -147,7 +163,6 @@ namespace WebMatrix.Data } while (!unique && reader.Read ()); } - connection.Close (); command.Dispose (); return rows; @@ -155,24 +170,23 @@ namespace WebMatrix.Data public object QueryValue (string commandText, params object[] args) { + EnsureConnectionOpened (); + var command = PrepareCommand (commandText); PrepareCommandParameters (command, args); - connection.Open (); - TriggerConnectionOpened (this, connection); - var result = command.ExecuteScalar (); - connection.Close (); command.Dispose (); return result; } - // TODO: I don't think this is actually quite generic (SQL Server specific) public object GetLastInsertId () { - const string sql = "select @@IDENTITY"; + string sql; + if (!lastInsertedIdStmts.TryGetValue (providerName, out sql)) + throw new NotSupportedException ("This operation is not available for your database"); return QueryValue (sql); } @@ -207,6 +221,16 @@ namespace WebMatrix.Data evt (self, new ConnectionEventArgs (connection)); } + void EnsureConnectionOpened () + { + if (opened) + return; + + connection.Open (); + opened = true; + TriggerConnectionOpened (this, connection); + } + public DbConnection Connection { get { return connection; -- cgit v1.2.3