The previous post, Accessing Sample Database Data (Part 3), discussed the need to change the connection string for a database in the App.CONFIG file. However, you haven’t actually gained access to the database as of yet. Depending on how the book is written, you may have to create the database using code and manually inputting the entries, run a script, or add a database file to SQL Server. Of the three, writing the database code and manually inputting the entries is the most straightforward. All you need to do is perform the instructions written in the book. As mentioned in my first post, many readers find this method the least acceptable because database management tasks of this sort are supposedly in the realm of DBAs.
In order to work with databases in Visual Studio, you must have a connection to the database. All of my books show how to create such a connection using Server Explorer. The project will not include a connection when you open the downloadable source code. The connection to the server is part of your Visual Studio setup and you must create it when you begin working with the downloadable source. The short take on creating a connection is to right click the Data Connections folder in Server Explorer and choose Add Connection from the context menu. You’ll see a wizard that will lead you through the steps for creating the connection.
The next level of complexity is the script. A number of my books use scripts in order to make it at least reasonable to use something other than SQL Server as the database manager. More and more of my readers want to use other solutions. Accommodating this need is proving difficult, but using scripts does have the advantage of reducing the work required to use non-SQL Server solutions. I’m going to assume that you’re working with Visual Studio and that you have a connection to your database manager, whatever that database manager might be. The following steps tell how to use scripts in a general way (you may find slight variations in the steps between different versions of Visual Studio).
- Choose File | Open | File. You see an Open File dialog box.
- Highlight the script you want to use (a script always has a .SQL extension) and click Open. You see the script opened in Visual Studio.
- Right click anywhere in the editor window and choose Execute SQL from the context menu. Visual Studio interacts with your database manager to run the script.
Using a Database File
The third level of complexity is actually using a database file. The advantage of using this option is that the database is completely configured and contains everything needed to use it. This level represents the least amount of work on the part of the reader in most cases. It’s also the least flexible because it only works with SQL Server and the version of SQL Server must be able to support the features in the database file. Use these steps to work with a SQL Server database file from Visual Studio.
- Open Server Explorer by choosing View | Server Explorer. You see the Server Explorer window open as shown here.
- Right click Data Connections and choose Add Connection from the context menu. You see the Choose Data Source dialog box shown here.
- Select Microsoft SQL Server Database File. The wizard automatically chooses the correct Data Provider option for you. Click Continue. You see the Add Connection dialog box shown here.
- Click Browse. You see the Select SQL Server Database File dialog box shown here.
- Highlight the file you want to use and click Open. You return to the Add Connection dialog box.
- Click Test Connection. You should see a success message. If your version of SQL Server doesn’t support the features needed to use the database file, you’ll see an error message that states the file can’t be downgraded. The message will also tell you which version of SQL Server you require to use the database file.
- Click OK. Visual Studio creates the connection for you. What has actually happened in the background is that SQL Server has created the connection at the request of Visual Studio.
I try to choose the database option for my books with care. Many of my books use more than one option to allow more people to work with the option that best suits their needs. However, it’s impossible to please everyone with the choices I make. When you encounter problems using one of the database options I’ve selected, I’ll try my best to help you work through the difficulties. As with every other aspect of my books, working with the databases is a learning experience. Let me know your thoughts on database access in my books or if you have additional database access questions at John@JohnMuellerBooks.com.