Read I Series (AS/400)

How to call it:

IseriesAccess thing = new IseriesAccess(IseriesAccess.Environment.DEV, "username", "password",IseriesAccess.Architecture.Bit32);

DataTable as400Data = thing.ReadISeriesTable("the library", "the table", "1=1");

This is the library

using System;

using System.Collections.Generic;

using System.Linq;

using System.Data.Odbc;

using System.Data;

namespace ConnectToAS400

{

    class IseriesAccess

    {

        #region Public constants and/or Enums

        /// <summary>

        /// ISeries flag which allows the restriction of the type of sql that can be executed

        /// </summary>

        private enum ConnType

        {

            AllowAnySQL = 0,

            AllowSelectAndCall=1,

            AllowSelectOnly=2

        }

        public enum Environment

        {

            DEV=1,

            FCB=2

        }

        public enum Architecture

        {

            Bit32=32,

            Bit64=64

        }

        #endregion

        #region Private members

        private string _pwd;

        private string _uid;

        private Environment _environment;

        private string _connectionString;

        private Architecture _architecture;

        #endregion

        #region Constructor

        /// <summary>

        /// Initializes ISeries object .

        /// </summary>

        /// <param name="environment">FCB or Dev</param>

        /// <param name="uid">The user under whose context the query will execute</param>

        /// <param name="password">The user's password</param>

        public IseriesAccess(Environment environment, string uid, string password, Architecture architecture )

        {

            _environment = environment;

            _uid = uid;

            _pwd = password;

            _architecture = architecture;

            _connectionString = "Driver={Client Access ODBC Driver (" + (int)_architecture + "-bit)};System=" + _environment.ToString() + ";CONNTYPE=" + ((int)ConnType.AllowSelectAndCall).ToString() + ";TRANSlATE=1;Uid=" + _uid + ";pwd=" + _pwd;

        }

        /// <summary>

        /// Initializes ISeries object .

        /// </summary>

        /// <param name="connectionString">The complete connectionstring. Note - if the ConnType attribute is not included inside the string, it will be appended to the end of the driver string with a value of 2. See IBM docs.</param>

        public IseriesAccess(string connectionString)

        {

            _connectionString = connectionString;

            if (connectionString.IndexOf("ConnType", StringComparison.CurrentCultureIgnoreCase) == -1)

            {

                _connectionString += "ConnType=" + ConnType.AllowSelectAndCall.ToString();

            }

        }

        #endregion

        /// <summary>

        /// Reads all columns from specified ISeries library and table using a specified filter

        /// </summary>

        /// <param name="libraryName">The ISeries Library (database)</param>

        /// <param name="fileName">The ISeries File (table)</param>

        /// <param name="filter">The where Clause. Do no include "Where".</param>

        /// <returns>A filled System.Data.DataTable</returns>

        public DataTable ReadISeriesTable(string libraryName, string fileName, string filter)

