Visual Studio .NET provides many built-in wizards and designers to help you build your data access architecture rapidly and efficiently at design time. With a minimum of actual coding, you can implement robust data access for your application. The ADO.NET object model is fully available through code, however, to implement customized features or to fine tune your program.
Connecting to a Database
You can implement a connection to a database in your application in many different ways. The easiest way is to use the graphical tools provided by Visual Studio .NET to create a connection at design time.
Current data connections are managed in the Server Explorer window. This window is normally docked to the left pane of the Integrated Development Environment (IDE) and shares the same space as the Toolbox. If the Toolbox is currently visible, you can view the Server Explorer by clicking the Server Explorer tab located at the bottom of the Toolbox.
Data connections that are currently available to Visual Studio .NET are displayed as child nodes of the Data Connections node in Server Explorer. If you want to add a connection that is already listed under the Data Connections node to your project, all you need to do is drag the connection from the Server Explorer window to the designer.
A new Connection object of the appropriate type (Sql or OleDb) is created and automatically configured to connect to your database.
To add a connection from the Server Explorer
Drag the node that represents a database from the Server Explorer window to the designer.
You can also create a new connection in the Server Explorer. You can create a new connection by right-clicking the Data Connections node and choosing Add Connection. This launches the Data Link Properties dialog box.
The Data Link Properties dialog box is a visual way to configure your data connection. The Providers tab allows you to choose the database provider that you will use in your connection. The Connection tab allows you to configure the specific properties of your connections, and the Advanced tab configures properties not normally needed for every connection.
The All tab allows you to view and edit the current settings of all the properties. Once you have configured your new connection, you can click OK to close the Data Link Properties window. The new connection appears in the Server Explorer window and can be dragged to your designer.
To create a new connection in the Server Explorer
In the Server Explorer, right-click Data Connections and choose Add Connection to open the Data Link Properties window.
On the Provider tab, choose the appropriate provider for your data connection.
On the Connection tab, choose the database you want to connect to. Depending on the provider, you might also have to set additional properties such as the server name or password settings.
When you have entered the appropriate information, click the Test Connection button to verify that the connection is functional.
Drag the new connection from the Server Explorer to the designer surface. A new connection object of the appropriate type is created and correctly configured.
You can also create a database connection manually, either by dragging a Connection object from the Toolbox to the designer or by declaring and instantiating the object in your code.
If you create a connection this way, you will have to manually set the ConnectionString property.
As with other classes represented in Data Providers, there are two types of Command classes. The OleDbCommand class is designed to interact with a wide variety of database types. The SqlCommand class is designed to interact specifically with Microsoft SQL Server 7.0.
Because they only require an active connection and do not need to interact with a DataAdapter, Command objects provide a very fast and efficient way to interact with a database. Command objects can be used to perform the following actions:
Execute commands that do not return values, such as INSERT, UPDATE, and DELETE.
Execute commands that return a single value.
Execute Database Definition Language (DDL) commands, such as CREATE TABLE, or ALTER.
Work with a DataAdapter to return a DataSet.
Return a result set directly through an instance of a DataReader object. This provides the fastest way to access data and is very useful when read-only data is required.
Return a result set as an XML stream. This method is only available with the SqlCommand class.
Return a result set from multiple tables or command statements.
Creating and Configuring a Data Command
You can create a data command in three ways:
By dragging a stored procedure from the Server Explorer window to the designer.
By dragging a SqlCommand or OleDbCommand from the Data tab of the Toolbox to the designer and configuring it in the designer.
By declaring and instantiating the appropriate type of Command object in code and configuring it manually.
The first way to create a data command is the most straightforward. Any stored procedure on a database can automatically be used to create a Command object. When a stored procedure is dragged onto the designer, a Command object of the appropriate type is created. The new command references the stored procedure and can immediately be used to execute the stored procedure as is—no additional configuration is necessary.
To create a Command object that references an existing stored procedure
Drag the stored procedure from the Server Explorer to the designer. An appropriately configured instance of a Command object is created.
Creating a Command object in the designer is almost as easy. You can create a Command object by dragging a SqlCommand or an OleDbCommand from the Data tab of the Toolbox to the designer. This creates an instance of the Command object you have selected. Once created, you must configure your Command object by setting the Connection, CommandType, and CommandText properties.
The CommandType property determines what kind of command is contained by the CommandText property. There are three possible values for the CommandType property:
Text.
A value of Text for this property indicates that the value contained in the CommandText property will be parsed as a SQL text command. In this case, the CommandText property must be set to a valid SQL expression. A Command object can contain multiple SQL statements separated by semicolons (;). If a Command object contains more than one SQL statement, the statements will be executed sequentially when the command is executed.
StoredProcedure.
If the CommandType property is set to StoredProcedure, the value contained in the CommandText property must contain the name of an existing stored procedure on the database. Executing this command will cause the stored procedure of the same name to be executed.
TableDirect.
A CommandType of TableDirect indicates that the name of a table or tables must be indicated by the CommandText property. Executing this command will return all of the columns and all of the rows of the table or tables named in the CommandText property.
The Connection property must be set to an active connection of the appropriate type (that is, a SqlCommand must have a SqlConnection as its connection, and an OleDbCommand must have an OleDbConnection).
Executing Commands
Each type of Command object has three methods to execute the command it represents:
ExecuteNonQuery
ExecuteScalar
ExecuteReader
The SqlCommand class exposes an additional method for executing the command it represents: ExecuteXmlReader.
Each of these methods executes the data command represented by the Command object. The difference between these methods lies in the value(s) they return. The simplest method is ExecuteNonQuery.
This executes the data command, but returns no value. Thus, this is the method typically used to call SQL commands or stored procedures of the INSERT, UPDATE, or DELETE types. Additionally, this is the only way in ADO.NET to execute DDL commands such as CREATE or ALTER. The ExecuteScalar method returns the first column of the first row of data returned by the command, no matter how many rows the command actually selects.
ExecuteReader returns a DataReader object that can iterate through a result set in a forward-only, read-only manner without involving a DataAdapter. This is the fastest way to retrieve data and frequently the most efficient way when you do not need to update or otherwise manipulate the database itself. The SqlCommand class exposes one additional method: ExecuteXmlReader. This class returns an XmlReader object that iterates through the result set and provides data in a forward-only, read-only manner, formatted as XML.
Parameters
Data commands will frequently make use of parameters. Often, the values of some elements of a data command are not known until run time. Consider an application that tracks inventory for a bookstore. It might contain a function that looks up books based on title. You might implement this functionality by querying the database with a SQL statement similar to the following example:
SELECT * FROM Books WHERE (Title LIKE [value])
At design time, you know that you will want to find all the books that have a title similar to a value that will be supplied by the user at run time. Because you do not know what value the user will supply in advance, you must employ some mechanism for supplying that value to the statement at run time.
Parameters are values that fill placeholders left in the command text. Each parameter is an instance of the OleDbParameter or SqlParameter class, as appropriate. Parameters are stored in the Command object's Parameters property, and at run time, the values are read from the property and placed into the SQL statement or supplied to the stored procedure.
Command objects provide a Parameters collection that exposes a collection of Parameter objects of the appropriate type.
The Direction property specifies whether the parameter is for input or output. The possible values for this property are Input, Output, InputOutput, or ReturnValue, which indicates the parameter is to contain a return value from a stored procedure or function.
In code, you can refer to members of the Parameters collection either by their index or by name. The ParameterName property specifies the name that can be used as a key to specify the parameter in code.
The Precision, Scale, and Size properties all affect the size and accuracy of the parameters. Precision and Scale are used with numeric and decimal parameters, and represent the maximum number of digits of the Value property and the number of decimal places that Value resolves to, respectively. Size is used with binary and string parameters and represents the maximum size of data in the column.
SourceColumn and SourceVersion are used when the parameter is bound to a column in a DataTable. The SourceColumn property specifies the column used to look up or map values, and the SourceVersion property specifies which version of the column to use when it is being edited.
The Value property contains the value represented by the parameter.
When the CommandType property of the Command object is set to Text, you must specify a placeholder for any parameters that must be inserted into the SQL statement. With OleDbCommand objects, this placeholder takes the form of a question mark (?).
When multiple parameters are required by the command text, they are inserted in the order that they appear in the Parameters collection.
When using a SqlCommand object, you must use named parameters. Placeholders for a named parameter are created by preceding the name of your parameter (as specified by the ParameterName property) with an @ symbol.
To use a Command object to execute a non-query command
This procedure is used to execute INSERT, UPDATE, and DELETE commands as well as DDL commands such as CREATE TABLE and ALTER.
Set the CommandType property to StoredProcedure if specifying a stored procedure or Text if specifying a SQL string.
Set the CommandText property to the name of the stored procedure or the desired SQL string, as appropriate.
Specify any parameters and their appropriate values.
To use a Command object to return a single value
Set the CommandType property to StoredProcedure if specifying a stored procedure or Text if specifying a SQL string.
Set the CommandText property to the name of the stored procedure or the desired SQL string, as appropriate.
Specify any parameters and their appropriate values.
Call the Command.ExecuteScalar method.
Using DataReaders
Executing non-query or scalar-returning commands with a Command object is relatively straightforward. To use a Command object with queries that return more than one value, however, you must use the ExecuteReader method to return a DataReader.
A DataReader is a lightweight object that provides read-only, forward-only data in a very fast and efficient manner. You can use the DataReader to iterate through the records returned in a result set to expose the values directly to program logic. Using a DataReader is much more efficient than using a DataAdapter to fill a DataSet, but is also more limited. The data provided is read-only; no updates can be performed with a DataReader.
Also, the data access is forward-only; once a record has been read, it cannot be returned to. Additionally, a DataReader is a connected data access structure. A DataReader requires the exclusive use of an active connection for the entire time it is in existence.
Creating a DataReader
DataReaders cannot be created explicitly. Rather, you must instantiate a DataReader by making a call to a Command object's ExecuteReader command. Like other members of the different data providers, each DataProvider has its own class of DataReader. An OleDbCommand object returns an OleDbDataReader, and a SqlCommand object returns a SqlDataReader.
When a Command object's ExecuteReader method is called, the Command object executes the command it represents and builds a DataReader of the appropriate type, which can be assigned to a reference variable.
Simple Data Access with the DataReader
Once you have a reference to a DataReader, you can iterate through the records and read them into memory as needed. When the DataReader is first returned, it is positioned before the first record of the result set. To make the first record available, you must call the Read method. If a record is available, the Read method advances the DataReader to the next record and returns True (true). If a record is not available, the Read method returns False (false).
To access data with a DataReader
Call your Command object's ExecuteReader method and assign the returned DataReader to an appropriately typed variable.
Iterate through the result set within a While (while) loop. You should perform any operations with the data while inside this loop. You must call the DataReader object's Read method before using the data.
When finished, call the DataReader object's Close method to release the connection.
Retrieving Typed Data Using a DataReader
Although the data exposed by a DataReader is typed as objects, the DataReader also exposes methods to retrieve data contained in a result set. These methods are named Get followed by the name of the type to be retrieved. For example, the name of the method to retrieve a Boolean value is called GetBoolean. If you know the type of data in a given column, you can use these methods to return strongly typed data from that column.
Using Multiple Result Sets
If the CommandType property of your Command object is set to Text, you can return multiple result sets with a single command by providing multiple SQL commands in the CommandText property. To indicate multiple commands, separate each command with a semicolon (;). For example:
SELECT * FROM Accounts; SELECT * FROM Creditors
When multiple SQL statements are specified in the CommandText property of a Command object, the statements are executed sequentially. If more than one statement returns a result set, multiple result sets are returned by the DataReader, also sequentially.
The first result set is returned automatically by the DataReader. To access the next result set, you must call the NextResult method.
Like the Read method, the NextResult method returns False (false) when there are no more result sets to be read. Unlike the Read method, however, the DataReader comes into existence positioned at the first result set. If the NextResult method is called before the first result set is read, the first result set is discarded, and the next result set is read.
To read multiple result sets with a DataReader
Set the CommandType property of your Command object to Text.
Specify multiple SQL statements in the CommandText property of your Command object. These should be separated by a semicolon.
Call the ExecuteReader method of your Command object and assign the DataReader to a variable.
Use the DataReader object's NextResult method to iterate through the result sets.
Executing Ad Hoc SQL Queries
At times, you might not know which SQL query you want to execute until run time. You might want to receive a search string through user input, programmatically determine columns to be returned, or even determine the appropriate table at run time. You can create, configure, and execute commands all at run time.
The first step in executing an ad hoc SQL query is to build the command string. When building the command string, you should start by creating the outline of the command string. Where required, add string variables that represent the values to be inserted at run time. Use the concatenation operator to connect the strings.
The Command classes of each Data Provider expose constructors that allow you to set the CommandText and Connection properties at instantiation. After setting these properties, all you need to do is open the connection and execute the command. The following example demonstrates how a method might receive a search string, build an ad hoc SQL command, and execute it against the database.
Creating and Configuring DataAdapters
DataAdapter objects provide the link between a data source and a DataSet by managing the exchange of data. A DataAdapter is responsible for moving data between a data source and a DataSet. In some applications, this movement is strictly one-way. Other applications, however, might require continued querying and updating of the data source. The DataAdapter incorporates the functionality required to retrieve data, populate a DataSet, and perform updates as required.
There are two primary DataAdapters that are included with Visual Studio .NET. The SqlDataAdapter is designed to provide optimal communication with Microsoft SQL Server 7.0 or higher. TheOleDbDataAdapter provides access to any data source that is exposed by an OleDb Provider.
A single DataAdapter is generally used to manage data exchange between a single DataTable object in a DataSet and a single source database table. Because DataSet objects can contain multiple tables, you should create a single DataAdapter for each table you want to add to the DataSet.
There are three ways you can create a DataAdapter. You can drag database elements from the Server Explorer, you can use the new Data Adapter Configuration wizard, or you can create and configure the DataAdapter manually in code. The following sections describe each of these approaches.
Creating a DataAdapter Using Server Explorer
The easiest way to create a DataAdapter is by using the Server Explorer window. The Data Connections node of the Server Explorer window lists each installed data connection. Each Data Connection node, in turn, expands to provide detail about the database it represents, including a list of available Tables, Views, and Stored Procedures.
You can create a DataAdapter that represents a table by dragging a table from the Server Explorer window onto the designer. The resulting DataAdapter is of the correct type (SqlDataAdapter or OleDbDataAdapter) and has correctly configured SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand properties.
You can also configure your DataAdapter to return a subset of the available columns in a table. To do this, expand the node that represents the table that contains the columns you want to select. You can then select individual columns by clicking the column entries while holding down the Ctrl key. When you have selected the columns you want to add, drag them to the designer. This creates a DataAdapter that is configured to manage only these columns.
To create a DataAdapter in Server Explorer
In the Server Explorer window, expand the node that represents the connection to the database you are using in your application.
Expand the Tables node to display the tables present in the database.
Select your table. If you do not want to use the entire table, expand the node represented by the table to display the individual columns. Select columns with the mouse while holding down the Ctrl key.
Drag your selection to the designer. A new instance of the appropriate type of DataAdapter is created and configured.
Creating a DataAdapter with the Data Adapter Configuration Wizard
You can create a new DataAdapter by dragging the appropriate DataAdapter class from the Toolbox to the designer. This launches the Data Adapter Configuration wizard.
To create a DataAdapter with the Data Adapter Configuration Wizard
From the Data tab of the Toolbox, drag the appropriate type of DataAdapter onto the designer.
This launches the Data Adapter Configuration wizard. Click Next to begin configuring your DataAdapter.
In the Choose Your Data Connection page, select the appropriate Data Connection from the drop-down menu. Click Next to continue.
Retrieving Data Using DataAdapters
A DataSet is an in-memory representation of data that is inherently disconnected from the database. A DataSet can represent a complete set of data including multiple tables, data relationships, and constraints. Because a DataSet is a disconnected representation of data, it can include data from multiple data sources. All interactions between DataSet objects and databases are managed by DataAdapter objects.
The DataAdapter encapsulates the functionality required to fill a DataSet with data and to update the database. It acts as a bridge between the connection and DataSet. The DataAdapter maintains a reference to a database connection in its Connection property, which represents the database that any data actions are executed against. The DataAdapter also exposes a SelectCommand property that contains the instructions for selecting data from a data source as a Command object. Like other Data Provider members, there is a DataAdapter implementation for each Data Provider.
You can fill a DataSet with data by calling the DataAdapter's Fill method. The Fill method executes the instructions contained within the SelectCommand property across the connection specified by the Connection property, and fills a specified DataSet with the resultant data. You must specify either a DataSet or a DataTable as the target of a Fill method.
To retrieve data with a DataAdapter
Create an instance of the appropriate type of DataAdapter that specifies the data you want to select.
Create an instance of a DataSet or DataTable object.
Call the Fill method of the DataAdapter, specifying the DataTable or DataSet as the target.
When working with DataAdapter objects, a single DataAdapter is generally used to manage a single table of data. If you want to load multiple tables into a single DataSet, you should use multiple DataAdapter objects. A single DataSet can be the target of multiple Fill commands. When each DataAdapter calls its Fill method, a new DataTable is created, filled with data and added to the DataSet.
To fill a DataSet with multiple tables
Create an instance of a DataSet.
Create a DataAdapter of the appropriate type for each table you want to have represented in your DataSet.
Sequentially call the Fill method of each DataAdapter, specifying your DataSet as the target.
Typed DataSet Objects
Standard DataSet objects are inherently weakly typed. Every data point is exposed as an object and must be converted to the appropriate type to perform any type specific manipulations. Working with weakly typed variables can cause type mismatch errors, which can be difficult to debug. ADO.NET provides an alternative to working with weakly typed variables: the typed DataSet.
A typed DataSet, as the name implies, is a DataSet that implements strong typing for each member. Tables and columns of a typed DataSet are accessible through user-friendly names that represent the names of the tables and columns you are working with, and data is exposed as typed instead of as objects. This provides many advantages to your application. Readability and maintainability of your code is increased.
Type-mismatch errors are discovered at compile time instead of at run time, saving valuable testing cycles. You can use friendly name syntax instead of collection syntax, and your typed data members can be displayed at design time in the Intellisense window.
related post
DAY 11 OOPS INTRODUCTION
DAY 12 POLYMORPHISM
DAY 13 INHERITANCE AND POLYMORPHISM
DAY 14 EBUGGING TOOLS IN DOT NET
DAY 15 DEBUG AND TRACE IN CLASSES
DAY 16 UNIT TEST PLAN
DAY 17 EXCEPTIONS IN VISUAL STUDIO
DAY 19 ADO.NET INTRODUCTION
DAY 20 DATA ACCESSING IN DOT NET
DAY 21 DATA BASE OBJECTS
Connecting to a Database
You can implement a connection to a database in your application in many different ways. The easiest way is to use the graphical tools provided by Visual Studio .NET to create a connection at design time.
Current data connections are managed in the Server Explorer window. This window is normally docked to the left pane of the Integrated Development Environment (IDE) and shares the same space as the Toolbox. If the Toolbox is currently visible, you can view the Server Explorer by clicking the Server Explorer tab located at the bottom of the Toolbox.
Data connections that are currently available to Visual Studio .NET are displayed as child nodes of the Data Connections node in Server Explorer. If you want to add a connection that is already listed under the Data Connections node to your project, all you need to do is drag the connection from the Server Explorer window to the designer.
A new Connection object of the appropriate type (Sql or OleDb) is created and automatically configured to connect to your database.
To add a connection from the Server Explorer
Drag the node that represents a database from the Server Explorer window to the designer.
You can also create a new connection in the Server Explorer. You can create a new connection by right-clicking the Data Connections node and choosing Add Connection. This launches the Data Link Properties dialog box.
The Data Link Properties dialog box is a visual way to configure your data connection. The Providers tab allows you to choose the database provider that you will use in your connection. The Connection tab allows you to configure the specific properties of your connections, and the Advanced tab configures properties not normally needed for every connection.
The All tab allows you to view and edit the current settings of all the properties. Once you have configured your new connection, you can click OK to close the Data Link Properties window. The new connection appears in the Server Explorer window and can be dragged to your designer.
To create a new connection in the Server Explorer
In the Server Explorer, right-click Data Connections and choose Add Connection to open the Data Link Properties window.
On the Provider tab, choose the appropriate provider for your data connection.
On the Connection tab, choose the database you want to connect to. Depending on the provider, you might also have to set additional properties such as the server name or password settings.
When you have entered the appropriate information, click the Test Connection button to verify that the connection is functional.
Drag the new connection from the Server Explorer to the designer surface. A new connection object of the appropriate type is created and correctly configured.
You can also create a database connection manually, either by dragging a Connection object from the Toolbox to the designer or by declaring and instantiating the object in your code.
If you create a connection this way, you will have to manually set the ConnectionString property.
- To create a new connection in code
- Declare and instantiate the appropriate type of Connection object.
- Set the ConnectionString property.
- To create a new connection in the designer
- Drag the appropriate type of Connection object from the Data tab of the Toolbox to the designer.
- In the Properties window, set the ConnectionString property.
- Using Data Commands
As with other classes represented in Data Providers, there are two types of Command classes. The OleDbCommand class is designed to interact with a wide variety of database types. The SqlCommand class is designed to interact specifically with Microsoft SQL Server 7.0.
Because they only require an active connection and do not need to interact with a DataAdapter, Command objects provide a very fast and efficient way to interact with a database. Command objects can be used to perform the following actions:
Execute commands that do not return values, such as INSERT, UPDATE, and DELETE.
Execute commands that return a single value.
Execute Database Definition Language (DDL) commands, such as CREATE TABLE, or ALTER.
Work with a DataAdapter to return a DataSet.
Return a result set directly through an instance of a DataReader object. This provides the fastest way to access data and is very useful when read-only data is required.
Return a result set as an XML stream. This method is only available with the SqlCommand class.
Return a result set from multiple tables or command statements.
Creating and Configuring a Data Command
You can create a data command in three ways:
By dragging a stored procedure from the Server Explorer window to the designer.
By dragging a SqlCommand or OleDbCommand from the Data tab of the Toolbox to the designer and configuring it in the designer.
By declaring and instantiating the appropriate type of Command object in code and configuring it manually.
The first way to create a data command is the most straightforward. Any stored procedure on a database can automatically be used to create a Command object. When a stored procedure is dragged onto the designer, a Command object of the appropriate type is created. The new command references the stored procedure and can immediately be used to execute the stored procedure as is—no additional configuration is necessary.
To create a Command object that references an existing stored procedure
Drag the stored procedure from the Server Explorer to the designer. An appropriately configured instance of a Command object is created.
Creating a Command object in the designer is almost as easy. You can create a Command object by dragging a SqlCommand or an OleDbCommand from the Data tab of the Toolbox to the designer. This creates an instance of the Command object you have selected. Once created, you must configure your Command object by setting the Connection, CommandType, and CommandText properties.
The CommandType property determines what kind of command is contained by the CommandText property. There are three possible values for the CommandType property:
Text.
A value of Text for this property indicates that the value contained in the CommandText property will be parsed as a SQL text command. In this case, the CommandText property must be set to a valid SQL expression. A Command object can contain multiple SQL statements separated by semicolons (;). If a Command object contains more than one SQL statement, the statements will be executed sequentially when the command is executed.
StoredProcedure.
If the CommandType property is set to StoredProcedure, the value contained in the CommandText property must contain the name of an existing stored procedure on the database. Executing this command will cause the stored procedure of the same name to be executed.
TableDirect.
A CommandType of TableDirect indicates that the name of a table or tables must be indicated by the CommandText property. Executing this command will return all of the columns and all of the rows of the table or tables named in the CommandText property.
The Connection property must be set to an active connection of the appropriate type (that is, a SqlCommand must have a SqlConnection as its connection, and an OleDbCommand must have an OleDbConnection).
Executing Commands
Each type of Command object has three methods to execute the command it represents:
ExecuteNonQuery
ExecuteScalar
ExecuteReader
The SqlCommand class exposes an additional method for executing the command it represents: ExecuteXmlReader.
Each of these methods executes the data command represented by the Command object. The difference between these methods lies in the value(s) they return. The simplest method is ExecuteNonQuery.
This executes the data command, but returns no value. Thus, this is the method typically used to call SQL commands or stored procedures of the INSERT, UPDATE, or DELETE types. Additionally, this is the only way in ADO.NET to execute DDL commands such as CREATE or ALTER. The ExecuteScalar method returns the first column of the first row of data returned by the command, no matter how many rows the command actually selects.
ExecuteReader returns a DataReader object that can iterate through a result set in a forward-only, read-only manner without involving a DataAdapter. This is the fastest way to retrieve data and frequently the most efficient way when you do not need to update or otherwise manipulate the database itself. The SqlCommand class exposes one additional method: ExecuteXmlReader. This class returns an XmlReader object that iterates through the result set and provides data in a forward-only, read-only manner, formatted as XML.
Parameters
Data commands will frequently make use of parameters. Often, the values of some elements of a data command are not known until run time. Consider an application that tracks inventory for a bookstore. It might contain a function that looks up books based on title. You might implement this functionality by querying the database with a SQL statement similar to the following example:
SELECT * FROM Books WHERE (Title LIKE [value])
At design time, you know that you will want to find all the books that have a title similar to a value that will be supplied by the user at run time. Because you do not know what value the user will supply in advance, you must employ some mechanism for supplying that value to the statement at run time.
Parameters are values that fill placeholders left in the command text. Each parameter is an instance of the OleDbParameter or SqlParameter class, as appropriate. Parameters are stored in the Command object's Parameters property, and at run time, the values are read from the property and placed into the SQL statement or supplied to the stored procedure.
Command objects provide a Parameters collection that exposes a collection of Parameter objects of the appropriate type.
The Direction property specifies whether the parameter is for input or output. The possible values for this property are Input, Output, InputOutput, or ReturnValue, which indicates the parameter is to contain a return value from a stored procedure or function.
In code, you can refer to members of the Parameters collection either by their index or by name. The ParameterName property specifies the name that can be used as a key to specify the parameter in code.
The Precision, Scale, and Size properties all affect the size and accuracy of the parameters. Precision and Scale are used with numeric and decimal parameters, and represent the maximum number of digits of the Value property and the number of decimal places that Value resolves to, respectively. Size is used with binary and string parameters and represents the maximum size of data in the column.
SourceColumn and SourceVersion are used when the parameter is bound to a column in a DataTable. The SourceColumn property specifies the column used to look up or map values, and the SourceVersion property specifies which version of the column to use when it is being edited.
The Value property contains the value represented by the parameter.
When the CommandType property of the Command object is set to Text, you must specify a placeholder for any parameters that must be inserted into the SQL statement. With OleDbCommand objects, this placeholder takes the form of a question mark (?).
When multiple parameters are required by the command text, they are inserted in the order that they appear in the Parameters collection.
When using a SqlCommand object, you must use named parameters. Placeholders for a named parameter are created by preceding the name of your parameter (as specified by the ParameterName property) with an @ symbol.
To use a Command object to execute a non-query command
This procedure is used to execute INSERT, UPDATE, and DELETE commands as well as DDL commands such as CREATE TABLE and ALTER.
Set the CommandType property to StoredProcedure if specifying a stored procedure or Text if specifying a SQL string.
Set the CommandText property to the name of the stored procedure or the desired SQL string, as appropriate.
Specify any parameters and their appropriate values.
To use a Command object to return a single value
Set the CommandType property to StoredProcedure if specifying a stored procedure or Text if specifying a SQL string.
Set the CommandText property to the name of the stored procedure or the desired SQL string, as appropriate.
Specify any parameters and their appropriate values.
Call the Command.ExecuteScalar method.
Using DataReaders
Executing non-query or scalar-returning commands with a Command object is relatively straightforward. To use a Command object with queries that return more than one value, however, you must use the ExecuteReader method to return a DataReader.
A DataReader is a lightweight object that provides read-only, forward-only data in a very fast and efficient manner. You can use the DataReader to iterate through the records returned in a result set to expose the values directly to program logic. Using a DataReader is much more efficient than using a DataAdapter to fill a DataSet, but is also more limited. The data provided is read-only; no updates can be performed with a DataReader.
Also, the data access is forward-only; once a record has been read, it cannot be returned to. Additionally, a DataReader is a connected data access structure. A DataReader requires the exclusive use of an active connection for the entire time it is in existence.
Creating a DataReader
DataReaders cannot be created explicitly. Rather, you must instantiate a DataReader by making a call to a Command object's ExecuteReader command. Like other members of the different data providers, each DataProvider has its own class of DataReader. An OleDbCommand object returns an OleDbDataReader, and a SqlCommand object returns a SqlDataReader.
When a Command object's ExecuteReader method is called, the Command object executes the command it represents and builds a DataReader of the appropriate type, which can be assigned to a reference variable.
Simple Data Access with the DataReader
Once you have a reference to a DataReader, you can iterate through the records and read them into memory as needed. When the DataReader is first returned, it is positioned before the first record of the result set. To make the first record available, you must call the Read method. If a record is available, the Read method advances the DataReader to the next record and returns True (true). If a record is not available, the Read method returns False (false).
To access data with a DataReader
Call your Command object's ExecuteReader method and assign the returned DataReader to an appropriately typed variable.
Iterate through the result set within a While (while) loop. You should perform any operations with the data while inside this loop. You must call the DataReader object's Read method before using the data.
When finished, call the DataReader object's Close method to release the connection.
Retrieving Typed Data Using a DataReader
Although the data exposed by a DataReader is typed as objects, the DataReader also exposes methods to retrieve data contained in a result set. These methods are named Get followed by the name of the type to be retrieved. For example, the name of the method to retrieve a Boolean value is called GetBoolean. If you know the type of data in a given column, you can use these methods to return strongly typed data from that column.
Using Multiple Result Sets
If the CommandType property of your Command object is set to Text, you can return multiple result sets with a single command by providing multiple SQL commands in the CommandText property. To indicate multiple commands, separate each command with a semicolon (;). For example:
SELECT * FROM Accounts; SELECT * FROM Creditors
When multiple SQL statements are specified in the CommandText property of a Command object, the statements are executed sequentially. If more than one statement returns a result set, multiple result sets are returned by the DataReader, also sequentially.
The first result set is returned automatically by the DataReader. To access the next result set, you must call the NextResult method.
Like the Read method, the NextResult method returns False (false) when there are no more result sets to be read. Unlike the Read method, however, the DataReader comes into existence positioned at the first result set. If the NextResult method is called before the first result set is read, the first result set is discarded, and the next result set is read.
To read multiple result sets with a DataReader
Set the CommandType property of your Command object to Text.
Specify multiple SQL statements in the CommandText property of your Command object. These should be separated by a semicolon.
Call the ExecuteReader method of your Command object and assign the DataReader to a variable.
Use the DataReader object's NextResult method to iterate through the result sets.
Executing Ad Hoc SQL Queries
At times, you might not know which SQL query you want to execute until run time. You might want to receive a search string through user input, programmatically determine columns to be returned, or even determine the appropriate table at run time. You can create, configure, and execute commands all at run time.
The first step in executing an ad hoc SQL query is to build the command string. When building the command string, you should start by creating the outline of the command string. Where required, add string variables that represent the values to be inserted at run time. Use the concatenation operator to connect the strings.
The Command classes of each Data Provider expose constructors that allow you to set the CommandText and Connection properties at instantiation. After setting these properties, all you need to do is open the connection and execute the command. The following example demonstrates how a method might receive a search string, build an ad hoc SQL command, and execute it against the database.
Creating and Configuring DataAdapters
DataAdapter objects provide the link between a data source and a DataSet by managing the exchange of data. A DataAdapter is responsible for moving data between a data source and a DataSet. In some applications, this movement is strictly one-way. Other applications, however, might require continued querying and updating of the data source. The DataAdapter incorporates the functionality required to retrieve data, populate a DataSet, and perform updates as required.
There are two primary DataAdapters that are included with Visual Studio .NET. The SqlDataAdapter is designed to provide optimal communication with Microsoft SQL Server 7.0 or higher. TheOleDbDataAdapter provides access to any data source that is exposed by an OleDb Provider.
A single DataAdapter is generally used to manage data exchange between a single DataTable object in a DataSet and a single source database table. Because DataSet objects can contain multiple tables, you should create a single DataAdapter for each table you want to add to the DataSet.
There are three ways you can create a DataAdapter. You can drag database elements from the Server Explorer, you can use the new Data Adapter Configuration wizard, or you can create and configure the DataAdapter manually in code. The following sections describe each of these approaches.
Creating a DataAdapter Using Server Explorer
The easiest way to create a DataAdapter is by using the Server Explorer window. The Data Connections node of the Server Explorer window lists each installed data connection. Each Data Connection node, in turn, expands to provide detail about the database it represents, including a list of available Tables, Views, and Stored Procedures.
You can create a DataAdapter that represents a table by dragging a table from the Server Explorer window onto the designer. The resulting DataAdapter is of the correct type (SqlDataAdapter or OleDbDataAdapter) and has correctly configured SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand properties.
You can also configure your DataAdapter to return a subset of the available columns in a table. To do this, expand the node that represents the table that contains the columns you want to select. You can then select individual columns by clicking the column entries while holding down the Ctrl key. When you have selected the columns you want to add, drag them to the designer. This creates a DataAdapter that is configured to manage only these columns.
To create a DataAdapter in Server Explorer
In the Server Explorer window, expand the node that represents the connection to the database you are using in your application.
Expand the Tables node to display the tables present in the database.
Select your table. If you do not want to use the entire table, expand the node represented by the table to display the individual columns. Select columns with the mouse while holding down the Ctrl key.
Drag your selection to the designer. A new instance of the appropriate type of DataAdapter is created and configured.
Creating a DataAdapter with the Data Adapter Configuration Wizard
You can create a new DataAdapter by dragging the appropriate DataAdapter class from the Toolbox to the designer. This launches the Data Adapter Configuration wizard.
To create a DataAdapter with the Data Adapter Configuration Wizard
From the Data tab of the Toolbox, drag the appropriate type of DataAdapter onto the designer.
This launches the Data Adapter Configuration wizard. Click Next to begin configuring your DataAdapter.
In the Choose Your Data Connection page, select the appropriate Data Connection from the drop-down menu. Click Next to continue.
Retrieving Data Using DataAdapters
A DataSet is an in-memory representation of data that is inherently disconnected from the database. A DataSet can represent a complete set of data including multiple tables, data relationships, and constraints. Because a DataSet is a disconnected representation of data, it can include data from multiple data sources. All interactions between DataSet objects and databases are managed by DataAdapter objects.
The DataAdapter encapsulates the functionality required to fill a DataSet with data and to update the database. It acts as a bridge between the connection and DataSet. The DataAdapter maintains a reference to a database connection in its Connection property, which represents the database that any data actions are executed against. The DataAdapter also exposes a SelectCommand property that contains the instructions for selecting data from a data source as a Command object. Like other Data Provider members, there is a DataAdapter implementation for each Data Provider.
You can fill a DataSet with data by calling the DataAdapter's Fill method. The Fill method executes the instructions contained within the SelectCommand property across the connection specified by the Connection property, and fills a specified DataSet with the resultant data. You must specify either a DataSet or a DataTable as the target of a Fill method.
To retrieve data with a DataAdapter
Create an instance of the appropriate type of DataAdapter that specifies the data you want to select.
Create an instance of a DataSet or DataTable object.
Call the Fill method of the DataAdapter, specifying the DataTable or DataSet as the target.
When working with DataAdapter objects, a single DataAdapter is generally used to manage a single table of data. If you want to load multiple tables into a single DataSet, you should use multiple DataAdapter objects. A single DataSet can be the target of multiple Fill commands. When each DataAdapter calls its Fill method, a new DataTable is created, filled with data and added to the DataSet.
To fill a DataSet with multiple tables
Create an instance of a DataSet.
Create a DataAdapter of the appropriate type for each table you want to have represented in your DataSet.
Sequentially call the Fill method of each DataAdapter, specifying your DataSet as the target.
Typed DataSet Objects
Standard DataSet objects are inherently weakly typed. Every data point is exposed as an object and must be converted to the appropriate type to perform any type specific manipulations. Working with weakly typed variables can cause type mismatch errors, which can be difficult to debug. ADO.NET provides an alternative to working with weakly typed variables: the typed DataSet.
A typed DataSet, as the name implies, is a DataSet that implements strong typing for each member. Tables and columns of a typed DataSet are accessible through user-friendly names that represent the names of the tables and columns you are working with, and data is exposed as typed instead of as objects. This provides many advantages to your application. Readability and maintainability of your code is increased.
Type-mismatch errors are discovered at compile time instead of at run time, saving valuable testing cycles. You can use friendly name syntax instead of collection syntax, and your typed data members can be displayed at design time in the Intellisense window.
related post
DAY 11 OOPS INTRODUCTION
DAY 12 POLYMORPHISM
DAY 13 INHERITANCE AND POLYMORPHISM
DAY 14 EBUGGING TOOLS IN DOT NET
DAY 15 DEBUG AND TRACE IN CLASSES
DAY 16 UNIT TEST PLAN
DAY 17 EXCEPTIONS IN VISUAL STUDIO
DAY 19 ADO.NET INTRODUCTION
DAY 20 DATA ACCESSING IN DOT NET
DAY 21 DATA BASE OBJECTS
No comments:
Post a Comment