ugly decimal and booleand handling in ODBC


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 :

first:
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 :
dec_2pens.ToString().Replace(",",".")

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;
                  dtIzc.Columns.Add(colSel);                  
                  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)

LVL 1
sspatsAsked:
Who is Participating?
 
testnConnect With a Mentor Commented:
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>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:datagrid>
0
 
_TAD_Connect With a Mentor Commented:


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???)


Anyway.....


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.
0
 
testnConnect With a Mentor Commented:
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

dec_2pens.ToString()

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 = ",";

Convert.ToString(dec_2pens,cul)
0
 
testnCommented:
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.

Convert.ToString(dec_2pens,cul)
0
All Courses

From novice to tech pro — start learning today.