SQLite 数据库在插入查询时被锁定
本文关键字：锁定 查询 插入 数据库 SQLite | 更新日期: 2023-09-27 18:30:53
我将分享我的助手类。我在使用 SQLiteAddcustomer 函数时遇到数据库被锁定错误。我找到了一些带有"using"语句的解决方案，但问题仍然存在。我该如何解决这个问题？
public class SQLiteHelper
{
String gDatabaseName = @"Data Source=RTM_Spark.sqlite;Version=3;";
SQLiteConnection gConnection;
SQLiteCommand gCommand;
SQLiteDataReader gDataReader;
SQLiteDataReader gDataReaderRoot;
SQLiteDataReader gDataReaderDirectory;
SQLiteDataReader gDataReaderCustomer;
SQLiteDataReader gDataReaderDevice;
public SQLiteHelper()
{
gDatabaseName = @"Data Source=RTM_Spark.sqlite;Version=3;";
}
public SQLiteHelper(String pDatabaseName)
{
gDatabaseName = String.Format("Data Source={0}", pDatabaseName);
}
public void SQLiteExecuteQuery(String pSQLCommand)
{
gCommand = new SQLiteCommand(gConnection);
gCommand.CommandText = pSQLCommand;
gCommand.ExecuteNonQuery();
}
public int SQLiteGetNodeID(String pSelectedNode)
{
int tSubID = 0;
if (gConnection.State.ToString() == "Open")
{
gCommand = gConnection.CreateCommand();
this.ExecuteNonQuery(String.Format("SELECT ID FROM tblNodes where isVisible=1 AND nodeName='{0}'", pSelectedNode));
gDataReader = gCommand.ExecuteReader();
while (gDataReader.Read())
{
tSubID = Convert.ToInt32(gDataReader["ID"]);
}
}
return tSubID;
}
public int SQLiteGetNodeType(String pSelectedNode)
{
int tNodeType = 0;
if (gConnection.State.ToString() == "Open")
{
gCommand = gConnection.CreateCommand();
this.ExecuteNonQuery(String.Format("SELECT nodeType FROM tblNodes where isVisible=1 AND nodeName='{0}'", pSelectedNode));
gDataReader = gCommand.ExecuteReader();
while (gDataReader.Read())
{
tNodeType = Convert.ToInt32(gDataReader["nodeType"]);
}
}
return tNodeType;
}
public void SQLiteAddCustomer(TreeNode pSelectedNode, String pCustomerName)
{
int tNodeType = 99;
if (gConnection.State.ToString() == "Open")
{
gCommand = gConnection.CreateCommand();
this.ExecuteNonQuery(String.Format("SELECT nodeType FROM tblNodes where isVisible=1 AND nodeName='{0}'", pCustomerName));
SQLiteDataReader tDataReader = gCommand.ExecuteReader();
while (tDataReader.Read())
{
tNodeType = Convert.ToInt32(gDataReader["nodeType"]);
}
if (tNodeType == 0 || tNodeType == 1 || tNodeType == 2)
{
MessageBox.Show(pCustomerName + " already saved on your list.");
}
else
{
int tSelectedID = SQLiteGetNodeID(pSelectedNode.Text);
int tSelectedNodeType = SQLiteGetNodeType(pSelectedNode.Text);
if (tSelectedNodeType == 0)
{
gCommand = gConnection.CreateCommand();
this.ExecuteNonQuery(String.Format("INSERT INTO tblNodes (nodeName,nodeType,nodeSubID,isVisible) VALUES ('{0}',1,'{1}',1)", pCustomerName, tSelectedID));
}
else
MessageBox.Show("You can add Customer under directory");
}
}
}
public int ExecuteNonQuery(String pSQLCommand)
{
gConnection = new SQLiteConnection(gDatabaseName);
gConnection.Open();
gCommand = new SQLiteCommand(gConnection);
gCommand.CommandText = pSQLCommand;
int rowsUpdated = gCommand.ExecuteNonQuery();
return rowsUpdated;
}
public void SQLiteOpenConnection()
{
gConnection = new SQLiteConnection(gDatabaseName);
gConnection.Open();
}
public void SQLiteCloseConnection()
{
gConnection.Close();
}
public int SQLiteGetRootID()
{
int tRootID = 0;
if (gConnection.State.ToString() == "Open")
{
gCommand = gConnection.CreateCommand();
this.ExecuteNonQuery(String.Format("SELECT ID FROM tblNodes where nodeName='{0}'", "RTM Spark"));
gDataReader = gCommand.ExecuteReader();
while (gDataReader.Read())
{
tRootID = Convert.ToInt32(gDataReader["ID"]);
}
}
gConnection.Close();
return tRootID;
}
public void UpdateTreeView(TreeView pTreeView)
{
refreshDatabaseConnection();
pTreeView.Nodes.Clear();
TreeNode rootNode = pTreeView.Nodes.Add("RTM Spark");
getDirectoryNameQuery();
while (gDataReaderRoot.Read())
{
String tDirectoryName = gDataReaderRoot["nodeName"].ToString();
TreeNode directoryNode = rootNode.Nodes.Add(tDirectoryName);
rootNode.ExpandAll();
getDirectoryIDQuery(tDirectoryName);
while (gDataReaderDirectory.Read())
{
int tDirectoryID = Convert.ToInt32(gDataReaderDirectory["ID"]);
getCustomerNameQuery(tDirectoryID);
while (gDataReaderDirectory.Read())
{
String tCustomerName = gDataReaderDirectory["nodeName"].ToString();
TreeNode tCustomerNode = directoryNode.Nodes.Add(tCustomerName);
tCustomerNode.ExpandAll();
directoryNode.ExpandAll();
getCustomerIDQuery(tCustomerName);
while (gDataReaderCustomer.Read())
{
int tCustomerID = Convert.ToInt32(gDataReaderCustomer["ID"]);
getDeviceNameQuery(tCustomerID);
while (gDataReaderDevice.Read())
{
String tDeviceName = gDataReaderDevice["nodeName"].ToString();
TreeNode tDeviceNode = tCustomerNode.Nodes.Add(tDeviceName);
tDeviceNode.ExpandAll();
tCustomerNode.ExpandAll();
directoryNode.ExpandAll();
}
}
}
}
}
gConnection.Close();
}
public void refreshDatabaseConnection()
{
gConnection.Dispose();
gConnection.Close();
gConnection = new SQLiteConnection(gDatabaseName);
gConnection.Open();
}
public void getDirectoryNameQuery()
{
int tRootID = SQLiteGetRootID();
gCommand = gConnection.CreateCommand();
this.ExecuteNonQuery(String.Format("SELECT nodeName from tblNodes where isVisible=1 AND nodeSubID='{0}'", tRootID));
gDataReaderRoot = gCommand.ExecuteReader();
}
public void getDirectoryIDQuery(String pDirectoryName)
{
this.ExecuteNonQuery(String.Format("SELECT ID from tblNodes where isVisible=1 AND nodeName='{0}'", pDirectoryName));
gDataReaderDirectory = gCommand.ExecuteReader();
}
public void getCustomerNameQuery(int pDirectoryID)
{
this.ExecuteNonQuery(String.Format("SELECT nodeName from tblNodes where isVisible=1 AND nodeSubID='{0}'", pDirectoryID));
gDataReaderDirectory = gCommand.ExecuteReader();
}
public void getCustomerIDQuery(String pCustomerName)
{
this.ExecuteNonQuery(String.Format("SELECT ID from tblNodes where isVisible=1 AND nodeName='{0}'", pCustomerName));
gDataReaderCustomer = gCommand.ExecuteReader();
}
public void getDeviceNameQuery(int pCustomerID)
{
this.ExecuteNonQuery(String.Format("SELECT nodeName from tblNodes where isVisible=1 AND nodeSubID='{0}'", pCustomerID));
gDataReaderDevice = gCommand.ExecuteReader();
}
}
}
您可以在帮助程序类中使用锁定 mechanishm，在执行命令之前，您可以锁定并执行插入或更新。此锁将不允许其他线程进入此步骤进行插入或删除。在帮助程序中声明锁定对象
public static object LockObject = new object();
插入/更新前锁定
if (tSelectedNodeType == 0)
{
gCommand = gConnection.CreateCommand();
lock(LockObject)
{
this.ExecuteNonQuery(String.Format("INSERT INTO tblNodes (nodeName,nodeType,nodeSubID,isVisible) VALUES ('{0}',1,'{1}',1)", pCustomerName, tSelectedID));
}
}