First declare the following points:
1, perhaps a little beginner, I hope the master do not "spray" me, because I know not everyone is a master, I am afraid of the experts said I loaded 13;
2, if there is anything wrong place, but also hope that you must be open-minded to learn;
3, this article belongs to the author original, respect others labor achievements, reproduced please indicate the author, thank you.
Here's the lecture:
Started in the face of this problem when I also looked up a lot of information on the Internet, seemingly did not find a good solution, and then I began to debug the program, I found that sometimes the operation does not complain, sometimes run an error, this is very tangled, but then I found that I put ASP.net Development Server -Port XXX exit, and then run the program will not be the error ... I thought it was good, can later I have found a problem, as long as the port is not closed or when the program runs the page is constantly refreshed, it will error "there is an open DataReader associated with this command, you must first close it." ”
I fire, I began to put the program under the N breakpoints, one step debugging, I did not find that I was using the DataReader object Ah, this is strange, why I do not use DataReader object, it is such a mistake? I am very silent also. After my repeated scrutiny, finally found a solution, the original this anomaly is not necessarily related to DataReader oh, but also may be related to connection objects Oh!
Look at the following code:
Copy Code code as follows:
private static SqlConnection SqlConnection;
public static SqlConnection SqlConnection
{
Get
{
String sqlconnectionstring = system.configuration.configurationmanager.connectionstrings["TandyTang"]. ToString ();
if (SqlConnection = null)
{
SqlConnection = New SqlConnection (sqlConnectionString);
Sqlconnection.open ();
}
else if (sqlconnection.state = = System.Data.ConnectionState.Closed)
{
Sqlconnection.open ();
}
else if (sqlconnection.state = = System.Data.ConnectionState.Broken)
{
Sqlconnection.close ();
Sqlconnection.open ();
}
return SqlConnection;
}
}
<summary>
Sqlgetdatatable
<param name= "Proc" ></param>
<param name= "Type" ></param>
<param name= "param" ></param>
<param name= "Count" ></param>
<returns>DataTable</returns>
public static DataTable sqlgetdatatable (String proc, CommandType type, string[] param, out int count)
{
DataSet ds = new DataSet ();
using (SqlCommand cmd = new SqlCommand (proc, SqlConnection))
{
sqlparameter[] myparms = new sqlparameter[2];
Myparms[0] = new SqlParameter ("@Id", SqlDbType.Int, 4);
Myparms[0]. Value = paramvalue[0];
MYPARMS[1] = new SqlParameter ("@Name", SqlDbType.VarChar, 50);
MYPARMS[1]. Value = paramvalue[1];
foreach (SqlParameter parameter in myparms)
{
Cmd. Parameters.Add (parameter);
}
Cmd.commandtype = type;
SqlDataAdapter da = new SqlDataAdapter (cmd);
Da. Fill (DS);
}
Return DS. Tables[0];
}
The above code, I believe in the General people's opinion, should be no problem, I also think there is no problem. The first piece of code is to create a data connection and open a database connection, and the second code is to create a SqlCommand that uses SqlConnection as the first parameter, which is the key to the problem.
If you run a value on the page sqlgetdatatable () method will not have any problems, if the page is constantly refreshed page on the "already open with this command associated with the DataReader, you must first turn it off." "Such an anomaly (maybe not, oh, you'll know if you try.) Code Red font part is where the error is prompted
After my thinking, finally found a solution, the solution is as follows, look at the following code:
Copy Code code as follows:
private static string connectionString = system.configuration.configurationmanager.connectionstrings["Tandytang"]. ConnectionString;
<summary>
Sqlgetdatatable
<param name= "Proc" ></param>
<param name= "Type" ></param>
<param name= "param" ></param>
<param name= "Count" ></param>
<returns>DataTable</returns>
public static DataTable sqlgetdatatable (String proc, CommandType type, string[] param, out int count)
{
DataSet ds = new DataSet ();
using (SqlConnection con = new SqlConnection (connectionString))
{
using (SqlCommand cmd = new SqlCommand (proc, con))
{
sqlparameter[] myparms = new SQLPARAMETER[11];
Myparms[0] = new SqlParameter ("@Id", SqlDbType.Int, 4);
Myparms[0]. Value = paramvalue[0];
MYPARMS[1] = new SqlParameter ("@Name", SqlDbType.VarChar, 50);
MYPARMS[1]. Value = paramvalue[1];
foreach (SqlParameter parameter in myparms)
{
Cmd. Parameters.Add (parameter);
}
Cmd.commandtype = type;
using (SqlDataAdapter da = new SqlDataAdapter (cmd))
{
Da. Fill (DS);
}
}
Return DS. Tables[0];
}
}
It should be found that there are different places, the above method, does not use the SqlConnection method, but the SqlConnection object written to the using () {}.
PS: Personal advice not to use the first method, you may encounter a very troublesome Oh, personal suggestion to use the using () {} statement to put Ado.net objects in the using () {} is best, the benefits of using () {} I think we all know, so I do not do too much explanation.
