Filling Datasets with Data

The typical Visual Studio mechanism for executing Transact-SQL queries and for filling datasets is the TableAdapter.

You can execute SQL statements or stored procedures against a data source using TableAdapters or command objects (for example, SqlCommand). To load data into datasets created using design tools in Visual Studio, use TableAdapters. To load data into datasets created programmatically, use data adapters. If your application does not use datasets, use command objects to execute SQL statements or stored procedures directly against a database.

The following topics provide details for filling Datasets with data in Visual Studio:

Topic	Description
How to: Fill a Dataset with Data	Provides details for loading data into datasets using TableAdapters and DataAdapters.
How to: Create and Execute an SQL Statement that Returns Rows	Provides details for creating and executing SQL statements that return rows using TableAdapter queries and Command objects.
How to: Create and Execute an SQL Statement that Returns a Single Value	Provides details for creating and executing SQL statements that return single values using TableAdapter queries and Command objects.
How to: Create and Execute an SQL Statement that Returns No Value	Provides details for creating and executing SQL statements that return no value using TableAdapter queries and Command objects.
How to: Execute a Stored Procedure that Returns Rows	Provides details for executing stored procedures that return rows using TableAdapter queries and Command objects.
How to: Execute a Stored Procedure that Returns a Single Value	Provides details for executing stored procedures that return single values using TableAdapter queries and Command objects.
How to: Execute a Stored Procedure that Returns No Value	Provides details for executing stored procedures that return no value using TableAdapter queries and Command objects.
How to: Set and Get Parameters for Command Objects	Provides details for assigning values to parameters in queries and stored procedures, and reading values in parameters returned from executed commands.
Walkthrough: Filling a Dataset with Data	Provides details for creating a dataset and populating it with data from a database.
Walkthrough: Reading XML Data into a Dataset	Provides details for creating a Windows application that loads XML data into a dataset and then displays the dataset in a DataGridView control.

Filling Datasets

If you create a dataset with a Visual Studio design-time tool (such as the Dataset Designer or the Data Source Configuration Wizard), then you use a TableAdapter to fill it. TableAdapters execute your SQL statements or stored procedures.

If you create a dataset without design-time tools, then you must use data adapters to fill and update data. (TableAdapters are not actual classes in the .NET Framework 4, so they are not suitable for working with datasets that have been created without the use of design-time tools. For more information on loading data into datasets with either TableAdapters or data adapters, see How to: Fill a Dataset with Data.

TableAdapter Queries

You can execute TableAdapter queries to fill data in datasets (more specifically, to load data into the DataTables that make up a dataset). You can create TableAdapter queries using the TableAdapter Query Configuration Wizard in the Dataset Designer. TableAdapter queries appear as named methods on a TableAdapter and are executed by calling the TableAdapter method. For more information on creating and executing TableAdapter queries, see the following pages:

• How to: Create and Execute an SQL Statement that Returns Rows

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

• How to: Create and Execute an SQL Statement that Returns No Value

• How to: Execute a Stored Procedure that Returns Rows

• How to: Execute a Stored Procedure that Returns a Single Value

• How to: Execute a Stored Procedure that Returns No Value

Command Objects

Command objects give you the ability to execute SQL statements and stored procedures directly against a database, without needing a DataSet, TableAdapter, or DataAdapter. (The term 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.)

You configure commands to query data using SQL statements or stored procedures by setting the data command's CommandType property to one of the values in the CommandType enumeration. Set the CommandType to Text for executing SQL statements, or set it to StoredProcedure for executing stored procedures. Then set the CommandText property to either a SQL statement or the name of the stored procedure. You can then execute the data command by calling one of its execute methods (ExecuteReader, ExecuteScalar, ExecuteNonQuery).

Each of the .NET Framework Data Providers (ADO.NET) offers a command object optimized for specific databases.

By using data commands, you can do the following in your application:

• Execute Select commands that return a result you can read directly, rather than loading it into the dataset. To read the results, use a data reader (OleDbDataReader, SqlDataReader, OdbcDataReader, or OracleDataReader object), which works like a read-only, forward-only cursor that you can bind controls to. This is a useful strategy for reducing memory usage and loading read-only data very quickly.

• Execute database definition (DDL) commands to create, edit, and remove tables, stored procedures, and other database structures. (You must have permissions to perform these actions, of course.)

• Execute commands to get database catalog information.

• Execute dynamic SQL commands to update, insert, or delete records — rather than updating dataset tables and then copying changes to the database.

• Execute commands that return a scalar value (that is, a single value), such as the results of an aggregate function (SUM, COUNT, AVG, and so on).

• Execute commands that return data from a SQL Server database (version 7.0 or later) in XML format. A typical use is to execute a query and get back data in XML format, apply an XSLT transform to it (to convert the data to HTML), and then send the results to a browser.

A command's properties contain all the information necessary to execute a command against a database. This includes:

• A connection   The command references a connection that it uses to communicate with the database.

• The name or text of a command   The command includes the actual text of an SQL statement or the name of a stored procedure to execute.

• Parameters   A command might require you to pass parameter values along with it (input parameters). The command might also return values in the form of a return value or output parameter values. Each command has a collection of parameters that you can set or read individually to pass or receive values. For more information, see How to: Set and Get Parameters for Command Objects.

You execute a command using a method appropriate to the results you expect to get back. For example, if you expect rows, you call the command's ExecuteReader method, which returns records in a data reader. If you are performing an UPDATE, INSERT, or DELETE command, you call the command's ExecuteNonQuery method, which returns a value indicating the number of rows affected. If you are performing an aggregate function, such as returning the count of orders for a customer, you call the ExecuteScalar method.

Multiple Result Sets

A typical use of a command object is to return a single table of data (a set of rows). However, commands can execute procedures that return multiple result sets. This can happen in different ways. One way is that the command references a stored procedure that returns multiple result sets. Alternatively, the command can contain two (or more) statements or stored procedure names. In that case, the statements or procedures are run sequentially and return multiple result sets with a single call.

If you specify multiple statements or procedures for a command, they must all be of the same type. For example, you can run successive SQL statements or successive stored procedures. However, you cannot mix stored procedure calls and SQL statements in the same command. For more information, see Retrieving Data Using a DataReader (ADO.NET).

Note

For Oracle, the .NET Framework Data Provider for Oracle does not support batched SQL statements. However, it does allow you to use multiple REF CURSOR output parameters to fill a dataset, each in its own data table. You must define the parameters, mark them as output parameters, and indicate that they are REF CURSOR data types. Note that you will be unable to use the Update method when the OracleDataAdapter object is filled from REF CURSOR parameters to a stored procedure, because Oracle does not provide the information necessary to determine what the table name and column names are when the SQL statement is executed.

Security

When using data commands with a CommandType property set to Text, carefully check information that is sent from a client before passing it to your database. Malicious users might try to send (inject) modified or additional SQL statements in an effort to gain unauthorized access or damage the database. Before you transfer user input to a database, you should always verify that the information is valid. A best practice is to always use parameterized queries or stored procedures when possible.

See Also

Concepts

Preparing Your Application to Receive Data

Fetching Data into Your Application

Binding Controls to Data in Visual Studio

Editing Data in Your Application

Validating Data

Saving Data

Other Resources

Overview of Data Applications in Visual Studio

Connecting to Data in Visual Studio

Tools for Working with Data Sources in Visual Studio