How do I go about this:

Hello,
I am trying to create forum pages (like these here at VBForums.com) in ASP.Net. It is more of a learning project so I can get into ASP.Net. I want to make them as efficient as possible, so here are some questions I need to have answered:

1. Should I have just one database connection for the app, and share it among all the pages, or should I just build a connection on each page load?

2. How should I keep people logged in while browsing the forum? I have started by using cookies on the users computer, but that still means that I would have to verify the cookie against the database every time a page loads that requires the user to be logged in (reply or new post). Would a session object be better to use? How would using it to store the information affect server performance?

Thanks a bunch for any help provided. Also, Lethal, if you read this, did you take any of those exams yet for .Net?

The views contained here in this article are those of the author (Me) and are set forth as help if needed.

1. I would have a connection open and close for every request. If you have an always open connection, after a while you will run out of connections. You can create one basic object and just open and close it for each connection attempt (Thats how i do it and it works fine with 1500+ users.)

2. I would look into sessions for authorization as used by forums and such applications. Cookies are good for keeping a username or user id, but keep session variables to hold and query for authentication purposes. (I also use this for a few apps i wrote that works fine with 1500+ users.)

Hope that helps a little.

I would have to agree with Kleptos. Also, I didn't get a chance to take the C# Windows exam yet. I'm gonna try to knock out the sql server 2000 here in a few weeks, then tackle the C# exams.

Thanks for the advice. I will go ahead and code it that way then. I will create a general object that any page can use then, and call it to connect and retrieve the information required. (that is what you meant right?)

Thanks again

1. Use seperate objects and connections for each page... although one global connection in theory sounds good, you will run into problems with it. Besides, if you have a connection on each page, Connection Pooling will happen anyways, that is what it is designed for :)


2. Since sessions in .NET have been made so much more robust, i would use them. You can store session state in sql server, a 'state' server, or the old fashioned way... This allows for great scalability.. used to be that sessions and web farms were not meant to coexist, but with the 2 new options of storing state, they can, and they can well!

as for authentication... on the login form, authenticate the info against the database... then have a session variable like:

Session("IsLoggedIn") = "1"

Now, on every page which you want to authenticate the user, put something like this in the page load:

If Session("IsLoggedIn") <> "1" Then
Response.Redirect("Login.aspx")
End If

This is a simple way to only query the database for authentication once.. and, it should be fairly secure... I wouldn't recommend authenticating this way if you're using cookies, since the client could always change the cookie to appear that they are logged in... but sessions should be plenty secure...

This is the method i use now, and it seems to work fine... if there are any major security issues related to this, i'd like to be made aware of them. But as far as i know, this works, and works fine!

Hope that helps you out a bit :) good luck on the forums... actually, i almost fancy coding up something of the sort myself... if i ever get the time.

Quote:

---

Originally posted by Redth
as for authentication... on the login form, authenticate the info against the database... then have a session variable like:

Session("IsLoggedIn") = "1"

Now, on every page which you want to authenticate the user, put something like this in the page load:

If Session("IsLoggedIn") <> "1" Then
Response.Redirect("Login.aspx")
End If

---

That was exactly how I plan to handle it now that I know that session variables won't kill performance. Thanks for backing up that decision.

One thing I had a question on is this statement you made:

Quote:

---

1. Use seperate objects and connections for each page... although one global connection in theory sounds good, you will run into problems with it. Besides, if you have a connection on each page, Connection Pooling will happen anyways, that is what it is designed for

---

Are telling me that .Net will pool the connections to the db by itself without me having to do anything?

I first plan to do this forum with an Access db, then do it with an SQL Server db, and finally with a MySQL db. (I need the experience!!!..lol). I mostly want to leverage the most from each different database option so I can learn the ins and outs of each so I can get better with them.

as i understand it, connection pooling will happen on its own as long as you have not set it up so it specifically WON'T happen...

I don't know much more about it than that... but i've been told by many people that this will indeed happen. Connections will pool automatically.

And while developing using sql server halfway across the continent being called from my slow dialup connection, i've noticed this (i think)... if i do things close enough to each other, pages load very quickly with data... if i wait, it takes longer...

but unless someone can prove otherwise, i think it is safe to continue the assumption that pooling will occur automatically. I think that this happens at the driver level. :)

cheers!

