DataReader cannot use detailed example of using

This article introduces a detailed example that DataReader can't use using, and shares it with you as follows:

public static MySqlDataReader ExecuteMySqlReader(string sqlStr)
{
MySqlConnection conn = new MySqlConnection(MyConString);
MySqlCommand cmd = new MySqlCommand(sqlStr, conn);
try
{
conn.Open();
// Execute CloseConnection Command, if you close the associated DataReader Object, the associated Connection Object will also close 
// Use using(conn) An error will be reported, because the connection will be closed after executing the command, and other codes will call DataReader Object, the connection has been closed. 
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
catch (Exception exp)
{
throw new Exception(exp.Message);
}
}

PS: MySqlDataReader is used in Using

Conclusion: When DataReader is placed in Using method, resources will be released automatically, and if exception handling occurs in the middle, the occupied resources will also be released.
Test process: Because there is no record of all the sub-process here, it is only a general explanation of the results. Interested children's shoes can be tested by themselves.

First of all, the normal processing flow:

MySqlDataReader dr = MySqlHelper.ExecuteReader(MySqlHelper.Conn, CommandType.Text, sqlStr, parameters)
while (dr.Read())
{
keyWords.Add(dr["KeyWord"].ToString());
}
dr.Close()

This seems to be no problem, but in the test, if "keyWords. Add (dr [" KeyWord "]. ToString ());" When an exception occurs, the program will be transferred to the exception handling module, so that the following close method will not be executed, resulting in the continuous accumulation of database connections. When the maximum value is reached, the problem will be revealed.

The first processing method below adopts exception handling:

MySqlDataReader dr = MySqlHelper.ExecuteReader(MySqlHelper.Conn, CommandType.Text, sqlStr, parameters);
try{
while (dr.Read())
{
keyWords.Add(dr["KeyWord"].ToString());
}
}
catch(){
}
finnally{
dr.Close();
}

There is no doubt that this method is easy to think of.

The second processing method is intended to be processed by using method. But according to theoretical knowledge, this is ok. However, the real program running environment can't be guided by theoretical knowledge sometimes. Now the program is here, and there is a very suitable test environment. Why not test it yourself? So there is a process of 1:
I am using MySql database, C # written procedures.

First, add some basic knowledge:

1. Using defines scope: Release resources immediately, and release resources at the end of scope. When you use an instance of a class in a snippet, you want to automatically call the Dispose method of that class instance to release resources whenever you leave the snippet for whatever reason.

The Dispose method that triggers the instance releases resources when an exception is thrown at or halfway through the using statement and control leaves the statement block.

Then look at the implementation of MySqlDataReader:

public sealed class MySqlDataReader : DbDataReader, IDataReader, IDisposable, IDataRecord { ... } 

It does inherit the IDisposable method, which should be correct in theory.

2. MySql to view the number of connections:

Command: show processlist; If it is an root account, you can see the current connection of all users. If it is other ordinary accounts, you can only see the connections you occupy.
show processlist; Only the first 100 items are listed. If you want to list them all, please use show full processlist;

With these two theoretical knowledge, the following test is much easier:

1. Do not use using or close the connection:

 MySqlDataReader dr = MySqlHelper.ExecuteReader(MySqlHelper.Conn, CommandType.Text, sqlStr, parameters);
 while (dr.Read())
 {
 keyWords.Add(dr["KeyWord"].ToString());
 }

The number of tests and connections is increasing.

2. Do not use, use closing operation:

MySqlDataReader dr = MySqlHelper.ExecuteReader(MySqlHelper.Conn, CommandType.Text, sqlStr, parameters);
while (dr.Read())
{
keyWords.Add(dr["KeyWord"].ToString());
}
dr.Close()

Test, the number of connections does not change.

3. Do not use Using, use closing operation, and make an exception in the intermediate execution process:

MySqlDataReader dr = MySqlHelper.ExecuteReader(MySqlHelper.Conn, CommandType.Text, sqlStr, parameters);
while (dr.Read())
{
keyWords.Add(dr["Keyord"].ToString());
}
dr.Close()

The number of tests and connections is increasing.

4. Using is adopted, and there is no abnormal situation:

Using(MySqlDataReader dr = MySqlHelper.ExecuteReader(MySqlHelper.Conn, CommandType.Text, sqlStr, parameters))
{
while (dr.Read())
{
keyWords.Add(dr["KeyWord"].ToString());
}
}

Test, the number of connections did not increase.

5. Make an exception in the middle of Using:

Using(MySqlDataReader dr = MySqlHelper.ExecuteReader(MySqlHelper.Conn, CommandType.Text, sqlStr, parameters))
{
while (dr.Read())
{
keyWords.Add(dr["Keyord"].ToString());
}
}

Test, the number of connections did not increase.