Uredi

Dijelite putem

Create a basic data application by using ADO.NET

  • Članak

Note

Datasets and related classes are legacy .NET Framework technologies from the early 2000s that enable applications to work with data in memory while the applications are disconnected from the database. The technologies are especially useful for applications that enable users to modify data and persist the changes back to the database. Although datasets have proven to be a very successful technology, we recommend that new .NET applications use Entity Framework Core. Entity Framework provides a more natural way to work with tabular data as object models, and it has a simpler programming interface.

When you create an application that manipulates data in a database, you typically perform tasks such as defining connection strings, inserting data, and running stored procedures. As you follow this article, you can discover how to interact with a database from within a forms over data (FOD) Windows Forms application by using Visual C# or Visual Basic and ADO.NET. All .NET data technologies, including datasets, LINQ (Language-Integrated Query) to SQL, and Entity Framework, ultimately perform steps that are similar to those steps shown in this article.

This article demonstrates how to quickly get data out of a database. If your application needs to modify data in nontrivial ways and update the database, consider using Entity Framework and data binding. Doing so allows your user interface controls to automatically sync to changes in the underlying data.

To access the complete code for this tutorial, see the Visual Studio docs GitHub repo for C# and Visual Basic.

Important

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

Prerequisites

  • Visual Studio with the .NET desktop development and Data storage and processing workloads installed. To install them, open Visual Studio Installer and choose Modify next to the version of Visual Studio you want to modify.

  • SQL Server Express LocalDB. If you don't have SQL Server Express LocalDB, you can install it from the SQL Server download page.

This article assumes that you're familiar with the basic functionality of the Visual Studio IDE. It also assumes that you can create a Windows Forms application, add forms to a project, add buttons and other controls to the forms, set control properties, and code simple events. If you aren't comfortable with these tasks, complete the Create a Windows Forms app in Visual Studio with C# tutorial or the Create a Windows Forms app in Visual Studio with Visual Basic tutorial before you start this walkthrough.

Set up the sample database

Create the sample database by following these steps:

  1. In Visual Studio, open the Server Explorer window.

  2. Right-click Data Connections and choose Create New SQL Server Database.

  3. For Server name, enter (localdb)\mssqllocaldb.

  4. For New database name, enter Sales, and then choose OK.

    Visual Studio creates an empty Sales database under the Data Connections node in Server Explorer.

  5. Right-click the Sales data connection and select New Query.

    A query editor window opens.

  6. Copy the Sales Transact-SQL script to your clipboard.

  7. Paste the T-SQL script into the query editor window, and then select Execute.

    After a short time, the query finishes running and the database objects are created. The database contains two tables: Customer and Orders. These tables contain no data initially, but you can add data when you run the application that you create. The database also contains five basic stored procedures.

Create the forms and add controls

  1. Create a C# or Visual Basic project with the Windows Forms App (.NET Framework) template and name it SimpleDataApp.

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

  2. In Solution Explorer, add two Windows forms to your project so that it has a total of three forms, and give them the following names:

    • Navigation

    • NewCustomer

    • FillOrCancel

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

    Note

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

    Navigation form

    Screenshot that shows the Navigation form details.

    Controls for the Navigation form

    Control text Control type Control properties
    Add an account Button Name = btnGoToAdd
    Fill or cancel an order Button Name = btnGoToFillOrCancel
    Exit Button Name = btnExit

    NewCustomer form

    Screenshot that shows the NewCustomer form details.

    Controls for the NewCustomer form

    Label/Control text Control type Control properties
    Customer name TextBox Name = txtCustomerName
    Customer ID TextBox Name = txtCustomerID
    ReadOnly = True
    Create account Button Name = btnCreateAccount
    Order amount NumericUpdown Name = numOrderAmount
    DecimalPlaces = 0
    Maximum = 5000
    Order date DateTimePicker Name = dtpOrderDate
    Format = Short
    Place order Button Name = btnPlaceOrder
    Finish Button Name = btnAddFinish
    Add another account Button Name = btnAddAnotherAccount

    FillOrCancel form

    Screenshot that shows the FillOrCancel form details.

    Controls for the FillOrCancel form

    Label/Control text Control type Control properties
    Order ID TextBox Name = txtOrderID
    Find order Button Name = btnFindByOrderID
    If filling an order... DateTimePicker Name = dtpFillDate
    Format = Short
    (None) DataGridView Name = dgvCustomerOrders
    ReadOnly = True
    RowHeadersVisible = False
    Cancel order Button Name = btnCancelOrder
    Fill order Button Name = btnFillOrder
    Finish Button Name = btnFinishUpdates

