Executing a Stored Procedure from C# Web Service

Hello,

I have a stored procedure that deletes items from a data table that are over 30 days old....

(create or replace
PROCEDURE PAGE_CLEANUP AS

   begin
   
   -- delete entries in EMAILS_SENT that are older than 30 days.
   
   DELETE FROM TRM_EMAILS_SENT
         WHERE TIMESTAMP < SYSDATE - 30;
   
end PAGE_CLEANUP;)

However, when I call the stored procedure from a method within my C# Webservice it doenst run the procedure.  The unit test says that everything works fine but there are no rows deleted when I check the database.

Any help would be greatly appreciated.

Thanks
public static void CleanUpPaging()
        {
            using (OracleConnection connection = new OracleConnection(ConnectionString))
            {
                connection.Open();
                OracleCommand command = new OracleCommand("PAGE_CLEANUP", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.ExecuteNonQuery();

            }
        }

Open in new window

jmpattonAsked:
Who is Participating?
 
Daniel Van Der WerkenConnect With a Mentor Independent ConsultantCommented:
Okay, I don't have the Oracle .NET data provider installed, so I will do this with the T-SQL provider, but I assume this should work for Oracle if you replace the names, etc.  See if this works.  



public static void CleanUpPaging()
        {
            using ( SqlConnection connection = new SqlConnection( ConnectionString ) )
            {
                connection.Open();
                using ( SqlCommand command = new SqlCommand( "PAGE_CLEANUP", connection ) )
                {
                    command.CommandType = CommandType.StoredProcedure;
                    int countRowsDeleted = command.ExecuteNonQuery();
                    // You can check here to see if the rows are deleted by 
                    // seeing if countRowsDeleted > 0 or not.
                    command.Transaction.Commit();
                }
            }
        }

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Try adding a commit in the procedure?
0
 
jmpattonAuthor Commented:
That didnt do it either.  Im baffeled
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
VonBeanCommented:
Sounds as if the code is not executing.  Perhaps a datasource needs to be updated.
0
 
slightwv (䄆 Netminder) Commented:
Can you try that delete statement from SQL*Plus to make sure it actually deletes rows?
0
 
jmpattonAuthor Commented:
Yes I can run the statement from SQL plus and it works
0
 
aryefCommented:
This should work for you. Pass the parameters as string like "param1=value1;param2=value2" etc.
The trick is with OracleDbType - it should be correctly defined for each SP parameter but it seems that  varchar2 will work for most cases.

public int RunOracleStoredProcedure(OracleConnection conn, string statement, string parameters)
        {
            int ret = -1;
           OracleCommand cmd = null;

            string[] values = parameters.Split(',');
           
            if(conn.State != ConnectionState.Open)
                conn.Open();

            try
            {
                cmd = new OracleCommand(statement, conn);

                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                foreach (string item in values)
                {
                    string[] paramname;
                    paramname = item.Split('=');
                   
                        string ParamName = paramname[0].TrimEnd('=');
                       
                        if (ParamName != "")
                        {
                        string ParamValue = paramname[1].TrimStart('=');
                        cmd.Parameters.Add(":" + ParamName, OracleDbType.Varchar2, ParamValue, ParameterDirection.Input);
                   
                        }
                }

               
                       
                cmd.ExecuteScalar();
             
                   }
            catch (Exception ex)
            {
              errorMessage = ex.Message.ToString();
              ret = -1;
            }
                       return ret;
        }
0
All Courses

From novice to tech pro — start learning today.