2. Retrieving Server Settings
A task you might find yourself performing often
with SMO is retrieving server settings of a particular SQL Server
instance. You might have to create a Windows application like the one
shown in Figure 5
or create a Web-based application to retrieve server settings and allow
your users to modify SQL Server settings. You can choose which settings
to allow users to change by simply deciding whether to show those
settings in the application.
The application in Figure 6-5
is really simple and builds on what you have learned so far. It allows
you to iterate through all the available servers, select a server, and
then select a database and display the server’s settings.
We can list all the servers available to our application by reusing the ListAllKnownInstances method (which is part of the custom code shown previously in Listing 2). ListAllKnownInstances returns a DataTable of all the servers that are available and visible to you. As shown in Listing 3, we then iterate through the DataRow collection for each row in the DataTable, verifying that the value is not null or blank and adding it to the combo box named cboServers using the Items.Add
method. Lastly, after we have filled the combo box with all the
available servers, we can select as the default value in the combo box
the local SQL Server instance’s name using the LocalServer.Name property.
Listing 3. Enumerating available servers and filling the combo box
private void btnSearch_Click(object sender, EventArgs e)
{
Cursor = Cursors.WaitCursor;
//get a DataTable of the servers
DataTable dtList = SMOUtilities.ListAllKnownInstances();
foreach (DataRow dr in dtList.Rows)
{
String ServerName;
ServerName = dr["Server"].ToString();
if (dr["Instance"] != null && dr["Instance"].ToString().Length > 0)
ServerName += @"\" + dr["Instance"].ToString();
if (cboServers.Items.IndexOf(ServerName) < 0)
cboServers.Items.Add(ServerName);
}
// By default select the local server
Server LocalServer = new Server();
String LocalServerName = LocalServer.Name;
if (LocalServer.InstanceName != null && LocalServer.InstanceName.Length > 0)
LocalServerName += @"\" + LocalServer.InstanceName;
int intCboIndex = cboServers.FindStringExact(LocalServerName);
if (intCboIndex >= 0)
cboServers.SelectedIndex = intCboIndex;
Cursor = Cursors.Default;
}
|
Next we need to connect to the selected server. We can do this by calling the ConnectSMO custom method described in Listing 6-4.
You call this method by passing in the name of the SQL Server instance
you want to connect to and a Boolean value indicating whether you want
to include system databases in the returned list of available databases.
The applications form has a check box to indicate whether you want to
include system databases. We can call ConnectSMO like this:
private void btnConnect_Click(object sender, EventArgs e)
{
ConnectSMO(cboServers.Text, chkSystemDB.Checked);
}
The ConnectSMO method is responsible for adding each database to the cboDatabase combo box. It first calls a generic method called FillComboWithDatabases, which is located in the SMOUtilities class. Listing 4 shows the ConnectSMO method that calls FillComboWithDatabases. After the FillComboWithDatabases method is called, ConnectSMO cleans up the user interface and returns control to the user.
Listing 4. The ConnectSMO custom method
private void ConnectSMO(string strServerName, bool blnIncludeSystemDB)
{
try
{
//use an hourglass
Cursor = Cursors.WaitCursor;
//clear out the items, if you don't do this then
//the combo box will have dupes when you click on
//the show system database box
cboDatabase.Items.Clear();
//this is a reusable function that fills the
//combo box with the available databases on a
//particular server, passing in the cbo by reg
//we will reuse this in a later example
SMOUtilities.FillComboWithDatabases(strServerName, blnIncludeSystemDB, ref
cboDatabase);
//now that the cbo is processed we
//will manipulate the UI
if (cboDatabase.Items.Count > 0)
{
lblDatabase.Text = cboDatabase.Items.Count.ToString() + " Database(s) found.
Select one:";
cboDatabase.Enabled = true;
}
else
lblDatabase.Text = "No Results";
chkSystemDB.Enabled = true;
}
catch (SmoException exSMO)
{
MessageBox.Show(exSMO.Message.ToString());
SetFormErrorParms();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
SetFormErrorParms();
}
finally
{
Cursor = Cursors.Default;
}
}
|
FillComboWithDatabases, shown in Listing 5, accepts a ComboBox object as well as the server name and a flag to indicate whether you should include system databases. FillComboWithDatabases obtains a reference to the SQL Server instance you want by using the Server object and then iterating through the Databases collection and adding each database to the cboDatabase combo box.
Listing 5. Filling the combo box with the databases using the FillComboWithDatabases custom method
public static void FillComboWithDatabases(string strServerName, bool blnIncludeSystemDB,
ref ComboBox cbo)
{
int intDBCount = 0;
// Fill the db combo
Server svr = new Server(strServerName);
foreach (Database db in svr.Databases)
{
//determine if the system database
//flag is set and include/exclude
if (blnIncludeSystemDB || !db.IsSystemObject)
{
intDBCount++;
cbo.Items.Add(db.Name);
}
}
}
|
Now
that we have our SQL Server instance selected, it’s time to select the
database and then display all its properties. In this application, we
will bind the properties of the database to a Windows Forms PropertyGrid. We’ll do this in the combo box’s SelectedIndexChanged event, which is shown in Listing 6.
If you want to show only certain properties, you can iterate through
the properties collection and display only the properties you want the
user to be able to see.
Listing 6. Reacting to the SelectedIndexChanged event
private void comboDB_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
//This function will get the database properties
//for selected database in the combobox
Cursor = Cursors.WaitCursor;
Server SelectedServer = new Server(cboServers.Text);
Database SelectedDatabase = SelectedServer.Databases[cboDatabase.Text];
propertyGrid1.SelectedObject =SelectedDatabase;
}
catch (SmoException exSMO)
{
DBPropLabel.Text = "";
MessageBox.Show(exSMO.ToString());
}
catch (Exception ex)
{
DBPropLabel.Text = "";
MessageBox.Show(ex.ToString());
}
finally
{
this.Cursor = Cursors.Default;
}
}
|
