io Programmo 40/IOPROG_40.ISO/SOFT/NETFrameworkSDK.exe/comsdk.cab/samples.exe/QuickStart/aspplus/doc/webdataaccess.aspx

Text File | 2000-06-11 | 29.0 KB | 668 lines

<%@ Register TagPrefix="Acme" TagName="SourceRef" Src="/quickstart/util/SrcRef.aspx"%>

<!-- #include virtual="/quickstart/aspplus/include/header.inc" -->

<h4>Server-Side Data Access</h4>
<p>

<!-- #include virtual="/quickstart/aspplus/include/wftoc4.inc" -->
<p>
<hr>

<!--BEGIN SECTION-->
<a name="serverdata">
<span class="subhead">Introduction to Server-Side Data</span>
<p>

Data Access is the heart of any real-world application, and ASP+ provides a rich set of controls that are well-integrated with the
managed data access APIs provided in the NGWS runtime.  In this section we will walk through several iterations of a sample that uses the
ASP+ DataGrid control to bind to the results of SQL queries and XML data files.  This section assumes some familiarity with database
fundamentals and the SQL query language.
<p>
Server-side data access is unique in that web pages are basically stateless, which presents some difficult challenges when trying to
perform transactions such as inserting or updating records from a set of data retrieved from a database.  As we'll see in this section,
the DataGrid control can help manage these challenges, allowing you to concentrate more on your application logic and less on the
details of state management and event handling.

<!--BEGIN SECTION-->
<br>
<a name="adoplus">
<br>
<span class="subhead">Connections, Commands, and DataSets</span>
<p>
The NGWS runtime provides a complete set of managed data access APIs for data-intensive application development.  These APIs help to
abstract the data and present the it in a consistent way regardless of its actual source (SQL Server, OLEDB, XML, etc).  There are
essentially three objects you will work with most often: Connections, Commands, and DataSets.
<p>
<ul>
<li>A <b>Connection</b> represents a physical connection to some data store, such as SQL Server or an XML file.
<li>A <b>Command</b> represents a directive to retrieve from (select) or manipulate the data store (insert, update, delete).
<li>A <b>DataSet</b> represents the actual data an application works with.  Note that DataSets are always disconnected from
their source Connection and data model and can be modified independantly.  However, changes to a DataSet can be easily
reconciled with the originating data model.
</ul>
<p>
For a more detailed walkthough of the managed data access solution in the NGWS runtime, please read the
<a href="/quickstart/aspplus/doc/adoplusoverview.aspx">ADO+ Overview</a> section of this tutorial.

<!--BEGIN SECTION-->
<br>
<a name="sqldata">
<br>
<span class="subhead">Accessing SQL-based Data</span>
<p>

An application typically needs to perform one or more select, insert, update, or delete queries to a SQL database.  An example for each of
these is given in the table below:
<p>

<table class="table2" width="80%" cellpadding=3>
<tr>
<th width="150">
Query
</th>
<th>
Example
</th>
</tr>
<tr>
<td>
<b>Simple Select</b>
</td>
<td>
SELECT * from Employees WHERE FirstName = 'Bradley';
</td>
</tr>
<tr>
<td>
<b>Join Select</b>
</td>
<td>
SELECT * from Employees E, Managers M WHERE E.FirstName = M.FirstName;
</td>
</tr>
<tr>
<td>
<b>Insert</b>
</td>
<td>
INSERT into Employees VALUES ('123-45-6789','Bradley','Millington','Program Manager');
</td>
</tr>
<tr>
<td>
<b>Update</b>
</td>
<td>
UPDATE Employees SET Title = 'Development Lead' WHERE FirstName = 'Bradley';
</td>
</tr>
<tr>
<td>
<b>Delete</b>
</td>
<td>
DELETE from Employees WHERE Productivity < 10;
</td>
</tr>

</table>
<p>

To give your page access to the classes you will need to perform SQL data access, you must import the <b>System.Data</b> and
<b>System.Data.SQL</b> namespaces into your page:
<p>

<div class="code"><pre>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQL" %>
</pre></div>

