loop through the SQL Server databases and populate the database names in a listbox

Introduction

This article explains about looping through the SQL server databases and displaying the data in a list box so that user can choose the database as one of inputs.

Background

My client wants to display the SQL server data in a list box control.The database is in YYYY_MM format.For example database name is like '2004_06'.So I understood that I am going to display year and months in a listbox.

Using the code

My asp.net application is calling sql server data.

// Making a Connection to SQL server
 databaseSqlstrConnection = ConfigurationSettings.AppSettings ("ConnectionString")
 SqlConn = New SqlConnection (sqlstrConnection)
 SqlCmd = New SqlCommand ("select Name from master.dbo.sysdatabases where name like '20[0-9][0-9]_[0-9][0-9]'", sqlConn)

Define the connection in web.config and command text plays vital role here. I have used master.dbo.sysdatabases to retrieve all the databases which has YYYY_MM format. This solves the problem of getting the database names with YYYY_MM format.

After getting the names of the databases using a sql query we need to loop through the databases and display in a listbox. SQLDataReader provides the fastest method of retrieving data from SQL server database Since the application only requires retrieving data ie. used for populating the pages. Its worth clarifying readonly issue here.

//looping through the database names using datareader class
          Try
           sqlConn.Open ()
           ...
           Myreader = sqlCmd.ExecuteReader
           While (myreader.Read ())
               FileDate = Split (myreader (0), "_")
               WriteOption (myreader (0), getMonthFromNumber (fileDate (1)) & "   " & fileDate (0))
           End While

Points of Interest

It reads the database names one by one and displays in a listbox.There might be better ways of doing this.But this really works.