在何处/何时使用connection.close()
本文关键字:close connection 何时使 在何处 | 更新日期: 2023-09-27 18:25:24
有人能帮我连接吗。打开()和关闭()。我不太确定何时关闭和打开连接。下面添加了给我带来错误的代码。
如果有人能给我一个提示,我将不胜感激。请随时编辑我的代码,显示在哪里关闭连接,以便我从中学习。
我还是个学生。谢谢
public class LoanDAL
{
string connString = ConfigurationManager.ConnectionStrings["Oakhorizons"].ToString();
public LoanDAL()
{
//
// TODO: Add constructor logic here
//
}
public DataTable getAllLoanInfoDT()
{
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = conn;
// cmd.CommandType = CommandType.StoredProcedure;
cmd2.CommandText = "SELECT DISTINCT loanUpdateDate FROM LoanPortfolio WHERE (custID LIKE 'OH00002') AND (loanType LIKE 'Personal Loan')";
cmd2.Parameters.AddWithValue("@custID", "OH00002");
cmd2.Parameters.AddWithValue("@loanType", "Personal Loan");
conn.Open();
DateTime loanUpdateDate = DateTime.Now;
SqlDataReader myReader = cmd2.ExecuteReader();
while (myReader.Read())
{
loanUpdateDate = Convert.ToDateTime(myReader[0]);
break;
}
DateTime currDateTime = DateTime.Now;
int loanToBeAdded = (((currDateTime.Year - loanUpdateDate.Year) * 12) + currDateTime.Month - loanUpdateDate.Month) * 500;
if (loanToBeAdded > 0)
{
String sql = "UPDATE LoanPortfolio SET loanPaid = loanPaid + " + loanToBeAdded.ToString() + ", LastUpdatedLoanPaidDate = " + DateTime.Now.ToString();
sql += " WHERE (loanType LIKE 'Personal Loan') AND (custID LIKE 'OH00002')";
cmd2.Connection = conn;
cmd2.CommandText = sql;
cmd2.ExecuteNonQuery();
}
conn.Close();
using (SqlDataAdapter dAd = new SqlDataAdapter("SELECT * FROM LoanPortfolio where custID like 'OH00002'", conn))
{
DataTable dTable = new DataTable();
dAd.Fill(dTable);
return dTable;
}
}
}
//Returning a DataSet which contains all the information in the Player Table
public DataSet getAllLoanInfoDS()
{
using (SqlConnection conn = new SqlConnection(connString))
{
using (SqlDataAdapter dAd = new SqlDataAdapter("SELECT * FROM LoanPortfolio where custID like 'OH00002", conn))
{
DataSet myDS = new DataSet();
dAd.Fill(myDS);
return myDS;
}
}
}
}
您不必显式关闭连接,因为您使用了带有sqlconnection对象的{}块。连接将自动关闭。
第二,如果您想显式关闭连接,那么在所有数据库操作完成后将其关闭。
例如,在适配器填充操作完成时将其关闭。
....
....
....
using (SqlDataAdapter dAd = new SqlDataAdapter("SELECT * FROM LoanPortfolio where custID like 'OH00002'", conn))
{
DataTable dTable = new DataTable();
dAd.Fill(dTable);
conn.Close();
return dTable;
}
本节:
conn.Close();
using (SqlDataAdapter dAd = new SqlDataAdapter("SELECT * FROM LoanPortfolio where custID like 'OH00002'", conn))
{
DataTable dTable = new DataTable();
dAd.Fill(dTable);
return dTable;
}
关闭后您正在重新使用同一个连接。请重新打开并重复使用。或者根本不要关闭,因为您仍在同一个using
块中。
更新:MSDN:http://msdn.microsoft.com/en-us/library/bh8kx08z(v=vs.100).aspx
Fill方法隐式打开DataAdapter如果发现连接尚未打开,则使用。如果填充打开连接,当Fill为完成。
因此,在上面的部分"conn.close()"应该无关紧要,"Fill"将打开并关闭连接。
using
块将在完成连接后处理掉连接。因此,不需要显式关闭连接。
Sql dataAdapter本身管理连接。它在填充命令后打开和关闭。然而,使用ExecuteReader或ExecuteNonQuery明确要求打开和关闭连接,您可以在执行nonquery和ExecuteReader命令之前打开连接,如果您的代码遇到错误,并且代码没有到达conn.close(),那么还会发生什么情况命令。
public class LoanDAL
{
string connString = ConfigurationManager.ConnectionStrings["Oakhorizons"].ToString();
public LoanDAL()
{
//
// TODO: Add constructor logic here
//
}
public DataTable getAllLoanInfoDT()
{
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = conn;
// cmd.CommandType = CommandType.StoredProcedure;
cmd2.CommandText = "SELECT DISTINCT loanUpdateDate FROM LoanPortfolio WHERE (custID LIKE 'OH00002') AND (loanType LIKE 'Personal Loan')";
cmd2.Parameters.AddWithValue("@custID", "OH00002");
cmd2.Parameters.AddWithValue("@loanType", "Personal Loan");
DateTime loanUpdateDate = DateTime.Now;
try
{
conn.Open();
SqlDataReader myReader = cmd2.ExecuteReader();
while (myReader.Read())
{
loanUpdateDate = Convert.ToDateTime(myReader[0]);
break;
}
conn.Close();
DateTime currDateTime = DateTime.Now;
}
Catch(Exception Ex)
{//Close connection in case of any exception .
conn.Close();
}
int loanToBeAdded = (((currDateTime.Year - loanUpdateDate.Year) * 12) + currDateTime.Month - loanUpdateDate.Month) * 500;
if (loanToBeAdded > 0)
{
try
{
String sql = "UPDATE LoanPortfolio SET loanPaid = loanPaid + " + loanToBeAdded.ToString() + ", LastUpdatedLoanPaidDate = " + DateTime.Now.ToString();
sql += " WHERE (loanType LIKE 'Personal Loan') AND (custID LIKE 'OH00002')";
cmd2.Connection = conn;
cmd2.CommandText = sql;
conn.Open();
cmd2.ExecuteNonQuery();
conn.Close();
}
Catch(Exception Ex)
{
//Close connection in case of exception .
Conn.close()
throw Ex;
}
}
using (SqlDataAdapter dAd = new SqlDataAdapter("SELECT * FROM LoanPortfolio where custID like 'OH00002'", conn))
{
DataTable dTable = new DataTable();
dAd.Fill(dTable);
return dTable;
}
}
}
//Returning a DataSet which contains all the information in the Player Table
public DataSet getAllLoanInfoDS()
{
using (SqlConnection conn = new SqlConnection(connString))
{
using (SqlDataAdapter dAd = new SqlDataAdapter("SELECT * FROM LoanPortfolio where custID like 'OH00002", conn))
{
DataSet myDS = new DataSet();
dAd.Fill(myDS);
return myDS;
}
}
}
}
您的连接字符串需要添加"MultipleActiveResultSets=True"。如果您使用多个命令,就像在一个连接中有两个命令一样,请在连接字符串中启用"MultipleActiveResultSets=True",或者在与服务器的每次事务后打开并关闭(每个连接一个命令)