public virtual void Salvar()
{
using (SqlConnection connection = new SqlConnection(
connectionString))
{
List <string> campos = new List <string>();
List <string> valores = new List <string>();
foreach (PropertyInfo pi in this.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
OpcoesBase pOpcoesBase = (OpcoesBase)pi.GetCustomAttribute(typeof(OpcoesBase));
if (pOpcoesBase != null && pOpcoesBase.UsarNoBancoDeDados && !pOpcoesBase.AutoIncrementar)
{
if (this.Key == 0)
{
if (!pOpcoesBase.ChavePrimaria)
{
campos.Add(pi.Name);
if (pi.PropertyType.Name == "Double")
{
valores.Add("'" + pi.GetValue(this).ToString().Replace(".", "").Replace(",", ".") + "'");
}
else
{
valores.Add("'" + pi.GetValue(this) + "'");
}
}
}
else
{
if (!pOpcoesBase.ChavePrimaria)
{
valores.Add(" " + pi.Name + " = '" + pi.GetValue(this) + "'");
}
}
}
}
string queryString = string.Empty;
if (this.Key == 0)
{
queryString = "insert into " + this.GetType().Name + "s (" + string.Join(", ", campos.ToArray()) + ")values(" + string.Join(", ", valores.ToArray()) + ");";
}
else
{
queryString = "update " + this.GetType().Name + "s set " + string.Join(", ", valores.ToArray()) + " where id = " + this.Key + ";";
}
SqlCommand command = new SqlCommand(queryString, connection);
command.Connection.Open();
command.ExecuteNonQuery();
}
}
private void setProperty(ref IBase obj, SqlDataReader reader)
{
foreach (PropertyInfo pi in obj.GetType().GetProperties(BindingFlags.Public))
{
OpcoesBase pOpcoesBase = (OpcoesBase)pi.GetCustomAttribute(typeof(OpcoesBase));
if (pOpcoesBase != null && pOpcoesBase.UsarNoBancoDeDados)
{
pi.SetValue(obj, reader[pi.Name].ToString());
}
}
}
public virtual bool Salvar()
{
bool confirmacao = false;
try
{
using (SqlConnection connection = new SqlConnection(
util.stringConexaoSql))
{
List <string> campos = new List <string>();
List <string> valores = new List <string>();
foreach (PropertyInfo pi in this.GetType().GetProperties()) //GetProperties(/*BindingFlags.Public*/) não consegui fazer pegar apenas as publics
{
OpcoesBase pOpcoesBase = (OpcoesBase)pi.GetCustomAttribute(typeof(OpcoesBase));
if (pOpcoesBase != null && pOpcoesBase.UsarNoBanco && !pOpcoesBase.AutoIncremento)
{
campos.Add(pi.Name);
if (!pOpcoesBase.Criptografado)
{
if (pi.PropertyType.Name.ToString().Equals("DateTime"))
{
valores.Add("'" + DateTime.Parse(pi.GetValue(this).ToString()).ToString("yyyy-MM-dd HH:mm:ss") + "'");
}
else
{
valores.Add("'" + pi.GetValue(this) + "'");
}
}
else
{
valores.Add("'" + util.criptografa(pi.GetValue(this).ToString(), pOpcoesBase.chaveCripto) + "'");
}
}
}
string queryString = "insert into " + this.GetType().Name + "s (" + string.Join(", ", campos.ToArray()) + ")values(" + string.Join(", ", valores.ToArray()) + ");";
SqlCommand command = new SqlCommand(queryString, connection);
command.Connection.Open();
command.ExecuteNonQuery();
confirmacao = true;
}
}
catch (Exception error)
{
FrmAlerta alerta = new FrmAlerta("Ocorreu um erro ao salvar " + this.GetType().Name + ". Mensagem de erro:" + error.Message, null);
alerta.ShowDialog();
confirmacao = false;
}
return(confirmacao);
}
public virtual List <IBase> Busca()
{
var list = new List <IBase>();
using (SqlConnection connection = new SqlConnection(
util.stringConexaoSql))
{
List <string> where = new List <string>();
string chavePrimaria = string.Empty;
foreach (PropertyInfo pi in this.GetType().GetProperties())
{
OpcoesBase pOpcoesBase = (OpcoesBase)pi.GetCustomAttribute(typeof(OpcoesBase));
if (pOpcoesBase != null || pOpcoesBase.ChavePrimaria)
{
if (pOpcoesBase.ChavePrimaria)
{
chavePrimaria = pi.Name;
}
if (pOpcoesBase.UsarNoBanco && pOpcoesBase.UsarParaBuscar)
{
if (pi.PropertyType.Name.ToString().Equals("DateTime"))
{
where.Add(pi.Name + " = '" + DateTime.Parse(pi.GetValue(this).ToString()).ToString("yyyy-MM-dd HH:mm:ss") + "'");
}
else if (pi.GetValue(this) != null)
{
where.Add(pi.Name + " = '" + pi.GetValue(this) + "'");
}
}
}
}
string queryString = "select * from " + this.GetType().Name + "s";
if (where.Count > 0)
{
queryString += " where " + string.Join(" and ", where.ToArray());
}
SqlCommand command = new SqlCommand(queryString, connection);
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
var obj = (IBase)Activator.CreateInstance(this.GetType());
setProperty(ref obj, reader);
list.Add(obj);
}
}
return(list);
}
public virtual List <IBase> Busca()
{
var list = new List <IBase>();
using (SqlConnection connection = new SqlConnection(
connectionString))
{
List <string> where = new List <string>();
string parametroBusca = string.Empty;
foreach (PropertyInfo pi in this.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
OpcoesBase pOpcoesBase = (OpcoesBase)pi.GetCustomAttribute(typeof(OpcoesBase));
if (pOpcoesBase != null)
{
if (pOpcoesBase.ChavePrimaria)
{
parametroBusca = pi.Name;
}
if (pOpcoesBase.UsarParaBuscar)
{
var valor = pi.GetValue(this);
if (valor != null)
{
where.Add(pi.Name + " = '" + valor + "'");
parametroBusca = pi.Name;
}
}
}
}
string queryString = "select * from " + this.GetType().Name + "s where " + parametroBusca + " is not null";
if (where.Count > 0)
{
queryString += " and " + string.Join(" and ", where.ToArray());
}
SqlCommand command = new SqlCommand(queryString, connection);
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
var obj = (IBase)Activator.CreateInstance(this.GetType());
setProperty(ref obj, reader);
list.Add(obj);
}
}
return(list);
}
public virtual bool delete()
{
bool confirmacao = false;
try
{
using (SqlConnection connection = new SqlConnection(
util.stringConexaoSql))
{
List <string> campos = new List <string>();
List <string> where = new List <string>();
foreach (PropertyInfo pi in this.GetType().GetProperties())
{
OpcoesBase pOpcoesBase = (OpcoesBase)pi.GetCustomAttribute(typeof(OpcoesBase));
if (pOpcoesBase != null && pOpcoesBase.UsarNoBanco && !pOpcoesBase.UsarParaBuscar)
{
if (pOpcoesBase.UsarNoBanco && pOpcoesBase.UsarParaBuscar)
{
if (pi.GetValue(this) != null)
{
where.Add(pi.Name + " = '" + pi.GetValue(this) + "'");
}
}
string queryString = "DELETE FROM " + this.GetType().Name + "s";
if (where.Count > 0)
{
queryString += " where " + string.Join(" and ", where.ToArray());
}
SqlCommand command = new SqlCommand(queryString, connection);
command.Connection.Open();
command.ExecuteNonQuery();
confirmacao = true;
}
}
}
}
catch (Exception error)
{
FrmAlerta alerta = new FrmAlerta("Ocorreu um erro ao salvar " + this.GetType().Name + ". Mensagem de erro:" + error.Message, null);
alerta.ShowDialog();
confirmacao = false;
}
return(confirmacao);
}
/// <summary>
/// Realiza busca
/// </summary>
/// <returns>Lista</returns>
public List <IBase> Buscar()
{
var list = new List <IBase>();
using (SqlConnection conn = new SqlConnection(connString))
{
List <string> valores = new List <string>();
string chavePrimaria = string.Empty;
foreach (PropertyInfo pi in this.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
OpcoesBase opcoesBase = (OpcoesBase)pi.GetCustomAttribute(typeof(OpcoesBase));
if (opcoesBase != null)
{
if (opcoesBase.ChavePrimaria)
{
chavePrimaria = pi.Name;
}
if (opcoesBase.UsarParaBuscar)
{
var valor = pi.GetValue(this);
if (valor != null)
{
valores.Add(string.Concat(pi.Name, " = '", valor, "'"));
}
}
}
}
string queryString = $"SELECT * FROM {this.GetType().Name}s WHERE {chavePrimaria} IS NOT NULL";
if (valores.Count > 0)
{
queryString = string.Concat(queryString, " and ", string.Join(" and ", valores.ToArray()));
}
SqlCommand cmd = new SqlCommand(queryString, conn);
cmd.Connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var obj = (IBase)Activator.CreateInstance(this.GetType());
SetProperty(ref obj, reader);
list.Add(obj);
}
return(list);
}
}
/// <summary>
/// Método que salva na base
/// </summary>
public virtual void Salvar()
{
using (SqlConnection conn = new SqlConnection(connString))
{
List <string> campos = new List <string>();
List <string> valores = new List <string>();
foreach (PropertyInfo pi in this.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
OpcoesBase opcoesBase = (OpcoesBase)pi.GetCustomAttribute(typeof(OpcoesBase));
if (opcoesBase != null && opcoesBase.UsarNoBancoDeDados && !opcoesBase.Identity)
{
if (Key == 0)
{
campos.Add(pi.Name);
valores.Add(string.Concat("'", pi.GetValue(this), "'"));
}
else
{
valores.Add(string.Concat(pi.Name, " = '", pi.GetValue(this), "'"));
}
}
}
string queryString = "";
if (Key == 0)
{
queryString = string.Concat("INSERT INTO ", this.GetType().Name, "s (", string.Join(",", campos.ToArray()), ") values(", string.Join(",", valores.ToArray()), ");");
}
else
{
queryString = string.Concat("UPDATE ", this.GetType().Name, "s SET ", string.Join(",", valores.ToArray()), " WHERE ID =", Key, ";");
}
SqlCommand cmd = new SqlCommand(queryString, conn);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
private void setProperty(ref IBase obj, SqlDataReader reader)
{
Util u = new Util();
foreach (PropertyInfo pi in obj.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
OpcoesBase pOpcoesBase = (OpcoesBase)pi.GetCustomAttribute(typeof(OpcoesBase));
if (pOpcoesBase != null && pOpcoesBase.UsarNoBanco && reader[pi.Name].ToString() != null)
{
if (!pOpcoesBase.Criptografado)
{
pi.SetValue(obj, reader[pi.Name]);
}
else
{
pi.SetValue(obj, u.descriptografa(reader[pi.Name].ToString(), pOpcoesBase.chaveCripto));
}
}
}
}
public virtual string max(String campo)
{
String _return = null;
using (SqlConnection connection = new SqlConnection(util.stringConexaoSql))
{
List <string> where = new List <string>();
foreach (PropertyInfo pi in this.GetType().GetProperties())
{
OpcoesBase pOpcoesBase = (OpcoesBase)pi.GetCustomAttribute(typeof(OpcoesBase));
if (pOpcoesBase != null)
{
if (pOpcoesBase.UsarNoBanco && pOpcoesBase.UsarParaBuscar)
{
var valor = pi.GetValue(this);
if (valor != null && !pOpcoesBase.ChavePrimaria)
{
where.Add(pi.Name + " = '" + valor + "'");
}
}
}
}
string queryString = "select isnull(max(" + campo + "),0) from " + this.GetType().Name + "s where " + campo + " is not null";
if (where.Count > 0)
{
queryString += " and " + string.Join(" and ", where.ToArray());
}
SqlCommand command = new SqlCommand(queryString, connection);
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.Read() && reader.GetValue(0) != null)
{
_return = reader.GetInt32(0).ToString();
}
}
return(_return != null ? _return : "0");
}
public virtual void CriarTabela()
{
using (SqlConnection connection = new SqlConnection(
connectionString))
{
string chavePrimaria = "";
List <string> campos = new List <string>();
foreach (PropertyInfo pi in this.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
OpcoesBase pOpcoesBase = (OpcoesBase)pi.GetCustomAttribute(typeof(OpcoesBase));
if (pOpcoesBase != null && pOpcoesBase.UsarNoBancoDeDados && !pOpcoesBase.AutoIncrementar)
{
if (pOpcoesBase.ChavePrimaria)
{
chavePrimaria = pi.Name + " int identity, ";
}
else
{
campos.Add(pi.Name + " " + tipoPropriedade(pi) + " ");
}
}
}
string tabelaExiste = "IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[" + this.GetType().Name + "s]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)" +
"DROP TABLE " + this.GetType().Name + "s";
SqlCommand command = new SqlCommand(tabelaExiste, connection);
command.Connection.Open();
command.ExecuteNonQuery();
string queryString = "CREATE TABLE " + this.GetType().Name + "s (";
queryString += chavePrimaria;
queryString += string.Join(",", campos.ToArray());
queryString += "); ";
command = new SqlCommand(queryString, connection);
command.ExecuteNonQuery();
}
}
/// <summary>
/// Criar tabela
/// </summary>
public void CriarTabela()
{
using (SqlConnection conn = new SqlConnection(connString))
{
string chavePrimaria = "";
List <string> campos = new List <string>();
foreach (PropertyInfo pi in this.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
OpcoesBase opcoesBase = (OpcoesBase)pi.GetCustomAttribute(typeof(OpcoesBase));
if (opcoesBase != null && opcoesBase.UsarNoBancoDeDados)
{
if (opcoesBase.ChavePrimaria)
{
chavePrimaria = pi.Name + " int identity, ";
}
else
{
campos.Add(string.Concat(pi.Name, " " + TipoPropriedade(pi)));
}
}
}
string tabelaExist = "IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'[dbo].[" + this.GetType().Name + "s]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)"
+ "DROP TABLE " + this.GetType().Name + "s;";
SqlCommand cmd = new SqlCommand(tabelaExist, conn);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
string queryString = $"CREATE TABLE {this.GetType().Name}s (";
queryString += chavePrimaria;
queryString += string.Join(", ", campos.ToArray()) + ");";
SqlCommand cmdTable = new SqlCommand(queryString, conn);
cmdTable.Connection.Open();
cmdTable.ExecuteNonQuery();
cmdTable.Connection.Close();
}
}
public virtual void Salvar()
{
using (SqlConnection connection = new SqlConnection(
connectionString))
{
List <string> campos = new List <string>();
List <string> valores = new List <string>();
foreach (PropertyInfo pi in this.GetType().GetProperties(BindingFlags.Public))
{
OpcoesBase pOpcoesBase = (OpcoesBase)pi.GetCustomAttribute(typeof(OpcoesBase));
if (pOpcoesBase != null && pOpcoesBase.UsarNoBancoDeDados)
{
campos.Add(pi.Name);
valores.Add("'" + pi.GetValue(this) + "'");
}
}
string queryString = "insert into " + this.GetType().Name + "s (" + string.Join(", ", campos.ToArray()) + ")values(" + string.Join(", ", valores.ToArray()) + ");";
SqlCommand command = new SqlCommand(queryString, connection);
command.Connection.Open();
command.ExecuteNonQuery();
}
}
public virtual bool update()
{
bool confirmacao = false;
try
{
using (SqlConnection connection = new SqlConnection(
util.stringConexaoSql))
{
List <string> campos = new List <string>();
List <string> where = new List <string>();
foreach (PropertyInfo pi in this.GetType().GetProperties())
{
OpcoesBase pOpcoesBase = (OpcoesBase)pi.GetCustomAttribute(typeof(OpcoesBase));
if (pOpcoesBase != null && pOpcoesBase.UsarNoBanco && !pOpcoesBase.UsarParaBuscar)
{
if (!pOpcoesBase.Criptografado)
{
if (pi.PropertyType.Name.ToString().Equals("DateTime"))
{
campos.Add(pi.Name + " = '" + DateTime.Parse(pi.GetValue(this).ToString()).ToString("yyyy-MM-dd HH:mm:ss") + "'");
}
else
{
campos.Add(pi.Name + " = '" + pi.GetValue(this) + "'");
}
}
else
{
campos.Add(pi.Name + " = '" + util.criptografa(pi.GetValue(this).ToString(), pOpcoesBase.chaveCripto) + "'");
}
}
if (pOpcoesBase.UsarNoBanco && pOpcoesBase.UsarParaBuscar)
{
var valor = pi.GetValue(this);
if (valor != null)
{
where.Add(pi.Name + " = '" + valor + "'");
}
}
}
string queryString = "update " + this.GetType().Name + "s set " + string.Join(", ", campos.ToArray());
if (where.Count > 0)
{
queryString += " where " + string.Join(" and ", where.ToArray());
}
SqlCommand command = new SqlCommand(queryString, connection);
command.Connection.Open();
command.ExecuteNonQuery();
confirmacao = true;
}
}
catch (Exception error)
{
FrmAlerta alerta = new FrmAlerta("Ocorreu um erro ao salvar " + this.GetType().Name + ". Mensagem de erro:" + error.Message, null);
alerta.ShowDialog();
confirmacao = false;
}
return(confirmacao);
}