[C#] SQL time naar timespan

[PoweRoy]

Ik ben bezig met een naslag programma te maken met een Acces database als opslag. Nu heb ik in een tabel een column met als type Time. Dit gebruik ik om de speeltijd van een liedje in op te slaan dus in HH:MM:SS formaat (In Acces staat het aangegeven met Data/Time)

Nu als ik een select uitvoer op deze database en wil laten zien in een datagridview in c# heb ik een probleem met de tijd column. Hij geeft 30-12-1899 0:00 aan ipv 00:00:56.

De query die uitwordt gevoerd is "select * from Music".

Via google kom ik manieren tegen waar je een lege datatable hebt die vult met kolomen vanuit de database. Dan verander je de tijd column naar een Timespan met :
musicTable.Columns[4].DataType = System.Type.GetType("System.TimeSpan");
En dan 1 voor 1 de rijen importen maar dit werkt niet. Dit komt omdat in de rij de tijd waarde niet een timespan is waardoor hij op z'n gat gaat:

Specified cast is not valid.Couldn't store <30-12-1899 0:00:56> in duration Column. Expected type is TimeSpan.

De tijd wordt in de database opslagen door middel van de ToString() van een Timespan.

C#:

public DataTable getMusic()
{
    //create the connection string
    string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + databaseFile+ ";Jet OLEDB:Engine Type=5";

    //create the database query
    string query = "SELECT * FROM Music";

    //create an OleDbDataAdapter to execute the query
    OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);

    //create a command builder
    OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);

    //create a DataTable to hold the query results
    DataTable dTable = new DataTable();

    //fill the DataTable
    dAdapter.Fill(dTable);

    //return the table
    return dTable;
}

dit werkt dus niet:

C#:

DataTable rawMusicTable = manager.getMusic();
DataTable musicTable = rawMusicTable.Clone();
musicTable.Columns[4].DataType = System.Type.GetType("System.TimeSpan");
for (int i = 0; i < rawMusicTable.Rows.Count; i++ )
{
     musicTable.ImportRow(rawMusicTable.Rows[i]);
}

Het vreemste vind ik zelf dat in C# niet automatisch wordt gedetecteerd dat het een TimeSpan veld is. Hoe kan ik dit probleem verhelpen?

[whoami]

C# ontdekt dat niet, omdat het in Access ook maar gewoon een datum-veld is. Enkel de notatie in Access is 'tijd'. Er zal echter altijd een datum in opgeslagen worden.

Je zult eens moeten uitvissen wat de '0' datum is die Access gebruikt, en dan zal je die 0-datum moeten aftrekken van de datum die je opgeslagen hebt. Dan zal je een TimeSpan object terugkrijgen.

Enneh, datums converteren naar strings om zo op te slaan ?
Ik zou eens kijken naar parametrized queries.

[ Voor 13% gewijzigd door whoami op 16-01-2008 12:00 ]

[Gurbe de n00b]

Als het in de database word opgeslagen als een string, dan zal je hem eerst moeten converteren met de
Timespan.Parse methode denk ik.

C#:

// Example of the TimeSpan.Parse( string ) and TimeSpan.ToString( ) 
// methods.
using System;

class TSParseToStringDemo
{
    static void ParseNDisplayTimeSpan( string intervalStr )
    {
        // Write the first part of the output line.
        Console.Write( "{0,20}   ", intervalStr );

        // Parse the parameter, and then convert it back to a string.
        try
        {
            TimeSpan intervalVal = TimeSpan.Parse( intervalStr );
            string   intervalToStr = intervalVal.ToString( );

            // Pad the end of the TimeSpan string with spaces if it 
            // does not contain milliseconds.
            int pIndex = intervalToStr.IndexOf( ':' );
            pIndex = intervalToStr.IndexOf( '.', pIndex );
            if( pIndex < 0 )   intervalToStr += "        ";

            Console.WriteLine( "{0,21}", intervalToStr );
        }
        catch( Exception ex )
        {
            // If Parse throws an exception, write the message.
            Console.WriteLine( ex.Message );
        }
    } 

