Section 2

• Part 2
  
  • Topic 3

 

Create, delete, and edit data in a connected environment.

• Retrieve data by using a DataReader object.
• Build SQL commands visually in Server Explorer.
• Build SQL commands in code.
• Create parameters for a command object.
• Perform database operations by using a command object.
• Retrieve data from a database by using a command object.
• Perform asynchronous operations by using a command object.
• Perform bulk copy operations to copy data to a SQL Server computer.
• Store and retrieve binary large object (BLOB) data types in a database.

Summary

DataReaders provide forward only access to Records returned from a database. New in .Net 2.0, the DataSet has a method to retrieve a DataReader that retrieves data against the disconnected data present in the data set.

You can drag tables from the ServerExplorer to visually create SQLConnections and commands.

You can build SQL Commands in Code by passing the Command Text to the Constructor of the SQLCommand object or specifying it in the CommandText Property of the object. You can use three different execute methods depending on the type of data you are expecting to return: Execute reader returns a DataReader, ExecuteScalar returns a single value, and ExceuteNonQuery returns no rows.

You can parameterize your sql queries or execute stored procedures with parameters using SQLCommandParameters as follows (Code Snippet Taken from Resources Using Parameterized SQL Queries)

SqlConnection objConnection = new SqlConnection(_ConnectionString);

objConnection.Open();

SqlCommand objCommand = new SqlCommand(

"SELECT * FROM User WHERE Name = @Name AND Password = @Password",

objConnection);

objCommand.Parameters.Add("@Name", NameTextBox.Text);

objCommand.Parameters.Add("@Password", PasswordTextBox.Text);

SqlDataReader objReader = objCommand.ExecuteReader();

if (objReader.Read())

{

This approach is also safer that building your text as a string because it is not suseptable to injection attacks, and allows sql server to cache the execution plan.

New to ADO.Net 2.0 is the ability to asynchronously execute a command. All the familiar execute methods (ExecuteReader, ExecuteScalar and ExecuteNonQuery) now also have Async Versions prefixed with both Begin and End. In order to use asynchronous commands, the connections on which the commands will be executed must be initialized with async=true in the connection string. An exception will be thrown if any of the asynchronous methods are called on a command with a connection that doesn't have async=true in its connection string.

The SqlBulkCopy Object is new to ADO.Net 2.0 and is used to perform large copy operations like copying one table to a new table. It exposes a DestinationTable Name and has a writeToServer method to being the operation.

In order to store and retrieve blob data types using ADO.Net you must use byte arrays to get data in and out of the fields that contain BLOB types. See the resources for details.

Other Resources & Links:

Data Access in ASP.Net 2.0
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/dataaccess.asp

Using Parameterized SQL Queries
http://www.uberasp.net/ArticlePrint.aspx?id=46

Asynchronous Command Execution in ASP.Net 2.0
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/async2.asp

Bulk Copy Operations in ADO.Net 2.0
http://www.c-sharpcorner.com/Code/2004/June/BulkCopy.asp

HOW TO: Read and Write BLOB Data by Using ADO.NET Through ASP.NET
http://support.microsoft.com/default.aspx?scid=kb;en-us;326502