Use data from DataTable in all forms

Question

In the class I execute a stored procedure and return a DataTable.

public DataTable getUserInfo(int abid)
    {
        DataTable tbl = new DataTable();

        string constring =ConfigurationManager.ConnectionStrings["myconn"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constring))
        {
            using (SqlCommand cmd = new SqlCommand("getUserInfo", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ABID", abid);

                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(tbl);  
            }
        }
        return tbl;
    }

Then in the code behind of the default.aspx.cs page I call the class and using a foreach loop I get all the data I need.

            DataTable tbl = new DataTable();
            tbl = u.getUserInfo(abid);

            foreach (DataRow row in tbl.Rows)
            {
                string firstName = row["firstName"].ToString();
                string lastName = row["lastName"].ToString();
                string fullname = row["fullname"].ToString();
                string Phone = row["phone"].ToString();  
            }

This solution works if I only had to use the data in this page only. I need the same data to use in different pages in the project. Of course, I could call the class every time and create different data tables and store them in different variables, but I know it's not very efficient. I'm sure there's a better way to do this.

Is there a way that I could create global variables? For instances I load the datatable into those variables and I can use them throughout the project? Or any other solution?

Thanks

Answer 1

If that data is shared among different users then use Cache object. See: ASP.NET Caching: Techniques and Best Practices.

But if that data is unique to each user then store that information in a Session object. But remember, Sessions are maintained for each user on Server, If you keep too much data in Session then it will require more resources from the Server.

See: ASP.NET Session State Overview

To store information in Session

//To store
Session["UserInfo"] = tbl;

To retrieve

DataTable tbl = Session["UserInfo"] as DataTable;
if (tbl != null)
{
    //Datatable found;
}

Session object can be accessed on multiple pages.

You also have other options to maintain state across pages in ASP.Net, like Cookies. See ASP.NET State Management Overview

Answer 2

I suggest that you implement a lazy-loading solution to this.

Since static member are shared across all requests to the application, you can cache data that you know isn't going to change. Obviously you have to be careful about caching too much or for too long, or you're going to run int RAM issues. You'll also have to consider concurrency.

Here's a simplified example: create a class that represents a user, and then create a static method, LoadByID(int id), and a static Dictionary to stored already-loaded users. Then, when a page requests a user, serve them the item from the cache if it already exists.

//... user instance fields ...

private static Dictionary<int, User> cache = new Dictionary<int, User>();
private static object lockObj = new object();

public static User LoadByID(int id)
{
    lock (lockObj) //Prevent double-adding items
    {
        if (cache.ContainsKey(id))
        {
            return cache[id]; //We've already loaded the record.
        }
        else
        {
            //Some function that actually calls the database
            //and constructs user objects
            User loaded = LoadUserInternal(id);
            cache.Add(id, loaded)
            return loaded;
        }
    }
}

private static User LoadUserInternal(int id)
{
    //Load and construct the user
}

Answer 3

Web is stateless, which means a new instance of a web page class is re-created each time the page is posted to the server. As we all know, HTTP is a stateless protocol, it can't hold client information on a page.

If the user inserts some information and move to the next page, that data will be lost and the user would not be able to retrieve that information. We need to store information. Session provides a facility to store information on server memory. It can support any type of object to store along with our own custom objects. For every client, session data is stored separately, which means session data is stored on a per client basis. Have a look at the following diagram:

State management using session is one of the best ASP.NET features, because it is secure, transparent from users, and we can store any kind of object in it.

Advantages:

1. It helps maintain user state and data all over the application.
2. It is easy to implement and we can store any kind of object.
3. Stores client data separately.
4. Session is secure and transparent from the user.

Disadvantages:

1. Performance overhead in case of large volumes of data/user, because session data is stored in server memory.
2. Overhead involved in serializing and de-serializing session data, because in the case of StateServer and SQLServer session modes, we need to serialize the objects before storing them.

Reference: 1.http://www.codeproject.com/Articles/32545/Exploring-Session-in-ASP-Net