ugly decimal and booleand handling in ODBC

Posted on 2003-12-08
Last Modified: 2012-05-04

i am using ODBC driver to work with mysql database -
first pain is decimals. My locale regards using comma as decimal separator. So to take them from datarow and send to mysql i do following :

object dec_2pens = drw_aRow["2pens"];
if (dec_2pens == System.DBNull.Value) {dec_2pens = 0;} // to avoid dbNull exception

second - in sqlStatement i am writing :

that looks not nice

second pain are booleans. The function below is self-explanaitory.

private void PakIzcenModBool(System.Data.DataTable dtIzc, string strField)
                  // workaround as odbc driver does not recognize tinyint(1) as boolean
                  //make and add checkbox select column
                  System.Data.DataColumn colSel = new System.Data.DataColumn();
                        colSel.AllowDBNull = false;
                        colSel.ColumnName = strField + "_b";
                        colSel.Caption = strField + "_b";
                        colSel.DataType = System.Type.GetType("System.Boolean");
                      //colSel.Expression = "Convert ( " + strField + ", 'System.Boolean' )";
                        colSel.DefaultValue = false;
                  for (int i = 0; i < dtIzc.Rows.Count; i++) {
                  Boolean boolUse = Convert.ToBoolean(dtIzc.Rows[i][strField]);
                  dtIzc.Rows[i][strField + "_b"] = boolUse;

are there any ways to do these things nicer?
 what are your experiences with mysql ?
(I've tried bytefx 'native' .net mysql driver - but it still does not recognize my language chars correctly
and there was another driver MwsqldriverCS which works poorly with different data types)

Question by:sspats
  • 3
LVL 22

Assisted Solution

_TAD_ earned 20 total points
ID: 9902031

First with the comma/decimal thing... you should REALY look into the system.Globalization namespace.

It's pretty straight forward, using that namespace you can convert back and forth between En-US and En-GB pretty quickly with almost no overhead.

Secondly as far as boolean data goes.... a simple cast will do you just fine.

I don't recall all of the rules off the top of my head, but I know all positive numbers are true and I am pretty sure 0 is false, and negative numbers are undefined.

(or is it negative are false and 0 is undefined???)


int i = 27;
bool b = (bool) i;   // b = true

If I had .net on this computer I would test it out to give you the exact rules... sorry I can't right now.
LVL 18

Assisted Solution

testn earned 70 total points
ID: 9903330
about , and ., just change your culture somethere..... ie in the constructor

CultureInfo cul =  new CultureInfo( "en-US", false ); // you can change it to your country's culture
NumberFormatInfo nfi = cul.NumberFormat;
nfi.CurrencyDecimalSeparator = ",";
Thread.CurrentThread.CurrentCulture = cul;

and then when convert the number to string, you just


but it will affect everywhere.

if you don't want to do that you can also save the culture info somewhere as a variable

CultureInfo cul =  new CultureInfo( "en-US", false ); // you can change it to your country's culture
NumberFormatInfo nfi = cul.NumberFormat;
nfi.CurrencyDecimalSeparator = ",";

LVL 18

Expert Comment

ID: 9903341
if you want to avoid dbnull and you just want to show the value in string, you just use the above expression instead. You don't have even to handle null. The value will be shown as empty string since convert.tostring will convert dbnull into string.empty.

LVL 18

Accepted Solution

testn earned 70 total points
ID: 9903405
for boolean stuff, i think there is no way that you can convert the data into boolean automatically. It is not mysql managed provider problem but tinyint can be byte not just boolean. It is unlike SQL Server 2000 that has another data type call "Bit".

You might consider to use Convert.ToBoolean and Convert.ToByte to convert back and forth between mysql mapped datatype and .net datatype without adding a new column. You can perform the conversion when you use it.

For example, if you use ADO.NET with ASP.NET

<asp:datagrid id="DataGrid1" runat="server">
<Columns >
<asp:TemplateColumn HeaderText ="Product Information" >
<ItemTemplate >
<asp:Label ID="lblID" Runat=server text=<%#DataBinder.Eval(Container.DataItem , "ProductID")%>></asp:Label>
<asp:Label ID="lblName" Runat=server text=<%#DataBinder.Eval(Container.DataItem , "ProductName")%>></asp:Label>
<asp:CheckBox id="chk1" Runat =server  checked="<%#DataBinder.Eval(Container.DataItem,"Discontinued")%>"></asp:CheckBox>

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question