Store the connection string

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

To find the connection string:

  1. In Server Explorer, right-click the Sales data connection, and then choose Properties.

  2. Locate the Connection String property and copy its string value to the clipboard.

To store the connection string in your project:

  1. In Solution Explorer, do one of the following steps, depending on your project type:

    • For a C# project, expand the Properties node under the project, and then open the Settings.settings file.

    • For a Visual Basic project, select Show All Files, expand the My Project node, and then open the Settings.settings file.

  2. In the Name column, enter connString.

  3. In the Type list, select (Connection string).

  4. In the Scope list, select Application.

  5. In the Value column, enter your connection string (without any outside quotes), and then save your changes.

    Screenshot that shows the Connection String data in Settings.settings.

Caution

In a real application, you should store the connection string securely, as described in Connection strings and configuration files. For best security, use an authentication method that doesn't rely on storing a password in the connection string. For example, Windows Authentication for an on-premises SQL Server database. For more information, see Save and edit connection strings.

Write the code for the forms

This section contains brief overviews of what each form does. It also provides the code that defines the underlying logic when you select a button on the form.

The Navigation form opens when you run the application and includes the following buttons:

  • Add an account: Opens the NewCustomer form.

  • Fill or cancel orders: Opens the FillOrCancel form.

  • Exit: Closes the application.

Make the Navigation form the startup form

For C# projects:

  1. In Solution Explorer, open Program.cs.

  2. Change the Application.Run line to: Application.Run(new Navigation());

For Visual Basic projects:

  1. In Solution Explorer, right-click the project and choose Properties.

  2. In the Project Designer, select the Application tab, and then select Navigation in the Startup object list.

Create autogenerated event handlers for the Navigation form

To create empty event handler methods, double-click each of the three buttons on the Navigation form. Double-clicking a button adds autogenerated code in the Designer code file, which enables a button selection to raise an event.

If you decide to copy and paste code directly into your code files instead of using the double-click action in the designer, ensure you set the event handler to the right method:

  1. In the Properties window of the code file for the form, switch to the Events tab by using the lightning bolt toolbar button.

  2. Search for the Click property and verify its value is the correct event handler method.

Add code for the Navigation form logic

In the code page for the Navigation form, complete the method bodies for the three button-select event handlers as shown in the following code.

/// <summary>
/// Opens the NewCustomer form as a dialog box,
/// which returns focus to the calling form when it is closed. 
/// </summary>
private void btnGoToAdd_Click(object sender, EventArgs e)
{
    Form frm = new NewCustomer();
    frm.Show();
}

/// <summary>
/// Opens the FillorCancel form as a dialog box. 
/// </summary>
private void btnGoToFillOrCancel_Click(object sender, EventArgs e)
{
    Form frm = new FillOrCancel();
    frm.ShowDialog();
}

/// <summary>
/// Closes the application (not just the Navigation form).
/// </summary>
private void btnExit_Click(object sender, EventArgs e)
{
    this.Close();
}

NewCustomer form

When you enter a customer name and then select the Create account button, the NewCustomer form creates a customer account, and SQL Server returns an IDENTITY value as the new customer ID. You can then place an order for the new account by specifying an amount and an order date, and selecting the Place order button.

