CODEWITHSUNDEEP
c#winformssqlitedatagridviewboolean
Christian Caraballo
Christian Caraballo

Reputation: 73

Show Yes/No instead of 0/1 in datagrid view for c#?

I'm filling a datagrid in c# from a SQLite database, but SQLite databases have no true boolean value, so I've made an SQLite INTEGER field with a check to ensure it is 0 or 1. Instead of show that 0 or 1 in the datagrid, I'd like to show a Yes/no. 1=Yes, 0=No. I'd like the data in the DB to stay the same, just change what is displayed to the user. The sql statement filling the table right now is a simple one:

SELECT * FROM TABLE1

What would be the best way to achieve this? Thank you.

EDIT: The field in question is the "Injured" field. Table Schema:

CREATE TABLE "SmashRoster" (
    "SmashRosterID" INTEGER PRIMARY KEY AUTOINCREMENT,
    "CharacterName" TEXT NOT NULL,
    "TotalTournaments"  INTEGER NOT NULL,
    "Wins"  INTEGER NOT NULL,
    "Losses"    INTEGER NOT NULL,
    "Championships" INTEGER NOT NULL,
    "InjuriesCaused"    INTEGER NOT NULL,
    "Injured"   INTEGER NOT NULL DEFAULT 0 CHECK(Injured IN (0,1))
);

Method used to pull the data from the DB, Bind to the datagrid is at the bottom:

  SQLiteConnection cnn = new SQLiteConnection(@"Data 
    Source=C:\SQLiteDB\SQLiteDB.db;Version=3;");
  SQLiteCommand cmd;
  SQLiteDataReader dataReader;
  DataTable roster;

  cnn.Open();
  cmd = new SQLiteCommand(@"SELECT * FROM SmashRoster", cnn);
  roster = new DataTable();

  dataReader = cmd.ExecuteReader();
  roster.Load(dataReader);

  dataReader.Close();
  cmd.Dispose();
  cnn.Dispose();
  rosterGridView.DataSource = roster;

Upvotes: 1

Views: 1244

Answers (2)

user12031933
user12031933

Reputation:

Assuming the data value is an integer as you say, you can use the CellFormatting event of the DataGridView control like this:

private void grid_CellFormatting(object sender, ataGridViewCellFormattingEventArgs e)
{
  if ( e.ColumnIndex == columnIndex )
    if ( e.Value is int )
      e.Value = (int)e.Value == 0 ? "No" : "Yes";
}

All values other than 0 are considered true else you can modify the test as you want.

Replace columnIndex by the index you need starting from 0.

Also replace the int check and cast by long if you use a SQLite x64 driver.

You can also use the column name like this:

if ( (sender as DataGridView)?.Columns[e.ColumnIndex].Name == "column name" )

Or the binded data property name from your query:

if ( (sender as DataGridView)?.Columns[e.ColumnIndex].DataPropertyName == "name" )

Using column index or name you are independant from the query.

Using data property name you are independant from the columns order and naming.

You may choose what you prefer.

Upvotes: 2

Marco Guignard
Marco Guignard

Reputation: 653

Depending of the situation.

You could use a case expression directly in the sql statement. See https://www.sqlitetutorial.net/sqlite-case/

If you use some business object layer, you could have a specific property to expose this field as a calculated string instead of a integer. With ADO.NET Dataset/Datatable, you could have the same behaviour with Expression column https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/dataset-datatable-dataview/creating-expression-columns

You could use the CellFormatting event of the datagridview to spot the right column and convert the Int to a String.

Upvotes: 0

Related Questions