Dot net SQL server operations

Li honggen

SQLDMO (SQL Distributed Management Objects, SQL Distributed Management object) encapsulates Objects in the Microsoft SQL Server 2000 database. The SQL-DMO allows you to write applications in languages that support automation or COM to manage all parts of SQL Server installation. SQL-DMO is the application interface (API) used by SQL Server Enterprise Manager in SQL Server 2000; therefore, applications that use SQL-DMO can execute all the functionality that SQL Server Enterprise Manager performs. </P> <p> SQL-DMO is used for any automated or COM applications that must contain SQL Server Management, such as: <br/> 1. encapsulate SQL Server as your data storage and try to reduce your SQL Server management tasks. <Br/> 2. Applications with special management logic are incorporated into the program itself. <Br/> 3. You want to integrate the SQL Server management task application in your user interface. </P> <p> the SQLDMO object comes from SQLDMO. dll. SQLDMO. dll is released along with SQL Server2000. SQLDMO. dll itself is a COM Object. Therefore, it must be referenced in your. NET project first. <Br/>

Obtain the list of SQL servers in the Network: <br/> cbDatabase is a drop-down list box <br/>

'Get SQL server list' must install SQL server 2000 SP2 and later Dim I As Short Dim sqlApp As New SQLDMO. application () Dim ServerName As SQLDMO. nameList ServerName = sqlApp. listAvailableSQLServers For I = 1 To ServerName. count cbServer. items. add (ServerName. item (I) Next

Obtain the list of all databases on the specified SQL Server:

'Obtain the list of all databases on the specified SQL Server. application () Dim oServer As New SQLDMO. SQLServer () oServer. connect ("(local)", "sa", "sa") cbDatabase. items. clear () Dim db As SQLDMO. database For Each db In oServer. databases Me. cbDatabase. items. add (db. name) Next

Obtain all tables, views, and stored procedures:

Dim I As Short Dim oServer As New SQLDMO. SQLServer () oServer. connect ("(local)", "sa", "sa") Dim db As New SQLDMO. database () For I = 1 To oServer. databases. count If oServer. databases. item (I, "dbo "). name = "Northwind" Then Exit For Next If I> oServer. databases. count Then Exit Sub db = oServer. databases. item (I, "dbo") ListBox1.Items. clear () 'To get all the stored procedures For I = 1 To db. storedProcedures. count ListBox1.Items. add (db. storedProcedures. item (I, "dbo "). name) Next 'To obtain all tables For I = 1 To db. tables. count ListBox1.Items. add (db. tables. item (I, "dbo "). name) Next 'To get all views For I = 1 To db. views. count ListBox1.Items. add (db. views. item (I, "dbo "). name) Next

Use SQLDMO to back up a database with a progress bar:

'Progress add progress bar 1 control' references Microsoft SQLDMO Object Library 'declare Public WithEvents bkps As SQLDMO. backup 'database Backup operation Private Sub btnBackUp_Click (ByVal sender As System. object, ByVal e As System. eventArgs) Handles btnBackUp. click Dim oSQLServer As New SQLDMO. SQLServer () oSQLServer. loginSecure = False oSQLServer. connect ("(local)", "sa", "sa") 'Connect to the server Me. cursor = Windows. forms. cursors. waitCursor bkps = CreateObject ("SQLDMO. backup ") bkps. database = "Northwind" 'specifies the Database bkps to be backed up. action = 0 bkps. files = "f: \ Northwind. bak "'specifies the backup file bkps. initialize = True ProgressBar1.Value = 0 ProgressBar1.Maximum = 100 Me. cursor = Windows. forms. cursors. default () Application. doEvents () Dim mouseCur As Cursor Me. cursor = Windows. forms. cursors. waitCursor bkps. SQLBackup (oSQLServer) ProgressBar1.Value = 100 Application. doEvents () bkps = Nothing Me. cursor = Windows. forms. cursors. default () MsgBox ("Database Backup completed", MsgBoxStyle. information, "system Message") End Sub 'displays the progress Private Sub bkps_PercentComplete (ByVal Message As String, ByVal Percent As Integer) Handles bkps. percentComplete ProgressBar1.Value = ProgressBar1.Maximum * (Percent/100) End Sub

SQLDMO For C #
By Kevin Goss

Download SQLDMO.zip

When times I have had a need to get at SQL Server details in my applications. until recently I had to use API calland bastardized ADO callto get the information I needed. now we have SQLDMO (SQL Distributed Management Objects ). although not widely known or used, SQLDMO provides a very powerful set of functionality to do just about anything with an SQL Server from code. for the purposes of this example I will show how to retrieve a list of SQL Servers on your local network, how to connect to one, and how to retrieve a list of tables, stored procedures, or views from a server.

The SQLDMO object comes from the SQLDMO. dll that ships with SQL Server 2000. the dll itself is a COM object and you must reference it from your. net project as such. the IDE will create the necessary COM wrappers needed to use the library. NOTE: if you use the statement "using SQLDMO;" in your app you may get an error.

(You must re-reference the com object for the sample app to work)

After referencing the COM object, you can begin using it quite easily.

All of the operations passed med in the example use one or more of the following objects:

• SQLDMO. Application
• SQLDMO. SQLServer
• SQLDMO. Database
• SQLDMO. NameList

There are a multitude of objects available for actions such as backups and restores, but for the purpose of this article I decided to keep it simple to forget you into the world of SQLDMO.

Listing the available SQL Servers on your network is quite simple. first you need a references SQLDMO. application object. next you set an instance of SQLDMO. nameList to the return value of the SQLDMO. application. listAvailableSQLServers () method. the SQLDMO. nameList if a COM collection of the server names.

Keep in mind, calling COM objects is a little funky until you get used to it, but the conventions are similar with all of them. here is example code which fills a combo box name cboServers with a list of all available SQL Servers on the local network:

// Get all available SQL Servers
SQLDMO. Application sqlApp = new SQLDMO. ApplicationClass ();
SQLDMO. NameList sqlServers = sqlApp. ListAvailableSQLServers ();
For (int I = 0; I <sqlServers. Count; I ++)
{
Object srv = sqlServers. Item (I + 1 );
If (srv! = Null)
{
This. cboServers. Items. Add (srv );
}
}
If (this. cboServers. Items. Count> 0)
This. cboServers. SelectedIndex = 0;
Else
This. cboServers. Text = "<No available SQL Servers> ";

As you can see, this is quite simple. Just remember that COM collections start at an index of 1, not 0.

Connecting to a server and getting a list of databases is also fairly simple. the following code will take the chosen SQL Server in the combo box, connect to it (with a user name and password in 2 text boxes ), and then poulates another combo box with a list of databases on the server.

// Get all available databases from an SQL Server
SQLDMO. Application sqlApp = new SQLDMO. ApplicationClass ();
SQLDMO. SQLServer srv = new SQLDMO. SQLServerClass ();
Srv.connect(this.cboservers.selecteditem.tostring(,,this.txtuser.text,this.txt Password. Text );
Foreach (SQLDMO. Database db in srv. Databases)
{
If (db. Name! = Null)
This. cboDatabase. Items. Add (db. Name );
}

Getting a list of objects by type is also a breeze with this library. Again, you make a connection to the database, and then you loop through the object collection.

// Get all Stored procedures-tables are in the Tables collection, views are in the Views collection
SQLDMO. SQLServer srv = new SQLDMO. SQLServerClass ();
Srv.connect(this.cboservers.selecteditem.tostring(,,this.txtuser.text,this.txt Password. Text );
For (int I = 0; I <srv. Databases. Count; I ++)
{
If (srv. Databases. Item (I + 1, "dbo"). Name = this. cboDatabase. SelectedItem. ToString ())
{
SQLDMO. _ Database db = srv. Databases. Item (I + 1, "dbo ");
This. lstObjects. Items. Clear ();
For (int j = 0; j <db. StoredProcedures. Count; j ++)
{
This. lstObjects. Items. Add (db. StoredProcedures. Item (j + 1, "dbo"). Name );
}
Break;
}
}

Well folks, that is it for my SQLDMO beginners 'tutorial. please download the sample code and app to see it in action. as you can see, this is a much easier alternative when SQL information or control is needed. happy coding !!!