SQLDMO testing

1 SQLDMO(testing)
2 ‘Connecting using SQL Authentication
3 Dim objDMO As SQLServer
4 objDMO = New SQLDMO.SQLServer
5 objDMO.Connect("(local)", "sa", "of_course_I_changed_my_password")
6 Dim objSQLServer As SQLDMO.SQLServer
7 objSQLServer = New SQLDMO.SQLServer
8 objSQLServer.LoginSecure = True
9 Call objSQLServer.Connect("cnmtldv01psge")
10
11 Console.WriteLine(objSQLServer.Name())
12 Console.WriteLine(objSQLServer.HostName())
13 Console.WriteLine(objSQLServer.Status())
14
15 display SpaceAvailableInMB for each Database
16 Chr(9): tab; chr(13): carriage return; chr(10): line feed
17 Dim oDatabase As SQLDMO.Database
18 For Each oDatabase In objSQLServer.Databases
19 Console.WriteLine(oDatabase.Name & Chr(9) & oDatabase.SpaceAvailableInMB & Chr(13))
20 Next
21
22 ‘create a database
23 Dim nDatabase As New SQLDMO.Database
24 Dim nDBFileData As New SQLDMO.DBFile
25 Dim nLogFile As New SQLDMO.LogFile
26 Dim databaseName As String
27 databaseName = "zzz"
28 nDatabase.Name = databaseName
29 nDBFileData.Name = databaseName
30 nDBFileData.PhysicalName = "D:\\sqldata\\MSSQL\\Data\\" & databaseName & "_Data.mdf"
31 nDBFileData.PrimaryFile = True
32 nDBFileData.Size = 2
33 nDBFileData.MaximumSize = 10
34 nDBFileData.FileGrowthType = SQLDMO.SQLDMO_GROWTH_TYPE.SQLDMOGrowth_Percent
35 nDBFileData.FileGrowth = 10
36 nDatabase.FileGroups.Item("PRIMARY").DBFiles.Add(nDBFileData)
37 nLogFile.Name = databaseName + "Log"
38 nLogFile.PhysicalName = "D:\\sqldata\\MSSQL\\Data\\" & databaseName + "_Log.ldf"
39 nLogFile.Size = 2
40 nLogFile.MaximumSize = 5
41 nDatabase.TransactionLog.LogFiles.Add(nLogFile)
42 objSQLServer.Databases.Add(nDatabase)
43
44 Update(AutoUpdateStat)
45 Dim objDBz As SQLDMO._Database
46 objDBz = objSQLServer.Databases.Item("zxx")
47 objdbz.Owner as string
48 objDBz.CreateDate as string
49 objdbz.Size readonly as integer
50 objdbz.SpaceAvailableInMB as single
51 If objDBz.DBOption.AutoUpdateStat = False Then
52 objDBz.DBOption.AutoUpdateStat = True
53 End If
54
55 ‘query the database
56 Dim oTheResults As SQLDMO.QueryResults
57 oTheResults = objDBz.ExecuteWithResults("SELECT * FROM Table1") ‘Length of the Batch. For example, we are currently using 50 characters.
58 Dim numOfCols As Integer
59 numOfCols = oTheResults.Columns
60 Dim num As Integer
61 For num = 1 To oTheResults.Rows
62 Dim j As Integer
63 For j = 1 To numOfCols
64 Console.Write(oTheResults.GetColumnString(num, j))
65 Console.Write(Chr(9))
66 Next
67 Console.Write(Chr(10))
68 Next
69
70 ‘display tables inside a database; Assuming, we have set up our connection.
71 Dim objDB As SQLDMO.Database
72 objDB = objSQLServer.Databases.Item("northwind")
73 Dim oTable As SQLDMO.Table
74 For Each oTable In objDB.Tables
75 ‘Console.WriteLine(oTable.Name)
76 Next
77
78
79 ‘scripting: Assuming, we have set up our connection.
80 Dim oTable2 As SQLDMO.Table
81 oTable2 = objDB.Tables.Item("employees")
82 ‘Console.WriteLine("Script of table employees: " & oTable2.Script())
83
84 ‘display indexed columns
85 Dim indexcolname As String
86 Dim idxcnt As Integer
87 Dim idxloops As Integer
88 Dim colCount As Integer
89 Dim idxObj As SQLDMO.Index
90 Dim idxcol As SQLDMO.Column
91 idxcnt = 0
92 objDB = objSQLServer.Databases.Item("zxx")
93 Dim objTable As SQLDMO.Table
94 objTable = objDB.Tables.Item("Table1")
95 idxcnt = objTable.Indexes.Count ‘ number of indexes in the table
96 Dim i As Integer
97 For i = 1 To idxcnt
98 idxObj = objTable.Indexes.ItemByID(i) ‘one-based (instead of zero based)
99 colCount = idxObj.ListIndexedColumns.Count ‘number of cols in this index
100 Dim j As Integer
101 For j = 1 To colCount
102 idxcol = idxObj.ListIndexedColumns.Item(j)
103 ‘Console.WriteLine("index Name: " & idxObj.Name & " __and col Name of it: " & idxcol.Name)
104 Next
105 Next
106
107 ‘display and update config values
108 Dim cv As SQLDMO.ConfigValue
109 With objSQLServer.Configuration
110 .ShowAdvancedOptions = True
111 For Each cv In .ConfigValues
112 ‘Console.WriteLine(cv.Name & "_" & cv.CurrentValue & "_" & cv.RunningValue)
113 Next
114 End With
115
116 following is not tested
117 allow changes to system tables
118 With SQLServer.Configuration
119 .ConfigValues("allow updates").CurrentValue = 1
120 .ReconfigureWithOverride()
121 End With
122
123 display table information
124 Dim col2 As SQLDMO.Column
125 col2 = objTable.Columns.ItemByID(1)
126 Dim ie As IEnumerator = col2.Datatype.GetEnumerator()
127 While ie.MoveNext
128 Console.WriteLine(ie.Current.ToString())
129 End While
130
131 objSQLServer.DisConnect()
132
133 Console.WriteLine(goSQLApp.SQLServers.Item("(local)").Databases.Item("zxx").Tables.ItemByID(0).Name())

