How to: Create and Execute an SQL Statement that Returns a Single Value

Article
02/04/2013

To execute an SQL statement that returns a single value, you can run a TableAdapter query that is configured to run an SQL statement (for example, CustomersTableAdapter.CustomerCount()).

If your application does not use TableAdapters, call the ExecuteScalar method on a command object, setting its CommandType property to Text. ("Command object" refers to the specific command for the .NET Framework Data Provider your application is using. For example, if your application is using the .NET Framework Data Provider for SQL Server, the command object would be SqlCommand.)

The following examples show how to execute SQL statements that return single values from a database using either TableAdapters or command objects. For more information on querying with TableAdapters and commands, see Filling Datasets with Data.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.

Executing SQL Statements that Return Single Values Using a TableAdapter

This example shows how to create a TableAdapter query using the TableAdapter Query Configuration Wizard, and then it provides information on how to declare an instance of the TableAdapter and execute the query.

To create an SQL statement returning a single value using a TableAdapter

Open a dataset in the Dataset Designer. For more information, see How to: Open a Dataset in the Dataset Designer.
If you do not already have one, create a TableAdapter. For more information on creating TableAdapters, see How to: Create TableAdapters.
If you already have a query on your TableAdapter that uses an SQL statement to return a single value, then skip to the next procedure, "To declare an instance of the TableAdapter and execute the query." Otherwise, continue with step 4 to create a new query that returns a single value.
Right-click the TableAdapter that you want, and use the shortcut menu to add a query.

The TableAdapter Query Configuration Wizard opens.
Leave the default value of Use SQL statements, and then click Next.
Choose the SELECT which returns a single value option, and then click Next.
Type your SQL statement, or use the Query Builder to assist with creating one, and then click Next.
Provide a name for the query.
Complete the wizard; the query is added to the TableAdapter.
Build your project.

To declare an instance of the TableAdapter and execute the query

Declare an instance of the TableAdapter that contains the query you want to execute.
- To create an instance using design-time tools, drag the TableAdapter that you want from the Toolbox. (Components in your project now appear in the Toolbox under a heading that matches your project name.) If the TableAdapter does not appear in the Toolbox, then you may need to build your project.
  
  -or-
- To create an instance in code, replace the following code with the names of your DataSet and TableAdapter.
  
  Dim tableAdapter As New DataSetTableAdapters.TableAdapter
  
  Note
  
  TableAdapters are not actually located inside their associated dataset classes. Each dataset has a corresponding collection of TableAdapters in its own namespace. For example, if you have a dataset named SalesDataSet, then there would be a SalesDataSetTableAdapters namespace that contains its TableAdapters.
Call your query as you would call any other method in code. Your query is a method on the TableAdapter. Replace the following code with the names of your TableAdapter and query. You also need to pass in any parameters required by your query and do something with the return value (for example, assign it to a variable). If you are not sure if your query requires parameters, or what parameters it requires, then check IntelliSense for the required signature of the query. Depending on whether your query takes parameters or not, the code would look similar to one of the following examples:

TableAdapter.Query()

TableAdapter.Query(Parameters)
You will likely need to assign the value returned by the query to a variable. TableAdapter queries that return a single value return a data type based on the query (as opposed to the ExecuteScalar method, which returns an object). For example, if your TableAdapter query selects a single column whose data type is an integer, then the return value of the query is an integer. If the column allows null values, the return value is one of the nullable types (for example, Nullable(Of Integer)). For more information on nullable types, see Nullable. The complete code to declare an instance of a TableAdapter and execute a query should look similar to the following (this example assumes the return value is an integer; adjust your code according to the data type returned by your query):
```
Dim tableAdapter As New NorthwindDataSetTableAdapters.CustomersTableAdapter()

Dim returnValue As Integer
returnValue = CType(tableAdapter.GetCustomerCount(), Integer)
```
```
NorthwindDataSetTableAdapters.CustomersTableAdapter tableAdapter = 
    new NorthwindDataSetTableAdapters.CustomersTableAdapter();

int returnValue = (int)tableAdapter.GetCustomerCount();
```

Executing SQL Statements that Return Single Values Using a Command Object

The following example shows how to create a command and execute an SQL statement that returns a single value. For information on setting and getting parameter values for a command, see How to: Set and Get Parameters for Command Objects.

This example uses the SqlCommand object and requires:

References to the System, System.Data, and System.Xml namespaces.
A data connection named SqlConnection1.
A table named Customers in the data source that SqlConnection1 connects to. (Otherwise, you need a valid SQL statement for your data source).

To execute an SQL statement returning a single value using a DataCommand

Add the following code to a method that you want to execute the code from. You return a single value by calling the ExecuteScalar method of a command (for example, ExecuteScalar). The data is returned in an Object.

Dim sqlConnection1 As New SqlConnection("Your Connection String")
Dim cmd As New SqlCommand
Dim returnValue As Object

cmd.CommandText = "SELECT COUNT(*) FROM Customers"
cmd.CommandType = CommandType.Text
cmd.Connection = sqlConnection1

sqlConnection1.Open()

returnValue = cmd.ExecuteScalar()

sqlConnection1.Close()

SqlConnection sqlConnection1 = new SqlConnection("Your Connection String");
SqlCommand cmd = new SqlCommand();
Object returnValue;

cmd.CommandText = "SELECT COUNT(*) FROM Customers";
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;

sqlConnection1.Open();

returnValue = cmd.ExecuteScalar();

sqlConnection1.Close();

Security

The application requires permission to access the database and execute the SQL statement.

How to: Create and Execute an SQL Statement that Returns a Single Value

Executing SQL Statements that Return Single Values Using a TableAdapter

To create an SQL statement returning a single value using a TableAdapter

To declare an instance of the TableAdapter and execute the query

Executing SQL Statements that Return Single Values Using a Command Object

To execute an SQL statement returning a single value using a DataCommand

Security

See Also

Tasks

Reference

Concepts

Additional resources