public List <CourseVO> AddCourses(CourseVO[] pCourses)
{
List <CourseVO> result = new List <CourseVO>();
try
{
AuthenticationService.CheckIfUSerIsAuthenticated();
CourseTableAdapter adapter = new CourseTableAdapter();
string logParams = "";
foreach (CourseVO vo in pCourses)
{
if (logParams.Length > 0)
{
logParams += ",\n";
}
// TODO: Fill properties here & Create "Smart Insert Stored Procedure in the dataset!
vo.ID = (int)adapter.SmartInsert(vo.Name);
result.Add(vo);
// TODO: Fill properties here
logParams += String.Format("Name:\"{0}\"", vo.Name);
}
adapter.Dispose();
LogService.Log("CourseService", "AddCourses", logParams);
}
catch (ONyRException e)
{
LogService.LogError(e.ErrorCode, e.StackTrace);
throw new FaultException <ONyRFaultException>(new ONyRFaultException((int)e.ErrorCode));
}
catch (SqlException ex)
{
string s = "Class: " + ex.Class + "\n";
for (int i = 0; i < ex.Errors.Count; i++)
{
s += "Index #" + i + "\n" +
"Message: " + ex.Errors[i].Message + "\n" +
"LineNumber: " + ex.Errors[i].LineNumber + "\n" +
"Source: " + ex.Errors[i].Source + "\n" +
"Procedure: " + ex.Errors[i].Procedure + "\n" +
"Class: " + ex.Errors[i].Class + "\n";
}
LogService.LogError(ErrorCode.UnknownError, null, s);
throw new FaultException <ONyRFaultException>(new ONyRFaultException((int)ErrorCode.DatabaseError));
}
catch (Exception e)
{
LogService.LogError(ErrorCode.UnknownError, e.StackTrace, e.Message);
throw new FaultException <ONyRFaultException>(new ONyRFaultException((int)ErrorCode.UnknownError));
}
return(result);
}
public List<KnowledgePoint> GetKnowledgePointList(string courseCode, string CourseNumber)
{
List<KnowledgePoint> list = new List<KnowledgePoint>();
KnowledgePointTableAdapter kpAdapter = new KnowledgePointTableAdapter();
KnowledgePointParentChildRelationTableAdapter kpRelationAdatper = new KnowledgePointParentChildRelationTableAdapter();
KnowledgePointAssetRelationTableAdapter kpAssetAdapter = new KnowledgePointAssetRelationTableAdapter();
CourseTableAdapter courseAdapter = new CourseTableAdapter();
var courseIdList = courseAdapter.GetIdByCourseDetails(courseCode, Convert.ToInt32(CourseNumber));
int courseId = courseIdList[0].Id;
PriorityTableAdapter priorityAdapter = new PriorityTableAdapter();
AssetTableAdapter assetAdapter = new AssetTableAdapter();
AssetTypeTableAdapter assetTypeAdapter = new AssetTypeTableAdapter();
foreach (var row in kpAdapter.GetKnowledgePointByCourseId(courseId))
{
KnowledgePoint kp = new KnowledgePoint(row.Id, courseCode + CourseNumber, row.Name, row.xPos, row.yPos);
kp.priority = (Priority)Enum.Parse(typeof(Priority), priorityAdapter.GetPriorityById(row.Priority)[0].Priority);
kp.createDate = row.CreatedTime.ToString("G");
kp.modifiedDate = row.LastModifiedTime.ToString("G");
foreach (var relation in kpRelationAdatper.GetParentIdByChildId(row.Id))
{
kp.addParent(relation.ParentKnowledgePointId);
}
foreach (var relatedAsset in kpAssetAdapter.GetAssetListByKnowledgePointId(row.Id))
{
foreach (var assetPointer in assetAdapter.GetAssetDetailById(relatedAsset.AssetId))
{
AssetType type = (AssetType)Enum.Parse(typeof(AssetType), assetTypeAdapter.GetDataById(assetPointer.AssetType)[0].AssetType);
if (type == AssetType.PDF | type == AssetType.PPT | type == AssetType.Audio | type == AssetType.Video)
{
AssetPointer pointer = new AssetPointer(assetPointer.Id, assetPointer.Path + assetPointer.AssetName, type, relatedAsset.Start, relatedAsset.End);
kp.addAsset(pointer);
}
else if (type == AssetType.SelfTest)
{
int selftestId = Convert.ToInt32(assetPointer.AssetName);
SelfTestTableAdapter stAdapter = new SelfTestTableAdapter();
var st = stAdapter.GetSelfTestDataById(Convert.ToInt32(assetPointer.AssetName))[0];
SelfTestQuesion stQuestion = new SelfTestQuesion(st.Question, st.AnswerKey);
kp.addTestQuestion(stQuestion);
}
else if (type == AssetType.QandA)
{
int messageId = Convert.ToInt32(assetPointer.AssetName);
QandATableAdapter qaAdapter = new QandATableAdapter();
var qa = qaAdapter.GetQAsById(messageId)[0];
Message message = new Message(qa.Id, "user Id: " + qa.AskedBy, qa.Question);
try
{
string answerTypeString = (new AnswerTypeTableAdapter()).GetDataById(qa.AnswerType)[0].AnswerType;
Answer answer = new Answer(answerTypeString, qa.Answer);
}
catch
{
}
finally {
message.addAnswer(new Answer(AnswerType.NoAnswer, ""));
}
message.createTime = qa.CreatedTime.ToString("G");
kp.messageBox.lastModifiedDate = qa.CreatedTime.ToString("G");
kp.addMessage(message);
}
}
}
kp.createDate = row.CreatedTime.ToString("G");
kp.modifiedDate = row.LastModifiedTime.ToString("G");
list.Add(kp);
}
return list;
}
public string insertNewMessage(string courseCode, string courseNumber, string userId, string message)
{
QandATableAdapter qaAdapter = new QandATableAdapter();
CourseTableAdapter courseAdapter = new CourseTableAdapter();
AssetTableAdapter assetAdapter = new AssetTableAdapter();
var table = courseAdapter.GetIdByCourseDetails(courseCode, Convert.ToInt32(courseNumber));
if (table.Count > 0)
{
qaAdapter.InsertNewQuestion(message, Convert.ToInt32(userId), null, null, DateTime.Now, null, Convert.ToInt32(table[0].Id));
var qaTable = qaAdapter.GetMessageListByCourseId(Convert.ToInt32(table[0].Id));
int qaID = qaTable[qaTable.Count-1].Id;
assetAdapter.InsertAsset(qaID.ToString(), qaID.ToString(), 3104);
return "success";
}
return "failed";
}
public string insertAnswerToMessage(string courseCode, string courseNumber, string messageId, string answer, string answerTypeId)
{
QandATableAdapter qaAdapter = new QandATableAdapter();
CourseTableAdapter courseAdapter = new CourseTableAdapter();
var table = courseAdapter.GetIdByCourseDetails(courseCode, Convert.ToInt32(courseNumber));
if (table.Count > 0)
{
if (qaAdapter.GetQAsById(Convert.ToInt32(messageId)).Count > 0)
{
qaAdapter.AddAnswerToQuestion(answer, Convert.ToInt32(answerTypeId), DateTime.Now, Convert.ToInt32(messageId));
return "success";
}
}
return "failed";
}
public MessageBox GetMessageBox(string courseCode, string courseNumber)
{
MessageBox messageBox = new MessageBox(courseCode + courseNumber, "");
QandATableAdapter qaAdapter = new QandATableAdapter();
CourseTableAdapter courseAdapter = new CourseTableAdapter();
var courseIdList = courseAdapter.GetIdByCourseDetails(courseCode, Convert.ToInt32(courseNumber));
int courseId = courseIdList[0].Id;
var qaTable = qaAdapter.GetMessageListByCourseId(courseId);
AnswerTypeTableAdapter answerTypeAdapter = new AnswerTypeTableAdapter();
UserDataTableAdapter userAdapter = new UserDataTableAdapter();
foreach (var row in qaTable)
{
int userId = row.AskedBy;
string username = userAdapter.GetUserDataById(userId)[0].Username;
Message message = new Message(username, row.Question);
message.createTime = row.CreatedTime.ToString("G");
message.messageId = row.Id;
try
{
Answer answer = new Answer(row.AnswerType, row.Answer);
message.addAnswer(answer);
}
catch (StrongTypingException e)
{
}
messageBox.addMessage(message);
}
return messageBox;
}
public List <CourseVO> ModifyCourses(CourseVO[] pOriginalCourses, CourseVO[] pNewCourses, bool isForced = false)
{
List <CourseVO> result = new List <CourseVO>();
try
{
AuthenticationService.CheckIfUSerIsAuthenticated();
CourseTableAdapter adapter = new CourseTableAdapter();
string logParams = "";
bool modifyConflict = false;
for (int i = 0; i < Math.Min(pOriginalCourses.Length, pNewCourses.Length); ++i)
{
ONyRDataSet.CourseRow row = adapter.GetDataByID(pNewCourses[i].ID)[0];
// TODO: Fill properties here
// TODO: Watch out for null values!
CourseVO vo = new CourseVO(row.ID);
if (vo.Equals(pOriginalCourses[i]) || isForced)
{
if (logParams.Length > 0)
{
logParams += ",\n";
}
result.Add(vo);
// TODO: Fill properties here
adapter.Update(pNewCourses[i].Name, vo.ID);
logParams += String.Format("Name:\"{0}\" => \"{1}\"", vo.Name, pNewCourses[i].Name);
}
else
{
modifyConflict = true;
}
}
adapter.Dispose();
LogService.Log("CourseService", "ModifyCourses", logParams);
if (modifyConflict)
{
throw new ONyRException(ErrorCode.ModifyConflict);
}
}
catch (ONyRException e)
{
LogService.LogError(e.ErrorCode, e.StackTrace);
throw new FaultException <ONyRFaultException>(new ONyRFaultException((int)e.ErrorCode));
}
catch (SqlException ex)
{
string s = "Class: " + ex.Class + "\n";
for (int i = 0; i < ex.Errors.Count; i++)
{
s += "Index #" + i + "\n" +
"Message: " + ex.Errors[i].Message + "\n" +
"LineNumber: " + ex.Errors[i].LineNumber + "\n" +
"Source: " + ex.Errors[i].Source + "\n" +
"Procedure: " + ex.Errors[i].Procedure + "\n" +
"Class: " + ex.Errors[i].Class + "\n";
}
LogService.LogError(ErrorCode.UnknownError, null, s);
throw new FaultException <ONyRFaultException>(new ONyRFaultException((int)ErrorCode.DatabaseError));
}
catch (Exception e)
{
LogService.LogError(ErrorCode.UnknownError, e.StackTrace, e.Message);
throw new FaultException <ONyRFaultException>(new ONyRFaultException((int)ErrorCode.UnknownError));
}
return(result);
}
public List <CourseVO> LoadCourses(CourseFilter pFilter, int pId = -1, int[] pIds = null)
{
List <CourseVO> result = null;
try
{
AuthenticationService.CheckIfUSerIsAuthenticated();
LogService.Log("CourseService", "LoadCourses", String.Format("Filter:{0}; ID:{1}; IDs: {2}", pFilter, pId, pIds));
CourseTableAdapter adapter = new CourseTableAdapter();
ONyRDataSet.CourseDataTable table;
result = new List <CourseVO>();
if (pFilter == CourseFilter.All)
{
table = adapter.GetData();
}
else if (pFilter == CourseFilter.ById)
{
table = adapter.GetDataByID(pId);
}
else if (pFilter == CourseFilter.ByIds)
{
table = adapter.GetData();
}
else
{
throw new ONyRException(ErrorCode.UnknownError);
}
for (int i = 0; i < table.Count; ++i)
{
if (pFilter != CourseFilter.ByIds || pIds.Contains(table[i].ID))
{
// TODO: Fill properties here
// TODO: try-catch block for every Nullable field!
result.Add(new CourseVO(table[i].ID, table[i].Name));
}
}
adapter.Dispose();
}
catch (ONyRException e)
{
LogService.LogError(e.ErrorCode, e.StackTrace);
throw new FaultException <ONyRFaultException>(new ONyRFaultException((int)e.ErrorCode));
}
catch (SqlException ex)
{
string s = "Class: " + ex.Class + "\n";
for (int i = 0; i < ex.Errors.Count; i++)
{
s += "Index #" + i + "\n" +
"Message: " + ex.Errors[i].Message + "\n" +
"LineNumber: " + ex.Errors[i].LineNumber + "\n" +
"Source: " + ex.Errors[i].Source + "\n" +
"Procedure: " + ex.Errors[i].Procedure + "\n" +
"Class: " + ex.Errors[i].Class + "\n";
}
LogService.LogError(ErrorCode.UnknownError, null, s);
throw new FaultException <ONyRFaultException>(new ONyRFaultException((int)ErrorCode.DatabaseError));
}
catch (Exception e)
{
LogService.LogError(ErrorCode.UnknownError, e.StackTrace, e.Message);
throw new FaultException <ONyRFaultException>(new ONyRFaultException((int)ErrorCode.UnknownError));
}
return(result);
}
/// <summary>
/// This method works with the strong typed DataSet
/// </summary>
private static void QueryDBByStrongTypedDataSet()
{
Console.WriteLine("Use LINQ to query strong typed DataSet...");
/////////////////////////////////////////////////////////////////////
// Fill the strong typed DataSet && Insert data into database
//
// Create a strong typed DataSet object
SQLServer2005DBDataSet dsSQLServer = new SQLServer2005DBDataSet();
// Create the PersonTableAdapter and fill the Person DataTable
PersonTableAdapter taPerson = new PersonTableAdapter();
SQLServer2005DBDataSet.PersonDataTable tblPerson = dsSQLServer.
Person;
taPerson.Fill(tblPerson);
// Create a new row into the Person DataTable
// We don't set the PersonCategory value because we have set the
// default value property of the DataSet PersonCategory column.
SQLServer2005DBDataSet.PersonRow rowPerson = tblPerson.NewPersonRow();
rowPerson.LastName = "Sun";
rowPerson.FirstName = "Hongye";
rowPerson.SetHireDateNull();
rowPerson.EnrollmentDate = DateTime.Now;
rowPerson.Picture = ReadImage(@"MSDN.jpg");
tblPerson.AddPersonRow(rowPerson);
// Update the database
taPerson.Update(tblPerson);
// Use PersonTableAdapter to insert new record into the database
// directly
taPerson.Insert("Ge", "Jialiang", DateTime.Now, null,
ReadImage(@"MSDN.jpg"), 2);
// Create the CourseTableAdapter and fill the Course DataTable
CourseTableAdapter taCourse = new CourseTableAdapter();
SQLServer2005DBDataSet.CourseDataTable tblCourse = dsSQLServer.Course;
taCourse.Fill(tblCourse);
// Create the CourseGradeTableAdapter and fill the CourseGrade
// DataTable
CourseGradeTableAdapter taCourseGrade = new CourseGradeTableAdapter();
SQLServer2005DBDataSet.CourseGradeDataTable tblCourseGrade =
dsSQLServer.CourseGrade;
taCourseGrade.Fill(tblCourseGrade);
/////////////////////////////////////////////////////////////////////
// Perform the query operation in one DataTable
//
Console.WriteLine("\nQuery people whose first name is Roger:");
// Person the query
var query = from p in tblPerson.AsEnumerable()
where p.FirstName == "Roger"
select p;
// Display the query results
foreach (var p in query)
{
Console.WriteLine("ID = {0}, Name = {1} {2}", p.PersonID,
p.FirstName, p.LastName);
}
/////////////////////////////////////////////////////////////////////
// Perform the query operation across multiple DataTables
//
Console.WriteLine("\nQuery the max grade of each course:");
// Perform the query and get a collection of hte anonymous type,
// new { int CourseID, string Title, decimal TopGrade }
var courses = from grade in tblCourseGrade.AsEnumerable()
group grade by grade.CourseID into g
join cur in tblCourse.AsEnumerable()
on g.Key equals cur.CourseID
select new
{
CourseID = g.Key,
Title = cur.Title,
TopGrade = g.Max(gra => gra.IsGradeNull() ?
decimal.Zero : gra.Grade)
};
// Display the query results
foreach (var c in courses)
{
Console.WriteLine("Course = {0}, TopGrade = {1}", c.Title,
c.TopGrade);
}
/////////////////////////////////////////////////////////////////////
// Perform the query operation across multiple related DataTables
//
Console.WriteLine("\nQuery all grades that Nino got:");
// Perform the query betweem related DataTables and get the anonymous
// type, new { PersonRow Person, CourseRow Course, CourseGradeRow
// CourseGrade}
var grades = from per in tblPerson.AsEnumerable()
from gra in per.GetCourseGradeRows()
join cur in tblCourse.AsEnumerable()
on gra.CourseID equals cur.CourseID
where per.FirstName == "Nino"
select new
{
Person = per,
Course = cur,
CourseGrade = gra
};
// Display the query results
foreach (var grade in grades)
{
Console.WriteLine("FirstName = {0}, Course= {1}, Grade= {2}",
grade.Person.FirstName, grade.Course.Title,
grade.CourseGrade.Grade);
}
}
/// <summary>
/// Insert and Select data using Strong Typed DataSet
/// </summary>
private static void InsertSelectUsingStrongTypedDataSet()
{
Console.WriteLine("\r\nInsert and Select data using Strong Typed "
+ "DataSet...");
// 1. Create a Strong Typed DataSet object and fill its corresponding
// data tables
// Create a Strong Typed DataSet object
SQLServer2005DBDataSet dsSQLServer = new SQLServer2005DBDataSet();
// Use the PersonTableAdapter to fill the Person table
PersonTableAdapter taPerson = new PersonTableAdapter();
SQLServer2005DBDataSet.PersonDataTable tblPerson = dsSQLServer.
Person;
taPerson.Fill(tblPerson);
// Use the CourseTableAdapter to fill the Course table
CourseTableAdapter taCourse = new CourseTableAdapter();
SQLServer2005DBDataSet.CourseDataTable tblCourse = dsSQLServer.
Course;
taCourse.Fill(tblCourse);
// Use the DepartmentTableAdapter to fill the Department table
DepartmentTableAdapter taDepartment = new DepartmentTableAdapter();
SQLServer2005DBDataSet.DepartmentDataTable tblDepartment =
dsSQLServer.Department;
taDepartment.Fill(tblDepartment);
// 2. Insert a record into the Person table
//--- (Strong Typed DataSet)
// We don't set the PersonCategory value because we have set the
// default value property of the DataSet PersonCategory column.
SQLServer2005DBDataSet.PersonRow addRowPerson = tblPerson.
NewPersonRow();
addRowPerson.LastName = "Ge";
addRowPerson.FirstName = "Jialiang";
addRowPerson.SetHireDateNull();
addRowPerson.EnrollmentDate = DateTime.Now;
addRowPerson.Picture = ReadImage(@"MSDN.jpg");
tblPerson.AddPersonRow(addRowPerson);
// ---Insert a record into the Person table
// ---(Untyped DataSet)
//DataRow addRowPerson = tblPerson.NewRow();
//addRowPerson["LastName"] = "Ge";
//addRowPerson["FirstName"] = "Jialiang";
//addRowPerson["HireDate"] = DBNull.Value;
//addRowPerson["EnrollmentDate"] = DateTime.Now;
//addRowPerson["Picture"] = ReadImage(@"MSDN.jpg");
//tblPerson.Rows.Add(addRowPerson);
// Update the Person table
taPerson.Update(tblPerson);
// 3. Insert a record into the Person table using PersionTableAdapter
taPerson.Insert("Sun", "Hongye", DateTime.Now, null,
ReadImage(@"MSDN.jpg"), 2);
// 4. Find a certain record in the Person table by primary key
// ---(Strong Typed DataSet)
SQLServer2005DBDataSet.PersonRow findRowPerson = tblPerson.
FindByPersonID(1);
// ---Find a certain record in the Person table by primary key
// ---(Untyped DataSet)
//DataRow findRowPerson = tblPerson.Rows.Find(1);
// Display the result record
if (findRowPerson != null)
{
// Use IsColumnNameNull method to check the DBNull value
Console.WriteLine("{0}\t{1} {2}\t{3}", findRowPerson.PersonID,
findRowPerson.FirstName, findRowPerson.LastName,
findRowPerson.IsEnrollmentDateNull() ? "(DBNull)" :
findRowPerson.EnrollmentDate.ToShortDateString());
}
// 5. Display data in two related data tables
foreach (SQLServer2005DBDataSet.DepartmentRow rowDepartment in
dsSQLServer.Department)
{
Console.WriteLine("\r\nCourses for Department({0})",
rowDepartment.DepartmentID);
foreach (SQLServer2005DBDataSet.CourseRow rowCourse
in rowDepartment.GetCourseRows())
{
Console.WriteLine("{0} - {1}", rowCourse.CourseID,
rowCourse.Title);
}
}
}
