Click here to Skip to main content
Click here to Skip to main content

SQL Server Authentication using SMO

, 17 Dec 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
Setup a connection string by allowing the user to select the server and database
DBConnectionMgr.JPG

Introduction

In this article, I will be writing about generating a connection string by allowing the user to select a server and an instance along with database. The user could also choose between type of authentication. It works the same way SQL Server allows the user to connect to a database.

It's a simple application using SMO - SQL Server Management Objects.

Background

Ok, now I do assume that you have basic C# knowledge (Check boxes and drop-down list). Also I have used Application_Idle event and EventHandler in this project, so you will have to read about it first.

Working

I have used two objects:

  1. Microsoft.SqlServer.Management.Common.ServerConnection 
    	ServerConnection m_ServerConnection;
  2. Microsoft.SqlServer.Management.Smo.Server Server m_Server;

The DLLs for the above, namely Microsoft.SqlServer.Smo.dll and Microsoft.SqlServer.ConnectionInfo.dll should be present in the SQL Server directory (Program Files\Microsoft SQL Server\90\SDK\Assemblies). Make sure they are there, or else you will have to download these DLLs and include them in the references.

To start with, I have declared all the global variables in Properties.Settings. You can access it directly by opening Settings.settings file in Visual Studio IDE. There is a variable in it called BackupConnectionString which is supposed to hold the default connection string. It could be excluded if you wish so.

Now, we need to first populate the list of servers that we may have on our network. That is done in Application_Idle event. We then use that list as DataSource to the drop-down box that shows the list of all the servers available. The user should also be allowed to key in the Server name if he doesn't want to choose from the available servers:

DataTable dtServers = SmoApplication.EnumAvailableSqlServers(false);
dtServers.PrimaryKey = new DataColumn[] { dtServers.Columns[0] };
this.cmbServerName.DataSource = dtServers;

Then we need to check all the options that the user has selected, like the Authentication mode and all. On the basis of that, we change our connection string.

The user has selected the Server and the instance by now, and all the authentication details. We now need to show all the databases residing on that server when the user clicks on the Test connection button or clicks on the database drop down. However, we do ignore the system databases.

I have written a ConnectDatabase method for testing the connection:

if (!string.IsNullOrEmpty(this.cmbServerName.Text))
{
	this.m_ServerConnection = 
		new ServerConnection(this.cmbServerName.Text.ToString());
      //First check type of Authentication
      if (this.rdbWindowsAuthentication.Checked == true)   //Windows Authentication
      {
      	this.m_ServerConnection.LoginSecure = true;
            this.m_Server = new Server(this.m_ServerConnection);
      }
      else
      {
      	// Create a new connection to the selected server name
		this.m_ServerConnection.LoginSecure = false;
      	this.m_ServerConnection.Login = this.txtUserName.Text;       //Login User
	//Login Password
         this.m_ServerConnection.Password = this.txtPassword.Text;    
	this.m_ServerConnection.DatabaseName = this.cmbDbName.Text;  //Database Name
      	// Create a new SQL Server object using the connection we created
      	this.m_Server = new Server(this.m_ServerConnection);
      }
	return true;
}
return false;

To add the name of databases to the dropdown box:

this.cmbDbName.Items.Clear();
// Loop through the databases list
foreach (Database db in this.m_Server.Databases)
{
      //We don't want to be adding the System databases to our list
      //Check if database is system database
      if (!db.IsSystemObject) 
      {
 	     this.cmbDbName.Items.Add(db.Name); // Add database to combobox
      }
}
this.cmbDbName.SelectedIndex = 0;

When the user presses the Ok button, I add all the values to the global variables declared in Properties.Settings.

History

  • 17th December, 2008: Initial post

License

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

Share

About the Author

Rupesh Tarwade
Technical Lead
India India
I have been designing and developing systems in .NET since 2006. Started out with .NET 1.1, and progressed to 2.0, 3.0 and 3.5. I have recently dived into SharePoint 2010 development. My areas of expertise are Workflows in .NET and SharePoint.
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
GeneralMy vote of 5 PinmemberLambin13-Dec-10 5:47 
GeneralRe: My vote of 5 PinmemberRupesh Tarwade30-May-11 21:47 
GeneralA little suggestion about your code PinmemberLambin13-Dec-10 5:43 
QuestionWhy yellow "Gathering information" message showing - very long time? Pinmembergramir2-Aug-10 2:41 
AnswerRe: Why yellow "Gathering information" message showing - very long time? PinmemberLambin13-Dec-10 5:17 
GeneralCrash Pinmemberrctaubert8-May-09 4:50 
QuestionGreat Article but.... PinmemberRighteousBrother9-Jan-09 6:16 
AnswerRe: Great Article but.... PinmemberRupesh Tarwade11-Jan-09 21:01 
GeneralRe: Great Article but.... PinmemberRighteousBrother11-Jan-09 23:34 
GeneralReusing an existing dialog PinmemberArtem Smirnov23-Dec-08 10:13 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.1411028.1 | Last Updated 17 Dec 2008
Article Copyright 2008 by Rupesh Tarwade
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid