Stuck Again Using Database Stuff

Question

4.00/5 (1 vote)

See more:

My goal here is to allow a user to select which instance of SQL Server to use for storing information. We have a couple of versions running on the network, so I have to let users find for themselves the right location.

According to the Watch Window output, my code for obtaining a table of available servers is workinh nicely, but I can't figure out how to display it in a way that the user can see. I tried using a DataGridView, but it won't let me bind to a table that has not yet been created:

namespace SQLTest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        public static DataTable GetSources()
        {
            SqlDataSourceEnumerator MyEnum = SqlDataSourceEnumerator.Instance;
            DataTable SourceTable = MyEnum.GetDataSources();
            return SourceTable;
        }
        private DataTable Servers = new DataTable();
        private static void DisplayData(System.Data.DataTable table)
        {
            foreach (System.Data.DataRow row in table.Rows)
            {
                foreach (System.Data.DataColumn col in table.Columns)
                {
                    Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
                }
                Console.WriteLine("============================");
            }
        }

        private void btnSelectSource_Click(object sender, EventArgs e)
        {
            Servers = GetSources();
            DisplayData(Servers);
            
        }
    }
}

The call to DisplayData() shows the proper information in a watch window, but when I try to bind the DataGridView to the newly-created dataset table, Servers, it won't let me.

I want the user to see all the available instances of SQL Server, and to select a destination database, but I haven't a clue where to go next. I've searched a few hundred articles tonight, but haven't come away with much of value relating to this particular problem.

How do I display a table generated on the fly, and allow the user to select one instance of the servers available?

Posted 24-Mar-11 17:36pm

Roger Wright

Add a Solution

Comments

Henry Minute 25-Mar-11 1:31am

Roger, if you go to this (http://sqlserversamples.codeplex.com/) page on codeplex select the Microsoft SQL Server Database Engine link and then download Microsoft Database Engine Samples (CLR, Full Text, SMO). When you have unpacked them, have a look at the Samples\Engine\Programmability\SMO\ServerConnect\CS solution (it's a VS2005 but updates with no problems to 2008). It shows a Server Selection Dialog. For how to use it look at the Samples\Engine\Programmability\SMO\ManageDatabases sample. Good stuff!

2 solutions

Solution 2

For the record, this worked quite nicely:

MSIL

private void GetSources()
        {
            this.lbxSelectServer.Items.Clear();
            SqlDataSourceEnumerator MyEnum = SqlDataSourceEnumerator.Instance;
            DataTable SourceTable = MyEnum.GetDataSources();
            using (SourceTable)
            {
                foreach (DataRow dr in SourceTable.Rows)
                {
                    string item=dr[0].ToString() + '/' + dr[1].ToString();
                    this.lbxSelectServer.Items.Add(item);
                }
            }

        }

        private void btnSelectSource_Click(object sender, EventArgs e)
        {
            GetSources();

        }

        private void lbxSelectServer_SelectedIndexChanged(object sender, EventArgs e)
        {
            string Database = lbxSelectServer.SelectedItem.ToString();
            MessageBox.Show(Database); // Add functional code
        }

Thanks, Henry, for the excellent clues!

Posted 25-Mar-11 18:41pm

Roger Wright

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Henry Minute · Accepted Answer · 2011-03-24T17:54:00

Solution 1

I use this:

C#

private void GetServers()
{
    this.cboxServers.Items.Clear();
    using (DataTable dt = SmoApplication.EnumAvailableSqlServers())
    {
        foreach (DataRow dr in dt.Rows)
        {
            this.cboxServers.Items.Add(dr[0]);
        }
    }
}

SmoApplication can be found in using Microsoft.SqlServer.Management.Smo

Obviously you can substitute a ListBox for my ComboBox.

MSDN Page for EnumAvailableSqlServers[^]

Hope this helps. :)

Posted 24-Mar-11 17:54pm

Henry Minute

Comments

Wayne Gaylard 25-Mar-11 3:41am

Thanks, learnt something new.

Roger Wright 25-Mar-11 23:39pm

I may give it a try, Henry, but the method I'm using works fine across the network. I just couldn't figure out a way to get the returned table to display. A listbox would be far simpler than the DataGridView, but the book I have for reference is rather limited. Thanks!