Database Connection in Codebehind

Hi everyone

I've been asked a few times to explain how to connect to a database using code, instead of just using SQLDataSource in Design view. This article explains how to connect to a database and by passing parameters to a Stored Procedure which returns a result set.

Firstly, we need to setup our web.config file with the connection information needed to connect to the database. The reason we put this in web.config is because the data will then be available to all aspects of the web site. Here is an explanation of the lines to add.

  • DatabaseConnectionString - this is how we will reference this connection string throughout the web site
  • ServerName - this is the name of the database server we want to connect to
  • DatabaseUser - this is the database user that has access to the Stored Procedure we want to connect to. In fact, this user should have access to all Stored Procedures because we will probably want to use it elsewhere in the site
  • DatabaseUserPassword - this is the password for the database user

OK, so now we can connect to our database. Let's jump into page code and see how to use it.

Right-click on a page and select View Code. This will give you the codebehind page. Click inside the Page_Load code block because that is where will be putting our code. Putting code in this location means it will be executed when the page loads. Page Load occurs before other controls etc are loaded on the page, so it's a good place to put code blocks.

	// set up the connection string from web.config
SqlConnection conn = new SqlConnection( ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ToString() );
		// open this connection
                conn.Open();

		// tell the system which stored procedure we want to execute - notice we passed the connection object in here
                using (SqlCommand cmd = new SqlCommand("proc_UserSite_GetDetails", conn))
                {
                    // 	set up the parameters and give it some values
                    cmd.Parameters.Add("@AWCUserKey", SqlDbType.Int).Value = UserID;
                    cmd.Parameters.Add("@SiteKey", SqlDbType.Int).Value = SiteKey;
                    // 	tell the system that it's a stored procedure we are executing (not directly against a table)
                    cmd.CommandType = CommandType.StoredProcedure;
                    //	setup a way to read the data that gets sent back from the stored procedure
                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                    	//	keep going until all rows have been read
                        while (dr.Read())
                        {
                            // do something with the information we got back
                            // FirstName is a field that is returned from the stored procedure
                            Label1.Text = dr["FirstName"].ToString();
                        }
                    }
                }
		//	close the connection
                conn.Close();

Hopefully that explains it well enough. Til next time ...

Note You will also need to put these item in the top of the codebehind page
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

Til next time ...