|
Automating SQLServer from VB (using Microsoft SQLDMO Obect Lirary) - Part-I
The article looks at automating Microsoft SQL server from visual basic using SQL Distributed Management Objects
(SQLDMO object library).
In this part, I'll explain about connecting to Sqlserver, getting a list of all the available databases, tables and fields.
The first thing you need to do before getting started is to add a reference to Microsoft SQLDMO Obect Lirary from VB.
(goto Project-->References-->Microsoft SQLDMO Object Library)
'Declaring Object Variables
Dim oSQLServer As New SQLDMO.SQLServer
Dim oDatabase As SQLDMO.Database
Dim oTable As SQLDMO.Table
Dim oColumn As SQLDMO.Column
Now, having declared all the required variables, it's about time we connected to the server.
And this is how we do it.
'EITHER connect using server name, username and password (sql server authentication)
oSQLServer.Connect "IC6", "forApp", "mahipal"
'OR
'connect using a direct secure connection (Windows authentication)
oSQLServer.LoginSecure = True
oSQLServer.Connect "(local)" 'replace with your server name if not a local installation
Now that we've connected to the database, let's get the list of all the databases in the server
'get a list of databases from a SQLServer
For Each oDatabase In oSQLServer.Databases
If oDatabase.Status <> SQLDMODBStat_Inaccessible Then 'if it's accessible, go ahead
List1.AddItem oDatabase.Name
End If
Next
And this is how we get the Table list form a given database
'get a list of tables from a database
For Each oTable In oSQLServer.Databases("Northwind").Tables
List1.AddItem oTable.Name
Next
Lastly, this is how we get the Fields in a given table, their datatypes, length and if they can take NULLs or not.
'get list of columns, their datatypes, size, null or not null etc. from a table
Set oTable = oSQLServer.Databases("Northwind").Tables("Customers")
For Each oColumn In oTable.Columns
If oColumn.AllowNulls Then strTemp = "NULL" Else strTemp = "NOT NULL"
List1.AddItem oColumn.Name & " - " & oColumn.Datatype & " (" & oColumn.Length & ") - " & strTemp
Next oColumn
This concludes Part-I of the article on SQLDMO. In Part-II , I'll look at creating a database, adding tables to the database,
and adding columns to tables using SQLSMO.
|
