How do I connect my ASP.NET project to the AdventureWorksLT database?

Posted under ASP.NET, SQL Server 2005, Visual Web Developer 2008 Express by westminster

Establishing database connections to SQL Server 2005 is pretty straight forward, provided that you have the necessary components:

• SQL Server 2005 Express
• Visual Web Developer 2008 Express
• Microsoft SQL Server Management Studio Express

1. Launch Visual Web Developer 2008 Express. If you have not already done so, create a web.config file in your .NET project by selecting: File > New File > Web Configuration File.



2. Launch SQL Server Management Studio Express and connect to your database. Note: Take notice of the Server Name in the Connect to Server window as this will be used later.
3. Locate the <connectionStrings/> element then replace it with:<connectionStrings>
     <add name="AdventureWorksLT" connectionString="Server=MISSOURI\SQLEXPRESS;Database=AdventureWorksLT;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings> where MISSOURI is the name of yourserver (see Note from above).
4. Now, a query to test our connection:if (!IsPostBack)
{
   // Declare objects
   SqlConnection conn;
   SqlCommand versionComm;
   SqlDataReader reader;
   // Read the connection string from web.config
   string connectionString = ConfigurationManager.ConnectionStrings[
   "AdventureWorksLT"].ConnectionString;
   // Initialize the connection
   conn = new SqlConnection(connectionString);
   // Create the command to read version info
   versionComm = new SqlCommand(
   "SELECT [Database Version] + ' ' + CONVERT(varchar(10), VersionDate, 101) AS DatabaseInfo FROM BuildVersion",
   conn);
   try
   {
       // Open the connection
       conn.Open();
       // Execute the command
       reader = versionComm.ExecuteReader();
       // Display the data on the form
       if (reader.Read())
       {
           Label6.Text = reader["DatabaseInfo"].ToString();
       }
       // Close the reader
       reader.Close();
   }
   finally
   {
       // Close the connection
       conn.Close();
   }
5. Remember to import the SqlClient namespace by adding:using System.Data.SqlClient;to your code-behind file.