必须先关闭与此命令关联的Datareader

b4wnujal  于 2022-10-24  发布在  Angular
关注(0)|答案(2)|浏览(258)

我正在使用带有angularjs的MVC-5,我有一个DBFunction.cs类,其中的方法如下

public SqlDataReader ExecuteSP_Reader(string ProcedureName, SqlCommand MyCommand)
        {
            if (con.State == ConnectionState.Closed)
                con.Open();
            MyCommand.CommandType = CommandType.StoredProcedure;
            MyCommand.CommandText = ProcedureName;
            //MyCommand.Connection = (SqlConnection)HttpContext.Current.Application["con"];
            MyCommand.Connection = con;

            SqlDataReader _Reader = MyCommand.ExecuteReader();

            //con.Close();
            return _Reader;
        }

和另一个名为TASK的类
我正在从类的方法中获取数据库中的数据

public List<object>getdatabyuser(string UserAutoId=null)
        {
            cmd = new SqlCommand();
            List<object> getdatauser = new List<object>();
            if(!string.IsNullOrEmpty(UserAutoId))
                cmd.Parameters.AddWithValue("@userautoid", UserAutoId);
            SqlDataReader dr;
            dr = dbf.ExecuteSP_Reader("TMS_GETTASKBYUSERNAME", cmd);
            while(dr.Read())
            {
                getdatauser.Add(new
                {
                    TaskName=dr["TaskName"].ToString(),
                    ParentAutoID=dr["ParentAutoID"].ToString(),
                    UserAutoId = dr["UserAutoId"].ToString(),
                    AssignBy=dr["AssignBy"].ToString(),
                    AssignTo=dr["AssignTo"].ToString()
                });
            }
            dr.Close();
            return getdatauser;
        }

这里面还有另一层,我使用了一个名为dashboard.asmx的Web服务

[WebMethod]
        [ScriptMethod(UseHttpGet = true)]
        public void gettaskbyuser(string UserAutoId)
        {
            var json = "";
            json = js.Serialize(objtask.getdatabyuser(UserAutoId));
            Context.Response.Write(json);
        }

在这里,我将数据转换为json格式并将数据发送到我的angularjs控制器
这是我的控制器

$http.get('/WebServices/dashboard.asmx/gettaskbyuser', {
                params: {
                    UserAutoId: $scope.showparam.UserAutoId
                }
            }).then(function (response) {
                $scope.getdatainmodal = response.data;
            })

点击按钮即可显示数据,
我有一张table

Azhar   4
Das 1
Minesh  1

<td><a href="#" ng-click="showmodal(x)" data-toggle="modal" data-target="#myModal">{{ x.TaskCount }}</a></td>

在我的表格中有一个a标签,当用户点击它时,就会弹出带有数据的模式

<table class="table table-bordered table-responsive">
                            <tr class="bg-primary">
                                <td><center><span class="glyphicon glyphicon-edit"></span></center></td>
                                <td>TaskName</td>
                                <td>User Name</td>
                                <td>AssignBy</td>
                                <td>AssignTo</td>
                                <td ng-hide="true">Parent Id</td>
                            </tr>
                            <tr ng-repeat="x in getdatainmodal">
                                <td><center><a href="#" class="btn btn-success">Edit</a></center></td>
                                <td>{{x.TaskName}}</td>
                                <td>{{x.UserAutoId}}</td>
                                <td>{{x.AssignBy}}</td>
                                <td>{{x.AssignTo}}</td>
                                <td ng-hide="true">{{x.ParentAutoID}}</td>
                            </tr>
                        </table>

