public static string INSERT(string[] Fields, DBTableNames Target)
{
// specifying which fields to insert
var query = new StringBuilder();
query.Append("INSERT " + DBTableConverter.Tables[Target] + " ( ");
int counter = 0;
foreach (string field in Fields)
{
query.Append(field);
if (!(counter + 1 == Fields.Length))
{
query.Append(", ");
}
counter++;
}
//concating paramater names to the query
counter = 0;
query.Append(" ) VALUES ( ");
foreach (string field in Fields)
{
query.Append("@" + field);
if (!(counter + 1 == Fields.Length))
{
query.Append(", ");
}
counter++;
}
query.Append(" )");
return(query.ToString());
}
public static async Task <List <User> > GetUsers(QueryBuilder queryBuilder)
{
MySqlConnection connection = null;
try
{
connection = await SQLConnection.GetConnection();
List <string> regexp = new List <string>()
{
"userName"
};
string query = queryBuilder.ToSearchQueryString(regexp);
string sql = $"SELECT id, username, role FROM {DBTableNames.users}{(!String.IsNullOrEmpty(query) ? $" WHERE {query}" : "")};";
MySqlCommand command = new MySqlCommand(sql, connection);
List <User> users = await Task.Run(() =>
{
MySqlDataReader reader = command.ExecuteReader();
List <User> users = new List <User>();
while (reader.Read())
{
users.Add(new User(Convert.ToInt32(reader[0]), reader[1].ToString(), reader[2].ToString()));
}
reader.Close();
connection.Close();
return(users);
});
return(users);
}
catch (Exception e)
{
Debug.LogError("Ошибка: " + e);
connection.Close();
return(null);
}
}
public static string SELECT(string[] Fields, DBTableNames Target, string condition)
{ // This utility func. helps building select queries.
/*
* PARAMETERS:
* -Fields => Name of the fields to be fetched from corresponding table
* -Target => Enum type of required table
* -condition => Any condition to be looked for in query
*
* OUTPUT:
* -constructed SELECT query
*
*/
var query = new StringBuilder();
query.Append("SELECT ");
int counter = 0;
foreach (string field in Fields)
{
query.Append(field);
if (!(counter + 1 == Fields.Length))
{
query.Append(", ");
}
counter++;
}
query.Append(" FROM ");
query.Append(DBTableConverter.Tables[Target]);
if (condition.Length != 0)
{
query.Append(" " + condition);
}
return(query.ToString());
}