Use "SQL" to Create database

Function CreateDatabase()Function CreateDatabase(ByVal sDBName As String, ByVal sPath As String, Optional ByVal sDataSize As String = "2", Optional ByVal sMaxDataSize As String = "10", Optional ByVal sLogSize As String = "1", Optional ByVal sMaxLogSize As String = "5", Optional ByVal sGrowth As String = "10", Optional ByVal auto_shrink As Boolean = False, Optional ByVal auto_update_statistics As Boolean = True, Optional ByVal auto_close As Boolean = False, Optional ByVal auto_create_statistics As Boolean = True) As Boolean
Dim str As String
Dim db_option1 As String = "sp_dboption ‘" & sDBName & "‘, autoshrink, true"
Dim db_option2 As String = "sp_dboption ‘" & sDBName & "‘, ‘auto update statistics’, false"
Try
Select Case guInternal.DBTypeSelected
Case enumDBTYPE.MSSQLServer
‘str = "CREATE DATABASE MyDatabase ON PRIMARY (NAME = MyDatabase_Data, FILENAME = ‘C:\\MyDatabaseData.mdf’, SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) LOG ON (NAME = MyDatabase_Log, FILENAME = ‘C:\\MyDatabaseLog.ldf’, SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 10%)"
str = "CREATE DATABASE " & sDBName & " ON PRIMARY (NAME = " & sDBName & "_Data, FILENAME = ‘" & sPath & sDBName & "Data.mdf’, SIZE = " & sDataSize & "MB, MAXSIZE = " & sMaxDataSize & "MB, FILEGROWTH = " & sGrowth & "%) LOG ON (NAME = " & sDBName & "_Log, FILENAME = ‘" & sPath & sDBName & "Log.ldf’, SIZE = " & sLogSize & "MB, MAXSIZE = " & sMaxLogSize & "MB, FILEGROWTH = " & sGrowth & "%)"

