Microsoft CRM integration: Oracle database access from MS CRM

Today's article topic is a customization possibility demonstration for the user web interface of Microsoft CRM. As an example we'll use MS CRM integration with an ASP.Net application, accessing customer data access, when customers are stored in Oracle 10g database. Let's begin: First, let's create the table to store customer information in Oracle database. We'll use web application iSQL for table metadata manipulation: Table is now created and contains four fields: CUSTOMER_ID, FIRST_NAME, LAST_NAME и ADDRESS. Fill it with text data: Now we'll work with data access to Oracle database from ASP.Net application. We should download from Oracle site Now let's create Linked Server in MS SQL Server 2000: Note: in the Security tab we need to use security context with the credentials, having valid access to Oracle Database. Linked Server is ready - let's test it functioning - open table list. We should see customer table there: Now we'll create stored procedure for Oracle data access: SET ANSI_NULLS ON SET ANSI_WARNINGS ON GO CREATE PROCEDURE MyCustomersList AS SELECT * FROM OPENQUERY(ORACLE, 'SELECT * FROM Customer') RETURN Next step is customizing Microsoft CRM using interface. We'll add customer list button into Quote screen toolbar. Edit isv.config: Change Url to your host name. To create ASPX page we'll use RAD for ASP.Net - WebMatrix: Create new page for data access: Change it's code to access our data: Sub Page_Load(Sender As Object, E As EventArgs) &nbsp&nbsp Dim ConnectionString As String = "server=(local);database=Albaspectrum;trusted_connection=true" &nbsp&nbsp Dim CommandText As String = "EXEC MyCustomersList" &nbsp&nbsp Dim myConnection As New SqlConnection(ConnectionString) &nbsp&nbsp Dim myCommand As New SqlCommand(CommandText, myConnection) &nbsp&nbsp myConnection.Open() &nbsp&nbsp DataGrid1.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection) &nbsp&nbsp DataGrid1.DataBind() End Sub Now we'll test our web application by calling it from MS CRM. Boris Makushkin is senior software developer in Alba Spectrum Technologies US nation-wide Great Plains, Microsoft CRM customization company, based in Chicago and having locations in multiple states and internationally (