public List <QuestionMaster> GetByGroup(int GroupId, Connection con) { Command cmd = new Command(); string Query = @"SELECT * FROM Question_Master WHERE QuestionGroup=@QuestionGroup AND Active=1; SELECT * FROM Question_Options QO WHERE QO.Qid IN(SELECT Q.Qid FROM Question_Master Q WHERE QuestionGroup=@QuestionGroup and Q.Active=1) and QO.Active=1"; cmd.Parameters.Add("@QuestionGroup", DBType.Int, GroupId); cmd.CommandText = Query; cmd.CommandType = DBConnection.CommandType.Text; con.cmd = cmd; DataSet DS = con.getDataSet(); List <QuestionMaster> obj = new List <QuestionMaster>(); QuestionOptions q = new QuestionOptions(); List <QuestionOptions> op = q.GetData(DS.Tables[1]); foreach (DataRow DR in DS.Tables[0].Rows) { obj.Add(new QuestionMaster(DR, op.FindAll(r => r.Qid == (long)DR["Qid"]))); } return(obj); }
public List <QuestionOptions> GetData(DataTable DT) { List <QuestionOptions> qo = new List <QuestionOptions>(); foreach (DataRow DR in DT.Rows) { QuestionOptions _qo = new QuestionOptions(); _qo.Oid = Convert.ToInt64(DR["Oid"]); _qo.Qid = Convert.ToInt64(DR["Qid"]); _qo.SrNo = Convert.ToString(DR["SrNo"]); _qo.Answer = Convert.ToString(DR["Answer"]); _qo.Active = Convert.ToInt32(DR["Active"]); _qo.CreatedDate = Convert.ToDateTime(DR["CreatedDate"]); _qo.CreatedBy = Convert.ToInt64(DR["CreatedBy"]); qo.Add(_qo); } return(qo); }
public QuestionMaster Create(QuestionMaster QM, Connection con) { if (con == null) { con = new Connection(); } Command cmd = new Command(); string query1 = ""; string query2 = ""; if (string.IsNullOrEmpty(QM.Question)) { throw new Exception("Question is required"); } else { query1 += "Question,"; query2 += "@Question,"; cmd.Parameters.Add("@Question", DBType.VarChar, QM.Question); } if (QM.QuestionGroup == 0) { throw new Exception("QuestionGroup is required"); } else { query1 += "QuestionGroup,"; query2 += "@QuestionGroup,"; cmd.Parameters.Add("@QuestionGroup", DBType.Int, QM.QuestionGroup); } query1 += "SrNo,"; query2 += "@SrNo,"; cmd.Parameters.Add("@SrNo", DBType.Int, QM.SrNo); if (QM.Active == 0) { throw new Exception("Active is required"); } else { query1 += "Active,"; query2 += "@Active,"; cmd.Parameters.Add("@Active", DBType.Int, QM.Active); } if (string.IsNullOrEmpty(QM.QLanguage)) { throw new Exception("QLanguage is required"); } else { query1 += "QLanguage,"; query2 += "@QLanguage,"; cmd.Parameters.Add("@QLanguage", DBType.VarChar, QM.QLanguage); } if (!string.IsNullOrEmpty(QM.QuestionType)) { query1 += "QuestionType,"; query2 += "@QuestionType,"; cmd.Parameters.Add("@QuestionType", DBType.VarChar, QM.QuestionType); } if (string.IsNullOrEmpty(QM.CurrectAnswer)) { throw new Exception("CurrectAnswer is required"); } else { query1 += "CurrectAnswer,"; query2 += "@CurrectAnswer,"; cmd.Parameters.Add("@CurrectAnswer", DBType.VarChar, QM.CurrectAnswer); } if (string.IsNullOrEmpty(QM.AnswerDetails)) { //throw new Exception("AnswerDetails is required"); } else { query1 += "AnswerDetails,"; query2 += "@AnswerDetails,"; cmd.Parameters.Add("@AnswerDetails", DBType.VarChar, QM.AnswerDetails); } if (QM.CreatedDate == Convert.ToDateTime("1900-01-01")) { throw new Exception("CreatedDate is required"); } else { query1 += "CreatedDate,"; query2 += "@CreatedDate,"; cmd.Parameters.Add("@CreatedDate", DBType.DateTime, QM.CreatedDate); } query1 += "CreatedBy,"; query2 += "@CreatedBy,"; cmd.Parameters.Add("@CreatedBy", DBType.Int, QM.CreatedBy); string Query = "INSERT INTO Question_Master(" + query1.TrimEnd(',') + ") VALUES (" + query2.TrimEnd(',') + ") ;" + (con.ServerType == DBServerType.MYSQL ? " SELECT last_insert_id(); " : "SELECT @@IDENTITY"); cmd.CommandText = Query; cmd.CommandType = DBConnection.CommandType.Text; con.cmd = cmd; DataTable DT = con.getDataTable(); QM.Qid = Convert.ToInt32(DT.Rows[0][0]); QuestionOptions qo = new QuestionOptions(); qo.Create(QM.Options, QM.Qid, con); return(QM); }
public QuestionOptions Create(QuestionOptions QO, Connection con) { if (con == null) { con = new Connection(); } Command cmd = new Command(); string query1 = ""; string query2 = ""; if (QO.Qid == 0) { throw new Exception("Qid is required"); } else { query1 += "Qid,"; query2 += "@Qid,"; cmd.Parameters.Add("@Qid", DBType.Int, QO.Qid); } if (string.IsNullOrEmpty(QO.SrNo)) { throw new Exception("SrNo is required"); } else { query1 += "SrNo,"; query2 += "@SrNo,"; cmd.Parameters.Add("@SrNo", DBType.VarChar, QO.SrNo); } if (string.IsNullOrEmpty(QO.Answer)) { throw new Exception("Answer is required"); } else { query1 += "Answer,"; query2 += "@Answer,"; cmd.Parameters.Add("@Answer", DBType.VarChar, QO.Answer); } if (QO.Active == 0) { throw new Exception("Active is required"); } else { query1 += "Active,"; query2 += "@Active,"; cmd.Parameters.Add("@Active", DBType.Int, QO.Active); } if (QO.CreatedDate == Convert.ToDateTime("1900-01-01")) { throw new Exception("CreatedDate is required"); } else { query1 += "CreatedDate,"; query2 += "@CreatedDate,"; cmd.Parameters.Add("@CreatedDate", DBType.DateTime, QO.CreatedDate); } query1 += "CreatedBy,"; query2 += "@CreatedBy,"; cmd.Parameters.Add("@CreatedBy", DBType.Int, QO.CreatedBy); string Query = "INSERT INTO Question_Options(" + query1.TrimEnd(',') + ") VALUES (" + query2.TrimEnd(',') + ") ;" + (con.ServerType == DBServerType.MYSQL ? " SELECT last_insert_id(); " : "SELECT @@IDENTITY"); cmd.CommandText = Query; cmd.CommandType = DBConnection.CommandType.Text; con.cmd = cmd; DataTable DT = con.getDataTable(); QO.Oid = Convert.ToInt32(DT.Rows[0][0]); return(QO); }