Kentico CMS 7.0 Developer's Guide

Displaying data from an external database or Web Service

Displaying data from an external database or Web Service

Previous topic Next topic Mail us feedback on this topic!  

Displaying data from an external database or Web Service

Previous topic Next topic JavaScript is required for the print function Mail us feedback on this topic!  

Besides displaying Kentico CMS content, you can also display data from your external database or Web Service. In this case, you need to develop a user control (ASCX) that will use ADO.NET to retrieve the data or that will contact the Web Service and call its methods. Since you can place any custom code into the user control, you will simply use the standard ASP.NET code you would use when creating the website from scratch.

 

Example: Retrieving data from the sample Northwind database

 

In this simple example, you will see how to display data on the sample Corporate Site from the Categories table of the Northwind database using ADO.NET. You may need to use some other database if you do not have the sample Northwind database on your server.

 

Open the web project in Visual Studio using the WebProject.sln file. Create the folder CorporateSite and create a new user control CustomData.ascx in this folder. It's important to create the control in this folder so that it's exported with your website later, when you decide to import the website on your live server.

 

Drag and drop the standard ASP.NET GridView control on your user control and set its ID to GridView1.

 

Add the following line to the beginning of the code behind file:

 

[C#]

 

using System.Data;

using System.Data.SqlClient;

 

Add the following code to the Page_Load method and replace the sample details in the connection string (database, server, user id, password, ...) with appropriate real ones:

 

[C#]

 

// create sql connection - you could use Oracle or OLEDB provider as well

 

SqlConnection cn = new SqlConnection("Persist Security Info=False;database=northwind;server=server1;user id=sa;password=psswd;Current Language=English;Connection Timeout=120;");

 

// create data adapter

SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM categories", cn);

DataSet ds = new DataSet();

     

// fill the dataset with data from database

da.Fill(ds);

 

// bind data to the grid view

GridView1.DataSource = ds;

GridView1.DataBind();

 

Save all changes.

 

Sign in as administrator to CMS Desk, select the /Examples document in the content tree and create a new Page (menu item). Call it Custom Data,choose the Create a blank page option and click Save.

 

devguide_clip0266

 

Switch to the Design tab, add a new web part General/User control and set its User control virtual path property value to ~/corporatesite/customdata.ascx.

 

Click OK and switch to the Live site mode of the page. You will see a grid with data from the external database:

 

devguide_clip0267

 

As you can see we used standard ASP.NET methods to display external data on the website.