db connection within multiple pages asp.net

Question

I am having a problem with my npgsql connection to database. The point is that my application validates the user against db.If successful then redirects to the next page and display the SQL query. When the site is redirected and tries to read the query an exception says : Connection is not open. Anyone can help me with that?

string connectionString =
"Server=serverName;" +
"Database=dbName;" +
"User ID=" + strUsername + ";" +
"Password=" + strPassword + ";" +
"Port=portNo;";

NpgsqlConnection dbcon;
dbcon = new NpgsqlConnection(connectionString);
dbcon.Open();

A couple questions. Where is the query stored in code? Are you trying to use the same variables across pages? Did you close your connection after making a database call? Do you make separate calls for validation and "the query"? — gunr2171
– gunr2171, Commented Feb 27, 2013 at 16:51
1. It's stored on first page the Login page. 2. Exactly if the user is authenticated then use that for all queries on second page. 3. No I am not closing it. 4. No, no separate calls for validation. — user2090983
– user2090983, Commented Feb 27, 2013 at 16:59
So you are trying to use code stored on the first page in the second? (this code being either the stored query or the code to get to the database) — gunr2171
– gunr2171, Commented Feb 27, 2013 at 17:02

gunr2171 · Accepted Answer · 2013-02-27 17:28:00Z

It seems you are trying to reuse code that is inaccessible from different locations. Here would be my approach.

Create a common data accessor. Create a class (you might have to store it in App_Data if the folder exists) called DataAccessor. This is a class that you initialize and call from each page in which you want to use it.

public class DataAcessor
{
    private _connectionString;

    public DataAcessor(string connectionString)
    {
        _connectionString = connectionString;
    }

    public bool ValidateUser(string username, string password)
    {
        //code to call database for validation only, returns true or false
    }

    public string GetUserName(int userID) //or some other call to the database
    { }
}

Now on each page, make a new object of the class and use it. You can not rely on data from other pages unless you use a Session variable.

DataAccessor da = new DataAccessor("<some conn string>");
da.ValidateUser("joe", "asdf");

And on your second page:

DataAccessor da = new DataAccessor("<some conn string>");
da.GetUserId(123);

Collectives™ on Stack Overflow

db connection within multiple pages asp.net

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related