public bool GetCommand(int jobId, Guid sessionId, string commentText)
{
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
IDbCommand cmd = new OleDbCommand("sp_add_comment", (OleDbConnection)cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
//Get user id
FindUser find = new FindUser();
int userId = int.Parse(find.GetUserIDBySessionId(sessionId).ToString());
cmd.Parameters.Add(new OleDbParameter("@user_id", userId));
cmd.Parameters.Add(new OleDbParameter("@job_id", jobId));
cmd.Parameters.Add(new OleDbParameter("@comText", commentText));
cmd.ExecuteNonQuery();
return true;
}
catch(OleDbException e)
{
Console.WriteLine(e.Message);
}
finally
{
cn.Close();
}
return false;
}
public bool GetCommand(int commentId)
{
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
IDbCommand cmd = new OleDbCommand("sp_delete_comment", (OleDbConnection)cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
cmd.Parameters.Add(new OleDbParameter("@comment_id", commentId));
cmd.ExecuteNonQuery();
return(true);
}
catch (OleDbException e)
{
Console.WriteLine(e.Message);
}
finally
{
cn.Close();
}
return(false);
}
//public List<string> Execute(string qry)
//{
// ConnectionString cnString = new ConnectionString();
// IDbConnection cn = new OleDbConnection(cnString.GetConnString());
// IDbCommand cmd = new OleDbCommand(qry, (OleDbConnection)cn);
// List<string> contents = new List<string>();
// try
// {
// // open the connection
// cn.Open();
// // execute the query
// // read the data into a data reader
// IDataReader rdr = cmd.ExecuteReader();
// while (rdr.Read())
// {
// for (int i = 0; i < rdr.FieldCount; i++)
// {
// contents.Add(rdr[i].ToString());
// }
// }
// return contents;
// }
// catch (Exception)
// {
// }
// finally
// {
// cn.Close();
// }
// return contents;
//}
public DataTable Execute(string qry)
{
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
IDbCommand cmd = new OleDbCommand(qry, (OleDbConnection)cn);
//List<DbUser> contents = new List<DbUser>();
DataTable dt = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter((OleDbCommand)cmd);
try
{
// open the connection
cn.Open();
da.Fill(dt);
//contents = (from DataRow row in dt.Rows
// select new DbUser
// {
// UserId = (int)row["SHARE_HOLDER_ID"],
// FirstName = row["FIRST_NAME"].ToString(),
// LastName = row["LAST_NAME"].ToString()
// }).ToList();
}
catch (Exception)
{
}
finally
{
cn.Close();
}
return dt;
}
public bool GetCommand(List <DbJob> obj, int stream_id)
{
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
InsertJob insJob = new InsertJob();
try
{
cn.Open();
IDbTransaction tran = cn.BeginTransaction();
IDbCommand cmd = new OleDbCommand("sp_del_job", (OleDbConnection)cn);
cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OleDbParameter("@jobId", obj[0].JobId));
int affectedRows = cmd.ExecuteNonQuery();
tran.Commit();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
cn.Close();
}
return(false);
}
public bool GetCommand(int commentId)
{
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
IDbCommand cmd = new OleDbCommand("sp_delete_comment", (OleDbConnection)cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
cmd.Parameters.Add(new OleDbParameter("@comment_id", commentId));
cmd.ExecuteNonQuery();
return true;
}
catch (OleDbException e)
{
Console.WriteLine(e.Message);
}
finally
{
cn.Close();
}
return false;
}
public bool GetCommand(int jobId, Guid sessionId, string commentText)
{
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
IDbCommand cmd = new OleDbCommand("sp_add_comment", (OleDbConnection)cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
//Get user id
FindUser find = new FindUser();
int userId = int.Parse(find.GetUserIDBySessionId(sessionId).ToString());
cmd.Parameters.Add(new OleDbParameter("@user_id", userId));
cmd.Parameters.Add(new OleDbParameter("@job_id", jobId));
cmd.Parameters.Add(new OleDbParameter("@comText", commentText));
cmd.ExecuteNonQuery();
return(true);
}
catch (OleDbException e)
{
Console.WriteLine(e.Message);
}
finally
{
cn.Close();
}
return(false);
}
public bool Execute(string qry)
{
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
IDbCommand cmd = new OleDbCommand(qry, (OleDbConnection)cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
IDbTransaction tran = cn.BeginTransaction();
cmd.Transaction = tran;
int affectedRows = cmd.ExecuteNonQuery();
Console.WriteLine(affectedRows);
if (affectedRows > 0)
{
tran.Commit();
return true;
}
else
{
tran.Rollback();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
cn.Close();
}
return false;
}
//public List<string> Execute(string qry)
//{
// ConnectionString cnString = new ConnectionString();
// IDbConnection cn = new OleDbConnection(cnString.GetConnString());
// IDbCommand cmd = new OleDbCommand(qry, (OleDbConnection)cn);
// List<string> contents = new List<string>();
// try
// {
// // open the connection
// cn.Open();
// // execute the query
// // read the data into a data reader
// IDataReader rdr = cmd.ExecuteReader();
// while (rdr.Read())
// {
// for (int i = 0; i < rdr.FieldCount; i++)
// {
// contents.Add(rdr[i].ToString());
// }
// }
// return contents;
// }
// catch (Exception)
// {
// }
// finally
// {
// cn.Close();
// }
// return contents;
//}
public DataTable Execute(string qry)
{
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
IDbCommand cmd = new OleDbCommand(qry, (OleDbConnection)cn);
//List<DbUser> contents = new List<DbUser>();
DataTable dt = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter((OleDbCommand)cmd);
try
{
// open the connection
cn.Open();
da.Fill(dt);
//contents = (from DataRow row in dt.Rows
// select new DbUser
// {
// UserId = (int)row["SHARE_HOLDER_ID"],
// FirstName = row["FIRST_NAME"].ToString(),
// LastName = row["LAST_NAME"].ToString()
// }).ToList();
}
catch (Exception)
{
}
finally
{
cn.Close();
}
return(dt);
}
public void Test_JobUserWriteCommand_ExecuteMethod_Returns_A_Bool()
{
ConnectionString str = new ConnectionString();
IDbConnection cn = new OleDbConnection(str.GetConnString());
IDbDataAdapter da = new OleDbDataAdapter();
JobUserWriteCommand write = new JobUserWriteCommand(cn, da);
bool result = write.Execute();
Assert.IsInstanceOf(typeof(bool), result);
}
public bool GetCommand(List <DbJob> obj)
{
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
IDbCommand cmd = new OleDbCommand("sp_update_job", (OleDbConnection)cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
IDbTransaction tran = cn.BeginTransaction();
cmd.Transaction = tran;
// Get IDs
GetStatusID statusid = new GetStatusID();
int status_ID = int.Parse(statusid.Read(obj[0].Status));
GetStreamID streamid = new GetStreamID();
int stream_ID = int.Parse(streamid.Read(obj[0].Stream));
// Add job
cmd.Parameters.Add(new OleDbParameter("@jobId", obj[0].JobId));
cmd.Parameters.Add(new OleDbParameter("@streamId", stream_ID));
cmd.Parameters.Add(new OleDbParameter("@statusId", status_ID));
cmd.Parameters.Add(new OleDbParameter("@descr", obj[0].Description));
cmd.Parameters.Add(new OleDbParameter("@jTitle", obj[0].Title));
cmd.Parameters.Add(new OleDbParameter("@ddline", obj[0].Deadline));
cmd.Parameters.Add(new OleDbParameter("@comp", obj[0].Company));
cmd.Parameters.Add(new OleDbParameter("@loc", obj[0].Location));
int affectedRows = cmd.ExecuteNonQuery();
if (affectedRows > 0)
{
tran.Commit();
return(true);
}
else
{
tran.Rollback();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
cn.Close();
}
return(false);
}
public bool Execute(DbUser alteredUser)
{
// update user details
GetStreamID id = new GetStreamID();
string stream_id = id.Read(alteredUser.Stream);
GetTStatusID id2 = new GetTStatusID();
string tstatus_id = id2.Read(alteredUser.TStatus);
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
IDbCommand cmd = new OleDbCommand("sp_update_profile", (OleDbConnection)cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
IDbTransaction tran = cn.BeginTransaction();
cmd.Transaction = tran;
// Add job
cmd.Parameters.Add(new OleDbParameter("@userLocation", alteredUser.Location));
cmd.Parameters.Add(new OleDbParameter("@userID", alteredUser.UserId));
cmd.Parameters.Add(new OleDbParameter("@userStream", stream_id));
cmd.Parameters.Add(new OleDbParameter("@tstatID", tstatus_id));
cmd.Parameters.Add(new OleDbParameter("@userDegree", alteredUser.Degree));
cmd.Parameters.Add(new OleDbParameter("@userModules", alteredUser.Modules));
int affectedRows = cmd.ExecuteNonQuery();
if (affectedRows > 0)
{
tran.Commit();
return(true);
}
else
{
tran.Rollback();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
cn.Close();
}
return(false);
}
public bool Execute(DbUser alteredUser)
{
// update user details
GetStreamID id = new GetStreamID();
string stream_id = id.Read(alteredUser.Stream);
GetTStatusID id2 = new GetTStatusID();
string tstatus_id = id2.Read(alteredUser.TStatus);
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
IDbCommand cmd = new OleDbCommand("sp_update_profile", (OleDbConnection)cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
IDbTransaction tran = cn.BeginTransaction();
cmd.Transaction = tran;
// Add job
cmd.Parameters.Add(new OleDbParameter("@userLocation", alteredUser.Location));
cmd.Parameters.Add(new OleDbParameter("@userID", alteredUser.UserId));
cmd.Parameters.Add(new OleDbParameter("@userStream", stream_id));
cmd.Parameters.Add(new OleDbParameter("@tstatID", tstatus_id));
cmd.Parameters.Add(new OleDbParameter("@userDegree", alteredUser.Degree));
cmd.Parameters.Add(new OleDbParameter("@userModules", alteredUser.Modules));
int affectedRows = cmd.ExecuteNonQuery();
if (affectedRows > 0)
{
tran.Commit();
return true;
}
else
{
tran.Rollback();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
cn.Close();
}
return false;
}
public bool GetCommand(int recommender_id, int recommended_id, int job_id, string reason)
{
if (DetermineIfExists(recommender_id, recommended_id, job_id))
{
return(true);
}
else
{
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
IDbCommand cmd = new OleDbCommand("sp_add_recommendation", (OleDbConnection)cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
IDbTransaction tran = cn.BeginTransaction();
cmd.Transaction = tran;
cmd.Parameters.Add(new OleDbParameter("@userId", recommender_id));
cmd.Parameters.Add(new OleDbParameter("@user_Id", recommended_id));
cmd.Parameters.Add(new OleDbParameter("@jobId", job_id));
cmd.Parameters.Add(new OleDbParameter("@reason", reason));
int affectedRows = cmd.ExecuteNonQuery();
if (affectedRows > 0)
{
tran.Commit();
return(true);
}
else
{
tran.Rollback();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(false);
}
finally
{
cn.Close();
}
return(true);
}
}
public bool GetCommand(int recommender_id, int recommended_id, int job_id, string reason)
{
if (DetermineIfExists(recommender_id, recommended_id, job_id))
{
return true;
}
else
{
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
IDbCommand cmd = new OleDbCommand("sp_add_recommendation", (OleDbConnection)cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
IDbTransaction tran = cn.BeginTransaction();
cmd.Transaction = tran;
cmd.Parameters.Add(new OleDbParameter("@userId", recommender_id));
cmd.Parameters.Add(new OleDbParameter("@user_Id", recommended_id));
cmd.Parameters.Add(new OleDbParameter("@jobId", job_id));
cmd.Parameters.Add(new OleDbParameter("@reason", reason));
int affectedRows = cmd.ExecuteNonQuery();
if (affectedRows > 0)
{
tran.Commit();
return true;
}
else
{
tran.Rollback();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return false;
}
finally
{
cn.Close();
}
return true;
}
}
public bool GetCommand(List <DbUser> obj, int job_id)
{
if (DetermineIfExists(obj[0].UserId, job_id))
{
//Console.WriteLine(formattedSkill);
return(true);
}
else
{
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
IDbCommand cmd = new OleDbCommand("sp_add_favourite", (OleDbConnection)cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
IDbTransaction tran = cn.BeginTransaction();
cmd.Transaction = tran;
cmd.Parameters.Add(new OleDbParameter("@userId", obj[0].UserId));
cmd.Parameters.Add(new OleDbParameter("@jobId", job_id));
int affectedRows = cmd.ExecuteNonQuery();
if (affectedRows > 0)
{
tran.Commit();
return(true);
}
else
{
tran.Rollback();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(false);
}
finally
{
cn.Close();
}
return(true);
}
}
public bool Execute(DbUser alteredUser)
{
FindUser fuser = new FindUser();
List <DbUser> userList = fuser.Execute(alteredUser.Username);
alteredUser.UserId = userList[0].UserId;
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
IDbCommand cmd = new OleDbCommand("sp_update_password", (OleDbConnection)cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
IDbTransaction tran = cn.BeginTransaction();
cmd.Transaction = tran;
// Add job
cmd.Parameters.Add(new OleDbParameter("@userID", alteredUser.UserId));
cmd.Parameters.Add(new OleDbParameter("@newPass", alteredUser.Password));
int affectedRows = cmd.ExecuteNonQuery();
if (affectedRows > 0)
{
tran.Commit();
return(true);
}
else
{
tran.Rollback();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
cn.Close();
}
return(false);
}
public bool Execute(DbUser alteredUser)
{
FindUser fuser = new FindUser();
List<DbUser> userList = fuser.Execute(alteredUser.Username);
alteredUser.UserId = userList[0].UserId;
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
IDbCommand cmd = new OleDbCommand("sp_update_password", (OleDbConnection)cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
IDbTransaction tran = cn.BeginTransaction();
cmd.Transaction = tran;
// Add job
cmd.Parameters.Add(new OleDbParameter("@userID", alteredUser.UserId));
cmd.Parameters.Add(new OleDbParameter("@newPass", alteredUser.Password));
int affectedRows = cmd.ExecuteNonQuery();
if (affectedRows > 0)
{
tran.Commit();
return true;
}
else
{
tran.Rollback();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
cn.Close();
}
return false;
}
public bool GetCommand(string skill, int userID)
{
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
try
{
cn.Open();
IDbTransaction tran = cn.BeginTransaction();
// Add userskill
IDbCommand cmd = new OleDbCommand("sp_del_userSkill", (OleDbConnection)cn);
cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
Console.WriteLine(userID);
InsertSkill ins = new InsertSkill();
string formattedSkill = ins.FormatSkill(skill);
GetSkillId id = new GetSkillId();
int skillid = int.Parse(id.Read(formattedSkill));
cmd.Parameters.Add(new OleDbParameter("@userID", int.Parse(userID.ToString())));
cmd.Parameters.Add(new OleDbParameter("@skillID", int.Parse(skillid.ToString())));
int affectedRows = cmd.ExecuteNonQuery();
if (affectedRows > 0)
{
tran.Commit();
return true;
}
else
{
return false;
}
}
catch(Exception e)
{
Console.WriteLine(e.Message);
return false;
}
}
public bool GetCommand(string skill, int userID)
{
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
try
{
cn.Open();
IDbTransaction tran = cn.BeginTransaction();
// Add userskill
IDbCommand cmd = new OleDbCommand("sp_del_userSkill", (OleDbConnection)cn);
cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
Console.WriteLine(userID);
InsertSkill ins = new InsertSkill();
string formattedSkill = ins.FormatSkill(skill);
GetSkillId id = new GetSkillId();
int skillid = int.Parse(id.Read(formattedSkill));
cmd.Parameters.Add(new OleDbParameter("@userID", int.Parse(userID.ToString())));
cmd.Parameters.Add(new OleDbParameter("@skillID", int.Parse(skillid.ToString())));
int affectedRows = cmd.ExecuteNonQuery();
if (affectedRows > 0)
{
tran.Commit();
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(false);
}
}
public bool GetCommand(List <DbJob> job, List <string> deletedSkills)
{
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
InsertJob insJob = new InsertJob();
try
{
cn.Open();
IDbTransaction tran = cn.BeginTransaction();
IDbCommand cmd = new OleDbCommand("sp_del_jobskill", (OleDbConnection)cn);
cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
for (int j = 0; j < deletedSkills.Count(); j++)
{
string formattedSkill = insJob.FormatSkill(deletedSkills[j]);
GetSkillId id = new GetSkillId();
int delskillid = int.Parse(id.Read(formattedSkill));
cmd.Parameters.Add(new OleDbParameter("@jobId", job[0].JobId));
cmd.Parameters.Add(new OleDbParameter("@delSkillId", delskillid));
int affectedRows = cmd.ExecuteNonQuery();
}
tran.Commit();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
cn.Close();
}
return(false);
}
public string Execute(string qry)
{
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
IDbCommand cmd = new OleDbCommand(qry, (OleDbConnection)cn);
try
{
// open the connection
cn.Open();
// execute the query
// read the data into a data reader
string id = cmd.ExecuteScalar().ToString();
return(id);
}
catch (Exception)
{
}
finally
{
cn.Close();
}
return("0");
}
public bool Execute(string qry)
{
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
IDbCommand cmd = new OleDbCommand(qry, (OleDbConnection)cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
IDbTransaction tran = cn.BeginTransaction();
cmd.Transaction = tran;
int affectedRows = cmd.ExecuteNonQuery();
Console.WriteLine(affectedRows);
if (affectedRows > 0)
{
tran.Commit();
return(true);
}
else
{
tran.Rollback();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
cn.Close();
}
return(false);
}
public string Execute(string qry)
{
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
IDbCommand cmd = new OleDbCommand(qry, (OleDbConnection)cn);
try
{
// open the connection
cn.Open();
// execute the query
// read the data into a data reader
string id = cmd.ExecuteScalar().ToString();
return id;
}
catch (Exception)
{
}
finally
{
cn.Close();
}
return "0";
}
public bool GetCommand(DbJob job, List <string> skills, Guid sessionID)
{
//string cmdString = "sp_add_job(" + job[0].UserId + ",'" + job[0].StreamId + "','" +
// job[0].StatusId + "','" + job[0].Description + "','" +
// job[0].Title + "','" + job[0].Deadline + "','" +
// job[0].Company + "'," + job[0].Location + ")";
//IWriteCommand cmd = new WriteCommand();
//cmd.SetCommand(cmdString);
//if (cmd.Execute())
//{
// return true;
//}
//return false;
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
IDbCommand cmd = new OleDbCommand("sp_add_job", (OleDbConnection)cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
IDbTransaction tran = cn.BeginTransaction();
cmd.Transaction = tran;
// Get IDs
GetStatusID statusid = new GetStatusID();
int status_ID = int.Parse(statusid.Read(job.Status));
GetStreamID streamid = new GetStreamID();
int stream_ID = int.Parse(streamid.Read(job.Stream));
FindUser find = new FindUser();
int userId = int.Parse(find.GetUserIDBySessionId(sessionID).ToString());
// Add job
cmd.Parameters.Add(new OleDbParameter("@user_id", userId));
cmd.Parameters.Add(new OleDbParameter("@stream_id", stream_ID));
cmd.Parameters.Add(new OleDbParameter("@status_id", status_ID));
cmd.Parameters.Add(new OleDbParameter("@description", job.Description));
cmd.Parameters.Add(new OleDbParameter("@title", job.Title));
cmd.Parameters.Add(new OleDbParameter("@deadline", job.Deadline));
cmd.Parameters.Add(new OleDbParameter("@company", job.Company));
cmd.Parameters.Add(new OleDbParameter("@location", job.Location));
int affectedRows = cmd.ExecuteNonQuery();
if (affectedRows > 0)
{
// Add jobskills
for (int i = 0; i < skills.Count(); i++)
{
IDbCommand cmd2 = new OleDbCommand("sp_add_jobSkill", (OleDbConnection)cn);
cmd2.Transaction = tran;
cmd2.CommandType = CommandType.StoredProcedure;
string formattedSkill = FormatSkill(skills[i]);
GetSkillId id = new GetSkillId();
int skillid = int.Parse(id.Read(formattedSkill));
cmd2.Parameters.Add(new OleDbParameter("@skill_id", int.Parse(skillid.ToString())));
affectedRows = affectedRows + cmd2.ExecuteNonQuery();
}
tran.Commit();
return(true);
}
else
{
tran.Rollback();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
cn.Close();
}
return(false);
}
public bool addNewSkills(int job_id, List <string> newlyAddedSkills)
{
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
IDbCommand cmd = new OleDbCommand("sp_update_jobskill", (OleDbConnection)cn);
cmd.CommandType = CommandType.StoredProcedure;
InsertJob insJob = new InsertJob();
for (int i = 0; i < newlyAddedSkills.Count(); i++)
{
string formattednewSkill = insJob.FormatSkill(newlyAddedSkills[i]);
GetSkillId newId = new GetSkillId();
int addedskillid = int.Parse(newId.Read(formattednewSkill));
try
{
cn.Open();
IDbTransaction tran = cn.BeginTransaction();
cmd.Transaction = tran;
cmd.Parameters.Add(new OleDbParameter("@jobId", job_id));
cmd.Parameters.Add(new OleDbParameter("@addedSkillId", addedskillid));
int affectedRows = cmd.ExecuteNonQuery();
if (affectedRows > 0)
{
// // Add jobskills
// for (int i = 0; i < skills.Count(); i++)
// {
// IDbCommand cmd2 = new OleDbCommand("sp_add_jobSkill", (OleDbConnection)cn);
// cmd2.Transaction = tran;
// cmd2.CommandType = CommandType.StoredProcedure;
// string formattedSkill = FormatSkill(skills[i]);
// GetSkillId id = new GetSkillId();
// int skillid = int.Parse(id.Read(formattedSkill));
// cmd2.Parameters.Add(new OleDbParameter("@skill_id", int.Parse(skillid.ToString())));
// affectedRows = affectedRows + cmd2.ExecuteNonQuery();
// }
tran.Commit();
return(true);
}
else
{
tran.Rollback();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
cn.Close();
}
}
return(false);
}
public bool GetCommand(DbJob job, List<string> skills, Guid sessionID)
{
//string cmdString = "sp_add_job(" + job[0].UserId + ",'" + job[0].StreamId + "','" +
// job[0].StatusId + "','" + job[0].Description + "','" +
// job[0].Title + "','" + job[0].Deadline + "','" +
// job[0].Company + "'," + job[0].Location + ")";
//IWriteCommand cmd = new WriteCommand();
//cmd.SetCommand(cmdString);
//if (cmd.Execute())
//{
// return true;
//}
//return false;
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
IDbCommand cmd = new OleDbCommand("sp_add_job", (OleDbConnection)cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
IDbTransaction tran = cn.BeginTransaction();
cmd.Transaction = tran;
// Get IDs
GetStatusID statusid = new GetStatusID();
int status_ID = int.Parse(statusid.Read(job.Status));
GetStreamID streamid = new GetStreamID();
int stream_ID = int.Parse(streamid.Read(job.Stream));
FindUser find = new FindUser();
int userId = int.Parse(find.GetUserIDBySessionId(sessionID).ToString());
// Add job
cmd.Parameters.Add(new OleDbParameter("@user_id", userId));
cmd.Parameters.Add(new OleDbParameter("@stream_id", stream_ID));
cmd.Parameters.Add(new OleDbParameter("@status_id", status_ID));
cmd.Parameters.Add(new OleDbParameter("@description", job.Description));
cmd.Parameters.Add(new OleDbParameter("@title", job.Title));
cmd.Parameters.Add(new OleDbParameter("@deadline", job.Deadline));
cmd.Parameters.Add(new OleDbParameter("@company", job.Company));
cmd.Parameters.Add(new OleDbParameter("@location", job.Location));
int affectedRows = cmd.ExecuteNonQuery();
if (affectedRows > 0)
{
// Add jobskills
for (int i = 0; i < skills.Count(); i++)
{
IDbCommand cmd2 = new OleDbCommand("sp_add_jobSkill", (OleDbConnection)cn);
cmd2.Transaction = tran;
cmd2.CommandType = CommandType.StoredProcedure;
string formattedSkill = FormatSkill(skills[i]);
GetSkillId id = new GetSkillId();
int skillid = int.Parse(id.Read(formattedSkill));
cmd2.Parameters.Add(new OleDbParameter("@skill_id", int.Parse(skillid.ToString())));
affectedRows = affectedRows + cmd2.ExecuteNonQuery();
}
tran.Commit();
return true;
}
else
{
tran.Rollback();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
cn.Close();
}
return false;
}
public bool GetCommand(string skill)
{
//IWriteCommand cmd = new WriteCommand();
//string qry = "INSERT INTO Skill (skill_id, skilltext) VALUES (skill_id_seq.nextval, '" + skill + "')";
//if (cmd.Execute(qry) == true)
//{
// return true;
//}
//return false;
string formattedSkill = FormatSkill(skill);
// if exists do nothing
if (DetermineIfExists(formattedSkill))
{
Console.WriteLine(formattedSkill);
return(true);
}
// else add the skill to the database
else
{
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
try
{
cn.Open();
IDbTransaction tran = cn.BeginTransaction();
// Add skill
int affectedRows = 0;
IDbCommand cmd = new OleDbCommand("sp_add_skill", (OleDbConnection)cn);
cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OleDbParameter("@skilltxt", formattedSkill));
affectedRows = cmd.ExecuteNonQuery();
if (affectedRows > 0)
{
tran.Commit();
return(true);
}
else
{
tran.Rollback();
return(false);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
cn.Close();
}
return(false);
}
}
IDbCommand cmd; // = new OleDbCommand(qry, (OleDbConnection)cn);
public void SetCommand(string qry)
{
cn = new OleDbConnection(cnString.GetConnString());
cmd = new OleDbCommand(qry, (OleDbConnection)cn);
cmd.CommandType = CommandType.StoredProcedure;
}
public bool GetCommand(string skill)
{
//IWriteCommand cmd = new WriteCommand();
//string qry = "INSERT INTO Skill (skill_id, skilltext) VALUES (skill_id_seq.nextval, '" + skill + "')";
//if (cmd.Execute(qry) == true)
//{
// return true;
//}
//return false;
string formattedSkill = FormatSkill(skill);
// if exists do nothing
if (DetermineIfExists(formattedSkill))
{
Console.WriteLine(formattedSkill);
return true;
}
// else add the skill to the database
else
{
ConnectionString cnString = new ConnectionString();
IDbConnection cn = new OleDbConnection(cnString.GetConnString());
try
{
cn.Open();
IDbTransaction tran = cn.BeginTransaction();
// Add skill
int affectedRows = 0;
IDbCommand cmd = new OleDbCommand("sp_add_skill", (OleDbConnection)cn);
cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OleDbParameter("@skilltxt", formattedSkill));
affectedRows = cmd.ExecuteNonQuery();
if (affectedRows > 0)
{
tran.Commit();
return true;
}
else
{
tran.Rollback();
return false;
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
cn.Close();
}
return false;
}
}