Converting tinyint to Yes / No and binding it to grid

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

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

0	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

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

0	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

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

0	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

Latest Posts