F
F.oliveirarocha
Guest
Hi folks, back again in my new project, I'm getting an error message saying:
Title: Exception Unhandled
System.Format Exception. "A chain character inserted was not in a correct format" <---- translated ---> or "The input string was not in the correct format"
The error is on line 105, of the FrmClientsFile.cs, which I'm including below, as well as the SQL Server ClientTB,
I did a repository using a SQL Server procedure, and also making consult, (query on the DB). When I try to save a new client I get the error message above. I checked all itens of the table, and everything is matching (fields and sizes).
Please I need help and all suggestion as possible. Thanks in advance.
Obs.: A Consult on a clients' cellular, in another form, is performing correctly, using the Repository ClientBusiness with the procedure at SQL Server.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Autocarweb;
using System.Configuration;
using System.Data.SqlClient;
using Autocarweb.Windowsform;
using System.Domain;
using System.Domain.Business;
using System.Domain.Interfaces;
using System.Domain.Repositories;
using AutocarWebConnection.Interface;
namespace Autocarweb.Windowsform
{
public partial class FrmClientsFile : Form
{
private ActionScreen _actionScreen;
public FrmClientsFile(ActionScreen actionScreen, Client client)
{
_actionScreen = actionScreen;
InitializeComponent();
txtClientID.ReadOnly = true;
txtClientID.TabStop = false;
if(actionScreen == ActionScreen.Consult)
{
txtClientID.Text = client.ClientID.ToString();
txtname.Text = client.Name;
txtAddress.Text = client.Address;
txtcity.Text = client.City;
txtCounty.Text = client.County;
txtState.Text = client.State;
txtzip_code.Text = client.Zip_Code;
txtcpf.Text = client.CPF;
txtIdnumber.Text = client.Idnumber;
txtemail.Text = client.email;
txtcelular.Text = client.celular_phone;
txtTelResidence.Text = client.home_phone;
// desabilitar os campos da consulta
txtname.ReadOnly = true;
txtname.TabStop = false;
txtAddress.ReadOnly = true;
txtAddress.TabStop = false;
txtcity.ReadOnly = true;
txtcity.TabStop = false;
txtCounty.ReadOnly = true;
txtCounty.TabStop = false;
txtState.ReadOnly = true;
txtState.TabStop = false;
txtzip_code.ReadOnly = true;
txtzip_code.TabStop = false;
txtcpf.ReadOnly = true;
txtcpf.TabStop = false;
txtIdnumber.ReadOnly = true;
txtIdnumber.TabStop = false;
txtemail.ReadOnly = true;
txtemail.TabStop = false;
txtcelular.ReadOnly = true;
txtcelular.TabStop = false;
txtTelResidence.ReadOnly = true;
txtTelResidence.TabStop = false;
btn_save.Enabled = false;
}
}
private void btn_close_Click(object sender, EventArgs e)
{
this.Close();
}
private void btn_save_Click(object sender, EventArgs e)
{
ClientBusiness clientBusiness = new ClientBusiness();
// ClientRepository clientRepository = new ClientRepository();
Client client = new Client();
client.Name = txtname.Text;
client.Address = txtAddress.Text;
client.City = txtcity.Text;
client.County = txtCounty.Text;
client.State = txtState.Text;
client.Zip_Code = txtzip_code.Text;
client.CPF = txtcpf.Text;
client.Idnumber = txtIdnumber.Text;
client.email = txtemail.Text;
client.celular_phone = txtcelular.Text;
client.home_phone = txtTelResidence.Text;
var returned = clientBusiness.Insert(client);
int ID = Convert.ToInt32(returned); // Error at this line
MessageBox.Show("Cliente " + returned + " Inserido com Sucesso!", "AVISO", MessageBoxButtons.OK,
MessageBoxIcon.Information);
/*
try
{
int ID = Convert.ToInt32(returned);
MessageBox.Show("Cliente " + returned + " Inserido com Sucesso!", "AVISO", MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
catch (Exception Ex)
{
MessageBox.Show("Cliente não pode serInserido!", "ERRO", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
*/
}
}
}
Now I will include the SQL Server Table - I'm using Microsoft SQL Server Management Studio 12.0.4237.0
ClientID int Unchecked
Name varchar(50) Checked
Address varchar(50) Checked
City varchar(50) Checked
County varchar(50) Checked
State varchar(2) Checked
Zip_Code varchar(10) Checked
CPF varchar(14) Checked
Idnumber varchar(14) Checked
email varchar(50) Checked
celular_phone varchar(15)Checked
home_phone varchar(15) Checked
Now below is the procedure:
Create procedure uspClientInsert
@name as varchar(50),
@Address as varchar(50),
@City as varchar(50),
@County as varchar(50),
@State as varchar(2),
@Zip_Code varchar (10),
@CPF varchar (14),
@Idnumber varchar(14),
@email varchar(50),
@celular_phone varchar (15),
@home_phone varchar(15)
as
begin
insert into ClientTB
( Name, Address, City, County, State, Zip_Code, CPF, Idnumber, email, celular_phone, home_phone)
values
('@name', '@Address', '@City', '@County', '@State', '@Zip_Code', '@CPF', '@Idnumber', '@email', '@celular_phone', '@home_phone')
select @@IDENTITY as returnedID
end
And the Repositories:
public string Insert(Client entity)
{
try
{
clearParameters();
// addParameters("@clientID", entity.ClientID);
addParameters("@name", entity.Name);
addParameters("@Address", entity.Address);
addParameters("@City", entity.City);
addParameters("@County", entity.County);
addParameters("@State", entity.State);
addParameters("@Zip_Code", entity.Zip_Code);
addParameters("@CPF", entity.CPF);
addParameters("@Idnumber", entity.Idnumber);
addParameters("@email", entity.email);
addParameters("@celular_phone", entity.celular_phone);
addParameters("@home_phone", entity.home_phone);
string returnID = executeCommand(CommandType.StoredProcedure, "uspClientInsert").ToString();
return returnID;
}
catch (Exception Ex)
{
return Ex.Message;
}
Save procedure at the ClientRepository
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using AutocarWebConnection;
using AutocarWebConnection.Interface;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace System.Domain.Repositories
{
public class ClientRepository : Context, IRepository<Client>
{
public string Delete(Client entity)
{
throw new NotImplementedException();
}
public Client FindID(int id)
{
throw new NotImplementedException();
}
public IEnumerable<Client> ListCell_Phone(string Cell_phone)
{
try
{
clearParameters();
addParameters("@celular_phone", Cell_phone);
DataTable dtClient = new DataTable();
IList<Client> clients = new List<Client>();
dtClient = consultExecution(CommandType.StoredProcedure, "uspClientConsult_celular_phone");
foreach (DataRow line in dtClient.Rows)
{
Client client = new Client();
client.ClientID = Convert.ToInt32(line["ID"]);
client.Name = line["Name"].ToString();
client.Address = line["Address"].ToString();
client.County = line["County"].ToString();
client.City = line["City"].ToString();
client.State = line["State"].ToString();
client.Zip_Code = line["Zip_Code"].ToString();
client.CPF = line["CPF"].ToString();
client.Idnumber = line["IdNumber"].ToString();
client.celular_phone = line["Celular_phone"].ToString();
client.home_phone = line["home_phone"].ToString();
clients.Add(client);
}
return clients;
}
catch (Exception Ex)
{
throw new Exception(Ex.Message);
}
}
public string Save(Client entity)
{
string returned = "";
if (entity.ClientID <= 0)
{
returned = Insert(entity);
}
if (entity.ClientID > 0)
{
returned = Update(entity);
}
return returned;
}
public string Insert(Client entity)
{
try
{
clearParameters();
// addParameters("@clientID", entity.ClientID);
addParameters("@name", entity.Name);
addParameters("@Address", entity.Address);
addParameters("@City", entity.City);
addParameters("@County", entity.County);
addParameters("@State", entity.State);
addParameters("@Zip_Code", entity.Zip_Code);
addParameters("@CPF", entity.CPF);
addParameters("@Idnumber", entity.Idnumber);
addParameters("@email", entity.email);
addParameters("@celular_phone", entity.celular_phone);
addParameters("@home_phone", entity.home_phone);
string returnID = executeCommand(CommandType.StoredProcedure, "uspClientInsert").ToString();
return returnID;
}
catch (Exception Ex)
{
return Ex.Message;
}
}
public string Update(Client entity)
{
return null;
}
public IEnumerable<Client> ListallClients()
{
try
{
DataTable dtClient = new DataTable();
IList<Client> clients = new List<Client>();
dtClient = consultExecution(CommandType.StoredProcedure, "uspClientConsultAllclients");
foreach (DataRow line in dtClient.Rows)
{
Client client = new Client();
// client.ClientID = Convert.ToInt32(line["ID"]);
client.Name = line["Name"].ToString();
client.Address = line["Address"].ToString();
client.County = line["County"].ToString();
client.City = line["City"].ToString();
client.State = line["State"].ToString();
client.Zip_Code = line["Zip_Code"].ToString();
client.CPF = line["CPF"].ToString();
client.Idnumber = line["IdNumber"].ToString();
client.celular_phone = line["Celular_phone"].ToString();
client.home_phone = line["home_phone"].ToString();
clients.Add(client);
}
return clients;
}
catch (Exception Ex)
{
throw new Exception(Ex.Message);
}
}
}
}
public string Save(Client entity)
{
string returned = "";
if (entity.ClientID <= 0)
{
returned = Insert(entity);
}
if (entity.ClientID > 0)
{
returned = Update(entity);
}
return returned;
}
Client Repository with all methods.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using AutocarWebConnection;
using AutocarWebConnection.Interface;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace System.Domain.Repositories
{
public class ClientRepository : Context, IRepository<Client>
{
public string Delete(Client entity)
{
throw new NotImplementedException();
}
public Client FindID(int id)
{
throw new NotImplementedException();
}
public IEnumerable<Client> ListCell_Phone(string Cell_phone)
{
try
{
clearParameters();
addParameters("@celular_phone", Cell_phone);
DataTable dtClient = new DataTable();
IList<Client> clients = new List<Client>();
dtClient = consultExecution(CommandType.StoredProcedure, "uspClientConsult_celular_phone");
foreach (DataRow line in dtClient.Rows)
{
Client client = new Client();
client.ClientID = Convert.ToInt32(line["ID"]);
client.Name = line["Name"].ToString();
client.Address = line["Address"].ToString();
client.County = line["County"].ToString();
client.City = line["City"].ToString();
client.State = line["State"].ToString();
client.Zip_Code = line["Zip_Code"].ToString();
client.CPF = line["CPF"].ToString();
client.Idnumber = line["IdNumber"].ToString();
client.celular_phone = line["Celular_phone"].ToString();
client.home_phone = line["home_phone"].ToString();
clients.Add(client);
}
return clients;
}
catch (Exception Ex)
{
throw new Exception(Ex.Message);
}
}
public string Save(Client entity)
{
string returned = "";
if (entity.ClientID <= 0)
{
returned = Insert(entity);
}
if (entity.ClientID > 0)
{
returned = Update(entity);
}
return returned;
}
public string Insert(Client entity)
{
try
{
clearParameters();
// addParameters("@clientID", entity.ClientID);
addParameters("@name", entity.Name);
addParameters("@Address", entity.Address);
addParameters("@City", entity.City);
addParameters("@County", entity.County);
addParameters("@State", entity.State);
addParameters("@Zip_Code", entity.Zip_Code);
addParameters("@CPF", entity.CPF);
addParameters("@Idnumber", entity.Idnumber);
addParameters("@email", entity.email);
addParameters("@celular_phone", entity.celular_phone);
addParameters("@home_phone", entity.home_phone);
string returnID = executeCommand(CommandType.StoredProcedure, "uspClientInsert").ToString();
return returnID;
}
catch (Exception Ex)
{
return Ex.Message;
}
}
public string Update(Client entity)
{
return null;
}
public IEnumerable<Client> ListallClients()
{
try
{
DataTable dtClient = new DataTable();
IList<Client> clients = new List<Client>();
dtClient = consultExecution(CommandType.StoredProcedure, "uspClientConsultAllclients");
foreach (DataRow line in dtClient.Rows)
{
Client client = new Client();
// client.ClientID = Convert.ToInt32(line["ID"]);
client.Name = line["Name"].ToString();
client.Address = line["Address"].ToString();
client.County = line["County"].ToString();
client.City = line["City"].ToString();
client.State = line["State"].ToString();
client.Zip_Code = line["Zip_Code"].ToString();
client.CPF = line["CPF"].ToString();
client.Idnumber = line["IdNumber"].ToString();
client.celular_phone = line["Celular_phone"].ToString();
client.home_phone = line["home_phone"].ToString();
clients.Add(client);
}
return clients;
}
catch (Exception Ex)
{
throw new Exception(Ex.Message);
}
}
}
}
IRepository Interface
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace AutocarWebConnection.Interface
{
public interface IRepository<T> where T : class
{
string Save(T entity);
string Delete(T entity);
T FindID(int id);
IEnumerable<T> ListallClients();
}
}
Class Context - Establishing connection and excuting commands.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace AutocarWebConnection
{
public class Context
{
private SqlConnection connection;
private SqlConnection Createconnection()
{
connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Strconnection"].ConnectionString);
return connection;
}
private SqlParameterCollection sqlParameterCollection = new SqlCommand().Parameters;
private SqlCommand createcommand(CommandType cmdType, string procedureName)
{
connection = Createconnection();
connection.Open();
SqlCommand cmd = connection.CreateCommand();
cmd.CommandType = cmdType;
cmd.CommandText = procedureName;
cmd.CommandTimeout = 7200;
foreach (SqlParameter sqlParameter in sqlParameterCollection)
{
cmd.Parameters.Add(new SqlParameter(sqlParameter.ParameterName, sqlParameter.Value));
}
return cmd;
}
protected void addParameters(string parameterName, object parameterValue)
{
sqlParameterCollection.AddWithValue(parameterName, parameterValue);
}
protected void clearParameters()
{
sqlParameterCollection.Clear();
}
// Metodo que executa a persistência no Banco de Dados (Inserir, Alterar, Excluir)
protected object executeCommand(CommandType cmdType, string procedureName)
{
try
{
SqlCommand cmd = createcommand(cmdType, procedureName);
return cmd.ExecuteScalar();
}
catch (Exception Ex)
{
throw new Exception(Ex.Message);
}
finally
{
connection.Close();
}
}
// Metodo de executa a consulta no Banco de Dados
protected DataTable consultExecution(CommandType cmdType, string procedureName)
{
try
{
SqlCommand cmd = createcommand(cmdType, procedureName);
DataTable dt = new DataTable();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
sqlDataAdapter.Fill(dt);
return dt;
}
catch (Exception Ex)
{
throw new Exception(Ex.Message);
}
finally
{
connection.Close();
}
}
}
}
And Finally the Class Client:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace System.Domain
{
public class Client
{
public int ClientID { get; set; }
public string Name { get; set; }
public string Address { get; set; }
public string City { get; set; }
public string County { get; set; }
public string State { get; set; }
public string Zip_Code { get; set; }
public string CPF { get; set; }
public string Idnumber { get; set; }
public string email { get; set; }
public string celular_phone { get; set; }
public string home_phone { get; set; }
}
}
Continue reading...
Title: Exception Unhandled
System.Format Exception. "A chain character inserted was not in a correct format" <---- translated ---> or "The input string was not in the correct format"
The error is on line 105, of the FrmClientsFile.cs, which I'm including below, as well as the SQL Server ClientTB,
I did a repository using a SQL Server procedure, and also making consult, (query on the DB). When I try to save a new client I get the error message above. I checked all itens of the table, and everything is matching (fields and sizes).
Please I need help and all suggestion as possible. Thanks in advance.
Obs.: A Consult on a clients' cellular, in another form, is performing correctly, using the Repository ClientBusiness with the procedure at SQL Server.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Autocarweb;
using System.Configuration;
using System.Data.SqlClient;
using Autocarweb.Windowsform;
using System.Domain;
using System.Domain.Business;
using System.Domain.Interfaces;
using System.Domain.Repositories;
using AutocarWebConnection.Interface;
namespace Autocarweb.Windowsform
{
public partial class FrmClientsFile : Form
{
private ActionScreen _actionScreen;
public FrmClientsFile(ActionScreen actionScreen, Client client)
{
_actionScreen = actionScreen;
InitializeComponent();
txtClientID.ReadOnly = true;
txtClientID.TabStop = false;
if(actionScreen == ActionScreen.Consult)
{
txtClientID.Text = client.ClientID.ToString();
txtname.Text = client.Name;
txtAddress.Text = client.Address;
txtcity.Text = client.City;
txtCounty.Text = client.County;
txtState.Text = client.State;
txtzip_code.Text = client.Zip_Code;
txtcpf.Text = client.CPF;
txtIdnumber.Text = client.Idnumber;
txtemail.Text = client.email;
txtcelular.Text = client.celular_phone;
txtTelResidence.Text = client.home_phone;
// desabilitar os campos da consulta
txtname.ReadOnly = true;
txtname.TabStop = false;
txtAddress.ReadOnly = true;
txtAddress.TabStop = false;
txtcity.ReadOnly = true;
txtcity.TabStop = false;
txtCounty.ReadOnly = true;
txtCounty.TabStop = false;
txtState.ReadOnly = true;
txtState.TabStop = false;
txtzip_code.ReadOnly = true;
txtzip_code.TabStop = false;
txtcpf.ReadOnly = true;
txtcpf.TabStop = false;
txtIdnumber.ReadOnly = true;
txtIdnumber.TabStop = false;
txtemail.ReadOnly = true;
txtemail.TabStop = false;
txtcelular.ReadOnly = true;
txtcelular.TabStop = false;
txtTelResidence.ReadOnly = true;
txtTelResidence.TabStop = false;
btn_save.Enabled = false;
}
}
private void btn_close_Click(object sender, EventArgs e)
{
this.Close();
}
private void btn_save_Click(object sender, EventArgs e)
{
ClientBusiness clientBusiness = new ClientBusiness();
// ClientRepository clientRepository = new ClientRepository();
Client client = new Client();
client.Name = txtname.Text;
client.Address = txtAddress.Text;
client.City = txtcity.Text;
client.County = txtCounty.Text;
client.State = txtState.Text;
client.Zip_Code = txtzip_code.Text;
client.CPF = txtcpf.Text;
client.Idnumber = txtIdnumber.Text;
client.email = txtemail.Text;
client.celular_phone = txtcelular.Text;
client.home_phone = txtTelResidence.Text;
var returned = clientBusiness.Insert(client);
int ID = Convert.ToInt32(returned); // Error at this line
MessageBox.Show("Cliente " + returned + " Inserido com Sucesso!", "AVISO", MessageBoxButtons.OK,
MessageBoxIcon.Information);
/*
try
{
int ID = Convert.ToInt32(returned);
MessageBox.Show("Cliente " + returned + " Inserido com Sucesso!", "AVISO", MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
catch (Exception Ex)
{
MessageBox.Show("Cliente não pode serInserido!", "ERRO", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
*/
}
}
}
Now I will include the SQL Server Table - I'm using Microsoft SQL Server Management Studio 12.0.4237.0
ClientID int Unchecked
Name varchar(50) Checked
Address varchar(50) Checked
City varchar(50) Checked
County varchar(50) Checked
State varchar(2) Checked
Zip_Code varchar(10) Checked
CPF varchar(14) Checked
Idnumber varchar(14) Checked
email varchar(50) Checked
celular_phone varchar(15)Checked
home_phone varchar(15) Checked
Now below is the procedure:
Create procedure uspClientInsert
@name as varchar(50),
@Address as varchar(50),
@City as varchar(50),
@County as varchar(50),
@State as varchar(2),
@Zip_Code varchar (10),
@CPF varchar (14),
@Idnumber varchar(14),
@email varchar(50),
@celular_phone varchar (15),
@home_phone varchar(15)
as
begin
insert into ClientTB
( Name, Address, City, County, State, Zip_Code, CPF, Idnumber, email, celular_phone, home_phone)
values
('@name', '@Address', '@City', '@County', '@State', '@Zip_Code', '@CPF', '@Idnumber', '@email', '@celular_phone', '@home_phone')
select @@IDENTITY as returnedID
end
And the Repositories:
public string Insert(Client entity)
{
try
{
clearParameters();
// addParameters("@clientID", entity.ClientID);
addParameters("@name", entity.Name);
addParameters("@Address", entity.Address);
addParameters("@City", entity.City);
addParameters("@County", entity.County);
addParameters("@State", entity.State);
addParameters("@Zip_Code", entity.Zip_Code);
addParameters("@CPF", entity.CPF);
addParameters("@Idnumber", entity.Idnumber);
addParameters("@email", entity.email);
addParameters("@celular_phone", entity.celular_phone);
addParameters("@home_phone", entity.home_phone);
string returnID = executeCommand(CommandType.StoredProcedure, "uspClientInsert").ToString();
return returnID;
}
catch (Exception Ex)
{
return Ex.Message;
}
Save procedure at the ClientRepository
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using AutocarWebConnection;
using AutocarWebConnection.Interface;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace System.Domain.Repositories
{
public class ClientRepository : Context, IRepository<Client>
{
public string Delete(Client entity)
{
throw new NotImplementedException();
}
public Client FindID(int id)
{
throw new NotImplementedException();
}
public IEnumerable<Client> ListCell_Phone(string Cell_phone)
{
try
{
clearParameters();
addParameters("@celular_phone", Cell_phone);
DataTable dtClient = new DataTable();
IList<Client> clients = new List<Client>();
dtClient = consultExecution(CommandType.StoredProcedure, "uspClientConsult_celular_phone");
foreach (DataRow line in dtClient.Rows)
{
Client client = new Client();
client.ClientID = Convert.ToInt32(line["ID"]);
client.Name = line["Name"].ToString();
client.Address = line["Address"].ToString();
client.County = line["County"].ToString();
client.City = line["City"].ToString();
client.State = line["State"].ToString();
client.Zip_Code = line["Zip_Code"].ToString();
client.CPF = line["CPF"].ToString();
client.Idnumber = line["IdNumber"].ToString();
client.celular_phone = line["Celular_phone"].ToString();
client.home_phone = line["home_phone"].ToString();
clients.Add(client);
}
return clients;
}
catch (Exception Ex)
{
throw new Exception(Ex.Message);
}
}
public string Save(Client entity)
{
string returned = "";
if (entity.ClientID <= 0)
{
returned = Insert(entity);
}
if (entity.ClientID > 0)
{
returned = Update(entity);
}
return returned;
}
public string Insert(Client entity)
{
try
{
clearParameters();
// addParameters("@clientID", entity.ClientID);
addParameters("@name", entity.Name);
addParameters("@Address", entity.Address);
addParameters("@City", entity.City);
addParameters("@County", entity.County);
addParameters("@State", entity.State);
addParameters("@Zip_Code", entity.Zip_Code);
addParameters("@CPF", entity.CPF);
addParameters("@Idnumber", entity.Idnumber);
addParameters("@email", entity.email);
addParameters("@celular_phone", entity.celular_phone);
addParameters("@home_phone", entity.home_phone);
string returnID = executeCommand(CommandType.StoredProcedure, "uspClientInsert").ToString();
return returnID;
}
catch (Exception Ex)
{
return Ex.Message;
}
}
public string Update(Client entity)
{
return null;
}
public IEnumerable<Client> ListallClients()
{
try
{
DataTable dtClient = new DataTable();
IList<Client> clients = new List<Client>();
dtClient = consultExecution(CommandType.StoredProcedure, "uspClientConsultAllclients");
foreach (DataRow line in dtClient.Rows)
{
Client client = new Client();
// client.ClientID = Convert.ToInt32(line["ID"]);
client.Name = line["Name"].ToString();
client.Address = line["Address"].ToString();
client.County = line["County"].ToString();
client.City = line["City"].ToString();
client.State = line["State"].ToString();
client.Zip_Code = line["Zip_Code"].ToString();
client.CPF = line["CPF"].ToString();
client.Idnumber = line["IdNumber"].ToString();
client.celular_phone = line["Celular_phone"].ToString();
client.home_phone = line["home_phone"].ToString();
clients.Add(client);
}
return clients;
}
catch (Exception Ex)
{
throw new Exception(Ex.Message);
}
}
}
}
public string Save(Client entity)
{
string returned = "";
if (entity.ClientID <= 0)
{
returned = Insert(entity);
}
if (entity.ClientID > 0)
{
returned = Update(entity);
}
return returned;
}
Client Repository with all methods.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using AutocarWebConnection;
using AutocarWebConnection.Interface;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace System.Domain.Repositories
{
public class ClientRepository : Context, IRepository<Client>
{
public string Delete(Client entity)
{
throw new NotImplementedException();
}
public Client FindID(int id)
{
throw new NotImplementedException();
}
public IEnumerable<Client> ListCell_Phone(string Cell_phone)
{
try
{
clearParameters();
addParameters("@celular_phone", Cell_phone);
DataTable dtClient = new DataTable();
IList<Client> clients = new List<Client>();
dtClient = consultExecution(CommandType.StoredProcedure, "uspClientConsult_celular_phone");
foreach (DataRow line in dtClient.Rows)
{
Client client = new Client();
client.ClientID = Convert.ToInt32(line["ID"]);
client.Name = line["Name"].ToString();
client.Address = line["Address"].ToString();
client.County = line["County"].ToString();
client.City = line["City"].ToString();
client.State = line["State"].ToString();
client.Zip_Code = line["Zip_Code"].ToString();
client.CPF = line["CPF"].ToString();
client.Idnumber = line["IdNumber"].ToString();
client.celular_phone = line["Celular_phone"].ToString();
client.home_phone = line["home_phone"].ToString();
clients.Add(client);
}
return clients;
}
catch (Exception Ex)
{
throw new Exception(Ex.Message);
}
}
public string Save(Client entity)
{
string returned = "";
if (entity.ClientID <= 0)
{
returned = Insert(entity);
}
if (entity.ClientID > 0)
{
returned = Update(entity);
}
return returned;
}
public string Insert(Client entity)
{
try
{
clearParameters();
// addParameters("@clientID", entity.ClientID);
addParameters("@name", entity.Name);
addParameters("@Address", entity.Address);
addParameters("@City", entity.City);
addParameters("@County", entity.County);
addParameters("@State", entity.State);
addParameters("@Zip_Code", entity.Zip_Code);
addParameters("@CPF", entity.CPF);
addParameters("@Idnumber", entity.Idnumber);
addParameters("@email", entity.email);
addParameters("@celular_phone", entity.celular_phone);
addParameters("@home_phone", entity.home_phone);
string returnID = executeCommand(CommandType.StoredProcedure, "uspClientInsert").ToString();
return returnID;
}
catch (Exception Ex)
{
return Ex.Message;
}
}
public string Update(Client entity)
{
return null;
}
public IEnumerable<Client> ListallClients()
{
try
{
DataTable dtClient = new DataTable();
IList<Client> clients = new List<Client>();
dtClient = consultExecution(CommandType.StoredProcedure, "uspClientConsultAllclients");
foreach (DataRow line in dtClient.Rows)
{
Client client = new Client();
// client.ClientID = Convert.ToInt32(line["ID"]);
client.Name = line["Name"].ToString();
client.Address = line["Address"].ToString();
client.County = line["County"].ToString();
client.City = line["City"].ToString();
client.State = line["State"].ToString();
client.Zip_Code = line["Zip_Code"].ToString();
client.CPF = line["CPF"].ToString();
client.Idnumber = line["IdNumber"].ToString();
client.celular_phone = line["Celular_phone"].ToString();
client.home_phone = line["home_phone"].ToString();
clients.Add(client);
}
return clients;
}
catch (Exception Ex)
{
throw new Exception(Ex.Message);
}
}
}
}
IRepository Interface
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace AutocarWebConnection.Interface
{
public interface IRepository<T> where T : class
{
string Save(T entity);
string Delete(T entity);
T FindID(int id);
IEnumerable<T> ListallClients();
}
}
Class Context - Establishing connection and excuting commands.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace AutocarWebConnection
{
public class Context
{
private SqlConnection connection;
private SqlConnection Createconnection()
{
connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Strconnection"].ConnectionString);
return connection;
}
private SqlParameterCollection sqlParameterCollection = new SqlCommand().Parameters;
private SqlCommand createcommand(CommandType cmdType, string procedureName)
{
connection = Createconnection();
connection.Open();
SqlCommand cmd = connection.CreateCommand();
cmd.CommandType = cmdType;
cmd.CommandText = procedureName;
cmd.CommandTimeout = 7200;
foreach (SqlParameter sqlParameter in sqlParameterCollection)
{
cmd.Parameters.Add(new SqlParameter(sqlParameter.ParameterName, sqlParameter.Value));
}
return cmd;
}
protected void addParameters(string parameterName, object parameterValue)
{
sqlParameterCollection.AddWithValue(parameterName, parameterValue);
}
protected void clearParameters()
{
sqlParameterCollection.Clear();
}
// Metodo que executa a persistência no Banco de Dados (Inserir, Alterar, Excluir)
protected object executeCommand(CommandType cmdType, string procedureName)
{
try
{
SqlCommand cmd = createcommand(cmdType, procedureName);
return cmd.ExecuteScalar();
}
catch (Exception Ex)
{
throw new Exception(Ex.Message);
}
finally
{
connection.Close();
}
}
// Metodo de executa a consulta no Banco de Dados
protected DataTable consultExecution(CommandType cmdType, string procedureName)
{
try
{
SqlCommand cmd = createcommand(cmdType, procedureName);
DataTable dt = new DataTable();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
sqlDataAdapter.Fill(dt);
return dt;
}
catch (Exception Ex)
{
throw new Exception(Ex.Message);
}
finally
{
connection.Close();
}
}
}
}
And Finally the Class Client:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace System.Domain
{
public class Client
{
public int ClientID { get; set; }
public string Name { get; set; }
public string Address { get; set; }
public string City { get; set; }
public string County { get; set; }
public string State { get; set; }
public string Zip_Code { get; set; }
public string CPF { get; set; }
public string Idnumber { get; set; }
public string email { get; set; }
public string celular_phone { get; set; }
public string home_phone { get; set; }
}
}
Continue reading...