How to store a List<Object> in a database?

Question

I am working on a project in C# where I have the following situation.

I have a class that represents player:

class Player
{
   int ID;
   string Name;
}

and another representing a team:

class Team
{
   int ID, 
   string Name;
   List<Player> PlayersInThisTeam;
}

The problem is that I have no knowledge about databases and do not know the correct way to store a List.

Answer 1

First you need to choose a database to work with. For C# there are several options, with Microsoft SQL Server the most complete option to work with C#.

To persist your data, the recommended is to use a ORM (Object Relational Mapper, relational object mapper). There are also several options, the most complete being the Entity Framework, that creates and modifies the database for you according to your code, makes lazy and anticipated load of aggregated data, takes care of some validations and more resources.

In the Entity Framework, your Player gets like this:

public class Player
{
   [Key]
   public int PlayerId { get; set; }
   public int? TeamId { get; set; } // O jogador pode ou não ter um time

   public string Name { get; set; }

   public virtual Team Team { get; set; }
}

And your Team:

public class Team
{
   [Key]
   public int TeamId { get; set; } 
   public string Name { get; set; }

   public virtual ICollection<Player> PlayersInThisTeam { get; set; }
}

Inserting a team:

public SeuProjetoContext context = new SeuProjetoContext();
context.Teams.Add(new Team {
    Name = "Time"
});
context.SaveChanges();

Inserting a player:

public SeuProjetoContext context = new SeuProjetoContext();
context.Players.Add(new Player {
    Name = "Jogador"
});
context.SaveChanges();

Updating a player:

public SeuProjetoContext context = new SeuProjetoContext();
var player = context.Players.FirstOrDefault(p => p.Name == "Jogador");
if (player != null) 
{
    player.Name = "Fulano";
    context.Entry(player).State = EntityState.Modified;
    context.SaveChanges();
}

Excluding a player

public SeuProjetoContext context = new SeuProjetoContext();
var player = context.Players.FirstOrDefault(p => p.Name == "Jogador");
if (player != null) 
{
    context.Players.Remove(player);
    context.SaveChanges();
}

Selecting all the players:

public SeuProjetoContext context = new SeuProjetoContext();
var allPlayers = context.Players.ToList();

Associating a team to a player

public SeuProjetoContext context = new SeuProjetoContext();
var player = context.Players.FirstOrDefault(p => p.Name == "Fulano");
if (player != null)
{
    var timeJogador = context.Teams.FirstOfDefault(t => t.Name == "Time");
    if (timeJogador != null)
    {
        player.Time = timeJogador;
        context.Entry(player).State = EntityState.Modified;
        context.SaveChanges();
    }
}

Selecting a team and all its players (advance charge)

public SeuProjetoContext context = new SeuProjetoContext();
var timeComJogadores = context.Teams
                              .Include(t => t.PlayersInThisTeam)
                              .FirstOrDefault(t => t.Name == "Time");
// timeJogadores.PlayersInThisTeam virá com os jogadores populados.

Selecting a team and all its players (lazy load)

public SeuProjetoContext context = new SeuProjetoContext();
var timeComJogadores = context.Teams
                              .FirstOrDefault(t => t.Name == "Time");
var jogadores = timeComJogadores.PlayersInThisTeam.ToList(); // Aqui forço uma segunda consulta apenas com os jogadores.

Answer 2

Complementing the Gypsy, you can use the Dapperwith EFor with a connection that implements Isqlconnection

Dapper works with Extension methods for your connection, i.e., you will initially create a connection to your database, as if using ADO.Net, for example: Sqlconnection, Oracleconnection, Mysqlconnection, etc. In the case of Dapper, you too is responsible for opening and closing your connection.

Player class

 public class Player
{

    [Key] //DataAnnotations - referenciando chave primária
    public int PlayerId { get; set; }
    public string Name { get; set; }

    // Fks
    [ForeignKey("Team")]  //DataAnnotations - referenciando FK
    public int? TeamId { get; set; }

    public virtual Team Team { get; set; }  // propriedade navegativa 
}

Team class

public class Team
    {
        [Key]
        public int TeamId { get; set; }
        public string Name { get; set; }

        // Fks
        public ICollection<Player> Player { get; set; }
    }

Code

static void Main(string[] args)
        {
            //Inserindo Team
            Console.WriteLine("DIgite o nome do Team");
            string nomeTeam = Console.ReadLine();
            var conexao = new TeamPlayerContext().Database.Connection;
            conexao.Query("INSERT INTO Teams (Name) values(@Name)", new { Name = nomeTeam }); // Salvando team

            // Inserindo Player e vinculando a team
            Console.WriteLine("DIgite o nome do jogador");
            string nomeJogador= Console.ReadLine();
                        conexao.Open();
                var dados = conexao.Query("select * from Teams");
                foreach (dynamic linha in dados)
                {
                    Console.WriteLine("{0} - {1}", linha.TeamId, linha.Name);
                }
            Console.WriteLine("Digite para qual time o jogador joga");
            int timeJogador = Convert.ToInt32(Console.ReadLine());
            conexao.Query("INSERT INTO  Players(Name, TeamId) values(@Name, @TeamId)", new { Name = nomeJogador, TeamID = timeJogador }); // salvando player
            // Limpando a tela
            Console.Clear();
            // listando jogadores cadatrados
            Console.WriteLine("Jogadores Cadastrados");
            var jogadores = conexao.Query("select * from Players");
            foreach (dynamic linha in dados)
            {
                Console.WriteLine("{0} - {1} -- {2}", linha.PlayerId, linha.Name, linha.TeamId);
            }
            // Listando team Cadastrados
            Console.WriteLine("Times Cadastrados");
            var times = conexao.Query("select * from Teams");
            foreach (dynamic linha in dados)
            {
                Console.WriteLine("{0} - {1}", linha.TeamId, linha.Name);
            }
            Console.ReadKey();

            conexao.Close();
        }