Use a database value as a TimeSpan

Question

4.00/5 (1 vote)

See more:

Hello, I'm stuck...

I have this code setting a value of 30 minutes used in a timer. The time works great, but I don't want to hard code the value. As soon as I do, some knuckle head is going to ask for it to be changed...

C#

this.applicationIdle.IdleTime = System.TimeSpan.Parse( "00:30:00" );

What I want to do is pull the "TimeSpan" value, or something similar, in a database to make the 00:30:00 customize-able for each customer. Thoughts?

Here is what I tride, but for some reason, the pData always returns -1???
P.S. I've tried public string, private string, protected string, LostMy string... :(

C#

public string getConfValIdleTime()
    {
    string queryString = MgmtSystem.Properties.Settings.Default.SqlGetConfIdleTime;
    string conString = MgmtSystem.Properties.Settings.Default.ConnectionString;
    log.Info( "Executing SQL: " + queryString );
    log.Info( "Using Database Connection: " + conString );
    SqlConnection myConnection = new SqlConnection( conString );
    SqlCommand myCommand = new SqlCommand( queryString, myConnection );
    try
        {
        myConnection.Open();
        string pData = Convert.ToString( myCommand.ExecuteNonQuery() );
        myConnection.Close();
        return pData;
        }
    catch ( SqlException ex )
        {
        return ex.Message;
        }
    }

SQL Code:

SQL

SELECT [cv].[CustomValue] FROM [dbo].[ConfigurationValues] AS cv WHERE [cv].[ID] = 1

Results:
00:40:00

Posted 1-Sep-14 22:40pm

Derek Kennard

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Rob Philpott · Answer 1 · 2014-09-01T22:55:00

Yes, there's a fair bit wrong here.

Firstly ExecuteNonQuery does not return results from queries, just the number of rows 'affected'. You'll want to replace that with ExecuteReader.

Also you exception catch block just returns the exception message. This is bad if you're expecting real data.

Thomas Nielsen - getCore · Answer 2 · 2014-09-03T23:39:00

Solution 3

The pragmatic approach is to store the ticks.

This resolves into a long int value which can be put in the constructor of a timespan again.

Or.. you could do something difficult or clever instead :)

Posted 3-Sep-14 23:39pm

Thomas Nielsen - getCore

Derek Kennard · Answer 3 · 2014-09-02T11:14:00

Here's what I did... after Rob's help and many fun and four letter adjectives, I rebuilt the SQL table to have varchar and INT values for storage. I added the "idle time" to the database as integer 40.

I wrote a new method making it private and keeping the code local. Before, I was trying to create it public, pass it all around, and I was making a mess. I also didn't realize that the code used call the idle time was being called from several places, so I cleaned that up as well.

C#

// Get DB value for timeout
private static int getIdleTime()
    {
    string conString = MgmtSystem.Properties.Settings.Default.ConnString;
    int idleTime = 0;
    string sql = MgmtSystem.Properties.Settings.Default.SqlGetConfIdleTime;
    using ( SqlConnection conn = new SqlConnection( conString ) )
        {
        SqlCommand cmd = new SqlCommand( sql, conn );
        try
            {
            conn.Open();
            idleTime = ( int )cmd.ExecuteScalar(); //may not need cast
            }
        catch ( SqlException ex )
            {
            MessageBox.Show( ex.ToString() );
            log.Debug( "SQL Exception in Main Desktop under getIdleTime(): " + ex );
            }
        }
    return ( int )idleTime;
    }

Took that new value of idleTime and sent it a "string'n" :)
set var = idleTime
string finalVar so it looked like the parsed time span values it so desperately needed: "00:40:00"
and passed it into the time span parse

C#

int var = getIdleTime();
string finalVar = "00:" + var + ":00";
applicationIdle.IdleTime = System.TimeSpan.Parse( finalVar );

Now everything is perfect. On main form load, the timer counts down, alerts the user they've been idle for "X" minutes, and then logs them out when they fail to comply.

BTW - Rob, I know the catch doesn't return a value, only a SQL exception. That's what I want. If the value is not set in the database, then I don't want the user to continue. It is an industry wide regulation that all PHI products have an idle time.

Use a database value as a TimeSpan

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0