Skip to main content

DevExpress v24.1 Update — Your Feedback Matters

Our What's New in v24.1 webpage includes product-specific surveys. Your response to our survey questions will help us measure product satisfaction for features released in this major update and help us refine our plans for our next major release.

Take the survey Not interested

Bind to Azure SQL Database

  • 6 minutes to read

This example illustrates how to bind a GridControl to an Azure SQL database:

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;

#See Also