Updating a Database from WinForms Controls, Old School
So much about programming technology changes and a lot of it stays the same. One reader wrote and asked me how to update a database from WinForms controls (not a grid), writing SQL inline. I haven't used this approach in a while, so I had no ready-made examples. I did whip one up and send it to him, though. Another reader wrote and told me that my Sieve of Eratosthenes prime number algorithm sucked (his word, not mine), but after he told me that he had been writing it and re-writing it for 40 years I didn't feel so bad.
Of all of the reader email I get, just these two things alone reminded me that of course I don't know everything, and many of you are working at a widening level of experience. To that end, this article is an old school article that shows new-to-WinForms programmers how to read data from controls and contrive SQL that updates a database.
I would encourage anyone reading this article, working at this level, to immediately upgrade to stored procedures very quickly and then move on to LINQ. Inline SQL works fine, but it's so 90s. LINQ, LINQ to SQL, LINQ to Entities, and declarative programming will help you be much more productive and are much cooler technologies.
Describing the Scenario
Maybe you are a FoxPro programmer or one of the dwindling numbers of COBOL or assembler programmers finally moving over to Web and Windows programming with .NET, or maybe you are just getting started. (We were all there once, and in this business everyone is just getting started with some new technology—like Ruby, F#, or LINQ to Entities.) The challenge is how to get data from a WinForm control (or controls) into your database. The way you can do it (but fewer of us will going forward) is to read a value, format it as part of a SQL query, and invoke the SQL command.
There is no value judgment when I tell you this. This approach works, and it works well. The challenge is that more of your peers will be using LINQ, other party tools, or declarative programming, and their productivity will outpace yours if you dogmatically stick with the approach outlined here. If you are just transitioning or getting started, this is a good place to dabble but not put down roots.
Inserting a Record into the Northwind Categories Sample Database
The Northwind database is a safe place to start. The database is readily available, the schema is well-known and has sample data, and it's not too clever. To complete the demo, I used Visual Studio 2008, .NET 3.5, and a basic WinForms Form with TexBoxes for the data. You should be able to replicate this example with Visual Studio Express or the first version of .NET, though.
To summarize, you will need to complete these basic steps:
- Make sure Northwind is available and running on something like SQLExpress (or SQL Server).
- Create a WinForms application in Visual Studio for VB.
- Add a couple of TextBoxes representing the fields you want to update and a button to invoke an event for the update.
- In the Button's event handler connect to the database, read the TextBox.Text properties, and piece together a SQL Insert command, invoking the command.
If you are updating multiple tables, put everything inside of a transaction. Other than that, sending SQL to a database is pretty much the same using this approach.
Attaching the Northwind Sample Database
Depending on your version of Visual Studio, the Northwind database may not be in your Server Explorer Data Connections list (see Figure 1). If Northwind isn't there, add it by following these steps (using Figure 2 as a visual guide):
- Right-click Data Connections in the Server Explorer and select Add Connection.
- Change the Data source to Microsoft SQL Server.
- Enter .\SQLExpress for the Server name. (The .\ means localhost, or your machine.)
- In Connect to a database, select the Northwind sample database.
- Click Test Connection to make sure everything works, and click OK.
Figure 1: Visual Studio 9 doesn't have good old Northwind installed by default.