<p>
To perform a select query to a SQL database we create a <b>SQLConnection</b> to the database passing the connection string, and then
construct a <b>SQLDataSetCommand</b> object that contains our query statement.  To populate a DataSet with the results from the query,
we call the command's <b>FillDataSet</b> method:
<p>

<div class="code"><pre>
SQLConnection myConnection = new SQLConnection("server=localhost;uid=sa;pwd=;database=pubs");
SQLDataSetCommand myCommand = new SQLDataSetCommand("select * from Authors", myConnection);

DataSet ds = new DataSet();
myCommand.FillDataSet(ds, "Authors");
</pre></div>

<p>
When performing commands that do not require data to be returned, such as inserts, updates, and deletes, we use a
<b>SQLCommand</b> instead of the SQLDataSetCommand.  The command is issued by calling an <b>Execute</b>
method, which returns the number of rows affected.  Notice that the connection must be explicitly opened when we use the
SQLCommand (the SQLDataSetCommand automatically handles opening the connection for you).
<p>

<div class="code"><pre>
SQLConnection myConnection = new SQLConnection("server=localhost;uid=sa;pwd=;database=pubs");
SQLCommand mycommand = new SQLCommand(
                   "UPDATE Authors SET phone='[phone]' WHERE au_id = '123-45-6789'",
                    myConnection);

myCommand.ActiveConnection.Open();
myCommand.Execute();
myCommand.ActiveConnection.Close();
</pre></div>

<p>
<b>Important: </b> Always remember to close the Connection to the data model before the page finishes executing.  If you do
not close the connection, you may inadvertantly exhaust the connection limit while waiting for the page instances to be
garbage-collected.

<!--BEGIN SECTION-->
<br>
<a name="select">
<br>
<span class="subhead">Binding SQL Data to a DataGrid</span>
<p>
The following sample shows a simple select query bound to a DataGrid control.  The DataGrid renders a table containing the SQL data.

<p>
<Acme:SourceRef
  RunSample="/quickstart/aspplus/samples/webforms/data/datagrid1.aspx"
  ViewSource="/quickstart/aspplus/samples/webforms/data/datagrid1.src"
  Icon="/quickstart/aspplus/images/datagrid1.gif"
  Caption="DataGrid1.aspx"
  runat="server" />

<p>

Like the DropDownList shown in the DataBinding section, the DataGrid control supports a <b>DataSource</b> property that takes
an ICollection type.  We can set this to the result of a SQL query by assigning the DataSet's <b>DefaultView</b> property, which is of
type <b>DataView</b>.  The DefaultView represents the current state of the DataSet, including any changes which have been made by
application code (row deletes or value changes, for example).  After setting the DataSource property, we call DataBind() to populate the
control.

<!--BEGIN SECTION-->
<br>
<a name="param">
<br>
<span class="subhead">Performing a Parameterized Select</span>
<p>

We can also perform a parameterized select using the SQLDataSetCommand object.  This next sample shows how we can modify the
data selected using the value posted from a select HtmlControl.
<p>

<Acme:SourceRef
  RunSample="/quickstart/aspplus/samples/webforms/data/datagrid2.aspx"
  ViewSource="/quickstart/aspplus/samples/webforms/data/datagrid2.src"
  Icon="/quickstart/aspplus/images/datagrid2.gif"
  Caption="DataGrid2.aspx"
  runat="server" />

<p>
The SQLDataSetCommand maintains a <b>Parameters</b> collection that can be used to replace variable identifiers
(denoted by an "@" in front of the name) with values.  We add a new <b>SQLParameter</b> to this collection that specifies the
name, type, and size of the parameter, and then set its <b>Value</b> property to the value of the select.
<p>

<div class="code"><pre>
myCommand.SelectCommand.Parameters.Add(new SQLParameter("@State", SQLDataType.VarChar, 2));
myCommand.SelectCommand.Parameters["@State"].Value = MySelect.Value;
</pre></div>

<p>
<b>Important: </b> Notice that we've set the DataGrid's MaintainState property to false. If the data will be populated in each request,
there is no benefit to having the DataGrid store state information to be roundtripped with form posts. Because the DataGrid stores all
of it's data when maintaining state, it is important to turn it off when appropriate to improve the performance of your pages.
<p>

