Switching SQLite database file always showing data from old SQLite database file in datagridview C# Winforms

Question

I am developing a Winforms application which handles multi user and multi company concept. If a user enters in a company and open something, like accounts, it displays that data in a datagridview, but when user switches company inside the app, it also switches the SQLite database file.

But the problem is: the datagridview is not displaying data from the second company, it is still showing the data from the first company.

This is my code:

private void ViewSearch(string search)
{
     try
     {
         string query = string.Empty;

         if (string.IsNullOrEmpty(search) && !string.IsNullOrEmpty(search))
         {
             query = "Select * from tblAccount";
         }
         else
         {
             query = "Select * from tblAccount where AccountName Like '%" + search + "%'";
             ds = AccessToDatabaseComp.retrieve(query);
             dgvAccountView.AutoGenerateColumns = false;
             bs.DataSource = ds.Tables[0];
           
             if (ds != null)
             {
                 if (ds.Tables[0].Rows.Count > 0)
                 {
                     dgvAccountView.DataSource = ds.Tables[0];

                     dgvAccountView.Columns[0].HeaderText = "Account ID";
                     dgvAccountView.Columns[0].Width = 50;
                     dgvAccountView.Columns[0].DataPropertyName = "AccountID";
                     dgvAccountView.Columns[0].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleLeft;

                     dgvAccountView.Columns[1].HeaderText = "Account Name";
                     dgvAccountView.Columns[1].Width = 500;
                     dgvAccountView.Columns[1].DataPropertyName = "AccountName";
                     dgvAccountView.Columns[1].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleLeft;

                     dgvAccountView.Columns[2].HeaderText = "Credit";
                     dgvAccountView.Columns[2].Width = 150;
                     dgvAccountView.Columns[2].DataPropertyName = "Credit";
                     dgvAccountView.Columns[2].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight;

                     dgvAccountView.Columns[3].HeaderText = "Debit";
                     dgvAccountView.Columns[3].Width = 150;
                     dgvAccountView.Columns[3].DataPropertyName = "Debit";
                     dgvAccountView.Columns[3].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight;
                 }
                 else
                 {
                     dgvAccountView.DataSource = null;
                 }
             }
             else
             {
                 dgvAccountView.DataSource = null;
             }
         }
     }
     catch
     {
     }
}

Data access class:

public class AccessToDatabaseComp
{
     private static string dbPathcomp = Application.StartupPath + "\\" + frmMain.Instance.lblDBFile.Text + ";";
     private static string conStringComp = "Data Source=" + dbPathcomp + "version=3";
     //SQLiteConnection sqliteconComp = new SQLiteConnection(conStringComp);
     private static SQLiteConnection sqlite;

     public static SQLiteConnection sqliteconComp()
     {
         if (sqlite == null)
         {
             sqlite = new SQLiteConnection(conStringComp);
         }

         if (sqlite.State != System.Data.ConnectionState.Open)
         {
             sqlite.Open();
         }
         else
         {
             sqlite.Close();
         }

         return sqlite;
     }

     public static bool insert(string query)
     {
         try
         {
             int rows = 0;
             SQLiteDataAdapter da = new SQLiteDataAdapter();
             da.InsertCommand = new SQLiteCommand(query, sqliteconComp());
             rows = da.InsertCommand.ExecuteNonQuery();

             if (rows > 0)
             {
                 return true;
             }
             else
             {
                 return false;
             }
         }
         catch
         {
             return false;
         }
     }

     public static bool update(string query)
     {
         try
         {
             int rows = 0;
             SQLiteDataAdapter da = new SQLiteDataAdapter();
             da.UpdateCommand = new SQLiteCommand(query, sqliteconComp());
             rows = da.UpdateCommand.ExecuteNonQuery();

             if (rows > 0)
             {
                 return true;
             }
             else
             {
                 return false;
             }
         }
         catch
         {
             return false;
         }
     }

     public static bool delete(string query)
     {
         try
         {
             int rows = 0;
             SQLiteDataAdapter da = new SQLiteDataAdapter();
             da.DeleteCommand = new SQLiteCommand(query, sqliteconComp());

             rows = da.DeleteCommand.ExecuteNonQuery();

             if (rows > 0)
             {
                 return true;
             }
             else
             {
                 return false;
             }
         }
         catch
         {
             return false;
         }
     }

     public static DataSet retrieve(string query)
     {
         try
         {
             SQLiteDataAdapter da1 = new SQLiteDataAdapter();
             
             da1.SelectCommand = new SQLiteCommand(query, sqliteconComp());
             DataSet ds1 = new DataSet();
             da1.Fill(ds1);
           
             return ds1;
         }
         catch
         {
             return null;
         }
     }
}

