Ejemplo n.º 1
0
        /// <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);
                    }
                }
            }
        }
Ejemplo n.º 2
0
        /// <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);
        }
Ejemplo n.º 3
0
        /// <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);
        }
Ejemplo n.º 4
0
 public DBHelper(DBHelperCommandArgs args) : this()
 {
     if (args == null)
     {
         throw new ArgumentNullException("args");
     }
     CommandArgs = args;
 }
Ejemplo n.º 5
0
        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));
        }
Ejemplo n.º 6
0
 /// <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;
     }
 }
Ejemplo n.º 7
0
 public DBHelper(DBHelperCommandArgs args) : this()
 {
     CommandArgs = args ?? throw new ArgumentNullException(nameof(args));
 }
Ejemplo n.º 8
0
 public DBHelper()
 {
     CommandArgs      = new DBHelperCommandArgs();
     AffectedRowCount = -1;
 }
Ejemplo n.º 9
0
        /// <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);
        }