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;      

    }

Adam Robinson · Accepted Answer · 2010-08-05 13:55:20Z

7

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;
  }
}

edited Aug 5, 2010 at 13:55

answered Aug 5, 2010 at 13:36

Adam Robinson

186k36 gold badges294 silver badges351 bronze badges

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

9 Comments

Nathan Stanford Over a year ago

Is there a way to put the class in the web config so I don't have to do a using every time?

Henk Holterman Over a year ago

@Nathan: the using is for very different purposes.

Jeroen Over a year ago

Since it's static you would only use 1 line of code to reference it so i suggest you use the above example as it is.

Adam Robinson Over a year ago

@Nathan: using is for cleaning up unmanaged resources. It's not something that can be done in one place. It's something that should have been in place in your existing code

Nathan Stanford Over a year ago

@Adam would you mind showing me how to change the Select to return a list of objects... ex:List<Category> = select * from Categories? like the the DataSet code you did. I think returning a List of Objects from a reader would be better or what would you suggest?

|

Henk Holterman · Accepted Answer · 2010-08-05 13:45:14Z

1

It is a good idea to use a const in a class that is commonly used in your DataLayer:

public class CategoryDAL 
{ 
   public const string connStringName =  "staceys_cakesConnectionString";

   // the rest 
}

and use the identifier connStringName everywhere in your class.

I have made it public so that it is available outside of the class as CategoryDAL.connStringName but that will rarely be necessary.

That may seem a little like only shifting the problem, but you get good intellisense and refactoring support.

edited Aug 5, 2010 at 13:45

answered Aug 5, 2010 at 13:36

Henk Holterman

276k33 gold badges353 silver badges540 bronze badges

2 Comments

Nathan Stanford Over a year ago

I am sorta new to Dot Net where do I put that in the web.config?

Henk Holterman Over a year ago

You've already defined-once something in Web.config. That leaves you with a string-key to that definition. The const in my answer goes somewhere in the DAL class. I'll edit.

derek · Accepted Answer · 2010-08-05 13:34:04Z

0

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

answered Aug 5, 2010 at 13:34

derek

4,8961 gold badge25 silver badges26 bronze badges

1 Comment

Adam Robinson Over a year ago

Err, that's what he's doing. He wants to know how to remove the requirement that he pass the same key in everywhere.

Mario · Accepted Answer · 2010-08-05 13:41:35Z

0

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

Connection String

answered Aug 5, 2010 at 13:41

Mario

4402 silver badges9 bronze badges

1 Comment

Mario Over a year ago

Sorry you're right, and a static property or method seems to be the best answer.

Collectives™ on Stack Overflow

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

4 Answers 4

9 Comments

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

9 Comments

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related