        {

            try

            {

                DataTable as400Data = new DataTable();

                string query = String.Format("Select * from {0}.{1}", libraryName, fileName);

                if (!string.IsNullOrEmpty(filter))

                {

                    query = query.Trim() + " where " + filter;

                }

                using (OdbcConnection c = new OdbcConnection(_connectionString))

                {

                    c.Open();

                    using (OdbcCommand selectCMD = new OdbcCommand(query))

                    {

                        selectCMD.Connection = c;

                        OdbcDataReader r = selectCMD.ExecuteReader();

                        as400Data.Load(r);

                    }

                    c.Close();

                }

                return as400Data;

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        /// <summary>

        /// Reads specific columns from specified ISeries library and table using a specified filter

        /// </summary>

        /// <param name="libraryName">The ISeries Library (database)</param>

        /// <param name="fileName">The ISeries File (table)</param>

        /// <param name="columnList">Comma-delimited list of columns to retrieve</param>

        /// <param name="filter">The where Clause. Do no include "Where".</param>

        /// <returns>A filled System.Data.DataTable</returns>

        public DataTable ReadISeriesTable(string libraryName, string fileName, string columnList, string filter)

        {

            try

            {

                DataTable as400Data = new DataTable();

                string query = String.Format("Select columnList from {0}.{1}", libraryName, fileName);

                if (!string.IsNullOrEmpty(filter))

                {

                    query = query.Trim() + " where " + filter;

                }

                using (OdbcConnection c = new OdbcConnection(_connectionString))

                {

                    c.Open();

                    using (OdbcCommand selectCMD = new OdbcCommand(query))

                    {

                        selectCMD.Connection = c;

                        OdbcDataReader r = selectCMD.ExecuteReader();

                        as400Data.Load(r);

                    }

                    c.Close();

                }

                return as400Data;

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

    }

}

CRM 2011 - Load a web page in an iframe using a URL thats in the record for the Entity.

I have an entity that has a URL as one of the fields. The Url points to a PDF document on the web. I want to display that PDF when the form opens, as part of the form. Something like this:

So the DocumentLocationfield points to a pdf. I want the PDF to be displayed, as shown below, when the update form is opened. Here's how:

1. Create a web resource for the solution, as follows:

<html><head>
    <title>HTML Web Resource</title>
  <meta charset="utf-8">
  </head><body>
    <script type="text/javascript">
     var objectExists=parent.window.Xrm.Page.getAttribute('fcbt_documentlocation');
     if(objectExists != null)
     {
           var fileName = parent.window.Xrm.Page.getAttribute('fcbt_documentlocation').getValue();
           document.write("Loading pdf from " + fileName);
           document.location.href=fileName;
     }
    </script>  
</body></html>

2. Associate that script with the OnLoad event of the form.

Thats it - now if you have a field called fcbt_documentlocation and it contains a valid url to a PDF, the form will open with the pdf displayed.

Note that the checking for null is essential - because it will always be null when inserting a row! 

Crm2011 - how to delete an entity using IOrganizationService

In order to delete the entity, you need its guid. If you do not have the Guid, you must get it first. The following code shows how to do this:

public void RemoveXrmBillHeaderByInvoiceId(string invoiceId, string org)

{

    //Get the service

    IOrganizationService xrmService = GetXrmService(org);

    //Build the Where clause using a QueryExpression

    QueryExpression query = new QueryExpression() { EntityName = "fcbt_billheader" };

    //Tell the query that I only want the guid column returned to me

    ColumnSet columnSet = new ColumnSet();       // new ColumnSet(true) -> will retreive all columns.

    columnSet.Columns.Add("fcbt_billheaderid");

    query.ColumnSet = columnSet;

    //Define the WHERE clause using a ConditionExpression

    ConditionExpression condition = new ConditionExpression() { AttributeName = "fcbt_invoiceid", Operator = ConditionOperator.Equal };

    condition.Values.Add(invoiceId);

    FilterExpression filter = new FilterExpression() { FilterOperator = LogicalOperator.And };

    filter.Conditions.Add(condition);

    query.Criteria = filter;

    EntityCollection entityCollection = xrmService.RetrieveMultiple(query);

    if (entityCollection.Entities.Count > 0)

    {

        Guid billHeaderId = entityCollection.Entities[0].GetAttributeValue<System.Guid>("fcbt_billheaderid");

        xrmService.Delete("fcbt_billheader", billHeaderId);

    }

}

crm2011 - writing optionset values to the database using IOrganizationService

Create an enum in C#, with the same values as are in CRM2011:

public enum ApprovalStatus

{

    ReviewRequired = 657070000,

    Approved = 657070001,

    Rejected = 657070002

}

This is my method for doing the add:

   public void AddBillHeader(string org, ApprovalStatus approvalStatus, DateTime billingDate)

        {

            IOrganizationService svc = GetXrmService(org);

            Entity bh = new Entity("fcbt_billheader");

            bh["fcbt_billingdate"] = billingDate;

            bh["fcbt_approvalstatus"] = new OptionSetValue((int)approvalStatus);

            svc.Create(bh);

         }

c# Get numeric value of Enum

I have an Enum as follows:

public enum ApprovalStatus

{

    ReviewRequired = 657070000,

    Approved = 657070001,

    Rejected = 657070002

}

I have an function that passes in the literal value of this enum:

public void AddBillHeader(string org, ApprovalStatus approvalStatus)

        {

            IOrganizationService svc = GetXrmService(org);

            Entity bh = new Entity("fcbt_billheader");

            bh["fcbt_approvalstatus"] = //NEED THE NUMERIC VALUE OF THE ENUM HERE

            svc.Create(bh);

        }

To do that, I have to convert the value to an Int - simple as that:

(int)approvalStatus;

crm 2011 Write to multiple Orgs with CRM, without initialization overhead

I have a simple class that writes to XRM. It has a method that adds to multiple orgs.

So the code below works; it creates the IOrganization service fine. But there is some overhead with it. What if you want to jump around, writing to one org and then the next? That can be expensive.

Uri OrganizationUri = new Uri(String.Format("{0}/{1}/XRMServices/2011/Organization.svc", _server, org));

using (OrganizationServiceProxy serviceProxy = new OrganizationServiceProxy(OrganizationUri, null, _credentials, null))

{

  serviceProxy.ServiceConfiguration.CurrentServiceEndpoint.Behaviors.Add(new ProxyTypesBehavior());

  return (IOrganizationService)serviceProxy;

}

The idea is to keep a list of initialized Orgs, and re-use them when needed. So I create a XRMGateway class with this private member:

private List<KeyValuePair<string, IOrganizationService>> _organizationServices;

In my constructor I initialize the object:

_organizationServices = new List<KeyValuePair<string, IOrganizationService>>();

Then whenever I want to write to the new org, I do this:

public void AddBillHeader(string org, ApprovalStatus approvalStatus, )

{

   IOrganizationService svc = GetXrmService(org);

   //do my stuff

}

The magic lies in the GetXrmService(org) method:

private IOrganizationService GetXrmService(string org)

{

    try

    {

        foreach (KeyValuePair<string, IOrganizationService> kvp in _organizationServices)

        {

            if (kvp.Key == org)

            {

                return kvp.Value;

            }

        }

        Uri OrganizationUri = new Uri(String.Format("{0}/{1}/XRMServices/2011/Organization.svc", _server, org));

        using (OrganizationServiceProxy serviceProxy = new OrganizationServiceProxy(OrganizationUri, null, _credentials, null))

        {

            serviceProxy.ServiceConfiguration.CurrentServiceEndpoint.Behaviors.Add(new ProxyTypesBehavior());

            KeyValuePair<string, IOrganizationService> kvp = new KeyValuePair<string, IOrganizationService>(org, (IOrganizationService)serviceProxy);

            _organizationServices.Add (kvp);

            return (IOrganizationService)serviceProxy;

        }

    }

    catch (Exception ex)

    {

        throw;

    }

}

Entity Framework Tutorial

Right here: http://entityframeworktutorial.net/