I know that SQL Server can pool them,
but I am not so sure about Access or MySQL.

Anyway, here is a class I wrote that I will use
to access the database for information. I would
appreciate any comments on it that any of
you might have:

Code:

---

public class DataObject
{
        #region Class Variables
        private OleDbConnection theConnection;
        private OleDbCommand theCommand;
        private OleDbDataReader theReader;
        #endregion

        #region Constructor(s)
        public DataObject()
        {
                // Set up the connection object.
                theConnection = new OleDbConnection();
                theConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\inetpub\\wwwroot\\bkrforums\\bin\\BKRForums.mdb";
        }
        #endregion

        #region Class Methods
        public bool UserNameExists(string user)
        {
                        // This holds whether or not the user exists.  Initially false.
                bool userExists = false;

                // Get the records that match the user that was passed in.
                theCommand = new OleDbCommand("SELECT * FROM Users WHERE UserName = '" + user.ToUpper() + "'", theConnection);
                theConnection.Open();
                theReader = theCommand.ExecuteReader();
                       
                // If there are records that match, that meant that the user name exists
                // already, and we need to return true.
                while(theReader.Read())
                {
                        userExists = true;
                }

                // Close the reader and the connections.
                theReader.Close();
                theConnection.Close();

                // Return whether the user exists or not.
                return userExists;
        }

        public bool LoginInfoCorrect(string user, string pass)
        {
                // This holds whether or not the logon info is correct.  Initially false.
                bool loginCorrect = false;

                // Get the records that match the user that was passed in.
                theCommand = new OleDbCommand("SELECT * FROM Users WHERE UserName = '" + user.ToUpper() + "' AND Password = '" + pass + "'", theConnection);
                theConnection.Open();
                theReader = theCommand.ExecuteReader();
                       
                // See if the username/password matches a record, and if so,
                // check the password for proper case.
                while(theReader.Read())
                {
                        if(theReader["Password"].ToString() == pass)
                        {
                                loginCorrect = true;
                        }
                }

                // Close the reader and the connections.
                theReader.Close();
                theConnection.Close();

                // Return whether the user exists or not.
                return loginCorrect;
        }

        public DataSet GetCategories()
        {
                // Fill the dataset with the category information.
                DataSet theDataSet = new DataSet();
                OleDbDataAdapter theAdapter = new OleDbDataAdapter("SELECT * FROM Categories", theConnection);
                theAdapter.Fill(theDataSet);
                       
                // Return the dataset.
                return theDataSet;
        }

        public DataSet GetPosts(int categoryNum)
        {
                // Fill the dataset with the category posts.
                DataSet theDataSet = new DataSet();
                OleDbDataAdapter theAdapter = new OleDbDataAdapter("SELECT PostID, UserName, DateTime, Views, Replies, Subject FROM Posts WHERE CatID = " + categoryNum, theConnection);
                theAdapter.Fill(theDataSet);
                       
                // Return the dataset.
                return theDataSet;
        }

        public DataSet GetPostDetails(int categoryNum, int postNum)
        {
                // Fill the dataset with the post and replies.
                DataSet theDataSet = new DataSet();

                OleDbDataAdapter theAdapter = new OleDbDataAdapter("SELECT UserName, DateTime, Subject, Message FROM Replies WHERE PostID = " + postNum, theConnection);
                theAdapter.Fill(theDataSet);
                       
                // Get the records that match the user that was passed in.
                theCommand = new OleDbCommand("SELECT UserName, DateTime, Subject, Message FROM Posts WHERE PostID = " + postNum, theConnection);
                       
                // Create a new row to add to the data set.  This one will hold
                // the original post.
                DataRow dr;
                dr = theDataSet.Tables[0].NewRow();

                theConnection.Open();
                theReader = theCommand.ExecuteReader();
                       
                // Create the main post row.
                while(theReader.Read())
                {
                        dr["UserName"] = theReader["UserName"];
                        dr["DateTime"] = theReader["DateTime"];
                        dr["Subject"] = theReader["Subject"];
                        dr["Message"] = theReader["Message"];
                }
                       
                // Close the reader and connection.
                theReader.Close();
                theConnection.Close();

                // Add the row at the beginning of the dataset.
                theDataSet.Tables[0].Rows.InsertAt(dr,0);
                       
                // Return the dataset.
                return theDataSet;
        }
        #endregion
}

---