DataGrid2.aspx statically populates the values of the select box, but this won't work well if those values ever change in the
database.  Because the select HtmlControl also supports an ICollection DataSource property, we can use a select query to dynamically
populate the select box instead, which guarantees the database and UI are always in sync.  To see how this works, view the sample
below:

<p>

<Acme:SourceRef
  RunSample="/quickstart/aspplus/samples/webforms/data/datagrid3.aspx"
  ViewSource="/quickstart/aspplus/samples/webforms/data/datagrid3.src"
  Icon="/quickstart/aspplus/images/datagrid3.gif"
  Caption="DataGrid3.aspx"
  runat="server" />


<!--BEGIN SECTION-->
<br>
<a name="insert">
<br>
<span class="subhead">Inserting Data in a SQL Database</span>
<p>

To insert a row into the database, we can add a simple input form to the page, and execute an insert command in the form submit
event handler.  Just as we've done in the previous two samples, we use the Command object's Parameters collection to populate the
comannd's values.  Notice that we also check to make sure the required values are not null before attempting to insert into the
database.  This prevents an accidental violation of the database's field constraints.  We also execute the insert command inside of
a try/catch block just in case the primary key for inserted row already exists.

<p>

<Acme:SourceRef
  RunSample="/quickstart/aspplus/samples/webforms/data/datagrid4.aspx"
  ViewSource="/quickstart/aspplus/samples/webforms/data/datagrid4.src"
  Icon="/quickstart/aspplus/images/datagrid4.gif"
  Caption="DataGrid4.aspx"
  runat="server" />

<p>
Instead of explicitly checking the input values, we could have just as easily used the validator controls provided with ASP+.  This next
sample shows how to do that.  Note that using the RegEx Validator gives us the additional benefit of checking the format for the author
id, zip code and phone number fields.
<p>

<Acme:SourceRef
  RunSample="/quickstart/aspplus/samples/webforms/data/datagrid5.aspx"
  ViewSource="/quickstart/aspplus/samples/webforms/data/datagrid5.src"
  Icon="/quickstart/aspplus/images/datagrid5.gif"
  Caption="DataGrid5.aspx"
  runat="server" />

<!--BEGIN SECTION-->
<br>
<a name="update">
<br>
<span class="subhead">Updating Data in a SQL Database</span>
<p>
Updating a database can often be tricky in web applications.  The DataGrid control provides some built-in support for this scenario that
makes updates easier.  To allow rows to be edited, the DataGrid supports an integer <b>EditItemIndex</b> property, which indicates which row
of the grid should be editable.  When this property is set, the DataGrid renders the row at that index as text input boxes instead of
simple labels.  A value of -1 indicates that no rows are editable (the default).  The page can enclose the DataGrid in a server-side form and
get access to the edited data through the DataGrid's object model.
<p>
To figure out which row should be editable, we need a way to accept some input from the client about which row they would like to edit.
The DataGrid can contain an <b>EditCommandColumn</b> that renders links for firing three special events: EditCommand,
UpdateCommand, and CancelCommand.  The EditCommandColumn is declaratively added to the DataGrid's <b>Columns</b>
collection as follows:

<div class="code"><pre>
<ASP:DataGrid id="MyDataGrid" runat="server"
  ...
  OnEditCommand="MyDataGrid_Edit"
  OnCancelCommand="MyDataGrid_Cancel"
  OnUpdateCommand="MyDataGrid_Update"
  DataKeyField="au_id"
>

  <property name="Columns">
    <asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Update" />
  </property>

</ASP:DataGrid>
</pre></div>

On the DataGrid tag itself, we wire-up event handlers to each of the commands fired from the EditCommandColumn.
The <b>DataGridCommandEventArgs</b> argument of these handlers gives us direct access to the index selected by
the client, which we use the set the DataGrid's EditItemIndex.  Note that we need to re-bind the DataGrid for the change
to take effect:
<p>

