C# Populate Object

Question

I know people are going to scream that this topic is all over the internet. I know, I've read them. And I still don't understand it. I simply want to populate my object from the results of a stored procedure. Now this stored procedure takes at least 2 parameters - an action and what to find.

@Action @customername

The @Action simply determine what the stored procedure needs to do and returns the results. So for example if I want to update a customer, I'd call the sp through my object:

public class Customer()
{
    //properties

    public int UpdateCustomer()
    {
        using (SQLConnection connection = new SqlConnection(Helper.CnnVal("DataConnection")))
        {
            SQLCommand = new SqlCommand(Helper.Procedure("Customer"), connection);

            command.CommandType = CommandType.StoredProcedure;

            SqlParameterCollection parameterCollection = command.Parameters;

            parameterCollection.Add("@Action", SqlDbType.NVarChar, -1).Value = "Update"

            //complete the rest of it....
        }
    }
}

This works well. So the problem arises when I simply want to populate the object with the results of the sp. In this case I would pass "Retrieve" as the @Action parameter and this.customer_name as the @customername parameter to the sp.

But how do I put the results from the stored procedure into the object?

I have this...

    public void GetCustomer()
    {
        using (SQLConnection connection = new SqlConnection(Helper.CnnVal("DataConnection")))
        {
            var retrieval = new DynamicParameters();

            retrieval.Add("@Action", "Retrieve");
            retrieval.Add("@name", this.customer_Name);

            connection.Open():
            connection.Execute(Helper.Procedure("Customer"), retrieval, commandType: CommandType.StoredProcedure);
        }
    }

But I don't think it's working.

Back a long time ago I used to run a "fetch" for PHP when I needed to populate an object. Should I go back to this?

Take a look at how the code looks here with dapper. https://stackoverflow.com/questions/52683331/using-dapper-how-do-i-pass-in-the-values-for-a-sql-type-as-param You can populate a model from the result — Judy007, Oct 19 '18 at 20:03

Muhammad Azeez · Accepted Answer · 2018-10-19T20:21:14.883

You need to execute a SqlReader on the command, Something like this:

using (var connection = new SqlConnection("Connection"))
using (var command = new SqlCommand("Retrieve", connection))
{
    command.CommandType = System.Data.CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@Action", "Retrieve");
    command.Parameters.AddWithValue("@name", this.customer_Name);

    connection.Open();

    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            var item = new YourClass();
            // You can use GetX() methods to get the value of the fields
            item.Name = reader.GetString("name");
            // You can also access the fields by using bracket notation
            item.Age = (int)reader["age"];

            // Be careful of nullable fields though!
        }
    }
}

score 0 · Answer 2 · answered Oct 19 '18 at 20:43

Using @Encrypt0r advice and guidance I got it working:

public void GetCustomer() {
            using (SqlConnection connection = new SqlConnection(Helper.CnnVal("DataConnection"))) {
                SqlCommand command = new SqlCommand(Helper.Procedure("Customer"), connection);

                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@Action", "Retrieve");
                command.Parameters.AddWithValue("@name", this.customer_name);

                connection.Open();

                using (var reader = command.ExecuteReader()) {
                    while (reader.Read()) {
                        this.tbl_id = (int)reader["tbl_id"];
                        this.customer_name = (string)reader["customer_name"];
                        this.customer_id = reader.GetInt32(customer_id);
                        this.customer_address = (string)reader["customer_address"];
                        this.customer_city = (string)reader["customer_city"];
                        this.customer_state = (string)reader["customer_state"];
                        this.customer_zip = reader.GetInt32(customer_zip);
                    }
                }

C# Populate Object

2 Answers2