    static void Main( )
    {
        Console.WriteLine(
            "This example of TimeSpan.Parse( string ) and \n" +
            "TimeSpan.ToString( ) " +
            "generates the following output.\n" );
        Console.WriteLine( "{0,20}   {1,21}", 
            "String to Parse", "TimeSpan or Exception" );
        Console.WriteLine( "{0,20}   {1,21}", 
            "---------------", "---------------------" );

        ParseNDisplayTimeSpan( "0" );
        ParseNDisplayTimeSpan( "14" );
        ParseNDisplayTimeSpan( "1:2:3" );
        ParseNDisplayTimeSpan( "0:0:0.250" );
        ParseNDisplayTimeSpan( "10.20:30:40.50" );
        ParseNDisplayTimeSpan( "99.23:59:59.9999999" );
        ParseNDisplayTimeSpan( "0023:0059:0059.0099" );
        ParseNDisplayTimeSpan( "24:0:0" );
        ParseNDisplayTimeSpan( "0:60:0" );
        ParseNDisplayTimeSpan( "0:0:60" );
        ParseNDisplayTimeSpan( "10:" );
        ParseNDisplayTimeSpan( ":10" );
        ParseNDisplayTimeSpan( "10:20:" );
        ParseNDisplayTimeSpan( ".123" );
        ParseNDisplayTimeSpan( "10." );
        ParseNDisplayTimeSpan( "10.12" );
    } 
} 

/*
This example of TimeSpan.Parse( string ) and
TimeSpan.ToString( ) generates the following output.

     String to Parse   TimeSpan or Exception
     ---------------   ---------------------
                   0        00:00:00
                  14     14.00:00:00
               1:2:3        01:02:03
           0:0:0.250        00:00:00.2500000
      10.20:30:40.50     10.20:30:40.5000000
 99.23:59:59.9999999     99.23:59:59.9999999
 0023:0059:0059.0099        23:59:59.0099000
              24:0:0   TimeSpan overflowed because the duration is too long.
              0:60:0   TimeSpan overflowed because the duration is too long.
              0:0:60   TimeSpan overflowed because the duration is too long.
                 10:   Input string was not in a correct format.
                 :10   Input string was not in a correct format.
              10:20:   Input string was not in a correct format.
                .123   Input string was not in a correct format.
                 10.   Input string was not in a correct format.
               10.12   Input string was not in a correct format.
*/ 

Probleem is natuurlijk dat je eerst alles moet doorlopen om alles om te zetten naar een timespan datetype.

[Niemand_Anders]

Als jouw programma de enigste applicatie is welke gebruik maakt van de access database, kun je TimeSpan (en DateTime trouwens ook) het beste opslaan op basis van de Ticks property.

Op deze manier slaat de CLI de datums zelf op als je een object met TimeSpan of DateTime serialiseerd. Ticks zij van het formaat (Int64 of long). In Access zul je waarschijnlijk double moeten gebruiken om de waarde op te slaan.

Daarna heb je geen last meer van een datum in je TimeSpan. Gebruik bij de presentatie van een datum of timestamp wel altijd het format argument zodat je de presentatie krijgt welke je verwacht.

[Gurbe de n00b]

Wat ook een oplossing is, is de duur van een liedje in seconden opslaan. Deze kun je zo weer omzetten naar een datetime.

[PoweRoy]

Wat ik bedoelde is dat de timespan als een string in een query wordt gezet:

alleen tijd veld overgelaten:

C#:

"INSERT INTO " + table + "(duration) " + "VALUES ('" + <timespan object> + "')"

Dan zorgt de OleDbCommand of de database voor de parsing, het komt immers goed in de database.

Gurbe de n00b schreef op woensdag 16 januari 2008 @ 12:10:
Wat ook een oplossing is, is de duur van een liedje in seconden opslaan. Deze kun je zo weer omzetten naar een datetime.

Daar heb ik ook aan gedacht maar vind ik niet bepaald mooi Ook de table moet dan worden omgezet.

[ Voor 36% gewijzigd door PoweRoy op 16-01-2008 12:13 ]

[RobIII]