Create autogenerated event handlers for the NewCustomer form

Create an empty Click event handler for each button on the NewCustomer form by double-clicking each of the four buttons. Double-clicking the buttons also adds autogenerated code in the Designer code file that enables a button select to raise an event.

Add code for the NewCustomer form logic

To complete the NewCustomer form logic, follow these steps:

  1. Bring the System.Data.SqlClient namespace into scope so that you don't need to fully qualify the names of its members.

    using System.Data.SqlClient;

  1. Add some variables and helper methods to the class.

    // Storage for IDENTITY values returned from database.
private int parsedCustomerID;
private int orderID;

/// <summary>
/// Verifies that the customer name text box is not empty.
/// </summary>
private bool IsCustomerNameValid()
{
    if (txtCustomerName.Text == "")
    {
        MessageBox.Show("Please enter a name.");
        return false;
    }
    else
    {
        return true;
    }
}

/// <summary>
/// Verifies that a customer ID and order amount have been provided.
/// </summary>
private bool IsOrderDataValid()
{
    // Verify that CustomerID is present.
    if (txtCustomerID.Text == "")
    {
        MessageBox.Show("Please create customer account before placing order.");
        return false;
    }
    // Verify that Amount isn't 0.
    else if ((numOrderAmount.Value < 1))
    {
        MessageBox.Show("Please specify an order amount.");
        return false;
    }
    else
    {
        // Order can be submitted.
        return true;
    }
}

/// <summary>
/// Clears the form data.
/// </summary>
private void ClearForm()
{
    txtCustomerName.Clear();
    txtCustomerID.Clear();
    dtpOrderDate.Value = DateTime.Now;
    numOrderAmount.Value = 0;
    this.parsedCustomerID = 0;
}

  1. Complete the method bodies for the four button-select event handlers.

    /// <summary>
/// Creates a new customer by calling the Sales.uspNewCustomer stored procedure.
/// </summary>
private void btnCreateAccount_Click(object sender, EventArgs e)
{
    if (IsCustomerNameValid())
    {
        // Create the connection.
        using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.connString))
        {
            // Create a SqlCommand, and identify it as a stored procedure.
            using (SqlCommand sqlCommand = new SqlCommand("Sales.uspNewCustomer", connection))
            {
                sqlCommand.CommandType = CommandType.StoredProcedure;

                // Add input parameter for the stored procedure and specify what to use as its value.
                sqlCommand.Parameters.Add(new SqlParameter("@CustomerName", SqlDbType.NVarChar, 40));
                sqlCommand.Parameters["@CustomerName"].Value = txtCustomerName.Text;

                // Add the output parameter.
                sqlCommand.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.Int));
                sqlCommand.Parameters["@CustomerID"].Direction = ParameterDirection.Output;

                try
                {
                    connection.Open();

                    // Run the stored procedure.
                    sqlCommand.ExecuteNonQuery();

                    // Customer ID is an IDENTITY value from the database.
                    this.parsedCustomerID = (int)sqlCommand.Parameters["@CustomerID"].Value;

                    // Put the Customer ID value into the read-only text box.
                    this.txtCustomerID.Text = Convert.ToString(parsedCustomerID);
                }
                catch
                {
                    MessageBox.Show("Customer ID was not returned. Account could not be created.");
                }
                finally
                {
                    connection.Close();
                }
            }
        }
    }
}

