Posts Tagged ‘sql 2005’

As previously mentioned my main program depends 100% off a SQL database, below is a dump of my “Configuration” database, that holds all the system settings.
Many are loaded into memory the first time, or when I run PollDevices query this way the information is updated when needed.  I know its a lot of information but maybe a developer can use these fields as ideas for their program.

Below are my two main functions I’ve created call (get a value or update a value).

Grabbing a value from the “Config” Database

For example this function will return the value of a specific query for example


 Public Function Lg_Get_config(ByVal field As String) As String

        Dim sqlConnection35 As New System.Data.SqlClient.SqlConnection(My.Settings.InsteonConnectionString)
        Dim cmd35 As New System.Data.SqlClient.SqlCommand

        Dim Reader As SqlClient.SqlDataReader
        cmd35.CommandText = "SELECT * FROM Config where config= '" + field.ToLower.Trim + "'"
        cmd35.Connection = sqlConnection35

        Do While sqlConnection35.State <> ConnectionState.Open

        Reader = cmd35.ExecuteReader()

        ' --------- check for data, if not return 'FALSE' AND leave function
        If Reader.HasRows = False Then
            Lg_Get_config = False
            Exit Function
        End If

'---------- return the value and close all connections and exit.
        Lg_Get_config = Reader.Item("value")

    End Function

Setting a value in the “Config” Database

The next example updates a field, and is very similar just that it uses the SQL update command

For example lets say I want to update the “wake_up_time” to something else

lg_set_config("wake_up_time","9:00:00 AM")

    Public Function Lg_Set_config(ByVal field As String, ByVal value As String) As Boolean

        '------------- SETUP CONNECTION
        Dim sqlConnection4 As New System.Data.SqlClient.SqlConnection(My.Settings.InsteonConnectionString)
        Dim cmd4 As New System.Data.SqlClient.SqlCommand
        cmd4.CommandType = System.Data.CommandType.Text
        cmd4.Connection = sqlConnection4

        '------ The Update Command to modify the field
        cmd4.CommandText = "UPDATE Config SET Value= '" + value + "' WHERE config='" + field.Trim.ToLower + "'"

        '--------- Since we are not getting anything in return this is a NonQuery Executre
        Lg_Set_config = True
        Update_TimeStamp()  ' - I update the TimeStamp Value so anytime its queried they know is been updated.

    End Function

Data Dictionary The “Config” Database

marker – Int field
config – nvarchar(128)
value – nvarchar(255)
description – nvarchar(255)

* marker field is my primary key, this is also set to “Identity Specification” which means it will autopopulate with a incremental value, so no record is the same, example (1,2,3,4,5,6 etc)

Below is a dump of all my fields and a quick explanation of what they represent.  Descriptions starting with RST are the ones that are updated by the system automatically. For example if I turn on the outside lights, I update the field at that moment to say it has been turned on.

outdoor_turn_off_time 10:32 PM When will all outdoor lights will turn off

