Click here to Skip to main content
15,882,388 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
When I want to save the record using C# with sqlite database, it gives an exception that the database is locked , I have already checked my connection open and close properly.
I am using that structure for opening and closing
C#
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;
}</clsdocumentdetails>
Posted
Updated 18-Oct-15 22:53pm
v2

1 solution

Sqlite database is a single client write only engine, all writes to a table will lock the entire database for the duration of the action : http://www.sqlite.org/faq.html#q5[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900