Connecting to SQL Server with Visual Studio Express Editions

Question

I find it odd that in 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 use Visual Web Developer 2008 Express  you can connect to a regular SQL Server  Oracle  ODBC  etc   For people developing command-line or other C  apps that need to talk to a SQL Server database  do you really need to build your LINQ Data Access code with one IDE  Visual Web Developer  and your program in another  Visual C       It s not a hard workaround  but it seems weird   If Microsoft wanted to force you to upgrade to Visual Studio to connect to SQL Server  why would they include that feature in one of their free IDEs but not the other   I feel like I might be missing something  like how to do it all in Visual C

User · Answer

Workaround    Open your solution in Visual Web Developer Express  It will not load some of the projects in the solution but it is ok  Make a new connection in Database Explorer to the required database from SQL Server  Add a new class library project  Add a LINQ to SQL Classes item and link it to your database  Close the solution  Open the solution in Visual C  Express    Now you have a LINQ to SQL classes library that is linked to your SQL Server database in Visual C  Express   Update  The solution is for Visual Studio Express 2010

User · Answer

If you are using this to get a LINQ to SQL which I do and wanted for my Visual Developer  1  get the free Visual WEB Developer  use that to connect to SQL Server instance  create your LINQ interface  then copy the generated files into your Vis-Dev project  I don t use VD because it sounds funny   Include only the   dbml files  The Vis-Dev environment will take a second or two to recognize the supporting files  It is a little extra step but for sure better than doing it by hand or giving up on it altogether or EVEN WORSE  paying for it  Mooo ha ha haha

User · Answer

I just happened to have started my home business application in windows forms for the convenience. I'm 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'm skipping details here but that database will be a merged database synched between 2-3 computers that will also use the application I'm developing right now.

I found a quick workaround, at least, I think I did because I'm now able to use my stored procedures in tableadapters without any issues so far.

I copy pasted an SQL connection that I used in a different project at work (VS2010 Premium) in the app.config and changed everything I needed there. When I went back to my Settings.settings, I just had to confirm that I wanted what was inside the app.config file. The only downsides I can see is that you can't "test" the connection since when you go inside the configuration of the connection string you can't go anywhere since "SQL Server" is not an option. The other downside is that you need to input everything manually since you can't use any wizards to make it work.

I don't know if I should have done it that way but at least I can connect to my SQL server now :).

EDIT :

It only works with SQL Server 2008 R2 Express instances. If you try with SQL Server 2008 R2 Workgroup and up, you'll get a nasty warning from Visual C# 2010 Express telling you that "you can't use that connection with the current version of Visual Studio". I got that when I was trying to modify some of my tableadapters. I switched back to an SQL Express instance to develop and it's working fine again.

User · Answer

You should be able to choose the SQL Server Database file option to get the right kind of database  the system data SqlClient provider   and then manually correct the connection string to point to your db   I think the reasoning behind those db choices probably goes something like this      If you re using the Express Edition  and you re not using Visual Web Developer  you re probably building a desktop program  If you re building a desktop program  and you re using the express edition  you re probably a hobbyist or uISV-er working at home rather than doing development for a corporation    If you re not developing for a corporation  your app is probably destined for the end-user and your data store is probably going on their local machine  You really shouldn t be deploying server-class databases to end-user desktops  An in-process db like Sql Server Compact or MS Access is much more appropriate    However  this logic doesn t quite hold   Even if each of those 4 points is true 90  of the time  by the time you apply all four of them it only applies to  65  of your audience  which means up to 35  of the express market might legitimately want to talk to a server-class db  and that s a significant group   And so  the simplified  greedy  version    A real db server  and the hardware to run it  costs real money  If you have access to that  you ought to be able to afford at least the standard edition of visual studio

User · Answer

You should be able to choose the SQL Server Database file option to get the right kind of database  the system data SqlClient provider   and then manually correct the connection string to point to your db   I think the reasoning behind those db choices probably goes something like this      If you re using the Express Edition  and you re not using Visual Web Developer  you re probably building a desktop program  If you re building a desktop program  and you re using the express edition  you re probably a hobbyist or uISV-er working at home rather than doing development for a corporation    If you re not developing for a corporation  your app is probably destined for the end-user and your data store is probably going on their local machine  You really shouldn t be deploying server-class databases to end-user desktops  An in-process db like Sql Server Compact or MS Access is much more appropriate    However  this logic doesn t quite hold   Even if each of those 4 points is true 90  of the time  by the time you apply all four of them it only applies to  65  of your audience  which means up to 35  of the express market might legitimately want to talk to a server-class db  and that s a significant group   And so  the simplified  greedy  version    A real db server  and the hardware to run it  costs real money  If you have access to that  you ought to be able to afford at least the standard edition of visual studio

User · Answer

