How to use async for getting network SQL Servers

Question

In a user interface I need users to pick a SQL server from all available instances over the network. I use a button for users to click to display the servers. It take about 25 seconds after the button is clicked before you can display the list. I am thinking to get the server list ready when the page is loaded. So when users click on the button the list is already ready. However, it gets stuck before the page is loaded. remoteSqlServers is used for ItemSource.

 internal static ObservableCollection<string> remoteSqlServers = GetRemoteSqlServers().Result;   

    public static async Task<ObservableCollection<string>> GetRemoteSqlServers()
    {

        var SqlServers = await Task<ObservableCollection<string>>.Run(() =>
        {
            return new SqlEnumerationHelper().SqlEnumerateRemote();

        }).ConfigureAwait(false);

        return SqlServers;

    }

I changed it to below it doesn't get stuck before it loads but it still takes quite some seconds. It seems the async doens't work and don't know why.

 public static ObservableCollection<string> remoteSqlServers = new ObservableCollection<string>();

 public GetSqlViewModel()
{
  ...
    remoteSqlServers = GetRemoteSqlServers().Result;
  ...
 }

   public static async Task<ObservableCollection<string>> GetRemoteSqlServers()
    {

        var SqlServers = await Task<ObservableCollection<string>>.Run(() =>
        {
            return new SqlEnumerationHelper().SqlEnumerateRemote();

        }).ConfigureAwait(false);
        return SqlServers;

    }

Here is SqlEnumerateRemote:

  public ObservableCollection<string> SqlEnumerateRemote()
    {
        ObservableCollection<string> sqlServerNames = new ObservableCollection<string>();
        LogHelper.log("SqlEnumerationHelper:SqlEnumerateRemote", "Starting");

        try
        {
            //Make an initial call to the sql browser service to wake it up
            using (DataTable sqlSources = SqlDataSourceEnumerator.Instance.GetDataSources())
            {
                //There are issues with SqlDataSourceEnumerator, that it doesn't always return all sql instances
            }

            SqlDataSourceEnumerator instance = SqlDataSourceEnumerator.Instance;
            System.Data.DataTable dt = instance.GetDataSources();
            DataRow[] rows = dt.Select(string.Empty, "ServerName asc");

            if (rows.Length > 0)
            {
                foreach (DataRow dr in rows)
                {
                    string serverName = dr["ServerName"].ToString();
                    string instanceName = dr["InstanceName"].ToString();
                    if (instanceName.Length > 0)
                        serverName += "\\" + instanceName;
                    sqlServerNames.Add(serverName);
                }
            }
            LogHelper.log("SqlEnumerationHelper:SqlEnumerateRemote", "Ending");
        }
        catch (Exception e)
        {
            LogHelper.log("SqlEnumerationHelper:SqlEnumerateRemote", "SqlDataSourceEnumerator.GetDataSources and assoc processing failed" + e.Message);
        }

        return sqlServerNames;
    }

Would it work to set remoteSqlServers to null right away, then in the Task, populate it? to me it seems like its still waiting for remoteSqlServers to get a value before continuing. — Dylan
– Dylan, Commented Oct 13, 2015 at 19:09
If I set it to null first, at what point should I get the real value from GetRemoteSqlServers() method? — Jane wang
– Jane wang, Commented Oct 13, 2015 at 19:30

E-French · Accepted Answer · 2015-10-13 20:08:03Z

1

Round two!

I am unsure what SqlEnumerateRemote() is up to, but this shouldn't hang up your code while it works it's magic.

When you are trying to load remoteSqlServers it is waiting for your operation to complete before it initializes.

Initialize, then load like this:

internal static ObservableCollection<string> remoteSqlServers = new ObservableCollection<string>();

public MyControl()
{
    InitializeComponent();
    GetRemoteSqlServers();
    //so on and so forth
}

public static async Task GetRemoteSqlServers()
{
    foreach (var item in (await Task.Run(() =>
    { return new SqlEnumerationHelper().SqlEnumerateRemote(); })))
    {
        remoteSqlServers.Add(item);
    }
}

answered Oct 13, 2015 at 20:08

E-French

716 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

E-French · Accepted Answer · 2015-10-13 20:43:38Z

0

Edit: This answer is not the right one for this question, though I an going to leave it up for the occasional passerby.

Try out a BackgroundWorker, they are great for getting the heavy lifting away from the UI.

using System.Collections.ObjectModel;
using System.ComponentModel;

internal static ObservableCollection<string> remoteSqlServers;

public BackgroundWorker Sync = new BackgroundWorker();
public MyControl()
{
    InitializeComponent();

    remoteSqlServers = new ObservableCollection<string>();

    Sync.DoWork += Sync_DoWork;
    Sync.RunWorkerCompleted += Sync_RunWorkerCompleted;
    Sync.WorkerSupportsCancellation = true;
    Sync.RunWorkerAsync();

}

private void Sync_DoWork(object sender, DoWorkEventArgs e)
{
    BackgroundWorker worker = sender as BackgroundWorker;
    if ((worker.CancellationPending == true))
    {
        e.Cancel = true;
    }
    else
    {
        return SqlEnumerationHelper().SqlEnumerateRemote();
    }
}

private void Sync_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
    if ((e.Cancelled == false))
    {
        if (e.Result != null)
        {
            remoteSqlServers = (SqlServers)e.Result;
        }
    }
}

edited Oct 13, 2015 at 20:43

answered Oct 13, 2015 at 19:10

E-French

716 bronze badges

2 Comments

Eric J. Over a year ago

He's asking specifically about the async/await pattern. BackgroundWorker is quite a step back.

E-French Over a year ago

@Eric, can you take a look at round two on this question?

Collectives™ on Stack Overflow

How to use async for getting network SQL Servers

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related