This is another in an occasional series of short articles about working with SQL Server data in a .Net application, to explain how to work with with SQL Server data, what is possible, and how to work with the issues that can come up. The idea for this article was prompted when I tried to run the examples from the free versions of Visual Studio and SQL Server Express. I thought I would write about the process and maybe save someone else some problems.
I downloaded and installed the free SQL Server Express Edition and Microsoft Visual Studio Community Edition. However, when I looked at the code in Visual Studio I got the dreaded red squiggly line on the SqlClient namespace:
The dlls for the System.Data.SqlClient namespace were not included with the packages that come with Visual Studio Community Edition.
Actually the solution was a lot simpler than I thought it might be: NuGet. NuGet is a code repository supported by Microsoft for sharing code as packages. When I was looking for something else (actually to answer a question in response to my article about the SqlConnection object), I saw an item from a Google search that suggest that the person should use NuGet. I had heard of NuGet and had acually used it before, so that would seem to be a good place to start.
In Visual Studio, I found the manager for NuGet in the Project menu. The option is called "Manage NuGet Packages," as shown in this screen shot.
In the Manager, click Browse (is it a tab?) and enter System.Data.SqlClient in the search box. The correct package showed at the top of the list.
In the image above, the SqlClient item has a green arrow because I had already downloaded it when I took the screen shot. Click to download the package from NuGet.
Now the red squiggly disappears and the code compiles, but there was still a problem when I tried the .Net code to open the connection.
conn.ConnectionString = "Data Source=(local);Initial Catalog=tempdb;Integrated Security=True"; conn.Open();
The command times out because the protocol to make the connection to SQL Server Express has not been enabled. So, this requires a change in SQL Server Configuration Manager. In the tree list on the left, open the SQL Server Network Configuration note and select the Protocols for your instance of SQL Server.
I enabled the TCP/IP protocol as shown in this screen shot. According to what I read in the documentation, Named Pipes protocol can also be used but I did not try it.
This solved the timeout, but the connection string still did not have the correct data source. A quick MSDN search for specifying SQL Express in a connection string informed me that the data source property should be specified like this:
Since in C# the backslash character "\" is used to create Escape Sequences to specify special characters, in the C# code we have to escape the escape character like this:
conn.ConnectionString = "Data Source=.\\sqlexpress;Initial Catalog=tempdb;Integrated Security=True"; conn.Open();
If your connection string is in a configuration file you do not have to add the escape of the escape. Once the correct data source was in place the connection was opened with no problems and no delays.
Another point that may be helpful. After the trial period for Visual Studio Community Edition ran out I got the message about renewing the license. To re-activate, it turns out you only have to register with Microsoft and the licensing message went away. I guess that's where the "Community" part comes in. Now I get periodic emails, like the one letting me know I have zero points,
Anyway, I hope this is helpful.