| Programmer to ProgrammerTM | |||||
| |
|||||
|
| |
||||
|
|
|
|
|
|
|
|
|
|
| |||||||||||||||||||
| The ASPToday
Article December 19, 2001 |
Previous
article - December 18, 2001 |
||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||
| ABSTRACT |
| ||||||||||||||||||||||||||||||
|
| |||||||||||||||||||||||||||||||
| Article Discussion | Rate this article | Related Links | Index Entries | ||||||||
| ARTICLE | |||||||||||
Remember when creating an ASP form based on multiple, joined tables was a hassle? The troubles ranged from navigating the perils of client-side form fields, to "tricking" your way through the woes of finding out which row you were modifying. Finally, when you went to update the database, it was a single row at a time. If you wanted to post multiple rows, say, using XML, you typically had to instantiate several objects and configure streams. If your client had Internet Explorer, this was doable, though not at all pretty. Netscape users? Plan to extend the project schedule, and devise a clever workaround or two. In any case, adding new tables or data structure changes to the mix meant more coding time, measured in days or weeks.
With ASP.NET, you can make many of these multi-table issues easier using DataGrids. These small miracles are one of the most powerful of all server-side controls, providing flexible presentation, ordering and manipulation of information from SQL Server and any other data source you can imagine. When you combine their advantages with the capabilities of the DataSet object, the tough now becomes the trivial. Row-oriented adds, changes and deletes are simple. The database update is now a matter of using a DataSet property, throwing in some XML and XSL, and sending a single batch of data to the database.
To accomplish these tasks, I'll be covering the following in this article:
You should be familiar with the following:
| Select.sql | SQL Server scripts for stored procedures GetAuthor, GetRoyalty and GetTitleData, which retrieve data from the Authors, TitleAuthor, roysched and Title tables based on a given TitleID |
| Update.sql | SQL Server scripts for stored procedure UpdateTitle, which updates the Authors, TitleAuthor and Royalty tables using XML |
| TitlePage.aspx | WebForm discussed in this article, containing a form to edit author and royalty information |
| TitlePage.aspx.vb | The CodeBehind page for TitlePage.aspx, containing all functions and subroutines listed in this document |
This article uses a data entry form based on the Pubs database that ships with SQL Server. The WebForm allows you to edit author and royalty Information related to a particular title, and then commit those changes to a database.
The steps involved are:
This process is illustrated by the following diagram. All processes take place on the Web Server, with the exception of the SQL Command and data retrieval from SQL Database tables.
This diagram was created with SQL Server Enterprise Manager, in the Diagrams section of the Pubs database.
As the above diagram shows, we have four SQL Server tables in our example. They are related as follows:
Our ASPX form is based on the table shown above in the SQL Server sample database Pubs. The Titles table has a one-to-many relationship with the Royalty Schedule (roysched) table. Titles and authors have a many-to-many relationship; an author can write more than one book, with more than one co-author. One book can be by many authors. The junction table titleauthor enforces this relationship, and allows a particular title to split its royalties among each author, by percentage.
To represent the database structure, I use a single DataSet object that contains the three business entities as DataTables ( Authors, Titles and Royalties). Note that these DataTables are not identical to their database counterparts. The only rule that we have to follow is to make the column name is the same as those retrieved from the database. In this case, the column names match the names pulled back from the stored procedures.
The DataSet definition is created in the following function:
Private Function BuildDataSet() As DataSet
Dim myDataSet As New DataSet()
Dim Table As DataTable = New DataTable("Title")
Dim DataColumn As New System.Data.DataColumn()
'---Create the table of Column Meta Data for the adhoc report
With Table.Columns
.Add("TitleID", GetType(System.Int32))
.Add("Title", GetType(System.String))
.Add("Type", GetType(System.String))
.Add("Price", GetType(System.Double))
.Add("YTD_Sales", GetType(System.Double))
.Add("PubDate", GetType(System.DateTime))
End With
myDataSet.Tables.Add(Table)
The first table contains the title information for the page. The column names correspond to those returned from the GetTitleData stored procedure (see the SQL Scripts in the code download).
For the Royalty DataTable, I included an Identity field, RowIndex , to make it easier to update and remove rows.
Table = New DataTable("Royalty")
Dim Keys(0) As DataColumn
Dim NewDataColumn As New System.Data.DataColumn()
NewDataColumn.ColumnName = "RowIndex"
NewDataColumn.DataType = GetType(System.Int32)
NewDataColumn.AllowDBNull = False
NewDataColumn.AutoIncrement = True
NewDataColumn.AutoIncrementSeed = 0
NewDataColumn.AutoIncrementStep = 1
With Table.Columns
Keys(0) = NewDataColumn
.Add(NewDataColumn)
.Add("lorange", GetType(System.String))
.Add("hirange", GetType(System.String))
.Add("royalty", GetType(System.String))
End With
Table.PrimaryKey = Keys
The final table, Author , includes an Identity field, RowIndex , and the Royalty Percentage from the junction table, TitleAuthor. Since we are looking at the authors for a single title record, we can put the non-key attributes from the join table into the Authors DataTable.
Table = New DataTable("Author")
NewDataColumn = New System.Data.DataColumn()
NewDataColumn.ColumnName = "RowIndex"
NewDataColumn.DataType = GetType(System.Int32)
NewDataColumn.AllowDBNull = False
NewDataColumn.AutoIncrement = True
NewDataColumn.AutoIncrementSeed = 0
NewDataColumn.AutoIncrementStep = 1
Keys(0) = NewDataColumn
With Table.Columns
.Add(NewDataColumn)
.Add("au_id", GetType(System.String))
.Add("au_lname", GetType(System.String))
.Add("au_fname", GetType(System.String))
.Add("phone", GetType(System.String))
.Add("royaltyper", GetType(System.Int32))
.Add("contract", GetType(System.Int32))
End With
Table.PrimaryKey = Keys
myDataSet.Tables.Add(Table)
Return myDataSet
End Function
The Title Details page, TitlePage.aspx , allows you to Add , Edit and Delete royalty or author information. Warning: I have not put validation controls in the code. Enter invalid values at your own risk ( hint: the Author ID should be in Social Security Number (SSN) format. For those of you who don't live in the United States, it's a Federal Identification Code in the format "nn-nn-nnnn").
To install and run the TitlePage example, do the following:
If you run the project from the design environment, set the value of TitleID in the Page_Load event. Otherwise, when you launch the browser, use the following URL (if running on your local machine): http://localhost/Pubs/TitlePage.aspx?titleid=xxxx, where xxxx is the ID of the title you want to edit (PC1035 is a good example)
The following class libraries must be included with the Imports keyword at the top of the CodeBehind file (TitlePage.aspx.vb)
| System.Data.SqlClient | SQL Server data access libraries |
| System.IO | Used for the StringReader that holds the DataSet's XML |
| System.Xml.XPath | Provides the XpathNavigator and XpathDocument |
| System.XML | XML and XSL functionality, including the Transform |
When the TitlePage.aspx form loads, we first get the ID of the title that we want to edit. Then, if the page is not posted back (the first time it loads), we build the DataSet structure, and fill each table with columns from the given stored procedure. The FillDataSet function uses a SqlDataAdapter , and returns the DataSet passed to it after filling the specified DataTable.
Dim TitleID As String = Request.QueryString("TitleID")
Dim myConnection As SqlConnection
Dim TitleData As New DataSet()
If Not Page.IsPostBack Then
statuslabel.Text = ""
TitleData = BuildDataSet()
myConnection = GetConnection()
FillDataSet("exec GetTitleData @TitleID='" & TitleID & "'",
myConnection, TitleData, "Title")
FillDataSet("Exec GetRoyalty @TitleID='" & TitleID & "'",
myConnection, TitleData, "Royalty")
FillDataSet("Exec GetAuthors @TitleID='" & TitleID & "'",
myConnection, TitleData, "Author")
myConnection.Close()
Next, we bind the DataGrids to the appropriate DataTable objects, and populate the controls at the top of the page with formatted title information. During the binding process, each DataTable object is associated with a DataGrid. In my example, I've set each grid up manually with datafields that correspond to the DataTable column names. You can also create the DataGrid columns dynamically.
RoyaltyGrid.DataSource = TitleData.Tables("Royalty").DefaultView
RoyaltyGrid.DataBind()
AuthorGrid.DataSource = TitleData.Tables("Author").DefaultView
AuthorGrid.DataBind()
With TitleData.Tables("Title")
TitleLabel.Text = .Rows(0)("Title").ToString
TypeLabel.Text = .Rows(0)("Type").ToString
PriceLabel.Text = System.String.Format("{0:c}", .Rows(0)("Price"))
YTDSalesLabel.Text = System.String.Format("{0:c}", .Rows(0)("YTD_Sales"))
PublicationDateLabel.Text = System.String.Format("{0:d}",.Rows(0) ("PubDate"))
End With
In the above example, DataSource represents the Royalty DataTable object. Here, we're saying "populate the grid with the records in the Royalty DataTable". The DefaultView property returns a DataView object, which is what is actually bound to the grid. DataSets can have many associated "views" or filtered, sorted and modified sets of data. For more information on this, see the DataTable.DefaultView Property topic in the Visual Studio.NET help file.
Finally, we persist the TitleData DataSet to session, where it will be stored between page requests.
Session("TitleData") = TitleData
End If
End Sub
I wrote Edit, Update and Delete handlers for the RoyaltyGrid and the AuthorGrid controls. The Edit and Update procedures are fairly basic. Edit sets the EditItemIndex of the grid to the chosen row, and Update pushes the data from the Edit mode controls to the appropriate DataTable objects. My approach to deleting a record from a DataTable, like the Royalty table, is to remove the row from the table completely. Note that with this approach, the row is gone permanently, and can't be recovered (unless it's stored in the database). If you use the Delete method instead, you can recover the row from the DataTable (by invoking the DataRow's RejectChanges method). For more information on this topic, see DataRow.Delete Method, DataRow.RejectChanges Method, and DataRowCollection.Remove Method in the Visual Studio.Net Help File.
The code below uses the RoyaltyRowID (an identity column I defined in the DataTable that is not present in the database table), to remove the active row:
Sub DoRoyaltyItemDelete(ByVal objsource As Object, ByVal objargs As DataGridCommandEventArgs)
Dim TitleData As DataSet = GetTitleData()
Dim RowIndexControl As Label = CType (objargs.Item.FindControl ("RoyaltyRowID"), Label)
Dim RowIndex As Int32 = CType(RowIndexControl.Text, Int32)
TitleData.Tables("Royalty").Rows.Remove(TitleData.Tables("Royalty").Rows.
Find(RowIndex. ToString))
RoyaltyGrid.DataSource = TitleData.Tables("Royalty").DefaultView
RoyaltyGrid.DataBind()
End Sub
You might be wondering how I'm going to let the database know that I want to delete this row. As you'll see later, I will delete all of the royalty records in the database table, then add only those rows that are in the Royalty DataTable. This indirectly accomplishes the delete. I could have used the Delete method of the relevant DataRow, but I won't be using the DataSet's capabilities to accept or reject rows that have been added, changed or deleted. For information about how the DataSet can handle this automatically, see the DataSet.AcceptChanges Method and the DataRow.RowState Property topics in Visual Studio.Net Help.
Once you have the DataSet populated and ready to commit to the database, you have several alternatives, including:
If we were to call GetXML on our TitleData DataSet for Title "PC1035", we'd see something similar to the following:
<NewDataSet>
<Title>
<Title>But Is It User Friendly?</Title>
<Type>popular_comp</Type>
<Price>22.95</Price>
<YTD_Sales>8780</YTD_Sales>
<PubDate>1991-06-30T00:00:00.0000000-04:00</PubDate>
<title_id>PC1035</title_id>
</Title>
<Royalty>
<RowIndex>0</RowIndex>
<lorange>0</lorange>
<hirange>2000</hirange>
<royalty>10</royalty>
<title_id>PC1035</title_id>
</Royalty>
<Author>
<RowIndex>0</RowIndex>
<au_id>238-95-7766</au_id>
<au_lname>Carson</au_lname>
<au_fname>Cherylv/au_fname>
<phone>415 548-7723</phone>
<royaltyper>100</royaltyper>
<contract>1</contract>
</Author>
</NewDataSet>
The DataSet's DataSetName, NewDataSet , becomes the root element of the XML. Each DataTable is represented by an element named by the DataTable.Name, which contains the table's column names and values.
In my example, I chose to use the default element-centric mapping. You can use attribute-centric mapping in the DataSet. To do so, you must set the ColumnMapping property of each DataColumn in each table to MappingType.Attribute. Either method is valid, and depends a lot on your company (or team) standard, or on the format required for stored procedures or external data handlers (like WebServices). I chose to use element mapping to show how easy it is to transform the DataSet output to the specific format to any format using XSL. This approach does give you more output flexiblity, but the XSL transformation does incur extra overhead. I suggest that you perform load and stress tests in your particular application and environment to determine the specific impact. A direct update might be faster, but you lose some adaptability.
XSL allows us to apply simple rules, functions and manipulations to the data to produce almost any format. In this case, our SQL Server Stored procedure requires an attribute-centric mapping, with some slight modifications to the DataSet's XML. The stored procedure requires that the above XML be converted to this format:
<SAVE> <TITLE TITLETEXT="" PRICE="22.95" YTD_SALES="8780" PUBDATE="06/30/1991" TITLE_ID="PC1035"></TITLE> <ROYALTY LOW_RANGE="0" HIGH_RANGE="2000" ROYALTY="10"></ROYALTY> <ROYALTY LOW_RANGE="2001" HIGH_RANGE="3000" ROYALTY="12"></ROYALTY> <ROYALTY LOW_RANGE="3001" HIGH_RANGE="4000" ROYALTY="14"></ROYALTY> <ROYALTY LOW_RANGE="4001" HIGH_RANGE="10000" ROYALTY="16"></ROYALTY> <ROYALTY LOW_RANGE="10001" HIGH_RANGE="50000" ROYALTY="18"></ROYALTY> <AUTHOR AUTHOR_ID="238-95-7766" FIRST_NAME="Cheryl" LAST_NAME="Carson" PHONE="415 548-7723" CONTRACT="1" ROYALTYPERCENT="100" AU_ORDER="1"></AUTHOR> </SAVE>
To convert the DataSet's XML to this format, we apply the Title.XSL stylesheet as follows:
The document opens with some boilerplate XSL declarations, and an output encoding tag that makes the resulting XML easier on the eyes if you're using an editor (like Marrowsoft's Xselerator).
For a better description of these elements, check out http://www.zvon.org/xxl/XSLTutorial/Books/Book1/
The first <xsl:template match> statement creates the SAVE elements, wrapped around the XML that we generate with the remaining templates.
<?xml version="1.0" ?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output encoding="utf-8" indent="no" /> <xsl:template match="/"> <SAVE> <xsl:apply-templates /> </SAVE> </xsl:template>
The next part begins at the root element of the DataSet XML, NewDataSet
<xsl:template match="NewDataSet">
Now, for each Title element in the input XML, we create an element named TITLE with attributes that correspond to the elements under Title. Note that I am formatting the date in PubDate just to show how it is done. PubDate is already in a SQL Server compliant form, but if you were updating another DBMS or persisting the XML to another data source, you might wish to convert the date.
<xsl:for-each select="Title"> <xsl:element name="TITLE"> <xsl:attribute name="TITLETEXT"> <xsl:value-of select="title" /> </xsl:attribute> <xsl:attribute name="PRICE"> <xsl:value-of select="Price" /> </xsl:attribute> <xsl:attribute name="YTD_SALES"> <xsl:value-of select="YTD_Sales" /> </xsl:attribute> <xsl:attribute name="PUBDATE"> <xsl:value-of select="concat(substring(PubDate, 6,2),'/',substring(PubDate,9,2),'/',substring(PubDate,1,4))" /> </xsl:attribute> <xsl:attribute name="TITLE_ID"> <xsl:value-of select="title_id" /> </xsl:attribute> </xsl:element> </xsl:for-each>
Royalty elements get the same treatment. We are creating a ROYALTY element (note the difference in case) with attributes from the input XML.
<xsl:for-each select="Royalty">
<xsl:element name="ROYALTY">
<xsl:attribute name="LOW_RANGE">
<xsl:value-of select="lorange" />
</xsl:attribute>
<xsl:attribute name="HIGH_RANGE">
<xsl:value-of select="hirange" />
</xsl:attribute>
<xsl:attribute name="ROYALTY">
<xsl:value-of select="royalty" />
</xsl:attribute>
</xsl:element>
</xsl:for-each>
Finally, we iterate through all Author elements in the input XML, creating AUTHOR elements that contain the attributes found in the Authors DataTable. Note that the AU_ORDER element is not found in the input XML, but is generated by the XSL stylesheet with the position() function. This function returns the numeric order of a particular Author element in the list of Author elements.
<xsl:for-each select="Author">
<xsl:element name="AUTHOR">
<xsl:attribute name="AUTHOR_ID">
<xsl:value-of select="au_id" />
</xsl:attribute>
<xsl:attribute name="FIRST_NAME">
<xsl:value-of select="au_fname" />
</xsl:attribute>
<xsl:attribute name="LAST_NAME">
<xsl:value-of select="au_lname" />
</xsl:attribute>
<xsl:attribute name="PHONE">
<xsl:value-of select="phone" />
</xsl:attribute>
<xsl:attribute name="CONTRACT">
<xsl:value-of select="contract" />
</xsl:attribute>
<xsl:attribute name="ROYALTYPERCENT">
<xsl:value-of select="royaltyper" />
</xsl:attribute>
<xsl:attribute name="AU_ORDER">
<xsl:value-of select="position()" />
</xsl:attribute>
</xsl:element>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
When you click the Save button on the form, the SaveQuery procedure is called. First, we get the DataSet from Session. Then, we obtain the path of the "Title.XSL" file. Next, we create a SQLDataReader, which is basically a "forward-only" cursor. I use this to return a status from the database, and for a single-row return, it's pretty efficient. Check the Visual Studio.NET Help Topic SQLDataReader Class for a more detailed description.
Next, I pass the DataSet's XML, XSL Path, and an SQLConnection to the UpdateDatabase procedure, where the real work is done.
Private Sub SaveQuery()
Dim TitleData As DataSet = GetTitleData()
Dim strReplace As String
Dim TitleID As String = Request.QueryString("TitleID")
Dim strCurrentPath As String = Request.PhysicalPath
Dim strXSLPath As String = Left(strCurrentPath, InStrRev(strCurrentPath,
"\")) & "Title.xsl"
Dim SQLDataReader As SqlDataReader = UpdateDatabase (TitleData.GetXml, strXSLPath, GetConnection())
Try
While SQLDataReader.Read()
statuslabel.Text = (SQLDataReader("return"))
End While
Catch
End Try
End Sub
UpdateDatabase is a surprisingly short routine:
Public Function UpdateDatabase(ByVal XML As String, _
ByVal xslPath As String, _
ByRef Con As SqlConnection) As SqlDataReader
First, we create an XMLTextReader object, which holds the DataSet's XML, once we've converted that to a StringReader.
Dim objXTReader As New XmlTextReader(New StringReader(XML))
Now we create an XSLTransform object, and load the Title.XSL path:
Dim objTransform As New Xsl.XslTransform()
objTransform.Load(xslPath)
The next bit of code creates the necessary objects for a Transform: an XpathDocument , which takes in the XmlTextReader we created, an XPathNavigator for that document, and an XMLReader to receive the results of the transform.
Dim objXPDoc As New XPathDocument(objXTReader)
Dim objxpnav As XPathNavigator
objxpnav = objXPDoc.CreateNavigator
Dim objReader As XmlReader
The actual transformation uses the XPathNavigator to the XML from the DataSet and pushes the results into the XmlReader.
objReader = objTransform.Transform(objxpnav, Nothing)
Finally, we move to the content of the newly-transformed XML, and execute our stored procedure:
objReader.MoveToContent()
Return FillDataReader("EXEC UpdateTitle @XML='" &
Replace(objReader.ReadOuterXml.ToString, "'",
"''") & "'", Con)
objReader.Close()
objXTReader.Close()
End Function
The stored procedure UpdateTitle has only one parameter ( @XML). If the business logic, database logic, or data structures changes, we only have to modify our XSL stylesheet. No new parameters need to be defined, and the stored procedure interface will not have to change.
Since I've created this entire example myself, I had the flexibility to write the stored procedure any way that I wished. In the real world, however, we often have to conform to someone else's standard and use existing procedures. With the technique described above, we can create any XML format we need.
The procedure begins by creating a pointer to the XML document, loaded from the parameter @XML.
declare
@TitleID char(6)
declare @idoc int
exec sp_xml_preparedocument @idoc OUTPUT, @XML
--push XML items to declare var's
SELECT
@TitleID=TITLE_ID
FROM openxml (@idoc, '/SAVE/TITLE',1)
with (
TITLE_ID char(6)
)
if @@error != 0 goto ERR_HANDLER
In the code above, we extract the TITLE_ID attribute from the class='codeintext'>TITLE element, and store it for use later.
The rest of the procedure operates inside a transaction. Remember earlier that I said we were going to handle deletes by removing all matching records from a table, then inserting only the active ones? A transaction is critical for this to work. In addition, since we are updating related child data, we don't want to perform half an update in case of error.
BEGIN TRANSACTION
DELETE FROM roysched WHERE title_id=@titleid
if @@error != 0 goto ERR_HANDLER
INSERT INTO roysched (title_id,
lorange,
hirange,
royalty)
SELECT
@titleID,LOW_RANGE,HIGH_RANGE,ROYALTY
FROM openxml (@idoc, '/SAVE/ROYALTY',1)
with (LOW_RANGE INT,
HIGH_RANGE INT,
ROYALTY INT)
if @@error!=0 goto ERR_HANDLER
The above code handles the "update" (insert and delete) to the Royalty table (Roysched), by selecting from the ROYALTY element as though it were a table.
Next, we're going to create a temporary table, #Authors to process author information. Once the temporary table is created, it can be used for future join operations without having to parse the XML document multiple times. It also makes the syntax easier on the eyes. Plus, we can dispose of the XML document handler and lighten the load on SQL Server a bit.
create table #Authors
(
Author_ID char(11),
LastName char(40),
FirstName char(40),
phone char(15),
RoyaltyPercent int,
Auth_Order int,
contract bit)
INSERT #Authors
(Author_ID,LastName,FirstName,phone,RoyaltyPercent,Contract,Auth_Order)
SELECT AUTHOR_ID,LAST_NAME,FIRST_NAME,PHONE,ROYALTYPERCENT,CONTRACT,AU_ORDER
FROM openxml (@idoc, '/SAVE/AUTHOR',1)
with (AUTHOR_ID CHAR(11),
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(20),
PHONE VARCHAR(15),
ROYALTYPERCENT int,
AU_ORDER int,
CONTRACT BIT)
if @@error != 0 goto ERR_HANDLER
-- remove XML document handle
exec sp_xml_removedocument @idoc
-- remove Author/Title associations
INSERT INTO authors (au_id,
au_lname,
au_fname,
phone,
contract)
SELECT Author_ID,LastName,FirstName,phone,Contract
FROM #Authors
WHERE AUTHOR_ID not in (SELECT au_id FROM authors)
if @@error != 0 goto ERR_HANDLER
UPDATE Authors
SET au_lname=t.LastName
,au_fname=t.FirstName
,phone=t.PHONE
,contract=t.CONTRACT
FROM #Authors t inner join Authors a on a.au_id=t.Author_id
if @@error != 0 goto ERR_HANDLER
-- Handle junction table (title/authors)
-- 1. Clear existing associations
-- 2. INSERT new associations
DELETE titleauthor
WHERE au_id in (SELECT AUTHOR_ID FROM #Authors)
if @@error != 0 goto ERR_HANDLER
INSERT titleauthor(au_id,title_Id,royaltyper,au_ord)
SELECT Author_ID,@titleid,RoyaltyPercent,Auth_Order FROM #Authors
if @@error != 0 goto ERR_HANDLER
Finally, we commit the changes (any errors were routed to the error handling routine below). We send back the last update time in a single-column rowset.
COMMIT SELECT 'SUCCESS: Record updated on ' + cast(getdate() as varchar(20)) as [return] -- drop table #authors ERR_HANDLER: ROLLBACK TRANSACTION -- drop table #authors SELECT 'Error:' + CAST(@@error as varchar(20)) as [return]
In this article, we've covered a lot of material, and gone through many stages of data manipulation before arriving at our destination - a flexible, easy-to-manage database update routine for multiple tables. The DataGrid , DataSet and DataTable objects are wonderfully powerful (and reliable) tools that facilitate the processes I described above. I've used the above techniques in production applications (with validation, of course), and have seen very few problems. Unfortunately, I don't have performance metrics or concrete comparisons of this method compared to others. In general, though, while it's probably not the fastest way to accomplish the task, it is very flexible.
There are a few changes you may want to consider making to the code, depending on the nature of your application. It is possible to post only changed , deleted or added records from DataTables, instead of posting the entire DataSet's XML.
The DataSet provides the GetChanges method, which returns a DataSet object containing modified, added or deleted rows, depending on the RowState parameter. For example, to get the XML for all added rows in a DataSet, try this:
DatasetName.GetChanges(RowState.Added).GetXML()
The XML returned from this subset is identical to that of the entire DataSet. If you only want to send down certain categories of rows, use this technique.
This technique will work for any number of tables in the same dataset, or with merged datasets (although these are tricky).
The DataSet object also allows you to create relationships between parent and child tables. In this instance, we might create one between the Title DataTable and the Royalty Table. If we added a DataTable that corresponded to the TitleAuthors SQL table, we could represent the relationship between Titles and Authors.
For the purposes of this screen, however, we are dealing with a single title, which we know, and records that are all related to that title (authors and royalties). We don't need to explicitly retrieve Child Rows (which Relations allow) in the front end, and we aren't creating any hierarchical structures. In this case, it's easier to NOT use them.
Note: When you establish relations between DataTables, the XML returned from the DataSet's GetXML () method is nested according to the hierarchy established by the Relations. Again, for our purposes it doesn't matter what the XML looks like; we transform it to suit our needs.
The best reference around for ASP.NET, including extensive coverage of DataGrid, DataSet, DataTable and XML objects, is the Wrox press book "Professional ASP.NET". The knowledge in this book formed the foundation for the code I've written.
For a tutorial on SQL Server's OpenXML , go to http://www.perfectxml.com/articles/xml/openxml.asp
For XSL assistance, read The XSL Companion, by Neil Bradley (Addison-Wesley, 2000)
There is an XSL tutorial at http://www.zvon.org/xxl/XSLTutorial/Books/Book1/
I use Marrowsoft Xselerator (http://www.marrowsoft.com/) almost exclusively to create my XSL style sheets. It's quick, customizable, and shows the results of transforms in a variety of modes. It also allows you to specify a particular implementation to use when running transformations.
For more DataGrid information, check out http://www.123aspx.com/directory.aspx?dir=105
|
| |||||||
| |||||||||||||||
|
| ASPToday is brought to you by
Wrox Press (http://www.asptoday.com/OffSiteRedirect.asp?Advertiser=www.wrox.com/&WROXEMPTOKEN=24242ZvMHOxryaV50EFuA2SBS2).
Please see our terms
and conditions and privacy
policy. ASPToday is optimised for Microsoft Internet Explorer 5 browsers. Please report any website problems to webmaster@asptoday.com. Copyright © 2002 Wrox Press. All Rights Reserved. |