Click here to Skip to main content
15,921,530 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi.

I have a sqlite data base which has a table named Customers. And I implemented few functions to change the data about a customer.

They are like

C#
changeName(int id, string newName);
chgangeTP(int id, string newTP);
changeAddress(int id, string newAddress);
changeEmail(int id, string newEmail);
changeNotes(int id, string newNotes);


and my problem is sqlite locks the database for editing purposes, as they say.

So if user wants to change multiple columns at once, it would cause an error.

for example >>

if user tried to edit both name and address, the program first calls changeName() function which locks the database to edit the name and then unlocks, and then it calls changeAddress() function which locks the database againg to edit the address and then unlocks again.

if program would be a bit faster than the database locking and unlocking task, the program will be trying to edit the address before it unlocks after editing the name.

So I want to know if there's a way to check if sqlite database or data table is locked.
Just like >>

C#
while(myDatabase.Locked)
 thread.Sleep(300);

//unlocked
changeAddress(500, "hell");


thanks in advance !
Posted

1 solution

If your code is locking the DB, that's because your code is not releasing it: you need to either use a common connection which you never release, or release the connection properly after each access. Adding waiting on your thread until it's released will not help that - becuas ethe thread that is holding them is the one that is waiting... :laugh:

At the moment, I'd guess that each of your "changeXXX" methods tries to connect to the DB, make the change, and exit - which doesn't release the DB file until the garbage collector gets round to it.
So change your code to use using blocks round the Connection object (and the Command objects, and so forth) to ensure that the file is released properly when you are "finished" with it.
 
Share this answer
 
Comments
M­­ar­­­­k 21-Sep-14 3:51am    
u say, if I use a single connection for all of my commands, it's okay to execute multiple commands at once and change the Address while another command is changing the name ?
OriginalGriff 21-Sep-14 4:08am    
Well - unless you are multithreading (and for a single user app you probably aren't) you can't do that anyway, as the single thread will execute the instructions serially.
So yes, you can open a connection when you start the app, and hold it open until you exit. That would block any other apps from accessing the file, which is generally ok for SQLite databases - except that backup systems can't copy the file either. You will probably have to make sure you close and dispose DataReaders (as you can't attach commands to a connection that has an open reader).

I'd probably open-process-close manually, and open-process-process-process-close for multiple operations myself rather than hold the DB open (I like backups, lots of backups! :laugh:)
M­­ar­­­­k 21-Sep-14 5:33am    
Sorry. But I still dont get that completely.. Just answer me to this...

if I change my code to this >>

using(SQLiteConnection con = new SQLiteConnection(conString))
{
con.Open();
using(SQLiteCommand commandToChangeName = new SQLiteCommand(sql1, con))
{
­ commandToChangeName.ExecuteNonQuery();
}
using (SQLiteCommand commandToChangeAddress = new SQLiteCommand(sql2, con))
{
­ commandToChangeAddress.ExecuteNonQuery();
}
}

1. Would it make sure that the second sqlite command (which changes the address) executes after the first one executed and it's editings has completed ?
OriginalGriff 21-Sep-14 6:01am    
Yes.
The only change I'd make is to add a Transaction round both INSERT / UPDATE Commands, so that if one fails the other can be "undone" - if your DB needs both changes correct, then it could be left in an "odd" state otherwise.

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