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;
}
}
/// <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();
}
}
/// <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 Company GetCompany(int companyid)
{
DBAccess db = new DBAccess();
db.Parameters.Add(new SqlParameter("@iCompanyID", companyid));
SqlDataReader dr = (SqlDataReader)db.ExecuteReader("JobsDb_Companies_SelectOne");
if (dr.HasRows)
{
Company c = new Company();
while (dr.Read())
{
c.CompanyID = dr.GetInt32(dr.GetOrdinal("companyid"));
c.CompanyName = dr.GetString(dr.GetOrdinal("CompanyName"));
c.BriefProfile = dr.GetString(dr.GetOrdinal("CompanyProfile"));
c.WebSiteUrl = dr.GetString(dr.GetOrdinal("WebSiteUrl"));
c.Address1 = dr.GetString(dr.GetOrdinal("Address1"));
c.Address2 = dr.GetString(dr.GetOrdinal("Address2"));
c.City = dr.GetString(dr.GetOrdinal("City"));
c.StateID = dr.GetInt32(dr.GetOrdinal("StateID"));
c.CountryID = dr.GetInt32(dr.GetOrdinal("CountryID"));
c.ZIP = dr.GetString(dr.GetOrdinal("ZIP"));
c.Phone = dr.GetString(dr.GetOrdinal("Phone"));
c.Fax = dr.GetString(dr.GetOrdinal("Fax"));
c.Email = dr.GetString(dr.GetOrdinal("CompanyEmail"));
c.UserName = dr.GetString(dr.GetOrdinal("UserName"));
}
dr.Close();
return c;
}
else
{
dr.Close();
return null;
}
}
public static string GetCompanyName(int companyid)
{
DBAccess db = new DBAccess();
db.AddParameter("@iCompanyID", companyid);
return (string)db.ExecuteScalar("JobsDb_Companies_SelectName");
}
/// <summary>
/// 获取最近七天的职位信息
/// </summary>
/// <returns></returns>
public static DataSet GetLatest()
{
DBAccess db = new DBAccess();
return db.ExecuteDataSet("JobsDb_JobPostings_GetLatest");
}
public static int Delete(JobPosting p)
{
DBAccess db = new DBAccess();
db.Parameters.Add(new SqlParameter("@iPostingID", p.JobPostingID));
int retval = db.ExecuteNonQuery("JobsDb_JobPostings_Delete");
return retval;
}
/// <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 GetStates(int countryid)
{
DBAccess db = new DBAccess();
db.AddParameter("@iCountryID", countryid);
return db.ExecuteDataSet("JobsDb_States_SelectForCountry");
}
public static string GetStateName(int id)
{
DBAccess db = new DBAccess();
db.AddParameter("@iStateID", id);
return db.ExecuteScalar("JobsDb_States_GetStateName").ToString();
}
public static DataSet GetStates()
{
DBAccess db = new DBAccess();
return db.ExecuteDataSet("JobsDb_States_SelectAll");
}
public static int Update(Company c)
{
DBAccess db = new DBAccess();
db.Parameters.Add(new SqlParameter("@iCompanyID", c.CompanyID));
db.Parameters.Add(new SqlParameter("@sUserName", c.UserName));
db.Parameters.Add(new SqlParameter("@sCompanyName", c.CompanyName));
db.Parameters.Add(new SqlParameter("@sAddress1", c.Address1));
db.Parameters.Add(new SqlParameter("@sAddress2", c.Address2));
db.Parameters.Add(new SqlParameter("@sCity", c.City));
db.Parameters.Add(new SqlParameter("@iStateID", c.StateID));
db.Parameters.Add(new SqlParameter("@iCountryID", c.CountryID));
db.Parameters.Add(new SqlParameter("@sZip", c.ZIP));
db.Parameters.Add(new SqlParameter("@sPhone", c.Phone));
db.Parameters.Add(new SqlParameter("@sFax", c.Fax));
db.Parameters.Add(new SqlParameter("@sCompanyEmail", c.Email));
db.Parameters.Add(new SqlParameter("@sWebSiteUrl", c.WebSiteUrl));
db.Parameters.Add(new SqlParameter("@sCompanyProfile", c.BriefProfile));
int retval = db.ExecuteNonQuery("JobsDb_Companies_Update");
return retval;
}
public static int Insert(Company c)
{
DBAccess db = new DBAccess();
SqlParameter objParam = new SqlParameter("@iCompanyID",0);
objParam.Direction = ParameterDirection.Output;
db.Parameters.Add(new SqlParameter("@sUserName", c.UserName));
db.Parameters.Add(new SqlParameter("@sCompanyName", c.CompanyName));
db.Parameters.Add(new SqlParameter("@sAddress1", c.Address1));
db.Parameters.Add(new SqlParameter("@sAddress2", c.Address2));
db.Parameters.Add(new SqlParameter("@sCity", c.City));
db.Parameters.Add(new SqlParameter("@iStateID", c.StateID));
db.Parameters.Add(new SqlParameter("@iCountryID", c.CountryID));
db.Parameters.Add(new SqlParameter("@sZip", c.ZIP));
db.Parameters.Add(new SqlParameter("@sPhone", c.Phone));
db.Parameters.Add(new SqlParameter("@sFax", c.Fax));
db.Parameters.Add(new SqlParameter("@sCompanyEmail", c.Email));
db.Parameters.Add(new SqlParameter("@sWebSiteUrl", c.WebSiteUrl));
db.Parameters.Add(new SqlParameter("@sCompanyProfile", c.BriefProfile));
db.Parameters.Add(objParam);
int retval = db.ExecuteNonQuery("JobsDb_Companies_Insert");
if (retval == 1)
{
return int.Parse(objParam.Value.ToString());
}
else
{
return -1;
}
}
/// <summary>
/// 获取简历的总数量
/// </summary>
/// <returns></returns>
public static int GetResumeCount()
{
DBAccess db = new DBAccess();
return (int)db.ExecuteScalar("JobsDb_Resumes_GetCount");
}
public static string GetExperienceLevelName(int id)
{
DBAccess db = new DBAccess();
db.AddParameter("@iExperienceLevelID", id);
return db.ExecuteScalar("JobsDb_ExperienceLevels_GetLevelName").ToString();
}
/// <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;
}
public static DataSet GetExperienceLevels()
{
DBAccess db = new DBAccess();
return db.ExecuteDataSet("JobsDb_ExperienceLevels_SelectAll");
}
/// <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");
}
public static DataSet GetPostings(string username)
{
DBAccess db = new DBAccess();
db.Parameters.Add(new SqlParameter("@sUserName", username));
DataSet ds = db.ExecuteDataSet("JobsDb_JobPostings_SelectByUser");
return ds;
}
public static int GetJobPostingCount()
{
DBAccess db = new DBAccess();
return (int)db.ExecuteScalar("JobsDb_JobPostings_GetCount");
}
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 JobPosting GetPosting(int id)
{
DBAccess db = new DBAccess();
db.Parameters.Add(new SqlParameter("@iPostingID", id));
SqlDataReader dr = (SqlDataReader)db.ExecuteReader("JobsDb_JobPostings_SelectOne");
if (dr.HasRows)
{
JobPosting objJobPosting = new JobPosting();
while (dr.Read())
{
objJobPosting.JobPostingID = dr.GetInt32(dr.GetOrdinal("PostingID"));
objJobPosting.CompanyID = dr.GetInt32(dr.GetOrdinal("CompanyID"));
objJobPosting.Title = dr.GetString(dr.GetOrdinal("Title"));
objJobPosting.ContactPerson = dr.GetString(dr.GetOrdinal("ContactPerson"));
objJobPosting.Department = dr.GetString(dr.GetOrdinal("Department"));
objJobPosting.Description = dr.GetString(dr.GetOrdinal("JobDescription"));
objJobPosting.City = dr.GetString(dr.GetOrdinal("City"));
objJobPosting.StateID = dr.GetInt32(dr.GetOrdinal("StateID"));
objJobPosting.CountryID = dr.GetInt32(dr.GetOrdinal("CountryID"));
objJobPosting.EducationLevelID = dr.GetInt32(dr.GetOrdinal("EducationLevelID"));
objJobPosting.JobTypeID = dr.GetInt32(dr.GetOrdinal("JobTypeID"));
objJobPosting.JobCode = dr.GetString(dr.GetOrdinal("JobCode"));
objJobPosting.MinSalary = dr.GetDecimal(dr.GetOrdinal("MinSalary"));
objJobPosting.MaxSalary = dr.GetDecimal(dr.GetOrdinal("MaxSalary"));
objJobPosting.PostingDate = dr.GetDateTime(dr.GetOrdinal("PostingDate"));
objJobPosting.PostedBy = dr.GetString(dr.GetOrdinal("PostedBy"));
}
dr.Close();
return objJobPosting;
}
else
{
dr.Close();
return new JobPosting();
}
}
public static int GetCompanyCount()
{
DBAccess db = new DBAccess();
return (int)db.ExecuteScalar("JobsDb_Companies_GetCount");
}
public static int Insert(JobPosting p)
{
DBAccess db = new DBAccess();
SqlParameter objParam = new SqlParameter("@iPostingID", 0);
objParam.Direction = ParameterDirection.Output;
db.Parameters.Add(new SqlParameter("@iCompanyID", p.CompanyID));
db.Parameters.Add(new SqlParameter("@sContactPerson", p.ContactPerson));
db.Parameters.Add(new SqlParameter("@sTitle", p.Title));
db.Parameters.Add(new SqlParameter("@sDepartment", p.Department));
db.Parameters.Add(new SqlParameter("@sJobCode", p.JobCode));
db.Parameters.Add(new SqlParameter("@sCity", p.City));
db.Parameters.Add(new SqlParameter("@iStateID", p.StateID));
db.Parameters.Add(new SqlParameter("@iCountryID", p.CountryID));
db.Parameters.Add(new SqlParameter("@iEducationLevelID", p.EducationLevelID));
db.Parameters.Add(new SqlParameter("@iJobTypeID", p.JobTypeID));
db.Parameters.Add(new SqlParameter("@curMinSalary", p.MinSalary));
db.Parameters.Add(new SqlParameter("@curMaxSalary", p.MaxSalary));
db.Parameters.Add(new SqlParameter("@sJobDescription", p.Description));
db.Parameters.Add(new SqlParameter("@daPostingDate", p.PostingDate));
db.Parameters.Add(new SqlParameter("@sPostedBy", p.PostedBy));
db.Parameters.Add(objParam);
int retval = db.ExecuteNonQuery("JobsDb_JobPostings_Insert");
if (retval == 1)
{
return int.Parse(objParam.Value.ToString());
}
else
{
return -1;
}
}
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 int Update(JobPosting p)
{
DBAccess db = new DBAccess();
db.Parameters.Add(new SqlParameter("@iPostingID", p.JobPostingID));
db.Parameters.Add(new SqlParameter("@iCompanyID", p.CompanyID));
db.Parameters.Add(new SqlParameter("@sContactPerson", p.ContactPerson));
db.Parameters.Add(new SqlParameter("@sTitle", p.Title));
db.Parameters.Add(new SqlParameter("@sDepartment", p.Department));
db.Parameters.Add(new SqlParameter("@sJobCode", p.JobCode));
db.Parameters.Add(new SqlParameter("@sCity", p.City));
db.Parameters.Add(new SqlParameter("@iStateID", p.StateID));
db.Parameters.Add(new SqlParameter("@iCountryID", p.CountryID));
db.Parameters.Add(new SqlParameter("@iEducationLevelID", p.EducationLevelID));
db.Parameters.Add(new SqlParameter("@iJobTypeID", p.JobTypeID));
db.Parameters.Add(new SqlParameter("@curMinSalary", p.MinSalary));
db.Parameters.Add(new SqlParameter("@curMaxSalary", p.MaxSalary));
db.Parameters.Add(new SqlParameter("@sJobDescription", p.Description));
db.Parameters.Add(new SqlParameter("@daPostingDate", p.PostingDate));
db.Parameters.Add(new SqlParameter("@sPostedBy", p.PostedBy));
int retval = db.ExecuteNonQuery("JobsDb_JobPostings_Update");
return retval;
}
/// <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");
}