asp.net mvc 5 reader.read() only gets first and last rows

Question

I have select2 list with employees which admin will add to project.

I select multiple employees and sending json with employee ID's to controller where trying to get employee by employee, from company database emp list by stored procedure [Employee] to web emp db.

I face such problem that reader reads only first and last rows and skips all employees in midle

    [HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult EmpAdd()
    {
        var project_id = Convert.ToInt32(Request["project"]);
        var company = Request["company"];
        var emp = Request["emp"];
        var ids = emp.Split(',');
        var project = db.apsk_project.Where(x => x.id == project_id).FirstOrDefault();

        cmd.CommandText = "Employee";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = sqlConnection1;

        foreach(var i in ids) {
            var it = Convert.ToInt32(i);
            var kortele = db.apsk_workers.Where(x => x.id == it).FirstOrDefault();
            if(kortele == null) {
                sqlConnection1.Open();

                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("company", company);
                cmd.Parameters.AddWithValue("nr", it);
                reader = cmd.ExecuteReader();

                if(reader.HasRows) {
                    while(reader.Read()) {

                        apsk_workers apsk = new apsk_workers();

                        var v = reader.GetString(1);
                        var p = reader.GetString(2);
                        var d = reader.GetDateTime(3);

                        apsk.kof_id = 0;
                        apsk.algos_tipas = "";
                        apsk.vardas = v;
                        apsk.pavarde = p;
                        apsk.ar_dirba = d;
                        apsk.company = company;
                        apsk.manager = User.Identity.Name;
                        apsk.id = it;

                        db.apsk_workers.Add(apsk);
                        db.SaveChanges();
                    }
                }
                sqlConnection1.Close();

                apsk_assigned _Assigned = new apsk_assigned();

                _Assigned.project_id = project_id;
                _Assigned.project = project.project;
                _Assigned.worker_id = it;

                db.apsk_assigned.Add(_Assigned);
                db.SaveChanges();
            } else {
                var ar_projekte = db.apsk_assigned.Where(x => x.project_id == project_id && x.worker_id == it).FirstOrDefault();
                if(ar_projekte == null) {
                    apsk_assigned _Assigned = new apsk_assigned();

                    _Assigned.project_id = project_id;
                    _Assigned.project = project.project;
                    _Assigned.worker_id = it;

                    db.apsk_assigned.Add(_Assigned);
                    db.SaveChanges();
                }
            }
        }

Assigning person to project works fine.

Answer 1

I'd make several changes:

• You are mixing EF with ado.net So please remove all connection opening and closings. Also lines with Parameters etc.
• Since id's is a string, you can convert it to an List of ints: https://stackoverflow.com/a/9301290/169714
• With Linq you can assign the employees who are already known to the project
• With Linq you can foreach add the unknown employees and savechanges after adding them alll.

Let us know if you need help with specific Linq queries. For instance: instead of db.apsk_project.Where(x => x.id == project_id).FirstOrDefault(); you could use db.apsk_project.FirstOrDefault(x => x.id == project_id);

So do not do a foreach and then a first or default to change per row, but think in collections so you can eliminate the loop and increase performance. Hope this helps. If not, please comment so I can try to explain more.