private static string getOrderBy(DataToQuerySelect queryData) { string orderBy = ""; try { foreach (FieldsToOrder field in queryData.orderBy) { if (String.IsNullOrEmpty(orderBy)) { orderBy = field.filedName + " " + field.orderType; } else { orderBy = orderBy + ", " + field.filedName + " " + field.orderType;; } } if (!String.IsNullOrEmpty(orderBy)) { orderBy = " Order By " + orderBy; } } catch { orderBy = ""; } return(orderBy); }
public static DataTable dataBaseSelect(DataToQuerySelect queryData) { DataTable table = new DataTable(); string fields = getFieldsForSelect(queryData); string limit = getLimit(queryData); string orderBy = getOrderBy(queryData); string conditions = getConditionsForSelect(queryData); string query = "Select " + fields + " From " + queryData.tableName + conditions + orderBy + limit + ";"; try { if (connected == true) { NpgsqlCommand command = new NpgsqlCommand(query, dataBaseConnection); NpgsqlDataAdapter data = new NpgsqlDataAdapter(command); data.Fill(table); } else { table = null; } } catch (Exception) { table = null; } return(table); }
public static int getWinnigNumber(int roulette_bet_id) { DataTable table = new DataTable(); DataToQuerySelect queryData = new DataToQuerySelect(); int winningNumber = 0; try { queryData.tableName = "roulette_bets"; List <string> fields = new List <string>(); fields.Add("winner_number"); queryData.fields = fields; Conditions condition = new Conditions(); List <Conditions> conditionsList = new List <Conditions>(); condition.filedName = "id"; condition.condition = "="; condition.value = roulette_bet_id.ToString(); conditionsList.Add(condition); queryData.conditions = conditionsList; dataBaseConnect(); table = dataBaseSelect(queryData); dataBaseDesconnect(); winningNumber = Int32.Parse(findAFieldValueInTable(table: table, fieldName: "winner_number", lineNum: 0)); } catch { winningNumber = 0; } return(winningNumber); }
public static int getRouletteBetID(int roullete_id) { int id = 0; DataToQuerySelect queryData = new DataToQuerySelect(); try { queryData.tableName = "roulette_bets"; List <string> fields = new List <string>(); fields.Add("*"); queryData.fields = fields; Conditions condition = new Conditions(); List <Conditions> conditionsList = new List <Conditions>(); condition.filedName = "roullete_id"; condition.condition = "="; condition.value = roullete_id.ToString(); conditionsList.Add(condition); condition.filedName = "state_id"; condition.condition = "="; condition.value = State.getID("open"); conditionsList.Add(condition); queryData.conditions = conditionsList; dataBaseConnect(); DataTable table = dataBaseSelect(queryData); dataBaseDesconnect(); id = Int32.Parse(findAFieldValueInTable(table: table, fieldName: "id", lineNum: 0)); } catch { id = 0; } return(id); }
public static DataTable getWinnersByNumber(int roulette_bet_id, int number) { DataTable table = new DataTable(); DataToQuerySelect queryData = new DataToQuerySelect(); try { queryData.tableName = "bet_by_number"; List <string> fields = new List <string>(); fields.Add("user_id"); fields.Add("amount"); fields.Add("'number' \"betType\""); queryData.fields = fields; Conditions condition = new Conditions(); List <Conditions> conditionsList = new List <Conditions>(); condition.filedName = "roulette_bets_id"; condition.condition = "="; condition.value = roulette_bet_id.ToString(); conditionsList.Add(condition); condition.filedName = "number"; condition.condition = "="; condition.value = number.ToString(); conditionsList.Add(condition); queryData.conditions = conditionsList; dataBaseConnect(); table = dataBaseSelect(queryData); dataBaseDesconnect(); } catch { table = null; } return(table); }
private int getCreatedId() { int createdID = 0; DataToQuerySelect queryData = new DataToQuerySelect(); try { queryData.tableName = "roulettes"; queryData.limit = "1"; List <string> fields = new List <string>(); fields.Add("id"); queryData.fields = fields; FieldsToOrder field = new FieldsToOrder(); List <FieldsToOrder> orderBy = new List <FieldsToOrder>(); field.filedName = "id"; field.orderType = "desc"; orderBy.Add(field); queryData.orderBy = orderBy; dataBaseConnect(); DataTable table = dataBaseSelect(queryData); dataBaseDesconnect(); if (table != null) { createdID = Int32.Parse(findAFieldValueInTable(table, "id", 0)); } } catch { errorInConsult = true; } return(createdID); }
public static string getID(string state) { string stateResult = ""; DataToQuerySelect queryData = new DataToQuerySelect(); try { queryData.tableName = "states"; List <string> fields = new List <string>(); fields.Add("id"); queryData.fields = fields; Conditions condition = new Conditions(); List <Conditions> conditionsList = new List <Conditions>(); condition.filedName = "state"; condition.condition = "="; condition.value = state; conditionsList.Add(condition); queryData.conditions = conditionsList; dataBaseConnect(); DataTable table = dataBaseSelect(queryData); dataBaseDesconnect(); stateResult = findAFieldValueInTable(table: table, fieldName: "id", lineNum: 0); } catch { stateResult = "NotFound"; } return(stateResult); }
public List <Dictionary <string, string> > ToList() { DataToQuerySelect queryData = new DataToQuerySelect(); List <Dictionary <string, string> > response = new List <Dictionary <string, string> >(); try { queryData.tableName = "roulettes"; List <string> fields = new List <string>(); fields.Add("id"); fields.Add("state_id"); queryData.fields = fields; FieldsToOrder field = new FieldsToOrder(); List <FieldsToOrder> orderBy = new List <FieldsToOrder>(); field.filedName = "id"; field.orderType = "asc"; orderBy.Add(field); queryData.orderBy = orderBy; dataBaseConnect(); DataTable table = dataBaseSelect(queryData); dataBaseDesconnect(); response = getRoulettes(table); } catch { response = null; } return(response); }
private static string getLimit(DataToQuerySelect queryData) { string top = ""; if (!String.IsNullOrEmpty(queryData.limit)) { top = " limit " + queryData.limit + " "; } return(top); }
private static string getFieldsForSelect(DataToQuerySelect queryData) { string fields = ""; foreach (string field in queryData.fields) { if (String.IsNullOrEmpty(fields)) { fields = field; } else { fields = fields + ", " + field; } } return(fields); }
private static string getConditionsForSelect(DataToQuerySelect queryData) { string condition = ""; string resultConditions = ""; try { foreach (Conditions conditions in queryData.conditions) { switch (conditions.condition) { case "=": condition = conditions.filedName + " " + conditions.condition + " '" + conditions.value + "'"; break; default: condition = conditions.filedName + " " + conditions.condition + " " + conditions.value; break; } if (String.IsNullOrEmpty(resultConditions)) { resultConditions = condition; } else { resultConditions = resultConditions + " and " + condition; } condition = ""; } if (!String.IsNullOrEmpty(resultConditions)) { resultConditions = " Where " + resultConditions; } } catch { resultConditions = ""; } return(resultConditions); }
public bool rouletteExists() { bool exists; DataToQuerySelect queryData = new DataToQuerySelect(); try { queryData.tableName = "roulettes"; List <string> fields = new List <string>(); fields.Add("*"); queryData.fields = fields; Conditions condition = new Conditions(); List <Conditions> conditionsList = new List <Conditions>(); condition.filedName = "id"; condition.condition = "="; condition.value = id.ToString(); conditionsList.Add(condition); queryData.conditions = conditionsList; dataBaseConnect(); DataTable table = dataBaseSelect(queryData); dataBaseDesconnect(); if (table != null && table.Rows.Count > 0) { exists = true; } else { exists = false; } } catch { errorInConsult = true; exists = false; } return(exists); }
public bool loadRoulette(int _id) { DataToQuerySelect queryData = new DataToQuerySelect(); id = _id; bool loaded = false; bool exists = rouletteExists(); if (exists) { queryData.tableName = "roulettes"; List <string> fields = new List <string>(); fields.Add("state_id"); fields.Add("last_number"); queryData.fields = fields; Conditions condition = new Conditions(); List <Conditions> conditionsList = new List <Conditions>(); condition.filedName = "id"; condition.condition = "="; condition.value = id.ToString(); conditionsList.Add(condition); queryData.conditions = conditionsList; dataBaseConnect(); DataTable table = dataBaseSelect(queryData); dataBaseDesconnect(); last_number = Int32.Parse(findAFieldValueInTable(table: table, fieldName: "last_number", lineNum: 0)); state = findAFieldValueInTable(table: table, fieldName: "state_id", lineNum: 0); state = State.getState(state); loaded = true; } else { id = 0; } return(loaded); }