how do I setup the Connection String where I only change it one place?

Question

How can I make the code

string connStr = ConfigurationManager.ConnectionStrings "staceys_cakesConnectionString"].ConnectionString; 

work generically and not need the staceys_cakesConnectionString? Or how can I set it somewhere else so I only have to change it one place when I change it?

using System; 
using System.Collections.Generic; 
using System.Linq; 
using System.Web; 
using System.Data; 
using System.Data.SqlClient; 
using System.Configuration; 

namespace SC1.Models.DAL 
{ 
  public class CategoryDAL 
  { 
    public CategoryDAL() 
    { 
    } 
    string connStr = ConfigurationManager.ConnectionStrings["staceys_cakesConnectionString"].ConnectionString; 

    // select all 
    public DataSet Select() 
    { 
      SqlConnection sqlConnection1 = new SqlConnection(); 
      string SqlString = "select * from Categories"; 
      SqlDataAdapter da = new SqlDataAdapter(SqlString, connStr); 
      DataSet ds = new DataSet(); 
      da.Fill(ds, "Categories"); 
      return (ds); 
    } 
    // save 
    // insert 
    // update 
    // delete 

  } 
} 

Example of a page function I have how can I make this one better using your suggestion @Adam or anyone else?

   // List
    public List<page> Select()
    {
      List<page> _list = new List<page>();
      string  SqlStatement = "select * from Pages";
      SqlConnection thisConnection = new SqlConnection(connStr);
      // Open the Connection
      thisConnection.Open();

      var thisCommand = thisConnection.CreateCommand();
      thisCommand.CommandText = SqlStatement;
      SqlDataReader thisReader = thisCommand.ExecuteReader();

      while (thisReader.Read())
      {
        // Create a new instance of the Current Page Object
        page currentPage = new page();
        // Fill the instance of the Current Page Object
        currentPage.PageID = Convert.ToInt32(thisReader["PageID"]);
        currentPage.ParentID = Convert.ToInt32(thisReader["ParentID"]);
        currentPage.CategoryID = Convert.ToInt32(thisReader["CategoryID"]);
        currentPage.Name = thisReader["Name"].ToString();
        currentPage.PageHTMLContent = thisReader["PageHTMLContent"].ToString();
        currentPage.NavigationText = thisReader["NavigationText"].ToString();
        currentPage.TopMenu = Convert.ToBoolean(thisReader["TopMenu"]);
        currentPage.SubMenu = Convert.ToBoolean(thisReader["SubMenu"]);
        currentPage.DisplayOrder = Convert.ToInt32(thisReader["DisplayOrder"]);
        currentPage.Active = Convert.ToBoolean(thisReader["Active"]);
        // Add the instance of the Current Page Object to the List<>.
        _list.Add(currentPage);
      }
      // Close the Database
      thisConnection.Close();
      return _list;      

    }

Answer 1

Depending on your application type, put it in an app.config or web.config file. Then you can use the ConfigurationManager class to access it.

Here's a helpful link

Answer 2

Just use a constant. For that matter, just use a static property and obtain the string that way.

public static class ConnectionStrings
{
    public static string StacyesCakes 
    { 
        get 
        { 
            ConfigurationManager.ConnectionStrings[
                  "staceys_cakesConnectionString"].ConnectionString; 
        }
    }
}

That will allow you to do things like:

using(var conn = new SqlConnection(ConnectionStrings.StaceysCakes))
{
    ...
}

Or (just adapting your existing code):

public DataSet Select() 
{ 
  SqlConnection sqlConnection1 = new SqlConnection(); 
  string SqlString = "select * from Categories"; 
  SqlDataAdapter da=new SqlDataAdapter(SqlString,ConnectionStrings.StaceysCakes); 
  DataSet ds = new DataSet(); 
  da.Fill(ds, "Categories"); 
  return (ds); 
} 

(You don't need sqlConnection1; you're not using it anywhere).

Note, however, that because SqlDataAdapter implements IDisposable and you're finished with it after this code executes, you should enclose it in a using block.

I would rewrite your function to something like this:

public DataSet Select() 
{ 
  using(SqlDataAdapter da = new SqlDataAdapter(
                            "select * from Categories",
                            ConnectionStrings.StaceysCakes))
  {
      DataSet ds = new DataSet(); 
      da.Fill(ds, "Categories"); 
      return ds;
  }
}

Answer 3

You could save the ConnectionString on your application configuration file. The next link explains more about it.

Connection String