locked
Stored Procedure keeps returning 0 RRS feed

  • Question

  • Hello,

    I wrote a plugin for CRM 2011 that runs a stored procedure. But for some reason the stored procedure just returns 0 all the time. Here is my code:

    using System;
    using System.Collections.Generic;
    using System.ServiceModel;
    using System.ServiceModel.Description;
    using System.Text;
    using Microsoft.Xrm.Sdk;
    using Microsoft.Xrm.Sdk.Query;
    using Microsoft.Xrm.Sdk.Discovery;
    using Microsoft.Xrm.Sdk.Client;
    using Microsoft.Xrm.Sdk.Messages;
    using Microsoft.Crm.Sdk.Messages;
    using System.Data.Sql;
    using System.Data.SqlClient;
    using System.Data;
    using System.Windows.Forms;
    
    namespace Rental
    {
        public class RentalOrder : IPlugin
        {
            private string InvtID;
            private DateTime shipDate;
            private DateTime returnDate;
            private string rentalValue;
            private string locationID;
            private decimal qtyAvail;
            private int result;
    
            public void Execute(IServiceProvider serviceProvider)
            {
                Entity targetEntityImage = null;
    
                var pluginExecutionContext = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
    
                if (pluginExecutionContext.PrimaryEntityName != "salesorderdetail" && pluginExecutionContext.MessageName != "Update")
                {
                    throw new Exception("This plugin should be fire on Order Product Entity Only!");
                }
    
                if (pluginExecutionContext.InputParameters.Contains("Target") && pluginExecutionContext.InputParameters["Target"] is Entity)
                {
                    targetEntityImage = (Entity)pluginExecutionContext.InputParameters["Target"];
                }
                else
                {
                    throw new Exception("No Target Entity Image found.");
                }
    
                try
                {
                    var factory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
                    var service = factory.CreateOrganizationService(pluginExecutionContext.UserId);
    
                    //Get Sales Order Detail
                    Guid salesorderdetailid = targetEntityImage.Id;
                    Entity salesorderdetail = service.Retrieve("salesorderdetail", salesorderdetailid, new Microsoft.Xrm.Sdk.Query.ColumnSet(true));
    
                    //Check Rental    
                    if (salesorderdetail.Contains("orbus_rentalstring") && (String)salesorderdetail.Attributes["orbus_rentalstring"] != "")
                    {
                        rentalValue = (String)salesorderdetail.Attributes["orbus_rentalstring"];
                    }
                    else
                    {
                        rentalValue = "0";
                    }
    
                    //Check Inventory
                    if (rentalValue == "1")
                    {
                        //Get Invtentory ID
                        if (salesorderdetail.Contains("productidname") && (String)salesorderdetail.Attributes["productidname"] != "")
                        {
                            InvtID = (String)salesorderdetail.Attributes["productidname"];
                        }
                        else
                        {
                            InvtID = "Not Rental";
                        }
                        
                        //Get Location ID
                        if (salesorderdetail.Contains("orbus_locationstring") && (String)salesorderdetail.Attributes["orbus_locationstring"] != "")
                        {
                            locationID = (String)salesorderdetail.Attributes["orbus_locationstring"];
                        }
                        else
                        {
                           locationID = "Not Rental";
                        }
    
                        //Get Ship Date
                        if (salesorderdetail.Contains("orbus_shipdate") && (DateTime)salesorderdetail.Attributes["orbus_shipdate"] != null)
                        {
                            shipDate = (DateTime)salesorderdetail.Attributes["orbus_shipdate"];
                        }
                        else
                        {
                            shipDate = DateTime.Today;
                        }
    
                        //Get Return Date
                        if (salesorderdetail.Contains("orbus_rentaldate") && (DateTime)salesorderdetail.Attributes["orbus_rentaldate"] != null)
                        {
                            returnDate = (DateTime)salesorderdetail.Attributes["orbus_rentaldate"];
                        }
                        else
                        {
                            returnDate = DateTime.Today;
                        }
    
                        //Get Quantity
                        if (salesorderdetail.Contains("quantity") && (Decimal)salesorderdetail.Attributes["quantity"] != 0)
                        {
                            qtyAvail = (Decimal)salesorderdetail.Attributes["quantity"];
                        }
                        else
                        {
                            qtyAvail = 0;
                        }
    
                        
                        SqlConnection cs = new System.Data.SqlClient.SqlConnection("Data Source=ORBUSSQL8;Initial Catalog=Orbus;Persist Security Info=True;User ID=sa;Password=sqlsa");
                       
    
                        SqlCommand cmd = new SqlCommand("OrbusRentalQty", cs);
                        cmd.CommandType = CommandType.StoredProcedure;
    
                        SqlParameter returnParameter = cmd.Parameters.Add("@Qty", SqlDbType.Int);
                        returnParameter.Direction = ParameterDirection.ReturnValue;
    
                        cmd.Parameters.Add("@SiteID", SqlDbType.VarChar).Value = locationID;
                        cmd.Parameters.Add("@InvtID", SqlDbType.VarChar).Value = InvtID;
                        cmd.Parameters.Add("@ReqDate", SqlDbType.DateTime).Value = shipDate;
                        cmd.Parameters.Add("@ReturnDate", SqlDbType.DateTime).Value = returnDate;
    
                        cs.Open();
                        cmd.ExecuteNonQuery();
                        result = (int)returnParameter.Value;                      
    
                        if (result > qtyAvail)
                        {
                            salesorderdetail["orbus_rentalresult"] = result;
                            service.Update(salesorderdetail);
                            salesorderdetail["orbus_alert"] = "No";
                            service.Update(salesorderdetail);
                            cs.Close();
                        }
                        else if (result == qtyAvail)
                        {
                            salesorderdetail["orbus_rentalresult"] = result;
                            service.Update(salesorderdetail);
                            salesorderdetail["orbus_alert"] = "No";
                            service.Update(salesorderdetail);
                            cs.Close();
                        }
                        else
                        {
                            //Update value to 'Yes'
                            salesorderdetail["orbus_rentalresult"] = result;
                            service.Update(salesorderdetail);
                            salesorderdetail["orbus_alert"] = "Yes";
                            service.Update(salesorderdetail);
                            cs.Close();
                        }
                            
                    }
                }
                catch (Exception ex)
                {
                    throw new InvalidPluginExecutionException("An error occured in the Rental Plugin.", ex);
                }
            }
        }
    }


    Arvin

    Thursday, August 14, 2014 7:38 PM

All replies

  • First of all, the plugin must run outside the sandbox for this to work.

    After that, I suggest you debug the plugin to find how far it gets.

    Something else to consider is how the stored procedure uses dates. CRM always uses DateTime types, and there may also be time-zone considerations, so I would avoid any exact matches on dates, but match on ranges instead


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Friday, August 15, 2014 8:56 AM
    Moderator