Connect ASP.NET WebSite to SQL Database

Question

I am currently trying to establish a connection between an ASP.NET web site project and a Database built by SQL Server 2008 R2.

The way I am required to do so is to use the connectionString from the Web.config page, but I have no idea what value to give it or how to establish a connection using said value. (Using C#)

Any help would be appreciated, as I found next to no information about the subject.

Here is the (default) value that is currently in the Web.config page:

<connectionStrings>
    <add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/>
</connectionStrings>

This may be useful to you...Its a trick for creating Connection strings stackoverflow.com/questions/3954029/… — John Hartsock
– John Hartsock, Commented Nov 4, 2011 at 19:47
"I found next to no information about the subject" - there are over 5000 hits on SO alone for 'connectionstring'!!! — Jason
– Jason, Commented Nov 4, 2011 at 19:51
@KreepN I did, but I am not sure about how proper my connection string is... this is all I put in it, and it works: Server=loca5lhost;Trusted_Connection=yes;Database=levidb. — Acidic
– Acidic, Commented Nov 8, 2011 at 20:37
Correct. When you're connection to a local DB, the string itself doesn't require much configuring. When you're connection to a SQL server instance on another machine is when it seems to look different and require more fields. — KreepN
– KreepN, Commented Nov 8, 2011 at 20:38
Here's my local one which I snagged from visual studio: Data Source=J\SQLEXPRESS;Initial Catalog=MyCatalog;Persist Security Info=True;User ID=*****;Password=*********** — KreepN
– KreepN, Commented Nov 8, 2011 at 20:39

KreepN · Accepted Answer · 2011-11-04 20:06:11Z

5

Use Configuration Manager:

using System.Data.SqlClient;
using System.Configuration;

string connectionString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;

using(SqlConnection SqlConnection = new SqlConnection(connectionString));

//The rest is here to show you how this connection would be used. But the code above this comment is all you really asked for, which is how to connect.

{

   SqlDataAdapter SqlDataAdapter = new SqlDataAdapter();
   SqlCommand SqlCommand = new SqlCommand();

   SqlConnection.Open();
   SqlCommand.CommandText = "select * from table";
   SqlCommand.Connection = SqlConnection;
   SqlDataReader dr = SqlCommand.ExecuteReader(CommandBehavior.CloseConnection);

}

edited Nov 4, 2011 at 20:06

answered Nov 4, 2011 at 19:49

KreepN

8,5981 gold badge43 silver badges58 bronze badges

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

3 Comments

abatishchev Over a year ago

If en exception will occur before a reader execution, the connection will not be closed. Thus I recommend to use using block.

KreepN Over a year ago

Personal preference, as he could be using it in a try-catch block. I'd agree, however, and will make sure I notate that accordingly.

Acidic Over a year ago

This does show me how to retrieve the connection string, so that part is pretty much answered for me. What to write in the Web.config file is another matter. Currently I simply did this: Server=localhost;Trusted_Connection=yes;Database=mydb and it works, but it seems far off from the default value that is in there.

huMpty duMpty · Accepted Answer · 2011-11-04 19:50:05Z

0

This article about Connect to SQL Server Using SQL Authentication in ASP.NET will probably give you a better idea of what need to be done.

As a pre check, just check if your mssqlserver services are running.

answered Nov 4, 2011 at 19:50

huMpty duMpty

14.5k14 gold badges64 silver badges102 bronze badges

1 Comment

Acidic Over a year ago

Problem is, I'm using ASP.Net 4 and I don't know how applicable information about ASP.Net 2 is.

abatishchev · Accepted Answer · 2011-11-04 19:59:43Z

0

string connectionString =  ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;

using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = connection.CreateCommand())
{
    command.CommandText = commandText;

    // command.Parameters.AddWithValue("@param", value);

    connection.Open();
    command.ExecuteNonQuery(); // or command.ExecuteScalar() or command.ExecuteRader()
}

answered Nov 4, 2011 at 19:59

abatishchev

101k88 gold badges303 silver badges443 bronze badges

Collectives™ on Stack Overflow

Connect ASP.NET WebSite to SQL Database

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related