Filtering Rows Using a Filter Expression
The DataView offers a RowFilter property, which we can use to provide a filter expression. We can set the filter expression in the property field. Its syntax is similar to an SQL WHERE clause. The following figure illustrates a filter that retrieves a specific row in the tabFlights table of the typed DataSet called ds Visual Studio .NET will generate the code that will filter the content of the dataset retrieving just the row having the value specified. this.dvFlights.RowFilter FLIGHTCODE...
The IDataRecord Interface
As we've just seen, the IDataReader interface allows us to move over the result set and get information regarding the schema, result set position etc. Once we've got our datareader, it's not much use if we can't actually access the data in the records. This is where the IDataRecord interface comes into play. Through this interface we can access the column values for the current row in the datareader. The tables below show some of the methods and properties on this interface Gets a Boolean value...
Accessing the Data in a TypeSafe Manner
We have learned a fair amount about datareaders. We know how to traverse single or multiple result sets and how we get a result sets contents via the indexer. So far we haven't explored using any other method of accessing data, like using the type-safe methods that all datareaders implement. This is what we'll look at now. As we saw earlier, indexers allow us to access columns in the datareader either using the column name or the column ordinal the zero-based position of the column in the table...
Using the SqlParameter and OleDbParameter Classes
The .NET Data Providers include classes for creating parameter objects that can be added to a command's ParametersCollection. The parameters are created with a name, data type, input or output specification, and a value if applicable. Both the SqlParameter and OleDbParameter classes function in the same way however, the SqlParameters are passed to the SQL Server as named parameters and must map to parameter names in the stored procedures. Creating Parameterized T-SQL Queries We can create...
Executing Stored Procedures with a DataReader
We have seen how to execute simple SQL statements against the database, so now we're going to look at how we use the command object and the datareader to execute a stored procedure. We'll be using the CustOrdersDetail stored procedure that comes with Northwind in this example. The stored procedure SQL is shown below don't worry too much about its contents, all we need to know is that it takes an order ID and returns the details for that order CREATE PROCEDURE CustOrdersDetail OrderID int AS...
ProviderSpecific Classes
The second major difference between ADO.NET and ADO is that there are specific sets of classes for each of the providers. In ADO, we simply used Connection, Command, and Recordset objects, regardless of the data source we were accessing if we changed the database system used say, upgraded from Access to SQL Server , then in theory at least all we needed to change was the connection string. Now we need to create a specific SqlConnection, OleDbConnection, etc. object, depending on the data...
Executing the Command
To execute a command and return any results we invoke one of the exposed execute methods only ExecuteNonQuery is used for executing commands that do not return a result set, such as update, insert, or delete commands. The ExecuteNonQuery method returns an integer value indicating the number of rows affected by the command. For all other types of commands -1 is returned. In the following code, ExecuteNonQuery returns the number of rows affected by the DELETE command. SqlConnection con new...