How do I reuse the connection string from the web.config file?
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.
Code:
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() + "<br />";
117 Label4.Text += reader["RelativeLN"].ToString() + "<br />";
118 Label4.Text += reader["RelativeBP"].ToString() + "<br />";
119 Label4.Text += reader["RelativeEmail"].ToString() + "<br />";
120 Label4.Text += reader["RelativeIM"].ToString() + "<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 }
Re: How do I reuse the connection string from the web.config file?
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
Re: How do I reuse the connection string from the web.config file?
You could've also just declared string connectionString at the class level!
