ASP.net Database connection to remote SQL server DB not in 'App_Data'

Question

New to the ASP.NET world. My default connection string in 'Web.config' (Visual Studio 2013) is initially connected to a local db as:

<add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\aspnet-dbTest-20141022090046.mdf;Initial Catalog=aspnet-dbTest-20141022090046;Integrated Security=True" providerName="System.Data.SqlClient"/>

I have been reading about connections to remote servers (SQL Express). I was able to modify the default connection string to the following:

<add name="DefaultConnection" connectionString="Data Source=MYSERVERNAME\SQLEXPRESS;
  Initial Catalog=myDatabase;
  Integrated Security=True;
  User ID=MYUSERNAMEISDIFFERENT;
  Password=MYUSERPASSWORDISDIFFERENT"
  providerName="System.Data.SqlClient"/>

The internal tables (non ASP.NET) are not available as objects to interact with and I do not see the database as a resource in the 'App_Data' folder.
Is this normal?
Should I include something "AttachDbFileName=|DataDirectory|" ?

I can connect and log in (I do see the login and new 'user' account inside MS SQLServer Management Studio in the database after it builds the ASP.Net tables.

Andy

Sam Hood · Accepted Answer · 2014-10-23 08:46:20Z

1

You want to access the connection string using the configuration manager:

var connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

This can then be used in a database connection such as:

var dt = new DataTable();
using (var con = new SqlConnection(connectionString))
{
    var command = new SqlCommand("SELECT * FROM table", con);

    con.Open();
    dt = command.ExecuteQuery();
}

'dt' will be filled with the results of the query provided to the SqlCommand object

There are other ways to connect, but generally this way is the simplest to get going with.

edited Oct 23, 2014 at 8:46

answered Oct 22, 2014 at 15:05

Sam Hood

3704 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Izzy Over a year ago

You can even get rid of the con.Close() as the using block ensures the destruction of DbConnection object by calling the Dispose()

DrSockMonkee Over a year ago

Think that did it. I put similar code in the system and it ran through without an error. (I can't see the results but it would not have run if it didn't work).

Sam Hood Over a year ago

@Izzy Good shout! Edited answer

DrSockMonkee · Accepted Answer · 2014-10-23 11:54:00Z

1

Sam's Answer worked for me...

However, As in anything I find that knowing the right term and or word makes the process so much easier.

The approach that all of the online training I have been going through was "Code First" examples. The way that I am trying to take the information I have learned is this. That I want to go to "Database First"

This link outlined everything that I wanted to do: http://www.asp.net/mvc/tutorials/mvc-5/database-first-development/creating-the-web-application

That is that I have Visual Studio 2013, and an existing database (AdventureWorks 2012) in SQLServerExpress.

Thanks again, and I hope the link helps someone..

Andy

answered Oct 23, 2014 at 11:54

DrSockMonkee

151 silver badge8 bronze badges

Collectives™ on Stack Overflow

ASP.net Database connection to remote SQL server DB not in 'App_Data'

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related