Reporting for WinForms - How to Store Connections Available in the Data Source Wizard

When a user adds a new data source, the list of available connections in the Data Source Wizard is populated with connections defined in the application configuration file. This example demonstrates how to use a custom storage (a connectons.xml) file to load and save Data Source Wizard connections. For this, implement the IConnectionStorageService and register it in the Report Designer component.

The CustomSqlDataConnection class (the SqlDataConnection descendant) in this example holds the names displayed in the connection list.

Important: In this example, the connection string in the XML file is not encrypted and exposes sensitive information (a username and a password). You should protect the file storage to prevent unauthorized access in a real-world application.

Files to Review

CustomConnectionStorageService.cs (VB: CustomConnectionStorageService.vb)
CustomSqlDataConnection.cs (VB: CustomSqlDataConnection.vb)
Form1.cs (VB: Form1.vb)

Documentation

Example Code

T119350/CustomConnectionStorageService.cs(vb)

C#
VB.NET

C#
using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.DataAccess.Sql;
using DevExpress.DataAccess.Wizard.Model;
using DevExpress.DataAccess.Wizard.Native;
using DevExpress.DataAccess.Wizard.Services;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Windows.Forms;
using System.Xml;

class CustomConnectionStorageService : IConnectionStorageService
{
    public bool CanSaveConnection => true;

    bool IConnectionStorageService.Contains(string connectionName)
    {
        return IncludeApplicationConnections ?
            DefaultStorage.Contains(connectionName) || 
                GetConnectionsFromXml().Any(c => string.Equals(c.Name, connectionName)) :
            GetConnectionsFromXml().Any(c => string.Equals(c.Name, connectionName));
    }

    IEnumerable<SqlDataConnection> IConnectionStorageService.GetConnections()
    {
        if (!File.Exists(FileName))
        {
            try
            {
                XmlDocument doc = new XmlDocument();
                XmlElement root = doc.CreateElement(xmlRootName);
                doc.AppendChild(root);
                doc.Save(FileName);

                SqlDataConnection defaultConnection = new CustomSqlDataConnection("Default Connection",
                    new MsSqlConnectionParameters("localhost", "Northwind", null, null, MsSqlAuthorizationType.Windows));
                SaveConnection(defaultConnection.Name, defaultConnection, true);
            }
            catch (Exception ex)
            {
                MessageBox.Show(string.Format("Cannot create '{0}' file because of exception:{1}{1}{2}", FileName,
                    Environment.NewLine, ex.Message));
                return null;
            }
        }

        return IncludeApplicationConnections ?
            DefaultStorage.GetConnections().Union(GetConnectionsFromXml()) :
            GetConnectionsFromXml();
    }

    public void SaveConnection(string connectionName, IDataConnection connection, bool saveCredentials)
    {
        try
        {
            XmlDocument doc = new XmlDocument();
            XmlElement root = null;
            if (File.Exists(FileName))
            {
                doc.Load(FileName);
                root = doc.DocumentElement;
                if (root != null)
                {
                    if (root.Name != xmlRootName)
                    {
                        MessageBox.Show(string.Format("Document element is '{0}', '{1}' expected",
                            root.Name, xmlRootName));
                        return;
                    }
                    if (root.SelectSingleNode(string.Format("Connection[Name = '{0}']", connectionName)) != null)
                        return;
                }
            }
            if (root == null)
            {
                root = doc.CreateElement(xmlRootName);
                doc.AppendChild(root);
            }

            XmlElement nameElement = doc.CreateElement("Name");
            nameElement.AppendChild(doc.CreateTextNode(connectionName));

            XmlElement connectionStringElement = doc.CreateElement("ConnectionString");
            connectionStringElement.AppendChild(doc.CreateTextNode(connection.CreateConnectionString(!saveCredentials)));

            XmlElement connectionElement = doc.CreateElement("Connection");
            connectionElement.AppendChild(nameElement);
            connectionElement.AppendChild(connectionStringElement);

            root.AppendChild(connectionElement);
            doc.Save(FileName);
        }
        catch (Exception ex)
        {
            MessageBox.Show(string.Format("Cannot save connection to '{0}' because of exception:{1}{1}{2}", FileName,
                Environment.NewLine, ex.Message));
        }
    }

    const string xmlRootName = "Connections";
    public string FileName { get; set; } = "connections.xml";
    public bool IncludeApplicationConnections { get; set; } = false;
    protected ConnectionStorageService DefaultStorage { get; } = new ConnectionStorageService();

