ContextQMD
Back to Devexpress

Bind to Azure SQL Database

windowsforms-401443-common-features-data-binding-bind-to-azure-data.md

latest14.8 KB
Original Source

Bind to Azure SQL Database

  • Oct 08, 2023
  • 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.

csharp
public abstract class DataProvider<T> where T : class {
    public virtual async Task<T> GetDataAsync() {
        return await Task.FromResult(GetData());
    }
    public abstract T GetData();
}
vb
Public MustInherit Class DataProvider(Of T As Class)
    Public Overridable Async Function GetDataAsync() As Task(Of T)
        Return Await Task.FromResult(GetData())
    End Function
    Public MustOverride Function GetData() As T
End Class

Connection Settings

The ConnectionSettings class provides API that retrieves connection details.

csharp
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;
    } 
}
vb
Public NotInheritable Class ConnectionSettings

    Private Sub New()
    End Sub

    Public Shared DataSource, UserID, Password, InitialCatalog As String
    Public Shared SelectQuery As String
    Public Shared Sub SetUp(ByVal dataSource As String, ByVal userId As String, ByVal password As String, ByVal initialCatalog As String)
        ConnectionSettings.DataSource = dataSource
        ConnectionSettings.UserID = userId
        ConnectionSettings.Password = password
        ConnectionSettings.InitialCatalog = initialCatalog
    End Sub
    Public Shared Function GetConnectionString() As String
        Dim builder = New SqlConnectionStringBuilder()
        builder.DataSource = ConnectionSettings.DataSource
        builder.UserID = ConnectionSettings.UserID
        builder.Password = ConnectionSettings.Password
        builder.InitialCatalog = ConnectionSettings.InitialCatalog
        Return builder.ConnectionString
    End Function
End Class

Connection credentials and a SQL query that retrieves data from the database are defined in the Main method.

csharp
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());
}
vb
Shared Sub 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())
End Sub

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.

csharp
gridView.LoadingPanelVisible = true;
//get data and set the data source
gridView.LoadingPanelVisible = false;
vb
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.

csharp
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;
            }
        }
    }
vb
Public Class DataTableProvider
    Inherits DataProvider(Of DataTable)

        Private Sub New()
        End Sub
        Private Shared i_instance As DataTableProvider
        Public Shared ReadOnly Property Instance() As DataTableProvider
            Get
                If i_instance Is Nothing Then
                    i_instance = New DataTableProvider()
                End If
                Return i_instance
            End Get
        End Property
        Public Overrides Function GetData() As DataTable
            Try
                Using connection = New SqlConnection(ConnectionSettings.GetConnectionString())
                    connection.Open()
                    Using adapter As New SqlDataAdapter(ConnectionSettings.SelectQuery, connection)
                        Dim dt = New DataTable()
                        adapter.Fill(dt)
                        Return dt
                    End Using
                End Using
            Catch
                Return Nothing
            End Try
        End Function
End Class

The DataTable populated with records is then used as a Grid Control’s data source.

csharp
gridView.LoadingPanelVisible = true;
gridControl.DataSource = await DataTableProvider.Instance.GetDataAsync();
gridView.BestFitColumns();
gridView.LoadingPanelVisible = false;
vb
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.

csharp
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;
        }
}
vb
Public Class SqlDataSourceProvider
    Inherits DataProvider(Of SqlDataSource)

        Private Sub New()
        End Sub
        Private Shared i_instance As SqlDataSourceProvider
        Public Shared ReadOnly Property Instance() As SqlDataSourceProvider
            Get
                If i_instance Is Nothing Then
                    i_instance = New SqlDataSourceProvider()
                End If
                Return i_instance
            End Get
        End Property
        Public Async Overrides Function GetDataAsync() As Task(Of SqlDataSource)
            Dim source = CreateSqlDataSource()
            Await source.FillAsync()
            Return source
        End Function
        Private Function CreateSqlDataSource() As SqlDataSource
            Dim connectionParameters As New MsSqlConnectionParameters(ConnectionSettings.DataSource, ConnectionSettings.InitialCatalog, ConnectionSettings.UserID, ConnectionSettings.Password, MsSqlAuthorizationType.SqlServer)
            Dim source As New SqlDataSource(connectionParameters)
            Dim query As New CustomSqlQuery()
            query.Name = "AzureQuery"
            query.Sql = ConnectionSettings.SelectQuery
            source.Queries.Add(query)
            Return source
        End Function
End Class

To populate a Grid Control with SqlDataSource data, specify the GridControl.DataMember property and GridControl.DataSource.

csharp
gridView.LoadingPanelVisible = true;
gridControl.DataSource = await SqlDataSourceProvider.Instance.GetDataAsync();
gridControl.DataMember = "AzureQuery";
gridView.BestFitColumns();
gridView.LoadingPanelVisible = false;
vb
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.

csharp
public partial class AdventureWorksLTContext : DbContext {
    public AdventureWorksLTContext()
        : base(ConnectionSettings.GetConnectionString()) {
    }
    public virtual DbSet<Product> Products { get; set; }
}
vb
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.

csharp
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;
            }
        }
}
vb
Public Class EFDataProvider
    Inherits DataProvider(Of List(Of Product))

        Private Sub New()
        End Sub
        Private Shared i_instance As EFDataProvider
        Public Shared ReadOnly Property Instance() As EFDataProvider
            Get
                If i_instance Is Nothing Then
                    i_instance = New EFDataProvider()
                End If
                Return i_instance
            End Get
        End Property
        Public Async Overrides Function GetDataAsync() As Task(Of List(Of Product))
            Using context As New AdventureWorksLTContext()
                context.Configuration.LazyLoadingEnabled = False
                Dim list = Await context.Products.Include("SalesOrderDetails").ToListAsync()
                Return list
            End Using
        End Function
End Class

The List with database records is then passed to the Data Grid.

csharp
gridView.LoadingPanelVisible = true;
gridControl.DataSource = await EFDataProvider.Instance.GetDataAsync();
gridView.BestFitColumns();
gridView.LoadingPanelVisible = false;
vb
gridView.LoadingPanelVisible = True
gridControl.DataSource = Await EFDataProvider.Instance.GetDataAsync()
gridView.BestFitColumns()
gridView.LoadingPanelVisible = False

See Also