Visual Basic and SQL Server – Generating Data

In order to test the performance of a database, or application that uses it, where there are large volumes of data, it may be useful to generate data rather than having to enter it manually.

The following example demonstrates how to generate data for a table called ‘person’, which was used in the examples for selecting, inserting, updating, deleting, importing and exporting data.

First of all, a connection to the database is established and the number of records to generate is specified. This can be set to any value, as long as there is enough space in the database to accommodate the records. A number of arrays are defined so that a random first name, last name and title can be selected for each record. The first name array also holds the gender associated with the name so that an appropriate title can be selected. A date range is also specified to allow for the generation of a random date of birth, along with other variables.

A ‘for’ loop is used to generate the desired number of records. A random number is generated to select a first name from the corresponding array and this is extracted, along with the gender. The same process occurs to extract a random last name. The gender is then used to extract a title from the appropriate array. The final random value generated is the date of birth.

Once all the values have been generated, an SQL ‘insert‘ statement is constructed and then executed. Feedback is provided as to the number of records added to the database. A ‘Try-Catch-Finally’ block is used to catch any errors that may occur, as well as close the database connection, regardless of whether the addition of records is successful or not.

' Database connection variable.
Dim connect = New SqlConnection(
   "Server=localhost\MSSQLSERVERDEMO; Database=Demo;" &
   "User Id=DemoUN; Password=DemoPW")

Try

   ' Connect to database.
   connect.Open()

Catch ex As Exception

   ' Confirm unsuccessful connection and stop program execution.
   Console.WriteLine("Database connection unsuccessful.")
   End

End Try

' Number of records to generate.
Dim recordsToGenerate As Integer = 500

' First names.
Dim fname = New String(19, 1) {
   {"Oliver", "M"}, {"Noah", "M"}, {"Harry", "M"},
   {"Leo", "M"}, {"Charlie", "M"}, {"Jack", "M"},
   {"Freddie", "M"}, {"Alfie", "M"}, {"Archie", "M"},
   {"Theo", "M"}, {"Olivia", "F"}, {"Sophia", "F"},
   {"Amelia", "F"}, {"Emily", "F"}, {"Ava", "F"},
   {"Isla", "F"}, {"Isabelle", "F"}, {"Charlotte", "F"},
   {"Layla", "F"}, {"Freya", "F"}
}

' Last names.
Dim lname = New String() {
   "Smith", "Johnson", "Williams", "Jones",
   "Brown", "Davis", "Miller", "Wilson",
   "Taylor", "Anderson", "Thomas", "White",
   "Martin", "Thompson", "Robinson", "Clark",
   "Walker", "Young", "Wright", "Hill"
}

' Male Titles.
Dim mtitle = New String() {
   "Mr", "Dr", "Prof"
}

' Female Titles.
Dim ftitle = New String() {
   "Miss", "Mrs", "Ms", "Dr", "Prof"
}

' Start and end dates for random date of birth range.
Dim startDob As Date = DateTime.Now.Date.AddYears(-100)
Dim endDob As Date = DateTime.Now.Date.AddYears(-20)

' Days between start and end date of birth.
Dim dobDiff As Integer = DateDiff("d", startDob, endDob)

' Random class and number.
Dim random As New Random()
Dim randomNumber As Integer

' New record values.
Dim firstname As String
Dim lastname As String
Dim gender As String
Dim title As String
Dim dob As DateTime

' SQL variable.
Dim sqlPersonInfo As String

' Record count.
Dim recordCount As Integer = 0

Try

   ' Generate specified number of records.
   For i = 1 To recordsToGenerate

       ' Randomly select a first name and associated gender.
       randomNumber = random.Next(0, fname.GetLength(0) - 1)
       firstname = fname(randomNumber, 0)
       gender = fname(randomNumber, 1)

       ' Randomly select a surname.
       randomNumber = random.Next(0, lname.GetLength(0) - 1)
       lastname = lname(randomNumber)

       ' Randomly select a title based on the gender.
       If gender = "M" Then

           randomNumber = random.Next(0, mtitle.GetLength(0) - 1)
           title = mtitle(randomNumber)

       Else

           randomNumber = random.Next(0, ftitle.GetLength(0) - 1)
           title = ftitle(randomNumber)

       End If

       ' Randomly select a date of birth.
       Randomize()
       dob = DateAdd("d", Int((dobDiff * Rnd()) + 1), startDob)

       ' Query text.
       sqlPersonInfo =
           "INSERT INTO person " &
           "            (firstname, lastname, title, dob) " &
           "VALUES (@firstname, @lastname, @title, @dob)"

       ' Query text incorporated into SQL command.
       Dim sqlInsert As New SqlCommand(sqlPersonInfo, connect)

       ' Bind the parameters to the query.
       sqlInsert.Parameters.AddWithValue("@firstname", firstname)
       sqlInsert.Parameters.AddWithValue("@lastname", lastname)
       sqlInsert.Parameters.AddWithValue("@title", title)
       sqlInsert.Parameters.AddWithValue("@dob",
                                         Format(dob, "yyyy-MM-dd"))

       ' Execute SQL.
       sqlInsert.ExecuteNonQuery()

       ' Increment the record count.
       recordCount += 1

   Next

   ' Provide feedback on the number of records added.
   If recordCount = 0 Then

       Console.WriteLine("No new person records added.")

   ElseIf recordCount = 1 Then

       Console.WriteLine(recordCount & " person record added.")

   Else

       Console.WriteLine(recordCount & " person records added.")

   End If

Catch ex As Exception

   ' Confirm error adding person information and exit.
   Console.WriteLine("Error adding person information.")
   End

Finally

   ' Close the database connection.
   connect.Close()

End Try