Solved

Executing a Stored Procedure from C# Web Service

Posted on 2010-09-15
7
633 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
7 Comments
 
LVL 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Install Problem 13 29
oracle- set role and grant privileges 6 28
SqlDataBase 7 46
Jquery/JSON passing value from one function to another 12 33
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

815 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now