C# Windows Forms App: Adding An Element To The BindingSource And Save These Changes To The DB [closed]

Question

I have build a C# Windows Forms app that used Entity Framework(DB first model). For the data I am using a .mdf database file.

The data view of dbo.students:

GUI:

The Form1 class:

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    StudentsEntities db = new StudentsEntities();

    private void Form1_Load(object sender, EventArgs e)
    {
        bsStudents.DataSource = db.students.ToList();
        dgvStudents.DataSource = bsStudents;

        lstResults.DisplayMember = "FullName";

        DisplayStudent();
    }

    // Displaying student info(name & credits) in lables, & refreshes DataGridView
    private void DisplayStudent()
    {
        if (bsStudents.Current != null)
        {
            lblName.Text = ((student)bsStudents.Current).FullName;
            lblCurrent.Text = ((student)bsStudents.Current).CurrentCredits.ToString();
            lblTotal.Text = ((student)bsStudents.Current).TotalCredits.ToString();

            dgvStudents.Refresh();
        }
    }

    private void bsStudents_CurrentChanged(object sender, EventArgs e)
    {
        DisplayStudent();
    }

    // add 1 credit to selected student's current credits
    private void btnAdd1_Click(object sender, EventArgs e)
    {
        ((student)bsStudents.Current).AddToCurrentCredits(1);
        DisplayStudent();
        lblForm1UserPrompt.Text = "1 credit has been added to the current credits of " + ((student)bsStudents.Current).FullName;
    }

    // add 3 credits to selected student's current credits
    private void btnAdd3_Click(object sender, EventArgs e)
    {
        ((student)bsStudents.Current).AddToCurrentCredits(3);
        DisplayStudent();
        lblForm1UserPrompt.Text = "3 credits has been added to the current credits of " + ((student)bsStudents.Current).FullName;
    }

    // place current credits into total credits of selected student
    private void btnAddCurrentToTotal_Click(object sender, EventArgs e)
    {
        ((student)bsStudents.Current).AddCurrentToTotal();
        DisplayStudent();
        lblForm1UserPrompt.Text = ((student)bsStudents.Current).FullName + "'s" + " current credits were added to their total credits.";
    }

    // change current credits of selected student to user input
    private void btnUpdateCurrentCredits_Click(object sender, EventArgs e)
    {
        string currentCredits = txtUpdateCurrentCredits.Text;
        int currentCreditsNumeric = 0;

        // validate user input
        if (int.TryParse(currentCredits, out currentCreditsNumeric))
        {
            ((student)bsStudents.Current).UpdateCurrentCredits(currentCreditsNumeric);
            DisplayStudent();
            lblForm1UserPrompt.Text = ((student)bsStudents.Current).FullName + "'s" + " current crdits have been set to " + currentCredits;
            txtUpdateCurrentCredits.Clear();
        }
        else
        {
            lblForm1UserPrompt.Text = "Please enter numeric data for current credits.";
            txtUpdateCurrentCredits.Clear();
        } 
    }

    // change total credits of selected student to user input
    private void btnUpdateTotalCredits_Click(object sender, EventArgs e)
    {
        string totalCredits = txtUpdateTotalCredits.Text;
        int totalCreditsNumeric = 0;

        // validate user input
        if (int.TryParse(totalCredits, out totalCreditsNumeric))
        {
            ((student)bsStudents.Current).UpdateTotalCredits(totalCreditsNumeric);
            DisplayStudent();
            lblForm1UserPrompt.Text = ((student)bsStudents.Current).FullName + "'s" + " total crdits have been set to " + totalCredits;
            txtUpdateTotalCredits.Clear();
        }
        else
        {
            lblForm1UserPrompt.Text = "Please enter numeric data for total credits.";
            txtUpdateCurrentCredits.Clear();
        }
    }

    // add 1 credit to all student's current credits
    private void btnAdd1All_Click(object sender, EventArgs e)
    {
        foreach (student s in bsStudents)
        {
            s.AddToCurrentCredits(1);
        }
        DisplayStudent();
        lblForm1UserPrompt.Text = "1 credit has been added to the current credits of all students";
    }

    // add 3 credits to all student's current credits
    private void btnAdd3All_Click(object sender, EventArgs e)
    {
        foreach (student s in bsStudents)
        {
            s.AddToCurrentCredits(3);
        }
        DisplayStudent();
        lblForm1UserPrompt.Text = "3 credits have been added to the current credits of all students";
    }

    // place current credits into total credits of all students
    private void btnAddCurrentToTotalAll_Click(object sender, EventArgs e)
    {
        foreach (student s in bsStudents)
        {
            s.AddCurrentToTotal();
        }
        DisplayStudent();
        lblForm1UserPrompt.Text = "The current credits of all students have been added to their total credits.";
    }

    // set current credits to 0 for all students
    private void btnSetCurrent0All_Click(object sender, EventArgs e)
    {
        foreach (student s in bsStudents)
        {
            s.CurrentCredits = 0;
        }
        DisplayStudent();
        lblForm1UserPrompt.Text = "The current credits of all students have been set to 0.";
    }

    // set total credits to 0 for all students
    private void btnSetTotal0All_Click(object sender, EventArgs e)
    {
        foreach (student s in bsStudents)
        {
            s.TotalCredits = 0;
        }
        DisplayStudent();
        lblForm1UserPrompt.Text = "The total credits of all students have been set to 0.";
    }

    // navigate to first element
    private void btnFirst_Click(object sender, EventArgs e)
    {
        bsStudents.MoveFirst();
        DisplayStudent();
    }

    // navigate to preceding element
    private void btnPrevious_Click(object sender, EventArgs e)
    {
        bsStudents.MovePrevious();
        DisplayStudent();
    }

    // navigate to succeeding element
    private void btnNext_Click(object sender, EventArgs e)
    {
        bsStudents.MoveNext();
        DisplayStudent();
    }

    // navigate to last element
    private void btnLast_Click(object sender, EventArgs e)
    {
        bsStudents.MoveLast();
        DisplayStudent();
    }

    // TODO: Maybe improve search
    private void txtSearchLastName_TextChanged(object sender, EventArgs e)
    {

    }

    // searches BindingSource for names that start with user input
    private void btnSearch_Click(object sender, EventArgs e)
    {
        List<student> students = new List<student>();

        foreach (student s in bsStudents)
        {
            if (s.StudentLastName.ToLower().StartsWith(txtSearchLastName.Text.ToLower()))
            {
                students.Add(s);
            }
        }

        if (students.Any())
        {
            bsStudents.Position = bsStudents.IndexOf(students.First());
            lstResults.DataSource = students;
            DisplayStudent();
        }
        else
        {
            MessageBox.Show("Not found");
        }
    }

    // navigate to name selected in ListBox
    private void lstResults_SelectedIndexChanged(object sender, EventArgs e)
    {
        List<student> students = new List<student>();

        foreach (student s in bsStudents)
        {
            if (s.FullName == lstResults.Text)
            {
                students.Add(s);
            }
        }

        if (students.Any())
        {
            bsStudents.Position = bsStudents.IndexOf(students.First());
        }
        else
        {
            MessageBox.Show("Not found");
        }
    }

    // create new student & add student to BindingSource
    private void btnAddStudent_Click(object sender, EventArgs e)
    {
        Form2 frmNew = new Form2();
        frmNew.ShowDialog();

        if (frmNew.stu != null)
        {
            bsStudents.Insert(0, frmNew.stu);
        }
    }

    // remove student from binding source
    private void btnRemove_Click(object sender, EventArgs e)
    {
        string fullName = ((student)bsStudents.Current).FullName;
        bsStudents.RemoveCurrent();
        lblForm1UserPrompt.Text = fullName + " has been removed.";
    }

    // save BindingSource to DB
    private void btnSaveToDB_Click(object sender, EventArgs e)
    {
        db.SaveChanges();
    }

    // exit app
    private void btnExitForm1_Click(object sender, EventArgs e)
    {
        this.Close();
    }
}

