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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Try adding a commit in the procedure?
0
jmpattonAuthor Commented:
That didnt do it either.  Im baffeled
0
VonBeanCommented:
Sounds as if the code is not executing.  Perhaps a datasource needs to be updated.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
Daniel Van Der WerkenIndependent 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.