<div class="code"><pre>
public void MyDataGrid_Edit(Object sender, DataGridCommandEventArgs E)
{
    MyDataGrid.EditItemIndex = (int)E.Item.ItemIndex;
    BindGrid();
}
</pre></div>

<p>
When a row of the DataGrid is being edited, the EditCommandColumn renders the Update and Cancel links.  If the client selects Cancel,
we simply set the EditItemIndex back to -1.  If the client selects Update, however, we need to execute our update command to the
database.  Performing an update query requires that we know the primary key in the database for the row we wish to update.  To
support this, the DataGrid exposes a <b>DataKeyField</b> property which we can set to the field name for the primary key.  In the
event handler wired to the UpdateCommand, we can retrieve the key name from the DataGrid's <b>DataKeys</b> collection.  We
index into this collection using the ItemIndex of the event:
<p>

<div class="code"><pre>
myCommand.Parameters["@Id"].Value = MyDataGrid.DataKeys[(int)E.Item.ItemIndex];
</pre></div>

<p>
At the end of the Update event handler, we set the EditItemIndex back to -1.  To see this code in action, view the sample below:
<p>

<Acme:SourceRef
  RunSample="/quickstart/aspplus/samples/webforms/data/datagrid6.aspx"
  ViewSource="/quickstart/aspplus/samples/webforms/data/datagrid6.src"
  Icon="/quickstart/aspplus/images/datagrid6.gif"
  Caption="DataGrid6.aspx"
  runat="server" />

<p>
One problem with the preceding example is that the primary key field (au_id) also renders as a text input box when a row is editable.
We really don't want the client to change this value, since we need it to tell which row to update in the database.  Fortunately, we can
disable this column from rendering as a text box by specifying exactly what each column looks like for the editable row.  We do this
by defining each row in the DataGrid's Columns collection, using the <b>BoundColumn</b> control to assign data fields with each
column.  Using this technique gives us complete control over the order of the columns, as well as their ReadOnly properties.  For the
au_id column we set the the ReadOnly property to true.  When a row is in edit mode, this column will continue to render as a Label.
View the sample below to see how this works:
<p>

<Acme:SourceRef
  RunSample="/quickstart/aspplus/samples/webforms/data/datagrid7.aspx"
  ViewSource="/quickstart/aspplus/samples/webforms/data/datagrid7.src"
  Icon="/quickstart/aspplus/images/datagrid7.gif"
  Caption="DataGrid7.aspx"
  runat="server" />

<p>
BoundColumns aren't the only controls we can set in the DataGrid's Columns collection.  We can also specify a <b>TemplateColumn</b>,
which gives us complete control over the contents of the column.  The template is just arbitrary content, so we can render anything
we like, including server controls, inside the DataGrid's columns.  In this next sample we use the TemplateColumn control to
render the "state" column as a DropDownList and the "contract" column as a checkbox HtmlControl.  The ASP+ databinding syntax
is used to output the data field value within the template.  Note that there is a bit of tricky logic to make the DropDownList and
checkbox reflect the state of the data inside the row.
<p>

<Acme:SourceRef
  RunSample="/quickstart/aspplus/samples/webforms/data/datagrid8.aspx"
  ViewSource="/quickstart/aspplus/samples/webforms/data/datagrid8.src"
  Icon="/quickstart/aspplus/images/datagrid8.gif"
  Caption="DataGrid8.aspx"
  runat="server" />

<p>
Just as we can place a DropDownList or checkbox HtmlControl in a TemplateColumn, we can also place other controls here.  This
sample adds Validator controls to the columns to check the client input before attempting to perform the update:
<p>

<Acme:SourceRef
  RunSample="/quickstart/aspplus/samples/webforms/data/datagrid9.aspx"
  ViewSource="/quickstart/aspplus/samples/webforms/data/datagrid9.src"
  Icon="/quickstart/aspplus/images/datagrid9.gif"
  Caption="DataGrid9.aspx"
  runat="server" />

