public string[] setVolunteerServiceDataBase(CreateVolunteerService VolunteerData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "";
DataBase Base = new DataBase();
SqlConnection Sqlconn = new SqlConnection(Base.GetConnString());
using (Sqlconn)
{
try
{
Sqlconn.Open();
string sql = "UPDATE VolunteerService SET ServiceStartTime=@vServiceBeginTime, ServiceEndTime=@vServiceEndTime, ServiceHours =@vServiceHour, Remark=@vOtherContent " +
"WHERE ID=@ServicedataID ";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ServicedataID", SqlDbType.Int).Value = Chk.CheckStringFunction(VolunteerData.ID);
cmd.Parameters.Add("@vServiceBeginTime", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(VolunteerData.vsStartTime);
cmd.Parameters.Add("@vServiceEndTime", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(VolunteerData.vsEndTime);
cmd.Parameters.Add("@vServiceHour", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(VolunteerData.vsHours);
cmd.Parameters.Add("@vOtherContent", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(VolunteerData.vsContent);
returnValue[0] = cmd.ExecuteNonQuery().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message;
}
}
return returnValue;
}
public string[] createVolunteerServiceDataBase(CreateVolunteerService VolunteerData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "";
DataBase Base = new DataBase();
SqlConnection Sqlconn = new SqlConnection(Base.GetConnString());
using (Sqlconn)
{
try
{
Sqlconn.Open();
string sql = "INSERT INTO VolunteerService(VolunteerID,ServiceDate,ServiceStartTime,ServiceEndTime,ServiceHours,Remark)" +
" VALUES (@VolunteerID,@vServiceDate,@vServiceBeginTime,@vServiceEndTime,@vServiceHour,@vOtherContent)";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@VolunteerID", SqlDbType.Int).Value = Chk.CheckStringFunction(VolunteerData.vID);
cmd.Parameters.Add("@vServiceDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(VolunteerData.vsDate);
cmd.Parameters.Add("@vServiceBeginTime", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(VolunteerData.vsStartTime);
cmd.Parameters.Add("@vServiceEndTime", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(VolunteerData.vsEndTime);
cmd.Parameters.Add("@vServiceHour", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(VolunteerData.vsHours);
cmd.Parameters.Add("@vOtherContent", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(VolunteerData.vsContent);
returnValue[0] = cmd.ExecuteNonQuery().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message;
}
}
return returnValue;
}
public CreateVolunteer getVolunteerServiceData(int indexpage,string vID)
{
CreateVolunteer returnValue = this.getVolunteerData(vID);
returnValue.Service = new List<CreateVolunteerService>();
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
Sqlconn.Open();
string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) " +
"AS RowNum, * FROM VolunteerService WHERE isDeleted = 0 AND VolunteerID = @vID ) " +
"AS NewTable " +
"WHERE RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage)";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@vID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(vID);
cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage;
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CreateVolunteerService addValue = new CreateVolunteerService();
addValue.vID = dr["ID"].ToString();
addValue.vsDate = DateTime.Parse(dr["ServiceDate"].ToString()).ToString("yyyy-MM-dd");
addValue.vsStartTime = dr["ServiceStartTime"].ToString();
addValue.vsEndTime = dr["ServiceEndTime"].ToString();
addValue.vsHours = dr["ServiceHours"].ToString();
addValue.vsContent = dr["Remark"].ToString();
returnValue.Service.Add(addValue);
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
CreateVolunteerService addValue = new CreateVolunteerService();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message;
returnValue.Service.Add(addValue);
}
}
return returnValue;
}
public string[] setVolunteerServiceDataBase(CreateVolunteerService VolunteerData)
{
CaseDataBase SDB = new CaseDataBase();
if (int.Parse(SDB._StaffhaveRoles[1]) == 1)
{
return SDB.setVolunteerServiceDataBase(VolunteerData);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