PoweRoy schreef op woensdag 16 januari 2008 @ 12:11:
Wat ik bedoelde is dat de timespan als een string in een query wordt gezet:

alleen tijd veld overgelaten:

C#:

"INSERT INTO " + table + "(duration) " + "VALUES ('" + <timespan object> + "')"

Dan zorgt de OleDbCommand of de database voor de parsing, het komt immers goed in de database.

Again; kijk eens naar Over het gebruik van Parametrized Queries En ik mis een where-clause in je statement, maar ik neem aan dat je die bewust hebt weggelaten?

[whoami]

Eigenlijk is het idd beter om een tijd (die geen datum is) op te slaan als een int, waarbij de int het aantal seconden voorstelt.
Je slaat dan de TotalSeconds van je Timespan op in je DB, en bij het ophalen kan je de constructor gebruiken van de Timespan waarbij je het aantal uren / minuten / seconden kunt opgeven.
Als je dan 520 seconden in je DB hebt opgeslagen, doe je gewoon:

code:

TimeSpan s = new TimeSpan(0, 0, 0, 520);

[whoami]

PoweRoy schreef op woensdag 16 januari 2008 @ 12:11:
Daar heb ik ook aan gedacht maar vind ik niet bepaald mooi Ook de table moet dan worden omgezet.

Niet mooi ?
Het is eigenlijk de enige juiste oplossing in dit geval, want je wil helemaal geen datum hebben... Je wilt gewoon een tijd hebben.
er bestaat geen type 'Time'. Die types zijn altijd 'DateTime', en er wordt dus altijd een datum opgeslagen. Dat jij enkel de tijd ziet in Access, is omdat de notatie 'tijd' is. Het datum-gedeelte wordt dus verborgen.
Daarbij komt ook nog eens dat een DateTime intern ook als 2 doubles opgeslagen wordt...

Dan zorgt de OleDbCommand of de database voor de parsing, het komt immers goed in de database.

Daar zou ik nog niet zo zeker van zijn....
En wat met SQL injection ?
Kijk dus maar eens naar parametrized queries.

[ Voor 14% gewijzigd door whoami op 16-01-2008 12:16 ]

[Gurbe de n00b]

RobIII schreef op woensdag 16 januari 2008 @ 12:13:
En ik mis een where-clause in je statement, maar ik neem aan dat je die bewust hebt weggelaten?

Een where-clause in een insert statement ?

[TeeDee]

Gurbe de n00b schreef op woensdag 16 januari 2008 @ 12:18:
[...]
Een where-clause in een insert statement ?

Select * from MUSIC in de TS. Moest ook even 2x lezen

[PoweRoy]

van parametrized queries had ik nog nooit gehoord maar heb het toegepast

Verder heb ik het tijd type in een integer veranderd (in de database), alleen bij het tonen gaat het nog niet helemaal goed.

Dit stuk bewaard. Wil nu het laten zien als een tijdspan ipv een int.

C#:

DataTable rawMusicTable = manager.getMusic();
DataTable musicTable = rawMusicTable.Clone();
musicTable.Columns[4].DataType = System.Type.GetType("System.TimeSpan");
for (int i = 0; i < rawMusicTable.Rows.Count; i++ )
{
    musicTable.ImportRow(rawMusicTable.Rows[i]);
}

ik krijg dan tezien: 00:00:00.0000056. De constructor van een TimeSpan heeft een constructor met 1 parameter(in het geval van nanoseconden, wat hij nu weergeeft) en van 3 parameters(in het geval van HH:MM:SS). Kan je dit dan ook automatisch laten doen bij het veranderen van het kolomtype

Of moet ik in de forloop een nieuwe row maken, oude waardes kopieren behalve de tijd (en die omzetten naar een timespan)

[ Voor 4% gewijzigd door PoweRoy op 16-01-2008 12:53 ]

[bigbeng]

Wat jij ziet is het resultaat van een standaardconversie van int naar timespan. Dit is een automatische conversie en die kun je dus ook niet echt sturen. Je zult bij het ophalen van de tijd, die tijd of naar nanoseconden om moeten zetten, of zelf de tijdconversie doen.

