public string QueryDeleteAll(BaseModele bm)
{
string query = $"delete {bm.GetType().Name}";
Console.WriteLine(query);
return(query);
}
public string QueryDelete(BaseModele bm)
{
string query = $"delete {bm.GetType().Name} where id = '{bm.Id}'";
Console.WriteLine(query);
return(query);
}
public string GetRefTable(BaseModele bm)
{
string nomtable = bm.GetType().Name;
if (nomtable.Length <= 4)
{
return(nomtable.ToUpper());
}
return(nomtable.Substring(0, 4).ToUpper());
}
public BaseModele FindById(BaseModele modele, string id)
{
List <BaseModele> bms = FindQuery($"select top 1 * from {modele.GetType().Name} where id='{id}'", modele);
if (bms.Count != 0)
{
return(bms[0]);
}
throw new Exception("aucune resultat");
}
public void Update(BaseModele bm, SqlConnection connection)
{
FieldInfo[] fieldInfos = GetFieldInfos(bm);
SqlCommand sqlm = new SqlCommand(QueryUpdate(bm), connection);
foreach (var field in fieldInfos)
{
Object o = field.GetValue(bm);
o = o ?? DBNull.Value;
sqlm.Parameters.Add(new SqlParameter(field.Name, o));
}
sqlm.ExecuteNonQuery();
sqlm.Dispose();
}
public string QueryList(BaseModele bm, string where)
{
if (string.IsNullOrEmpty(where))
{
where = "";
}
else
{
where = $"where {where}";
}
string query = $"select * from {bm.GetType().Name} {where}";
Console.WriteLine(query);
return(query);
}
public string QueryUpdate(BaseModele bm)
{
FieldInfo[] fieldInfos = GetFieldInfos(bm);
string valueColonne = "";
foreach (var field in fieldInfos)
{
valueColonne += $"{field.Name} = @{field.Name}, ";
}
valueColonne = valueColonne.Remove(valueColonne.Length - 2);
string query = $"update {bm.GetType().Name} set {valueColonne} where id = '{bm.Id}'";
Console.WriteLine(query);
return(query);
}
public void Save(BaseModele bm, SqlConnection conn)
{
bm.SetId(GetRefTable(bm), GetSeq(bm), conn);
SqlCommand cmd = new SqlCommand(QuerySave(bm), conn);
FieldInfo[] fields = GetFieldInfos(bm);
cmd.Parameters.Add(new SqlParameter("@id", bm.Id));
foreach (var field in fields)
{
Object o = field.GetValue(bm);
o = o ?? DBNull.Value;
cmd.Parameters.Add(new SqlParameter(field.Name, o));
}
cmd.ExecuteNonQuery();
cmd.Dispose();
}
public string QuerySave(BaseModele baseModele)
{
FieldInfo[] fields = GetFieldInfos(baseModele);
string nomColone = "";
string valueColone = "";
foreach (var field in fields)
{
nomColone += $"{field.Name}, ";
valueColone += $"@{field.Name}, ";
}
nomColone = nomColone.Remove(nomColone.Length - 2);
valueColone = valueColone.Remove(valueColone.Length - 2);
string query = $"insert into {baseModele.GetType().Name} (id, {nomColone}) values(@id, {valueColone})";
Console.WriteLine(query);
return(query);
}
public void Save(BaseModele bm)
{
SqlConnection connection = null;
Connexion conn = new Connexion();
try
{
connection = conn.GetConnection();
Save(bm, connection);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
finally
{
conn.Close(null, connection);
}
}
public void Delete(BaseModele bm)
{
SqlConnection scon = null;
Connexion c = new Connexion();
try
{
scon = c.GetConnection();
Delete(bm, scon);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
finally
{
c.Close(null, scon);
}
}
public List <BaseModele> FindQuery(string requette, BaseModele bm)
{
SqlConnection conn = null;
Connexion c = new Connexion();
try
{
conn = c.GetConnection();
return(FindQuery(requette, bm, conn));
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
finally
{
c.Close(null, conn);
}
}
public List <BaseModele> Find(BaseModele bm, string where)
{
SqlConnection conn = null;
Connexion c = new Connexion();
try
{
conn = c.GetConnection();
return(Find(bm, conn, where));
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
finally
{
c.Close(null, conn);
}
}
public List <BaseModele> FindQuery(string requette, BaseModele bm, SqlConnection conn)
{
Connexion c = new Connexion();
SqlDataReader sqd = null;
List <BaseModele> lmModeles = new List <BaseModele>();
Console.WriteLine(requette);
try
{
FieldInfo[] fields = GetFieldInfos(bm);
sqd = c.ResultSet(requette, conn);
while (sqd.Read())
{
BaseModele modele = (BaseModele)Activator.CreateInstance(bm.GetType());
modele.Id = sqd["id"].ToString();
foreach (var field in fields)
{
if (sqd[field.Name].GetType() != typeof(DBNull))
{
field.SetValue(modele, sqd[field.Name]);
}
}
lmModeles.Add(modele);
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
finally
{
c.Close(sqd, null);
}
return(lmModeles);
}
public FieldInfo[] GetFieldInfos(BaseModele bm)
{
return(bm.GetType().GetFields(BindingFlags.NonPublic | BindingFlags.Instance));
}
public string GetSeq(BaseModele bm)
{
return($"id{bm.GetType().Name}".ToLower());
}
public void Delete(BaseModele bm, SqlConnection conn)
{
Connexion c = new Connexion();
c.Execute(QueryDelete(bm), conn);
}
