Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Start Programming!
With MSDE installed and the server running, you are ready to use it in your applications. Other articles in this series explain how to use MSDE databases with Visual Basic .NET, but this section provides enough to get you started. (For more practice using MSDE applications, see my soon-to-be-posted articles "Introduction to Datasets with Visual Basic .NET" and "Discovering Database Structure with VB .NET.")
The BuildDB subroutine, shown in Listing 1, shows one way to interact with the server. The routine begins by creating a connect string that identifies the user name the program wants to use (sa) and the database server (BENDER\NetSDK). It uses the connect string to make a new SqlConnection object, and opens that object. This connects the program to the server.
The routine then makes an SqlCommand object attached to the connection. It will shortly use this object to execute a series of SQL statements.
BuildDB sets the SqlCommand object's CommandText property to a SQL statement that builds a database named MyDB. Parameters to the statement tell the server the following:
Name of the database
Files that should hold the database's data and logging information
Database's initial and maximum sizes
Amount by which the database should be enlarged when it is full
Next, the subroutine calls the object's ExecuteNonQuery method to execute the statement and create the database.
The subroutine repeats this process, placing a SQL statement in the SqlCommand object's CommandText property and then calling its ExecuteNonQuery method, to select the new database, create a table, insert a record, stop using the database, and drop the database. A real application wouldn't go to so much trouble to build the database and then drop it, but that's a good trial for this example.
The routine finishes by closing its database connection. This is important. In Visual Basic .NET, database connections are stored in a connection pool. When the program closes its connection, it is added to the pool. Later, if this program or another one needs to use the same connection, the system retrieves it from the pool. That saves time and resources. If the program doesn't close the connection when it is no longer needed, the connection doesn't go into the pool, and it wastes resources. This may not matter for this small test application, but it could cause problems for a larger system, so you may as well start building good habits now.
Listing 1[em]Subroutine BuildDB creates a database, adds a table, inserts a record in the table, and drops the database.
Private Sub BuildDB()
' Build a connect string.
Dim connect_string As String = _
"User Id=sa;Data Source=BENDER\NetSDK"
' Connect to the server.
lblStatus.Text = "Connecting"
lblStatus.Refresh()
Dim db_connection As New SqlConnection(connect_string)
db_connection.Open()
' Make a SqlCommand object.
Dim sql_command As New SqlCommand()
sql_command.Connection = db_connection
' Create the MyDB database.
lblStatus.Text = "Building database"
lblStatus.Refresh()
sql_command.CommandText = _
"CREATE DATABASE MyDB " & _
"ON (" & _
" NAME = MyDB_data, " & _
" FILENAME = 'C:\Temp\MyDB.mdf'," & _
" SIZE = 2MB," & _
" MAXSIZE = 10MB," & _
" FILEGROWTH = 2MB)" & _
"LOG ON(" & _
" NAME = MyDB_log," & _
" FILENAME = 'C:\Temp\MyDB.ldf'," & _
" SIZE = 1MB," & _
" MAXSIZE = 5MB," & _
"FILEGROWTH = 1MB)"
sql_command.ExecuteNonQuery()
' Use the new database.
lblStatus.Text = "Selecting database"
lblStatus.Refresh()
sql_command.CommandText = "USE MyDB"
sql_command.ExecuteNonQuery()
' Create the Books table.
lblStatus.Text = "Creating table"
lblStatus.Refresh()
sql_command.CommandText = _
"CREATE TABLE Books (" & _
" Title VARCHAR(60) PRIMARY KEY," & _
" URL VARCHAR(60) NOT NULL)"
sql_command.ExecuteNonQuery()
' Insert a record.
lblStatus.Text = "Inserting record"
lblStatus.Refresh()
sql_command.CommandText = _
"INSERT INTO Books VALUES (" & _
"'Visual Basic .NET Database Programming'," & _
"'http://www.vb-helper.com/vbdb.htm')"
sql_command.ExecuteNonQuery()
' Stop using the new database.
lblStatus.Text = "Using Master"
lblStatus.Refresh()
sql_command.CommandText = "USE Master"
sql_command.ExecuteNonQuery()
' Drop the database.
lblStatus.Text = "Dropping database"
lblStatus.Refresh()
sql_command.CommandText = "DROP DATABASE MyDB"
sql_command.ExecuteNonQuery()
' Close the connection.
db_connection.Close()
lblStatus.Text = "Done"
End Sub
Click here to download a Visual Basic .NET project that uses this code to test the database server. You will need to modify the code slightly to specify your server name instead of BENDER\NetSDK, and to provide your user name and password if you don't want to use sa (system administrator).