    protected IEnumerable<SqlDataConnection> GetConnectionsFromXml()
    {
        var result = new List<SqlDataConnection>();
        try
        {
            XmlDocument doc = new XmlDocument();
            doc.Load(FileName);
            foreach (XmlNode node in doc.SelectNodes("Connections/Connection[Name][ConnectionString]"))
                result.Add(new CustomSqlDataConnection(node["Name"].InnerText,
                    new CustomStringConnectionParameters(node["ConnectionString"].InnerText)));
            return result;
        }
        catch (Exception ex)
        {
            MessageBox.Show(
                string.Format("Cannot get connections from '{0}' because of exception:{1}{1}{2}",
                FileName, Environment.NewLine, ex.Message));
            return new SqlDataConnection[0];
        }
    }
}

Visual Basic
Imports System.IO
Imports System.Xml
Imports DevExpress.DataAccess.ConnectionParameters
Imports DevExpress.DataAccess.Sql
Imports DevExpress.DataAccess.Wizard.Model
Imports DevExpress.DataAccess.Wizard.Native
Imports DevExpress.DataAccess.Wizard.Services

Friend Class CustomConnectionStorageService
    Implements IConnectionStorageService

    Public ReadOnly Property CanSaveConnection() As Boolean Implements IConnectionStorageService.CanSaveConnection
        Get
            Return True
        End Get
    End Property

    Private Function Contains(ByVal connectionName As String) As Boolean Implements IConnectionStorageService.Contains
        Return If(IncludeApplicationConnections, DefaultStorage.Contains(connectionName) OrElse GetConnectionsFromXml().Any(Function(c) String.Equals(c.Name, connectionName)), GetConnectionsFromXml().Any(Function(c) String.Equals(c.Name, connectionName)))
    End Function

    Private Function GetConnections() As IEnumerable(Of SqlDataConnection) Implements IConnectionStorageService.GetConnections
        If Not File.Exists(FileName) Then
            Try
                Dim doc As New XmlDocument()
                Dim root As XmlElement = doc.CreateElement(xmlRootName)
                doc.AppendChild(root)
                doc.Save(FileName)

                Dim defaultConnection As SqlDataConnection = New CustomSqlDataConnection("Default Connection", New MsSqlConnectionParameters("localhost", "Northwind", Nothing, Nothing, MsSqlAuthorizationType.Windows))
                SaveConnection(defaultConnection.Name, defaultConnection, True)
            Catch ex As Exception
                MessageBox.Show(String.Format("Cannot create '{0}' file because of exception:{1}{1}{2}", FileName, Environment.NewLine, ex.Message))
                Return Nothing
            End Try
        End If

        Return If(IncludeApplicationConnections, DefaultStorage.GetConnections().Union(GetConnectionsFromXml()), GetConnectionsFromXml())
    End Function

    Public Sub SaveConnection(ByVal connectionName As String, ByVal connection As IDataConnection, ByVal saveCredentials As Boolean) Implements IConnectionStorageService.SaveConnection
        Try
            Dim doc As New XmlDocument()
            Dim root As XmlElement = Nothing
            If File.Exists(FileName) Then
                doc.Load(FileName)
                root = doc.DocumentElement
                If root IsNot Nothing Then
                    If root.Name <> xmlRootName Then
                        MessageBox.Show(String.Format("Document element is '{0}', '{1}' expected", root.Name, xmlRootName))
                        Return
                    End If
                    If root.SelectSingleNode(String.Format("Connection[Name = '{0}']", connectionName)) IsNot Nothing Then
                        Return
                    End If
                End If
            End If
            If root Is Nothing Then
                root = doc.CreateElement(xmlRootName)
                doc.AppendChild(root)
            End If

            Dim nameElement As XmlElement = doc.CreateElement("Name")
            nameElement.AppendChild(doc.CreateTextNode(connectionName))

            Dim connectionStringElement As XmlElement = doc.CreateElement("ConnectionString")
            connectionStringElement.AppendChild(doc.CreateTextNode(connection.CreateConnectionString(Not saveCredentials)))

            Dim connectionElement As XmlElement = doc.CreateElement("Connection")
            connectionElement.AppendChild(nameElement)
            connectionElement.AppendChild(connectionStringElement)

            root.AppendChild(connectionElement)
            doc.Save(FileName)
        Catch ex As Exception
            MessageBox.Show(String.Format("Cannot save connection to '{0}' because of exception:{1}{1}{2}", FileName, Environment.NewLine, ex.Message))
        End Try
    End Sub

    Private Const xmlRootName As String = "Connections"
    Public Property FileName() As String = "connections.xml"
    Public Property IncludeApplicationConnections() As Boolean = False
    Protected ReadOnly Property DefaultStorage() As New ConnectionStorageService()

    Protected Function GetConnectionsFromXml() As IEnumerable(Of SqlDataConnection)
        Dim result = New List(Of SqlDataConnection)()
        Try
            Dim doc As New XmlDocument()
            doc.Load(FileName)
            For Each node As XmlNode In doc.SelectNodes("Connections/Connection[Name][ConnectionString]")
                result.Add(New CustomSqlDataConnection(node("Name").InnerText, New CustomStringConnectionParameters(node("ConnectionString").InnerText)))
            Next node
            Return result
        Catch ex As Exception
            MessageBox.Show(String.Format("Cannot get connections from '{0}' because of exception:{1}{1}{2}", FileName, Environment.NewLine, ex.Message))
            Return New SqlDataConnection(){}
        End Try
    End Function
