How to connect to local SQL Server database?

Question

I am creating a web application/website using ASP.net in visual studio 2010. We have our basic website and I even created a SQL Server database which is in the App_Data folder of my web application folder.

I created tables and a few procedures, but I do not know how to have my web forms or their controller (C#) classes access the tables. Below is my rough setup to access it. I don't know what to set the string to equal. The database is in webapplication1/App_Data/database.mdf.

The file I want to access it is webapplication/App_Code/DataConnect.cs. What should the string equal. What do I need to do to test it?

{ 
SqlConnection _sqlConn = null;
string _connectionString = ?
 _sqlConn2 = new SqlConnection(_connectionString);
 _sqlConn.Open();
}

Solution 1

You may use following connection string.

string _connectionString =@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"

You can also add the connection string into web.config's connectionString section and later use it in code.

<connectionStrings>
  <add name="CnStr" 
       connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" 
       providerName="System.Data.SqlClient"/>
</connectionStrings>

To retrieve connectionString from web.config

string _connectionString=System.Configuration.ConfigurationManager.ConnectionStrings["CnStr"].ConnectionString;

Solution 2

You can manually write connection string into your code...

string strcon = @"Data Source=SERVERNAME; Initial Catalog=DATABASENAME; Integrated Security=True";

OR

Follow below steps to connect local SQL Server database...

1. Go to View > Server Explorer/Database Explorer
2. Right click on Data Connections > Add Connection...
3. Select Server name, Choose authentication type, Select your created database.
4. Test your connection and then OK.
5. Right click on database > Properties and use connection string...

Check below link for more understanding....

• Bind Gridview In ASP.NET
• Overview SQL Server Database

Comments

[marc_s]

Welcome to StackOverflow: if you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it!

[imzrh]

Thanks for the comment, i have revised the answer.