Solved

Executing a Stored Procedure from C# Web Service

Posted on 2010-09-15
7
677 Views
Last Modified: 2013-12-07
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

0
Comment
Question by:jmpatton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33685483
Try adding a commit in the procedure?
0
 

Author Comment

by:jmpatton
ID: 33685527
That didnt do it either.  Im baffeled
0
 

Expert Comment

by:VonBean
ID: 33685562
Sounds as if the code is not executing.  Perhaps a datasource needs to be updated.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33685567
Can you try that delete statement from SQL*Plus to make sure it actually deletes rows?
0
 

Author Comment

by:jmpatton
ID: 33685588
Yes I can run the statement from SQL plus and it works
0
 
LVL 20

Accepted Solution

by:
Daniel Van Der Werken earned 500 total points
ID: 33685990
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
 

Expert Comment

by:aryef
ID: 33712446
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

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question