feat: list e insert

Data/SqliteClientRepository.cs

@@ -1,9 +1,9 @@
 using System.Collections.Generic;
+using System.Data;
 using System.Threading.Tasks;
 using Microsoft.Data.Sqlite;
 using Microsoft.Extensions.Configuration;
 using Workshop8.Models;
-using System;
 
 namespace Workshop8.Data
 {
@@ -21,76 +21,63 @@ namespace Workshop8.Data
 
         public async Task<IEnumerable<Cliente>> GetAllClientsAsync()
         {
-            var clients = new List<Cliente>();
+            var itens = new List<Cliente>();
 
-            using var connection = new SqliteConnection(_connectionString);
-            await connection.OpenAsync();
+            using SqliteConnection conn = await GetConnection();
 
-            var command = connection.CreateCommand();
-            command.CommandText = "SELECT Id, Nome, Email, CriadoEm FROM Clientes";
+            var cmd = conn.CreateCommand();
+
+            cmd.CommandText = "SELECT Id, Nome, Email, CriadoEm FROM Clientes";
+
+            await using var reader = await cmd.ExecuteReaderAsync();
 
-            using var reader = await command.ExecuteReaderAsync();
             while (await reader.ReadAsync())
             {
-                clients.Add(new Cliente
+                var p = new Cliente
                 {
                     Id = reader.GetInt32(0),
                     Nome = reader.GetString(1),
                     Email = reader.GetString(2),
                     CriadoEm = reader.GetDateTime(3)
-                });
+                };
+                itens.Add(p);
             }
+            return itens;
+        }
 
-            return clients;
+        private static async Task<SqliteConnection> GetConnection()
+        {
+            var conn = new SqliteConnection("Data Source=database.sqlite;");
+            await conn.OpenAsync();
+            return conn;
         }
 
         public async Task AddClientAsync(Cliente c)
         {
-            using var connection = new SqliteConnection(_connectionString);
-            await connection.OpenAsync();
+            var conn = await GetConnection();
 
-            var command = connection.CreateCommand();
-            command.CommandText = @"
-                INSERT INTO Clientes (Nome, Email, CriadoEm) 
-                VALUES ($nome, $email, $criadoEm);
-            ";
+            var cmd = conn.CreateCommand();
 
-            command.Parameters.AddWithValue("$nome", c.Nome);
-            command.Parameters.AddWithValue("$email", c.Email);
-            command.Parameters.AddWithValue("$criadoEm", c.CriadoEm);
+            cmd.CommandText = @"INSERT INTO Clientes (nome, email, criadoEm) 
+            VALUES (@nome, @email, @criadoEm) ";
 
-            await command.ExecuteNonQueryAsync();
+            cmd.CommandType = CommandType.Text;
+            cmd.Parameters.Add(new SqliteParameter("@nome", c.Nome));
+            cmd.Parameters.Add(new SqliteParameter("@email", c.Email));
+            cmd.Parameters.Add(new SqliteParameter("@criadoEm", c.CriadoEm));
+
+            await cmd.ExecuteNonQueryAsync();
+            
         }
 
         public async Task UpdateClientAsync(Cliente c)
         {
-            using var connection = new SqliteConnection(_connectionString);
-            await connection.OpenAsync();
-
-            var command = connection.CreateCommand();
-            command.CommandText = @"
-                UPDATE Clientes 
-                SET Nome = $nome, Email = $email
-                WHERE Id = $id;
-            ";
-
-            command.Parameters.AddWithValue("$nome", c.Nome);
-            command.Parameters.AddWithValue("$email", c.Email);
-            command.Parameters.AddWithValue("$id", c.Id);
-
-            await command.ExecuteNonQueryAsync();
+            // TODO: implementar UPDATE
         }
 
         public async Task DeleteClientAsync(int id)
         {
-            using var connection = new SqliteConnection(_connectionString);
-            await connection.OpenAsync();
-
-            var command = connection.CreateCommand();
-            command.CommandText = "DELETE FROM Clientes WHERE Id = $id;";
-            command.Parameters.AddWithValue("$id", id);
-
-            await command.ExecuteNonQueryAsync();
+            // TODO: implementar DELETE
         }
     }
 }