将数据库值用作TimeSpan [英] Use a database value as a TimeSpan

问题描述

您好，我被卡住了...



我将此代码设置为计时器中使用的30分钟值。时间很好，但我不想硬编码。一旦我这样做，一些指关节头会要求改变它...

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...

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

我想要做的是拉出TimeSpan值，或类似的东西，数据库使00:30:00为每个客户定制。想法？



这是我想要的，但由于某种原因，pData总是返回-1 ???

P.S.我试过公共字符串，私有字符串，受保护字符串，LostMy字符串... :(

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... :(

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代码：

SQL Code:

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

结果：

00:40:00

Results:
00:40:00

推荐答案

是的，这里有一点错误。



首先，ExecuteNonQuery不返回查询结果，只是'受影响'的行数。你需要用ExecuteReader替换它。



你也异常catch块只返回异常消息。如果你期待真实的数据，这是不好的。

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.

实用的方法是存储刻度。



这解析为一个long int值，可以再次放在timepan的构造函数中。



或者......你可以做一些困难或聪明的事情：）

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

这就是我所做的......在Rob的帮助以及许多有趣和四个字母的形容词之后，我重建了SQL表以获得存储的varchar和INT值。我将空闲时间作为整数40添加到数据库。



我写了一个新方法，将其设为私有并保持代码本地化。在此之前，我试图将其公之于众，将其全部传递出去，我一团糟。我也没有意识到所使用的代码调用空闲时间是从几个地方调用的，所以我也清理了它。

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.

// 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;
    }

获取idleTime的新值并将其发送给string'n：）

set var = idleTime

string finalVar所以它看起来像它所急需的解析时间跨度值：00:40:00

并将其传递到时间跨度解析

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

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

现在一切都很完美。在主表单加载时，计时器倒计时，提醒用户他们已经空闲了X分钟，然后在他们不遵守时将其记录下来。



BTW - Rob，我知道catch没有返回值，只有一个SQL异常。这就是我想要的。如果未在数据库中设置该值，则我不希望用户继续。所有PHI产品都有空闲时间，这是一项行业范围的规定。

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.

这篇关于将数据库值用作TimeSpan的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！