outdoor_turnedon Y RST, If outdoor lights are on?
extractor_girls N RST, If the extractor is on or off
extractor_girls_time 10 In Minutes, Timeout for the girls extractor
timer_randomlights N If the random lights are on
random_lights_min 10 Random lights timer in minutes
time_after_sunset_to_turn_on 12 Time in minutes after sunset the lights outside will turn on
temperature 75% RST, Current temperature
temperature_last_read 6:40:29 AM RST, When was the temperature last read
temperature_text Mostly Cloudy RST, Text from NOAA.GOV
temperature_image RST, Local image
SunRise 6:59:52 AM RST, SunRise in time
SunSet 7:56:07 PM RST, SunSet in Time
Outdoor_turn_on_time 8:08:07 PM RST from program, Outdoor turn on time
fan_off_low 15 Turn off Fan Threshold
fan_on_high 88 Turn on fan Threshold
voicemails N RST,If Voicemails are present?
master_closet_timer 3 Master Closet Timer in Minutes
master_closet N RST,Master Closet is on?
alarm_clock_status ON Mine, Turn on Alarm clock, M-F
poll_devices_interval 525 Minutes to poll each device
poll_devices_next_poll 8/23/2008 15:28 RST, Next time to poll
master_closet_turn_off_time 8/22/2008 22:39 RST, Master Closet
extractor_girls_turn_off_time 8/22/2008 21:46 RST,Girls Turn off Extractor time
Entrance_turnedon N RST, Is entrance on?
entrace_turn_on_time 8:01:07 PM RST, What time to turn entrance on
time_after_sunset_entrance_turn_on 5 Time to turn on Entrace after SunSet in Minutes
house_state NIGHT House State (E,O,I)
wake_up_time 6:15:00 AM Master Wake up time, M-F only
time_after_sunrise_to_turn_off 10 Time after SunRise to turn off Nook (Sat and sun Only)
incomming_call N CallerID Found? – No Longer Used
incomming_stamp 8/22/2008 17:29 RST, TimeStamp to clear call (No longer used)
incomming_name Luis Garcia RST, Caller ID
incomming_image luis.png RST, Imaged pulled from Contacts DB
incomming_date 8/22/2008 17:29 RST, Incomming Time
incomming_number xxxxxxxxxx RST, Incomming Number
entrance_turn_off_pre -5 Time in minutes before turn off time to turn off the entrance only
entrance_turn_off_pre_time 10:27:00 PM From program result of entracne_Trun_off_pre
motion_hallway N Motion is detected in hallway
extractor_master N If the extractor is on or off
extractor_master_time 10 Timeout for Extrator Master
extractor_master_turn_off_time 8/23/2008 6:18 RST,Master Turn off Extractor time
sensor_hallway N IF Hallway Sensor is on
sensor_hallway_time 6 Timeout Hallway
sensor_hallway_turn_off_time 8/23/2008 6:14 Hallway Timeout
sensor_hallway_bypass N Set to N if normal and Y to ignoreTimer
sensor_entrance_last_seen 8/23/2008 2:48 RST, Last time movement was detected
sensor_entrance_rescan_timeout 3 Minutes before the entrance sends out another signal
sensor_entrance_bypass Y If sensor entrance is bypassed (Y)=dont check (N)=yes check
sensor_master_closet_bypass N If sensor master is bypassed (Y)=dont check (N)=yes check
sensor_hallway_last_seen 8/23/2008 6:08 RST, Last time movement was detected
sensor_master_bathroom_last_seen 8/23/2008 6:09 RST,Last time movement was detected in master batrhoom
sensor_den_last_seen 8/22/2008 23:05 RST,Last time movement was detected in DEN
sensor_garage_last_seen 8/22/2008 20:59 RST,Last time movement was detected in Garage
sensor_hallway_start_time 6:00:00 PM Time that the Sensor will kick in. Usually set 3pm since its day time it wont turn on
sensor_garage_door_state CLOSED Garage door if open
temperature_last_read_wdw 6:40:29 AM Last time temperature was read for WDW
sensor_outside_bathroom_last_seen 8/23/2008 0:25 RST,Last time movement was detected in master batrhoom
sensor_outside_bathroom_timeout 25 Outside bathroom timeout
sensor_outsidebathroom_enabled Y RST, Enabled or not?
music_playing_status N RST, Is Radio Music Playing
sensor_garage_door_state_last 8/22/2008 20:58 RST, Last time Garage was open or closed
sensor_garage_door_timer 6/23/2008 20:48 RST, Time is has been opened
decorations_status OFF Status of all decorations
sensor_den_timer 8/22/2008 23:10 RST, Time the Den will turn off it no movement
sensor_den_timer_timeout 5 Minutes, Den will turn off after no activity
sensor_master_closet_last_seen 8/22/2008 22:36 RST, MasterCloset Last Seen
decorations_on 7:58:07 PM Time Decorations turn on
decorations_off 10:45:00 PM Time Decorations turn off
decorations_after_sunset_on 2 Decorations turn on after sunset
speakers_status OFF RST, if speakers are on
coffee_machine_status TRUE CoffeeMachine timer if on or OFF
coffee_machine_timer 6:20:00 AM CoffeeMachine OnTime
sensor_laundry_last_seen 8/22/2008 21:30 RSt, LAST seen activity in Laundry
sensor_laundry_timeout 3 Minutes before laundry turns off
sensor_laundry_timer 8/22/2008 21:33 RST, When will it turn off
kelly_alarm_on 6:15:00 AM Kelly On_Time
kelly_alarm_status ON IF Kellys alarm is on or off
barbie_alarm_on 7:01:00 AM Barbie OnTime
barbie_alarm_status ON Barbie on-time
back_house_lights_on 9:00:00 PM When will the backlights turn on
back_house_lights_off 9:30:00 PM When will the backlights turn off
guilda_alarm_on 6:15:00 AM Guildas Light will turn on
guilda_alarm_status ON Guildas Alarm status
kelly_alarm_song S:\Mp3\Hanna Montana6 – I Got Nerve.mp3 Kelly AlarmSong
barbie_alarm_song S:\Mp3\Abba\(1994) Thank You For The Music\Disc 1\21 – Abba – My Love, My Life.mp3 Kelly AlarmSong
kelly_artist_songs1 hanna mon Kelly selected artist for Alarm
kelly_artist_songs2 vanessa Kelly selected artist for Alarm
kelly_artist_songs3 ashley tins Kelly selected artist for Alarm
barbie_artist_songs1 cascada Barbie Selected artist for Alarm
barbie_artist_songs2 Abba Barbie Selected artist for Alarm
barbie_artist_songs3 Rick Springfield Barbie Selected artist for Alarm
kelly_alarm_song_title Hanna Montana – I Got Nerve Last song Played
barbie_alarm_song_title ABBA – My Love, My Life Last song Played