Last post Sep 13, 2014 10:47 PM by wmec
Sep 11, 2014 08:11 PM|roadman19771|LINK
I am creating an ASP.NET site. I need to write two SQL queries but only one Sql Data Reader is allowed. My web hosting service uses Microsoft SQL Server.
I have tried MultipleActiveResultSets=true in the connection string with no luck.
Here is my code:
SqlConnection conn2 = new SqlConnection("Data Source=**********;Integrated Security=False;User ID=********;password=********");
SqlCommand com2 = new SqlCommand("SELECT COUNT (*) FROM SiteUser WHERE Username = @Username", conn2);
SqlDataReader Read = com2.ExecuteReader();
SqlCommand com3 = new SqlCommand("SELECT COUNT (*) FROM SiteUser WHERE License = @License", conn2);
SqlDataReader Read3 = com3.ExecuteReader();
How do I execute two or more SQL statements?
Thanks in advance for your help.
Sep 11, 2014 09:08 PM|oned_gk|LINK
Try close reader before do another reader
Sep 12, 2014 01:49 AM|markfitzme|LINK
Why is only one datareader allowed? You should be able to open as many as you need so long as you have enough connections in the connection pool. It could be due to the fact you are using one connection for multiple commands. Don't. Set the each command
to use it's own connection, open the connection right before you call the ExecuteReader, then close the connection as soon as the reader is done. Repeat that for the other reader.
DataReaders aren't the kind of thing you want lying around since they essentially leave a connection open and they only read one row at a tame as you iterate through the rows.
In this case specifically though, they are a complete waste for you. You are only reading one row so you really don't need a datareader at all.
Use the SqlCommand method called ExecuteScalar() instead;
You can do:
int myInt = (int)com2.ExecuteScalar();
No need for a reader when you are only getting one value.
Sep 13, 2014 10:47 PM|wmec|LINK
You can recall the following event
private static void CreateCommand(string queryString,
using (SqlConnection connection = new SqlConnection(
SqlCommand command = new SqlCommand(queryString, connection);
SqlDataReader reader = command.ExecuteReader();
to pass different queries.