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); }