public virtual int Delete(PL_DepartmentMaster prp)
{
MyConnection Mycon = new MyConnection();
Mycon.cmd.CommandText = "[USP_M_DEPARTMENT_MASTER]";
Mycon.cmd.CommandType = CommandType.StoredProcedure;
Mycon.cmd.Parameters.AddWithValue("@spType", prp.sptype);
Mycon.cmd.Parameters.AddWithValue("@TableID", prp.TableID);
SqlParameter p2 = new SqlParameter("@msg", SqlDbType.VarChar, 200);
p2.Direction = ParameterDirection.Output;
Mycon.cmd.Parameters.Add(p2);
try
{
Mycon.Open();
int retvalue = Mycon.cmd.ExecuteNonQuery();
Mycon.Close();
prp.msg = Mycon.cmd.Parameters["@msg"].Value.ToString();
return(retvalue);
}
catch (Exception ex)
{
prp.msg = ex.Message.ToString();
return(0);
}
finally
{
if (Mycon.Mycon.State == ConnectionState.Open)
{
Mycon.Close();
}
Mycon.cmd.Dispose();
}
}
public int Insert(PL_TeporaryDuty pl, string xmlDoc)
{
con.cmd.CommandText = "USP_Temporary_Duty";
con.cmd.CommandType = CommandType.StoredProcedure;
con.cmd.Parameters.AddWithValue("@sptype", pl.sptype);
con.cmd.Parameters.AddWithValue("@ECODE", pl.ECODE);
con.cmd.Parameters.AddWithValue("@EMP_NAME", pl.EMP_NAME);
con.cmd.Parameters.AddWithValue("@DoB", pl.DoB);
con.cmd.Parameters.AddWithValue("@Levels", pl.Levels);
con.cmd.Parameters.AddWithValue("@Cells", pl.Cells);
con.cmd.Parameters.AddWithValue("@DateTime_Of_Start", pl.DateTime_Of_Start);
con.cmd.Parameters.AddWithValue("@Gpf_Pran_No", pl.Gpf_Pran_No);
con.cmd.Parameters.AddWithValue("@BasicPay", pl.BasicPay);
con.cmd.Parameters.AddWithValue("@Ranks", pl.Ranks);
con.cmd.Parameters.AddWithValue("@HeadqtrOffice", pl.HeadqtrOffice);
con.cmd.Parameters.AddWithValue("@OrderForMove_Duty", pl.OrderForMove_Duty);
con.cmd.Parameters.AddWithValue("@AuthorityRule_TR_SR", pl.AuthorityRule_TR_SR);
con.cmd.Parameters.AddWithValue("@StationFrom_Journey_commenced", pl.StationFrom_Journey_commenced);
con.cmd.Parameters.AddWithValue("@EmpType", pl.EmpType);
con.cmd.Parameters.AddWithValue("@fin_year", pl.fin_year);
con.cmd.Parameters.AddWithValue("@GPS_NPS", pl.GPS_NPS);
con.cmd.Parameters.AddWithValue("@TableID", pl.TableID);
con.cmd.Parameters.AddWithValue("@freeze_flage", pl.freeze_flage);
con.cmd.Parameters.AddWithValue("@refrence_no", pl.refrence_no);
con.cmd.Parameters.AddWithValue("@CREATEDBY", pl.CreatedBy);
con.cmd.Parameters.AddWithValue("@xmlDoc", xmlDoc);
SqlParameter p2 = new SqlParameter("@P_MSG", SqlDbType.VarChar, 200);
p2.Direction = ParameterDirection.Output;
con.cmd.Parameters.Add(p2);
try
{
con.Open();
int RetValue = con.cmd.ExecuteNonQuery();
pl.MSG = con.cmd.Parameters["@P_MSG"].Value.ToString();
return(RetValue);
}
catch (Exception ex)
{
// pl.MSG = con.cmd.Parameters["@P_MSG"].Value.ToString();
con.Close();
return(0);
}
finally
{
con.Close();
con.cmd.Dispose();
}
}
public DataTable Bindgrid(PL_Designation pl)
{
MyConnection con = new MyConnection();
DataTable dt = new DataTable();
con.Open();
con.cmd.CommandText = "Sp_Designation";
con.cmd.CommandType = CommandType.StoredProcedure;
con.cmd.Parameters.Add("@ParentID", pl.ParentID);
con.cmd.Parameters.Add("@sptype", pl.sptype);
SqlParameter p2 = new SqlParameter("@msg", SqlDbType.VarChar, 2000);
p2.Direction = ParameterDirection.Output;
con.cmd.Parameters.Add(p2);
SqlParameter p1 = new SqlParameter("@ID", SqlDbType.Int);
p1.Direction = ParameterDirection.InputOutput;
p1.Value = pl.TableID;
con.cmd.Parameters.Add(p1);
SqlDataReader sdr;
sdr = con.cmd.ExecuteReader();
dt.Load(sdr);
con.Close();
return(dt);
}
//Adds a new file.
public void AddFile(File file)
{
bool isConnOpen = false;
string sql = "INSERT INTO userfiles (file_owner, file_title, link) VALUES(@fileOwner, @fileTitle, @link)";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("@fileOwner", file.FileOwner);
cmd.Parameters.AddWithValue("@fileTitle", file.FileTitle);
cmd.Parameters.AddWithValue("@link", file.Link);
if (MyConnection.State == System.Data.ConnectionState.Closed)
{
MyConnection.Open();
isConnOpen = true;
MyTransaction = MyConnection.BeginTransaction();
}
if (MyTransaction != null)
{
//used to participate in an opened trasaction happening somewhere else
//assign the Transaction property to the opened transaction
cmd.Transaction = MyTransaction;
}
cmd.ExecuteNonQuery();
MyTransaction.Commit();
if (isConnOpen)
{
MyConnection.Close();
isConnOpen = false;
}
}
public List <Product> GetProducts(string email)
{
string sql = "Select Id, Name, Price, Ownerfk from products where ownerfk=@email";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("@email", email);
MyConnection.Open();
List <Product> results = new List <Product>();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Product p = new Product();
p.Id = reader.GetInt32(0);
p.Name = reader.GetString(1);
p.Price = reader.GetDouble(2);
p.OwnerFk = reader.GetString(3);
results.Add(p);
}
}
MyConnection.Close();
return(results);
}
public List <Product> GetProducts()
{
string sql = "Select Id, Name, File, Ownerfk, Shareuser from products ";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
MyConnection.Open();
List <Product> results = new List <Product>();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Product p = new Product();
p.Id = reader.GetInt32(0);
p.Name = reader.GetString(1);
p.File = reader.GetString(2);
p.OwnerFK = reader.GetString(3);
p.Shareuser = reader.GetString(4);
results.Add(p);
}
}
MyConnection.Close();
return(results);
}
public void DeleteProduct(int id)
{
string sql = "Delete from products where Id = @id";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("@id", id);
bool connectionOpenedInThisMethod = false;
if (MyConnection.State == System.Data.ConnectionState.Closed)
{
MyConnection.Open();
connectionOpenedInThisMethod = true;
}
if (MyTransaction != null)
{
cmd.Transaction = MyTransaction; //to participate in the opened trasaction (somewhere else), assign the Transaction property to the opened transaction
}
cmd.ExecuteNonQuery();
if (connectionOpenedInThisMethod == true)
{
MyConnection.Close();
}
}
public File GetFile(int id)
{
string sql = "Select Id, Name, Description, Ownerfk, Link from files where Id=@id";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("@id", id);
MyConnection.Open();
File file = new File();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
file.Id = reader.GetInt32(0);
file.Name = reader.GetString(1);
file.Description = reader.GetString(2);
file.OwnerFk = reader.GetString(3);
file.Link = reader.GetString(4);
}
}
MyConnection.Close();
return(file);
}
public List <File> GetFiles(string email)
{
string sql = "Select Id, Name, Description, Ownerfk from files where ownerfk=@email";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("@email", email);
MyConnection.Open();
List <File> results = new List <File>();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
File f = new File();
f.Id = reader.GetInt32(0);
f.Name = reader.GetString(1);
f.Description = reader.GetString(2);
f.OwnerFk = reader.GetString(3);
results.Add(f);
}
}
MyConnection.Close();
return(results);
}
public async Task <T> SingleOrDefaultAsync()
{
try
{
if (MyConnection.State == ConnectionState.Closed)
{
MyConnection.Open();
}
using (var command = MyConnection.CreateCommand())
{
command.CommandText = SqlStr;
if (MyParameters != null && MyParameters.Length > 0)
{
command.Parameters.AddRange(MyParameters);
}
return((T)await command.ExecuteScalarAsync().ConfigureAwait(false));
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
if (MyConnection.State == ConnectionState.Open)
{
MyConnection.Close();
}
}
}
public string getDBMessages()
{
try
{
if (MyConnection.State == System.Data.ConnectionState.Open)
{
MyConnection.Close();
}
MyConnection.Open();
OdbcCommand cmd = new OdbcCommand("Select message from messages where name=?", MyConnection);
cmd.Parameters.Add("@email", OdbcType.VarChar, 255).Value = "human";
OdbcDataReader dr = cmd.ExecuteReader();
ArrayList values = new ArrayList();
while (dr.Read())
{
string messagev = dr[0].ToString();
}
MyConnection.Close();
return messagev;
}
catch (Exception ex)
{
return ex.Message;
}
}
//Get files of a specified email from db
public List <File> GetFiles(string email)
{
string sql = "Select * from userfiles where file_owner = @email";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("@email", email);
List <File> outputList = new List <File>();
MyConnection.Open();
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
outputList.Add(new File()
{
Id = reader.GetInt32(0),
FileOwner = reader.GetString(1),
FileTitle = reader.GetString(2),
Link = reader.GetString(3)
});
}
}
MyConnection.Close();
return(outputList);
}
public DataTable bindDDL(string name)
{
MyConnection con = new MyConnection();
con.Open();
con.cmd.CommandText = "[Sp_Designation]";
con.cmd.CommandType = CommandType.StoredProcedure;
con.cmd.Parameters.Add("@Name", SqlDbType.VarChar, 100).Value = name;
SqlParameter p2 = new SqlParameter("@msg", SqlDbType.VarChar, 2000);
p2.Direction = ParameterDirection.Output;
con.cmd.Parameters.Add(p2);
con.cmd.Parameters.Add("@ID", SqlDbType.VarChar, 100).Value = 0;
con.cmd.Parameters.Add("@Sptype", SqlDbType.Int).Value = 4;
DataTable dt = new DataTable();
SqlDataReader sdr;
sdr = con.cmd.ExecuteReader();
dt.Load(sdr);
con.Close();
return(dt);
}
public List <Groupe> FindByGroup(Groupe g)
{
string Requete = "Select * from Groupes ";
if (g.Code != "" || g.Nom != "" || g.Description != "" || g.Filiere.Titre != "")
{
Requete += " where ";
}
bool and = false;
if (g.Code != "")
{
Requete += " Code like '%" + g.Code + "%'";
and = true;
}
if (g.Nom != "")
{
if (and)
{
Requete += " and ";
}
Requete += " Nom like '%" + g.Nom + "%'";
and = true;
}
if (g.Description != "")
{
if (and)
{
Requete += " and ";
}
Requete += " Description like '%" + g.Description + "%'";
and = true;
}
if (g.Filiere.Titre != "")
{
if (and)
{
Requete += " and ";
}
Requete += " IdFiliere like '%" + g.Filiere.Id + "%'";
and = true;
}
List <Groupe> ListGroupe = new List <Groupe> ();
OleDbDataReader read = MyConnection.ExecuteReader(Requete);
while (read.Read())
{
Groupe gr = new Groupe();
gr.Id = read.GetInt32(0);
gr.Nom = read.GetString(1);
gr.Code = read.GetString(2);
gr.Description = read.GetString(3);
gr.Filiere = new FiliereDAO().FindById(read.GetInt32(4));
ListGroupe.Add(gr);
}
MyConnection.Close();
return(ListGroupe);
}
public virtual int insert(PL_DepartmentMaster prp)
{
MyConnection Mycon = new MyConnection();
Mycon.cmd.CommandText = "[USP_M_DEPARTMENT_MASTER]";
Mycon.cmd.CommandType = CommandType.StoredProcedure;
Mycon.cmd.Parameters.AddWithValue("@DepartmentCode", prp.DepartmentCode);
Mycon.cmd.Parameters.AddWithValue("@sptype", prp.sptype);
Mycon.cmd.Parameters.AddWithValue("@DepartmentName", prp.DepartmentName);
Mycon.cmd.Parameters.AddWithValue("@PayBillSignAuth", prp.PayBillSighAuth);
Mycon.cmd.Parameters.AddWithValue("@PayBillSighAuthUpload", prp.PayBillSighAuthUpload);
Mycon.cmd.Parameters.AddWithValue("@CreatedBy", prp.CreatedBy);
SqlParameter p1 = new SqlParameter("@TableID", SqlDbType.VarChar, 10);
p1.Direction = ParameterDirection.InputOutput;
p1.Value = prp.TableID;
Mycon.cmd.Parameters.Add(p1);
SqlParameter p2 = new SqlParameter("@msg", SqlDbType.VarChar, 200);
p2.Direction = ParameterDirection.Output;
Mycon.cmd.Parameters.Add(p2);
try
{
Mycon.Open();
int RetValue = Mycon.cmd.ExecuteNonQuery();
Mycon.Close();
prp.msg = Mycon.cmd.Parameters["@msg"].Value.ToString();
return(RetValue);
}
catch (Exception ex)
{
prp.msg = ex.Message.ToString();
return(0);
}
finally
{
if (Mycon.Mycon.State == ConnectionState.Open)
{
Mycon.Close();
}
Mycon.cmd.Dispose();
}
}
public virtual int Insert(PL_Designation pl)
{
MyConnection con = new MyConnection();
con.cmd.CommandText = "[Sp_Designation]";
con.cmd.CommandType = CommandType.StoredProcedure;
con.cmd.Parameters.Add("@HName", pl.HName);
con.cmd.Parameters.Add("@SHName", pl.SHName);
con.cmd.Parameters.Add("@Name", pl.Name);
con.cmd.Parameters.Add("@sptype", pl.sptype);
con.cmd.Parameters.Add("@ParentID", pl.ParentID);
con.cmd.Parameters.Add("@TreeLevel", pl.TreeLevel);
con.cmd.Parameters.Add("@ParentDesignationID", pl.MyList);
con.cmd.Parameters.Add("@DesignationID", pl.TableID);
SqlParameter p1 = new SqlParameter("@ID", SqlDbType.Int);
p1.Value = pl.TableID;
p1.Direction = ParameterDirection.InputOutput;
con.cmd.Parameters.Add(p1);
SqlParameter p2 = new SqlParameter("@msg", SqlDbType.VarChar, 200);
p2.Direction = ParameterDirection.Output;
con.cmd.Parameters.Add(p2);
try
{
con.Open();
int RetValue = con.cmd.ExecuteNonQuery();
con.Close();
pl.msg = con.cmd.Parameters["@msg"].Value.ToString();
return(RetValue);
}
catch (Exception ex)
{
pl.msg = ex.Message;
return(0);
}
finally
{
if (con.Mycon.State == ConnectionState.Open)
{
con.Close();
}
con.cmd.Dispose();
}
}
public void changeoperator(string AirlinePrefix, string MAWBNumber, string operatorname, StreamWriter strWriter)
{
try
{
System.Data.OleDb.OleDbConnection MyConnection = null;
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
if (Trackername.Contains("NEW"))
{
//new tracker
MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes; DATABASE=http://klsstg03/pingSD/Support/;LIST={DEFD5BEC-D6A2-400E-9F98-8F6E653A2CE9};VIEW={CC4F76AA-FA1B-44D7-9611-665077F0E451};"); //tracker new
MyConnection.Open();
}
else if (Trackername.Contains("OLD"))
{
//old tracker
MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes; DATABASE=http://klsstg03/pingSD/;LIST={5D0E59F0-E498-4EF0-BBC1-C2FCF7C9150F};VIEW={87B39BBA-9465-4B2E-87D1-FE88E735EEA6};"); //tracker old
MyConnection.Open();
}
myCommand.Connection = MyConnection;
string DateStamp = System.DateTime.Now.ToString("dd/MM/yyyy H:mm");
string sql = "Update [Table1] set Operator='" + operatorname + "' where Pfx='" + AirlinePrefix + "' AND AWB_No='" + MAWBNumber + "' AND Shipment_Status='2 - Ack'";
//, PDF_Sent_At = '" +DateStamp + "'
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();
sql = "";
MyConnection.Close();
MyConnection.Dispose();
Console.WriteLine("Status updated successfully for AWB " + MAWBNumber + ".\n");
Console.WriteLine("Status updated successfully for AWB " + MAWBNumber + ".\n");
strWriter.WriteLine(AirlinePrefix + "-" + MAWBNumber + " status Updated successfully as '4 - Updated in PING'");
strWriter.WriteLine(" ");
strWriter.WriteLine("-------------------------------------------------------");
strWriter.WriteLine(" ");
if (MyConnection.State == ConnectionState.Open)
{
MyConnection.Close();
MyConnection.Dispose();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
strWriter.WriteLine(MAWBNumber + " could not be Updated " + ex.Message);
strWriter.WriteLine(" ");
strWriter.WriteLine("-------------------------------------------------------");
strWriter.WriteLine(" ");
}
}
public void CloseTest()
{
var mock = new Mock <IMyDriver>();
var client = new MyConnection(new[] { mock.Object });
mock.Setup(driver => driver.Close());
client.Close();
mock.VerifyAll();
}
//Updates the LastLoggedIn to the current date/time.
public void UpdateLastLoggedIn(string email)
{
string sql = "UPDATE users set lastloggedin = @lastLoggedIn WHERE email = @email";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("@LastLoggedIn", DateTime.Now);
cmd.Parameters.AddWithValue("@email", email);
MyConnection.Open();
cmd.ExecuteNonQuery();
MyConnection.Close();
}
public void ExportToGrid(String path)
{
try
{
System.Data.DataTable dt = new System.Data.DataTable();
OleDbConnection MyConnection = null;
DataSet DtSet = null;
OleDbDataAdapter MyCommand = null; //Connection for MS Excel 2003 .xls format
string sheetname = "Sheet1";
// MyConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + path + "';Extended Properties=Excel 8.0;");
//Connection for .xslx 2007 format //
if (path.Substring(path.LastIndexOf('.')).ToLower() == ".xlsx")
{
MyConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + path + "';Extended Properties=Excel 12.0;"); //Select your Excel file
}
else
{
MyConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + path + "';Extended Properties= 'Excel 8.0;HDR=YES;IMEX=1;';");
}
MyConnection.Open();
System.Data.DataTable schemaTable = MyConnection.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
if (schemaTable.Rows.Count > 0)
{
sheetname = schemaTable.Rows[0]["TABLE_NAME"].ToString();
}
MyConnection.Close();
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetname + "]", MyConnection);
DtSet = new System.Data.DataSet(); //Bind all excel data in to data set
MyCommand.Fill(DtSet, "[" + sheetname + "]");
dt = DtSet.Tables[0];
MyConnection.Close(); //Check datatable have records
if (dt.Rows.Count > 0)
{
gvUploadKitty.DataSource = dt;
gvUploadKitty.DataBind();
ViewState["kittyTable"] = dt;
} //Delete temporary Excel file from the Server path
if (System.IO.File.Exists(path))
{
System.IO.File.Delete(path);
}
}
catch (Exception ex)
{
string js = "alert('The uploaded file is not in correct format !!');"; //"alert('" + ex + "');";
ScriptManager.RegisterStartupScript(this, this.GetType(), "script2", js, true);
}
}
public Formation FindById(int id)
{
string Requete = "Select * from Formations where id=" + id;
OleDbDataReader read = MyConnection.ExecuteReader(Requete);
Formation g = new Formation();
read.Read();
g.Id = read.GetInt32(0);
MyConnection.Close();
return(g);
}
public int Insert(PL_M_TuitionFee pl, string xmlDoc)
{
con.cmd.CommandText = "USP_M_TuitionFee";
con.cmd.CommandType = CommandType.StoredProcedure;
con.cmd.Parameters.AddWithValue("@sptype", pl.sptype);
con.cmd.Parameters.AddWithValue("@ECODE", pl.ECODE);
con.cmd.Parameters.AddWithValue("@EMP_NAME", pl.EMP_NAME);
con.cmd.Parameters.AddWithValue("@EmpType", pl.EmpType);
con.cmd.Parameters.AddWithValue("@fin_year", pl.fin_year);
con.cmd.Parameters.AddWithValue("@GPS_NPS", pl.GPS_NPS);
con.cmd.Parameters.AddWithValue("@TableID", pl.TableID);
con.cmd.Parameters.AddWithValue("@freeze_flage", pl.freeze_flage);
con.cmd.Parameters.AddWithValue("@refrence_no", pl.refrence_no);
con.cmd.Parameters.AddWithValue("@CREATEDBY", pl.CreatedBy);
con.cmd.Parameters.AddWithValue("@xmlDoc", xmlDoc);
SqlParameter p2 = new SqlParameter("@P_MSG", SqlDbType.VarChar, 200);
p2.Direction = ParameterDirection.Output;
con.cmd.Parameters.Add(p2);
try
{
con.Open();
int RetValue = con.cmd.ExecuteNonQuery();
pl.MSG = con.cmd.Parameters["@P_MSG"].Value.ToString();
return(RetValue);
}
catch (Exception ex)
{
// pl.MSG = con.cmd.Parameters["@P_MSG"].Value.ToString();
con.Close();
return(0);
}
finally
{
con.Close();
con.cmd.Dispose();
}
}
public List <Precision> GetallPres(int id)
{
List <Precision> p = new List <Precision>();
string req = "Select * From [Precisions] where modules_id =" + id;
OleDbDataReader sqr = MyConnection.ExecuteReader(req);
while (sqr.Read())
{
p.Add(new Precision(sqr.GetInt32(0), sqr.GetInt32(1), sqr.GetString(2), sqr.GetString(3), sqr.GetInt32(4), sqr.GetInt32(5), new ModuleDAO().FindById(sqr.GetInt32(6))));
}
MyConnection.Close();
return(p);
}
public Module getModulepre(int id)
{
List <Module> m = new List <Module>();
string req = "Select * from [Module] where id = " + id;
OleDbDataReader da = MyConnection.ExecuteReader(req);
while (da.Read())
{
m.Add(new Module(da.GetInt32(0), new PackageFilieres.FiliereDAO().FindById(da.GetInt32(1)), da.GetString(2), da.GetInt32(3), da.GetString(4), da.GetString(5), da.GetString(6), da.GetString(7), da.GetString(8), da.GetString(9), da.GetString(10), da.GetString(11), da.GetString(12)));
}
MyConnection.Close();
return(m.ElementAt(0));
}
/// <summary>
/// Recherche par Code, Titre, et Description
/// </summary>
/// <param name="filier"></param>
/// <returns></returns>
public List <Filiere> FindByFilier(Filiere filier)
{
string Requete = "Select * from Filieres ";
if (filier.Code != "" || filier.Titre != "" || filier.Description != "")
{
Requete += " where ";
}
bool and = false;
if (filier.Code != "")
{
Requete += " Code like '%" + filier.Code + "%'";
and = true;
}
if (filier.Titre != "")
{
if (and)
{
Requete += " and ";
}
Requete += " Titre like '%" + filier.Titre + "%'";
and = true;
}
if (filier.Description != "")
{
if (and)
{
Requete += " and ";
}
Requete += " Description like '%" + filier.Description + "%'";
and = true;
}
List <Filiere> ListFiliere = new List <Filiere>();
OleDbDataReader read = MyConnection.ExecuteReader(Requete);
while (read.Read())
{
Filiere f = new Filiere();
f.Id = read.GetInt32(0);
f.Titre = read.GetString(1);
f.Code = read.GetString(2);
f.Description = read.GetString(3);
ListFiliere.Add(f);
}
MyConnection.Close();
return(ListFiliere);
}
public List <Module> Select()
{
List <Module> liste = new List <Module>();
string req = "select * from Modules";
OleDbDataReader da = MyConnection.ExecuteReader(req);
while (da.Read())
{
liste.Add(new Module(da.GetInt32(0), new PackageFilieres.FiliereDAO().FindById(da.GetInt32(1)), da.GetString(2), da.GetInt32(3), da.GetString(4), da.GetString(5), da.GetString(6), da.GetString(7), da.GetString(8), da.GetString(9), da.GetString(10), da.GetString(11), da.GetString(12)));
}
MyConnection.Close();
return(liste);
}
public Groupe FindById(int id)
{
string Requete = "Select * from Groupes where id=" + id;
OleDbDataReader read = MyConnection.ExecuteReader(Requete);
Groupe g = new Groupe();
read.Read();
g.Id = read.GetInt32(0);
g.Code = read.GetString(1);
g.Nom = read.GetString(2);
g.Filiere = new FiliereDAO().FindById(read.GetInt32(3));
MyConnection.Close();
return(g);
}
public List <Precision> Select()
{
List <Precision> p = new List <Precision>();
string req = "Select * From Precisions";
OleDbDataReader sqr = MyConnection.ExecuteReader(req);
while (sqr.Read())
{
p.Add(new Precision(sqr.GetInt32(0), sqr.GetInt32(1), sqr.GetString(2), sqr.GetString(3), sqr.GetInt32(4), sqr.GetInt32(5), new ModuleDAO().FindById(sqr.GetInt32(6))));
// p.Add(new Precision(sqr.GetInt32(0), sqr.GetInt32(1), sqr.GetString(2), sqr.GetString(3), sqr.GetInt32(4), sqr.GetInt32(5),new ModuleDAO().FindById(sqr.GetInt32(6))));
}
MyConnection.Close();
return(p);
}
public void AddProduct(Product p)//string name, string file, string ownerfk)
{
string sql = "INSERT into products (name, file, ownerfk, shareuser) values (@name, @file, @ownerfk, @shareuser)";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("@name", p.Name);
cmd.Parameters.AddWithValue("@file", p.File);
cmd.Parameters.AddWithValue("@ownerfk", p.OwnerFK);
cmd.Parameters.AddWithValue("@shareuser", p.Shareuser);
MyConnection.Open();
cmd.ExecuteNonQuery();
MyConnection.Close();
}
public void AddFile(string filename, string fileurl, string receiver, int uid)
{
string sql = "INSERT INTO files (filename, fileurl, receiver, user_id) VALUES (@filename, @fileurl, @receiver, @user_id)";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("@filename", filename);
cmd.Parameters.AddWithValue("@fileurl", fileurl);
cmd.Parameters.AddWithValue("@receiver", receiver);
cmd.Parameters.AddWithValue("@user_id", uid);
MyConnection.Open();
cmd.ExecuteNonQuery();
MyConnection.Close();
}