Code-Behind and SQL Connection Strings

BobRoberts · November 1st, 2011, 09:57 AM

I've read all the relevant chapters in the book and skimmed the others, but can't seem to find where it covers accessing SQL in the C# code-behind. In examples I find elsewhere, the connection screen is always hard-coded in the code-behind. I find this hard to believe, since you would think there would be closer consistency and integration with the web.config file - whenever the connection string info changes, I need to go through all my code-behind data strings and change the information there as well. I've tried doing the following, and got an error:

Web.config:

Code:

<configuration>
  <connectionStrings>
    <add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true"
      providerName="System.Data.SqlClient" />
    <add name="TestConnectionString" connectionString="Data Source=SERVERNAME;Initial Catalog=TESTDB;Persist Security Info=True;User ID=sa;Password=********"
      providerName="System.Data.SqlClient" />
  </connectionStrings>

C# code behind:

Code:

SqlConnection conn = new SqlConnection("TestConnectionString");

Yields the error:
"Exception: Format of the initialization string does not conform to specification starting at index 0."

Imar · November 1st, 2011, 12:45 PM

Direct SQL using ADO.NET is not covered in my book. Instead I use technologies such as Entity Framework. I think the Pro edition of this book covers this. In addition you can check out my two series on N-Layered design that deal with this extensively:

http://imar.spaanjaars.com/416/build...pnet-20-part-1
http://imar.spaanjaars.com/476/n-lay...l-introduction

Cheers,

Imar

BobRoberts · November 3rd, 2011, 05:22 PM

I took a hint from chapter 19, page 699 - "The web.config File - Expression Syntax", and now have the code-behind connection string within the web.config file, where it's easier to modify and keep track of.

In the web.config file:

Code:

  <appSettings>
    <add key="csConnectionString" value="data source=SOMESERVER;initial catalog=TEST; integrated security=SSPI;persist security info=False; Trusted_Connection=Yes"/>
  </appSettings>

Just below the content tag of the web page:

Code:

<asp:Literal ID = "csConnectionString" runat="server" Visible="false" Text="<%$ AppSettings:csConnectionString %>" />

In the C# code-behind:

Code:

SqlConnection conn = new SqlConnection(@csConnectionString.Text);

If you need any help on your next book, please let me know, Imar!

Imar · November 3rd, 2011, 05:47 PM

Ouch; I think this is a bad idea as the connection string is now available in the page (hidden from the client or not).

What you are looking for is this:

WebConfigurationManager.ConnectionStrings["csConnectionString}'.ConnectionString

Cheers,

Imar