The Form2 class:

public partial class Form2 : Form
{
    public student stu;


    public Form2()
    {
        InitializeComponent();
        lblForm2UserPrompt.Text = "Please enter a first name, last name, current and total credits.";
    }

    private void btnSave_Click(object sender, EventArgs e)
    {
        string firstName = txtFirst.Text;
        string lastName = txtLast.Text;
        string currentCredits = txtCurrentCredits.Text;
        int currentCreditsNumeric = 0;
        string totalCredits = txtTotalCredits.Text;
        int totalCreditsNumeric = 0;

        //validate user input
        if (!string.IsNullOrEmpty(firstName) && !string.IsNullOrEmpty(lastName) && int.TryParse(currentCredits, out currentCreditsNumeric) && int.TryParse(totalCredits, out totalCreditsNumeric))
        {
            // instantiate & initialize student
            stu = new student();
            stu.StudentFirstName = char.ToUpper(firstName[0]) + firstName.Substring(1);
            stu.StudentLastName = char.ToUpper(lastName[0]) + lastName.Substring(1);
            stu.CurrentCredits = currentCreditsNumeric;
            stu.TotalCredits = totalCreditsNumeric;

            lblForm2UserPrompt.Text = "Student has been saved.";
        }
        else
        {
            //if user did not entered first or last name
            if (string.IsNullOrEmpty(firstName) || string.IsNullOrEmpty(lastName))
            {
                lblForm2UserPrompt.Text = "Please enter a first and last name.";
            }
            //if user did not entered a number for current credit
            else if (!int.TryParse(currentCredits, out currentCreditsNumeric))
            {
                lblForm2UserPrompt.Text = "Please enter numeric data for current credits.";
                txtCurrentCredits.Clear();
            }
            //if user did not entered a number for total credit
            else
            {
                lblForm2UserPrompt.Text = "Please enter numeric data for total credits.";
                txtTotalCredits.Clear();
            }
        }
    }

