System.Data.SqlClient Namespace
Represents a set of data commands and a database connection which are used to
fill the DataSet and update an SQL Server™ database.
The SqlDataAdapter serves as a bridge between SQL Server™ and a DataSet, providing the interface for retrieving and saving the data. Using the appropriate Transact-SQL statements against the data source, the SqlDataAdapter provides this bridge by mapping:
- Fill, which changes the data in the DataSet to match the data in the data source, and
- Update, which changes the data in the data source to match the data in the DataSet,
SqlDataAdapter is used in conjunction with SqlConnection and SqlCommand to increase performance when connecting to a Microsoft SQL Server™ database. To access other data sources, use OleDbDataAdapter along with its associated OleDbConnection and OleDbCommand.
The SqlDataAdapter also includes the SelectCommand, InsertCommand, DeleteCommand, UpdateCommand, and TableMappings properties for facilitating the loading and updating of data.
A new instance of SqlDataAdapter can be constructed in four ways. For a list of the different versions and the parameters that can be used with each, see the SqlDataAdapter constructor.
The following example shows using an SqlDataAdapter to fill a DataSet with records retrieved from a data source. Here, the entire logic is encapsulated within a Page_Load event handler that essentially:
- establishes a connection to the database,
- initializes an instance of a DataSet to contain the database information,
- and then enumerates the contents of the DataSet into an html table.
string html;
protected void Page_Load ( Object Src, EventArgs e ) {
// set up the connection
SqlConnection myConn = new SqlConnection (
"server=localhost; trusted_connection=yes; database=northwind" );
// set up the query
SqlDataAdapter myAdapter = new SqlDataAdapter ( "SELECT * FROM nw_Customers", myConn );
// instantiate dataset object
DataSet myData = new DataSet ( );
// fill with query results
myAdapter.Fill ( myData, "Customers" );
// display dataset contents into html table
// first open the table and set up the table headers
html += "<table cellspacing=1 class='data' width=80%>";
html += "<tr>";
html += "<th>Customer ID</th>";
html += "<th>Company Name</th>";
html += "</tr>";
// loop thru the dataset
foreach ( DataRow Customer in myData.Tables [ "Customers" ].Rows ) {
html += "<tr>";
html += "<td>" + Customer [ "CustomerID" ].ToString ( ) + "</td>";
html += "<td>" + Customer [ "CompanyName" ].ToString ( ) + "</td>";
html += "</tr>";
}
// close the table
html += "</table>";
}
dim html as string
protected sub Page_Load ( byVal Src as Object, byVal E as EventArgs ) {
' set up the connection
dim myConn as new SqlConnection (
"server=localhost; trusted_connection=yes; database=northwind" )
' set up the query
dim myAdapter as new SqlDataAdapter ( "SELECT * FROM nw_Customers", myConn )
' instantiate dataset object
dim myData as new DataSet ( )
' fill with query results
myAdapter.Fill ( myData, "Customers" )
' display dataset contents into html table
' first open the table and set up the table headers
html &= "<table cellspacing=1 class='data' width=80%>"
html &= "<tr>"
html &= "<th>Customer ID</th>"
html &= "<th>Company Name</th>"
html &= "</tr>"
' loop thru the dataset
foreach Customer as DataRow in myData.Tables ( "Customers" ).Rows
html &= "<tr>"
html &= "<td>" & Customer ( "CustomerID" ).ToString ( ) & "</td>"
html &= "<td>" & Customer ( "CompanyName" ).ToString ( ) & "</td>"
html &= "</tr>"
next
' close the table
html &= "</table>"
end sub |
| |
C# |
VB |
 Show me
SqlConnection SqlCommand DataSet DataTable
|
|
Constructors
Properties
Methods
Events
