编辑:
@Jeroen Mostert 的评论效果很好:添加 Pooling=false
到连接字符串。正如他/她指出的那样,我必须阅读更多有关此添加对整体性能的影响的信息。
在下面的代码中有两个连续的
using
块(为了清楚起见,已经删除了很多);
using
在删除数据库之前阻止 camsdb
,我最后一次连接到数据库并做最后的查询。 using
阻止并应该关闭数据库连接。 using
块(应该删除刚刚在第一个 using 块中连接到的数据库)未能删除数据库。 PostgresException
说“还有 1 个其他 session 正在使用该数据库。” 但是,当我注释掉第一个
using
时块,第二个
using
块——履行其职责——毫无问题地删除数据库。当代码到达相关的花括号时,使用块不是应该关闭连接吗?有没有办法强制垃圾收集器立即清理连接? (欢迎任何其他解决方案。)
private M_SqlConn m_sqlConn = null; // predefined
...
...
// *** using block no: 1 ***
using (m_sqlConn = new M_SqlConn("127.0.0.1", 5432, "camsdb", "my_user_name", "my_password")) {
// perform a last sql query
}
// *** using block no: 2 ***
using (m_sqlConn = new M_SqlConn("127.0.0.1", 5432, "postgres", "postgres", "admin_password")) {
if (this.DbDrop("camsdb")) {
FormWarn_ShowDialog(FormWarn.FormType.Info, "db dropped succesfully ...")
}
else {
FormWarn_ShowDialog(FormWarn.FormType.Error, "can not drop db !!!")
}
}
M_SqlConn
整个类在下面(不想弄乱页面,这就是为什么我一开始没有粘贴它):
using Npgsql;
using System;
using System.Text;
internal class M_SqlConn : IDisposable {
public Boolean connectionIsOpen = false;
private FormWarn formWarn = null;
private NpgsqlConnection sqlConnection = null;
private String hostAddress = "";
private String portNumber = "";
private String dbName = "";
private String roleName = "";
private String password = "";
internal M_SqlConn(String hostAddress, String portNumber, String dbName, String roleName, String password) {
this.hostAddress = hostAddress;
this.portNumber = portNumber;
this.dbName = dbName;
this.roleName = roleName;
this.password = password;
this.ConnectionOpen();
return;
}
private void ConnectionOpen() {
StringBuilder exceptionString = new StringBuilder(String.Empty);
NpgsqlConnectionStringBuilder connectionStringBuilder = new NpgsqlConnectionStringBuilder {
Host = this.hostAddress,
Port = Convert.ToInt32(this.portNumber),
Database = this.dbName,
Username = this.roleName
};
this.sqlConnection = new NpgsqlConnection(connectionStringBuilder.ToString() + $";Password={this.password}");
try {
this.sqlConnection.Open();
if (this.sqlConnection.State == System.Data.ConnectionState.Open) {
this.connectionIsOpen = true;
}
}
catch (PostgresException e) {
exceptionString.AppendLine(
$"{Environment.NewLine}" +
$"Postgres Exception{Environment.NewLine}" +
$"{e.ToString()}{Environment.NewLine}");
}
catch (NpgsqlException e) {
exceptionString.AppendLine(
$"{Environment.NewLine}" +
$"Npgsql Exception{Environment.NewLine}" +
$"{e.ToString()}{Environment.NewLine}");
}
catch (Exception e) {
exceptionString.AppendLine(
$"{Environment.NewLine}" +
$"General Exception{Environment.NewLine}" +
$"{e.ToString()}{Environment.NewLine}");
}
if (!this.connectionIsOpen) {
this.FormWarn_ShowDialog(FormWarn.FormType.Error, exceptionString.ToString());
}
return;
}
private void ConnectionClose() {
StringBuilder exceptionString = new StringBuilder(String.Empty);
if (this.sqlConnection != null) {
try {
this.sqlConnection.Close();
this.connectionIsOpen = false;
}
catch (PostgresException e) {
exceptionString.AppendLine(
$"{Environment.NewLine}" +
$"Postgres Exception{Environment.NewLine}" +
$"{e.ToString()}{Environment.NewLine}");
}
catch (NpgsqlException e) {
exceptionString.AppendLine(
$"{Environment.NewLine}" +
$"Npgsql Exception{Environment.NewLine}" +
$"{e.ToString()}{Environment.NewLine}");
}
catch (Exception e) {
exceptionString.AppendLine(
$"{Environment.NewLine}" +
$"General Exception{Environment.NewLine}" +
$"{e.ToString()}{Environment.NewLine}");
}
if (this.connectionIsOpen) {
this.FormWarn_ShowDialog(FormWarn.FormType.Error, exceptionString.ToString());
}
else {
this.sqlConnection.Dispose();
this.sqlConnection = null;
}
}
return;
}
public Boolean SqlCommandExecuteNonQuery(String sqlString) {
Boolean commandStatus = false;
StringBuilder exceptionString = new StringBuilder(String.Empty);
using (NpgsqlCommand sqlCommand = new NpgsqlCommand(sqlString, this.sqlConnection)) {
try {
sqlCommand.ExecuteNonQuery();
commandStatus = true;
}
catch (PostgresException e) {
exceptionString.AppendLine(
$"{Environment.NewLine}" +
$"Postgres Exception{Environment.NewLine}" +
$"{e.ToString()}{Environment.NewLine}");
}
catch (NpgsqlException e) {
exceptionString.AppendLine(
$"{Environment.NewLine}" +
$"Npgsql Exception{Environment.NewLine}" +
$"{e.ToString()}{Environment.NewLine}");
}
catch (Exception e) {
exceptionString.AppendLine(
$"{Environment.NewLine}" +
$"General Exception{Environment.NewLine}" +
$"{e.ToString()}{Environment.NewLine}");
}
};
if (!commandStatus) {
exceptionString.AppendLine(
$"{Environment.NewLine}" +
$"Sql string is:{Environment.NewLine}" +
$"{sqlString}{Environment.NewLine}");
this.FormWarn_ShowDialog(FormWarn.FormType.Error, exceptionString.ToString());
}
return (commandStatus);
}
public Int32 SqlCommandExecuteScalar(String sqlString) {
Int32 count = -1;
StringBuilder exceptionString = new StringBuilder(String.Empty);
using (NpgsqlCommand sqlCommand = new NpgsqlCommand(sqlString, this.sqlConnection)) {
try {
Int32 countTmp = Convert.ToInt32(sqlCommand.ExecuteScalar());
count = countTmp;
}
catch (PostgresException e) {
exceptionString.AppendLine(
$"{Environment.NewLine}" +
$"Postgres Exception{Environment.NewLine}" +
$"{e.ToString()}{Environment.NewLine}");
}
catch (NpgsqlException e) {
exceptionString.AppendLine(
$"{Environment.NewLine}" +
$"Npgsql Exception{Environment.NewLine}" +
$"{e.ToString()}{Environment.NewLine}");
}
catch (Exception e) {
exceptionString.AppendLine(
$"{Environment.NewLine}" +
$"General Exception{Environment.NewLine}" +
$"{e.ToString()}{Environment.NewLine}");
}
};
if (count == -1) {
exceptionString.AppendLine(
$"{Environment.NewLine}" +
$"Sql string is:{Environment.NewLine}" +
$"{sqlString}{Environment.NewLine}");
this.FormWarn_ShowDialog(FormWarn.FormType.Error, exceptionString.ToString());
}
return (count);
}
public NpgsqlDataReader SqlCommandExecuteQuery(String sqlString) {
NpgsqlDataReader dataReader = null;
StringBuilder exceptionString = new StringBuilder(String.Empty);
using (NpgsqlCommand sqlCommand = new NpgsqlCommand(sqlString, this.sqlConnection)) {
try {
dataReader = sqlCommand.ExecuteReader();
}
catch (PostgresException e) {
exceptionString.AppendLine(
$"{Environment.NewLine}" +
$"Postgres Exception{Environment.NewLine}" +
$"{e.ToString()}{Environment.NewLine}");
}
catch (NpgsqlException e) {
exceptionString.AppendLine(
$"{Environment.NewLine}" +
$"Npgsql Exception{Environment.NewLine}" +
$"{e.ToString()}{Environment.NewLine}");
}
catch (Exception e) {
exceptionString.AppendLine(
$"{Environment.NewLine}" +
$"General Exception{Environment.NewLine}" +
$"{e.ToString()}{Environment.NewLine}");
}
};
if (exceptionString.Length > 0) {
exceptionString.AppendLine(
$"{Environment.NewLine}" +
$"Sql string is:{Environment.NewLine}" +
$"{sqlString}{Environment.NewLine}");
this.FormWarn_ShowDialog(FormWarn.FormType.Error, exceptionString.ToString());
}
return (dataReader);
}
private void FormWarn_ShowDialog(FormWarn.FormType formType, String msg) {
using (this.formWarn = new FormWarn(formType, msg)) {
this.formWarn.ShowDialog();
}
this.formWarn.Dispose();
this.formWarn = null;
return;
}
public void Dispose() {
if (this.formWarn != null) {
this.formWarn.Dispose();
this.formWarn = null;
}
if (this.connectionIsOpen) {
this.ConnectionClose();
}
this.hostAddress = "";
this.portNumber = "";
this.dbName = "";
this.roleName = "";
this.password = "";
return;
}
}
我试过@Panagiotis 的伎俩,但没有奏效。更改了
if
阻止到下面,但这次程序停止给出“System.NullReferenceException:'对象引用未设置为对象的实例。'”
if (this.sqlConnection.State == System.Data.ConnectionState.Open) {
this.sqlConnection.Dispose(); // exception here
this.sqlConnection = null;
}
请您参考如下方法:
你,当然,已经知道了,但让我们解决一些概念。
什么 using
在 C# 中意味着什么?
这意味着该类是否实现了 IDisposable
, Dispose()
方法将在对象实例生命周期结束时调用。
谁打开和关闭连接?
图书馆做到了。您只需调用库公开的某个类的实例的某个方法,并希望它为您完成这项工作。
为什么要保持连接?
大多数情况下,您确实希望与您的应用程序重用相同的连接,而且大多数情况下这很好,因为每个连接都会消耗资源。
这就是我们有连接池的原因。
你能做些什么来缓解你的问题?
您可以按照评论中的建议尝试另一个库。
或查看 TemaTre 回答 她/他看起来对那个特定的图书馆有很好的洞察力。
但也要注意你正在做一些不寻常的事情。
您确实有两个连接(和两个连接池?)。
一种用于“普通”用户查询数据库。
管理员删除数据库的第二个连接。
我可以建议你先把数据库放在单用户模式 并确保没有其他连接,即使是应用程序外部的连接。您可以在删除 DB 之前使用简单的 SQL 命令来实现它。