Click or drag to resize

Data access performance

How to programming a better performance to access database

This solution is about data access to read only

This topic contains the following sections:

Requirement
Programming

A example of solution

Declaration reference to helper

C#
Copy
using SOWIData.Database;

Declarations static object of application and data list

C#
Copy
/// <summary>
/// App object on memory for a better performance
/// </summary>
private static App.Customer App = null;

/// <summary>
/// Data list on memory for a better performance
/// </summary>
private static List<Data.Customer> DataList = null;

Static method to data list handling

C#
Copy
/// <summary>
/// Gives a customers list by parameter <paramref name="pOrganisationID"/>
/// </summary>
/// <param name="pConnectionString">Data connection string. If empty then puts current configuration.</param>
/// <param name="pUserName">Database access user name. If empty then puts name of logged user (<see cref="System.Environment.UserName"/>)</param>
/// <param name="pOrganisationID">Organisation to load. If 0 then load all data items</param>
/// <returns>Data list of customers</returns>
public static List<Data.Customer> GetCustomers(
    string pConnectionString = "",
    string pUserName = "",
    int pOrganisationID = 0)

{
    try
    {
        //* declaration *
        bool lParameterIsChanged = false;   //* method parameters (Setting) did changed so reread data *
        bool lDatabaseIsChanged = false;    //* method parameters (App: ConnectionString, UserName) did changed so reload data *

        #region --- parameters ---

        //* checks parameter pOrganisationID *
        if (Setting.OrganisationID != pOrganisationID)
        {
            lParameterIsChanged = true;
        }
        Setting.OrganisationID = pOrganisationID;

        #endregion

        #region --- database ---

        //* checks database access connection string *
        if (pConnectionString.Length == 0)
        {
            //* puts SOWIApp.sconfig current configuration *
            pConnectionString = SOWIApp.Management.Data.Settings.Database.Setting.ConnectionString;
        }

        //* checks database access user name *
        if (pUserName.Length == 0)
        {
            //* puts logged user name *
            pUserName = System.Environment.UserName;
        }

        //* do check necessary a new instance of App *
        lDatabaseIsChanged = App.Customer.App.DatabaseIsChanged(pConnectionString, pUserName);
        if (lDatabaseIsChanged)
        {
            App.Customer.App = new Customer(pConnectionString, pUserName);
        }

        //* do check necessary load data list *
        if (App.Customer.DataList == null)
        {
            lDatabaseIsChanged = true;
        }

        #endregion

        //* do check necessary reread data list *
        if (lDatabaseIsChanged || lParameterIsChanged)
        {
            App.Customer.DataList = App.Customer.App.GetList();
        }

        return App.Customer.DataList;

    }
    catch (Exception ex)
    {
        SIC.Protocol.Write(ref ex);
        throw;
    }
}

Setting class as nested

C#
Copy
/// <summary>
/// Current setting of parameters
/// </summary>
/// <remarks>
/// The setting puts by method <see cref="App.Customer.GetCustomers(string, string, int)"/>
/// </remarks>
public static class Setting
{
    /// <summary>
    /// Current customers filtered by organisation ID
    /// </summary>
    /// <remarks>If value 0 then as no filter</remarks>
    public static int OrganisationID = 0;
}