Using CMSConnectionContext with external datasource

Jay Heavner asked on March 24, 2016 22:30

I am trying to use the CMSConnectionContext with an external DB. I have my connection string setup properly in my web.config.

Here's my code. I got this pretty much verbatim from support.

using (var cs = new CMSConnectionContext("External"))
{
   var x = new DataQuery().From("CUSTOMER").Column("CustomerID").Where("LAST_NAME", QueryOperator.Equals, userLastName);
   x.ForEachRow((dr) =>
   {
      Response.Write(HTMLHelper.HTMLEncode(dr["CustomerID"] + "<br />"));
   });
}

The following two statements are executed against my DB.

exec sp_executesql N'SELECT [ClassXmlSchema], [ClassTableName]
FROM CMS_Class
WHERE [ClassName] = @ClassName',N'@ClassName nvarchar(9)',@ClassName=N'CMS.Class'
go
exec sp_executesql N'SELECT [ClassXmlSchema], [ClassTableName]
FROM CMS_Class
WHERE [ClassName] = @ClassName',N'@ClassName nvarchar(9)',@ClassName=N'CMS.Query'
go

Which won't work because I don't have a CMS_Class table. Does anyone have an example of DataQuery working with an external DB? Google cannot find a single example.

dataquery

Recent Answers

Dawid Jachnik answered on March 25, 2016 10:18 (last edited on March 25, 2016 10:19)

Hello,

You need to change that the source table isn't any class defined in Kentico, but a custom one.

new DataQuery().From(new QuerySource(new QuerySourceTable("CUSTOMER"))).Column("CustomerID").Where("LAST_NAME", QueryOperator.Equals, userLastName);

3 votesVote for this answer Mark as a Correct answer

Jay Heavner answered on March 25, 2016 14:52

I updated my code to use what you'd suggested but the SQL captured by the profiling tool still references CMS_Class

exec sp_executesql N'SELECT [ClassXmlSchema], [ClassTableName]
FROM CMS_Class
WHERE [ClassName] = @ClassName',N'@ClassName nvarchar(9)',@ClassName=N'CMS.Class'

The following code works perfectly but I'd like to understand how I could successfully do this with DataQuery.

GeneralConnection cn = ConnectionHelper.GetConnectionByName("ExternalCMSConnectionString");
QueryDataParameters parameters = new QueryDataParameters();
parameters.Add("@LastName", "Smith");
QueryParameters qParams = new QueryParameters("SELECT CustomerID FROM CUSTOMER WHERE LAST_NAME = @LastName", parameters, QueryTypeEnum.SQLQuery);
DataSet ds = cn.ExecuteQuery(qParams);

1 votesVote for this answer Mark as a Correct answer

Dawid Jachnik answered on March 25, 2016 15:51

I've debug this part of code, and that right, the DataQuery always check the classes.

I found some tricky solution, where you can use part of DataQuery API

var x = new DataQuery().From(new QuerySource(new QuerySourceTable("CUSTOMER"))).Column("CustomerID").Where("LAST_NAME", QueryOperator.Equals, userLastName);


    GeneralConnection cn = ConnectionHelper.GetConnectionByName("ExternalCMSConnectionString");

    QueryParameters qParams = new QueryParameters(x.ToString(true), null, QueryTypeEnum.SQLQuery);
    DataSet ds = cn.ExecuteQuery(qParams);

3 votesVote for this answer Mark as a Correct answer

Jay Heavner answered on March 25, 2016 16:06

If the DataQuery always checks the classes then it's not appropriate for external data sources correct? That would be contrary to the documentation.

I get what you're doing with the example but I might as well using native SQL rather than rely on a ToString() method of DataQuery to generate SQL.

Is this a bug? An idea that was never brought to fruition? Something else? We've traditionally used Entity Framework for external data sources but I thought if Kentico had something that approximated a lightweight ORM that it would be worth checking out.

0 votesVote for this answer Mark as a Correct answer

Dawid Jachnik answered on March 31, 2016 13:02

It's not a bug. Kentico API are created only to use with Kentico objects, not external one. You should stay with traditional framework to achive this. But if your datasource is in the file you can use example from the article named "External sources of data"

3 votesVote for this answer Mark as a Correct answer