0

I am using mvc-5 with angularjs i have a class DBFunction.cs in which i have a method like

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;
        }

and another class called task

i am fetching the data from the database from the method of the class

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;
        }

and there is another layer in this, i used a webservice called dashboard.asmx

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

here i am converting the data into json format an sending the data to my controller of angularjs

and here is my controller

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

the data will be shown on button click,

i have a table

Azhar   4
Das 1
Minesh  1

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

there is an a tag in my table and when user click that, modal pops up with the data

<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>

which works fine for the first time, but when a user clicks it again for the second time, for other user's record, it is showing an exceptions

Possibly unhandled rejection: {"data":"System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

what is wrong in this code?

2 Answers2

1

I have faced a similar situation where I wanted to send the data from the backend to the front using asmx(Asp.net web services) but failed multiple times backed the front end was not able to reach the file format that I intended to send. even when the breaking point was added in the back end it was not useful. so instead of converting the backend data into an HTTP request, I made it into a JSON. Here is my Code I guess it might help you.

Here is my view in index on top you can see a Service created using Anularjs which is loading the json into our model which we will see when we use the angular Model and Controller

This is the view of the Asp.net MVC controller which is there in the end of this answer which should be there in the views/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>

Here is my angularjs Model and Controller used for this

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

Don't forget to add the connection string in the configuration file.

here is my asmx webservice which i used to make the service into a string which is there in the models section of the MVC so inorder to access this int he Controller we need to reference the name space of this modles in the top of the controller like

  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);

        }
        

    }
}

and finally my MVCcontroller which I used this does the job injecting the json converted to sting into the view using the view("Viewname","",functionthatreturnstring_data);

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);
            }
    }
}

hope you get the answer your looking for. if this is confusing DM me i would be easier with words than to write it down here.

Charles
  • 11
  • 1
0
  1. Please create always a new connection. .NET is doing connection pooling.
  2. close Connection, Reader and Dispose the Command after data reading.
  3. Why not use Entity Framework to handle security issues like sql injection and stuff?
Shmukko
  • 572
  • 3
  • 11