First, declare the following:
1. It may be a bit elementary to explain. I hope that the experts will not "spray" me, because I know that not everyone is a master, and I am afraid that the experts will say that I have installed 13;
2. If there is anything wrong with it, I hope you can point out that you must learn it modestly;
3. This article is original to the author and respects the fruits of others' work. For more information, see the author. Thank you.
Let's start with the following:
When I first encountered this problem, I also checked a lot of information on the Internet. It seems that I didn't find any good solutions, and then I started to debug the program. I found that sometimes there is no error when running, sometimes it is difficult to run an error, but later I found that I put ASP. NET Development Server-port xxx exits, and then runs the program, no error is reported... I thought it was okay. But I found a problem later. As long as the port is not closed or the page is constantly refreshed during the program running, an error will be reported "The DataReader associated with this command has been opened, you must first disable it."
I got angry. I started to put N breakpoints in the program and debug them in one step. I didn't find that I used the DataReader object. That's strange. Why didn't I use the DataReader object, but does it include such errors? I am speechless. After repeated scrutiny, I finally found a solution. It turns out that this exception is not necessarily related to DataReader, but may also be related to the Connection object!
See the following code:
Copy codeThe Code is as follows: private static SqlConnection Sqlconnection;
Public static 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 eyes of ordinary people, should be okay, I think there is no problem. The first code is to create a data connection and open a database connection. The second code is to create a SqlCommand, where SqlConnection is used as the first parameter, which is the key to the problem.
If you run the sqlgetable able () method once on the page, there will be no problem. If you refresh the page without stopping, the page will be "A DataReader associated with this command has been opened, you must first disable it." This exception may not occur. The red part of the code indicates the error prompt)
After my thinking, I finally found a solution. The solution is as follows:Copy codeThe Code is 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];
}
}
The above method does not use the SqlConnection method, but writes the SqlConnection object to using.
PS: I personally suggest you do not use the first method. It may be very troublesome. I suggest using the using () {} statement to put ADO. NET has no objects in using () {}. I think everyone knows the benefits of using () {}, so I will not explain it too much.
