How to store a Listobject in a database?

4

I'm working on a C # project where I have the following situation.

I have a class that represents a player:

class Player
{
   int ID;
   string Name;
}

and one that represents a team:

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

The problem is that I do not know about databases and I do not know the right way to store a List.

    
asked by anonymous 27.07.2016 / 18:31

2 answers

12

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

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

In the Entity Framework, its Player looks 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();
}

Deleting 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 players:

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

Associating a team with 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 (preload)

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.
    
27.07.2016 / 18:54
9

Complementing with Gypsy, you can use Dapper with EF or a connection that implements ISqlConnection

  

Dapper works with extension methods for your connection, that is,   you will initially create a connection to your database,   as if it were to use ADO.Net, for example: SqlConnection,   OracleConnection, MySqlConnection, etc. In Dapper's case, 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();
        }
    
27.07.2016 / 20:06