SQL Server™ Management Objects (SMO) offer developers a robust toolset for operations such as backing up and restoring databases, and issuing Data Definition Language (DDL) commands. Using SQL SMO you can also connect to SQL Server, iterate through a collection of database objects and perform a variety of tasks against them.

I have been created simple program using vb.net 2008 and sql server 2005 using SQL SMO.

The purpose of this program is to show the list of database available and show the data based on sql queries to datagrid. All the connection is using SQL SMO.

#1 step:

Add Reference Microsoft.SqlServer.SMO and Microsoft.SqlServer.ConnectionInfo

#2 step:

Create class SMOHelper:

Imports Microsoft.SqlServer.Management.Smo
Public Class SMOHelper
#Region "Fields"
<p style="padding-left:30px;">Private _serverName As String
Private _instance As String
Private _version As String
Private _isLocal As Boolean
Private _server As Server
Private _userName As String
Private _password As String
Private _useWindowsAuthentication As Boolean = True

#End Region
#Region "Properties"
<p style="padding-left:30px;">Public Property ServerName() As String
Get
Return _serverName
End Get
Set(ByVal value As String)
_serverName = value
End Set
End Property
Public Property Instance() As String
Get
Return _instance
End Get
Set(ByVal value As String)
_instance = value
End Set
End Property
Public Property version() As String
Get
Return _version
End Get
Set(ByVal value As String)
_version = value
End Set
End Property
Public Property isLocal() As Boolean
Get
Return _isLocal
End Get
Set(ByVal value As Boolean)
_isLocal = value
End Set
End Property
Public ReadOnly Property server() As Server
Get
Return _server
End Get
End Property
Public Property userName() As String
Get
Return _userName
End Get
Set(ByVal value As String)
_userName = value
End Set
End Property
Public Property password() As String
Get
Return _password
End Get
Set(ByVal value As String)
_password = value
End Set
End Property
Public Property useWindowsAuthentication() As Boolean
Get
Return _useWindowsAuthentication
End Get
Set(ByVal value As Boolean)
_useWindowsAuthentication = value
End Set
End Property

#End Region
#Region "Constructors"
<p style="padding-left:30px;">Public Sub New()
_server = New Server
End Sub
Public Sub New(ByVal serverAndInstanceName1 As String, ByVal username1 As String, ByVal password1 As String, ByVal useWindowsAuthentication1 As Boolean)
_server = New Server
ServerName = serverAndInstanceName1
userName = username1
password = password1
End Sub

#End Region
#Region "Methods"
<p style="padding-left:30px;">Public Function GetDatabaseNameList() As List(Of String)
Dim dbList As New List(Of String)()
For Each db As Database In server.Databases
dbList.Add(db.Name)
Next
Return dbList
End Function
Public Sub Connect()
server.ConnectionContext.ServerInstance = ServerName
If useWindowsAuthentication = True Then
server.ConnectionContext.LoginSecure = useWindowsAuthentication
Else
server.ConnectionContext.LoginSecure = useWindowsAuthentication
server.ConnectionContext.Login = userName
server.ConnectionContext.Password = password
End If
Try
server.ConnectionContext.Connect()
Catch ex As Exception
MsgBox("ERROR" &amp; ex.Message.ToString)
End Try
End Sub

#End Region
End Class

<img class="aligncenter size-full wp-image-124" title="PRINT" src="http://redsouljaz.files.wordpress.com/2009/05/print.jpg" alt="PRINT" width="457" height="576" />

#3 Step: Add this code to the form code

Imports Microsoft.SqlServer.Management.Smo

Public Class Form1
Dim smoHelper As New SMOHelper

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim dtServers As DataTable = SmoApplication.EnumAvailableSqlServers(False)
Dim sqlServerName As String
For Each row As DataRow In dtServers.Rows

sqlServerName = row("Server").ToString
If Not row("Instance") Is Nothing And row("Instance").ToString.Length &gt; 0 Then
sqlServerName += "\" + row("Instance").ToString
End If
Next
txtServerName.Text = sqlServerName.ToString

End Sub

Private Sub chkUseWindowsAuthentication_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkUseWindowsAuthentication.CheckedChanged
If chkUseWindowsAuthentication.Checked = True Then
txtPassword.Enabled = False
txtUserName.Enabled = False
Else
txtPassword.Enabled = True
txtUserName.Enabled = True
End If

End Sub

Private Sub btnConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConnect.Click
If txtServerName.Text &lt;&gt; "" And (chkUseWindowsAuthentication.Checked = True Or (txtPassword.Text &lt;&gt; "" And txtUserName.Text &lt;&gt; "")) Then

grpConnect.Enabled = False
SMOHelper = New SMOHelper(txtServerName.Text, txtUserName.Text, txtPassword.Text, chkUseWindowsAuthentication.Checked)
smoHelper.Connect()
If Not smoHelper.server Is Nothing Then
cboDatabase.DataSource = smoHelper.GetDatabaseNameList()
cboDatabase.Focus()

End If

End If

End Sub

Private Sub btnExecute_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExecute.Click
If txtSQL.Text &lt;&gt; "" Then
Dim db As Database = smoHelper.server.Databases(cboDatabase.SelectedValue.ToString)
If Not db Is Nothing Then
Dim sql As String
sql = txtSQL.Text
Try
If sql.Replace(" ", "").ToUpper.StartsWith("SELECT") Then
Dim ds As DataSet = db.ExecuteWithResults(txtSQL.Text)

dgvResults.DataSource = ds.Tables(0)

End If
Catch ex As Exception

End Try
End If
End If
End Sub
End Class

You can read more at http://msdn.microsoft.com/en-us/magazine/cc163409.aspx

In that site, you can download example of program in c#


  • Share/Bookmark