Hm... I'm not doing anything fancy.
I've attached the tiny sqlite logging class below. It's called in an MVC app
simply by invoking:
@Html.Raw(Doug.Utils.Web.DbLogger.Dump())
Yes, it uses transactions; is that a bad thing?
I'm pretty sure the issue is something to do with file handles. For the same
reason after loading the page (from an IIS server) and then closing the
page, waiting 1 minute or two and then attempting to remove the db.sqlite
file, I get an "error, file is in use".
(Obviously, if you use File.Open() without a File.Close() this does not
happen; when the page view ends the file handle is automatically released).
If you want to see it in action, create a new MVC project and add:
DbLogger.Get();
To the home index page. Run it in debug mode and you'll see the issue.
Cheers,
Doug.
code (in case the attachment fails):
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
using System.Data.SQLite;
using System.IO;
namespace Doug.Utils.Web
{
public class DbLoggerRecord
{
public String Context { get; set; }
public String Message { get; set; }
public DateTime Created { get; set; }
}
public class DbLogger
{
private static DbLogger _instance = null;
/// <summary>
/// Where to store the logging database.
/// </summary>
private const string relativeDbPath = "~/App_Data/DbLogger.sqlite";
private SQLiteConnection _connection = null;
private SQLiteTransaction _transaction = null;
public DbLogger()
{
var dbPath = Path.GetTempFileName();
if (HttpContext.Current != null)
dbPath = HttpContext.Current.Server.MapPath(relativeDbPath);
_connection = InitDb(dbPath);
}
private SQLiteConnection InitDb(String dbPath)
{
bool init = false;
if (!File.Exists(dbPath))
{
SQLiteConnection.CreateFile(dbPath);
init = true;
}
var rtn = new SQLiteConnection("Data Source="+dbPath);
rtn.Open();
// Pragma or this doesn't work in app_data folder.
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = rtn;
string pragma = "PRAGMA journal_mode = OFF";
cmd.CommandText = pragma;
cmd.ExecuteNonQuery();
// Check if we reallllly neeed to init?
if (!init) {
try
{
GetRecords(1);
}
catch(Exception)
{
try
{
File.Delete(dbPath);
init = true;
}
catch(Exception)
{
}
}
}
if (init)
CreateTable(rtn);
return rtn;
}
private void CreateTable(SQLiteConnection c)
{
var createTable = new StringBuilder();
createTable.Append("CREATE TABLE Log (");
createTable.Append(" Id PRIMARY KEY,");
createTable.Append(" Context TEXT,");
createTable.Append(" Message TEXT,");
createTable.Append(" Created TEXT)");
var cmd = createTable.ToString();
var sqlCmd = new SQLiteCommand(c);
sqlCmd.CommandText = cmd;
try
{
sqlCmd.ExecuteNonQuery();
}
catch(Exception)
{
}
}
public void Trace(String context, String message)
{
if (_transaction == null)
_transaction = _connection.BeginTransaction();
var insertMsg = new StringBuilder();
insertMsg.Append("INSERT INTO Log (Context, Message, Created)
VALUES (@Context, @Message, @Created)");
var cmd = insertMsg.ToString();
var sqlCmd = new SQLiteCommand(_connection);
sqlCmd.CommandText = cmd;
// Params
sqlCmd.Parameters.AddWithValue("@Context", context);
sqlCmd.Parameters.AddWithValue("@Message", message);
sqlCmd.Parameters.AddWithValue("@Created",
DateTime.Now.ToString());
sqlCmd.ExecuteNonQuery();
}
public void Close() {
if (_connection != null)
{
_connection.Close();
_connection = null;
}
}
public IEnumerable<DbLoggerRecord> GetRecords(int limit)
{
Commit();
var rtn = new List<DbLoggerRecord>();
string cmd = "SELECT * FROM Log ORDER BY Id ASC LIMIT " + limit;
var sqlCmd = new SQLiteCommand(_connection);
sqlCmd.CommandText = cmd;
SQLiteDataReader reader = sqlCmd.ExecuteReader();
while(reader.Read())
{
var instance = new DbLoggerRecord();
int i = reader.GetOrdinal("Created");
String createdString = (string) reader.GetValue(i);
instance.Created = (DateTime) DateTime.Parse(createdString);
if (reader.GetValue(reader.GetOrdinal("Context")).GetType()
!= typeof(DBNull))
instance.Context =
(String)reader.GetValue(reader.GetOrdinal("Context"));
else
instance.Context = "NULL";
if (reader.GetValue(reader.GetOrdinal("Message")).GetType()
!= typeof(DBNull))
instance.Message = (String)
reader.GetValue(reader.GetOrdinal("Message"));
else
instance.Message = "NULL";
rtn.Add(instance);
}
return rtn;
}
public void Clear()
{
string cmd = "DELETE FROM Log";
var sqlCmd = new SQLiteCommand(_connection);
sqlCmd.CommandText = cmd;
sqlCmd.ExecuteNonQuery();
}
public void Commit()
{
if (_transaction != null)
{
_transaction.Commit();
_transaction = null;
}
}
public string HtmlFormatRecords(IEnumerable<DbLoggerRecord> records)
{
StringBuilder b = new StringBuilder();
b.Append("<div><h4>DbLogger</h4>");
foreach(DbLoggerRecord record in records)
{
b.Append(String.Format("<div style='padding: 1px;'>{0}:
{1}</div>", record.Context, record.Message));
}
b.Append("</div>");
return b.ToString();
}
public static DbLogger Get()
{
if (_instance == null)
_instance = new DbLogger();
return _instance;
}
public static string Dump()
{
var l = Get();
var rtn = l.HtmlFormatRecords(l.GetRecords(100));
l.Close(); // If we're printing, we're done for now.
return (rtn);
}
}
}
On Mon, Jul 18, 2011 at 8:09 PM, Black, Michael (IS) <michael.bla...@ngc.com
> wrote:
> If you showed a small sample code that caused your problem it would sure
> help a lot.
>
>
>
> I peeked at the library code. That message is generated when _cnn is null
> (the DB connection).
>
> private void InitializeForReader()
> {
> if (_activeReader != null && _activeReader.IsAlive)
> throw new InvalidOperationException("DataReader already active on
> this command");
>
> if (_cnn == null)
> throw new InvalidOperationException("No connection associated with
> this command");
>
>
>
> It is set on a "new Connection".
>
>
>
> It is only turned to null in one place (in Commit). But why in the world
> you would null _cnn on a commit is beyond me.
>
> Are you using a commit? It could be autocommit I supposed causing it.
>
> I'd comment out that "_cnn = null" line below and see if it solves your
> problem. Sure would explain why it only works once.
>
>
>
> /// <summary>
> /// Commits the current transaction.
> /// </summary>
> public override void Commit()
> {
> IsValid(true);
>
> if (_cnn._transactionLevel - 1 == 0)
> {
> using (SQLiteCommand cmd = _cnn.CreateCommand())
> {
> cmd.CommandText = "COMMIT";
> cmd.ExecuteNonQuery();
> }
> }
> _cnn._transactionLevel--;
> _cnn = null;
> }
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
>
> ________________________________
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Doug [douglas.lin...@gmail.com]
> Sent: Monday, July 18, 2011 1:34 AM
> To: sqlite-users@sqlite.org
> Subject: EXT :[sqlite] Handle leak using IIS on windows 7?
>
> Hi there,
>
> I've searched around and found a few threads like this:
> http://sqlite.phxsoftware.com/forums/t/2480.aspx
>
> Basically, I have the same issue.
>
> When access the sqlite database via a website (MVC3 running on IIS) the
> first time, sqlite works fine.
>
> I properly call connections.Close() when I'm done...
>
> And the next time I try to access it I get:
> System.InvalidOperationException: No connection associated with this
> command
>
> Manually stopping the dev web server, or restarting the iis application
> pool
> fixes this for one more page view.
>
> It seems like the IIS config is leaving the process hanging around, and
> after calling close there (I guess) must be some handle which is being kept
> and keeping a reference to the database, preventing anything else from
> accessing it.
>
> Seeing as how this has happened to a few people, I was hoping someone here
> had seen this before and had a solution?
>
> I'm using the Precompiled Binaries for 32-bit Windows (.NET Framework 4.0)
> from
> http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki(32
> bit mode enabled on iis), but I've tried the 64-bit version with the
> same result.
>
> Cheers,
> Doug.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
using System.Data.SQLite;
using System.IO;
namespace Doug.Utils.Web
{
public class DbLoggerRecord
{
public String Context { get; set; }
public String Message { get; set; }
public DateTime Created { get; set; }
}
public class DbLogger
{
private static DbLogger _instance = null;
/// <summary>
/// Where to store the logging database.
/// </summary>
private const string relativeDbPath = "~/App_Data/DbLogger.sqlite";
private SQLiteConnection _connection = null;
private SQLiteTransaction _transaction = null;
public DbLogger()
{
var dbPath = Path.GetTempFileName();
if (HttpContext.Current != null)
dbPath = HttpContext.Current.Server.MapPath(relativeDbPath);
_connection = InitDb(dbPath);
}
private SQLiteConnection InitDb(String dbPath)
{
bool init = false;
if (!File.Exists(dbPath))
{
SQLiteConnection.CreateFile(dbPath);
init = true;
}
var rtn = new SQLiteConnection("Data Source="+dbPath);
rtn.Open();
// Pragma or this doesn't work in app_data folder.
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = rtn;
string pragma = "PRAGMA journal_mode = OFF";
cmd.CommandText = pragma;
cmd.ExecuteNonQuery();
// Check if we reallllly neeed to init?
if (!init) {
try
{
GetRecords(1);
}
catch(Exception)
{
try
{
File.Delete(dbPath);
init = true;
}
catch(Exception)
{
}
}
}
if (init)
CreateTable(rtn);
return rtn;
}
private void CreateTable(SQLiteConnection c)
{
var createTable = new StringBuilder();
createTable.Append("CREATE TABLE Log (");
createTable.Append(" Id PRIMARY KEY,");
createTable.Append(" Context TEXT,");
createTable.Append(" Message TEXT,");
createTable.Append(" Created TEXT)");
var cmd = createTable.ToString();
var sqlCmd = new SQLiteCommand(c);
sqlCmd.CommandText = cmd;
try
{
sqlCmd.ExecuteNonQuery();
}
catch(Exception)
{
}
}
public void Trace(String context, String message)
{
if (_transaction == null)
_transaction = _connection.BeginTransaction();
var insertMsg = new StringBuilder();
insertMsg.Append("INSERT INTO Log (Context, Message, Created)
VALUES (@Context, @Message, @Created)");
var cmd = insertMsg.ToString();
var sqlCmd = new SQLiteCommand(_connection);
sqlCmd.CommandText = cmd;
// Params
sqlCmd.Parameters.AddWithValue("@Context", context);
sqlCmd.Parameters.AddWithValue("@Message", message);
sqlCmd.Parameters.AddWithValue("@Created", DateTime.Now.ToString());
sqlCmd.ExecuteNonQuery();
}
public void Close() {
if (_connection != null)
{
_connection.Close();
_connection = null;
}
}
public IEnumerable<DbLoggerRecord> GetRecords(int limit)
{
Commit();
var rtn = new List<DbLoggerRecord>();
string cmd = "SELECT * FROM Log ORDER BY Id ASC LIMIT " + limit;
var sqlCmd = new SQLiteCommand(_connection);
sqlCmd.CommandText = cmd;
SQLiteDataReader reader = sqlCmd.ExecuteReader();
while(reader.Read())
{
var instance = new DbLoggerRecord();
int i = reader.GetOrdinal("Created");
String createdString = (string) reader.GetValue(i);
instance.Created = (DateTime) DateTime.Parse(createdString);
if (reader.GetValue(reader.GetOrdinal("Context")).GetType() !=
typeof(DBNull))
instance.Context =
(String)reader.GetValue(reader.GetOrdinal("Context"));
else
instance.Context = "NULL";
if (reader.GetValue(reader.GetOrdinal("Message")).GetType() !=
typeof(DBNull))
instance.Message = (String)
reader.GetValue(reader.GetOrdinal("Message"));
else
instance.Message = "NULL";
rtn.Add(instance);
}
return rtn;
}
public void Clear()
{
string cmd = "DELETE FROM Log";
var sqlCmd = new SQLiteCommand(_connection);
sqlCmd.CommandText = cmd;
sqlCmd.ExecuteNonQuery();
}
public void Commit()
{
if (_transaction != null)
{
_transaction.Commit();
_transaction = null;
}
}
public string HtmlFormatRecords(IEnumerable<DbLoggerRecord> records)
{
StringBuilder b = new StringBuilder();
b.Append("<div style='width: 90%; height: 200px; overflow: auto;
background: #efefef; position: absolute; bottom: 0px; border: 1px solid #000;
padding: 10px; margin: 10px;'><h4 style='margin: 0px 0px 4px 0px
!important;'>DbLogger</h4>");
foreach(DbLoggerRecord record in records)
{
b.Append(String.Format("<div style='padding: 1px;'>{0}:
{1}</div>", record.Context, record.Message));
}
b.Append("</div>");
return b.ToString();
}
public static DbLogger Get()
{
if (_instance == null)
_instance = new DbLogger();
return _instance;
}
public static string Dump()
{
var l = Get();
var rtn = l.HtmlFormatRecords(l.GetRecords(100));
l.Close(); // If we're printing, we're done for now.
return (rtn);
}
}
}_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
