已经有一个与此命令关联的打开的datareader必须先关闭。 [英] There is already an open datareader associated with this command which must be closed first.

问题描述

我已经创建了锦标赛，并且在其他地方的每个锦标赛中都有1张图片并将其存储在数据库中。现在，当人们点击删除时，它应该在一个连接中获取他们的锦标赛细节和相应的图像。



但它显示错误已经有一个与此命令关联的开放DataReader必须先关闭。

I have created tournament and have got 1 image for each of the tournament somewhere else and stored them in the db. Now, when people click on "Delete", it should get their tournament details and respective image deleted in a single connection.

But it's showing error "There is already an open DataReader associated with this Command which must be closed first."

protected void LVTournament_ItemDeleting(object sender, ListViewDeleteEventArgs e)
        {
            if (Session["TournId"] != null)
            {
                int ClubId = Convert.ToInt32(Request.Cookies["ClubDetails"]["ClubId"]);
                int TournId = Convert.ToInt32(Session["TournId"]);
                SqlConnection con = new SqlConnection(constr);
                ImageDeletion(TournId);
                string querydelTour = "DELETE FROM Tournaments WHERE TournamentId = @TournId";
                string querydelimg = "DELETE FROM TournamentImages WHERE TournamentId = @TournId";
                SqlCommand cmd1 = new SqlCommand(querydelTour, con);
                SqlCommand cmd2 = new SqlCommand(querydelimg, con);
                cmd1.Parameters.AddWithValue("@TournId", TournId);
                cmd2.Parameters.AddWithValue("@TournId", TournId);
                con.Open();
                cmd1.ExecuteNonQuery();
                cmd2.ExecuteNonQuery();
                con.Close();
                UpdateTourCount(ClubId);
                ClientScript.RegisterStartupScript(Page.GetType(), "Success", "<script language='javascript'>alert('Tournament Deleted...')</script>");
                Response.Redirect("TournamentView.aspx");
            }
            else
            {
                ScriptManager.RegisterStartupScript(this, GetType(), "Popup", "SessionExpires();", true);
                return;
            }
        }

我的尝试：



我想，这里有一个问题。

What I have tried:

I guess, there is a problem over here.

con.Open();
                cmd1.ExecuteNonQuery();
                cmd2.ExecuteNonQuery();
                con.Close();

如何摆脱这个？

How to get rid of this?

推荐答案

SqlConnection con = new SqlConnection(constr);
ImageDeletion(TournId);
string querydelTour = "DELETE FROM Tournaments WHERE TournamentId = @TournId";
string querydelimg = "DELETE FROM TournamentImages WHERE TournamentId = @TournId";
// these two commands use the same connection so you can only process
// one of the commands at a time, but you're doing both.
SqlCommand cmd1 = new SqlCommand(querydelTour, con);
SqlCommand cmd2 = new SqlCommand(querydelimg, con);

SqlConnection con1 = new SqlConnection(constr);
SqlConnection con2 = new SqlConnection(constr);
ImageDeletion(TournId);
string querydelTour = "DELETE FROM Tournaments WHERE TournamentId = @TournId";
string querydelimg = "DELETE FROM TournamentImages WHERE TournamentId = @TournId";
// these two commands use the same connection so you can only process
// one of the commands at a time, but you're doing both.
SqlCommand cmd1 = new SqlCommand(querydelTour, con1);
SqlCommand cmd2 = new SqlCommand(querydelimg, con2);

你还需要con1.Open和con2.Open等。



然而更好的设计是制作像DeleteTournamentImages和DeleteTournament这样的功能在这些函数中，你只需要执行与该任务相关的SQL工作，然后你就可以调用这两个函数。

You'll also need con1.Open and con2.Open etc.

However a better design would be to make a function like "DeleteTournamentImages" and "DeleteTournament" and inside those functions you'd do the SQL work related to just that task, then you'd call both of those functions.

发生的事情是你拒绝SQL连接，你没有关闭它和SqlCommand，SqlDataReader等你完成它们时没有处理它们的对象。

所以你的代码中的某个地方创建了一个DataReader，它仍然存在，即使它是一个方法创建'本地'，因为它在堆上，它将保留，直到垃圾收集器到处理它。



Don'回收你的连接，每次都换一个新的连接。总是处置连接，命令和所有其他SQL对象，或者这种问题不仅会变得更糟，它也会在网站繁忙时开始崩溃：这些东西是稀缺的资源，你不能只是继续创建它们！

What has happened is that you are refusing an SQL connection, which you don't close and SqlCommand, SqlDataReader, etc. objects that you don't Dispose when you are finished with them.
So somewhere in your code you create a DataReader, and it still exists, even if it was created 'local' to a method because it is on the heap where it will remain until the Garbage Collector gets around to Disposing of it.

Don't recycle your connection, make a new one each time. Always Dispose connection, command, and all other SQL objects, or this kind of problem is not only going to get worse, it will also start to crash your website as it gets busy: these things are a scarce resources and you can't just keep creating them!

这篇关于已经有一个与此命令关联的打开的datareader必须先关闭。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！