Custom Overload for ExecuteNonQuery Method

In this Article I will try to cover ExecuteNonQuery method Provided in SqlCommand class.

Here we will try to create 6 overload for this method and will try to see what it means :-

Stuation 1 :-

We need to Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in the connection string.

In this we can write the Following overload :-

       public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null);
        }

Stuation 2 :-

We need to Execute a SqlCommand (that returns no resultset) against the database specified in the connection string using the provided parameters.

See the difference Here though No resultset(equivalent to situation 1)
But Parameters are there.

        public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");

            // Create & open a SqlConnection, and dispose of it after we are done
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Call the overload that takes a connection in place of the connection string
                return ExecuteNonQuery(connection, commandType, commandText, commandParameters);
            }
        }

Situation 3 :-

We need to Execute a stored procedure via a SqlCommand (that returns no resultset) against the database specified in
the connection string using the provided parameter values. This method will query the database to discover the parameters for the stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.

      public static int ExecuteNonQuery(string connectionString, string procedureName, params object[] parameterValues)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (procedureName == null || procedureName.Length == 0) throw new ArgumentNullException("procedureName");

            // If we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, procedureName);

                // Assign the provided values to these parameters based on parameter order
                AssignParameterValues(commandParameters, parameterValues);
 
                // Call the overload that takes an array of SqlParameters
                return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, procedureName, commandParameters);
            }
            else
            {
                // Otherwise we can just call the SP without params
                return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, procedureName);
            }
        }

Situation 4:-

We need to Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection.

       public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)
        {
            // Pass through the call providing null for the set of SqlParameters
            return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null);
        }

Situation 5:-

We Need to Execute a SqlCommand (that returns no resultset) against the specified SqlConnection using the provided parameters.

       public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            // Create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

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

            // Detach the SqlParameters from the command object, so they can be used again
            cmd.Parameters.Clear();
            if (mustCloseConnection)
                connection.Close();
            return retval;

        }

Situation 6 :-

We need to Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection using the provided parameter values. This method will query the database to discover the parameters for the stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.

        public static int ExecuteNonQuery(SqlConnection connection, string procedureName, params object[] parameterValues)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (procedureName == null || procedureName.Length == 0) throw new ArgumentNullException("procedureName");

            // If we receive parameter values, we need to figure out where they go
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
                SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, procedureName);

                // Assign the provided values to these parameters based on parameter order
                AssignParameterValues(commandParameters, parameterValues);

                // Call the overload that takes an array of SqlParameters
                return ExecuteNonQuery(connection, CommandType.StoredProcedure, procedureName, commandParameters);
            }
            else
            {
                // Otherwise we can just call the SP without params
                return ExecuteNonQuery(connection, CommandType.StoredProcedure, procedureName);
            }
        }

To Implement All these Overload you need to write Your SQLHELPER Class and accomodate the above Code in that class.

All Code sample is in C#.