How can I move data query away from user form. Winform C#

Question

I'm completely new to C# programming and I'm trying to learn on my own. Currently I'm building a mini-project to exercise.

I understand that the user layer should not have any data query for security reasons perhaps?

So I have created a separate Data Access class to retrieve data. This is what my data access class looks like(I'll be using stored procedures for better security once I learn how to use it):

  public class DataAccess
{


    public List<Customer> FilteredCustomersList(string name)
    {
        using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal("FineCreteDB")))
        {
            var output = connection.Query<Customer>($"SELECT * from `Customers` WHERE `Cust_Name` LIKE '{name}'").ToList();
            return output;
        }
    }

Basically I send over a string from the user form to query the database, the data is retrieved and stored in a list. User form:

 private void RetrieveData()
    {
        try
        {
            DataAccess db = new DataAccess();
            filteredcustomers = db.FilteredCustomersList(CustomerNameTxtBox_AutoComplete.Text);
            ntn_num = filteredcustomers.Select(x => x.Cust_NTN).ElementAt(0);
            strn_num = filteredcustomers.Select(x => x.Cust_STRN).ElementAt(0);
            address = filteredcustomers.Select(x => x.Cust_Address).ElementAt(0);
            phone_num = filteredcustomers.Select(x => x.Cust_Phone).ElementAt(0);
            id_num = filteredcustomers.Select(x => x.Cust_ID).ElementAt(0);
        }
        catch (Exception)
        {
            MessageBox.Show("Customer not found. If customer was recently added, try updating DB.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            DataAccess db = new DataAccess();
            filteredcustomers = db.AllCustomersList();
            ntn_num = "";
            strn_num = "";
            address = "";
            phone_num = "";
        }
    }

On the user form side, "filteredcustomers" holds the list of data sent back, now here is the problem: I use the filteredcustomers list to extract the different column values like so:

address = filteredcustomers.Select(x => x.Cust_Address).ElementAt(0);

and then use them to populate the respective textboxes like:

Address_TxtBox.Text = address;

Everything works fine, but I don't want the userform to have these queries for all individual columns, because from what I've understood so far, this is bad programming and bad for security as well.

Can anyone guide me how I can keep the values in Data Access layer and just call them into my form? I'm sorry if this is a long post, I'm just learning and wanted to be as detailed as possible.

Answer 1

You're already doing everything reasonably correctly as per how Dapper is to be used. Dapper doesn't maintain a local graph of entities from the db, track changes to it and automatically save them. If you want that, use something like EF

For dapper you retrieve data with a SELECT and send it back with an UPDATE

If you're only expecting one Customer for the name, do this:

var output = connection.QueryFirstOrDefault<Customer>($"SELECT * from `Customers` WHERE `Cust_Name` LIKE @n", new { n = name });

https://dapper-tutorial.net/queryfirst

This will return just one customer instance (or null; check it!) meaning you can tidy up your form code to:

        c = db.FilteredCustomer(CustomerNameTxtBox_AutoComplete.Text);
        ntn_num = c?.Cust_NTN;
        strn_num = c?.Cust_STRN;

And so on

Your "if customer was recently added try updating db" doesn't really make sense- the query is done live, so the db is about as up to date as it can be