I'm a student developer and trying to develop a little database client system for registering and listing clients for my friend who runs a small business. I thought this would be the perfect opportunity for me to try to put what I'm studying into something real that could be usable, even if it is just with basic features.
The program uses a local database in my project. I'm using Windows Forms, .NET Framework 4.7.2 with EF6, my database is the service based one that you can add in Visual Studio.
I created a form that is supposed to register clients, however when I click the button, it does not insert the values in the database even though the SQL inserts are running just fine? The database is set with the ID column being autoincrement, so I just bypass setting any ID in my codes since the database is supposed to generate it automatically right?
Here's the SQL output when I run it with some test values:
Opened connection at 17/03/2020 17:30:34 -03:00
SELECT Count(*)
FROM INFORMATION_SCHEMA.TABLES AS t
WHERE t.TABLE_SCHEMA + '.' + t.TABLE_NAME IN ('dbo.Clientes')
OR t.TABLE_NAME = 'EdmMetadata'
-- Executing at 17/03/2020 17:30:34 -03:00
-- Completed in 39 ms with result: 1
Closed connection at 17/03/2020 17:30:34 -03:00
"ZyonCliente.exe" (CLR v4.0.30319: ZyonCliente.exe): Carregado "C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Runtime.Serialization\v4.0_4.0.0.0__b77a5c561934e089\System.Runtime.Serialization.dll". Carregamento de símbolos ignorado. O módulo está otimizado e a opção do depurador 'Apenas Meu Código' está habilitada.
Opened connection at 17/03/2020 17:30:35 -03:00
SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
COUNT(1) AS [A1]
FROM [dbo].[__MigrationHistory] AS [Extent1]
WHERE [Extent1].[ContextKey] = @p__linq__0
) AS [GroupBy1]
-- p__linq__0: 'ZyonCliente1.DAL.ContextoBancoDeDados' (Type = String, Size = 4000)
-- Executing at 17/03/2020 17:30:35 -03:00
-- Failed in 13 ms with error: Invalid object name 'dbo.__MigrationHistory'.
Closed connection at 17/03/2020 17:30:35 -03:00
Opened connection at 17/03/2020 17:30:35 -03:00
SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
COUNT(1) AS [A1]
FROM [dbo].[__MigrationHistory] AS [Extent1]
) AS [GroupBy1]
-- Executing at 17/03/2020 17:30:35 -03:00
-- Failed in 6 ms with error: Invalid object name 'dbo.__MigrationHistory'.
Closed connection at 17/03/2020 17:30:35 -03:00
"ZyonCliente.exe" (CLR v4.0.30319: ZyonCliente.exe): Carregado "EntityFrameworkDynamicProxies-EntityFramework".
Opened connection at 17/03/2020 17:30:35 -03:00
Started transaction at 17/03/2020 17:30:35 -03:00
INSERT [dbo].[Clientes]([Nome], [Endereco], [Data_de_Nascimento], [Email], [Telefone])
VALUES (@0, @1, @2, @3, @4)
SELECT [ID]
FROM [dbo].[Clientes]
WHERE @@ROWCOUNT > 0 AND [ID] = scope_identity()
-- @0: 'testname' (Type = String, Size = 50)
-- @1: 'testaddress' (Type = String, Size = 100)
-- @2: '25/02/2020 17:30:17' (Type = DateTime2)
-- @3: 'testemail' (Type = String, Size = 80)
-- @4: 'testphone' (Type = String, Size = 50)
-- Executing at 17/03/2020 17:30:35 -03:00
-- Completed in 13 ms with result: SqlDataReader
Committed transaction at 17/03/2020 17:30:35 -03:00
Closed connection at 17/03/2020 17:30:35 -03:00
I have tried several different methods, even making an entire new project based on this same database but inserting the values using a direct inserting method (instead of using UnitsOfWork pattern) and as well using the BindingSource component to handle the inserting instead, nothing works and I don't know why because it seems that the SQL is running but the database is just refusing to update?
Here's the code for the button:
private void button1_Click(object sender, EventArgs e)
{
if (nomeTextBox.Text.Length == 0)
{
MessageBox.Show("Campo nome não pode estar vazio", "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
using (UnitsOfWork.UnitOfWork uow = new UnitsOfWork.UnitOfWork())
{
Cliente novocliente = new Cliente();
novocliente.Nome = nomeTextBox.Text;
novocliente.Endereco = enderecoTextBox.Text;
novocliente.Data_de_Nascimento = data_de_NascimentoDateTimePicker.Value;
novocliente.Email = emailTextBox.Text;
novocliente.Telefone = telefoneTextBox.Text;
uow.Clientes.Add(novocliente);
uow.Save();
uow.Dispose();
}
}
}
My UnitsOfWork code is:
using System;
using ZyonCliente1.Model;
namespace ZyonCliente1.UnitsOfWork
{
public class UnitOfWork : IUnitOfWork
{
private ZyonCliente1.DAL.ContextoBancoDeDados _context;
public UnitOfWork(ZyonCliente1.DAL.ContextoBancoDeDados context)
{
_context = context;
}
// Delete this default constructor if using an IoC container
public UnitOfWork()
{
_context = new ZyonCliente1.DAL.ContextoBancoDeDados();
}
public Repositories.IClienteRepository Clientes
{
get { return new ZyonCliente1.Repositories.ClienteRepository(_context); }
}
public void Save()
{
_context.SaveChanges();
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
if (_context != null)
{
_context.Dispose();
_context = null;
}
}
}
}
}
And my DbContext:
namespace ZyonCliente1.DAL
{
using System;
using System.Data.Entity;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Diagnostics;
using ZyonCliente1.Model;
//Database context file
public partial class ContextoBancoDeDados : DbContext
{
public ContextoBancoDeDados()
: base("name=ClientesModel")
{
}
public virtual DbSet<Cliente> Clientes { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
Database.Log = (query) => Debug.Write(query);
}
}
}
Also not sure if it is needed but here's my entity class code:
namespace ZyonCliente1.Model
{
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.Spatial;
[Table("Clientes")]
public partial class Cliente
{
public int ID { get; set; }
[Required]
[StringLength(50)]
public string Nome { get; set; }
[StringLength(100)]
public string Endereco { get; set; }
public DateTime? Data_de_Nascimento { get; set; }
[StringLength(80)]
public string Email { get; set; }
[StringLength(50)]
public string Telefone { get; set; }
}
}
The connection strings:
<connectionStrings>
<add name="ClientesModel" connectionString="data source=(LocalDB)\MSSQLLocalDB;attachdbfilename=|DataDirectory|\ClientesDatabase.mdf;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"
providerName="System.Data.SqlClient" />
<add name="ZyonCliente1.Properties.Settings.ClientesDatabaseConnectionString"
connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\ClientesDatabase.mdf;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
I have also tried to use [Key] on the Id but it also doesn't work. I'm not sure if it is needed to share the code of the other stuff like repositories and the like, since they are just the ordinary pattern for the UnitOfWork and Repositories but I can include them here or share a zip of my project for you guys. I'm really at a loss on what to do here hopefully someone here can help me please? I've been frying my brain on this for two days already.
Firstly, it would help to start with the simplest thing before delving into Unit of Work, Repositories, etc. This just complicates trying to identify the source of the problem.
First thing, if the DB is set up for AutoIncrement then you will need to tell EF that the key is an identity column.
[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int ID { get; set; }
[Key] is not required, but recommended. EF will resolve details like this by convention rather than configuration, but IMO relying on convention leads to developers reading into making assumptions about what they are reading, and assumptions lead to doubt when something inevitably doesn't work as expected.
When dealing with IDisposable and using blocks you do not need, nor should explicitly call Dispose(). using takes care of that.
So I would suggest starting simple with your initial code to ensure you're looking in the right place for things:
private void button1_Click(object sender, EventArgs e)
{
if (nomeTextBox.Text.Length == 0)
{
MessageBox.Show("Campo nome não pode estar vazio", "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
using (var context = new ContextoBancoDeDados("ClientesModel"))
{
Cliente novocliente = new Cliente
{
Nome = nomeTextBox.Text;
Endereco = nomeTextBox.Text;
Data_de_Nascimento = data_de_NascimentoDateTimePicker.Value;
Email = emailTextBox.Text;
Telefone = telefoneTextBox.Text;
};
context.Clientes.Add(novocliente);
context.SaveChanges();
}
}
If that works, then gradually look to re-factor back in your patterns, but only add what's truly justified. It needs to have a reason beyond that someone says it's a "best practice". Patterns like Unit of Work and Repository are extremely useful, but only when they serve a purpose in your code, such as facilitating unit testing. If you have no such requirement then they can just make things more complicated.
If you're not seeing the data, then I suspect your connection string may be pointing at a different database than you are checking.