This documentation is archived and is not being maintained.

Walkthrough: Creating a Simple Data Application by Using ADO.NET

When you create an application that manipulates data in a database, you perform basic tasks such defining connection strings, inserting data, and running stored procedures. By following this topic, you can discover how to interact with a database from within a simple Windows Forms application by using Visual C# or Visual Basic and ADO.NET.

Important noteImportant

To keep the code simple, it doesn’t include production-ready exception handling.

In this topic

To create the application, you'll need:

  • Visual Studio 2012 with Update 1 or

  • SQL Server 2012 Express LocalDB

  • The small sample database that you create by following the steps in Walkthrough: Creating a Small Sample Database.

  • The connection string for the database after you set it up. You can find this value by opening SQL Server Object Explorer or Server Explorer, choosing the database, opening its Properties window for the database, and then choosing the Debug tab.

This topic assumes that you're familiar with the basic functionality of the Visual Studio IDE and can create a Windows Forms application, add forms to that project, put buttons and other controls on those forms, set properties of those controls, and code simple events. If you aren't comfortable with these tasks, we suggest that you complete the Getting Started Tutorials before you start this topic.

The sample database for this walkthrough consists of the Customer and Orders tables. The tables contain no data initially, but you’ll add data when you run the application that you'll create. The database also has five simple stored procedures. Walkthrough: Creating a Small Sample Database contains a Transact-SQL script that creates the tables, the primary and foreign keys, the constraints, and the stored procedures.

  1. Create a project for a Windows Forms application, and then name it SimpleDataApp.

    Visual Studio creates the project and several files, including an empty Windows form that's named Form1.

  2. Add two Windows forms to your project so that it has three forms, and then give them the following names.

    • Navigation

    • NewCustomer

    • FillOrCancel

  3. For each form, add the text boxes, buttons, and other controls that appear in the following illustrations. For each control, set the properties that the tables describe.

    NoteNote

    The group box and the label controls add clarity but aren't used in the code.

Navigation form

Navigation dialog box

Controls for the Navigation form

Properties

Button

Name = btnGoToAdd

Button

Name = btnGoToFillOrCancel

Button

Name = btnExit

NewCustomer form

Add  a new customer and place an order

Controls for the NewCustomer form

Properties

TextBox

Name = txtCustomerName

TextBox

Name = txtCustomerID

Readonly = True

Button

Name = btnCreateAccount

NumericUpdown

DecimalPlaces = 0

Maximum = 5000

Name = numOrderAmount

DateTimePicker

Format = Short

Name = dtpOrderDate

Button

Name = btnPlaceOrder

Button

Name = btnAddAnotherAccount

Button

Name = btnAddFinish

FillOrCancel form

fill or cancel orders

Controls for the FillOrCancel form

Properties

TextBox

Name = txtOrderID

Button

Name = btnFindByOrderID

DateTimePicker

Format = Short

Name = dtpFillDate

DataGridView

Name = dgvCustomerOrders

Readonly = True

RowHeaderVisible = False

Button

Name = btnCancelOrder

Button

Name = btnFillOrder

Button

Name = btnFinishUpdates