    private void btnExit_Click(object sender, EventArgs e)
    {
        this.Close();
    }
}

The student class:

 public partial class student
{
    public int Id { get; set; }
    public string StudentLastName { get; set; }
    public string StudentFirstName { get; set; }
    public int CurrentCredits { get; set; }
    public int TotalCredits { get; set; }

    public string FullName
    {
        get
        {
            return $"{StudentFirstName} {StudentLastName}";
            //return StudentFirstName + " " + StudentLastName;
        }
    }

    public void AddToCurrentCredits(int newCurrentCredits)
    {
        CurrentCredits += newCurrentCredits;
    }

    public void AddCurrentToTotal()
    {
        TotalCredits += CurrentCredits;
        CurrentCredits = 0;
    }

    public void UpdateCurrentCredits(int newCurrentCredits)
    {
        CurrentCredits = newCurrentCredits;
    }

    public void UpdateTotalCredits(int newTotalCredits)
    {
        TotalCredits = newTotalCredits;
    }

}

The StudentsEntities class:

public partial class StudentsEntities : DbContext
{
    public StudentsEntities()
        : base("name=StudentsEntities")
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        throw new UnintentionalCodeFirstException();
    }

    public virtual DbSet<student> students { get; set; }
}

The app runs but not as expected...

These are the bits I am having trouble with:

1. I seem to be able to create a new student, & to be able to add them to the BindingSource. I know this because the show up in the DataGridView. But, their id value is always 0. And, I need for the id value to be unique.
2. When I save the changes made in the BindingSource to the underlying db file the new students are not saved. If I edit the credits of a student, these edits will save. But, the newly created students will not.

The btnAddStudent_Click method:

 private void btnAddStudent_Click(object sender, EventArgs e)
    {
        Form2 frmNew = new Form2();
        frmNew.ShowDialog();

        if (frmNew.stu != null)
        {
            bsStudents.Insert(0, frmNew.stu);
        }
    }

The student object is then created & provided with values in Form2.

After having added 2 new students & set all current credits to 0 in the BindingSource:

The data view of dbo.students after saving the changes made to the BindingSource:

As you can see the changes made to the students credits changed. But, the number of students did not.

What I am looking for:

1. I want for the new student objects to save to the BindingSource with unique ids.
2. When I save the changes made to the BindingSource I would like to see new students(if created) in the data view of the dbo.

What do you think?

Thanks in advance for the input.

Answer 1

1. I seem to be able to create a new student, & to be able to add them to the BindingSource. I know this because the show up in the DataGridView. But, their id value is always 0. And, I need for the id value to be unique.

2. When I save the changes made in the BindingSource to the underlying db file the new students are not saved. If I edit the credits of a student, these edits will save. But, the newly created students will not.

Both concerns are related to the table schema. Your students columns don't allowed nulls, and in your current work, you're passing null id, which is not allowed. Those, the changes were not committed. To solve it, you need to pass a valid Id when creating a new student. you can do this :

