Connecting to SQL Server Express from C# Express 2008

Question

I have an application that I want to connect to a database in an SQL Server Express instance. I am developing the application in Visual C# 2008 Express.

When I go to Database Explorer and click on the 'Connect to Database' button, the 'Choose Data Source' dialog appears. The problem is that the only three options I have are:
"Microsoft Access Database File"
"Microsoft SQL Server Compact 3.5"
"Microsoft SQL Server Database File"

If I choose "Microsoft SQL Server Database File", it tries to create a connection to the actual .mdf file and not the server.

How do I connect to the server?

Answer 1

Hi,

The Visual Studio Express versions don't support anything else than Access and Sql Express database file. This is only a designer limitation. You can write code that connects to any database on the same computer or remote. You use a SqlConnection with a connection string, so changing the connection string should do the trick.

To easily click your way to a connectionstring, go herte: www.connectionstrings.com

Hope this helps

Answer 2

 

I did some workaround to solve this issue, and it worked as fine as it would have been done in the "wizard" way...

 

First, this worked for me in the following scenarios:

 

Visual C# Express 2008 on Windows XP SP2 (fully updated)

SQL Server Express 2005 SP2 running on the same computer (No 'user instances' enabled)

 

And...

 

Visual C# Express 2008 on Windows XP SP2 (fully updated)

SQL Server Express 2005 SP2 running on a remote Computer (No 'user instances' enabled)

 

Here I go (Quick and Easy)

 

First, enable User Instances on the SQL Server Express, then establish a connection using the "Microsoft SQL Server Database File" option, this will give you access to the .NET SQL Server data provider. When you are done, save the connection and it will be added to the app.config file, which if not exists then it's created. And now open that app.config file and change the connection string with your desired connection, save changes, close all windows (in the Development Environment) and rebuild the solution, now try to create a TableAdapter on a DataSet and be sure to select the "yourconnectionname(Settings)" Connection option, because the other connection string may still appear. It should work fine.

 

Hope this helps you (or anybody reading this) with your trouble, if not solved yet.

 

See ya !

 

P.D. If you or anybody reading this needs a more detailed explanation, just reply this post, and I will reply back as soon as I get noticed.

Answer 3

Thank you Julio, that solved the problem and worked as expected.

Answer 4

Hi Julio,

You wrote:

then establish a connection using the "Microsoft SQL Server Database File" option, this will give you access to the .NET SQL Server data provider. When you are done, save the connection and it will be added to the app.config file, which if not exists then it's created.

I established a connection to a dummy.mdf file.  But how do I save this connection so that the config file is created?

Thanks.

Answer 5

I've figured it out now Julio.  Thanks for the original solution.

Coming to a new platform there's just such a steep learning curve. 

Sigh....

Answer 6

You could also do this way,

On the Visual C#, just create or add an Service-based Database by going through menu Project/Add new items.../Service-based Database, and it will automaticall add an app.config. On app.config you can change the connectionstring.

OR without creating a new item, just go to the setting of your project through menu Project/ "ProjectName" Properties or on your Solution Explorer, collapse the properties and double click the Settings.settings, and there you can add your new connectionstring.  After that, just add an  providerName  on app.config which is "System.Data.SqlClient".