<!--BEGIN SECTION-->
<br>
<a name="delete">
<br>
<span class="subhead">Deleting Data in a SQL Database</span>
<p>
Deleting from a database is very similar to an update or insert command, but we still need a way to determine the particular row of
the grid to delete.  Another control which can be added to the DataGrid's Columns collection is the <b>ButtonColumn</b>, which
simply renders a button control.  The ButtonColumn supports a Command property which can be set to "Delete".  On the DataGrid,
we wire up an event handler to the DeleteCommand, where we perform the delete operation.  Again we use the DataKeys collection
to determine the row selected by the client.
<p>

<Acme:SourceRef
  RunSample="/quickstart/aspplus/samples/webforms/data/datagrid10.aspx"
  ViewSource="/quickstart/aspplus/samples/webforms/data/datagrid10.src"
  Icon="/quickstart/aspplus/images/datagrid10.gif"
  Caption="DataGrid10.aspx"
  runat="server" />


<!--BEGIN SECTION-->
<br>
<a name="sort">
<br>
<span class="subhead">Sorting Data from a SQL Database</span>
<p>
A common requirement for any grid is the ability to sort the data it contains.  While the DataGrid control doesn't explicitly sort its
data for you, it does provide a way to call an event handler when the client clicks a column header, which you may use to
sort the data.  When the DataGrid's <b>AllowSorting</b> property is set to true, it will render hyperlinks for the column headers which
fire a Sort command back to the grid.  We set the <b>OnSortCommand</b> property of the DataGrid to the handler we wish to
call when the client clicks a column link.  The name of the column is passed as a SortField property on the
<b>DataGridSortCommandEventArgs</b> argument, which we can use to set the <b>Sort</b> property of the
DataView bound to the grid.

<p>
<div class="code"><pre>
<script>
    protected void MyDataGrid_Sort(Object Src, DataGridSortCommandEventArgs E)
    {
        ...
        DataView Source = ds.Tables["Authors"].DefaultView;
        Source.Sort = E.SortField;
        MyDataGrid.DataBind();
    }
</script>

<form runat="server">
    <ASP:DataGrid id="MyDataGrid" OnSortCommand="MyDataGrid_Sort" AllowSorting="true" runat="server" />
</form>
</pre></div>

<p>
The sample below shows this code in action:
<p>

<Acme:SourceRef
  RunSample="/quickstart/aspplus/samples/webforms/data/datagrid11.aspx"
  ViewSource="/quickstart/aspplus/samples/webforms/data/datagrid11.src"
  Icon="/quickstart/aspplus/images/datagrid11.gif"
  Caption="DataGrid11.aspx"
  runat="server" />

<p>
When using BoundColumns, we can explicitly set the SortField for each column, as demonstrated in this next sample:
<p>

<Acme:SourceRef
  RunSample="/quickstart/aspplus/samples/webforms/data/datagrid12.aspx"
  ViewSource="/quickstart/aspplus/samples/webforms/data/datagrid12.src"
  Icon="/quickstart/aspplus/images/datagrid12.gif"
  Caption="DataGrid12.aspx"
  runat="server" />


<!--BEGIN SECTION-->
<br>
<a name="masterdetail">
<br>
<span class="subhead">Working with Master-Detail Relationships</span>
<p>

Often your data model will contain relationships that cannot be represented using just a single grid.  A very common web-based interface
is one in which a row of data can be selected that navigates the client to a "details" page, which displays detailed information about
the selected row.  To accomplish this using the DataGrid, you can add a <b>HyperLinkColumn</b> to the Columns collection, which
specifies the details page to where the client will navigate when the link is clicked.  We use the format string syntax to substitute a field
value in this link, which is passed as a querystring argument.

<p>

<div class="code"><pre>
    <ASP:DataGrid id="MyDataGrid" runat="server">

      <property name="Columns">
          <asp:HyperLinkColumn
            DataNavigateUrlField="au_id"
            DataNavigateUrlFormatString="datagrid13_details.aspx?id={0}"
            Text="Get Details"
          />
      </property>

    </ASP:DataGrid>
</pre></div>

<p>
On the details page, we retrieve the querystring argument and perform a join select to obtain details from the database.  The sample
below demonstrates this scenario:
<p>