Please suggest some solution to get rid of this problem.

Answer 1

First of all, are you sure you wanted to write this:

if (string.IsNullOrEmpty(search) && !string.IsNullOrEmpty(search))
{
     query = "Select * from tblAccount";
}

Is there ever a moment that search is both empty and not empty?

Anyway, you have correctly separated your data from the way that your data is displayed.

From your code I gather that you always want to show the same columns, that these columns should always show the same properties in the same format.

So why would you want to reinitialize all columns inside method ViewSearch?

My advice would be to initialize your columns only once in the constructor, and only change the DataSource.

In your DataGridView you want to show various properties of a sequence of similar objects that all have at least an AccountId, an AccountName, a Credit, and a Debit.

To be safe after future changes, my advice would be to introduce an interface IAccount, every object that you want to show as a row in your DataGridView should at least implement this interface. As you assume that the object already have properties AccountId, AccountName, etc, this won't limit usage of your classes.

public interface IAccount         // TODO: invent a proper name
{
    int AccountId {get;}
    string AccountName {get;}
    decimal Debet {get;}
    decimal Credit {get;}
}

Your form needs a method to fetch the data that must be shown:

IEnumerable<IAccount> FetchAccountsToDisplay(string search)
{
    ... // do something with AccessToDatabaseComp

    // either return the fetched Accounts,
    // or return Enumerable.Empty<IAccount>();
}

How to fetch the data into an IEnumerable<IAccount> is not part of this question.

Using Visual Studio Designer, add your columns, and define how data must be displayed. In your constructor define which column should show which property:

public MyForm()
{
    InitializeComponent();

    // define which column should display which property:
    this.columnAccountId.DataPropertyName = nameof(IAccount.AccountId);
    this.columnAccountName.DataPropertyName = nameof(IAccount.AccountName);
    this.columnDebet.DataPropertyName = nameof(IAccount.Debet);
    this.columnCredit.DataPropertyName = nameof(IAccount.Credit);
}

You could also do this using the designer. The advantage of using nameof is that if you later change the names of the properties and you forget to change the names your compiler will complain.

public ICollection<IAccount> DisplayedAccounts
{
    get => (ICollection<IAccount>)this.dataGridView1.DataSource;
    set => this.dataGridView1.DataSource = value;
}

To display the data:

void OnDisplayAccounts(string search)
{
    ICollection<IAccount> accountsToDisplay = this.FetchAccountsToDisplay(search)
        .ToList();
    this.DisplayedAccounts = accountsToDisplay;
}

And presto! Your accounts are shown. Even if there aren't any accounts (= Enumerable.Empty<IAccount>()), then a nice empty DataGridView is shown.

Make it editable

The method above is simple and requires only a few lines of code. However the displayed data in the DataGridView cannot be changed by the operator. If you need that, only a few lines of code need to be changed.

public interface IAccount
{
    int AccountId {get; set;}
    string AccountName {get; set;}
    decimal Debet {get; set;}
    decimal Credit {get; set;}
}

public BindingList<IAccount> DisplayedAccounts
{
    get => (BindingList<IAccount>)this.dataGridView1.DataSource;
    set => this.dataGridView1.DataSource = value;
}

void OnDisplayAccounts(string search) { IList accountsToDisplay = this.FetchAccountsToDisplay(search) .ToList(); this.DisplayedAccounts = new BindingList(accountsToDisplay); }

Now the operator can edit the accounts. After that he clicks the OK button to indicate he has finished editing.

void ButtonOk_Clicked(object sender, ...)
{
    // fetch the edited data, as well as the original data:
    IEnumerable<IAccount> originalAccounts = ...
    IEnumerable<IAccount> editedAccounts = this.DisplayedAccounts.Cast<IAccount>();

     this.ProcessEditedAccounts(originalAccounts, editedAccounts);
}

Do some smart LINQ to find out which Accounts are

• removed: Id in original but not in edited
• added: Id in edited but not in original
• changed: Id in both original and in edited, account not equal

I think a full outer join might be handy here, but that is not part of the question

Finally two useful methods: get the current row and all selected rows

IAccount CurrentAccount => (IAccount)((DataGridViewRow)this.dataGridView1.CurrentRow)?.DataBoundItem;

IEnumerable<IAccount> selectedAccounts = this.dataGridView1.SelectedRows
    .Cast<DataGridViewRow>()
    .Select(row => row.DataBoundItem)
    .Cast<IAccount>();