When your application tries to open a connection to the database, your application must have access to the connection string. To avoid entering the string manually on each form, store the string in the App config file in your project, and create a method that returns the string when called from any form in your application.

  1. Open the shortcut menu for the project, and then choose Properties.

  2. In the left panel of the Properties window, choose the Project Settings tab.

  3. In the Name column, enter connString.

  4. In the Type list, choose (Connection String).

  5. In the Scope list, choose Application.

  6. In the Value column, enter your connection string, and then save your changes.

  1. On the menu bar, choose Project, Add Reference to add a reference to the System.Configuration.dll.

  2. On the menu bar, choose Project, Add Class to add a class file to your project, and then name the file Utility.

    Visual Studio creates the file and displays it in Solution Explorer.

  3. Open the Utility file, and then enter the following code. Notice the numbered comments (prefixed with Util-) that identify sections of the code. The table that follows the code calls out key points.

    No code example is currently available or this language may not be supported.

    Comment

    Description

    Util-1

    Add the System.Configuration namespace.

    Util-2

    Define a variable, returnValue, and initialize it to null (C#) or Nothing (Visual Basic).

    Util-3

    Even though you entered connString as the name of the connection string in the Properties window, you must specify "SimpleDataApp.Properties.Settings.connString" (C#) or "SimpleDataApp.My.MySettings.connString" (Visual Basic) in the code.

This section contains brief overviews of what each form does and shows the code that creates the forms. Numbered comments identify sections of the code.

The Navigation form opens when you run the application. The Add an account button opens the NewCustomer form. The Fill or cancel orders button opens the FillOrCancel form. The Exit button closes the application.

If you're using C#, in Solution Explorer, open Program.cs, and then change the Application.Run line to this: Application.Run(new Navigation());

If you're using Visual Basic, in Solution Explorer, open the Properties window, choose the Application tab, and then choose SimpleDataApp.Navigation on the Startup form list.

Create empty Click event handlers for the three buttons on the form.

Add this code to the Navigation form.

No code example is currently available or this language may not be supported.

When you enter a customer name and then choose the Create Account button, the NewCustomer form creates a customer account, and SQL Server returns an IDENTITY value as the new account number. You then place an order for the new account by specifying an amount and an order date and choosing the Place Order button.

Create an empty Click event handler for each button on the form.

Add the following code to the NewCustomer form. Step through each code block by using the numbered comments and the table after the code.

No code example is currently available or this language may not be supported.

Comment

Description

NC-1

Add System.Data.SqlClient and System.Configuration to the list of namespaces.

NC-2

Declare the parsedCustomerID and orderID variables, which you'll use later.

NC-3

Call the GetConnectionString method to get the connection string from the App config file, and store the value in the connstr string variable.

NC-4

Add code to the Click event handler for the btnCreateAccount button.

NC-5

Wrap the call to isCustomerName around the Click event code so that uspNewCustomer runs only if a customer name is present.

NC-6

Create a SqlConnection object (conn), and pass in the connection string in connstr.

NC-7

Create a SqlCommand object, cmdNewCustomer.

  • Specify Sales.uspNewCustomer as the stored procedure to run.

  • Use the CommandType property to specify that the command is a stored procedure.

NC-8

Add the @CustomerName input parameter from the stored procedure.

  • Add the parameter to the Parameters collection.

  • Use the SqlDbType enumeration to specify the parameter type as nvarchar(40).

  • Specify txtCustomerName.Text as the source.

NC-9

Add the output parameter from the stored procedure.

  • Add the parameter to the Parameters collection.

  • Use ParameterDirection.Output to identify the parameter as output.

NC-10

Add a Try – Catch – Finally block to open the connection, run the stored procedure, handle exceptions, and then close the connection.

NC-11

Open the connection (conn) that you created at NC-6.

NC-12

Use cmdNewCustomer's ExecuteNonQuery method to run the Sales.uspNewCustomer stored procedure, which runs an INSERT statement, not a query.

NC-13

The @CustomerID value is returned as an IDENTITY value from the database. Because it's an integer, you'll have to convert it to a string to display it in the Customer ID text box.

  • You declared parsedCustomerID at NC-2.

  • Store the @CustomerID value in parsedCustomerID for later use.

  • Convert the returned customer ID to a string, and insert it into txtCustomerID.Text.

NC-14

For this sample, add a simple, not production-quality catch clause.

NC-15

Always close a connection after you finish using it, so that it can be released to the connection pool. See SQL Server Connection Pooling (ADO.NET).

NC-16

Define a method to verify that a customer name is present.

  • If the text box is empty, display a message, and return false, because a name is required to create the account.

  • If the text box isn't empty, return true.

NC-17

Add code to the Click event handler for the btnPlaceOrder button.

NC-18

Wrap the call to isPlaceOrderReady around the btnPlaceOrder_Click event code so that uspPlaceNewOrder doesn't run if required input isn't present.

NC-19 through NC-25

These sections of code resemble the code that you added for the btnCreateAccount_Click event handler.

  • NC-19. Create the SqlCommand object, cmdNewOrder, and specify Sales.uspPlaceOrder as the stored procedure.

  • NC-20 through NC-23 are the input parameters for the stored procedure.

  • NC-24. @RC will contain a return value that's the generated order ID from the database. This parameter's direction is specified as ReturnValue.

  • NC-25. Store the value of order ID in the orderID variable that you declared at NC-2, and display the value in a message box.

NC-26

Define a method to verify that a customer ID exists and that an amount has been specified in numOrderAmount.

NC-27

Call the ClearForm method in the btnAddAnotherAccount Click event handler.

NC-28

Create the ClearForm method to clear values from the form if you want to add another customer.

NC29

Close the NewCustomer form, and return focus to the Navigation form.

The FillorCancel form runs a query to return an order when you enter an order ID and choose the Find Order button. The returned row appears in a read-only data grid. You can mark the order as cancelled (X) if you choose the Cancel Order button, or you can mark the order as filled (F) if you choose the Fill Order button. If you choose the Find Order button again, the updated row appears.

Create empty Click event handlers for the four buttons on the form.

Add the following code to the FillOrCancel form. Step through the code blocks by using the numbered comments and the table that follows the code.

No code example is currently available or this language may not be supported.

Comment

Description

FC-1

Add System.Data.SqlClient, System.Configuration, and System.Text.RegularExpressions to the list of namespaces.

FC-2

Declare the parsedOrderID variable.

FC-3

Call the GetConnectionString method to get the connection string from the App config file, and store the value in the connstr string variable.

FC-4

Add code to the Click event handler for btnFindOrderByID.

FC-5

Look familiar? These tasks are required before you try to run an SQL statement or a stored procedure.

  • Create a SqlConnection object.

  • Define the SQL statement or specify the name of the stored procedure. (In this case, you'll run a SELECT statement.)

  • Create a SqlCommand object.

  • Define any parameters for the SQL statement or stored procedure.

FC-6

This code uses SqlDataReader and DataTable to retrieve and display the query result.

  • Open the connection.

  • Create a SqlDataReader, rdr, by running cmdOrderID's ExecuteReader method.

  • Create a DataTable object to hold the retrieved data.

  • Load the data from the SqlDataReader into the DataTable object.

  • Display the data in the datagridview by specifying the DataTable as the DataSource for the datagridview.

  • Close the SqlDataReader.

FC-7

Add code to the Click event handler for btnCancelOrder. This code runs the Sales.uspCancelOrder stored procedure.

FC-8

Add code to the Click event handler for btnFillOrder. This code runs the Sales.uspFillOrder stored procedure.

FC-9

Create a method to verify that the OrderID is ready to submit as a parameter to the SqlCommand object.

  • Make sure that an ID has been entered in txtOrderID.

  • Use Regex.IsMatch to define a simple check for non-integer characters.

  • You declared the parsedOrderID variable at FC-2.

  • If the input is valid, convert the text to an integer, and store the value in the parsedOrderID variable.

  • Wrap the isOrderID method around the btnFindByOrderID, btnCancelOrder, and btnFillOrder Click event handlers.

Choose the F5 key to build and test your application after you code each Click event handler and then after you finish coding.

Show: