Moneta is a slight diversion from the Mercury project. Just as the Mercury project is named for the messenger of the gods in Roman mythology, Project Moneta is named for the Roman goddess of memory. The project goal is to give a very simple introduction to saving data from VB to a SQL Server.
So let me give my standard disclaimer – I’m no expert developer. I know we’ve got a number of members in the Facebook group he could code circles around me before their morning coffee…. so take this post as it is intended – an illustration of how easy it really is to do some of the things a lot of us want to do in home automation!
There are a number of packages and libraries available, allowing you to use whatever database is your favorite. I’m using SQL Server due to its integration in Visual Studio and the fact that you can get an Express version for free. The assumption here is that you know how to install, configure, and access your database software. We’ll start from adding the new table:
create table MQTTMessages (
MessageID int IDENTITY (1,1) NOT NULL PRIMARY KEY,
Topic varchar(75) NOT NULL,
MessageValue varchar(50) NOT NULL,
MessageDateTime datetime NOT NULL
);
I’d also add three or four records at this time, just to make it easier to verify that the app works correctly later.
The MessageID field will auto generate , so we don’t include it in our code. The other fields allow us to use our experience with MQTT to streamline the illustration of SQL – the actual implementation into Project mercury will be designed a bit differently, but the basic mechanics are the basic mechanics, with similarities even between different connectors/packages/libraries. Now the next portion:
Create a new Windows Form project.
Add a TextBox, set for multi-line, and resize it to use roughly half of the form (named txtLog in my code)
Add two more Text boxes, do not use multi-line, and stretch them to fill the second half of the form (txtTopic and txtPayload in my code)
Add two labels to identify the text boxes
Add two buttons, one labeled “Read Data” and one labeled “Save Data”
Next, go to your code. Import System.Data.SqlClient and declare your variables – your connection string can use a domain or an IP address for the server setting, but it won’t allow you to specify a port.
The next step is to add the “Read Data” button’s code. You open the connection, fire off the reader, and then loop through the records, doing some minor formatting before appending the message to the txtLog text box. If you added records when creating your table and put in the correct server and credentials in the connection string, then you should see data when you click the button (1 row per line). Closes connection.
The last step is to code the Save Data button. Open connection, set the parameters for SQLCommand and execute. The messagebox returning the number of rows affected (will always be 1 using this code), shows that it worked. In fact, if you click the read button again, it clears the old text and reloads from the DB, including the record you just added. Then closes the connection.
Here’s an example of what the you’ll see when running the app and clicking Read Data!
Our next entry will start the RFLink adventure. Stay tuned!
SmarterHome.club is the website for our Facebook community, The Smarter Home Club – which is an umbrella for all kinds of smart home technologies – home automation, security, custom electronics, weather stations, alternative energy, you name it. DIY focused.
If you’re interested in joining the Smarter Home Club’s Facebook group, please follow this link:
The Smarter Home Club on Facebook