Converting tinyint to Yes / No and binding it to grid

Posted by Raj.Trivedi under C# on 7/30/2013 | Points: 10 | Views : 2051 | Status : [Member] [MVP] | Replies : 5
Hello Team,

I have a table in which i have 3 columns and the values stored in the column is 1 or 0.

I am binding this to a Datagrid in Windows Application.

What i am trying to do is that the datagrid column should show Yes for 1 and No for 0.

and bind it data grid.

In short i want that the tinybit values should be converted to Yes or No depending on 1 and 0 where 1 should be converted Yes and 0 should be converted to NO

Regard's
Raj.Trivedi
"Sharing is Caring"
Please mark as answer if your Query is resolved



Responses

Posted by: Bandi on: 7/31/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Approaches:
1) You can use OnRowDataBound event to convert tinyint (1, 0) to Yes/No...
2) While retrieving data from databse only convert those values to Yes/No...
   SELECT CASE When col1 = 1 then 'Yes' ELSE 'No' END col1,

CASE When col2 = 1 then 'Yes' ELSE 'No' END col2,
CASE When col3 = 1 then 'Yes' ELSE 'No' END col3
FROM TableName


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 7/31/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Example is as follows:

 private void BindData()

{

//This connection string is used to connect to database
String strConnection = "Data Source=MySYSTEM ;Initial Catalog=DBName ;User Id=UserName ;Password=Passwod ";
//Establish SQL Connection
SqlConnection con = new SqlConnection(strConnection);
//Open database connection to connect to SQL Server
con.Open();
//Data table is used to bind the resultant data
DataTable dtusers = new DataTable();
// Create a new data adapter based on the specified query.
SqlDataAdapter da = new SqlDataAdapter("Select CASE WHEN col1=1 THEN 'Yes' ELSE 'No' END col1, CASE WHEN col2=1 THEN 'Yes' ELSE 'No' END col2, CASE WHEN col3=1 THEN 'Yes' ELSE 'No' END col3 from YesNoTable ", con);
//SQl command builder is used to get data from database based on query
SqlCommandBuilder cmd = new SqlCommandBuilder(da);
//Fill data table
da.Fill(dtusers);
//assigning data table to Datagridview
dataGridView1.DataSource = dtusers;
//Resize the Datagridview column to fit the gridview columns with data in datagridview
dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
con.Close();
}


Note: Use your Database details to retrieve records from database.... Mark as answer if it helps you

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ssj_Kumar on: 7/31/2013 [Member] Starter | Points: 25

Up
0
Down
There ara 2 option either you can do in SQL itself or you can do in C#, performance wise SQl will be better

SQL
SELECT CASE When col1 = 1 then 'Yes' ELSE 'No' END col1,
CASE When col2 = 1 then 'Yes' ELSE 'No' END col2,
CASE When col3 = 1 then 'Yes' ELSE 'No' END col3
FROM TableName


C#
for(int i = 0; i < dt.Rows.Count; i++)
{
for(int j = 0; j < dt.Columns.Count; i++)
{
if (dt.Rows[i][j].ToString() == "1")
{ dt.Rows[i][j] = "Yes"; }
else
{ dt.Rows[i][j] = "No"; }
}
}

Regards,
Jayakumar Selvakani

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 7/31/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
With the help of CellFormatting Event Handler we can format the Data values in the DataGrid
Here is the example of Windows Application with DataGridView Control
Step1: Drag & Drop DataGridControl in to the windows Form

Step2:
using System;

using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace DotNetFundaWindowApp
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
BindData();
}

private void BindData()
{

//This connection string is used to connect to database
String strConnection = "Data Source=ServerName ;Initial Catalog=DatabaseName ;User Id=LoginUser ;Password=pwd ";
//Establish SQL Connection
SqlConnection con = new SqlConnection(strConnection);
//Open database connection to connect to SQL Server
con.Open();
//Data table is used to bind the resultant data
DataTable dtusers = new DataTable();
// Create a new data adapter based on the specified query.
SqlDataAdapter da = new SqlDataAdapter("Select * from YesNoTableName ", con);
//SQl command builder is used to get data from database based on query
SqlCommandBuilder cmd = new SqlCommandBuilder(da);
//Fill data table
da.Fill(dtusers);
//assigning data table to Datagridview
dataGridView1.DataSource = dtusers;

// Formatting Cells Data in Data Grid
dataGridView1.CellFormatting += new DataGridViewCellFormattingEventHandler(this.dataGridView1_CellFormatting);

//Resize the Datagridview column to fit the gridview columns with data in datagridview
dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
con.Close();
}

private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
BindData();
}


// Handling CellFormatting allows one to accept user input, then map it to a different
// internal representation.
private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
if (e.Value != null)
{
try
{

if (e.Value.ToString() == "1")
{
e.Value = "Yes";
}
else if( e.Value.ToString() == "0")
{
e.Value = "No";
}
else { e.Value = e.Value; } // If there exist other than 1 or 0

// Set the FormattingApplied property to
// Show the event is handled.
e.FormattingApplied= true;

}
catch (FormatException)
{
// Set to false in case another CellFormatting handler
// wants to try to parse this DataGridViewCellFormattingEventArgs instance.
e.FormattingApplied= false;
}
}
}
}
}


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/2/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi Raj,
Have you checked above solution? Any update from your side?

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response