/// <summary> /// Where you want to re-use an args object, this can be an easier method than using a new DBHelper /// </summary> /// <param name="args"></param> /// <param name="szQ"></param> /// <param name="onRead"></param> /// <exception cref="ArgumentNullException"></exception> public static void ExecuteWithArgs(DBHelperCommandArgs args, string szQ, Action <MySqlDataReader> onRead) { if (args == null) { throw new ArgumentNullException(nameof(args)); } if (szQ == null) { throw new ArgumentNullException(nameof(szQ)); } using (MySqlCommand comm = new MySqlCommand()) { InitCommandObject(comm, args); using (comm.Connection = new MySqlConnection(ConnectionString)) { MySqlDataReader dr = null; comm.CommandText = szQ; comm.Connection.Open(); using (dr = comm.ExecuteReader()) { onRead?.Invoke(dr); } } } }
/// <summary> /// Execute a query that does not have rows as a result (e.g., an insert or a delete rather than a select). /// </summary> /// <param name="args">The DBHelpercommandArgs object to use; if not provided, the CommandArgs property is used.</param> /// <param name="initCommand">A lambdat to add any necessary parameters or otherwise pre-process the command</param> /// <returns>True for success</returns> public bool DoNonQuery(DBHelperCommandArgs args, Action <MySqlCommand> initCommand = null) { if (args == null) { args = CommandArgs; } bool fResult = true; using (MySqlCommand comm = new MySqlCommand()) { InitCommandObject(comm, args); initCommand?.Invoke(comm); using (comm.Connection = new MySqlConnection(ConnectionString)) { LastError = string.Empty; try { comm.Connection.Open(); AffectedRowCount = comm.ExecuteNonQuery(); comm.CommandText = "SELECT Last_Insert_Id()"; LastInsertedRowId = Convert.ToInt32(comm.ExecuteScalar(), CultureInfo.InvariantCulture); } catch (MySqlException ex) { LastError = ex.Message; fResult = false; throw new MyFlightbookException("Exception DoNonQuery:\r\nCode = " + ex.ErrorCode.ToString(CultureInfo.InvariantCulture) + "\r\n" + ex.Message + "\r\n" + comm.CommandText, ex); } } } return(fResult); }
/// <summary> /// Executes a query /// </summary> /// <param name="args">Query string plus any pre-initialized parameters</param> /// <param name="initCommand">Lambda to add any necessary parameters or otherwise pre-process the command</param> /// <param name="readRow">Lambda to read a row. All rows will be read unless true is returned</param> /// <param name="rowMode">Row mode - read all available rows or just a single row</param> /// <returns>True for success. Sets LastError</returns> public bool ReadRows(DBHelperCommandArgs args, Action <MySqlCommand> initCommand, Action <MySqlDataReader> readRow, ReadRowMode rowMode) { if (args == null) { throw new ArgumentNullException(nameof(args)); } if (readRow == null) { throw new ArgumentNullException(nameof(readRow)); } bool fResult = true; using (MySqlCommand comm = new MySqlCommand()) { InitCommandObject(comm, args); using (comm.Connection = new MySqlConnection(ConnectionString)) { LastError = string.Empty; initCommand?.Invoke(comm); try { comm.Connection.Open(); using (MySqlDataReader dr = comm.ExecuteReader()) { if (dr.HasRows) { while (dr.Read()) { readRow(dr); if (rowMode == ReadRowMode.SingleRow) { break; } } } } } catch (MySqlException ex) { throw new MyFlightbookException("MySQL error thrown in ReadRows; Query = " + CommandText ?? string.Empty, ex, string.Empty); } catch (MyFlightbookException ex) { LastError = ex.Message; fResult = false; } catch (Exception ex) { MyFlightbookException exNew = new MyFlightbookException(String.Format(CultureInfo.CurrentCulture, "Uncaught exception in ReadRows:\r\n:{0}", comm.CommandText), ex); MyFlightbookException.NotifyAdminException(exNew); throw exNew; } } } return(fResult); }
public DBHelper(DBHelperCommandArgs args) : this() { if (args == null) { throw new ArgumentNullException("args"); } CommandArgs = args; }
public bool ReadRows(string szQ, Action <MySqlCommand> initCommand, Action <MySqlDataReader> readRow, ReadRowMode rowMode) { DBHelperCommandArgs args = new DBHelperCommandArgs() { QueryString = szQ }; return(ReadRows(args, initCommand, readRow, rowMode)); }
/// <summary> /// Returns a command object for the query string, to which parameters can be attached. /// THE CONNECTION IS NOT INITIALIZED - IT IS UP TO THE CALLER TO using(MySqlConnection = new ....) { } IT!!! /// </summary> /// <param name="args">The specification of the query string and any pre-initialized parameters</param> /// <returns>A usable MySqlCommand object</returns> public static void InitCommandObject(MySqlCommand comm, DBHelperCommandArgs args) { if (comm == null) { throw new ArgumentNullException(nameof(comm)); } if (args == null) { throw new ArgumentNullException(nameof(args)); } comm.CommandText = args.QueryString; comm.Parameters.AddRange(args.Parameters.ToArray()); if (args.Timeout > 0) { comm.CommandTimeout = args.Timeout; } }
public DBHelper(DBHelperCommandArgs args) : this() { CommandArgs = args ?? throw new ArgumentNullException(nameof(args)); }
public DBHelper() { CommandArgs = new DBHelperCommandArgs(); AffectedRowCount = -1; }
/// <summary> /// The MySqlCommand initialized for this query, including any and all parameters. /// </summary> public DBHelperCommandArgs ModelQueryCommand() { List <string> lstWhereTerms = new List <string>(); List <MySqlParameter> lstParams = new List <MySqlParameter>(); // Add each of the terms string[] rgTerms = FullText.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries); for (int i = 0; i < rgTerms.Length; i++) { AddQueryTerm(rgTerms[i], String.Format(CultureInfo.InvariantCulture, "FullText{0}", i), "Concat(model, ' ', manufacturers.manufacturer, ' ', typename, ' ', family, ' ', modelname, ' ', categoryclass.CatClass)", lstWhereTerms, lstParams); } string szPreferred = "0"; if (PreferModelNameMatch) { string szModelMatch = String.Format(CultureInfo.InvariantCulture, "%{0}%", ConvertWildcards(FullText)); szPreferred = "IF(model LIKE ?modelMatch, 1, 0)"; lstParams.Add(new MySqlParameter("modelMatch", szModelMatch)); } AddQueryTerm(CatClass, "qCatClass", "catclass", lstWhereTerms, lstParams); AddQueryTerm(rNormalizeModel.Replace(Model, string.Empty), "qModel", "REPLACE(REPLACE(model, ' ', ''), '-', '')", lstWhereTerms, lstParams); if (ModelName.StartsWith(ICAOPrefix, StringComparison.CurrentCultureIgnoreCase)) { AddQueryTerm(ModelName.Substring(ICAOPrefix.Length), "qFamilyName", "family", lstWhereTerms, lstParams); } else { AddQueryTerm(ModelName, "qModelName", "modelname", lstWhereTerms, lstParams); } AddQueryTerm(ManufacturerName, "qMan", "manufacturer", lstWhereTerms, lstParams); AddQueryTerm(TypeName, "qType", "typename", lstWhereTerms, lstParams); if (ManufacturerID != Manufacturer.UnsavedID) { lstWhereTerms.Add(" (models.idManufacturer = ?manID) "); lstParams.Add(new MySqlParameter("manID", ManufacturerID)); } string szHavingPredicate = String.Join(" AND ", lstWhereTerms.ToArray()); const string szQTemplate = @"SELECT models.*, manufacturers.manufacturer, categoryclass.CatClass as 'Category/Class', {0} AS AircraftIDs, {1} AS preferred FROM models INNER JOIN manufacturers on manufacturers.idManufacturer = models.idmanufacturer INNER JOIN categoryclass on categoryclass.idCatClass = models.idcategoryclass {2} {3} {4} {5}"; const string szQSamplesTemplate = @"LEFT OUTER JOIN (SELECT ac.idmodel, group_concat(DISTINCT img.imageKey separator ',') AS AircraftIDs FROM Images img INNER JOIN aircraft ac ON CAST(img.imageKey AS Unsigned)=ac.idaircraft WHERE img.VirtPathID=1 GROUP BY ac.idmodel) Samples ON models.idmodel=Samples.idmodel"; string szQ = String.Format(CultureInfo.InvariantCulture, szQTemplate, IncludeSampleImages ? "Samples.AircraftIDs" : "NULL", szPreferred, IncludeSampleImages ? szQSamplesTemplate : string.Empty, szHavingPredicate.Length == 0 ? string.Empty : String.Format(CultureInfo.InvariantCulture, " WHERE {0} ", szHavingPredicate), SortOrderFromSortModeAndDirection(SortMode, SortDir), (Limit > 0 && Skip >= 0) ? String.Format(CultureInfo.InvariantCulture, " LIMIT {0},{1} ", Skip, Limit) : string.Empty); DBHelperCommandArgs args = new DBHelperCommandArgs(szQ, lstParams); return(args); }