Example #1
0
        /// <summary>
        /// Execute one or more SQL statements as a transaction. Roll back the transaction if any of them fail.
        /// </summary>
        public async Task Execute(
            IUi ui,
            int rowLimit,
            string connectionString)
        {
            List <string> statements = SqlQuery.SplitIntoStatements(Query);

            if (statements.Count == 0)
            {
                return;
            }

            var sw = new System.Diagnostics.Stopwatch();
            var currentStatement = "";

            try
            {
                using (var cn = new OdbcConnection(connectionString))
                {
                    // START CONNECT
                    ui.SetStatusLabel(ExecuteStatus.Connecting.Description());
                    sw.Restart();
                    cn.Open();
                    Duration_Connect = sw.Elapsed;
                    // END CONNECT

                    using (var cmd = new OdbcCommand())
                    {
                        cmd.Connection = cn;

                        // START EXECUTE
                        ui.SetStatusLabel(ExecuteStatus.Executing.Description());
                        sw.Restart();

                        using (var t = cn.BeginTransaction())
                        {
                            cmd.Transaction = t; // Assign transaction object for a pending local transaction.

                            foreach (var statement in statements)
                            {
                                if (string.IsNullOrEmpty(statement))
                                {
                                    continue;
                                }

                                currentStatement = statement.Trim();
                                cmd.CommandText  = currentStatement;

                                var statementResult = new StatementResult();

                                if (!currentStatement.StartsWith("select", StringComparison.OrdinalIgnoreCase))
                                {
                                    statementResult.RowsAffected = await cmd.ExecuteNonQueryAsync();

                                    statementResult.Duration_Execute = sw.Elapsed;
                                    // END EXECUTE
                                }
                                else
                                {
                                    using (var reader = await cmd.ExecuteReaderAsync())
                                    {
                                        statementResult.Duration_Execute = sw.Elapsed;
                                        // END EXECUTE

                                        // BEGIN STREAM
                                        ui.SetStatusLabel(ExecuteStatus.Streaming.Description());
                                        sw.Restart();

                                        statementResult.Data.Add(GetColumns(reader));

                                        int readCount = 1;
                                        while (readCount <= rowLimit && reader.Read())
                                        {
                                            statementResult.Data.Add(GetData(reader));
                                            readCount += 1;
                                        }

                                        statementResult.Duration_Stream = sw.Elapsed;
                                        // END STREAM

                                        ui.SetStatusLabel("");

                                        statementResult.RowsAffected = reader.RecordsAffected;
                                    }
                                }
                                Results.Add(statementResult);
                            }

                            try
                            {
                                t.Commit();
                            }
                            catch
                            {
                                t.Rollback();
                                throw;
                            }
                        }
                    }
                }
            }
            catch (ArgumentOutOfRangeException ex)
            {
                if (ex.Message == "Year, Month, and Day parameters describe an un-representable DateTime.")
                {
                    // filemaker allows importing incorrect data into fields, so we need to catch these errors!
                    Error = ExecuteError.UnrepresentableDateTimeValue.Description();
                }
                else
                {
                    Error = ex.Message;
                }

                Error += Environment.NewLine + Environment.NewLine + currentStatement;
            }
            catch (Exception ex)
            {
                Error = ex.Message + Environment.NewLine + Environment.NewLine + currentStatement;
            }
        }