public static IDbConnection GetConnection(RepositoryDbProvider dbProvider, string connectionString)
{
IDbConnection connection;
switch (dbProvider)
{
case RepositoryDbProvider.MariaDb:
connection = MySqlUtility.GetConnection(connectionString);
break;
case RepositoryDbProvider.PostgreSql:
connection = PgSqlUtility.GetConnection(connectionString);
break;
case RepositoryDbProvider.Oracle:
connection = OracleUtility.NewConnection(connectionString);
break;
default:
connection = MySqlUtility.GetConnection(connectionString);
break;
}
return(connection);
}
public InactiveSubject GetInActiveSubject(string subjectId)
{
using (var con = new OracleConnection(OracleUtility.conString))
{
con.Open();
var cmd = con.CreateCommand();
cmd.CommandText = $"SELECT * FROM {OracleDbTableName.InactiveSubject} WHERE Id=:Id";
cmd.Parameters.Add("Id", subjectId);
cmd.CommandType = CommandType.Text;
var da = new OracleDataAdapter();
da.SelectCommand = cmd;
var ds = new DataSet();
da.Fill(ds);
var dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
return(OracleUtility.GetInactiveSubject(dt.Rows[0]));
}
return(null);
}
}
public IEnumerable <InactiveSubject> ListInActiveSubject(string siteId)
{
using (var con = new OracleConnection(OracleUtility.conString))
{
con.Open();
var cmd = con.CreateCommand();
cmd.CommandText = $"SELECT * FROM {OracleDbTableName.InactiveSubject} WHERE SiteId=:SiteId";
cmd.Parameters.Add("SiteId", siteId);
cmd.CommandType = CommandType.Text;
var da = new OracleDataAdapter();
da.SelectCommand = cmd;
var ds = new DataSet();
da.Fill(ds);
var dt = ds.Tables[0];
var subjects = Enumerable.Empty <InactiveSubject>().ToList();
for (int i = 0; i < dt.Rows.Count; i++)
{
subjects.Add(OracleUtility.GetInactiveSubject(dt.Rows[i]));
}
return(subjects);
}
}
public IEnumerable <QuestionSuiteVM> GetAllQuestionSuiteBySubjectId(string SubjectId)
{
using (var con = new OracleConnection(OracleUtility.conString))
{
con.Open();
var cmd = con.CreateCommand();
cmd.CommandText = $"SELECT * FROM {OracleDbTableName.QuestionSuite} WHERE SubjectId=:SubjectId";
cmd.Parameters.Add("SubjectId", SubjectId);
cmd.CommandType = CommandType.Text;
var da = new OracleDataAdapter();
da.SelectCommand = cmd;
var ds = new DataSet();
da.Fill(ds);
var dt = ds.Tables[0];
var questionSuites = Enumerable.Empty <QuestionSuiteVM>().ToList();
for (int i = 0; i < dt.Rows.Count; i++)
{
questionSuites.Add(OracleUtility.GetQuestionSuite(dt.Rows[i]));
}
foreach (var questionSuite in questionSuites)
{
var cmd2 = con.CreateCommand();
cmd2.CommandText = $"SELECT * FROM {OracleDbTableName.Question} WHERE QuestionSuiteId=:QuestionSuiteId";
cmd2.Parameters.Add("QuestionSuiteId", questionSuite._id);
cmd2.CommandType = CommandType.Text;
var da2 = new OracleDataAdapter();
da2.SelectCommand = cmd2;
ds = new DataSet();
da2.Fill(ds);
dt = ds.Tables[0];
var questions = Enumerable.Empty <MultipleChoiceQuestionWithOneCorrectAnswer>().ToList();
for (int i = 0; i < dt.Rows.Count; i++)
{
questions.Add(OracleUtility.GetQuestion(dt.Rows[i]));
}
questionSuite.Questions = questions;
foreach (var question in questions)
{
var cmd3 = con.CreateCommand();
cmd3.CommandText = $"SELECT * FROM {OracleDbTableName.Choice} WHERE QuestionId=:QuestionId";
cmd3.Parameters.Add("QuestionId", question._id);
cmd3.CommandType = CommandType.Text;
var da3 = new OracleDataAdapter();
da3.SelectCommand = cmd3;
ds = new DataSet();
da3.Fill(ds);
dt = ds.Tables[0];
var choices = Enumerable.Empty <SelectableChoice>().ToList();
for (int i = 0; i < dt.Rows.Count; i++)
{
choices.Add(OracleUtility.GetChoice(dt.Rows[i]));
}
question.Choices = choices;
}
}
return(questionSuites);
}
}
