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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Installing IIS after Visual Studio 2015 Community Edition 5 47
New to SOAP and need some direction 10 46
C# Offline Apllication 5 55
SQL Login 17 40
Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now