[whoami]

Je haalt het aantal seconden op, en gebruikt de 3 arg constructor zoals ik al in een eerdere reply in dit topic gezegd heb ....

[PoweRoy]

Zo werkt het nu:

C#:

public DataTable getMusic()
{
    //create the connection string
    string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + databaseFile;

    //create the database query
    string query = "SELECT * FROM Music";

    //create an OleDbDataAdapter to execute the query
    OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);
    
    //create a DataTable to hold the query results
    DataTable rawTable = new DataTable();

    //fill the DataTable
    dAdapter.Fill(rawTable);

    //change the duration to a timespan
    DataTable musicTable = rawTable.Clone();
    musicTable.Columns[4].DataType = System.Type.GetType("System.TimeSpan");
    for (int i = 0; i < rawTable.Rows.Count; i++)
    {
        DataRow dRow = musicTable.NewRow();
        dRow["name"]        = rawTable.Rows[i][0];
//snipped
        dRow["duration"]    = new TimeSpan(0,0,Convert.ToInt32(rawTable.Rows[i][4]));
//snipped
        musicTable.Rows.Add(dRow);

        //musicTable.ImportRow(rawTable.Rows[i]);
    }            

    //return the table
    return musicTable;
}

whoami schreef op woensdag 16 januari 2008 @ 14:01:
Je haalt het aantal seconden op, en gebruikt de 3 arg constructor zoals ik al in een eerdere reply in dit topic gezegd heb ....

De 3 arg constructor gebruikte ik al eerder maar wist niet of ik die ook 'default' kan instellen bij een column. Via bovenstaande manier heb ik dit omzeilt, bedoelde je dit ook

[riezebosch]

typeof(System.TimeSpan) ipv System.Type.GetType("System.TimeSpan")

[Woy]

Of je veranderd je DataTable niet en gebruikt het CellFormatting event om je seconden om te zetten naar het juiste formaat.

[riezebosch]

rwb schreef op woensdag 16 januari 2008 @ 15:51:
Of je veranderd je DataTable niet en gebruikt het CellFormatting event om je seconden om te zetten naar het juiste formaat.

Aan zoiets zat ik ook te denken, en volgens mij kan het dan wél handig zijn om een DateTime te gebruiken en dat gewoon te formatten naar alleen de tijd.

[whoami]

riezebosch schreef op woensdag 16 januari 2008 @ 16:07:
[...]

Aan zoiets zat ik ook te denken, en volgens mij kan het dan wél handig zijn om een DateTime te gebruiken en dat gewoon te formatten naar alleen de tijd.

In het geval van de TS lijkt het mij onlogisch om de duur van een song als een DateTime op te slaan.
Als je dat doet, wil dat zeggen dat iedere applicatie die gebruik maakt van z'n DB moet weten dat hij de DateTime die in de DB zit moet aftrekken van een bepaalde datum (die moet gedocumenteerd zijn), om zo de TimeSpan te krijgen die de lengte van de song voorstelt.

Ik zie het nut niet in om de duur van een liedje als een DateTime op te slaan, maar als iemand daar wel iets nuttig in ziet, dan mag hij dat altijd laten weten natuurlijk..

[ Voor 20% gewijzigd door whoami op 16-01-2008 16:23 ]

[Woy]

Ik bedoel ook niet om het als datetime op te slaan. Maar gewoon in secondes. De TS stopt het daarna direct in een DataTable, maar er is toch niks mis mee om het daar in seconden te laten staan. Op het scherm kun je het daarna mooi omtoveren naar een string d.m.v. het CellFormatting event

[PoweRoy]

rwb schreef op woensdag 16 januari 2008 @ 15:51:
Of je veranderd je DataTable niet en gebruikt het CellFormatting event om je seconden om te zetten naar het juiste formaat.

Net even een testje gedaan en celformatting werkt net zo goed. En net zo snel, immers als het een timespan moet hij het ook de heletijd bij resize etc het displayen als een string.
En nu kan ik er extra dingen bij doen zoals kleur

[ Voor 47% gewijzigd door PoweRoy op 16-01-2008 16:52 ]