/// <summary>
/// Calls the Sales.uspPlaceNewOrder stored procedure to place an order.
/// </summary>
private void btnPlaceOrder_Click(object sender, EventArgs e)
{
    // Ensure the required input is present.
    if (IsOrderDataValid())
    {
        // Create the connection.
        using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.connString))
        {
            // Create SqlCommand and identify it as a stored procedure.
            using (SqlCommand sqlCommand = new SqlCommand("Sales.uspPlaceNewOrder", connection))
            {
                sqlCommand.CommandType = CommandType.StoredProcedure;

                // Add the @CustomerID input parameter, which was obtained from uspNewCustomer.
                sqlCommand.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.Int));
                sqlCommand.Parameters["@CustomerID"].Value = this.parsedCustomerID;

                // Add the @OrderDate input parameter.
                sqlCommand.Parameters.Add(new SqlParameter("@OrderDate", SqlDbType.DateTime, 8));
                sqlCommand.Parameters["@OrderDate"].Value = dtpOrderDate.Value;

                // Add the @Amount order amount input parameter.
                sqlCommand.Parameters.Add(new SqlParameter("@Amount", SqlDbType.Int));
                sqlCommand.Parameters["@Amount"].Value = numOrderAmount.Value;

                // Add the @Status order status input parameter.
                // For a new order, the status is always O (open).
                sqlCommand.Parameters.Add(new SqlParameter("@Status", SqlDbType.Char, 1));
                sqlCommand.Parameters["@Status"].Value = "O";

                // Add the return value for the stored procedure, which is  the order ID.
                sqlCommand.Parameters.Add(new SqlParameter("@RC", SqlDbType.Int));
                sqlCommand.Parameters["@RC"].Direction = ParameterDirection.ReturnValue;

                try
                {
                    //Open connection.
                    connection.Open();

                    // Run the stored procedure.
                    sqlCommand.ExecuteNonQuery();

                    // Display the order number.
                    this.orderID = (int)sqlCommand.Parameters["@RC"].Value;
                    MessageBox.Show("Order number " + this.orderID + " has been submitted.");
                }
                catch
                {
                    MessageBox.Show("Order could not be placed.");
                }
                finally
                {
                    connection.Close();
                }
            }
        }
    }
}

/// <summary>
/// Clears the form data so another new account can be created.
/// </summary>
private void btnAddAnotherAccount_Click(object sender, EventArgs e)
{
    this.ClearForm();
}

/// <summary>
/// Closes the form/dialog box.
/// </summary>
private void btnAddFinish_Click(object sender, EventArgs e)
{
    this.Close();
}

FillOrCancel form

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

Create autogenerated event handlers for the FillOrCancel form

Create empty Click event handlers for the four buttons on the FillOrCancel form by double-clicking the buttons. Double-clicking the buttons also adds autogenerated code in the Designer code file that enables a button select to raise an event.

Add code for the FillOrCancel form logic

To complete the FillOrCancel form logic, follow these steps.

  1. Bring the following two namespaces into scope so that you don't have to fully qualify the names of their members.

    using System.Data.SqlClient;
using System.Text.RegularExpressions;

  1. Add a variable and helper method to the class.

    // Storage for the order ID value.
private int parsedOrderID;

/// <summary>
/// Verifies that an order ID is present and contains valid characters.
/// </summary>
private bool IsOrderIDValid()
{
    // Check for input in the Order ID text box.
    if (txtOrderID.Text == "")
    {
        MessageBox.Show("Please specify the Order ID.");
        return false;
    }

    // Check for characters other than integers.
    else if (Regex.IsMatch(txtOrderID.Text, @"^\D*$"))
    {
        // Show message and clear input.
        MessageBox.Show("Customer ID must contain only numbers.");
        txtOrderID.Clear();
        return false;
    }
    else
    {
        // Convert the text in the text box to an integer to send to the database.
        parsedOrderID = Int32.Parse(txtOrderID.Text);
        return true;
    }
}

  1. Complete the method bodies for the four button-select event handlers.

    /// <summary>
