If we can't get the output parameters until the SqlDataReader is closed, then why ...?
1)
While the SqlDataReader is in use, the associated SqlConnection is occupied by the SqlDataReader service, and no other operation can be performed on the SqlConnection other than closing. This occurs until the Close method is called on the SqlDataReader. For example, you cannot get the output parameters until you call Close.
If the above statement is true, then why can the following method get the value from the out parameter before closing the reader:
public int Something()
{
using (SqlConnection con = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("some_procedure", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ID", SqlDbType.Int).Direction = ParameterDirection.Output;
con.Open();
cmd.ExecuteReader();
return (int)cmd.Parameters["@ID"].Value;
}
}
2)
You can reset the CommandText property and reuse the SqlCommand object. However, you must close the SqlDataReader before you can execute the new or previous command.
Why do you have to close sqldatareader before executing a new command?
thank
a source to share
Sorry to point it out so bluntly, but you don't seem to have a clear idea of what exactly the data reader ( System.Data.IDataReader
) is. So let me demonstrate:
using System;
using System.Data;
using System.Data.SqlClient;
string connectionString = "...";
// create and open a DB connection:
using (IDbConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// create a command object:
using (IDbCommand query = connection.CreateCommand())
{
query.CommandType = CommandType.Text;
query.CommandText = "SELECT ID, Name, Birthdate FROM SomeTable";
// execute the query and retrieve a data reader object:
using (IDataReader reader = query.ExecuteReader())
{
// the data reader is now used to read the query result:
while (reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
DateTime birthdate = reader.GetDateTime(0);
}
}
// at this point, the data reader object has been disposed.
// the DB connection can now be used again, e.g. by issuing
// another query:
command.CommandText = "SELECT COUNT(*) FROM SomeOtherTable";
...
}
}
Note. I refrained from using it var
, so you can see exactly what types are involved. (Note also that I mostly use type interface types IDbConnection
instead of concrete implementation classes such as SqlConnection
. This makes the code more adaptable to other RDBMSs.)
Now you can see that you have never used a data reader in your code. So your code worked because there was never any open data reader that could "block" your DB connection in the first place.
a source to share