public DataSet GetAll(string UserName, string Pass)
{
DataSet ds;
SqlConnection conn = null;
try
{
conn = new SqlConnection(DataBase.CONN);
conn.Open();
GWSiteStatusEnum status = DataBase.ValidateUser(conn, null, UserName, Pass);
if (status != GWSiteStatusEnum.OK)
{
return(null);
}
ds = DataBase.GetAllByTable(conn, "Players");
return(ds);
}
catch (SqlException ex)
{
//
// tratar a excepção!!!!
System.Console.WriteLine("EXCEPTION Method<GetAll> Class'Player': " + ex.Message);
return(null);
}
finally
{
conn.Close();
}
}
public DataSet SearchWord(string UserName, string Pass, string word)
{
DataSet ds;
SqlConnection conn = null;
try
{
conn = new SqlConnection(DataBase.CONN);
conn.Open();
GWSiteStatusEnum status = DataBase.ValidateUser(conn, null, UserName, Pass);
if (status != GWSiteStatusEnum.OK)
{
return(null);
}
ds = DataBase.SearchAllTables(word);
return(ds);
}
catch (SqlException ex)
{
//
// tratar a excepção!!!!
System.Console.WriteLine("EXCEPTION Method'SearchWord' Class'Users': " + ex.Message);
return(null);
}
finally
{
conn.Close();
}
}
public DataSet GetPlayerHistory(string UserName, string Pass, int PlayerID, string orderField, string orderType)
{
DataSet ds;
SqlConnection conn = null;
try
{
conn = new SqlConnection(DataBase.CONN);
conn.Open();
GWSiteStatusEnum status = DataBase.ValidateUser(conn, null, UserName, Pass);
if (status != GWSiteStatusEnum.OK)
{
return(null);
}
ds = DataBase.GetAllHistory("Scores", PlayerID, orderField, orderType);
return(ds);
}
catch (SqlException ex)
{
//
// tratar a excepção!!!!
System.Console.WriteLine("EXCEPTION Method<GetPlayerHistory> Class'Score': " + ex.Message);
return(null);
}
finally
{
conn.Close();
}
}
public DataSet GetMapScoreRank(string UserName, string Pass, int MapID, int retLines, string scoreOrder)
{
DataSet ds;
SqlConnection conn = null;
try
{
conn = new SqlConnection(DataBase.CONN);
conn.Open();
GWSiteStatusEnum status = DataBase.ValidateUser(conn, null, UserName, Pass);
if (status != GWSiteStatusEnum.OK)
{
return(null);
}
ds = DataBase.GetRank("Scores", MapID, retLines, scoreOrder);
return(ds);
}
catch (SqlException ex)
{
//
// tratar a excepção!!!!
System.Console.WriteLine("EXCEPTION Method<GetMapScoreRank> Class'Score': " + ex.Message);
return(null);
}
finally
{
conn.Close();
}
}
public GWSiteStatusEnum UpdatePlayer(string UserName, string Pass,
int?PlayerID, string Name, int?Age, char?Gender,
string Email, string Address, string Country)
{
GWSiteStatusEnum status = GWSiteStatusEnum.OK;
SqlConnection conn = null;
if (PlayerID.HasValue == false)
{
status = GWSiteStatusEnum.INVALID_ARGUMENT;
return(status);
}
else if (Name == null || Name.Trim().Length == 0)
{
status = GWSiteStatusEnum.INVALID_ARGUMENT;
return(status);
}
else if (Email == null || Email.Trim().Length == 0)
{
status = GWSiteStatusEnum.INVALID_ARGUMENT;
return(status);
}
try
{
conn = new SqlConnection(DataBase.CONN);
conn.Open();
//status = DataBase.Set
status = DataBase.ValidateUser(conn, null, UserName, Pass);
if (status != GWSiteStatusEnum.OK)
{
return(status);
}
string[] column = { "Name", "Age", "Gender", "Email", "Address", "Country" };
//int? PlayerID, string Name, int? Age, char? Gender,string Email, string Address, string Country)
string[] value = { Name, Age.ToString(), Gender.ToString(), Email, Address, Country };
status = DataBase.UpdateByID("Players", PlayerID, "PlayerID", column, value);
}
catch (SqlException ex)
{
//
// tratar a excepção!!!!
System.Console.WriteLine("EXCEPTION Method'UpdatePlayer' Class'Players': " + ex.Message);
return(status);
}
finally
{
conn.Close();
}
return(status);
}
public string GetUserRole(string UserName, string Pass, int UserID)
{
SqlConnection conn = null;
string urole = null;
try
{
//
// criar objecto de conexão à base de dados e abrir a conexão
//
conn = new SqlConnection(DataBase.CONN);
conn.Open();
//
// validar user
//
GWSiteStatusEnum status = DataBase.ValidateUser(conn, null, UserName, Pass);
if (status != GWSiteStatusEnum.OK)
{
return(null);
}
//
// efectuar pesquisa
//
urole = DataBase.GetRoleByID("Roles", UserID);
if (urole == null || urole.Trim().Length == 0)
{
return("User");
}
}
catch (SqlException ex)
{
//
// tratar a excepção!!!!
//
System.Console.WriteLine("EXCEPTION Method<GetByID> Class'User': " + ex.Message);
}
finally
{
//
// fechar a conexão
//
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return(urole);
}
public DataSet GetByID(string UserName, string Pass, int PlayerID)
{
SqlConnection conn = null;
DataSet ds = null;
try
{
//
// criar objecto de conexão à base de dados e abrir a conexão
//
conn = new SqlConnection(DataBase.CONN);
conn.Open();
//
// validar user
//
GWSiteStatusEnum status = DataBase.ValidateUser(conn, null, UserName, Pass);
if (status != GWSiteStatusEnum.OK)
{
return(null);
}
//
// efectuar pesquisa
//
ds = DataBase.GetByID(conn, null, "Players", "PlayerID", PlayerID);
}
catch (SqlException ex)
{
//
// tratar a excepção!!!!
//
System.Console.WriteLine("EXCEPTION Method<GetByID> Class'Player': " + ex.Message);
}
finally
{
//
// fechar a conexão
//
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return(ds);
}
public DataSet FindByName(string UserName, string Pass, string Column, string NamePattern)
{
SqlConnection conn = null;
DataSet ds = null;
try
{
//
// criar objecto de conexão à base de dados e abrir a conexão
//
conn = new SqlConnection(DataBase.CONN);
conn.Open();
//
// validar utilizador
//
GWSiteStatusEnum status = DataBase.ValidateUser(conn, null, UserName, Pass);
if (status != GWSiteStatusEnum.OK)
{
return(null);
}
//
// efectuar pesquisa
//
ds = DataBase.FindByStringField("Players", Column, NamePattern);
}
catch (SqlException ex)
{
//
// tratar a excepção!!!!
//
System.Console.WriteLine("EXCEPTION Method<FindByName> Class'Player': " + ex.Message);
}
finally
{
//
// fechar a conexão
//
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return(ds);
}
public GWSiteStatusEnum DeleteMap(string UserName, string Pass, int MapID)
{
GWSiteStatusEnum status = GWSiteStatusEnum.OK;
SqlConnection conn = null;
if (UserName == null || UserName.Trim().Length == 0)
{
return(GWSiteStatusEnum.INVALID_ARGUMENT);
}
else if (Pass == null || Pass.Trim().Length == 0)
{
return(GWSiteStatusEnum.INVALID_ARGUMENT);
}
else if (MapID.ToString() == null || Pass.ToString().Trim().Length == 0)
{
return(GWSiteStatusEnum.INVALID_ARGUMENT);
}
try
{
conn = new SqlConnection(DataBase.CONN);
conn.Open();
//status = DataBase.Set
status = DataBase.ValidateUser(conn, null, UserName, Pass);
if (status != GWSiteStatusEnum.OK)
{
return(status);
}
status = DataBase.DeleteByID("Maps", MapID);
}
catch (SqlException ex)
{
//
// tratar a excepção!!!!
System.Console.WriteLine("EXCEPTION Method'UpdateUser' Class'Users': " + ex.Message);
return(status);
}
finally
{
conn.Close();
}
return(status);
}
/// <summary>
/// Valida um login/password no sistema
/// </summary>
/// <param name="User">username de login na aplicação</param>
/// <param name="Pass">password de login na aplicação</param>
/// <returns></returns>
/*
* public override bool ValidateUser(string username, string password)
* {
* return true;
* }
*/
public static GWSiteStatusEnum ValidateUser(string username, string pass)
{
GWSiteStatusEnum status = GWSiteStatusEnum.OK;
SqlConnection conn = null;
try
{
//
// criar objecto de conexão à base de dados e abrir a conexão
//
conn = new SqlConnection(CONN);
conn.Open();
//
// invocar método auxiliar
//
status = ValidateUser(conn, null, username, pass);
}
catch (SqlException ex)
{
//
// tratar a excepção!!!!
//
System.Console.WriteLine("EXCEPTION in 'User' Validation: " + ex);
status = GWSiteStatusEnum.ERROR;
}
finally
{
//
// fechar a conexão
//
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return(status);
}
public static GWSiteStatusEnum ValidateUser(SqlConnection conn, SqlTransaction tx, string username, string pass)
{
GWSiteStatusEnum status = GWSiteStatusEnum.OK;
SqlCommand cmd = null;
try
{
//
// criar comando SQL a executar
//
cmd = new SqlCommand("SELECT COUNT(*) FROM Users WHERE UserName='******' AND Pass='******'", conn);
cmd.Transaction = tx;
//
// count(*) e ExecuteScalar()
//
int count = (int)cmd.ExecuteScalar();
if (count == 1)
{
status = GWSiteStatusEnum.OK;
}
else
{
status = GWSiteStatusEnum.INVALID_LOGIN;
}
}
catch (SqlException ex)
{
//
// tratar a excepção!!!!
//
System.Console.WriteLine("EXCEPTION in 'User' Validation: " + ex);
status = GWSiteStatusEnum.ERROR;
}
return(status);
}
public GWSiteStatusEnum UpdateMap(string UserName, string Pass, int MapID, string Descr)
{
GWSiteStatusEnum status = GWSiteStatusEnum.OK;
SqlConnection conn = null;
if (Descr == null || Descr.Trim().Length == 0)
{
status = GWSiteStatusEnum.INVALID_ARGUMENT;
return(status);
}
try
{
conn = new SqlConnection(DataBase.CONN);
conn.Open();
//status = DataBase.Set
status = DataBase.ValidateUser(conn, null, UserName, Pass);
if (status != GWSiteStatusEnum.OK)
{
return(status);
}
status = DataBase.UpdateByID("Maps", MapID, "MapID", "Descr", Descr);
}
catch (SqlException ex)
{
//
// tratar a excepção!!!!
System.Console.WriteLine("EXCEPTION Method<UpdateMap> Class'Users': " + ex.Message);
return(status);
}
finally
{
conn.Close();
}
return(status);
}
public int AddPlayer(string UserName, string Pass,
int?UserID, string Name, int?Age, char?Gender,
string Email, string Address, string Country,
out GWSiteStatusEnum status)
{
int PlayerID = -1;
SqlConnection conn = null;
//
// validar dados de entrada
//
if (UserID.HasValue == false)
{
status = GWSiteStatusEnum.INVALID_ARGUMENT;
return(-1);
}
else if (Name == null || Name.Trim().Length == 0)
{
status = GWSiteStatusEnum.INVALID_ARGUMENT;
return(-1);
}
else if (Email == null || Email.Trim().Length == 0)
{
status = GWSiteStatusEnum.INVALID_ARGUMENT;
return(-1);
}
try
{
//
// criar objecto de conexão à base de dados e abrir a conexão
//
conn = new SqlConnection(DataBase.CONN);
conn.Open();
//
// validar o utilizador
//
status = DataBase.ValidateUser(conn, null, UserName, Pass);
if (status != GWSiteStatusEnum.OK)
{
return(-1);
}
//
// criar comando SQL a executar
//
string sqlCmd = "INSERT INTO Players (UserID, Name, Age, Gender, Email, Address, Country) " +
"VALUES (@USerID, @Name, @Age, @Gender, @Email, @Address, @Country)";
SqlCommand cmd = new SqlCommand(sqlCmd, conn);
cmd.Parameters.AddWithValue("UserID", UserID);
cmd.Parameters.AddWithValue("Name", Name);
if (Age.HasValue == false)
{
cmd.Parameters.AddWithValue("Age", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("Age", Age);
}
if (Gender.HasValue == false)
{
cmd.Parameters.AddWithValue("Gender", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("Gender", Gender);
}
cmd.Parameters.AddWithValue("Email", Email);
if (Address.Length == 0)
{
cmd.Parameters.AddWithValue("Address", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("Address", Address);
}
if (Country.Length == 0)
{
cmd.Parameters.AddWithValue("Country", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("Country", Country);
}
//
// executar o comando
//
int insRows = cmd.ExecuteNonQuery();
//
// verificar o resultado
//
if (insRows == 0)
{
// não inseriu registo
status = GWSiteStatusEnum.NOT_OK;
}
else
{
//
// inseriu registo
// obter novo código de id gerado pela BD
// veja "referência Rápida ADO.net" no moodle
SqlCommand idCmd = new SqlCommand("SELECT @@IDENTITY", conn);
PlayerID = (int)idCmd.ExecuteScalar();
status = GWSiteStatusEnum.OK;
}
}
catch (SqlException ex)
{
//
// tratar a excepção!!!!
//
System.Console.WriteLine("EXCEPTION Method<AddPlayer> Class'Player': " + ex.Message);
status = GWSiteStatusEnum.ERROR;
}
finally
{
//
// fechar a conexão
//
if (conn != null && conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return(PlayerID);
}
public static GWSiteStatusEnum DeleteByID(string table, int key)
{
GWSiteStatusEnum status = GWSiteStatusEnum.OK;
SqlConnection conn = null;
try
{
//
// criar objecto de conexão à base de dados e abrir a conexão
//
conn = new SqlConnection(DataBase.CONN);
conn.Open();
string sSqlCmd = "";
//
// criar comando SQL a executar
//
if (table == "Maps")
{
sSqlCmd = "DELETE FROM Scores WHERE MapID=" + key.ToString();
SqlCommand cmd = new SqlCommand(sSqlCmd, conn);
int insRows = cmd.ExecuteNonQuery();
if (insRows == 0)
{
status = GWSiteStatusEnum.NOT_OK;
}
sSqlCmd = "DELETE FROM Maps WHERE MapID=" + key.ToString();
cmd = new SqlCommand(sSqlCmd, conn);
insRows = cmd.ExecuteNonQuery();
if (insRows == 0)
{
status = GWSiteStatusEnum.NOT_OK;
}
else
{
status = GWSiteStatusEnum.OK;
}
}
else if (table == "Users")
{
//DELETE FROM Scores WHERE PlayerID=(select PlayerID FROM Players WHERE UserID=30)
sSqlCmd = "DELETE FROM Scores WHERE PlayerID=(SELECT PlayerID FROM Players WHERE UserID=" + key.ToString() + ")";
SqlCommand cmd = new SqlCommand(sSqlCmd, conn);
int insRows = cmd.ExecuteNonQuery();
if (insRows == 0)
{
status = GWSiteStatusEnum.NOT_OK;
}
sSqlCmd = "DELETE FROM Players WHERE UserID=" + key.ToString();
cmd = new SqlCommand(sSqlCmd, conn);
insRows = cmd.ExecuteNonQuery();
if (insRows == 0)
{
status = GWSiteStatusEnum.NOT_OK;
}
sSqlCmd = "DELETE FROM Users WHERE UserID=" + key.ToString();
cmd = new SqlCommand(sSqlCmd, conn);
insRows = cmd.ExecuteNonQuery();
if (insRows == 0)
{
status = GWSiteStatusEnum.NOT_OK;
}
else
{
status = GWSiteStatusEnum.OK;
}
}
}
catch (SqlException ex)
{
//
// tratar a excepção!!!!
//
System.Console.WriteLine("EXCEPTION Method<AddNew 1> Class'DataBase': " + ex.Message);
status = GWSiteStatusEnum.ERROR;
}
finally
{
//
// fechar a conexão
//
if (conn != null && conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return(status);
}
public static GWSiteStatusEnum UpdateByID(string table, int?key, string keyName, string[] column, string[] value)
{
GWSiteStatusEnum status = GWSiteStatusEnum.OK;
SqlConnection conn = null;
try
{
//
// criar objecto de conexão à base de dados e abrir a conexão
//
conn = new SqlConnection(DataBase.CONN);
conn.Open();
//
// criar comando SQL a executar
//
System.Text.StringBuilder sb = new System.Text.StringBuilder("UPDATE " + table + " SET ");
for (int i = 0; i < value.Length - 1; i++)
{
sb.Append(column[i].ToString());
sb.Append("='");
sb.Append(value[i].ToString());
sb.Append("',");
}
sb.Length = sb.Length - 1;
sb.Append(" WHERE " + keyName + "='");
sb.Append(key);
sb.Append("'");
string sSqlCmd = sb.ToString();
SqlCommand cmd = new SqlCommand(sSqlCmd, conn);
System.Console.WriteLine("STRING: " + sSqlCmd);
//
// executar o comando
//
int insRows = cmd.ExecuteNonQuery();
//
// verificar o resultado
//
if (insRows == 0)
{
// não inseriu registo
status = GWSiteStatusEnum.NOT_OK;
}
else
{
//
// inseriu registo
//
status = GWSiteStatusEnum.OK;
}
}
catch (SqlException ex)
{
//
// tratar a excepção!!!!
//
System.Console.WriteLine("EXCEPTION Method<UpdateByID 2> Class'DataBase': " + ex.Message);
status = GWSiteStatusEnum.ERROR;
}
finally
{
//
// fechar a conexão
//
if (conn != null && conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return(status);
}
/*
* //-------------------------
* public static string AddJogador(string nome, int idade, string morada)
* {
* SqlConnection con = new SqlConnection(CONN);
* try
* {
* con.Open();
* SqlCommand cmd = new SqlCommand("addJogador", con);
* cmd.CommandType = CommandType.StoredProcedure;
* cmd.Parameters.AddWithValue("@nome", nome);
* cmd.Parameters.AddWithValue("@idade", idade);
* cmd.Parameters.AddWithValue("@morada", morada);
* cmd.ExecuteNonQuery();
* return null; // success
* }
* catch (Exception ex)
* {
* return ex.Message; // return error message
* }
* finally
* {
* con.Close();
* }
* }
* public static string DeleteUser(int key)
* {
* SqlConnection con = new SqlConnection(CONN);
* try
* {
* con.Open();
* SqlCommand cmd = new SqlCommand("deleteJogador", con);
* cmd.CommandType = CommandType.StoredProcedure;
* cmd.Parameters.AddWithValue("@jid", jid);
* cmd.ExecuteNonQuery();
* return null; // success
* }
* catch (Exception ex)
* {
* return ex.Message; // return error message
* }
* finally
* {
* con.Close();
* }
* }
* public static string UpdateJogador(int jid, string nome, int idade, string morada)
* {
* SqlConnection con = new SqlConnection(CONN);
* try
* {
* con.Open();
* SqlCommand cmd = new SqlCommand("updateJogador", con);
* cmd.CommandType = CommandType.StoredProcedure;
* cmd.Parameters.AddWithValue("@jid", jid);
* cmd.Parameters.AddWithValue("@nome", nome);
* cmd.Parameters.AddWithValue("@idade", idade);
* cmd.Parameters.AddWithValue("@morada", morada);
* cmd.ExecuteNonQuery();
* return null; // success
* }
* catch (Exception ex)
* {
* return ex.Message; // return error message
* }
* finally
* {
* con.Close();
* }
* }
*
*
* //------------------------------------------------------
*/
#region NoReturn
public static GWSiteStatusEnum UpdateByID(string table, int key, string keyName, string column, string value)
{
GWSiteStatusEnum status = GWSiteStatusEnum.OK;
SqlConnection conn = null;
try
{
//
// criar objecto de conexão à base de dados e abrir a conexão
//
conn = new SqlConnection(DataBase.CONN);
conn.Open();
//
// criar comando SQL a executar
//
// update Users Set Pass='******' WHERE UserID=30
//UpdateByID(table, key, keyName, column, value)
//UpdateByID("Maps", MapID, "MapID", "Descr", Descr);
string sSqlCmd = "UPDATE " + table + " SET " + column + "=" + "'" + value + "'" + " WHERE " + keyName + " = " + key.ToString();
SqlCommand cmd = new SqlCommand(sSqlCmd, conn);
//
// executar o comando
//
int insRows = cmd.ExecuteNonQuery();
//
// verificar o resultado
//
if (insRows == 0)
{
// não inseriu registo
status = GWSiteStatusEnum.NOT_OK;
}
else
{
//
// inseriu registo
//
status = GWSiteStatusEnum.OK;
}
}
catch (SqlException ex)
{
//
// tratar a excepção!!!!
//
System.Console.WriteLine("EXCEPTION Method<UpdateByID 1> Class'DataBase': " + ex.Message);
status = GWSiteStatusEnum.ERROR;
}
finally
{
//
// fechar a conexão
//
if (conn != null && conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return(status);
}
public int AddUser(string UserName, string Pass,
out GWSiteStatusEnum status)
{
int UserID = -1;
SqlConnection conn = null;
//
// validar dados de entrada
//
if (UserName == null || UserName.Trim().Length == 0)
{
status = GWSiteStatusEnum.INVALID_ARGUMENT;
return(-1);
}
else if (Pass == null || Pass.Trim().Length == 0)
{
status = GWSiteStatusEnum.INVALID_ARGUMENT;
return(-1);
}
try
{
//
// criar objecto de conexão à base de dados e abrir a conexão
//
conn = new SqlConnection(DataBase.CONN);
conn.Open();
//
// validar o utilizador
//
/*
* status = DataBase.ValidateUser(conn, null, UserName, Pass);
* if (status != GWSiteStatusEnum.OK)
* return -1;
*/
//
// criar comando SQL a executar
//
string sSqlCmd = "INSERT INTO Users (UserName, Pass) VALUES (@UserName, @Pass) SET @UserID = SCOPE_IDENTITY()";
SqlCommand cmd = new SqlCommand(sSqlCmd, conn);
cmd.Parameters.AddWithValue("UserName", UserName);
cmd.Parameters.AddWithValue("Pass", Pass);
SqlParameter UserIDParameter = new SqlParameter("@UserID", SqlDbType.Int);
UserIDParameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(UserIDParameter);
//
// executar o comando
//
int insRows = cmd.ExecuteNonQuery();
//
// verificar o resultado
//
if (insRows == 0)
{
// não inseriu registo
status = GWSiteStatusEnum.NOT_OK;
}
else
{
//
// inseriu registo
// obter novo código de id gerado pela BD
UserID = (int)UserIDParameter.Value;
status = GWSiteStatusEnum.OK;
}
}
catch (SqlException ex)
{
//
// tratar a excepção!!!!
//
System.Console.WriteLine("EXCEPTION Method<AddUser> Class'User': " + ex.Message);
status = GWSiteStatusEnum.ERROR;
}
finally
{
//
// fechar a conexão
//
if (conn != null && conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return(UserID);
}
public GWSiteStatusEnum Validate(string username, string pass)
{
GWSiteStatusEnum status = DataBase.ValidateUser(username, pass);
return(status);
}
public static GWSiteStatusEnum AddNew(string table, string keyName)
{
GWSiteStatusEnum status = GWSiteStatusEnum.OK;
SqlConnection conn = null;
SqlTransaction tn = null;
try
{
//
// criar objecto de conexão à base de dados e abrir a conexão
//
conn = new SqlConnection(DataBase.CONN);
conn.Open();
tn = conn.BeginTransaction();
//
// criar comando SQL a executar
//
string sSqlCmd = "INSERT INTO " + table + " VALUES " + "('" + keyName + "')";
SqlCommand cmd = new SqlCommand(sSqlCmd, conn);
//
// executar o comando
//
cmd.Transaction = tn;
int insRows = cmd.ExecuteNonQuery();
//
// verificar o resultado
//
if (insRows == 0)
{
// não inseriu registo
status = GWSiteStatusEnum.NOT_OK;
}
else
{
//
// inseriu registo
//
status = GWSiteStatusEnum.OK;
}
tn.Commit();
}
catch (SqlException ex)
{
//
// tratar a excepção!!!!
//
if (tn != null)
{
tn.Rollback();
}
System.Console.WriteLine("EXCEPTION Method<AddNew 1> Class'DataBase': " + ex.Message);
status = GWSiteStatusEnum.ERROR;
}
finally
{
//
// fechar a conexão
//
if (conn != null && conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return(status);
}
/*
* public static GWSiteStatusEnum AddNew(string table, string keyName)
* {
* GWSiteStatusEnum status = GWSiteStatusEnum.OK;
* SqlConnection conn = null;
*
* try
* {
* //
* // criar objecto de conexão à base de dados e abrir a conexão
* //
* conn = new SqlConnection(DataBase.CONN);
* conn.Open();
*
* //
* // criar comando SQL a executar
* //
* //INSERT INTO table_name
* //VALUES (value1, value2, value3,...)
*
* string sSqlCmd = "INSERT INTO " + table + " VALUES " +"('"+keyName+"')";
* SqlCommand cmd = new SqlCommand(sSqlCmd, conn);
* //
* // executar o comando
* //
* int insRows = cmd.ExecuteNonQuery();
*
* //
* // verificar o resultado
* //
* if (insRows == 0)
* // não inseriu registo
* status = GWSiteStatusEnum.NOT_OK;
* else
* {
* //
* // inseriu registo
* //
* status = GWSiteStatusEnum.OK;
* }
* }
* catch (SqlException ex)
* {
* //
* // tratar a excepção!!!!
* //
* System.Console.WriteLine("EXCEPTION Method<AddNew 1> Class'DataBase': " + ex.Message);
* status = GWSiteStatusEnum.ERROR;
* }
* finally
* {
* //
* // fechar a conexão
* //
* if (conn != null && conn.State == ConnectionState.Open)
* conn.Close();
* }
*
* return status;
*
*
* }
*/
public static GWSiteStatusEnum AddNew(string table, string[] value)
{
GWSiteStatusEnum status = GWSiteStatusEnum.OK;
SqlConnection conn = null;
try
{
//
// criar objecto de conexão à base de dados e abrir a conexão
//
conn = new SqlConnection(DataBase.CONN);
conn.Open();
//
// criar comando SQL a executar
//
//INSERT INTO table_name
//VALUES (value1, value2, value3,...)
System.Text.StringBuilder sb = new System.Text.StringBuilder("INSERT INTO " + table + " VALUES (");
foreach (string k in value)
{
sb.Append("'" + k.ToString() + "'");
sb.Append(",");
}
sb.Length = sb.Length - 1;
sb.Append(")");
string sSqlCmd = sb.ToString();
SqlCommand cmd = new SqlCommand(sSqlCmd, conn);
//
// executar o comando
//
int insRows = cmd.ExecuteNonQuery();
//
// verificar o resultado
//
if (insRows == 0)
{
// não inseriu registo
status = GWSiteStatusEnum.NOT_OK;
}
else
{
//
// inseriu registo
//
status = GWSiteStatusEnum.OK;
}
}
catch (SqlException ex)
{
//
// tratar a excepção!!!!
//
System.Console.WriteLine("EXCEPTION Method<AddNew 2> Class'DataBase': " + ex.Message);
status = GWSiteStatusEnum.ERROR;
}
finally
{
//
// fechar a conexão
//
if (conn != null && conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return(status);
}
public int AddScore(string UserName, string Pass,
int?PlayerID, int?MapID, int?Score, string Date,
out GWSiteStatusEnum status)
{
int ScoreID = -1;
SqlConnection conn = null;
//
// validar dados de entrada
//
if (PlayerID.HasValue == false)
{
status = GWSiteStatusEnum.INVALID_ARGUMENT;
return(-1);
}
else if (MapID.HasValue == false)
{
status = GWSiteStatusEnum.INVALID_ARGUMENT;
return(-1);
}
else if (Score.HasValue == false)
{
status = GWSiteStatusEnum.INVALID_ARGUMENT;
return(-1);
}
else if (Date.Length == 0)
{
status = GWSiteStatusEnum.INVALID_ARGUMENT;
return(-1);
}
try
{
//
// criar objecto de conexão à base de dados e abrir a conexão
//
conn = new SqlConnection(DataBase.CONN);
conn.Open();
//
// validar o utilizador
//
status = DataBase.ValidateUser(conn, null, UserName, Pass);
if (status != GWSiteStatusEnum.OK)
{
return(-1);
}
//
// criar comando SQL a executar
//
string sqlCmd = "INSERT INTO Scores (PlayerID, MapID, Score, Date) " +
"VALUES (@PlayerID, @MapID, @Score, @Date)";
SqlCommand cmd = new SqlCommand(sqlCmd, conn);
cmd.Parameters.AddWithValue("PlayerID", PlayerID);
cmd.Parameters.AddWithValue("MapID", MapID);
cmd.Parameters.AddWithValue("Score", Score);
cmd.Parameters.AddWithValue("Date", Date);
//
// executar o comando
//
int insRows = cmd.ExecuteNonQuery();
//
// verificar o resultado
//
if (insRows == 0)
{
// não inseriu registo
status = GWSiteStatusEnum.NOT_OK;
}
else
{
//
// inseriu registo
// obter novo código de id gerado pela BD
// veja "referência Rápida ADO.net" no moodle
SqlCommand idCmd = new SqlCommand("SELECT @@IDENTITY", conn);
ScoreID = (int)idCmd.ExecuteScalar();
status = GWSiteStatusEnum.OK;
}
}
catch (SqlException ex)
{
//
// tratar a excepção!!!!
//
System.Console.WriteLine("EXCEPTION Method<AddScore> Class'Score': " + ex.Message);
status = GWSiteStatusEnum.ERROR;
}
finally
{
//
// fechar a conexão
//
if (conn != null && conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return(ScoreID);
}