End Class

T119350/CustomSqlDataConnection.cs(vb)

C#
VB.NET

C#
using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.DataAccess.Native;
using DevExpress.DataAccess.Sql;

class CustomSqlDataConnection : SqlDataConnection, INamedItem
{
    public CustomSqlDataConnection(string name, DataConnectionParametersBase connectionParameters)
        : base(name, connectionParameters) { }
    string INamedItem.Name
    {
        get => Name + " (Custom)";
        set
        {
            Name = value;
        }
    }
}

Visual Basic
Imports DevExpress.DataAccess.ConnectionParameters
Imports DevExpress.DataAccess.Native
Imports DevExpress.DataAccess.Sql

Friend Class CustomSqlDataConnection
    Inherits SqlDataConnection
    Implements INamedItem
    Public Sub New(name As String, connectionParameters As DataConnectionParametersBase)
        MyBase.New(name, connectionParameters)
    End Sub
    Private Property INamedItem_Name() As String Implements INamedItem.Name
        Get
            Return Name & " (Custom)"
        End Get
        Set
            Name = Value
        End Set
    End Property
End Class

T119350/Form1.cs(vb)

C#
VB.NET

C#
using DevExpress.DataAccess.Wizard.Services;
using DevExpress.XtraReports.UI;
using DevExpress.XtraReports.UserDesigner;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.Design;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace T119350
{
    public partial class Form1 : DevExpress.XtraEditors.XtraForm
    {
        CustomConnectionStorageService connectionStorageService;
        public Form1()
        {
            InitializeComponent();

            connectionStorageService = new CustomConnectionStorageService()
            {
                FileName = "connections.xml",
                IncludeApplicationConnections = true
            };
            ReplaceService(this.reportDesigner1, 
                typeof(IConnectionStorageService), connectionStorageService);
            this.reportDesigner1.DesignPanelLoaded += DesignMdiControllerOnDesignPanelLoaded;
        }

        private void ReplaceService(IServiceContainer container, 
            Type serviceType, 
            object serviceInstance)
        {
            if (container.GetService(serviceType) != null)
                container.RemoveService(serviceType);
            container.AddService(serviceType, serviceInstance);
        }

        private void DesignMdiControllerOnDesignPanelLoaded(object sender, 
            DesignerLoadedEventArgs e)
        {
            ReplaceService(e.DesignerHost, typeof(IConnectionStorageService), connectionStorageService);
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            XtraReport report = new XtraReport();
            this.reportDesigner1.OpenReport(report);
        }
    }
}

Visual Basic
Imports DevExpress.DataAccess.Wizard.Services
Imports DevExpress.XtraReports.UI
Imports DevExpress.XtraReports.UserDesigner
Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.ComponentModel.Design
Imports System.Data
Imports System.Drawing
Imports System.Linq
Imports System.Text
Imports System.Windows.Forms

Namespace T119350
    Partial Public Class Form1
        Inherits DevExpress.XtraEditors.XtraForm

        Private connectionStorageService As CustomConnectionStorageService
        Public Sub New()
            InitializeComponent()

            connectionStorageService = New CustomConnectionStorageService() With {
                .FileName = "connections.xml",
                .IncludeApplicationConnections = True}
            ReplaceService(Me.reportDesigner1, GetType(IConnectionStorageService), connectionStorageService)
            AddHandler Me.reportDesigner1.DesignPanelLoaded, AddressOf DesignMdiControllerOnDesignPanelLoaded
        End Sub

        Private Sub ReplaceService(ByVal container As IServiceContainer, ByVal serviceType As Type, ByVal serviceInstance As Object)
            If container.GetService(serviceType) IsNot Nothing Then
                container.RemoveService(serviceType)
            End If
            container.AddService(serviceType, serviceInstance)
        End Sub

        Private Sub DesignMdiControllerOnDesignPanelLoaded(ByVal sender As Object, ByVal e As DesignerLoadedEventArgs)
            ReplaceService(e.DesignerHost, GetType(IConnectionStorageService), connectionStorageService)
        End Sub

        Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
            Dim report As New XtraReport()
            Me.reportDesigner1.OpenReport(report)
        End Sub
    End Class
End Namespace

Vasily (DevExpress Support)

last modified 2 years ago

Modified: May 26, 2023 2:31 PM

Platform: WinForms

Product: Data Access Library

Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

Reporting for WinForms - How to Store Connections Available in the Data Source Wizard

Files to Review

Documentation

Example Code

Recently viewed tickets