Code:
using Nexus.Database;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
namespace Nexus
{
public static class SQLExt
{
/// <summary>
/// hey i just locked you, and this is crazy, but here's my object. so lock me maybe
/// </summary>
public static object squirrellock = new object();
#region We Believe in Extensions
/// <summary>
/// We Count Rows
/// </summary>
/// <param name="query">i'm the Query</param>
/// <param name="args">and i'm the optional Parameters hh</param>
/// <returns>i'm a fetched row!!!!!!</returns>
public static async Task<int> numRows(this SQL sql, string query, params SqlParameter[] op)
{
int returnval = 0;
try
{
if (!sql.IsOpen)
sql.Open(sql.Host, sql.User, sql.Pass, sql.Database, sql.MARS, sql.Timeout);
if (sql.IsOpen)
{
using (SqlCommand cmd = new SqlCommand(query, sql.Connection))
{
if (op != null)
{
cmd.Parameters.AddRange(op);
}
using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
{
returnval = reader.Cast<object>().Count();
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
//throw new CoreException("[SQL] numRows -> Couldn't fetch Number of Rows!");
}
return returnval;
}
/// <summary>
/// We Execute Queries
/// </summary>
/// <param name="query">i'm the Query</param>
/// <param name="args">and i'm the optional Parameters hh</param>
public static async Task<bool> query(this SQL sql, string query, params SqlParameter[] op)
{
bool returnval = false;
try
{
using (SqlCommand cmd = new SqlCommand(query, sql.Connection))
{
if (op != null)
{
cmd.Parameters.AddRange(op);
}
int q = await cmd.ExecuteNonQueryAsync();
if (q > 0)
{
returnval = true;
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
throw new CoreException("[SQL] QUERY -> Couldn't Execute query");
}
return returnval;
}
/// <summary>
/// i Return Strings!!!!!!!!
/// </summary>
/// <param name="query">i'm the Query hh</param>
/// <param name="index">Column index</param>
/// <param name="column">Column Name</param>
/// <param name="op">and i'm the optional Parameters :@</param>
/// <returns>i'm the return value h</returns>
public static async Task<string> getString(this SQL sql, string query, params SqlParameter[] op)
{
string returnval = "";
try
{
using (SqlConnection con = new SqlConnection(sql.ConnString))
{
if (con.State != ConnectionState.Open)
con.Open();
using (SqlCommand cmd = new SqlCommand(query, con))
{
using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
{
if (reader.Read())
{
returnval = reader.GetString(0);
}
}
}
}
}
catch
{
throw new CoreException("[SQL] getString -> Couldn't get String value");
}
return returnval;
}
/// <summary>
/// i Return Strings!!!!!!!!
/// </summary>
/// <param name="query">i'm the Query hh</param>
/// <param name="index">Column index</param>
/// <param name="column">Column Name</param>
/// <param name="op">and i'm the optional Parameters :@</param>
/// <returns>i'm the return value h</returns>
public static async Task<string> getString(this SQL sql, string query, int index, params SqlParameter[] op)
{
string returnval = "";
try
{
using (SqlConnection con = new SqlConnection(sql.ConnString))
{
if (con.State != ConnectionState.Open)
con.Open();
using (SqlCommand cmd = new SqlCommand(query, con))
{
using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
{
if (reader.Read())
{
returnval = reader.GetString(index);
}
}
}
}
}
catch
{
throw new CoreException("[SQL] getString -> Couldn't get String value");
}
return returnval;
}
/// <summary>
/// i Return Strings!!!!!!!!
/// </summary>
/// <param name="query">i'm the Query hh</param>
/// <param name="column">Column Name</param>
/// <param name="op">and i'm the optional Parameters :@</param>
/// <returns>i'm the return value h</returns>
public static async Task<string> getString(this SQL sql, string query, string column, params SqlParameter[] op)
{
string returnval = "";
try
{
using (SqlConnection con = new SqlConnection(sql.ConnString))
{
if (con.State != ConnectionState.Open)
con.Open();
using (SqlCommand cmd = new SqlCommand(query, con))
{
if (op != null)
cmd.Parameters.AddRange(op);
using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
{
if (reader.Read())
{
returnval = reader[column].ToString();
}
}
}
}
}
catch
{
throw new CoreException("[SQL] getString -> Couldn't get String value");
}
return returnval;
}
/// <summary>
/// i Return String Arrays!!!!!!!!
/// </summary>
/// <param name="query">i'm the Query hh</param>
/// <param name="column">Column Name</param>
/// <param name="op">and i'm the optional Parameters :@</param>
/// <returns>i'm the return value h</returns>
public static async Task<string[]> getStringArray(this SQL sql, string query, string column, params SqlParameter[] op)
{
string[] returnval;
try
{
using (SqlConnection con = new SqlConnection(sql.ConnString))
{
if (con.State != ConnectionState.Open)
con.Open();
using (SqlCommand cmd = new SqlCommand(query, con))
{
using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
{
int i = 0;
returnval = new string[reader.FieldCount];
while (await reader.ReadAsync())
{
returnval[i] = reader[column].ToString();
i++;
}
}
}
}
}
catch
{
throw new CoreException("[SQL] getStringArray -> Couldn't get String Array");
}
return returnval;
}
/// <summary>
/// i Return String Arrays!!!!!!!!
/// </summary>
/// <param name="query">i'm the Query hh</param>
/// <param name="index">Column index</param>
/// <param name="op">and i'm the optional Parameters :@</param>
/// <returns>i'm the return value h</returns>
public static async Task<string[]> getStringArray(this SQL sql, string query, int index, params SqlParameter[] op)
{
string[] returnval;
try
{
using (SqlConnection con = new SqlConnection(sql.ConnString))
{
if (con.State != ConnectionState.Open)
con.Open();
using (SqlCommand cmd = new SqlCommand(query, con))
{
using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
{
int i = 0;
returnval = new string[reader.FieldCount];
while (await reader.ReadAsync())
{
returnval[i] = reader[index].ToString();
i++;
}
}
}
}
}
catch
{
throw new CoreException("[SQL] getStringArray -> Couldn't get String Array");
}
return returnval;
}
/// <summary>
/// i Return Ints!!!!!
/// </summary>
/// <param name="query">i'm the Query hh</param>
/// <param name="op">i swear i'm the optional Parameters</param>
/// <returns>i'm the Returned INT :@</returns>
public static int getInt(this SQL sql, string query, params SqlParameter[] op)
{
lock (squirrellock)
{
int returnval = 0;
try
{
using (SqlCommand cmd = new SqlCommand(query, sql.Connection))
{
if (op != null)
{
cmd.Parameters.AddRange(op);
}
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
if (reader.HasRows)
returnval = Convert.ToInt32(reader.GetValue(0));
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
throw new CoreException("[SQL] getInt -> Couldn't get Int Value");
}
return returnval;
}
}
/// <summary>
/// i Return Ints!!!!!
/// </summary>
/// <param name="query">i'm the Query hh</param>
/// <param name="index">Column index</param>
/// <param name="op">i swear i'm the optional Parameters</param>
/// <returns>i'm the Returned INT :@</returns>
public static int getInt(this SQL sql, string query, int index, params SqlParameter[] op)
{
lock (squirrellock)
{
int returnval = 0;
try
{
using (SqlCommand cmd = new SqlCommand(query, sql.Connection))
{
if (op != null)
{
cmd.Parameters.AddRange(op);
}
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
returnval = reader.GetInt32(index);
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
//throw new CoreException("[SQL] getInt -> Couldn't get Int Value");
}
return returnval;
}
}
/// <summary>
/// i Return Ints!!!!!
/// </summary>
/// <param name="query">i'm the Query hh</param>
/// <param name="column">Column Name</param>
/// <param name="op">i swear i'm the optional Parameters</param>
/// <returns>i'm the Returned INT :@</returns>
public static int getInt(this SQL sql, string query, string column, params SqlParameter[] op)
{
lock (squirrellock)
{
int returnval = 0;
try
{
using (SqlConnection con = new SqlConnection(sql.ConnString))
{
if (con.State != ConnectionState.Open)
con.Open();
using (SqlCommand cmd = new SqlCommand(query, con))
{
if (op != null)
{
cmd.Parameters.AddRange(op);
}
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
returnval = Convert.ToInt32(reader[column]);
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
throw new CoreException("[SQL] getInt -> Couldn't get Int Value");
}
return returnval;
}
}
/// <summary>
/// i Return Int Arrays!!!!!!!!
/// </summary>
/// <param name="query">i'm the Query hh</param>
/// <param name="column">Column Name</param>
/// <param name="op">and i'm the optional Parameters :@</param>
/// <returns>i'm the return value h</returns>
public static int[] getIntArray(this SQL sql, string query, string column, params SqlParameter[] op)
{
lock (squirrellock)
{
int[] returnval;
try
{
using (SqlConnection con = new SqlConnection(sql.ConnString))
{
if (con.State != ConnectionState.Open)
con.Open();
using (SqlCommand cmd = new SqlCommand(query, con))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
int i = 0;
returnval = new int[reader.FieldCount];
while (reader.Read())
{
returnval[i] = Convert.ToInt32(reader[column]);
i++;
}
}
}
}
}
catch
{
throw new CoreException("[SQL] getIntArray -> Couldn't get Int Array");
}
return returnval;
}
}
/// <summary>
/// i Return Int Arrays!!!!!!!!
/// </summary>
/// <param name="query">i'm the Query hh</param>
/// <param name="index">Column index</param>
/// <param name="op">and i'm the optional Parameters :@</param>
/// <returns>i'm the return value h</returns>
public static int[] getIntArray(this SQL sql, string query, int index, params SqlParameter[] op)
{
lock (squirrellock)
{
int[] returnval;
try
{
using (SqlConnection con = new SqlConnection(sql.ConnString))
{
if (con.State != ConnectionState.Open)
con.Open();
using (SqlCommand cmd = new SqlCommand(query, con))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
int i = 0;
returnval = new int[reader.FieldCount];
while (reader.Read())
{
returnval[i] = Convert.ToInt32(reader[index]);
i++;
}
}
}
}
}
catch
{
throw new CoreException("[SQL] getIntArray -> Couldn't get Int Array");
}
return returnval;
}
}
#endregion
#region Database Helper Functions
public static Dictionary<string, string> GuildByCharName(this SQL sql, string charname, params SqlParameter[] op)
{
lock (squirrellock)
{
Dictionary<string, string> returnval;
try
{
using (SqlConnection con = new SqlConnection(sql.ConnString))
{
if (con.State != ConnectionState.Open)
con.Open();
string query = Queries.GuildArrayQuery + "'" + charname + "'";
using (SqlCommand cmd = new SqlCommand(query, con))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
int i = 0;
returnval = new Dictionary<string, string>();
while (reader.Read())
{
returnval.Add(reader.GetName(i), reader.GetValue(i).ToString());
i++;
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
throw new CoreException("[SQL] GuildIDByCharName -> Couldn't get GuildID");
}
return returnval;
}
}
public static int GetMemberCountByCharname(this SQL sql, string charname, params SqlParameter[] op)
{
lock (squirrellock)
{
int returnval = 0;
try
{
using (SqlConnection con = new SqlConnection(sql.ConnString))
{
if (con.State != ConnectionState.Open)
con.Open();
string query = Queries.MembersCountQuery + "'" + charname + "'";
using (SqlCommand cmd = new SqlCommand(query, con))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
returnval = Convert.ToInt32(reader["MembersCount"]);
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
throw new CoreException("[SQL] MemberCountByCharname -> Couldn't get Members Count");
}
return returnval;
}
}
public static int GetUnionCountByCharname(this SQL sql, string charname, params SqlParameter[] op)
{
lock (squirrellock)
{
int returnval = 0;
try
{
using (SqlConnection con = new SqlConnection(sql.ConnString))
{
if (con.State != ConnectionState.Open)
con.Open();
string query = Queries.GuildArrayQuery + "'" + charname + "'";
using (SqlCommand cmd = new SqlCommand(query, con))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
int[] allies = new int[8];
for (int i = 1; i < 8; i++)
{
allies[i] = sql.getInt("select Ally" + i + " from _AlliedClans where ID='" + reader["Alliance"] + "'", "Ally" + i);
}
foreach (int z in allies)
{
if (z != 0)
returnval++;
}
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
throw new CoreException("[SQL] GetUnionCountByCharname -> Couldn't get Union Count");
}
return returnval;
}
}
public static int GetPlus(this SQL sql, string charname, int slot, params SqlParameter[] op)
{
lock (squirrellock)
{
int returnval = 0;
try
{
using (SqlConnection con = new SqlConnection(sql.ConnString))
{
if (con.State != ConnectionState.Open)
con.Open();
string query = Queries.OptLevelQuery(charname, slot);
using (SqlCommand cmd = new SqlCommand(query, con))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
returnval = Convert.ToByte(reader["OptLevel"]);
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
throw new CoreException("[SQL] GetPlus -> Couldn't get Item Plus");
}
return returnval;
}
}
#endregion
}
}