This page will be devoted to a couple of Database related macros. Database related macros are also very useful for the development of DotNetNuke custom modules. Using a database requires knowing how to connect to it. Therefore insert the following function. And before you get in too much of a hurry: Notice the connectionString in this function. Yours will be different. If you aren’t sure what your connectionString should be then just open your web.config file and it will be right there.
Function GetMyConnectionString() As String
Return "Data Source=.\SQLEXPRESS;AttachDbFilename=c:\inetpub\wwwroot\PHDnet1\App_Data\Database.mdf;" _
& "Integrated Security=True;User Instance=True"
End Function
Create, save, and run Test_003 :
Sub Test_003()
' ***** Title
MyOutput = AddOutputPane("Powerhouse Data Test_003")
Print(MyOutput, " ")
Print(MyOutput, " ***** Powerhouse Data VSM *****")
Print(MyOutput, " ")
Print(MyOutput, " > Beginning at " & Now())
Dim connectionString As String = GetMyConnectionString()
Dim command As System.Data.SqlClient.SqlCommand
queryString = "SELECT COUNT(*) FROM USERS"
Using connection As New SqlClient.SqlConnection(connectionString)
connection.Open()
Print(MyOutput, vbNewLine " > DataSource: " & connection.DataSource)
Print(MyOutput, " > Database: " & connection.Database)
Print(MyOutput, " > ConnectionString: " & connection.ConnectionString)
Print(MyOutput, " > State: " & connection.State)
command = New System.Data.SqlClient.SqlCommand(queryString, connection)
Print(MyOutput, " > Executing Query " & queryString)
Dim reader As SqlClient.SqlDataReader = command.ExecuteReader()
Try
While reader.Read()
Print(MyOutput, " > There are " & reader(0) & " users in your ""USERS"" data table.")
End While
Finally
reader.Close()
End Try
connection.Close()
End Using
Print(MyOutput, vbNewLine " > End " & Now())
End Sub
Here is another one to list all your tables. Take time to stop and examine the code. Notice how similar it is to the one above. That makes it easy! Create, save, and run Test_004 :
Sub Test_004()
' ***** Title
MyOutput = AddOutputPane("Powerhouse Data Test_004")
Print(MyOutput, " ")
Print(MyOutput, " ***** Powerhouse Data VSM *****")
Print(MyOutput, " ")
Print(MyOutput, " > Beginning at " & Now())
Print(MyOutput, " ")
s.Length = 0 'clear the stringbuilder
s.Append("select TABLE_NAME,TABLE_TYPE from INFORMATION_SCHEMA.TABLES")
queryString = s.ToString
Dim connectionString As String = GetMyConnectionString()
Dim command As System.Data.SqlClient.SqlCommand
Using connection As New SqlClient.SqlConnection(connectionString)
connection.Open()
command = New System.Data.SqlClient.SqlCommand(queryString, connection)
Print(MyOutput, " > Executing Query " & queryString)
Dim reader As SqlClient.SqlDataReader = command.ExecuteReader()
Try
While reader.Read()
Print(MyOutput, " > " & reader(0))
End While
Finally
reader.Close()
End Try
connection.Close()
End Using
Print(MyOutput, vbNewLine " > End " & Now())
End Sub
Feel free to change those query strings and see what you can do. Just be a little careful as you explore those query strings! Note also, you could easily change that connection string in the GetMyConnectionString function to examine other databases. And your macros which use the GetMyConnectionString function will all still work flawlessly. So I think you could routinely comment/uncomment out some different connection lines there (which might be advantageous for some developers).