<Acme:SourceRef
  RunSample="/quickstart/aspplus/samples/webforms/data/datagrid13.aspx"
  ViewSource="/quickstart/aspplus/samples/webforms/data/datagrid13.src"
  Icon="/quickstart/aspplus/images/datagrid13.gif"
  Caption="DataGrid13.aspx"
  runat="server" />


<!--BEGIN SECTION-->
<br>
<a name="storedprocs">
<br>
<span class="subhead">Writing and Using Stored Procedures</span>
<p>
In general, performing ad hoc queries comes at the expense of performance.  Using stored procedures can reduce the cost of
performing heavy database operations in an application.  A stored procedure is easy to create, and can even be done using a
SQL statement.  For example, the following statement creates a stored procedure that simply returns a table:

<p>
<div class="code"><pre>
CREATE Procedure GetAuthors AS
    SELECT * FROM Authors
    return
GO
</pre></div>
<p>

You can create stored procedures that accept parameters as well.  For example:

<p>
<div class="code"><pre>
CREATE Procedure LoadPersonalizationSettings (@UserId varchar(50)) AS
    SELECT * FROM Personalization WHERE UserID=@UserId
    return
GO
</pre></div>
<p>

Using a stored procedure from an ASP+ page is just an extension of what we've learned so far about the SQLCommand object.  The
CommandText is just the name of the stored procedure instead of the ad hoc query text.  We indicate to the SQLCommand that the
CommandText is a stored procedure by setting the

<p>
<div class="code"><pre>
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
</pre></div>
<p>

The following sample demonstrates a call to a stored procedure to fill the DataSet:
<p>

<Acme:SourceRef
  RunSample="/quickstart/aspplus/samples/webforms/data/datagrid14.aspx"
  ViewSource="/quickstart/aspplus/samples/webforms/data/datagrid14.src"
  Icon="/quickstart/aspplus/images/datagrid14.gif"
  Caption="DataGrid14.aspx"
  runat="server" />

<p>
Parameters to stored procedures are passed just as they are for ad hoc queries:
<p>

<Acme:SourceRef
  RunSample="/quickstart/aspplus/samples/webforms/data/datagrid15.aspx"
  ViewSource="/quickstart/aspplus/samples/webforms/data/datagrid15.src"
  Icon="/quickstart/aspplus/images/datagrid15.gif"
  Caption="DataGrid15.aspx"
  runat="server" />

<p>

<Acme:SourceRef
  RunSample="/quickstart/aspplus/samples/webforms/data/datagrid16.aspx"
  ViewSource="/quickstart/aspplus/samples/webforms/data/datagrid16.src"
  Icon="/quickstart/aspplus/images/datagrid16.gif"
  Caption="DataGrid16.aspx"
  runat="server" />


<!--BEGIN SECTION-->
<br>
<a name="xmldata">
<br>
<span class="subhead">Accessing XML-based Data</span>
<p>
At the beginning of this section we mentioned that the DataSet was designed to abstract data in a way that is independent of
the actual data source.  We can see this by changing the focus of our samples from SQL to XML.  The DataSet supports a
<b>ReadXml</b> method that takes a FileStream as its parameter.  The file we read in this case must contain both a schema
and the data we wish to read.  The DataSet expects data to be in the form:

<p>
<div class="code"><pre>
<DocumentElement>
    <TableName>
        <ColumnName1>column value</ColumnName1>
        <ColumnName2>column value</ColumnName2>
        <ColumnName3>column value</ColumnName3>
        <ColumnName4>column value</ColumnName4>
    </TableName>
    <TableName>
        <ColumnName1>column value</ColumnName1>
        <ColumnName2>column value</ColumnName2>
        <ColumnName3>column value</ColumnName3>
        <ColumnName4>column value</ColumnName4>
    </TableName>
</DocumentElement>
</pre></div>
<p>
Each TableName section corresponds to a single row in the table.  The following example shows reading schema
and data from an XML file using the ReadXml method of the DataSet.  Note that once the data is read into the DataSet
it is indistinguishable from SQL data -- the DataGrid binds to it just the same.
<p>

