Create an object with values from an mssql database

Question

I have a database with the following tables:

**Camping Spot**
PK - id          - int
FK - location_id - int
     number      - int
     capacity    - int

**Location**
PK - id          - int
     name        - string
     street      - varchar
     etc.

**Event**
PK - id          - int
FK - location_id - int
     name        - string
     datestart   - datetime
     etc.

I have the following classes

namespace modellen
{
    public class Spot
    {
        public int Id { get; set; }
        public int Number { get; set; }
        public int Capacity { get; set; }
    }
}

and

namespace DAL_laag
{
    public class SpotDal
    {
        List<Spot> spots = new List<Spot>();
        private Database database = new Database();

        public GiveAvailiableSpots(int event_id)
        {
            string query = "A query that gets Id, Number and Capacity";
            return ?
        }

    }

I want to get the id, number and capacity values from the table with a mssql query. Then I want to create a new Spot object and add the object to my list of Spots.

I can't figure out what the query would be to get these three value and the code to create a new object with these three values.

How would I do this?

Answer 1

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.

Answer 2

I think your query needs to look something like this.

string query = @"select  id, 
        number, 
        capacity 
from tblCampingSport cs
left join tblLocation l on cs.location_id == l.id
left join tblEvent e on e.location_id = l.id
where e.id = @eventId";

You're call to the db will look something like this:

List<QueryResult> results = new List<QueryResult>();

using(SqlConnection conn = new SqlConnection(ConnectionString))
{
    conn.Open();
    using(SqlCommand cmd = new SqlCommand(query, conn)
    {
        cmd.Parameters.AddWithValue("@eventId", event_id);
        var reader = cmd.ExecuteReader();

        if(reader.HasRows())
        {
            while(reader.Read())
            {
                QueryResult result = new QueryResult();
                result.EventId = (int)reader["id"];
                result.Number = (int)reader["number"];
                result.Capacity = (int)reader["capacity"];
                results.Add(result);
            }
        }
    }
}

Your class to store the results:

class QueryResult
{
    int EventId { get; set;}
    int Number { get; set;}
    int Capacity { get; set;}
}

None of this is tested or even compiled (it was written straight into this textbox), but I think it's a rough outline of how to get what you want from your tables.