Forums (Obsolete)

Member

Peter - 1/21/2011 3:13:17 PM

GeneralConnection, Named Queries and External Databases

Scenario:

I have need to pull data from an external database table to populate a UniGrid. I wrote a named query in a related Custom Table and instantiated a CMS.DataEngine.GeneralConnection object using a Connection string that pointed to the external database.

Issue:

The GeneralConnection.ExecuteQuery(string queryName, object[,] parameters, string where, string orderBy, int topN, string columns, int offset, int maxRecords, ref int totalRecords) method throws the error:

[DataConnection.ExecuteQuery]: Query: Proc_CMS_Class_GetQueryByName: caused exception: Could not find stored procedure 'Proc_CMS_Class_GetQueryByName'.

Thoughts:

I would assume the DataConnection object, now pointing at the external database, is trying to find the Proc_CMS_Class_GetQueryByName stored proc there instead of looking in the Kentico database like it should. However, interestingly enough, SOMETIMES it works?!?

There are work arounds to this for sure but I prefer to use Kentico's objects when I can. I'm not sure if ExecuteQuery method was ever meant to be used this way BUT Kentico does allow it to be instantiated with a different connection string so I would suggest that, at some point, the method NOT assume the database it is connected to is the Kentico database when attempting to access Kentico DB objects (perhaps it can grab the default Connection object and call whatever Kentico objects are needed from that?).

Can't say this is a bug I'd like to use this method b/c it handles the UniGrid's OnDataReload event parameters nearly one-to-one.

Kentico Support

kentico_jurajo - 2/2/2011 2:52:27 AM

RE:GeneralConnection, Named Queries and External Databases

Hi,

It is not possible to use our DataEngine to connect to external DB - even it is possible to use connection string to external DB - it works sometimes because sometimes the external connection string is used but most of the time is used the default connection string to Kentico DB. It is possible to use connection string to external DB, but it is assumed that it is also Kentico's DB.

In this case you need to use custom connection string and get the data from external DB as it is usual in general in .NET.

I am sorry for this inconvenience.

Best regards,
Juraj Ondrus

Member

Peter - 2/2/2011 7:49:52 PM

RE:GeneralConnection, Named Queries and External Databases

Juraj,

I might beg to differ on this assessment. I have, with consistent success, been able to use the GeneralConnection object combined with named queries to access remote databases. i.e. I have created named queries on System and Custom tables which I use to access data in other databases.

For example, I created a data source web part (based on Kentico's QueryDataSource web part that, instead of assuring the Kentico database, allows the developer to select from the various connection strings that exist in the web.config file.

The rest of the web part remains the same. The developer selects a named query, columns, where clause, etc. The named query is then run BUT against the remote database. Works well except for the caveat I mentioned above.

Why, might you ask, would I want to do this? Consistency. I like the query management tools Kentico supplies and I want to use them regardless if the data is local or remote. It is sure better then embedded sql and, in some ways, more flexible than Stored Procs or Views.

Cheers!

Kentico Support

kentico_jurajo - 2/9/2011 2:35:49 AM

RE:GeneralConnection, Named Queries and External Databases

Hi,

If I am getting the right meaning, you will need to do this in two steps. In first step you will get the query or stored procedure using the connection to the Kentico DB and get it from there and in second step, you will execute the query or stored procedure on the external database using the external connection string.

Best regards,
Juraj Ondrus