public static int NewId()
{
    using (var entity = new StudentsEntities())
    {
        return entity.students.Max(x => x.Id) + 1;
    }
}

and then do this :

// instantiate & initialize student
stu = new student
{
    Id = student.NewId(),
    StudentFirstName = char.ToUpper(firstName[0]) + firstName.Substring(1),
    StudentLastName = char.ToUpper(lastName[0]) + lastName.Substring(1),
    CurrentCredits = currentCreditsNumeric,
    TotalCredits = totalCreditsNumeric
};

Update

These are the results:

There were originally 5 students. IDs 1-5. I added 2 new students. The first one received an ID of 6. But, the second one did too.These students were did save to the dbo after a save was executed.

in short, you've executed NewId() twice before any updates. So, there were 5 records, and you've added 2 new rows (on the datagrid not the database). Those two records have not been committed to the database yet, so executing the NewId() would get the max current id from the database (which is 6). No matter how many times you execute it, as long as you've not commit any new changes, it'll always return the same value. So, either commit one row each time, and get the next max id from the database, or get the max id, then do your own counting, and commit the changes once.

For instance, your current work should store students in a collection, and whenever the user clicks save changes to the database, the application will insert the new records from that collection.

// this would be global 
private List<student> studentList = new List<student>();

public void AddStudent(int id, string firstName, string lastName, int currentCredits, int totalCredits)
{
    var _student = new student 
    {
        Id = id,
        StudentFirstName = char.ToUpper(firstName[0]) + firstName.Substring(1),
        StudentLastName = char.ToUpper(lastName[0]) + lastName.Substring(1),
        CurrentCredits = currentCredits,
        TotalCredits = totalCredits
    };
    
    studentList.Add(_student);
}


public void CommitNewStudents()
{
    // get the new students that have not been committed yet.
    var numberOfnewStudents = studentList.Count;
    
    if(numberOfnewStudents > 0)
    {
        //get the current max Id from the database.
        var maxDbId = student.NewId();
        
        for(int x =0; x < numberOfnewStudents; x++)
        {    
            // since the id is max + 1, then we can get the count of the new students, and add 1 to the maxId. 
            studentList[x].Id = maxDbId + x;
        }
        
        //now, add them to the entity and save the the changes. 
        db.students.AddRange(studentList);
        
        db.SaveChanges();    
    }
}

this should cover adding new students, however, you'll need to cover the update and delete logic in your app.

Another Update

I've checked some of your code (from the uploded source), and I noticed that you are treating all actions as INSERT. For instance, when you add new student, this is an insert action, but when you change a value of current student, this is an update action. You didn't specify anything that separates these actions.

Since you are using Entity Framework, you could use AddOrUpdate() extension which will update current records, and add new ones.

you could do this in your code :

// save BindingSource to DB
private void btnSaveToDB_Click(object sender, EventArgs e)
{
    if (bsStudents.Count != 0)
    {
        // Do UPSERT (UPDATE existing records & add new records)

        using (var context = new StudentsEntities())
        {
            var list = (IList<student>) bsStudents.DataSource;

            var studentsList = new student[list.Count];

            for (int x = 0; x < list.Count; x++)
            {
                var id = list[x].Id;


                var stud = new student
                {
                    Id = id < 1 ? x + 1 : id, //assuming that the datasource has loaded all records from DB.
                    StudentLastName = list[x].StudentLastName,
                    StudentFirstName = list[x].StudentFirstName,
                    CurrentCredits = list[x].CurrentCredits,
                    TotalCredits = list[x].TotalCredits
                };

                studentsList[x] = stud;
            }

            context.students.AddOrUpdate(studentsList);

            context.SaveChanges();

            //Rebind data
            dgvStudents.DataSource = null;

            bsStudents.DataSource = context.students.ToList();
            dgvStudents.DataSource = bsStudents;
        }



    }

}

Another thought that I saw, you are using BindingSource, it's not required in your case, you can load the db.students.ToList() directly to the datagridview datasource, and continue your work.

Another note, you modified the Entity Framework auto-generated student model class. It's okay, but keep in mind, this class will be regenarted whenever you update the Entity Framework Entities (say, you added new entities using the designer). So, I would suggest you move all modified work from the this class, to another file (you can create another partial class and move your work to it). Just try to avoid modifying on EF auto-generated classes, if you don't want to lose your work ;) ..