You should be able to choose the SQL Server Database file option to get the right kind of database  the system data SqlClient provider   and then manually correct the connection string to point to your db   I think the reasoning behind those db choices probably goes something like this      If you re using the Express Edition  and you re not using Visual Web Developer  you re probably building a desktop program  If you re building a desktop program  and you re using the express edition  you re probably a hobbyist or uISV-er working at home rather than doing development for a corporation    If you re not developing for a corporation  your app is probably destined for the end-user and your data store is probably going on their local machine  You really shouldn t be deploying server-class databases to end-user desktops  An in-process db like Sql Server Compact or MS Access is much more appropriate    However  this logic doesn t quite hold   Even if each of those 4 points is true 90  of the time  by the time you apply all four of them it only applies to  65  of your audience  which means up to 35  of the express market might legitimately want to talk to a server-class db  and that s a significant group   And so  the simplified  greedy  version    A real db server  and the hardware to run it  costs real money  If you have access to that  you ought to be able to afford at least the standard edition of visual studio

User · Answer

My guess is that with VWD your solutions are more likely to be deployed to third party servers, many of which do not allow for a dynamically attached SQL Server database file. Thus the allowing of the other connection type.

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

User · Answer

I just happened to have started my home business application in windows forms for the convenience. I'm 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'm skipping details here but that database will be a merged database synched between 2-3 computers that will also use the application I'm developing right now.

I found a quick workaround, at least, I think I did because I'm now able to use my stored procedures in tableadapters without any issues so far.

I copy pasted an SQL connection that I used in a different project at work (VS2010 Premium) in the app.config and changed everything I needed there. When I went back to my Settings.settings, I just had to confirm that I wanted what was inside the app.config file. The only downsides I can see is that you can't "test" the connection since when you go inside the configuration of the connection string you can't go anywhere since "SQL Server" is not an option. The other downside is that you need to input everything manually since you can't use any wizards to make it work.

I don't know if I should have done it that way but at least I can connect to my SQL server now :).

EDIT :

It only works with SQL Server 2008 R2 Express instances. If you try with SQL Server 2008 R2 Workgroup and up, you'll get a nasty warning from Visual C# 2010 Express telling you that "you can't use that connection with the current version of Visual Studio". I got that when I was trying to modify some of my tableadapters. I switched back to an SQL Express instance to develop and it's working fine again.

User · Answer

Workaround    Open your solution in Visual Web Developer Express  It will not load some of the projects in the solution but it is ok  Make a new connection in Database Explorer to the required database from SQL Server  Add a new class library project  Add a LINQ to SQL Classes item and link it to your database  Close the solution  Open the solution in Visual C  Express    Now you have a LINQ to SQL classes library that is linked to your SQL Server database in Visual C  Express   Update  The solution is for Visual Studio Express 2010

User · Answer

The only way I was able to get C  Express 2008 to work was to move the database file   So  I opened up SQL Server Management Studio and after dropping the database  I copied the file to my project folder   Then I reattached the database to management studio   Now  when I try to attach to the local copy it works   Apparently  you can not use the same database file more than once

User · Answer

My guess is that with VWD your solutions are more likely to be deployed to third party servers, many of which do not allow for a dynamically attached SQL Server database file. Thus the allowing of the other connection type.

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

User · Answer

You should be able to choose the SQL Server Database file option to get the right kind of database  the system data SqlClient provider   and then manually correct the connection string to point to your db   I think the reasoning behind those db choices probably goes something like this      If you re using the Express Edition  and you re not using Visual Web Developer  you re probably building a desktop program  If you re building a desktop program  and you re using the express edition  you re probably a hobbyist or uISV-er working at home rather than doing development for a corporation    If you re not developing for a corporation  your app is probably destined for the end-user and your data store is probably going on their local machine  You really shouldn t be deploying server-class databases to end-user desktops  An in-process db like Sql Server Compact or MS Access is much more appropriate    However  this logic doesn t quite hold   Even if each of those 4 points is true 90  of the time  by the time you apply all four of them it only applies to  65  of your audience  which means up to 35  of the express market might legitimately want to talk to a server-class db  and that s a significant group   And so  the simplified  greedy  version    A real db server  and the hardware to run it  costs real money  If you have access to that  you ought to be able to afford at least the standard edition of visual studio

User · Answer

If you are using this to get a LINQ to SQL which I do and wanted for my Visual Developer  1  get the free Visual WEB Developer  use that to connect to SQL Server instance  create your LINQ interface  then copy the generated files into your Vis-Dev project  I don t use VD because it sounds funny   Include only the   dbml files  The Vis-Dev environment will take a second or two to recognize the supporting files  It is a little extra step but for sure better than doing it by hand or giving up on it altogether or EVEN WORSE  paying for it  Mooo ha ha haha

User · Answer

My guess is that with VWD your solutions are more likely to be deployed to third party servers, many of which do not allow for a dynamically attached SQL Server database file. Thus the allowing of the other connection type.

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

User · Answer

The only way I was able to get C  Express 2008 to work was to move the database file   So  I opened up SQL Server Management Studio and after dropping the database  I copied the file to my project folder   Then I reattached the database to management studio   Now  when I try to attach to the local copy it works   Apparently  you can not use the same database file more than once

User · Answer

My guess is that with VWD your solutions are more likely to be deployed to third party servers, many of which do not allow for a dynamically attached SQL Server database file. Thus the allowing of the other connection type.

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

[c#] Connecting to SQL Server with Visual Studio Express Editions

The answer is

Examples related to c#

Examples related to visual-studio-2008

Examples related to visual-web-developer

Tags