SQLite database is locked

when i want to save the record using c sharp with sqlite database it gives exception that the database is locked , i have already check my connection opening and closing properly.

I am using that structure for opening and closing

public bool CreateAll(ClsDocumentsHeader obj, List objlist)

{

bool isintran = false;

try

{

result = false;

getvalue = 0;

#region "Set Connection And Transaction - - - - - - - - -"

con = new SQLiteConnection(DMS_2008_Data.Properties.Settings.Default.DataConnection.ToString());

con.Open();

trn = con.BeginTransaction(IsolationLevel.ReadCommitted);

isintran = true;

#endregion

#region "Create Header - - - - - - - - -"

com = new SQLiteCommand();

com.Connection = con;

com.CommandType = CommandType.Text;

com.CommandText = "INSERT INTO DocumentsHeader (DocumentCode, EmployeeName, EmployeeNotes, UserID) " +

"VALUES " +

"(@DocumentCode, @EmployeeName, @EmployeeNotes, @UserID)";

com.Parameters.Add("@DocumentCode", SqlDbType.NVarChar).Value = obj.DocumentCode.ToString().Trim();

com.Parameters.Add("@EmployeeName", SqlDbType.NVarChar).Value = obj.EmployeeName.ToString().Trim();

com.Parameters.Add("@EmployeeNotes", SqlDbType.NVarChar).Value = obj.EmployeeNotes.ToString().Trim();

com.Parameters.Add("@UserID", SqlDbType.NVarChar).Value = obj.UserID.ToString().Trim();

com.Transaction = trn;

com.ExecuteNonQuery();

com.Parameters.Clear();

com.Cancel();

com.Dispose();

#endregion

#region "Create Details - - - - - - - - -"

for (int i = 0; i < objlist.Count; i++)

{

com = new SQLiteCommand();

com.Connection = con;

com.CommandType = CommandType.Text;

com.CommandText = "INSERT INTO DocumentDetails (DocumentCode, DocumentName, DocumentType, DocumentExtention, DocumentPath, DocumentImage, DocumentFile, DocumentStatus, DocumentShared) " +

"VALUES (@DocumentCode, @DocumentName, @DocumentType, @DocumentExtention, @DocumentPath, @DocumentImage, @DocumentFile, @DocumentStatus, @DocumentShared)";

com.Parameters.Add("@DocumentCode", SqlDbType.NVarChar).Value = obj.DocumentCode.ToString().Trim();

com.Parameters.Add("@DocumentName", SqlDbType.NVarChar).Value = objlist[i].DocumentName.ToString().Trim();

com.Parameters.Add("@DocumentType", SqlDbType.NVarChar).Value = objlist[i].DocumentType.ToString().Trim();

com.Parameters.Add("@DocumentExtention", SqlDbType.NVarChar).Value = objlist[i].FileExtention.ToString().Trim();

com.Parameters.Add("@DocumentPath", SqlDbType.NVarChar).Value = objlist[i].DocumnetPath.ToString().Trim();

if (objlist[i].DocumentType.ToString().StartsWith("I") == true)

{

com.Parameters.Add("@DocumentImage", SqlDbType.Image).Value = objlist[i].Document;

com.Parameters.Add("@DocumentFile", SqlDbType.VarBinary).Value = DBNull.Value;

}

else

{

com.Parameters.Add("@DocumentImage", SqlDbType.Image).Value = DBNull.Value;

com.Parameters.Add("@DocumentFile", SqlDbType.VarBinary).Value = objlist[i].Document;

}

com.Parameters.Add("@DocumentStatus", SqlDbType.Bit).Value = objlist[i].Status;

com.Parameters.Add("@DocumentShared", SqlDbType.Bit).Value = objlist[i].IsShared;

com.Transaction = trn;

com.ExecuteNonQuery();

com.Parameters.Clear();

com.Cancel();

com.Dispose();

getvalue += 1;

OnChangeEventArgs evt = new OnChangeEventArgs(getvalue, objlist[i].DocumentName.ToString().Trim(), 0,0);

OnEventChange(evt);

System.Threading.Thread.Sleep(50);

}

#endregion

trn.Commit();

result = true;

}

catch (Exception ex)

{

if (isintran == true)

trn.Rollback();

result = false;

throw ex;

}

finally

{

if (com != null)

com.Dispose();

if (con != null)

con.Dispose();

com = null;

con = null;

}

return result;

}

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Priyaranjan K S11y ago
Hi,

This can occur if the connection is left open from a previous transaction or if the connection is closed prematurely before calling executenonquery . Place the connection object within the 'Using' statement and see. This way the connection will be closed automatically at the end of using statement . Place all your connection related logic within 'Using'

using (SQLiteConnection c = new SQLiteConnection(ConnectionString))
{
c.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, c))
{
cmd.ExecuteNonQuery();
}
}

You dont have to explicitly close the connection object .

Thanks,
Priyan

If you found this useful Please Accept this as Answer