<Acme:SourceRef
  RunSample="/quickstart/aspplus/samples/webforms/data/datagrid17.aspx"
  ViewSource="/quickstart/aspplus/samples/webforms/data/datagrid17.src"
  Icon="/quickstart/aspplus/images/datagrid17.gif"
  Caption="DataGrid17.aspx"
  runat="server" />

<p>
We can also read the data and schema separately, using the <b>ReadXmlData</b> and <b>ReadXmlSchema</b> methods
of the DataSet:
<p>

<Acme:SourceRef
  RunSample="/quickstart/aspplus/samples/webforms/data/datagrid18.aspx"
  ViewSource="/quickstart/aspplus/samples/webforms/data/datagrid18.src"
  Icon="/quickstart/aspplus/images/datagrid18.gif"
  Caption="DataGrid18.aspx"
  runat="server" />

<p>
Just as the DataSet supports reader methods for XML data, it also supports writing the data.  This sample implements a utility for
selecting data from SQL and writing the result as XML data or schema text.
<p>

<Acme:SourceRef
  RunSample="/quickstart/aspplus/samples/webforms/data/xmlgen.aspx"
  ViewSource="/quickstart/aspplus/samples/webforms/data/xmlgen.src"
  Icon="/quickstart/aspplus/images/xmlgen.gif"
  Caption="XMLGen.aspx"
  runat="server" />


<!--BEGIN SECTION-->
<a name="endofsection">

<h4>Section Summary</h4>
<ol>
<li>The NGWS runtime's managed data access APIs abstract data and present the it in a consistent way regardless of its actual
source (SQL Server, OLEDB, XML, etc).
<li>To give your page access to the classes you will need to perform SQL data access, you must import the System.Data and
System.Data.SQL namespaces into your page.
<li>Populating a DataSet from a SQL query involves creating a SQLConnection, associating a SQLDataSetCommand object with the connection
that contains your query statement, and filling the DataSet from the Command.
<li>The DataGrid control supports a DataSource property that takes an ICollection type. We can set this to the result of a SQL query by assigning the DataSet's DefaultView property, which is of type DataView.
<li>The SQLDataSetCommand maintains a Parameters collection that can be used to replace variable identifiers (denoted by an "@" in front of the name) with values.
<li>When performing commands that do not require data to be returned, such as inserts, updates, and deletes, we use a SQLCommand
 instead of the SQLDataSetCommand. The command is issued by calling an Execute method, which returns the number of rows
affected.
<li>The SQLConnection must be explicitly opened when we use the SQLCommand (the SQLDataSetCommand automatically handles opening the connection for you).
Always remember to close the SQLConnection to the data model before the page finishes executing. If you do not close the connection, you may inadvertantly exhaust the connection limit while waiting for the page instances to be garbage-collected.
<li>To allow rows to be edited, the DataGrid supports an integer EditItemIndex property, which indicates which row of the grid should be editable. When this property is set, the DataGrid renders the row at that index as text input boxes instead of simple labels.
<li>The DataGrid exposes a DataKeyField property which we can set to the field name for the primary key. In the event handler wired to the UpdateCommand, we can retrieve the key name from the DataGrid's DataKeys collection.
<li>Using BoundColumn controls in the DataGrid gives you complete control over the order of the columns, as well as their ReadOnly properties.
<li>Using TemplateColumn controls in the DataGrid gives you complete control over the contents of the column.
<li>The ButtonColumn control may be used to simply render a button control in each row for that column, which may be associated with an event.
<li>A HyperLinkColumn may be added to the DataGrid's Columns collection, which supports navigating to another page when the link is clicked.
<li>When the DataGrid's AllowSorting property is set to true, it will render hyperlinks for the column headers which fire a Sort command back to the grid. We set the OnSortCommand property of the DataGrid to the handler we wish to call when the client clicks a column link.
<li>The DataSet supports ReadXml, ReadXmlData, and ReadXmlSchema methods that take a FileStream as its parameter, which can be used to populate a DataSet from an XML file.
<li>Using stored procedures can reduce the cost of performing heavy database operations in an application.
</ol>
<p>

<!-- #include virtual="/quickstart/aspplus/include/footer.inc" -->