public static void Update(T entidade)
{
StringBuilder consulta = new StringBuilder();
consulta.Append($"UPDATE {GetTableName()} SET ");
List <SQLiteParameter> parametros = new List <SQLiteParameter>();
foreach (var e in GetColumns())
{
if (e.GetValue(entidade) != null)
{
consulta.Append($"{e.Name.ToLower()} = @{e.Name.ToLower()},");
parametros.Add(new SQLiteParameter($"@{e.Name.ToLower()}", e.GetValue(entidade)));
}
}
consulta.Remove(consulta.Length - 1, 1).Append($" WHERE Id = {entidade.Id}");
using (SQLiteConnection conexao = DbSqLite.GetInstance().GetConnection())
{
conexao.Open();
using (SQLiteTransaction transacao = conexao.BeginTransaction())
using (SQLiteCommand command = new SQLiteCommand())
{
command.Parameters.AddRange(parametros.ToArray());
command.Connection = conexao;
command.Transaction = transacao;
command.CommandText = consulta.ToString();
command.ExecuteNonQuery();
transacao.Commit();
}
}
}
public static void Delete(int id, string key = "")
{
using (SQLiteConnection conexao = DbSqLite.GetInstance().GetConnection())
{
string aux = "";
if (key == "")
{
aux = "WHERE Id";
}
else
{
aux = $"WHERE {key}";
}
conexao.Open();
using (SQLiteTransaction transacao = conexao.BeginTransaction())
using (SQLiteCommand command = new SQLiteCommand())
{
command.Connection = conexao;
command.Transaction = transacao;
command.CommandText = $"DELETE FROM {GetTableName()} {aux} = {id} ";
command.ExecuteNonQuery();
transacao.Commit();
}
}
}
public static int GetNextId()
{
///var generator = typeof(T).GetCustomAttribute<GeneratorAttribute>().Generator;
using (SQLiteConnection connection = DbSqLite.GetInstance().GetConnection())
{
connection.Open();
using (SQLiteCommand cmd = new SQLiteCommand($"SELECT max(id) from {GetTableName()}", connection))
{
var reader = cmd.ExecuteScalar();
return(Convert.IsDBNull(reader) ? 1 : Convert.ToInt32(reader) + 1);
}
}
}
public static DataTable GetDataTable(int id = 0)
{
string sql = $"Select * from {GetTableName()}";
if (id != 0)
{
sql += $" WHERE Id = {id}";
}
using (SQLiteConnection connection = DbSqLite.GetInstance().GetConnection())
{
connection.Open();
SQLiteDataAdapter fbData = new SQLiteDataAdapter(sql, connection);
DataTable dataTable = new DataTable();
fbData.Fill(dataTable);
return(dataTable);
}
}
public static List <T> Select(int id = 0)
{
string sql = $"Select * from {GetTableName()}";
if (id != 0)
{
sql += $" WHERE Id = {id}";
}
var colunas = typeof(T).GetProperties();//.Where(p => !p.IsDefined(typeof(WritableAttribute)));
List <T> lista = new List <T>();
using (SQLiteConnection conexao = DbSqLite.GetInstance().GetConnection())
{
conexao.Open();
using (SQLiteCommand command = new SQLiteCommand())
{
command.Connection = conexao;
command.CommandText = sql;
var reader = command.ExecuteReader();
IDictionary <string, object> dictionary = null;
while (reader.Read())
{
dynamic person = new ExpandoObject();
dictionary = (IDictionary <string, object>)person;
foreach (var c in colunas)
{
dictionary.Add(c.Name, reader[c.Name]);
}
lista.Add(Conversor.ConvertDictionaryTo <T>(dictionary));
}
}
return(lista);
}
}
public static int Insert(T entidade)
{
string nomeTabela = typeof(T).GetCustomAttribute <TableAttribute>().Nome;
StringBuilder sql = new StringBuilder();
List <SQLiteParameter> parametros = new List <SQLiteParameter>();
foreach (var c in GetColumns())
{
string auxiliar = string.Empty;
if (c.GetValue(entidade) != null)
{
auxiliar = $"@{c.Name.ToUpper()},";
sql.Append(auxiliar);
parametros.Add(new SQLiteParameter(auxiliar.Replace(",", ""), c.GetValue(entidade)));
}
}
var campos = sql.ToString().Substring(0, sql.Length - 1);
var sqlInsert = $"INSERT INTO {nomeTabela} ({campos.Replace("@", "")}) VALUES ({campos})";
using (SQLiteConnection conexao = DbSqLite.GetInstance().GetConnection())
{
conexao.Open();
using (SQLiteTransaction transacao = conexao.BeginTransaction())
using (SQLiteCommand command = new SQLiteCommand())
{
command.Parameters.AddRange(parametros.ToArray());
command.Connection = conexao;
command.Transaction = transacao;
command.CommandText = sqlInsert;
var id = Convert.ToInt32(command.ExecuteScalar());
transacao.Commit();
return(id);
}
}
}