Dim objDB As New clsCMDBObject
With objDB
‘ Specific parameters for execution
.ExecuteAsync = False
.DataTypeReturn = clsCMDBObject.enumDB_OBJECT_RETURN_TYPE.NoData
‘Command object definition
.Command.CommandType = CommandType.Text
.Command.CommandText = str
If ExecuteDBObject(objDB) = True Then
‘ Execution done without errors
‘Console.WriteLine("DataBase is Created Successfully")
‘objCMDataTable.LoadFromDataTable(objDB.DataTable(0))
End If
objDB.Dispose()
End With
objDB = Nothing

If auto_shrink <> False Then
objDB = New clsCMDBObject
With objDB
‘ Specific parameters for execution
.ExecuteAsync = False
.DataTypeReturn = clsCMDBObject.enumDB_OBJECT_RETURN_TYPE.NoData
‘Command object definition
.Command.CommandType = CommandType.Text
.Command.CommandText = db_option1
If ExecuteDBObject(objDB) = True Then
‘ Execution done without errors
End If
objDB.Dispose()
End With
objDB = Nothing
End If

If auto_update_statistics <> True Then
objDB = New clsCMDBObject
With objDB
‘ Specific parameters for execution
.ExecuteAsync = False
.DataTypeReturn = clsCMDBObject.enumDB_OBJECT_RETURN_TYPE.NoData
‘Command object definition
.Command.CommandType = CommandType.Text
.Command.CommandText = db_option2
If ExecuteDBObject(objDB) = True Then
‘ Execution done without errors
End If
objDB.Dispose()
End With
objDB = Nothing
End If


CreateDatabase = True



Case enumDBTYPE.Oracle

Case enumDBTYPE.DB2
Case enumDBTYPE.AS400
Case 0 ‘in this case the method "openDatabase" is not called, so the DBType is not selected
Throw New ApplicationException("Please call ""OpenDatabase"" first to create an active connection.")
End Select

Catch ex As Exception
CreateDatabase = False
‘MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information)
If ex.Message = "Database ‘" & sDBName & "‘ already exists." Then
Call CatchError("The database being created, exists on the current server.")
ElseIf ex.Message = "The ConnectionString property has not been initialized." Then
Call CatchError("Trying to create a database on a disconnected server.")
ElseIf guInternal.DBTypeSelected = 0 Then
Console.WriteLine(ex.Message & " ::ex.Message")
Call CatchError(ex.Message)
End If

End Try
End Function

Use "SQL" to retrieve database information

‘use one sql statement to retrieve lots of information. so, declare a table array
Dim tables() As clsCMDataTable
tables = currentCMDB.RetrieveDBProperties(selectedDBName)
If tables Is Nothing Then
    Me.Close()
    Exit Sub
End If

‘the location of each property is fixed
tbtDbsize.Text = “Owner”
tbtDbsize.VirtualTextBox.Text = tables(0).Rows(0)(2)
tbtDbsize.Enabled = False

tbtDbmaxsize.Text = “Date created”
tbtDbmaxsize.VirtualTextBox.Text = tables(0).Rows(0)(4)
tbtDbmaxsize.Enabled = False

tbtLogsize.Text = “Size”
tbtLogsize.VirtualTextBox.Text = tables(0).Rows(0)(1)
tbtLogsize.Enabled = False

tbtLogmaxsize.Text = “Space available” ‘http://supportcentral.ge.com/blog/sup_personal_post_comment.asp?person_id=1609810&post_id=48064
tbtLogmaxsize.VirtualTextBox.Text = tables(2).Rows(0)(2)
tbtLogmaxsize.Enabled = False

