CODEWITHSUNDEEP
c#mysql
Lukas N.
Lukas N.

Reputation: 53

Save database entrys in an object?

I'm relatively new to programming so here is my question.

I have a C#-Forms application and an access database. In the database I have the data of about 200-300 cars (Name, Year of construction, ...) In my forms application i show all the cars in a list and i have a filter where I can search for specific words and types and so on.

At the moment I react to any filter input and then execute a new sql-query and list all the cars that fit the filter.

I obviously don't think that's a good solution because i have a database access every keyDown action.

Is it a viable way to create a car-class and create an instance of this class for every car and store them in a list? What is the best way to handle all the 200 cars without reading them out of the database over and over again?

Upvotes: 0

Views: 50

Answers (3)

Sabrina_cs
Sabrina_cs

Reputation: 421

Seems more a philosophy question than a technical one, so I'd like to give you my 2 cents on this. If the Cars database is modified only by your application and the application is run by a single person, you can read the data once and set a mechanism in your UI to reaload the in memory data when through the UI you update the database. If you have multiple users using the data and updating the database, so that one user can add cars to the database while another is reading it to perform some operation on them, you have to prepare a mechanism to verify if data have been modified by someone else in the system to all users so that they can reload the cars list. (it can be something like a temporized query to a table where you memorize last date and time of update of the cars table for example, or it can be something more sophisticated in different cases). Said so, Usually when working with databases I prepare a DataProvider Class that 'speaks' with the database, creating, updating, deleting and querying data, a Class that represents the Table Row of my data (in your case a Car class) and The DataProvider returns a List to my User interface that I can Use As is, or, if needed I can move to an ObservableCollection if using it with WPF UI objects and the data can change, or I can create something like a UICar object that is a Car plus other properties related to its use inside the User Interface that can be more useful to provide actions and functionality to the user using your application. When you have your Cars collection inside your application, Searching in memory data using a simple Linq query becomes rather simple to implement and much more effective than calling the database at any change in the search textbox. Also, as a suggestion, to avoid querying (memory or db is the same) at any keystroke, set a timer (just a few milliseconds) before starting the query reset in the key event so that if the user is typing a word the query starts just when he/she stops typing. HTH

Upvotes: 0

Khatibzadeh
Khatibzadeh

Reputation: 470

the following code may help, although you should fill the blanks.

 class CarDA
    {
        public const string YOUR_SPECIFIC_CACHE_KEY = "YOUR_SPECIFIC_CACHE_KEY";
        public IList<Car> Search(string searchExpression)
        {
            var carList = ListAllCars();
            //AMK: do the math and do the filter

            return Filtered_Car_List;
        }

        private IList<Car> ListAllCars()
        {
            var ExpireTime = 10;
            if (!MemoryCache.Default.Contains(YOUR_SPECIFIC_CACHE_KEY))
            {
                MemoryCache.Default.Add(
                    new CacheItem(YOUR_SPECIFIC_CACHE_KEY, PopulateCarList()),
                    new CacheItemPolicy
                    {
                        AbsoluteExpiration = DateTimeOffset.Now.AddMinutes(ExpireTime)
                    });
            }
            return MemoryCache.Default.Get(YOUR_SPECIFIC_CACHE_KEY) as IList<Car>;
        }

        private IList<Car> PopulateCarList()
        {
            //AMK: fetch car list from db and create a list
            return new List<Car>();
        }
    }

    class Car
    {
        public string Name { get; set; }
    }

Upvotes: 0

ITGenius
ITGenius

Reputation: 139

If your car data is not changing frequantly than you can store in memory data and after filter on that.

When new record is added you need to update in memory data.

Upvotes: 1

Related Questions