Solved

ugly decimal and booleand handling in ODBC

Posted on 2003-12-08
4
375 Views
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 :

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)

0
Comment
Question by:sspats
  • 3
4 Comments
 
LVL 22

Assisted Solution

by:_TAD_
_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???)


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
 
LVL 18

Assisted Solution

by:testn
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

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
 
LVL 18

Expert Comment

by:testn
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.

Convert.ToString(dec_2pens,cul)
0
 
LVL 18

Accepted Solution

by:
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>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:datagrid>
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Article by: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

708 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

17 Experts available now in Live!

Get 1:1 Help Now