Create an object with values from an mssql database

Assuming you are using the MySql Libary to connect to your database. You need a class that allows you to connect to the database which looks like the code segment below; I am using the UWP for Windows 10 with c# so the code should work, however, there may be slight changes but nothing major.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using Windows.UI.Popups;
using Walsall_College_Auditor.Classes;

namespace Walsall_College_Auditor.Models
{
class dbConnect
{
    private MySqlConnection connection;
    private string server;
    private string database;
    private string uid;
    private string password;

    //Constructor
    public dbConnect()
    {
        Initialize();
    }

    //Initialize values
    private void Initialize()
    {
        //Prevent the application from throwing "windows-1252' is not a supported encoding name."
        System.Text.EncodingProvider ppp;
        ppp = System.Text.CodePagesEncodingProvider.Instance;
        Encoding.RegisterProvider(ppp);

        server = "localhost";
        database = "your_db_name"; //Put the new database name here
        uid = "root"; //Your db Login/Username
        password = ""; //Your db login password
        string connectionString = "SERVER=" + server + ";" + "DATABASE=" + database + ";" 
            + "UID=" + uid + ";" + "PASSWORD=" + password + ";SslMode=None";

        connection = new MySqlConnection(connectionString);
    }

    //open connection to database
    private bool OpenConnection()
    {
        try
        {
            connection.Open();
            return true;
        }
        catch (MySqlException ex)
        {
            //When handling errors, you can your application's response based 
            //on the error number.
            //The two most common error numbers when connecting are as follows:
            //0: Cannot connect to the server.
            //1045: Invalid username and/or password.
            switch (ex.Number)
            { 
                case 0:
                    var dialog1 = new MessageDialog("Cannot connect to server.  Contact administrator");
                    dialog1.Title = "Connection Error";
                    dialog1.ShowAsync();
                    break;

                case 1045:
                    var dialog2 = new MessageDialog("Invalid username/password, please try again");
                    dialog2.Title = "Connection Error";
                    dialog2.ShowAsync();
                    break;
            }
            return false;
        }
    }

    //Close connection
    private bool CloseConnection()
    {
        try
        {
            connection.Close();
            return true;
        }
        catch (MySqlException ex)
        {
            var dialog = new MessageDialog(ex.Message);
            dialog.Title = "Disconnecting Error";
            dialog.ShowAsync();
            return false;
        }
    }

There are other methods that I can post if you require them. Personally, I used this Connect c# to MySql to help me develop my methods for querying the database. If you are using windows forms then this will be perfect and if you are using the Universal Windows Platform then you will require a different version of the DLL file and adjustments to your code that are not shown on the provided link.

However to answer your question correctly: The code below exists in the same DB class and the code is a function that returns a list of companies.

public List<company> getSots()
    {
        string query = "SELECT * FROM tbl_spot"; //Your table name here
        List<Spot> dbSpots = new List<Spot>(); //List to store the gathered spots
        if (this.OpenConnection() == true)
        {
            MySqlCommand cmd = new MySqlCommand(query, connection);
            MySqlDataReader dataReader = cmd.ExecuteReader();
            while (dataReader.Read())
            {
                //Create a new company object and populate with a row at a time
                Spot x = new Spot();
                x.Id = int.Parse(dataReader["id_spot"].ToString());
                x.Number = int.Parse(dataReader["number"].ToString());
                x.Capacity = int.Parse(dataReader["capacity"].ToString());
                dbSpots.Add(x); //Add created Spot to the Spots list
            }
            dataReader.Close();
            this.CloseConnection();
            return dbCmpys; //Return the gathered db companies
        }
        else { return dbCmpys; }
    }

As you can see the code creates a list of the object type (in your case Spot), populates it using a loop to cycle through all the database records and returns the list once completed.

To use the function: In another class or form, create a new instance of the DB class, create a list that equals the function; like so:

//Your form code/class
class SpotForm
{
    private dbConnect dbConnection = new dbConnect(); //Database connection
    private List<Spot> listSpots = new List<Spot>(); //Local list

    // Constructor
    public SpotForm()
    {
        listSpots = dbConnection.getSpots();
    }

You now will have a list of the Spots from the database to do with as you please such as looping through to gather or manipulate the data.