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