第一次可以正常工作,但当用户第二次单击它时,对于其他用户的记录,它显示了一个异常
可能未处理的拒绝:{“Data”:“System.InvalidOperationException:已经有一个与此命令关联的打开的DataReader,必须首先关闭它。
这段代码出了什么问题?

bxfogqkk

bxfogqkk1#

我曾经遇到过类似的情况,我想使用ASMX(ASP.NET Web服务)将数据从后端发送到前端,但多次失败,因为前端无法达到我打算发送的文件格式。即使在后端添加了断点,也没有用。因此,我没有将后端数据转换为HTTP请求,而是将其转换为JSON。这是我的代码,我想它可能会对你有帮助。
这是我在索引顶部的视图,您可以看到使用Anularjs创建的服务,它将json加载到我们的模型中,当我们使用Angular 模型和控制器时,我们将看到这一点
这是位于本答案末尾的ASP.NET MVC控制器的视图,它应该位于视图/Shared/_Layout中

@model string

@{
    ViewBag.Title = "employee_data";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<h2>employee_data</h2>

@section javascriptInHead{
    <script src="~/Scripts/SinglePageApplication.js"></script>
    <script type="text/javascript">
        myModel.factory('bootstrapeddataemployee', function () {
            return {
                employee:@Html.Raw(Model)
        };
        }
            );
    </script>
}

<div ng-controller="employeeController">
    @*{{employees}}*@
    <table>
        <thead>
            <tr>
                <th>EmployeeId</th>
                <th>
                    EmployeeName
                </th>
                <th>
                    EmployeeGender
                </th>
                <th>
                    EmployeeSalary
                </th>
            </tr>
        </thead>
        <tbody>
            <tr ng-repeat="employee in employees">
                <td>
                    {{employee.id}}
                </td>
                <td>
                    {{employee.name}}
                </td>
                <td>
                    {{employee.gender}}
                </td>
                <td>
                    {{employee.salary}}
                </td>
            </tr>
        </tbody>
    </table>
    <div class="buttonstyler">
    <input type="button" value="GoToEmployees" class="custom-btn btn-1"  onclick="location.href='@Url.Action("Index", "Home")'" />
</div>
   </div>

以下是用于此操作的angularjs模型和控制器

var myModel = angular.module("myModel", []);
myModel.controller("employeeController", function ($scope, bootstrapeddataemployee) {
    $scope.employees = bootstrapeddataemployee.employee;
}
);

不要忘记在配置文件中添加连接字符串。
下面是我的asmx Web服务,我用它将服务转换为一个字符串,该字符串位于MVC的Models部分,因此为了访问这个int控制器,我们需要在控制器的顶部引用这个模型的名称空间,如下所示

using CoursesMVC.Models;

/*this is referencing and public down can be added just above the controller for more details look to the controller seciton of the answer.*/

  public CoursesMVC.Models.courses data = new CoursesMVC.Models.courses();/
using CoursesMVC.Controllers;
using System.Web.Script.Serialization;
using System.Configuration;
using Newtonsoft.Json;
using Newtonsoft.Json.Serialization;

namespace CoursesMVC.Models
{
    /// <summary>
    /// Summary description for courses
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
     [System.Web.Script.Services.ScriptService]
    public class courses : System.Web.Services.WebService
    {

        [WebMethod]
                public string GetAllEmployees()
        {
            List<Employee> listEmployees = new List<Employee>();

            string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(cs))
            {
                SqlCommand cmd = new SqlCommand("Select * from tblEmployees", con);
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Employee employee = new Employee();
                    employee.id = Convert.ToInt32(rdr["Id"]);
                    employee.name = rdr["Name"].ToString();
                    employee.gender = rdr["Gender"].ToString();
                    employee.salary = Convert.ToInt32(rdr["Salary"]);
                    listEmployees.Add(employee);
                }
            }

            JavaScriptSerializer js = new JavaScriptSerializer();
            var settings = new JsonSerializerSettings { ContractResolver = new CamelCasePropertyNamesContractResolver() };
            //return Context.Response.Write(js.Serialize(listEmployees));
            return JsonConvert.SerializeObject(listEmployees, Formatting.None, settings);

        }

    }
}

最后,我使用的MVC控制器使用view(“view name”,“”,返回STRING_DATA的函数)将转换为STING的json注入到视图中;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Newtonsoft.Json;
using Newtonsoft.Json.Serialization;
using CoursesMVC.Models;

namespace CoursesMVC.Controllers
{

    public class HomeController : Controller
    {

     public CoursesMVC.Models.courses data = new CoursesMVC.Models.courses();

      public ActionResult employee_data()
            {
            string emp_data = data.GetAllEmployees();
            return View("employee_data","",emp_data);
            }
    }
}

希望你能得到你想要的答案。如果这让我感到困惑,我会更容易用文字,而不是写在这里。

zaqlnxep

zaqlnxep2#

1.请始终创建新连接。.NET正在进行连接池。
1.数据读取后关闭Connection、Reader和Dispose命令。
1.为什么不使用实体框架来处理SQL注入之类的安全问题?

相关问题