SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server From .Net: Connecting Visual Studio Community Edition

By Tim Wellman,

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: NuGetNuGet 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:

Data Source=.\sqlexpress;

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.

Community Edition

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.

 

This article is part of the series Working with .NET and SQL Server:

Total article views: 136 | Views in the last 30 days: 2
 
Related Articles
FORUM

CONNECTING TO SQL SERVER THROUGH VISUAL STUDIO

CONNECTING TO SQL SERVER THROUGH VISUAL STUDIO

ARTICLE

Install Visual Studio 2015 in Server without Internet Connection

Install Visual Studio 2015 in Windows Server without Internet Connection

FORUM

connecting sql server 2005 express with visual studio

connecting sql server 2005 express with visual studio

BLOG

Connection String from Visual Studio

Querying Microsoft SQL Server : Connection String from Visual Studio: Connection String: A connecti...

FORUM

Is there integration of TFS 2008 source control with SQL Server 2008 Management Studio / Client tools for Database versioning???

Descriptive Tags: TFS 2008 source control , SQL Server 2008 Management Studio, SQL Server 2008 Clien...

Tags
.net    
c#    
express edition    
visual studio    
 
Contribute