public static bool LoadResultInOneScene(int playerID, int sceneID)
{
SqlConnection conn = DBUltils.GetDBConnection();
bool isCompleted = false;
conn.Open();
string sqlQuery = "SELECT isCompleted FROM RESULT WHERE PlayerID =" + playerID + "AND SceneID = " + sceneID;
SqlCommand cmd = new SqlCommand(sqlQuery, conn);
DbDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
int isCompletedIndex = reader.GetOrdinal("isCompleted");
isCompleted = Convert.ToBoolean(reader.GetValue(isCompletedIndex));
}
}
conn.Close();
return(isCompleted);
}
public static void LoadQADatabase(int sceneID, AnswerSet[] mainQASetSrc, AnswerSet[] subQASetSrc)
{
//Connect to sql databse
SqlConnection conn = DBUltils.GetDBConnection();
conn.Open();
//Query data
string sqlQuery = "SELECT Question,AnswerA,AnswerB,AnswerC,AnswerD,RightAnswer,isBonus FROM QUESTION WHERE SceneID = " + sceneID.ToString();
SqlCommand cmd = new SqlCommand(sqlQuery, conn);
DbDataReader reader = cmd.ExecuteReader();
int mainQACount = 0;
int subQACount = 0;
AnswerSet answerSet;
if (reader.HasRows)
{
//Read the data and add it to the mainQASetList an subQASetList
while (reader.Read())
{
int questionIndex = reader.GetOrdinal("Question");
string questionText = reader.GetString(questionIndex);
int answerAIndex = reader.GetOrdinal("AnswerA");
string answerAText = reader.GetString(answerAIndex);
int answerBIndex = reader.GetOrdinal("AnswerB");
string answerBText = reader.GetString(answerBIndex);
int answerCIndex = reader.GetOrdinal("AnswerC");
string answerCText = reader.GetString(answerCIndex);
int answerDIndex = reader.GetOrdinal("AnswerD");
string answerDText = reader.GetString(answerDIndex);
int rightAnswerIndex = reader.GetOrdinal("RightAnswer");
int rightAnswer = Convert.ToInt32(reader.GetValue(rightAnswerIndex));
int isBonusIndex = reader.GetOrdinal("isBonus");
int isBonus = Convert.ToInt32(reader.GetValue(isBonusIndex));
answerSet = new AnswerSet(questionText, answerAText, answerBText, answerCText, answerDText, rightAnswer);
if (isBonus == 0) //This is the main QA set
{
mainQASetSrc[mainQACount++] = answerSet;
}
else if (isBonus == 1) // This is the sub QA set
{
subQASetSrc[subQACount++] = answerSet;
}
}
}
conn.Close();
}
public static int LoadGameMode(int playerID)
{
int gameLife = 0;
SqlConnection conn = DBUltils.GetDBConnection();
conn.Open();
string sqlQuery = "SELECT GameLife FROM PLAYER WHERE PlayerID =" + playerID;
SqlCommand cmd = new SqlCommand(sqlQuery, conn);
DbDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
int gameLifeIndex = reader.GetOrdinal("GameLife");
gameLife = Convert.ToInt32(reader.GetValue(gameLife));
}
}
conn.Close();
return(gameLife);
}
//Load playerID base on the user name
public static int LoadPlayerID(string playerName)
{
int playerID = 0;
//Connect to sql database
SqlConnection conn = DBUltils.GetDBConnection();
conn.Open();
//Create query string
string sqlQuery = "SELECT playerID FROM PLAYER WHERE Username = '******'";
SqlCommand cmd = new SqlCommand(sqlQuery, conn);
//Query the database
DbDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
int playerIdIndex = reader.GetOrdinal("playerID");
playerID = Convert.ToInt32(reader.GetValue(playerIdIndex));
}
}
conn.Close();
return(playerID);
}
//This function will create a new game for player
public static void CreateNewGame(int playerID)
{
//@playerID int
//@score int
//@sceneID int
//@isComplete bool
//Connect to sql database
SqlConnection conn = DBUltils.GetDBConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_UpdateStatus", conn);
//Set type for command
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter playerIDPar = cmd.Parameters.Add("@playerID", SqlDbType.Int);
SqlParameter sceneIDPar = cmd.Parameters.Add("@sceneID", SqlDbType.Int);
SqlParameter scorePar = cmd.Parameters.Add("@score", SqlDbType.Int);
SqlParameter isCompleted = cmd.Parameters.Add("@isCompleted", SqlDbType.Bit);
for (int i = 0; i < 5; i++)
{
//Add parameters and set value for procedure
playerIDPar.Value = playerID;
sceneIDPar.Value = i;
scorePar.Value = 0;
isCompleted.Value = 0;
//Execute procedure
cmd.ExecuteNonQuery();
}
conn.Close();
}
public static void SaveGameInNormalScene(int playerID, int sceneID, int score, bool isCompleted)
{
//@playerID int
//@score int
//@sceneID int
//@isComplete bool
//Connect to sql database
SqlConnection conn = DBUltils.GetDBConnection();
conn.Open();
SqlCommand cmd = new SqlCommand("sp_UpdateStatus", conn);
//Set type for command
cmd.CommandType = CommandType.StoredProcedure;
//Add parameters and set value for procedure
cmd.Parameters.Add("@playerID", SqlDbType.Int).Value = playerID;
cmd.Parameters.Add("@sceneID", SqlDbType.Int).Value = sceneID;
cmd.Parameters.Add("@score", SqlDbType.Int).Value = score;
cmd.Parameters.Add("@isCompleted", SqlDbType.Bit).Value = isCompleted ? 1 : 0;
//Execute procedure
cmd.ExecuteNonQuery();
conn.Close();
}
public static void LoadResultDataBase(int playerID, bool[] isCompleted)
{
//Connect to sql database
SqlConnection conn = DBUltils.GetDBConnection();
conn.Open();
//Query data
string sqlQuery = "SELECT isCompleted FROM RESULT WHERE PlayerID = " + playerID;
SqlCommand cmd = new SqlCommand(sqlQuery, conn);
DbDataReader reader = cmd.ExecuteReader();
int isCompletedCount = 0;
if (reader.HasRows)
{
while (reader.Read())
{
int wasCompletedIndex = reader.GetOrdinal("isCompleted");
isCompleted[isCompletedCount++] = Convert.ToBoolean(reader.GetValue(wasCompletedIndex));
}
}
conn.Close();
}
public static void LoadScoreAllScenes(int playerID, ref int[] scoreScene)
{
SqlConnection conn = DBUltils.GetDBConnection();
conn.Open();
string sqlQuery = "SELECT Score, SceneID FROM RESULT WHERE PlayerID = " + playerID;
SqlCommand cmd = new SqlCommand(sqlQuery, conn);
DbDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
int sceneIDIndex = reader.GetOrdinal("SceneID");
int sceneID = Convert.ToInt32(reader.GetValue(sceneIDIndex));
int scoreIndex = reader.GetOrdinal("Score");
int score = Convert.ToInt32(reader.GetValue(scoreIndex));
if (sceneID < scoreScene.Length)
{
scoreScene[sceneID] = score;
}
}
}
conn.Close();
//Load totalscore
conn = DBUltils.GetDBConnection();
conn.Open();
sqlQuery = "SELECT TotalScore FROM PLAYER WHERE PlayerID = " + playerID;
cmd = new SqlCommand(sqlQuery, conn);
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
int totalScoreIndex = reader.GetOrdinal("TotalScore");
int totalScore = Convert.ToInt32(reader.GetValue(totalScoreIndex));
scoreScene[scoreScene.Length - 1] = totalScore;
}
}
conn.Close();
}
public static void LoadDatabase(int sceneID, AnswerSet[] Resources)
{
//Connect to sql databse
SqlConnection conn = DBUltils.GetDBConnection();
conn.Open();
Debug.Log("Connection Successfull!");
//Query data
string sqlQuery = "SELECT Question,AnswerA,AnswerB,AnswerC,AnswerD,RightAnswer FROM QUESTION WHERE SceneID = " + sceneID.ToString();
SqlCommand cmd = new SqlCommand(sqlQuery, conn);
DbDataReader reader = cmd.ExecuteReader();
int QACount = 0;
AnswerSet answerSet;
if (reader.HasRows)
{
//Read the data and add it to the mainQASetList an subQASetList
while (reader.Read())
{
int questionIndex = reader.GetOrdinal("Question");
string questionText = reader.GetString(questionIndex);
int answerAIndex = reader.GetOrdinal("AnswerA");
string answerAText = reader.GetString(answerAIndex);
int answerBIndex = reader.GetOrdinal("AnswerB");
string answerBText = reader.GetString(answerBIndex);
int answerCIndex = reader.GetOrdinal("AnswerC");
string answerCText = reader.GetString(answerCIndex);
int answerDIndex = reader.GetOrdinal("AnswerD");
string answerDText = reader.GetString(answerDIndex);
int rightAnswerIndex = reader.GetOrdinal("RightAnswer");
int rightAnswer = Convert.ToInt32(reader.GetValue(rightAnswerIndex));
answerSet = new AnswerSet(questionText, answerAText, answerBText, answerCText, answerDText, rightAnswer);
Resources[QACount++] = answerSet;
}
}
conn.Close();
}
public static void SaveGameModeToDb(int playerID, int gameLife)
{
SqlConnection conn = DBUltils.GetDBConnection();
conn.Open();
string sqlQuery = "UPDATE PLAYER SET GameLife = " + gameLife + "WHERE PlayerID = " + playerID;
SqlCommand cmd = new SqlCommand(sqlQuery, conn);
cmd.ExecuteNonQuery();
conn.Close();
}