/// Executes a t-SQL SELECT statement to obtain order data for a specified
/// order ID, then displays it in the DataGridView on the form.
/// </summary>
private void btnFindByOrderID_Click(object sender, EventArgs e)
{
    if (IsOrderIDValid())
    {
        using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.connString))
        {
            // Define a t-SQL query string that has a parameter for orderID.
            const string sql = "SELECT * FROM Sales.Orders WHERE orderID = @orderID";

            // Create a SqlCommand object.
            using (SqlCommand sqlCommand = new SqlCommand(sql, connection))
            {
                // Define the @orderID parameter and set its value.
                sqlCommand.Parameters.Add(new SqlParameter("@orderID", SqlDbType.Int));
                sqlCommand.Parameters["@orderID"].Value = parsedOrderID;

                try
                {
                    connection.Open();

                    // Run the query by calling ExecuteReader().
                    using (SqlDataReader dataReader = sqlCommand.ExecuteReader())
                    {
                        // Create a data table to hold the retrieved data.
                        DataTable dataTable = new DataTable();

                        // Load the data from SqlDataReader into the data table.
                        dataTable.Load(dataReader);

                        // Display the data from the data table in the data grid view.
                        this.dgvCustomerOrders.DataSource = dataTable;

                        // Close the SqlDataReader.
                        dataReader.Close();
                    }
                }
                catch
                {
                    MessageBox.Show("The requested order could not be loaded into the form.");
                }
                finally
                {
                    // Close the connection.
                    connection.Close();
                }
            }
        }
    }
}

/// <summary>
/// Cancels an order by calling the Sales.uspCancelOrder
/// stored procedure on the database.
/// </summary>
private void btnCancelOrder_Click(object sender, EventArgs e)
{
    if (IsOrderIDValid())
    {
        // Create the connection.
        using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.connString))
        {
            // Create the SqlCommand object and identify it as a stored procedure.
            using (SqlCommand sqlCommand = new SqlCommand("Sales.uspCancelOrder", connection))
            {
                sqlCommand.CommandType = CommandType.StoredProcedure;

                // Add the order ID input parameter for the stored procedure.
                sqlCommand.Parameters.Add(new SqlParameter("@orderID", SqlDbType.Int));
                sqlCommand.Parameters["@orderID"].Value = parsedOrderID;

                try
                {
                    // Open the connection.
                    connection.Open();

                    // Run the command to execute the stored procedure.
                    sqlCommand.ExecuteNonQuery();
                }
                catch
                {
                    MessageBox.Show("The cancel operation was not completed.");
                }
                finally
                {
                    // Close connection.
                    connection.Close();
                }
            }
        }
    }
}

/// <summary>
/// Fills an order by calling the Sales.uspFillOrder stored
/// procedure on the database.
/// </summary>
private void btnFillOrder_Click(object sender, EventArgs e)
{
    if (IsOrderIDValid())
    {
        // Create the connection.
        using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.connString))
        {
            // Create command and identify it as a stored procedure.
            using (SqlCommand sqlCommand = new SqlCommand("Sales.uspFillOrder", connection))
            {
                sqlCommand.CommandType = CommandType.StoredProcedure;

                // Add the order ID input parameter for the stored procedure.
                sqlCommand.Parameters.Add(new SqlParameter("@orderID", SqlDbType.Int));
                sqlCommand.Parameters["@orderID"].Value = parsedOrderID;

                // Add the filled date input parameter for the stored procedure.
                sqlCommand.Parameters.Add(new SqlParameter("@FilledDate", SqlDbType.DateTime, 8));
                sqlCommand.Parameters["@FilledDate"].Value = dtpFillDate.Value;

                try
                {
                    connection.Open();

                    // Execute the stored procedure.
                    sqlCommand.ExecuteNonQuery();
                }
                catch
                {
                    MessageBox.Show("The fill operation was not completed.");
                }
                finally
                {
                    // Close the connection.
                    connection.Close();
                }
            }
        }
    }
}

/// <summary>
/// Closes the form.
/// </summary>
private void btnFinishUpdates_Click(object sender, EventArgs e)
{
    this.Close();
}

Test your application

Run the application and try creating a few customers and orders to verify that everything is working as expected.

To verify that the database is updated with your changes:

  1. Open the Tables node in Server Explorer.

  2. Right-click the Customers and Orders nodes, and choose Show Table Data.

Related content