public TrainingTransfer GeTrainingDetailData(int trainingId)
{
TrainingTransfer trainingDetail = new TrainingTransfer();
TrainingAccess training = new TrainingAccess();
List <DocumentAccess> documentList = new List <DocumentAccess>();
ConnectDB db = new ConnectDB();
SqlCommand cmd;
SqlDataReader dr;
cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
//select data from TrainingModule table according trainingId
cmd.CommandText = "select * from TrainingModule WHERE trainingId = @trainingId;";
cmd.Parameters.AddWithValue("@trainingId", trainingId);
dr = db.searchData(cmd);
if (dr.HasRows)
{
while (dr.Read())
{
if (System.DBNull.Value.Equals(dr[0]))
{
training.TrainingId = 0;
}
else
{
training.TrainingId = Convert.ToInt32(dr[0]);
}
if (System.DBNull.Value.Equals(dr[1]))
{
training.TrainingName = null;
}
else
{
training.TrainingName = Convert.ToString(dr[1]);
}
if (System.DBNull.Value.Equals(dr[2]))
{
training.Description = null;
}
else
{
training.Description = Convert.ToString(dr[2]);
}
if (System.DBNull.Value.Equals(dr[3]))
{
training.Type = null;
}
else
{
training.Type = Convert.ToString(dr[3]);
}
if (System.DBNull.Value.Equals(dr[4]))
{
training.ModuleId = 0;
}
else
{
training.ModuleId = Convert.ToInt32(dr[4]);
}
}
}
if ((dr != null) && (!dr.IsClosed))
{
dr.Close();
}
trainingDetail.Training = training;
//select document list from database
cmd.CommandText = "SELECT documentId, name, type, uploadDate, description, size, trainingId FROM Document WHERE trainingId = @trainingDetailId";
cmd.Parameters.AddWithValue("@trainingDetailId", trainingId);
dr = db.searchData(cmd);
if (dr.HasRows)
{
while (dr.Read())
{
DocumentAccess document = new DocumentAccess();
if (System.DBNull.Value.Equals(dr[0]))
{
document.DocumentId = 0;
}
else
{
document.DocumentId = Convert.ToInt32(dr[0]);
}
if (System.DBNull.Value.Equals(dr[1]))
{
document.DocumentName = null;
}
else
{
document.DocumentName = Convert.ToString(dr[1]);
}
if (System.DBNull.Value.Equals(dr[2]))
{
document.Type = null;
}
else
{
document.Type = Convert.ToString(dr[2]);
}
if (System.DBNull.Value.Equals(dr[3]))
{
document.UploadDate = default(DateTime);
}
else
{
document.UploadDate = Convert.ToDateTime(dr[3]);
}
if (System.DBNull.Value.Equals(dr[4]))
{
document.Description = null;
}
else
{
document.Description = Convert.ToString(dr[4]);
}
if (System.DBNull.Value.Equals(dr[5]))
{
document.Size = 0;
}
else
{
document.Size = Convert.ToInt32(dr[5]);
}
if (System.DBNull.Value.Equals(dr[6]))
{
document.TrainingId = 0;
}
else
{
document.TrainingId = Convert.ToInt32(dr[6]);
}
documentList.Add(document);
}
}
if ((dr != null) && (!dr.IsClosed))
{
dr.Close();
}
db.Close();
trainingDetail.DocumentList = documentList;
return(trainingDetail);
}
public List <DocumentAccess> GetDocumentData()
{
List <DocumentAccess> documentList = new List <DocumentAccess>();
ConnectDB db = new ConnectDB();
SqlCommand cmd;
SqlDataReader dr;
cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from Document;";
dr = db.searchData(cmd);
if (dr.HasRows)
{
while (dr.Read())
{
DocumentAccess document = new DocumentAccess();
if (System.DBNull.Value.Equals(dr[0]))
{
document.DocumentId = 0;
}
else
{
document.DocumentId = Convert.ToInt32(dr[0]);
}
if (System.DBNull.Value.Equals(dr[1]))
{
document.DocumentName = null;
}
else
{
document.DocumentName = Convert.ToString(dr[1]);
}
if (System.DBNull.Value.Equals(dr[2]))
{
document.Type = null;
}
else
{
document.Type = Convert.ToString(dr[2]);
}
if (System.DBNull.Value.Equals(dr[3]))
{
document.UploadDate = new DateTime();
}
else
{
document.UploadDate = Convert.ToDateTime(dr[3]);
}
if (System.DBNull.Value.Equals(dr[4]))
{
document.Description = null;
}
else
{
document.Description = Convert.ToString(dr[4]);
}
if (System.DBNull.Value.Equals(dr[5]))
{
document.Size = 0;
}
else
{
document.Size = Convert.ToInt32(dr[5]);
}
if (System.DBNull.Value.Equals(dr[6]))
{
document.TrainingId = 0;
}
else
{
document.TrainingId = Convert.ToInt32(dr[6]);
}
documentList.Add(document);
}
}
if ((dr != null) && (!dr.IsClosed))
{
dr.Close();
}
db.Close();
return(documentList);
}
public bool Update(DocumentAccess document)
{
ConnectDB db = new ConnectDB();
SqlCommand cmd;
cmd = new SqlCommand("UpdateDocument");
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter RetVal = cmd.Parameters.Add("RetVal", SqlDbType.Int);
RetVal.Direction = ParameterDirection.ReturnValue;
SqlParameter documentId = cmd.Parameters.Add("@documentId", SqlDbType.Int);
documentId.Direction = ParameterDirection.Input;
SqlParameter name = cmd.Parameters.Add("@name", SqlDbType.VarChar, 255);
name.Direction = ParameterDirection.Input;
SqlParameter type = cmd.Parameters.Add("@type", SqlDbType.VarChar, 35);
type.Direction = ParameterDirection.Input;
SqlParameter uploadDate = cmd.Parameters.Add("@uploadDate", SqlDbType.DateTime);
uploadDate.Direction = ParameterDirection.Input;
SqlParameter description = cmd.Parameters.Add("@description", SqlDbType.VarChar, 255);
description.Direction = ParameterDirection.Input;
SqlParameter size = cmd.Parameters.Add("@size", SqlDbType.Int);
size.Direction = ParameterDirection.Input;
SqlParameter trainingId = cmd.Parameters.Add("@trainingId", SqlDbType.Int);
trainingId.Direction = ParameterDirection.Input;
if (document.DocumentId == 0)
{
return(false);
}
documentId.Value = document.DocumentId;
if (document.DocumentName != null)
{
name.Value = document.DocumentName;
}
if (document.Type != null)
{
type.Value = document.Type;
}
if (document.UploadDate != null)
{
uploadDate.Value = document.UploadDate;
}
if (document.Description != null)
{
description.Value = document.Description;
}
if (document.Size != 0)
{
size.Value = document.Size;
}
if (document.TrainingId != 0)
{
trainingId.Value = document.TrainingId;
}
db.changeData(cmd);
db.Close();
if (Int32.Parse(RetVal.Value.ToString()) == 0)
{
return(true);
}
else
{
return(false);
}
}
public List <DocumentAccess> Search(string key = null)
{
List <DocumentAccess> documentList = new List <DocumentAccess>();
ConnectDB db = new ConnectDB();
SqlCommand cmd;
SqlDataReader dr;
cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
if (key == null)
{
cmd.CommandText = "SELECT documentId, doc.name AS documentName, doc.type, uploadDate, doc.description, size, doc.trainingId, trainingName "
+ "FROM Document AS doc JOIN TrainingModule AS train ON doc.trainingId = train.trainingId;";
}
else
{
cmd.CommandText = "SELECT documentId, doc.name AS documentName, doc.type, uploadDate, doc.description, size, doc.trainingId, trainingName "
+ "FROM Document AS doc JOIN TrainingModule AS train ON doc.trainingId = train.trainingId WHERE doc.name LIKE @documentName;";
cmd.Parameters.Add("@documentName", SqlDbType.VarChar).Value = "%" + key + "%";
}
dr = db.searchData(cmd);
if (dr.HasRows)
{
while (dr.Read())
{
DocumentAccess document = new DocumentAccess();
if (System.DBNull.Value.Equals(dr[0]))
{
document.DocumentId = 0;
}
else
{
document.DocumentId = Convert.ToInt32(dr[0]);
}
if (System.DBNull.Value.Equals(dr[1]))
{
document.DocumentName = null;
}
else
{
document.DocumentName = Convert.ToString(dr[1]);
}
if (System.DBNull.Value.Equals(dr[2]))
{
document.Type = null;
}
else
{
document.Type = Convert.ToString(dr[2]);
}
if (System.DBNull.Value.Equals(dr[3]))
{
document.UploadDate = new DateTime();
}
else
{
document.UploadDate = Convert.ToDateTime(dr[3]);
}
if (System.DBNull.Value.Equals(dr[4]))
{
document.Description = null;
}
else
{
document.Description = Convert.ToString(dr[4]);
}
if (System.DBNull.Value.Equals(dr[5]))
{
document.Size = 0;
}
else
{
document.Size = Convert.ToInt32(dr[5]);
}
if (System.DBNull.Value.Equals(dr[6]))
{
document.TrainingId = 0;
}
else
{
document.TrainingId = Convert.ToInt32(dr[6]);
}
if (System.DBNull.Value.Equals(dr[7]))
{
document.TrainingName = null;
}
else
{
document.TrainingName = Convert.ToString(dr[7]);
}
documentList.Add(document);
}
}
if ((dr != null) && (!dr.IsClosed))
{
dr.Close();
}
db.Close();
return(documentList);
}
