C# VistaDBCommand.ExecuteNonQuery方法代码示例

        /// <summary>
        /// Call the export schema and data sql function to write out the xml file
        /// </summary>
        /// <param name="outputFilename">Name of the file to write to disk</param>
        public static void CallExportSchemaAndDataSQL(string outputFilename)
        {
            Console.WriteLine("Attempting to execute CLR Proc ExportSchemaAndData");
            using (VistaDBConnection connection = new VistaDBConnection())
            {
                connection.ConnectionString = SampleRunner.ConnectionString;
                connection.Open();

                try
                {
                    using (VistaDBCommand command = new VistaDBCommand())
                    {
                        // Straight forward way to call a function is just using SELECT
                        // You cannot EXEC a SqlFunction, and you cannot set the command here to be a stored proc
                        // Setting this command to a stored proc is a common error, the two are not the same
                        // SqlFunction = SELECT to call
                        // SqlProcdure = EXEC or direct call using StoredProcedure command type
                        command.Connection = connection;
                        command.CommandText = string.Format("SELECT ExportSchemaAndData('{0}');", outputFilename);
                        // This command does not return anything in the rowset, so just execute non query
                        command.ExecuteNonQuery();
                    }
                    Console.WriteLine(string.Format("Schema and Data export to {0}\\{1}.xml", Directory.GetCurrentDirectory(), outputFilename));
                }
                catch (Exception e)
                {
                    Console.WriteLine("Failed to execute CLR-Proc ExportSchemaAndData, Reason: " + e.Message);
                }
            }
        }

        tgDataResponse IDataProvider.ExecuteNonQuery(tgDataRequest request)
        {
            tgDataResponse response = new tgDataResponse();
            VistaDBCommand cmd = null;

            try
            {
                cmd = new VistaDBCommand();
                if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
                if (request.Parameters != null) Shared.AddParameters(cmd, request);

                switch (request.QueryType)
                {
                    case tgQueryType.TableDirect:
                        cmd.CommandType = CommandType.TableDirect;
                        cmd.CommandText = request.QueryText;
                        break;

                    case tgQueryType.StoredProcedure:
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = Shared.CreateFullName(request);
                        break;

                    case tgQueryType.Text:
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = request.QueryText;
                        break;
                }

                try
                {
                    tgTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);

                    #region Profiling

                    if (sTraceHandler != null)
                    {
                        using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "ExecuteNonQuery", System.Environment.StackTrace))
                        {
                            try
                            {
                                response.RowsEffected = cmd.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                esTrace.Exception = ex.Message;
                                throw;
                            }
                        }
                    }
                    else

                    #endregion Profiling

                    {
                        response.RowsEffected = cmd.ExecuteNonQuery();
                    }
                }
                finally
                {
                    tgTransactionScope.DeEnlist(cmd);
                }

                if (request.Parameters != null)
                {
                    Shared.GatherReturnParameters(cmd, request, response);
                }
            }
            catch (Exception ex)
            {
                CleanupCommand(cmd);
                response.Exception = ex;
            }

            return response;
        }

        esDataResponse IDataProvider.ExecuteNonQuery(esDataRequest request)
        {
            esDataResponse response = new esDataResponse();
            VistaDBCommand cmd = null;

            try
            {
                cmd = new VistaDBCommand();
                if (request.CommandTimeout != null) cmd.CommandTimeout = request.CommandTimeout.Value;
                if (request.Parameters != null) Shared.AddParameters(cmd, request);

                switch (request.QueryType)
                {
                    case esQueryType.TableDirect:
                        cmd.CommandType = CommandType.TableDirect;
                        cmd.CommandText = request.QueryText;
                        break;

                    case esQueryType.StoredProcedure:
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = Shared.CreateFullName(request);
                        break;

                    case esQueryType.Text:
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = request.QueryText;
                        break;
                }

                try
                {
                    esTransactionScope.Enlist(cmd, request.ConnectionString, CreateIDbConnectionDelegate);
                    response.RowsEffected = cmd.ExecuteNonQuery();
                }
                finally
                {
                    esTransactionScope.DeEnlist(cmd);
                }

                if (request.Parameters != null)
                {
                    Shared.GatherReturnParameters(cmd, request, response);
                }
            }
            catch (Exception ex)
            {
                CleanupCommand(cmd);
                response.Exception = ex;
            }

            return response;
        }

        /// <summary>
        /// Call the Stored Proc version to get the database version
        /// </summary>
        public static void CallGetDatabaseVersionProcedureSQL()
        {
            Console.WriteLine("Attempting to execute CLR Procedure GetVersionProcedure");

            using (VistaDBConnection connection = new VistaDBConnection(SampleRunner.ConnectionString))
            {
                connection.Open();

                try
                {

                    // Setup a command against the database like any other command, but then you have to change the command type
                    // to tell it you are calling a stored proc directly
                    using (VistaDBCommand command = new VistaDBCommand())
                    {
                        // Use our connection from above
                        command.Connection = connection;

                        // Put the name of the stored proc, you don't need to EXEC.  This command will be called directly
                        // Be sure to include all the parameters
                        command.CommandText = "GetVersionProcedure(@versionout);";
                        command.CommandType = System.Data.CommandType.StoredProcedure;  // Normally this is just text that is being executed

                        // Build up the parameter to the clr proc
                        VistaDBParameter outparam = new VistaDBParameter();
                        // This name has to match the entry in the commandtext
                        outparam.ParameterName = "@versionout";
                        // Telling it that this is an OUTPUT parameter
                        // This is how you should always get values back from a stored proc.  The return value in a stored proc is really only
                        // meant to tell you the number of rows affected, not values.
                        outparam.Direction = System.Data.ParameterDirection.Output;

                        // Add it to the command
                        command.Parameters.Add(outparam);

                        // We are not expecting any return values, and the output parameters will still be filled out
                        // using ExecuteNonQuery.  This saves object setup and teardown of a reader when we don't need it.
                        command.ExecuteNonQuery();

                        // Make sure the outparam is not null
                        if (outparam.Value != null)
                        {
                            // Print it to the console
                            Console.WriteLine(Convert.ToString(outparam.Value));
                        }

                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine("Failed to execute CLR Function GetVersionProcedure, Reason: " + e.Message);
                }
            }
        }

        /// <summary>
        /// Execute a VistaDBCommand (that returns no resultset) against the specified VistaDBTransaction
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(trans, CommandType.Text, "Select * from TableTransaction where ProdId=?", new VistaDBParameter("@prodid", 24));
        /// </remarks>
        /// <param name="transaction">A valid VistaDBTransaction</param>
        /// <param name="commandType">The CommandType (TableDirect, Text)</param>
        /// <param name="commandText">The T-SQL command</param>
        /// <param name="commandParameters">An array of VistaDBParamters used to execute the command</param>
        /// <returns>An int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(VistaDBTransaction transaction, CommandType commandType, string commandText, params VistaDBParameter[] commandParameters)
        {
            if( transaction == null ) throw new ArgumentNullException( "transaction" );
            if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );

            // Create a command and prepare it for execution
            VistaDBCommand cmd = new VistaDBCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, (VistaDBConnection)transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );

            // Finally, execute the command
            int retval = cmd.ExecuteNonQuery();

            // Detach the VistaDBParameters from the command object, so they can be used again
            cmd.Parameters.Clear();
            return retval;
        }