Hand Coding a Simple Program

Hand Coding a Simple Program

If you would like to manually code a simple data retrieval program, you can do it with a few lines of code. However, there are some caveats. First, only a single table can be retrieved. Second, that table must have a primary key. These restrictions are pretty important from where I stand, but it's good to know exactly how to hand code a program.

Create a new Windows project, and name it AutoGenerateCommands. Add a data grid control, and set its Name property to dgDataGrid. Add a button with the name btnRetrieve, and set its Text property to &Retrieve Data. Add a second button with the name btnUpdate, and set its Text property to &Update Source. Your familiar-looking form is shown in Figure 11-20.

Figure 11-20

The beginnings of our hand-coded program.

Add the following form-level variable declarations.

Dim sqlDataAdapter1 As SqlClient.SqlDataAdapter Dim dsDataSet As New DataSet()

Now add the following code to the btnRetrieve button's Click event handler. This code will do the heavy lifting of creating the connection to the database.

Private Sub btnRetrieve_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnRetrieve.Click Dim sConnString = "Initial Catalog=Northwind;" & _ "Data Source=localhost;" & _ "Integrated Security=SSPI;" & _ "persist security info=False" Dim sqlConn As New SqlClient.SqlConnection(sConnString) sqlDataAdapter1 = _ New SqlClient.SqlDataAdapter( _ "Select * from Categories", sqlConn) Dim sqlCmdBuilder As SqlClient.SqlCommandBuilder sqlCmdBuilder = _ New SqlClient.SqlCommandBuilder(sqlDataAdapter1) sqlDataAdapter1.Fill(dsDataSet, "Categories") With dgDataGrid .CaptionText = "Auto Command Generation" .AllowSorting = True .AlternatingBackColor = System.Drawing.Color.Bisque .SetDataBinding(dsDataSet, "Categories") End WithEnd Sub

Add the following code to the btnUpdate button's Click event handler:

Private Sub btnUpdate_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnSave.Click Try sqlDataAdapter1.Update(dsDataSet, "Categories") Catch ex As Exception MessageBox.Show("Error: " & ex.ToString) End TryEnd Sub

Run the program, and the data will be displayed as you'd expect. Again, this method of data retrieval can display only a single, keyed table, so it really makes sense to use the wizard to put in all of the commands required for a more robust program.