Question Update datagridview cell value changes to database in 3-tier architecture

[andrewmanuja]

Hi All,
I am currently working on getting

Please find the respective Data Access Layer code below;

 public void UpdateUser(PersonDTO bPerson)
        {
            using (SqlConnection conn = new SqlConnection(_connStr))

            using (var dCmd = new SqlCommand(@"UPDATE Person SET FirstName = @FirstName, LastName = @LastName, Age = @Age WHERE PersonID = @PersonID", conn))

            using (var adapter = new SqlDataAdapter { UpdateCommand = dCmd })
            {
                conn.Open();
                dCmd.Parameters.AddWithValue("personID", bPerson.PersonID);
                dCmd.Parameters.AddWithValue("firstName", bPerson.FirstName);
                dCmd.Parameters.AddWithValue("lastName", bPerson.LastName);
                dCmd.Parameters.AddWithValue("age", bPerson.Age);

                try
                {
                    adapter.Update(table);
                    dCmd.Dispose();
                }

                catch (Exception ex)
                {
                    throw ex;
                }
            }

            foreach (DataRow row in table.Rows)
            {
                if (row["Select Item"] as bool? == true)
                {
                    table.AcceptChanges();
                    row.SetAdded();                  
                }
            }
        }

My Business Logic Layer code looks like below;

 public void UpdateUser(PersonDTO bPerson)
        {        

            try
            {
                personDAL.UpdateUser(bPerson);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

I am bit confused how to write the code for the btnUpdate (Update button) to update the datagridview data to the database table. Is it just to call the Business Access Layer "UpdateUser" method?

Just a small clarification, Since I have bind the datatable, "table" as the dataGridView's datasource, I assume whatever the changes I perform to the datagridview cell data automatically updates the datatable, "table'.

Appreciate your feedback.

Kind regards,

Andrew

 

[jmcilhinney]

The fact that you're using n-tier doesn't really change what you need to do. The DataTable is the model. Your Button.Click event handler is going to invoke a method in the business logic layer and pass it that DataTable. That method will invoke a method in the data access layer to save the changes to the database. Done. The changes will be saved using a data adapter, just like always. You might want to create a Copy of the DataTable first, to ensure any issues don't affect the bound data. You can then return Boolean values in an appropriate way to indicate success or failure and, on success, call AcceptChanges explicitly on the original DataTable.

 

[jmcilhinney]

The data is the model and that gets passed up and down through the layers. You may or may not have transformations at the layer boundaries. For instance, in some of the web apps we build, we use Entity Framework inside a web service and then map to DTOs. The web site makes calls to the service and receives those DTOs, then maps to view models that are passed to the appropriate views. The process is then reversed for saving. If you don't have those transformations then you can just pass the same DataTable all the way up and then all the way down again.

 

[andrewmanuja]

Hi jmcilhinney,
Thanks for your valuable feedback.
Yes, I am using Data Transfer Objects in my application.

According to my understanding, I wrote the below code under the Data Access Layer and it looks like;

using (SqlConnection conn = new SqlConnection(_connStr))

            using (var dCmd = new SqlCommand(@"UPDATE Person SET FirstName = @FirstName, LastName = @LastName, Age = @Age WHERE PersonID = @PersonID", conn))

            using (var adapter = new SqlDataAdapter { UpdateCommand = dCmd })
            {
                conn.Open();
                dCmd.Parameters.AddWithValue("personID", bPerson.PersonID);
                dCmd.Parameters.AddWithValue("firstName", bPerson.FirstName);
                dCmd.Parameters.AddWithValue("lastName", bPerson.LastName);
                dCmd.Parameters.AddWithValue("age", bPerson.Age);

                try
                {
                    foreach (DataRow dr in table.Rows)
                    {                       
                      tablecopy.Rows.Add(dr.ItemArray);
                    }
                    adapter.Update(table);
                    dCmd.Dispose();                                     
                }

                catch (Exception ex)
                {
                    throw ex;
                }
            }

            foreach (DataRow row in table.Rows)
            {
                if (row["Select Item"] as bool? == true)
                {
                    table.AcceptChanges();
                    row.SetAdded();                   
                }
            }
            return true;

And my updated Business Logic Layer is looks like;

 public bool UpdateUser(PersonDTO bPerson)
        {

            try
            {
                return personDAL.UpdateUser(bPerson);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

Finally, the code under the btnUpdate event looks like below;

  foreach (DataRow row in table.Rows)
            {
                int dpersonID = Convert.ToInt32(row.Table.Columns["textPersonID"].ToString());
                string dfirstname = Convert.ToString(row.Table.Columns["textFirstName"].ToString());
                string dlastname = Convert.ToString(row.Table.Columns["textLastName"].ToString());
                int dage = Convert.ToInt32(row.Table.Columns["textAge"].ToString());

                PersonDTO newPerson = new PersonDTO();

                newPerson.PersonID = dpersonID;
                newPerson.FirstName = dfirstname;
                newPerson.LastName = dlastname;
                newPerson.Age = dage;

                try
                {

                    bool personUpdate = personBal.UpdateUser(newPerson);
                    if (personUpdate != true)
                    {
                        MessageBox.Show("No data found to update");
                    }
                    else
                    {
                        //personBal.UpdateUser(newPerson);                       
                        MessageBox.Show("Data updated successfully");
                        return;
                    }
                }
                catch (Exception ee)
                {
                    MessageBox.Show(ee.Message);
                }
            }

However, the update method is not triggering.

Appreciate your feedback please.

Thank you.

Kind regards,

Andrew

 

[Skydiver]

However, the update method is not triggering.

Well as you surmised to get entire train moving, you need that update method to work. The question is: Did you remember to hook up the event receiver to your btnUpdate click handler? Remember that VB.NET WinForm, and ASP.NET WebForms have "auto-wiring", but C# WinForms does not.

 

[andrewmanuja]

Hi Skydiver,

Can you please elaborate this bit more?

Thanks.

Andrew

 

[jmcilhinney]

You say:

Finally, the code under the btnUpdate event looks like below

but there is no such thing as code "under a Button". The code is in a method, which is part of the form, and that method is, presumably, supposed to handle the Click event of your Button. That won't happen all by itself. You have to make it happen by registering the method as a handler for that event. Have you done that? If not, that is why it's not being executed when you click the Button. When you double-click a control in the form designer, a method is generated and registered as a handler for the default event of that control. If you add the method yourself, you need to add the registration too. You can do that in code and it looks something like this:

myObject.SomeEvent += SomeMethod;

If you're talking about controls added in the designer though, you would do it using the Properties window. Click the Events button in that window and you can double-click an event to generate an event handler or select an existing method from a drop-down. If you open the designer code file, you can see all the event handler registrations added through the designer.

 

[andrewmanuja]

Hi jmcilhinney,

Thanks for your feedback.

I have used the designer window and created the event by double clicking on the "btnUpdate" controller.
Please refer the below code block

private void btnUpdate_Click(object sender, EventArgs e)
        {   
            foreach (DataRow row in personBal.LoadAll().Rows)             
             {
                
            }

Actually, I am adding a boolean datacolumn to the datatable in the Data Access Layer and load the data from the database table.

When triggering the update event, I am converting the datatable values in to parameters and passing them to execute the btnUpdate event.

Thus, my datatable, "table" looks like something below;

I got couple of questions,
1. When passing the data to the Data Access Layer, should I declare a DTO for "Select Item" column? This is because, the sql database table does not have a column for "Select Item". However, if needed, can I use something like below;

bool selectItem = Convert.ToBoolean(row.Table.Columns["Select Item"].ToString());

2. I am getting an error when converting the datatable data in to the parameters,

 int personID = Convert.ToInt32(row.Table.Columns["PersonID"].ToString());

Appreciate your valuable feedback please.

Thank you in advance.

Kind regards,

Andrew

 

[andrewmanuja]

Hi jmcilhinney,

1. When passing the data to the Data Access Layer, should I declare a DTO for "Select Item" column? This is because, the sql database table does not have a column for "Select Item". However, if needed, can I use something like below;
C#:

bool selectItem = Convert.ToBoolean(row.Table.Columns["Select Item"].ToString());

Sorry, yes I assume that, need to send the parameter value for the "Select Item" also, since I am performing a validation in the Data Access Layer and accordingly saving the updates to the database table.

Thank you.

Kind regards,

 

[jmcilhinney]

What is the point of that Select Item column? Is it specifically so that you can select which items are to be saved? If so, that is a presentation issue only, so that information should exist in the presentation layer only. The data access layer should have to care about that because it should only be receiving the data for the records that you want to save. Here's an example of how I might do what I think you're trying to do:

public class DataAccess
{
    public DataTable GetPeopleSchema()
    {
        var table = new DataTable();

        using (var adapter = new SqlDataAdapter("SELECT * FROM Person", "connection string here"))
        {
            adapter.FillSchema(table, SchemaType.Source);
        }

        return table;
    }

    public DataTable GetPeople()
    {
        var table = new DataTable();

        using (var adapter = new SqlDataAdapter("SELECT * FROM Person", "connection string here"))
        {
            adapter.Fill(table);
        }

        return table;
    }

    public void SavePeople(DataTable table)
    {
        using (var connection = new SqlConnection("connection string here"))
        using (var command = new SqlCommand("UPDATE Person SET GivenName = @GivenName, FamilyName = @FamilyName, DateOfBirth = @DateOfBirth WHERE PersonId = @PersonId", connection))
        using (var adapter = new SqlDataAdapter {UpdateCommand = command})
        {
            command.Parameters.Add("@GivenName", SqlDbType.VarChar, 50, "GivenName");
            command.Parameters.Add("@FamilyName", SqlDbType.VarChar, 50, "FamilyName");
            command.Parameters.Add("@DateOfBirth", SqlDbType.DateTime2, 0, "DateOfBirth");
            command.Parameters.Add("@ParentId", SqlDbType.Int, 0, "ParentId");

            adapter.Update(table);
        }
    }
}


public class BusinessLogic
{
    private readonly DataAccess dataAccess = new DataAccess();

    public List<PersonDto> GetPeople()
    {
        var table = dataAccess.GetPeople();
        var people = table.AsEnumerable()
                                      .Select(row => new PersonDto
                                                     {
                                                         PersonId = row.Field<int>(nameof(PersonDto.PersonId)),
                                                         GivenName = row.Field<string>(nameof(PersonDto.GivenName)),
                                                         FamilyName = row.Field<string>(nameof(PersonDto.FamilyName)),
                                                         DateOfBirth = row.Field<DateTime>(nameof(PersonDto.DateOfBirth))
                                                     })
                                      .ToList();

        return people;
    }

    public void SavePeople(List<PersonDto> people)
    {
        var table = dataAccess.GetPeopleSchema();

        foreach (var person in people)
        {
            var row = table.NewRow();

            row.ItemArray = new object[]
                            {
                                person.PersonId,
                                person.GivenName,
                                person.FamilyName,
                                person.DateOfBirth
                            };
            table.Rows.Add(row);
            row.SetModified();
        }

        dataAccess.SavePeople(table);
    }
}

public class PersonDto
{
    public int PersonId { get; set; }
    public string GivenName { get; set; }
    public string FamilyName { get; set; }
    public DateTime DateOfBirth { get; set; }
}


public class Presentation
{
    private readonly BusinessLogic businessLogic = new BusinessLogic();

    public void DisplayPeople()
    {
        var personDtos = businessLogic.GetPeople();
        var people = personDtos.Select(dto => new PersonModel(dto)).ToList();
        var peopleForm = new PeopleForm(people);

        peopleForm.Show();
    }

    public void SavePeople(List<PersonModel> people)
    {
        var personDtos = people.Where(p => p.IsSelected)
                                           .Select(p => new PersonDto
                                                                 {
                                                                     PersonId = p.PersonId,
                                                                     GivenName = p.GivenName,
                                                                     FamilyName = p.FamilyName,
                                                                     DateOfBirth = p.DateOfBirth
                                                                 })
                                           .ToList();

        businessLogic.SavePeople(personDtos);
    }
}

public class PersonModel : PersonDto
{
    public bool IsSelected { get; set; }

    public PersonModel(PersonDto person)
    {
        PersonId = person.PersonId;
        GivenName = person.GivenName;
        FamilyName = person.FamilyName;
        DateOfBirth = person.DateOfBirth;
        IsSelected = false;
    }
}

That's obviously simplified and I would probably use somnething like AutoMapper to map the data at each point but, hopefully, you get the idea.

 

[andrewmanuja]

Hi jmcilhinney,

Thank you very much for your valuable feedback.

I am studying your solution at the moment and working on implementing in the Windows Form environment.

Nevertheless, thank you very much once again and will get back to you if any clarifications required.

Kind regards,

Andrew