How do I reuse the connection string from the web.config file?

[gjon]

I have been trying to create the connection and then pass it around with no luck. Instead I've been able to just keep recreating it with each method on the page. I don't know why when I create a variable in the page_load event it just doesn't get seen inside of methods. If I put the variable holding the connection string in the page before the page_load event, I think the page class?, it still doesn't work. Could someone give me a heads up on why please? THank you so much in advance.

1    using System;
2    using System.Data;
3    using System.Configuration;
4    using System.Collections;
5    using System.Web;
6    using System.Web.Security;
7    using System.Web.UI;
8    using System.Web.UI.WebControls;
9    using System.Web.UI.WebControls.WebParts;
10   using System.Web.UI.HtmlControls;
11   using System.Data.SqlClient;
12   
13   public partial class userpage : System.Web.UI.Page
14   {
15       //string crrntusr = String.Empty;
16       //string crrntpass = String.Empty;
17   
18       protected void Page_Load(object sender, EventArgs e)
19       {
20           //if (!IsPostBack)
21           if (Page.PreviousPage != null)
22           {
23               usrNmeLbl.Text = PreviousPage.CurrentUser;
24               psswrdLbl.Text = PreviousPage.CurrentPass;
25               if (usrNmeLbl.Text != null)
26               {
27                   loadFamHist();
28               }
29               else
30                   Response.Redirect("login.aspx");
31           }
32           else
33           {
34               usrNmeLbl.Text = "Please log in";
35               psswrdLbl.Visible = false;
36               Label4.Visible = false;
37           }
38       }
39   
40       public void loadFamHist()
41       {
42           //Read the connection string from Web.config
43           string connectionString = ConfigurationManager.ConnectionStrings["thyTreeConnectionString"].ConnectionString;
44           //Initialize connection
45           SqlConnection conn = new SqlConnection(connectionString);
46           conn.Open();
47   
48           //comm.CommandText = "SELECT * FROM UsrTbl, RelativeTable, IP_Table WHERE RelativeTable.UserID IN (SELECT UserID FROM UsrTbl WHERE UserName = @usrnmeLbl)";
49           SqlCommand comm = new SqlCommand("SELECT * FROM UsrTbl, RelativeTable, IP_Table WHERE RelativeTable.UserID IN (SELECT UserID FROM UsrTbl WHERE UserName = @usrnmeLbl)", conn);
50           comm.Parameters.AddWithValue("@usrnmeLbl", usrNmeLbl.Text);
51   
52           SqlDataReader reader = comm.ExecuteReader();
53           //Check to see if the reader has data(Did the username exist in the database? We must check this
54           if (reader.Read() != false)
55           {
56               while (reader.Read())
57               {
58                   string usr = reader["UserName"].ToString();
59                   usr = usr.TrimEnd();
60                   string pss = reader["Password"].ToString();
61                   pss = pss.TrimEnd();
62                   //take the read data from the reader and display it for testing purposes
63                   //readDB_Lbl.Text = (String.Format("{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14}, {15}, {16}, {17}, {18}, {19}, {20}, {21}, {22}",
64                   //reader[0], reader[1], reader[2], reader[3], reader[4], reader[5], reader[6], reader[7], reader[8], reader[9], reader[10], reader[11], reader[12], reader[13], reader[14], reader[15], reader[16], reader[17], reader[18], reader[19], reader[20], reader[21], reader[22]));
65                   if (usrNmeLbl.Text == usr)
66                   {
67                       if (psswrdLbl.Text == pss)
68                       {
69                           //read the column from the reader and cast it to String as some may contain null values
70                           usrNmeLbl.Text = reader["FirstName"].ToString() + " ";
71                           psswrdLbl.Text = reader["LastName"].ToString() + "<br />";
72                           psswrdLbl.Text += "Place of Birth: " + reader["BirthPlace"].ToString() + "<br />";
73                           psswrdLbl.Text += "<img src=" + reader["Photo"].ToString() + " />" + "<br />";
74                           Label4.Text = "Your relatives: " + "<br />";
75                           getRelatives();
76                       }
77                       //If password does not match
78                       else
79                       {
80                           usrNmeLbl.Text = "You have not logged into a valid account. <br />Please try logging in.";
81                           psswrdLbl.Visible = false;
82                           Label4.Visible = false;
83                           reader.Close();
84                           conn.Close();
85                       }
86                   }
87                   //break; //exit out of the loop since user found
88               }
89           }
90           //If the database doesn't return a valid user, but null
91           else
92           {
93               usrNmeLbl.Text = "You have not logged into a valid account. <br />Please try logging in.";
94               psswrdLbl.Visible = false;
95               Label4.Visible = false;
96               reader.Close();
97               conn.Close();
98           }
99       }
100      //private void getRelatives(sqlCommand comm)
101          private void getRelatives()
102          {
103              //Read the connection string from Web.config
104              string connectionString = ConfigurationManager.ConnectionStrings["thyTreeConnectionString"].ConnectionString;
105              //Initialize connection
106              SqlConnection conn = new SqlConnection(connectionString);
107              conn.Open();
108  
109              SqlCommand comm = new SqlCommand("SELECT Relation, RelativeFN, RelativeLN, RelativeBP, RelativeEmail, RelativeIM FROM RelativeTable WHERE RelativeTable.UserID IN (SELECT UserID FROM UsrTbl WHERE UserName = @usrnmeLbl)", conn);
110          comm.Parameters.AddWithValue("@usrnmeLbl", usrNmeLbl.Text);
111  
112          SqlDataReader reader = comm.ExecuteReader();
113          while (reader.Read())
114          {
115              Label4.Text += reader["Relation"].ToString() + ": ";
116              Label4.Text += reader["RelativeFN"].ToString() + "&lt;br />";
117              Label4.Text += reader["RelativeLN"].ToString() + "&lt;br />";
118              Label4.Text += reader["RelativeBP"].ToString() + "&lt;br />";
119              Label4.Text += reader["RelativeEmail"].ToString() + "&lt;br />";
120              Label4.Text += reader["RelativeIM"].ToString() + "&lt;br />";
121          }
122          reader.Close();
123          conn.Close();
124      }
125  
126      //Clear all session variables
127      //Session.Clear();
128      //Removes a particular session variable
129      //Session.Contents.Remove("SomeSessionVariableName ");
130  }

[ComputerJy]

You obviously haven't heard about the N-Tier applications. If you use a separate class for data connections and operations you can have your connection stored in a class member

[mendhak]

You could've also just declared string connectionString at the class level!