tbtGrowth.Text = “Grow”
Dim growFromDB As String
growFromDB = CStr(tables(1).Rows(0)(6)).Trim
If growFromDB.EndsWith(“B”) Then
    tbtGrowth.Enabled = False
End If
tbtGrowth.VirtualTextBox.Text = growFromDB

tbtPath.Text = “File Location”
tbtPath.VirtualTextBox.Text = tables(1).Rows(0)(2)
tbtPath.Enabled = False

Dim autoShrinkFromDB As Boolean
Dim autoUpdateStatFromDB As Boolean
autoShrinkFromDB = currentCMDB.AutoShrink(selectedDBName)
CheckBoxNode1.Checked = autoShrinkFromDB
autoUpdateStatFromDB = currentCMDB.AutoUpdateStatistics(selectedDBName)
CheckBoxNode2.Checked = autoUpdateStatFromDB

use of: clsCMDataTable

Dim oServers As New clsCMDataTable

‘in form load sub
oServers.AddColumnDefinition(“ID”, TypeCode.Int32, clsCMDataTable.enumENCRYPTION_MODE.None, True, True, True, False, False)
oServers.AddColumnDefinition(“ServerName”, TypeCode.String)
oServers.AddColumnDefinition(“Status”, TypeCode.Int32)
oServers.AddColumnDefinition(“CMDatabase”, TypeCode.Object)

‘add rows; REMEMBER: YOU Should add multiple CELLS for one row in one line of code!!
With oServers
        .AddLineWithValues(“ServerName”, sServerName, “ServerCapacity”, iServerCapacity)
        .SaveInFile(sServersFile, clsCMConstants.enumFileFormat.Normal)
End With

‘retrieve values from the table
With oServers
  If .CountOfLines <> 0 Then
    Dim i As Int32
    For i = 0 To .CountOfLines – 1
      Dim sServer As String = .GetColumnValue(i, “ServerName”)
     grpServers.Items.AddRange(New Xceed.SmartUI.Controls.TreeView.Node() {CreateNode(sServer)})
      grpServers.Items(i).Key = .GetColumnValue(i, “ServerName”)
    Next
    pnlServer.Text = grpServers.Items(0).Text 
    pnlServer.Key = grpServers.Items(0).Key  
  End If
End With

‘in other sub
Dim theRow As DataRow = oServers.GetUniqueDataRow(“ServerName=’” & pnlServer.Text & “‘”)

‘remove
With oServers
               .RemoveLines(“ServerName=’” & sServerName & “‘”)
               .SaveInFile(sServersFile, clsCMConstants.enumFileFormat.Normal)
End With

Multidimensional Arrays

The value “gamma” is referenced by Letters(2,0); the value “e” is referenced by Letters(4,1).

Declaring Arrays

a multidimensional array commas are used for each additional column

Dim Letters(,) As String

Initializing Arrays

Dim Letters() As String = {"alpha","beta","gamma","delta","epsilon"}

Dim Letters(,) As String = { {"alpha","a"},{"beta","b"},{"gamma","c"},{"delta","d"},{"epsilon","e"} }

Imports System
Public Class MainClass
Shared Sub Main()
Const rowsUB As Integer = 4
Const columnsUB As Integer = 3
Dim rectangularArray(rowsUB, columnsUB) As Integer
'populate the array
Dim i As Integer
For i = 0 To rowsUB - 1
Dim j As Integer
For j = 0 To columnsUB - 1
rectangularArray(i, j) = i + j
Next j
Next i
'report the contents of the array
For i = 0 To rowsUB - 1
Dim j As Integer
For j = 0 To columnsUB - 1
Console.WriteLine( _
"rectangularArray[{0},{1}] = {2}", _
i, j, rectangularArray(i, j))
Next j
Next i
End Sub
End Class