// Insert
public int insert(string connectionString, Reliquat reliquat, StreamWriter writer)
{
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
try
{
if (writer != null)
{
writer.WriteLine("");
}
if (writer != null)
{
writer.WriteLine(DateTime.Now + " :: ReliquatManager => insert() | Creation d'une instance");
}
int x = -9;
conn.Open();
if (conn.State == System.Data.ConnectionState.Open)
{
string sql = @"INSERT INTO " + TABLE_NAME + " ('" + COLONNE_ID + "', '" + COLONNE_Date + "', '" + COLONNE_Modification + "', '" + COLONNE_DO_PIECE + "', '" + COLONNE_DO_REF + "', '" + COLONNE_DL_Ligne + "', " +
"'" + COLONNE_DL_Design + "', '" + COLONNE_DL_Qte + "', '" + COLONNE_DL_QtePL + "', '" + COLONNE_AR_Ref + "', '" + COLONNE_AR_CODEBARRE + "') " +
"VALUES(" + reliquat.id + ",'" + reliquat.date + "','" + reliquat.modification + "','" + reliquat.do_piece + "','" + reliquat.do_ref + "','" + reliquat.dl_ligne + "','" + reliquat.dl_design + "','" + reliquat.dl_qte + "','" + reliquat.dl_qtepl + "'" +
",'" + reliquat.ar_ref + "','" + reliquat.ar_codebarre + "')";
if (writer != null)
{
writer.WriteLine("SQL: " + sql);
}
SQLiteCommand command = new SQLiteCommand(sql, conn);
x = command.ExecuteNonQuery();
//Console.WriteLine("Table created");
}
conn.Close();
return(x);
}
catch (Exception ex)
{
Console.WriteLine("\nReliquat Insert [ERROR]");
Console.WriteLine("Message : " + ex.Message);
Console.WriteLine("");
Console.WriteLine("\nStackTrace : " + ex.StackTrace);
if (writer != null)
{
writer.WriteLine("Reliquat Insert [ERROR]");
}
if (writer != null)
{
writer.WriteLine("Message : " + ex.Message);
}
if (writer != null)
{
writer.WriteLine("");
}
if (writer != null)
{
writer.WriteLine("StackTrace : " + ex.StackTrace);
}
if (writer != null)
{
writer.Flush();
}
conn.Close();
return(-99);
}
}
}
// Update
public int update(string connectionString, StreamWriter writer, Reliquat reliquat)
{
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
try
{
if (writer != null)
{
writer.WriteLine("");
}
if (writer != null)
{
writer.WriteLine(DateTime.Now + " :: ReliquatManager => update() | Creation d'une instance");
}
int x = -9;
conn.Open();
if (conn.State == System.Data.ConnectionState.Open)
{
string sql = @"UPDATE " + TABLE_NAME + " SET " +
"" + COLONNE_Modification + " = '" + reliquat.modification + "', " +
"" + COLONNE_DL_Ligne + " = '" + reliquat.dl_ligne + "', " +
"" + COLONNE_DL_Qte + " = '" + reliquat.dl_qte + "', " +
"" + COLONNE_DL_QtePL + " = '" + reliquat.dl_qtepl + "' " +
"WHERE " + COLONNE_DO_PIECE + " = " + reliquat.do_piece + " AND " + COLONNE_DL_Ligne + " = " + reliquat.dl_ligne + " AND " + COLONNE_AR_CODEBARRE + " = " + reliquat.ar_codebarre + " ";
if (writer != null)
{
writer.WriteLine("SQL: " + sql);
}
SQLiteCommand command = new SQLiteCommand(sql, conn);
x = command.ExecuteNonQuery();
Console.WriteLine("In do_piece " + reliquat.do_piece + " the article " + reliquat.ar_codebarre + " on line " + reliquat.dl_ligne + ", is Updated !");
}
conn.Close();
return(x);
}
catch (Exception ex)
{
Console.WriteLine("\n Reliquat Update [ERROR]");
Console.WriteLine(ex.Message);
conn.Close();
if (writer != null)
{
writer.WriteLine("Reliquat Update [ERROR]");
}
if (writer != null)
{
writer.WriteLine("Message : " + ex.Message);
}
if (writer != null)
{
writer.WriteLine("");
}
if (writer != null)
{
writer.WriteLine("StackTrace : " + ex.StackTrace);
}
if (writer != null)
{
writer.Flush();
}
return(-99);
}
}
}
// delete a row
public bool deleteById(string connectionString, StreamWriter writer, Reliquat reliquat)
{
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
try
{
if (writer != null)
{
writer.WriteLine("");
}
if (writer != null)
{
writer.WriteLine(DateTime.Now + " :: ReliquatManager => deleteById() | Creation d'une instance");
}
conn.Open();
if (conn.State == System.Data.ConnectionState.Open)
{
string sql = @"DELETE FROM " + TABLE_NAME + " WHERE " + COLONNE_DO_PIECE + " = " + reliquat.do_piece + " AND " + COLONNE_DL_Ligne + " = " + reliquat.dl_ligne + " AND " + COLONNE_AR_CODEBARRE + " = " + reliquat.ar_codebarre + " ";
if (writer != null)
{
writer.WriteLine("SQL: " + sql);
}
using (IDbConnection db = conn)
{
SQLiteCommand command = new SQLiteCommand(sql, conn);
command.ExecuteNonQuery();
Console.WriteLine(" data have been deleted from " + TABLE_NAME + " !");
conn.Close();
return(true);
}
}
conn.Close();
return(false);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
conn.Close();
if (writer != null)
{
writer.WriteLine("Reliquat Delete by id [ERROR]");
}
if (writer != null)
{
writer.WriteLine("Message : " + ex.Message);
}
if (writer != null)
{
writer.WriteLine("");
}
if (writer != null)
{
writer.WriteLine("StackTrace : " + ex.StackTrace);
}
if (writer != null)
{
writer.Flush();
}
return(false);
}
}
}
// Get by ID
public Reliquat getById(string connectionString, StreamWriter writer, string do_piece, string dl_ligne, string ar_codebarre)
{
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
try
{
if (writer != null)
{
writer.WriteLine("");
}
if (writer != null)
{
writer.WriteLine(DateTime.Now + " :: ReliquatManager => getById() | Creation d'une instance");
}
Reliquat list = null;
conn.Open();
if (conn.State == System.Data.ConnectionState.Open)
{
string sql = "SELECT * FROM " + TABLE_NAME + " WHERE " + COLONNE_DO_PIECE + " = '" + do_piece + "' AND " + COLONNE_DL_Ligne + " = " + dl_ligne + " AND " + COLONNE_AR_CODEBARRE + " = '" + ar_codebarre + "'";
if (writer != null)
{
writer.WriteLine("SQL: " + sql);
}
using (IDbConnection db = conn)
{
var xxx = conn.Query <Reliquat>(sql);
if (xxx.ToList() == null || xxx.ToList().Count == 0)
{
conn.Close();
return(null);
}
list = xxx.ToList()[0];
Console.WriteLine("Reliquat list size : 1");
}
}
//conn.Close();
return(list);
}
catch (Exception ex)
{
Console.WriteLine("\nGET Reliquat [ERROR]");
Console.WriteLine(ex.Message);
//conn.Close();
if (writer != null)
{
writer.WriteLine("Reliquat Get [ERROR]");
}
if (writer != null)
{
writer.WriteLine("Message : " + ex.Message);
}
if (writer != null)
{
writer.WriteLine("");
}
if (writer != null)
{
writer.WriteLine("StackTrace : " + ex.StackTrace);
}
if (writer != null)
{
writer.Flush();
}
return(null);
}
}
}