Connect to SQL Server with Visual Studio Express Versions

Question

It seems strange to me that in the Visual C # 2008 Express edition, when you use the database explorer, your options are:

• Microsoft access
• SQL Server Compact 3.5 and
• SQL Server Database File

BUT, if you are using Visual Web Developer 2008 Express, you can connect to regular SQL Server, Oracle, ODBC, etc.

For people developing a command line or other C # applications that need to talk to a SQL Server database, do you really need to create LINQ / Data Access code from one IDE (Visual Web Developer) and your program to another (Visual C #)?

This is not a complicated workaround, but it seems weird. If Microsoft wanted to force you to switch to Visual Studio to connect to SQL Server, why include this feature in one of their free IDEs, but not another? I feel that maybe something is missing (for example, how to do it in Visual C #).

Answer 1

You should be able to select the SQL Server database file to get the type of database you need (the system.data.SqlClient provider), and then manually fix the connection string to point to your db.

I think the reasoning behind these db options looks something like this:

• If you are using Express Edition and you are not using Visual Web Developer, you are probably creating a desktop program.
• If you are creating a desktop program and using the express version, you are probably an amateur or uISV-er, working at home, and not working with a corporation.
• If you are not developing for a corporation, your application is probably intended for the end user, and your data warehouse is likely to work on the local machine.
• You really should not deploy server-class databases to end-user desktops. The db in the process, such as Sql Server Compact or MS Access, is much more appropriate.

However, this logic is not entirely true. Even if each of these 4 points corresponds to 90% of the time, by the time you apply all four of them, this applies only to ~ 65% of your audience, which means that up to 35% of the express market may legitimately want to talk to the server class db, and that is a significant group. So, the simplified (greedy) version:

• A real db server (and the hardware to run it) costs real money. If you have access to this, you should be able to provide at least a standard version of the visual studio.

Answer 2

Workaround:

• Open your solution in Visual Web Developer Express. It will not load some of the projects in the solution, but this is normal.
• Make a new connection in the Database Explorer to the required database with SQL Server.
• Add a new class library project.
• Add a LINQ to SQL Classes element and link it to your database.
• Close the solution.
• Open the solution in Visual C # Express.

You now have the LINQ to SQL class library associated with your SQL Server database in Visual C # Express.

Update

Solution for Visual Studio Express 2010.

Answer 3

I assume that with VWD your solutions will most likely be deployed to third-party servers, many of which do not allow the use of a dynamically attached SQL Server database file. Therefore, the resolution of a different type of connection.

This difference in IDE behavior is one of the main reasons for upgrading to the full version.

Answer 4

If you use this to get LINQ to SQL, which I do and want for my Visual Developer, 1) get free Visual WEB Developer, use it to connect to an instance of SQL Server, create your LINQ interface, then copy the generated files to the Vis project -Dev (I do not use VD because it sounds funny). Include only * .dbml files. In a Vis-Dev environment, it takes a second or two to recognize supporting files. This is a bit of an extra step, but of course, it's better than doing it manually or giving it up altogether, or EVEN GOOD by paying for it. Muo ha ha ha ha.

Answer 5

The only way I was able to get C # Express 2008 to work was to move the database file. So, I opened SQL Server Management Studio and, after deleting the database, copied the file to the project folder. Then I linked the database to the management studio. Now when I try to connect to a local copy, it works. Apparently, you cannot use the same database file more than once.

Answer 6

I just started using my home business application in window forms for convenience. I am currently using Visual C # Express 2010 / SQL Server 2008 R2 Express to develop it. I got the same problem as OP where I need to connect to an instance of SQL server. I will skip the details here, but this database will be a unified database, synchronized between 2-3 computers, which will also use the application that I am currently developing.

I found a quick workaround, at least I think I did, because now I can use my stored procedures in tables without any problems.

I copied the attached SQL connection that I used in another project at work (VS2010 Premium) in app.config, and changed everything I needed there. When I returned to Settings.settings, I just needed to confirm that I want what was in the app.config file. The only drawbacks that I see are that you cannot “test” the connection, because when you enter the connection string configuration you cannot find anywhere, because “SQL Server” is not an option. Another disadvantage is that you need to enter everything manually, since you cannot use any wizards to make it work.

I don't know if I should do this, but at least I can connect to my SQL server now :).

EDIT:

It only works with instances of SQL Server 2008 R2 Express. If you try to work with a SQL Server 2008 R2 workgroup or later, you will receive an unpleasant warning from Visual C # 2010 Express stating that "you cannot use this connection with the current version of Visual Studio." I got this when I tried to modify some of my tables. I switched to an instance of SQL Express for development, and it works fine again.