public int InsertCountry(Country c)
{
DBAccess db = new DBAccess();
SqlParameter p = new SqlParameter("@iCountryID", 0);
p.Direction = ParameterDirection.Output;
db.AddParameter("@sCountryName", c.CountryName);
db.AddParameter(p);
return db.ExecuteNonQuery("JobsDb_Countries_Insert");
}
public static int Insert(ExperienceLevel l)
{
DBAccess db = new DBAccess();
db.AddParameter("@sExperienceLevelName", l.ExperienceLevelName);
SqlParameter p = new SqlParameter("@iExperienceLevelID", SqlDbType.Int);
p.Direction = ParameterDirection.Output;
db.AddParameter(p);
int retval = db.ExecuteNonQuery("JobsDb_ExperienceLevels_Insert");
if (retval == 1)
{
return int.Parse(p.Value.ToString());
}
else
{
return -1;
}
}
public static int Insert(MyResume r)
{
DBAccess db = new DBAccess();
db.AddParameter("@iResumeID", r.ResumeID);
db.AddParameter("@sUserName", r.UserName);
SqlParameter p = new SqlParameter("@iMyResumeID", SqlDbType.Int);
p.Direction = ParameterDirection.Output;
db.AddParameter(p);
int retval = db.ExecuteNonQuery("JobsDb_MyResumes_Insert");
if (retval == 1)
{
return int.Parse(p.Value.ToString());
}
else
{
return -1;
}
}
public static string GetExperienceLevelName(int id)
{
DBAccess db = new DBAccess();
db.AddParameter("@iExperienceLevelID", id);
return db.ExecuteScalar("JobsDb_ExperienceLevels_GetLevelName").ToString();
}
public static string GetCountryName(int id)
{
DBAccess db = new DBAccess();
db.AddParameter("@iCountryID", id);
return db.ExecuteScalar("JobsDb_Countries_GetCountryName").ToString();
}
/// <summary>
/// 更新一条简历
/// </summary>
/// <param name="r"></param>
/// <returns></returns>
public static int Update(Resume r)
{
DBAccess db = new DBAccess();
db.AddParameter("@iResumeID", r.ResumeID);
db.AddParameter("@sJobTitle", r.JobTitle);
db.AddParameter("@sTargetCity", r.City);
db.AddParameter("@iTargateStateID", r.StateID);
db.AddParameter("@iTargetCountryID", r.CountryID);
db.AddParameter("@iRelocationCountryID", r.RelocationCountryID);
db.AddParameter("@iTargetJobTypeID", r.JobTypeID);
db.AddParameter("@iEducationLevelID", r.EducationLevelID);
db.AddParameter("@iExperienceLevelID", r.ExperienceLevelID);
db.AddParameter("@sResumeText", r.ResumeText);
db.AddParameter("@sCoverLetterText", r.CoveringLetterText);
db.AddParameter("@sUserName", r.UserName);
db.AddParameter("@dtPostDate", r.PostedDate);
return db.ExecuteNonQuery("JobsDb_Resumes_Update");
}
/// <summary>
/// 根据指定的用户名获取简历的编号
/// </summary>
/// <param name="username"></param>
/// <returns></returns>
public static int GetResumeID(string username)
{
DBAccess db = new DBAccess();
db.AddParameter("@sUserName", username);
SqlDataReader dr = (SqlDataReader)db.ExecuteReader("JobsDb_Resumes_SelectForUser");
int resumeid=-1;
if (dr.HasRows)
{
while (dr.Read())
{
resumeid = dr.GetInt32(dr.GetOrdinal("ResumeID"));
}
dr.Close();
}
return resumeid;
}
/// <summary>
/// 根据指定的用户名获取单个简历
/// </summary>
/// <param name="username">用户名</param>
/// <returns></returns>
public static Resume GetResume(string username)
{
//实例化DBAccess对象
DBAccess db = new DBAccess();
//添加指定的输入参数
db.AddParameter("@sUserName", username);
//执行存储过程JobsDb_Resumes_SelectForUser获取简历信息
SqlDataReader dr = (SqlDataReader)db.ExecuteReader("JobsDb_Resumes_SelectForUser");
if (dr.HasRows)
{
//实例化一个新的Resume对象
Resume r = new Resume();
//从DbDataReader中获取Resume信息
while (dr.Read())
{
r.ResumeID = dr.GetInt32(dr.GetOrdinal("ResumeID"));
r.City = dr.GetString(dr.GetOrdinal("TargetCity"));
r.CountryID = dr.GetInt32(dr.GetOrdinal("TargetCountryID"));
r.CoveringLetterText = dr.GetString(dr.GetOrdinal("CoverLetterText"));
r.EducationLevelID = dr.GetInt32(dr.GetOrdinal("EducationLevelID"));
r.ExperienceLevelID = dr.GetInt32(dr.GetOrdinal("ExperienceLevelID"));
r.JobTitle = dr.GetString(dr.GetOrdinal("JobTitle"));
r.JobTypeID = dr.GetInt32(dr.GetOrdinal("TargetJobTypeID"));
r.RelocationCountryID = dr.GetInt32(dr.GetOrdinal("RelocationCountryID"));
r.ResumeText = dr.GetString(dr.GetOrdinal("ResumeText"));
r.StateID = dr.GetInt32(dr.GetOrdinal("TargetStateID"));
r.UserName = dr.GetString(dr.GetOrdinal("UserName"));
r.PostedDate = dr.GetDateTime(dr.GetOrdinal("PostDate"));
}
//关闭DbDataReader对象并返回Resume对象。
dr.Close();
return r;
}
else
{
//如果没有记录，则将ResumeID值赋为-1。
dr.Close();
Resume r = new Resume();
r.ResumeID = -1;
return r;
}
}
public static int Delete(EducationLevel e)
{
DBAccess db = new DBAccess();
db.AddParameter("@iEducationLevelID",e.EducationLevelID);
return db.ExecuteNonQuery("JobsDb_EducationLevels_Delete");
}
public static int Insert(MySearch s)
{
DBAccess db = new DBAccess();
db.AddParameter("@sSearchCriteria", s.Criteria);
db.AddParameter("@iCountryID", s.CountryID);
db.AddParameter("@iStateID", s.StateID);
db.AddParameter("@iCity", s.City);
db.AddParameter("@sUserName", s.UserName);
SqlParameter p = new SqlParameter("@iMySearchID", SqlDbType.Int);
p.Direction = ParameterDirection.Output;
db.AddParameter(p);
int retval=db.ExecuteNonQuery("JobsDb_MySearches_Insert");
if (retval == 1)
{
return int.Parse(p.Value.ToString());
}
else
{
return -1;
}
}
public static MySearch GetMySearch(int mysearchid)
{
DBAccess db = new DBAccess();
db.AddParameter("@iMySearchID", mysearchid);
SqlDataReader dr = (SqlDataReader)db.ExecuteReader("JobsDb_MySearches_SelectOne");
if (dr.HasRows)
{
MySearch s = new MySearch();
while (dr.Read())
{
s.MySearchID = dr.GetInt32(dr.GetOrdinal("mysearchid"));
s.Criteria = dr.GetString(dr.GetOrdinal("SearchCriteria"));
s.CountryID = dr.GetInt32(dr.GetOrdinal("CountryID"));
s.StateID = dr.GetInt32(dr.GetOrdinal("StateID"));
s.City = dr.GetString(dr.GetOrdinal("City"));
s.UserName = dr.GetString(dr.GetOrdinal("UserName"));
}
dr.Close();
return s;
}
else
{
return null;
}
}
public static int Delete(MySearch s)
{
DBAccess db = new DBAccess();
db.AddParameter("@iMySearchID", s.MySearchID);
return db.ExecuteNonQuery("JobsDb_MySearches_Delete");
}
public int UpdateCountry(Country c)
{
DBAccess db = new DBAccess();
db.AddParameter("@iCountryID", c.CountryID);
db.AddParameter("@sCountryName", c.CountryName);
return db.ExecuteNonQuery("JobsDb_Countries_Update");
}
public static int Update(ExperienceLevel l)
{
DBAccess db = new DBAccess();
db.AddParameter("@iExperienceLevelID", l.ExperienceLevelID);
db.AddParameter("@sExperienceLevelName", l.ExperienceLevelName);
return db.ExecuteNonQuery("JobsDb_ExperienceLevels_Update");
}
public static string GetCompanyName(int companyid)
{
DBAccess db = new DBAccess();
db.AddParameter("@iCompanyID", companyid);
return (string)db.ExecuteScalar("JobsDb_Companies_SelectName");
}
/// <summary>
/// 根据指定的简历编号删除简历
/// </summary>
/// <param name="ResumeID"></param>
/// <returns></returns>
public static int Delete(int ResumeID)
{
DBAccess db = new DBAccess();
db.AddParameter("@iResumeID", ResumeID);
return db.ExecuteNonQuery("JobsDb_Resumes_Delete");
}
public static DataSet GetStates(int countryid)
{
DBAccess db = new DBAccess();
db.AddParameter("@iCountryID", countryid);
return db.ExecuteDataSet("JobsDb_States_SelectForCountry");
}
/// <summary>
/// 根据指定的用户简历编号获取简历信息
/// </summary>
/// <param name="resumeid">简历编号</param>
/// <returns></returns>
public static Resume GetResume(int resumeid)
{
DBAccess db = new DBAccess();
db.AddParameter("@iResumeID", resumeid);
SqlDataReader dr = (SqlDataReader)db.ExecuteReader("JobsDb_Resumes_SelectOne");
if (dr.HasRows)
{
Resume r = new Resume();
while (dr.Read())
{
r.ResumeID = dr.GetInt32(dr.GetOrdinal("ResumeID"));
r.City = dr.GetString(dr.GetOrdinal("TargetCity"));
r.CountryID = dr.GetInt32(dr.GetOrdinal("TargetCountryID"));
r.CoveringLetterText = dr.GetString(dr.GetOrdinal("CoverLetterText"));
r.EducationLevelID = dr.GetInt32(dr.GetOrdinal("EducationLevelID"));
r.ExperienceLevelID = dr.GetInt32(dr.GetOrdinal("ExperienceLevelID"));
r.JobTitle = dr.GetString(dr.GetOrdinal("JobTitle"));
r.JobTypeID = dr.GetInt32(dr.GetOrdinal("TargetJobTypeID"));
r.RelocationCountryID = dr.GetInt32(dr.GetOrdinal("RelocationCountryID"));
r.ResumeText = dr.GetString(dr.GetOrdinal("ResumeText"));
r.StateID = dr.GetInt32(dr.GetOrdinal("TargetStateID"));
r.UserName = dr.GetString(dr.GetOrdinal("UserName"));
r.PostedDate = dr.GetDateTime(dr.GetOrdinal("PostDate"));
}
dr.Close();
return r;
}
else
{
dr.Close();
return new Resume();
}
}
public static string GetStateName(int id)
{
DBAccess db = new DBAccess();
db.AddParameter("@iStateID", id);
return db.ExecuteScalar("JobsDb_States_GetStateName").ToString();
}
/// <summary>
/// 插入一个新的简历
/// </summary>
/// <param name="r"></param>
/// <returns></returns>
public static int Insert(Resume r)
{
DBAccess db = new DBAccess();
db.AddParameter("@sJobTitle", r.JobTitle);
db.AddParameter("@sTargetCity", r.City);
db.AddParameter("@iTargateStateID", r.StateID);
db.AddParameter("@iTargetCountryID", r.CountryID);
db.AddParameter("@iRelocationCountryID", r.RelocationCountryID);
db.AddParameter("@iTargetJobTypeID", r.JobTypeID);
db.AddParameter("@iEducationLevelID", r.EducationLevelID);
db.AddParameter("@iExperienceLevelID", r.ExperienceLevelID);
db.AddParameter("@sResumeText", r.ResumeText);
db.AddParameter("@sCoverLetterText", r.CoveringLetterText);
db.AddParameter("@sUserName", r.UserName);
db.AddParameter("@dtPostDate", r.PostedDate);
SqlParameter p=new SqlParameter("@iResumeID",SqlDbType.Int);
p.Direction=ParameterDirection.Output;
db.AddParameter(p);
int retval=db.ExecuteNonQuery("JobsDb_Resumes_Insert");
if (retval <= 0)
{
return -1;
}
else
{
return (int)p.Value;
}
}
public static DataSet GetMyResumes(string username)
{
DBAccess db = new DBAccess();
db.AddParameter("@sUserName", username);
return db.ExecuteDataSet("JobsDb_MyResumes_SelectForUser");
}
public static DataSet SearchJobs(string skills,int countryid,int stateid,string city)
{
string[] arr = skills.Split(' ');
DataSet ds = new DataSet();
DataSet dsTemp = new DataSet();
bool flag = false;
DBAccess db = new DBAccess();
foreach (string s in arr)
{
db.AddParameter("@sSkill", s);
if (countryid == -1)
{
db.AddParameter("@iCountryID", DBNull.Value);
}
else
{
db.AddParameter("@iCountryID", countryid);
}
if (stateid == -1)
{
db.AddParameter("@iStateID", DBNull.Value);
}
else
{
db.AddParameter("@iStateID", stateid);
}
if (city=="")
{
db.AddParameter("@sCity", DBNull.Value);
}
else
{
db.AddParameter("@sCity", city);
}
dsTemp = db.ExecuteDataSet("JobsDb_JobPostings_SelectForMatchingSkills");
db.Parameters.Clear();
ds.Merge(dsTemp);
if (flag == false)
{
DataColumn[] pk = new DataColumn[1];
pk[0] = ds.Tables[0].Columns["postingid"];
ds.Tables[0].PrimaryKey = pk;
flag = true;
}
}
return ds;
}
public static int Delete(MyJob j)
{
DBAccess db = new DBAccess();
db.AddParameter("@iMyJobID",j.MyJobID);
return db.ExecuteNonQuery("JobsDb_MyJobs_Delete");
}
