The previous installment of this series, Accessing Sample Database Data (Part 2), discussed the Windows-specific setup required to use your database from Visual Studio. It includes setting the file for read/write usage and also verifying that you have permission to work with the files. This part of the series will discuss one of the most important settings within Visual Studio itself, the connection string. In order to use a data source from within a Visual Studio application, you must tell Visual Studio where to find the data source—a sensible requirement when you think about it.
The problem for me as an author who is trying to provide you with downloadable source code is that I have no idea of what SQL Server configuration you plan to use on your system. I can make some educated guesses, but that’s about it. So, there is every chance that the connection string is going to be wrong when it comes to the actual location of the data source on your system; especially when you use a different setup from the one in the book.
In most cases, you’re most interested in locating a string similar to data source=.\SQLEXPRESS in your application. This string normally appears in the App.Config file. Over the years I have tried a number of permutations of this string, but none work out quite so well as .\SQLEXPRESS. The period represents the local machine, but you can replace it with any machine name desired. For example, if you use a separate server named WinServer, you’d use WinServer\SQLEXPRESS instead of .\SQLEXPRESS.
The SQLEXPRESS part of the data source attribute is also dependent on your setup. When you install SQL Express on your system, it usually installs as a named instance called SQLEXPRESS. However, you could just as easily install it as the default instance or you might not use SQL Express at all. To specify that you want to use the default instance of SQL Server, you use a period (.). So the default instance on the local machine would appear as .\.. Otherwise, you must provide the name of a named instance of SQL Server to use.
There is always a question of whether you have found every instance of the data source string in your application. Newer versions of Visual Studio place the string in one location when you create a connection using a wizard, App.Config. However, depending on how the application is put together, you could conceivably find the string in other locations. To ferret out these other locations, use the FindStr utility at the command line. I describe how to use this utility to locate errors in your application code in the Using the FindStr Utility to Locate Errors in Visual Studio post. You can use precisely the same approach for locating the data source. In this case, you’d open a command prompt at the main folder for the downloadable source, type FindStr \m \s “\SQLEXPRESS” *.*, and press Enter. FindStr will locate all instances of that particular data source.
In order to change the example code so that it works with your system, you must modify the data source string wherever it appears in the sample code to match your particular system. Let me know if you encounter problems making this change at John@JohnMuellerBooks.com. You can see the next post in this series at Accessing Sample Database Data (Part 4).