Danish Habib

Danish Habib

  • NA
  • 694
  • 242.8k

SQLite database is locked

Oct 19 2015 4:22 AM
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<ClsDocumentDetails> 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;
}

Answers (1)