Updating a Database from WinForms Controls, Old School

Introduction

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:

  1. Make sure Northwind is available and running on something like SQLExpress (or SQL Server).
  2. Create a WinForms application in Visual Studio for VB.
  3. Add a couple of TextBoxes representing the fields you want to update and a button to invoke an event for the update.
  4. 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):

  1. Right-click Data Connections in the Server Explorer and select Add Connection.
  2. Change the Data source to Microsoft SQL Server.
  3. Enter .\SQLExpress for the Server name. (The .\ means localhost, or your machine.)
  4. In Connect to a database, select the Northwind sample database.
  5. 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.

Figure 2: Configure the Northwind database using settings similar to those shown (noting the use of the Microsoft SQL Server (SqlClient) Data source).

Updating a Database from WinForms Controls, Old School

Creating a WinForms Application

WinForms is the catchy name for a Windows application. You can start the Windows application by selecting File|New Project and picking the Windows Forms Application item from the list of templates for Visual Basic.

The New Project items are basically a combination of existing wizards and scripts that play fill-in-the-blanks games with existing template files. This is all part of Visual Studio's extensibility, and if you have followed my column (over the last ten years, or search existing articles), you know that there are extensive articles on extensibility and wizards. There also are detailed examples in several of my books.

On the Form, add two TextBoxes and a button named Insert (see Figure 3). The Categories table has four columns; one is an automatic primary key and the other is an image. You will just update the two text fields. The schema for Categories is CategoryID, CategoryName, Description, and Picture. If you expand the Categories table, click a column, and open the Properties window Visual Studio will let you view the schema information.

[UpdatingDB3.jpg]

Figure 3: A simple form is the basis for everything else.

Note: Keep it simple if you are just getting started. It will keep your frustration level down and keep you moving forward. The key to progress is to avoid battle fatigue while you are learning new things.

Point and Click Programming is a Useful Starting Point

RAD (or Rapid Application Development) has a bad reputation, but some things like clicking to generate events is easy to do and helps you make progress while deferring things like wiring delegates (event handlers) manually, as long as you don't defer learning how to wire event handlers forever.

To generate a click event for the Insert button (refer to Figure 3), just double-click it. Add the code in Listing 1 to update the Categories table.

Listing 1: Insert a row in the Northwind Categories table from the TextBox values.

Imports System.Data
Imports System.Data.SqlClient


Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, _
   ByVal e As System.EventArgs) Handles Button1.Click

   Dim connectionString As String = _
      "Data Source=CASPAR\SQLEXPRESS;Initial " + _
      "Catalog=northwind;Integrated Security=True"

   Using connection = New SqlConnection(connectionString)
      connection.Open()

      Dim insert As String = String.Format( _
         "INSERT INTO Categories (CategoryName, Description ) " + _
         "VALUES('{0}','{1}')", TextBox1.Text, TextBox2.Text)
      Dim command As SqlCommand = New SqlCommand(insert, connection)
      command.ExecuteNonQuery()

   End Using


End Sub
End Class

Here is some "how-to" information you will want to know.

Add the Imports statement at the top of the listing by typing. The actual assemblies (fancy .NET speak for DLLs) for database stuff are already referenced in WinForms projects. (You can click the Show All Files button—shown in Figure 4—to see the references to included DLLs.)

[UpdatingDB4.jpg]

Figure 4: Click Show All Files to see all of the legwork Visual Studio has done for you, including adding references to useful assemblies.

The connection string is easy to get, too. Click the Northwind database in the Server Explorer and copy the connection string from the Properties window (see Figure 5). The rest of the code is boilerplate. If you don't want to remember how to write this boilerplate code, you can select it all—after its in Visual Studio one time) and drag and drop in the Toolbox. Then, like everything else in the toolbox, you can drag from the toolbox to your project at any future time.

[UpdatingDB5.jpg]

Figure 5: The connection string can be copied from the Properties window.

Run the project, add some text values, and you are off to the races. You can browse the database table to check your results.

Summary

Older techniques, such as updating a SQL database with inline SQL (like the example in this article), still work. Sometimes, it seems like technology changes exist to sell more software or to torture us. Even though software companies have to sell product to stay in business and sometimes new technologies feel like torture, the general belief is that changes in the way we do things exist to make us more productive.

After 20 plus years of experience, I can tell you that if you are just learning how to update a database in the manner shown, you are fortunate. The reason you are fortunate is that you can tuck this tidbit away and then skip this how-to approach and move on to LINQ. Focusing your energies on LINQ (besides being cooler) will make you immediately more productive and it's a lot more fun than cobbling inline SQL together.

About the Author

Paul Kimmel is the VB Today columnist for www.codeguru.com and has written several books on object-oriented programming and .NET. Check out his book LINQ Unleashed for C#, now available on Amazon.com and at fine bookstores everywhere. Look for his upcoming book Teach Yourself the ADO.NET Entity Framework in 24 Hours. You may contact him for technology questions at pkimmel@softconcepts.com. Paul Kimmel is a Technical Evangelist for Developer Express, Inc.

Copyright © 2008 by Paul Kimmel. All Rights Reserved.



Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • As mobile devices have pushed their way into the enterprise, they have brought cloud apps along with them. This app explosion means account passwords are multiplying, which exposes corporate data and leads to help desk calls from frustrated users. This paper will discover how IT can improve user productivity, gain visibility and control over SaaS and mobile apps, and stop password sprawl. Download this white paper to learn: How you can leverage your existing AD to manage app access. Key capabilities to …

  • Not long ago, security was viewed as one of the biggest obstacles to widespread adoption of cloud-based deployments for enterprise software solutions. However, the combination of advancing technology and an increasing variety of threats that companies must guard against is rapidly turning the tide. Cloud vendors typically offer a much higher level of data center and virtual system security than most organizations can or will build out on their own. Read this white paper to learn the five ways that cloud …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds