Microsoft CRM Integration: Oracle Database Access from MS CRM

Today's article topic is a customization possibility demonstration for a user Web interface of Microsoft CRM. As an example, I'll use MS CRM integration with ASP.Net application, accessing customer data access when customers are stored in an Oracle 10g database. Here's how to begin:

  1. First, create the table to store customer information in an Oracle database. You'll use an iSQL Web application for table metadata manipulation:
  2. The table is now created and contains four fields: CUSTOMER_ID, FIRST_NAME, LAST_NAME, and ADDRESS. Fill it with text data:
  3. Now, work with data access to an Oracle database from the ASP.Net application. You should download http://www.oracle.com Windows Instant Client from the Oracle site. You don't have to install it; just unpack all the files in the directory of your choice—for example, c:\oracle—and set the environmental variable TNS_ADMIN, pointing to this directory.
  4. In the c:\oracle directory (or where TNS_ADMIN points), create file tnsnames.ora as in the following (change the host and service names):
  5. ORCL1 =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)
                   (HOST = oraclehost.yourdomain.com)
                   (PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = ORCL1)
        )
      )
    
  6. Make a correction to the Windows registry to have MS SQL Linked Server work properly with the Oracle OLE DB Provider. In the KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI hive, make these changes:
  7. OracleXaLib  = "oracleclient8.dll"
    OracleSqlLib = "orasql8.dll"
    OracleOciLib = "oci.dll"
    
  8. Now, create a Linked Server in MS SQL Server 2000:
  9. Note: On the Security tab, you need to use a security context with the credentials so that you have valid access to the Oracle Database.

  10. Linked Server is now ready. You can test it functioning; open a table list. You should see a customer table there:
  11. Now, create a stored procedure for Oracle data access:
  12. SET ANSI_NULLS ON
    SET ANSI_WARNINGS ON
    GO
    
    CREATE PROCEDURE MyCustomersList AS
    
    SELECT * FROM OPENQUERY(ORACLE, 'SELECT * FROM Customer')
    
    RETURN
    
  13. The next step is customizing the Microsoft CRM using interface. Add a customer list button into the Quote screen toolbar. Edit isv.config:
    <quote>
       <ToolBar ValidForCreate="0" ValidForUpdate="1">
       <Button Title="Customers"
               ToolTip="Show customers list from Oracle DB"
               Icon="/_imgs/ico/16_convert.gif"
               Url="http://APOLLO/MyCustomersList.aspx"
               PassParams="1" WinParams="" WinMode="1"/>
       </ToolBar>
       <NavBar ValidForCreate="0" ValidForUpdate="1">
       </NavBar>
    </quote>
    
    Change the URL to your host name.
  14. To create an ASPX page, use RAD for ASP.Net - WebMatrix:
  15. Create a new page for data access:
  16. Change its code to access your data:
  17. Sub Page_Load(Sender As Object, E As EventArgs)
    
       Dim ConnectionString As String =
          "server=(local);database=Albaspectrum;
                          trusted_connection=true"
       Dim CommandText As String = "EXEC MyCustomersList"
    
       Dim myConnection As New SqlConnection(ConnectionString)
       Dim myCommand As New SqlCommand(CommandText, myConnection)
    
       myConnection.Open()
    
       DataGrid1.DataSource = myCommand.ExecuteReader
                             (CommandBehavior.CloseConnection)
       DataGrid1.DataBind()
    End Sub
    
  18. Now, test your Web application by calling it from MS CRM:

About the Author

Boris Makushkin is a lead software developer with Alba Spectrum Technologies, a USA Nationwide MS CRM consulting company.



Comments

  • JPG

    Posted by Mohamed Ragab on 05/17/2013 01:32am

    I cannot see the attached JPGs in your post , from more thane 1 pc .

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Companies undertaking an IT project need to find the right balance between cost and functionality. It's important to start by determining whether to build a solution from scratch, buy an out-of-the-box solution, or a combination of both. In reality, most projects will require some system tailoring to meet business requirements. Decision-makers must understand how much software development is enough and craft a detailed implementation plan to ensure the project's success. This white paper examines the different …

  • This paper examines the difficulties and pain points that organizations encounter as they seek to straddle the conflicting pressures of ever increasing email volumes on one hand, and the need for faster, more flexible accessibility on the other. It also explores what practices and policies are currently in use when it comes to archiving emails, and the ways in which businesses can improve practices in this crucial area. There are, for example, options available which can bring email archiving up-to-date, with …

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date