Bind to Azure SQL Database
- 6 minutes to read
This example illustrates how to bind a GridControl to an Azure SQL database:
- bind directly to a DataTable;
- use the DevExpress SqlDataSource component;
- use an Entity Framework model.
Visit this GitHub page to review and download a complete sample project.
Important
Create a server-level firewall rule for your database to retrieve data from Azure servers.
#Common Implementation Details
#Base DataProvider class
All three approaches utilize classes that are derived from the base DataProvider
class. This class defines the virtual asynchronous GetDataAsync
method whose overrides fetch data from Azure.
public abstract class DataProvider<T> where T : class {
public virtual async Task<T> GetDataAsync() {
return await Task.FromResult(GetData());
}
public abstract T GetData();
}
#Connection Settings
The ConnectionSettings
class provides API that retrieves connection details.
public static class ConnectionSettings {
public static string DataSource, UserID, Password, InitialCatalog;
public static string SelectQuery;
public static void SetUp(string dataSource, string userId, string password, string initialCatalog) {
DataSource = dataSource;
UserID = userId;
Password = password;
InitialCatalog = initialCatalog;
}
public static string GetConnectionString() {
var builder = new SqlConnectionStringBuilder();
builder.DataSource = ConnectionSettings.DataSource;
builder.UserID = ConnectionSettings.UserID;
builder.Password = ConnectionSettings.Password;
builder.InitialCatalog = ConnectionSettings.InitialCatalog;
return builder.ConnectionString;
}
}
Connection credentials and a SQL query that retrieves data from the database are defined in the Main
method.
static void Main() {
ConnectionSettings.SetUp(
"your_server_name.database.windows.net",
"your_login",
"your_password",
"your_database_name");
ConnectionSettings.SelectQuery = "SELECT * FROM SalesLT.Product";
//. . .
Application.Run(new Main());
}
#Loading Panel
Binding methods described in this article retrieve data asynchronously, the application UI stays responsive while the application loads database records. To indicate that the data is being loaded, Data Grid shows its loading panel.
gridView.LoadingPanelVisible = true;
//get data and set the data source
gridView.LoadingPanelVisible = false;
#Bind to a DataTable
In this DataProvider descendant, the GetData
method override creates a standard System.Data.SqlClient.SqlDataAdapter
object that fills a System.Data.DataTable
with records.
public class DataTableProvider : DataProvider<DataTable> {
DataTableProvider() { }
static DataTableProvider instance;
public static DataTableProvider Instance {
get {
if(instance == null)
instance = new DataTableProvider();
return instance;
}
}
public override DataTable GetData() {
try {
using(var connection = new SqlConnection(ConnectionSettings.GetConnectionString())) {
connection.Open();
using(SqlDataAdapter adapter = new SqlDataAdapter(ConnectionSettings.SelectQuery, connection)) {
var dt = new DataTable();
adapter.Fill(dt);
return dt;
}
}
}
catch {
return null;
}
}
}
The DataTable
populated with records is then used as a Grid Control’s data source.
gridView.LoadingPanelVisible = true;
gridControl.DataSource = await DataTableProvider.Instance.GetDataAsync();
gridView.BestFitColumns();
gridView.LoadingPanelVisible = false;
#DevExpress SqlDataSource component
The following code initializes a new SqlDataSource that uses a custom query to select data. The query is defined in a separate DXApplication.Data.ConnectionSettings
class.
public class SqlDataSourceProvider : DataProvider<SqlDataSource> {
SqlDataSourceProvider() { }
static SqlDataSourceProvider instance;
public static SqlDataSourceProvider Instance {
get {
if(instance == null)
instance = new SqlDataSourceProvider();
return instance;
}
}
public async override Task<SqlDataSource> GetDataAsync() {
var source = CreateSqlDataSource();
await source.FillAsync();
return source;
}
SqlDataSource CreateSqlDataSource() {
MsSqlConnectionParameters connectionParameters = new MsSqlConnectionParameters(
ConnectionSettings.DataSource,
ConnectionSettings.InitialCatalog,
ConnectionSettings.UserID,
ConnectionSettings.Password,
MsSqlAuthorizationType.SqlServer);
SqlDataSource source = new SqlDataSource(connectionParameters);
CustomSqlQuery query = new CustomSqlQuery();
query.Name = "AzureQuery";
query.Sql = ConnectionSettings.SelectQuery;
source.Queries.Add(query);
return source;
}
}
To populate a Grid Control with SqlDataSource
data, specify the GridControl.DataMember property and GridControl.DataSource.
gridView.LoadingPanelVisible = true;
gridControl.DataSource = await SqlDataSourceProvider.Instance.GetDataAsync();
gridControl.DataMember = "AzureQuery";
gridView.BestFitColumns();
gridView.LoadingPanelVisible = false;
#Use an EntityFramework model
To use the EntityFramework, open the NuGet Package Manager (“Project | Manage NuGet Packages…”) and install the latest stable Entity Framework 6 package. If you downloaded the sample from GitHub, open this Manager and click “Restore” to re-upload the package.
Declare a System.Data.Entity.DbContext
descendant that stores Azure database records.
public partial class AdventureWorksLTContext : DbContext {
public AdventureWorksLTContext()
: base(ConnectionSettings.GetConnectionString()) {
}
public virtual DbSet<Product> Products { get; set; }
}
The GetDataAsync
method overrides loads data from the Azure database to a new DbContext instance and imports this data into a new List<Entity> object.
public class EFDataProvider : DataProvider<List<Product>> {
EFDataProvider() { }
static EFDataProvider instance;
public static EFDataProvider Instance {
get {
if(instance == null)
instance = new EFDataProvider();
return instance;
}
}
public async override Task<List<Product>> GetDataAsync() {
using(AdventureWorksLTContext context = new AdventureWorksLTContext()) {
context.Configuration.LazyLoadingEnabled = false;
var list = await context.Products.Include("SalesOrderDetails").ToListAsync();
return list;
}
}
}
The List with database records is then passed to the Data Grid.
gridView.LoadingPanelVisible = true;
gridControl.DataSource = await EFDataProvider.Instance.GetDataAsync();
gridView.BestFitColumns();
gridView.LoadingPanelVisible = false;