MARS in SQL Server 2000

Question

Hello Friends,

I am working in ASP.Net MVC 1.0 and SQL Server 2000 using Entity Framework. My faulty controller code is given below:

            int checkUser = id ?? 0;
            string userNameFromNew, userNameToNew;

            if (checkUser == 1)
            {
                userNameFromNew = "U" + Request.Form["usernameFrom"];
                userNameToNew = "U" + Request.Form["usernameTo"];
            }
            else
            {
                userNameFromNew = "C" + Request.Form["usernameFrom"];
                userNameToNew = "C" + Request.Form["usernameTo"];
            }

            var rMatrix = from Datas in repository.GetTotalRightData() 
                          where Datas.UserName == userNameFromNew.Trim()
                          select Datas;

            Right_Matrix RM = new Right_Matrix();


            foreach(var Data in rMatrix)
            {
                RM.Column_Id = Data.Column_Id;
                RM.ProMonSys_Right = Data.ProMonSys_Right;
                RM.UserName = userNameToNew;
                UpdateModel(RM);
                this.repository.AddRightTransfer(RM);
            }

            return RedirectToAction("RightTransfer");

My faulty model code is given below:

            public void AddRightTransfer(Right_Matrix RM)
            {
                context.AddObject("Right_Matrix", RM);
                context.SaveChanges();
            }

My code shows error once it is in the model code stating the DataReader is already open and I need to close it first.

Please suggest a workaround.

Answer 1

Try moving the AddRightTransfer loop out of the LINQ foreach, and into a separate one. I think LINQ is executing the first add before the database result set is closed. By moving the call to AddRightTransfer into a second foreach, you should avoid the problem, if that's what is happening.

Here's an example:

List<Right_Matrix> matrixes = new List<Right_Matrix>();

foreach (var Data in rMatrix)
{
    Right_Matrix rm = new Right_Matrix();
    rm.Column_Id = Data.Column_Id;
    rm.ProMonSys_Right = Data.ProMonSys_Right;
    rm.UserName = userNameToNew;
    UpdateModel(rm);
    matrixes.Add(rm);
}

foreach (var rm in matrixes)
{
    this.repository.AddRightTransfer(rm);
}

Answer 2

The problem is that you are modifying the database while still iterating over it. Either finish the query before modifying, like this:

foreach(var Data in rMatrix.ToArray())

Or don't modify the database in your loop, like this:

        foreach(var Data in rMatrix)   
        {   
            RM.Column_Id = Data.Column_Id;   
            RM.ProMonSys_Right = Data.ProMonSys_Right;   
            RM.UserName = userNameToNew;   
            UpdateModel(RM);   
            context.AddObject("Right_Matrix", RM);  
        }
        context.SaveChanges();

Obviously, those two context. calls would have to be made into methods on your repository, but you get the picture.