//IF nuid == 0, getAllDecks belonging to all users
public static List<netDeck> getDecks(int nuid)
{
string SQL;
List<netDeck> deckList = new List<netDeck>();
MySqlCommand cmd = new MySqlCommand();
MySqlDataReader myData;
connect();
try
{
if (nuid == 0)
SQL = "SELECT ndid,cat,subcat,title,date,rat,num_v,nuid FROM networkdecks WHERE 1" + " ORDER BY cat, title" ;
else
SQL = "SELECT ndid,cat,subcat,title,date,rat,num_v,nuid FROM networkdecks WHERE nuid = " + Convert.ToString(nuid) + " ORDER BY cat, title";
cmd.Connection = conn;
cmd.CommandText = SQL;
myData = cmd.ExecuteReader();
while (myData.Read())
{
netDeck deck = new netDeck(myData.GetInt32(myData.GetOrdinal("ndid")),
myData.GetString(myData.GetOrdinal("cat")),
myData.GetString(myData.GetOrdinal("subcat")),
myData.GetString(myData.GetOrdinal("title")),
myData.GetString(myData.GetOrdinal("date")),
myData.GetFloat(myData.GetOrdinal("rat")),
myData.GetInt32(myData.GetOrdinal("num_v")),
myData.GetInt32(myData.GetOrdinal("nuid")));
deckList.Add(deck);
}
myData.Close();
conn.Close();
return deckList;
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
conn.Close();
throw new Exception();
}
}
public static List<netDeck> searchDeck(string[] searchStr, string opt)
{
string SQL;
List<netDeck> deckList = new List<netDeck>();
MySqlCommand cmd = new MySqlCommand();
MySqlDataReader myData;
connect();
try
{
SQL = "SELECT ndid,cat,subcat,title,date,rat,num_v,nuid FROM networkdecks WHERE 0 ";
switch(opt)
{
case("Category"):
foreach (string s in searchStr)
SQL += " OR LOWER(cat) LIKE " + "'%" + s.ToLower() + "%'";
break;
case("Title"):
foreach (string s in searchStr)
SQL += " OR LOWER(title) LIKE " + "'%" + s.ToLower() + "%'";
break;
case ("Sub-Category"):
foreach (string s in searchStr)
SQL += " OR LOWER(subcat) LIKE " + "'%" + s.ToLower() + "%'";
break;
default:
SQL = "SELECT nd.ndid,cat,subcat,title,date,rat,num_v,nd.nuid FROM networkdecks nd, networkusers nu WHERE nu.nuid = nd.nuid AND (0";
foreach (string s in searchStr)
SQL += " OR LOWER(nu.nname) LIKE " + "'%" + s.ToLower() + "%'";
SQL += ")";
break;
}
SQL += " ORDER BY cat, title";
cmd.Connection = conn;
cmd.CommandText = SQL;
myData = cmd.ExecuteReader();
while (myData.Read())
{
netDeck deck = new netDeck(myData.GetInt32(myData.GetOrdinal("ndid")),
myData.GetString(myData.GetOrdinal("cat")),
myData.GetString(myData.GetOrdinal("subcat")),
myData.GetString(myData.GetOrdinal("title")),
myData.GetString(myData.GetOrdinal("date")),
myData.GetFloat(myData.GetOrdinal("rat")),
myData.GetInt32(myData.GetOrdinal("num_v")),
myData.GetInt32(myData.GetOrdinal("nuid")));
deckList.Add(deck);
}
myData.Close();
conn.Close();
return deckList;
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
conn.Close();
throw new Exception();
}
}
public static netDeck getSpecificDeck(int ndid)
{
string SQL;
MySqlCommand cmd = new MySqlCommand();
MySqlDataReader myData;
netDeck deck = null;
connect();
try
{
SQL = "SELECT ndid,cat,subcat,title,date,rat,num_v,nuid FROM networkdecks WHERE ndid = " + Convert.ToString(ndid);
cmd.Connection = conn;
cmd.CommandText = SQL;
myData = cmd.ExecuteReader();
while (myData.Read())
{
deck = new netDeck(myData.GetInt32(myData.GetOrdinal("ndid")),
myData.GetString(myData.GetOrdinal("cat")),
myData.GetString(myData.GetOrdinal("subcat")),
myData.GetString(myData.GetOrdinal("title")),
myData.GetString(myData.GetOrdinal("date")),
myData.GetFloat(myData.GetOrdinal("rat")),
myData.GetInt32(myData.GetOrdinal("num_v")),
myData.GetInt32(myData.GetOrdinal("nuid")));
}
myData.Close();
conn.Close();
return deck;
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
conn.Close();
throw new Exception();
}
}
