Filling Datasets and Querying Data Overview
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.
For more information on |
See |
|---|---|
Filling a dataset using a TableAdapter |
|
TableAdapters |
|
Filling a dataset with a data adapter |
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 3.5, 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 a Single 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
Other Resources
Getting Started with Data Access
Connecting to Data in Visual Studio
Preparing Your Application to Receive Data
Fetching Data into Your Application
Displaying Data on Forms in Windows Applications