Learn about the use of SMO objects in SQL2005 (-)

sql2005| objects

1. Brief Introduction:
In this series, I'll demonstrate how to use the SMO (SQL Management Objects) to implement a variety of common database management tasks, such as backup, recovery, indexing, integrity checking, etc.
In this article we will focus on how to compile an SMO application and how to connect to SQL Server using SMO and get some server information, and the sample code will use VB.net, C #, and VBScript, respectively. If you're not using Visual Studio 2005, I'll explain how to compile with the command line

2. Compile an SMO application:
To use the SMO object in the. NET application, we must add the SMO assemblies application in Visual Studio 2005 which is very easy, by selecting Project>add Reference menu item, Then choose the assemblies below
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoEnum
Microsoft.SqlServer.SqlEnum

Of course, if you want to cool a little, you can use the command line to compile, such as:
[Visual Basic]

Vbc/t:exe C:\SMOTEST.VB/R:
"C:\Program Files\Microsoft SQL Server\90\sdk\assemblies\microsoft.sqlserver.connectioninfo.dll",
"C:\Program Files\Microsoft SQL Server\90\sdk\assemblies\microsoft.sqlserver.smo.dll",
"C:\Program Files\Microsoft SQL Server\90\sdk\assemblies\microsoft.sqlserver.smoenum.dll",
"C:\Program Files\Microsoft SQL Server\90\sdk\assemblies\microsoft.sqlserver.sqlenum.dll"

[C #]

Csc/t:exe/out:c:\smotest.exe C:\SMOTEST.VB/R:
"C:\Program Files\Microsoft SQL Server\90\sdk\assemblies\microsoft.sqlserver.connectioninfo.dll",
"C:\Program Files\Microsoft SQL Server\90\sdk\assemblies\microsoft.sqlserver.smo.dll",
"C:\Program Files\Microsoft SQL Server\90\sdk\assemblies\microsoft.sqlserver.smoenum.dll",
"C:\Program Files\Microsoft SQL Server\90\sdk\assemblies\microsoft.sqlserver.sqlenum.dll"
3. Get connection
It is very easy to get a connection to SQL Server, for the default instance and for a win-authenticated connection, we simply create a SMO server object, the following code
[Visual Basic]

Imports Microsoft.SqlServer.Management.Smo

Module Smotest
Sub Main ()

Dim svr as Server = New Server ()
Console.WriteLine (SVR). Name & "" & Svr.Information.VersionString)

End Sub
End Module

[C #]

Using System;
Using Microsoft.SqlServer.Management.Smo;

Namespace Smotest
{
Class Program
{
static void Main ()
{
Server SVR = new server ();
Console.WriteLine (SVR). Name + "" + svr.Information.VersionString);
}
}
}

[VBScript]

Set svr = CreateObject ("Sqlsmo.") Server ")
WScript.Echo SVR. Name & "" & Svr.Information.VersionString
Set svr = Nothing

Of course, if your server is not the default, for example, by using the server name/instance name, you can use the following statement:
Dim svr as Server = New Server ((local))

Server SVR = new Server (@ "(local) \instance01")

If you want to be more complex, and do not want to use win authorization authentication, you can refer to the following code
[Visual Basic]

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common

Module Smotest
Sub Main ()

Dim svr as Server = New Server ()
Svr. Connectioncontext.loginsecure = False
Svr. Connectioncontext.login = "username"
Svr. Connectioncontext.password = "Password"
Console.WriteLine (SVR). Name & "" & Svr.Information.VersionString)

End Sub
End Module

[C #]

Using System;
Using Microsoft.SqlServer.Management.Smo;
Using Microsoft.SqlServer.Management.Common;

Namespace Smotest
{
Class Program
{
static void Main ()
{
Serverconnection conn = new serverconnection ();
Conn. LoginSecure = false;
Conn. Login = "username";
Conn. Password = "Password";
Server SVR = new server (conn);
Console.WriteLine (SVR). Name + "" + svr.Information.VersionString);
}
}
}

[VBScript]

Set svr = CreateObject ("Sqlsmo.") Server ")
Svr. Connectioncontext.loginsecure = False
Svr. Connectioncontext.login = "username"
Svr. Connectioncontext.password = "Password"
WScript.Echo SVR. Name & "" & Svr.Information.VersionString
Set svr = Nothing

The default SMO object behavior is to use connection pooling, and the connection is automatically established and released as required. If you do not want to do so, you can set the Autodisconnectmode property to change its behavior, if set of course the property value is Noautodisconnect, then the connection requires the display of the establishment and display of the shutdown, and this connection can not be used by the pool, by setting When the Nonpooledconnection property value is true, we explicitly point out that the current connection cannot be pool, and you can use Profiler to observe this behavior of the SMO application
As follows:
Default behavior-buffering connections
Dim svr as Server = New Server ()
Console.WriteLine (SVR). Name & "" & Svr.Information.VersionString)
The profiler Viewer will display the following message:

Non-default behavior-non-buffered connection
Dim svr as Server = New Server ()
Svr. Connectioncontext.nonpooledconnection = True
Svr. Connectioncontext.connect ()
Console.WriteLine (SVR). Name & "" & Svr.Information.VersionString)
Svr. Connectioncontext.disconnect ()
The profiler Viewer will display the following message:

Well, here's the end of the article, and we'll take a closer look at the SMO object and use it to do some simple backup, restore, and so on.

This article is from English-http://www.sqldbatips.com/showarticle.asp?ID=37