Solved

Executing a Stored Procedure from C# Web Service

Posted on 2010-09-15
7
617 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 19

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

762 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

19 Experts available now in Live!

Get 1:1 Help Now