Category

Visual Web Developer

• .NET, Programming, Visual Web Developer

  ASP.NET: Using Hierarchical Data with ObjectDataSource

  05.03.08 | Permalink | Comment?

  When Microsoft introduced the SqlDataSource, they made it very easy to add database connectivity to your ASP.NET pages with a minimum of programming. The SqlDataSource (and the related AccessDataSource) allow you to define database connections declaratively – that is, you define what data they will retrieve and how to manipulate it inside the web page, instead of writing code to provide these functions. Here’s a sample of what an SqlDataSource might look like:

  <asp:SqlDataSource ID="SampleDataSource" runat="server"
ConnectionString="Data Source=localhost;Initial Catalog=SampleDB;Integrated Security=True"
SelectCommand="GetSampleData" SelectCommandType="StoredProcedure">

  Since SQL Server can return multiple data sets in a single request, the SqlDataSource also made it easy to work with hierarchical data. In other words, if you have an Author record, it’s pretty simple to get a list of Book records associated with it. Microsoft has an in-depth article on this worth reading. For now we’ll just look at a simple example.

  Let’s start by assuming that we have a data source called AuthorDatasource with tables named Author and Book, and a relation between them named Author-BookRelation. We then create a FormView called AuthorFormView:

  <asp:FormView ID="AuthorFormView" runat="server"
DataSourceID="AuthorDatasource"
DataMember="Author"
DataKeyNames="AuthorID">

  Next we’ll create a DataList to display the books associated with this author:

  <asp:DataList ID="BookList" runat="server"
DataSource='<%# Container.DataItem.CreateChildView(Author-BookRelation) %>'
DataKeyField="AuthorBookID">

  Instead of assigning a DataSourceID, we’re providing the actual data using CreateChildView to create a DataView. When you provide the data this way, however, you do lose the ability to use the declared insert, update, and delete statements for the child data – you have to implement this yourself, in code. You can still use the declared commands on the parent, though.

  (Note: In this example it isn’t particularly useful to use CreateChildView – it would be trivial to create another data source and select only the books assigned to this author. It’s much nicer to be able to do this when you have multiple parent records, or parent-child-grandchild data.)

  But direct data access is the old school way of doing things. Microsoft has introduced a new datasource, the ObjectDataSource, which facilitates separating the display of data (web page), from the business logic and database layers. And ObjectDataSource does not work with hierarchical data. Or… does it?

  Actually there is a way to get it to work, and it’s not that complicated.

  Going back to our Author – Book example, let’s create a couple of objects. (I’m not even going to pretend to use a data-access layer here – we’ll pretend the data appears by magic.) First we’ll look at the Library object, which will supply the lists of authors and books:

  Public Class Library
  Public Shared Function GetAuthors() As List(Of Author)
...
    Return AuthorList
  End Function
End Class

  If you wanted to select a single author, you could create a function GetAuthorByID which would accept a parameter of the appropriate type. Next let’s look at the Author object:

  Public Class Author
  Public AuthorID As Integer
  Public AuthorName As String
  Public AuthorDescription As String
End Class

  Now we’ve defined the objects we want to use, let’s create the ObjectDataSource:

  <asp:ObjectDataSource ID="AuthorDatasource" runat="server"
SelectMethod="GetAuthors" TypeName="Biblio" DataObjectTypeName="Author">

  What does this tell us? It tells us that ASP.NET will use the Biblio class’s GetAuthors method to return a list of type Author. We can then bind our controls to the fields such as AuthorName, etc.

  So – how would we get and bind a list of books, eh, smart guy? We add one more property to our Author object:

  Public Class Author
...
  Public AuthorBooks As List(Of Book)
End Class

  A DataSource property can work with any class that implements either IEnumerable or IList (or so I’ve been told). So, updating the code we used above, we now have this:

  <asp:DataList ID="BookList" runat="server"
DataSource='<%# CType(Container.DataItem, Author).Books %>'
DataKeyField="AuthorBookID">

  I don’t know if the CType is absolutely required but it doesn’t hurt. The result – we can now painlessly display the list of books from the Author object.

  You can load up the data either by passing a Dataset object from your data access layer and then grinding through all the data, or (my favorite) you can define the XML output of your dataset with an XSD schema, use the GetXML method, and serialize this XML into the objects directly.

  Of course I’m leaving out the code to update the books, insert new records, etc., but that should be simple for any semi-intelligent programmer (right?).

  Now I don’t know everything about ASP.NET, this data isn’t a particularly good example, and I’m sure there’s plenty of naivete in what I’ve written here, but I still think this can be a useful technique to display related data using the ObjectDatasource.

  If this code helps you out, be sure to let me know in the comments.

• .NET, Programming, Visual Web Developer

  ASP.NET: Handling Custom Update Commands with ItemCommand

  04.03.08 | Permalink | 3 Comments

  When programming an ASP.NET application, it’s very simple to use the built-in Button and LinkButton controls to perform actions like adding a record to a database or saving changes. When you create one of these buttons, the HTML looks something like this:

  <asp:LinkButton ID="EditButton" runat="server" CommandName="Edit">Edit</asp:LinkButton>

  Notice the CommandName attribute. Built-in commands include Insert, Update, Delete, Edit, and Cancel. If you are using a container such as a DataGrid or a DataList, you can customize these commands by adding an event handler:

  <asp:DataList ID="AppList" runat="server" DataSourceID="AppDataSource"_
DataKeyField="AppID" OnEditCommand="AppList_EditCommand">

  But what if, instead of redefining the standard command, you want to create your own? This is very, very simple, and you implement it using the OnItemCommand event handler. The ItemCommand also applies to objects like the FormView, and adds a lot of flexibility to your applications. First add it to the container definition (a FormView in this example):

  <asp:FormView ID="AppFormView" runat="server" DataSourceID="AppDataSource"_
DataKeyNames="AppID" OnItemCommand="AppFormView_ItemCommand">

  You can do a lot of cool stuff in the Item Command procedure, but here we’re going to focus on a common problem: calculating fields before saving to the database. For example, if you are uploading a file using the FileUpload control, you can modify the Update command to save it in the database, or you may want to record the time each record is modified and the username of the person who modified it.

  To implement the various commands, edit the <name>_ItemCommand procedure in your code and add a Select statement:

  Protected Sub AppFormView_ItemCommand(ByVal sender As Object,_
ByVal e As System.Web.UI.WebControls.FormViewCommandEventArgs)

     Select Case e.CommandName

        Case "Cancel"
  …

  Let’s look at how to handle some of the standard commands, using the FormView as an example. These will be different for a DataList or other objects – that code can generally be found in the MSDN Library.

  Most of the commands have a built-in method – for example, if the command is “Update”, the FormView comes with a UpdateItem method. Cancel is an exception; to implement this command, run FormView.DataBind to replace any changed fields with the values retrieved from the database.

  Now we’re ready to create our own custom command. Let’s take a look at it first, then I’ll explain later:

  <asp:LinkButton ID="RetireButton" runat="server"_
CommandName="Retire">Retire</asp:LinkButton>

  …

     Case "Retire"

        AppDatasource.UpdateParameters("AppRetired").DefaultValue = True
      AppFormView.UpdateItem(True)

  So – the first step is the create a button or link button invoking your custom command, in this case a command to retire an application. Once the button is created, you can add a Case to the ItemCommand procedure with the name of the new command, and then fill in your code. For this example we are using an update parameter to store the new value. You can also place this value in a bound control, as we’ll see in a minute.

  There are a couple of limitations here. First, you can only use the parameter method if there are no controls bound to this field. If there are any, the parameter’s default value will be replaced by the value in the control. Second, if you will not be setting this parameter every time you update this record, then you must use a bound field. Otherwise when you update without specifying a value, the data in the database will be overwritten by the default. In practical terms this means that you’ll probably be writing this value to a bound control.

  Since we only want to mark an application as retired once, we’ll instead stick this value in a bound field, and it will be “remembered” even when we don’t supply it:

     Case "Retire"

        AppRetiredCtrl = AppFormView.FindControl("AppRetired")
      AppRetiredCtrl.Value = True
      AppFormView.UpdateItem(True)

  All we had to do here is find the control (a HiddenField) and stuff in the value. When we call UpdateItem this new value will be saved to the database.

  Customizing the built-in commands and adding your own new ones is a powerful way to build your application without writing too much extra code. If these examples help you out, please leave a note in the comments and let me know.

  Clarification: I didn’t cover handling the standard FormView commands such as “Insert”, but to be clear: You should not call a FormView.InsertItem command (for example) from inside the ItemCommand handler if the CommandName actually is “Insert”. A FormView control always executes the built-in actions for the standard events, even if you have a custom handler defined inside the ItemCommand procedure. So you don’t have to call FormView.ItemUpdate for the “Update” command, or FormView.DeleteItem for the “Delete” command. Your code is in addition to the default actions of these commands, and takes place before they are executed.

  If you are creating a custom command, then you will need to invoke the appropriate method, InsertItem or UpdateItem or whatever.

  Things are a bit different for a DataList. It isn’t as tightly integrated with its data source, and doesn’t even have methods such as InsertItem – the programmer is responsible for handling these commands himself inside ItemCommand, and will usually implement something like DataSource.Update (or .Insert, or whatever).

  I wasn’t too clear about this in my own mind and as a result I wasn’t clear in this post. Sorry about that.

• .NET, Programming, Visual Web Developer

  ASP.NET: Adding a Null Row to a Data Bound Drop-Down List

  07.10.06 | Permalink | 19 Comments

  Today’s .NET tip is a quickie (previous entries here and here): When you are using a database to fill the rows of a data bound drop-down list control, you will inevitably run into an error when the bound field is null or an empty string. You don’t want to add a null row to the list source, so what do you do?

  This one’s simple – there’s a property of drop-down lists called AppendDataBoundItems. Set this to true, and you can provide one or more static entries, with rows from the list data source appened beneath them. Sample code:

  <asp:DropDownList ID="BoundList"
runat="server"
DataSourceID="ListDataSource"
DataTextField="EntryDescription"
DataValueField="EntryID"
SelectedValue='<%# Bind("BoundItemID") %>'
AppendDataBoundItems="true">

    <asp:ListItem Value=""></asp:ListItem>

  </asp:DropDownList>

  So, what we have here is a drop-down list called “BoundList”. This list gets its rows from a datasource called “ListDataSource”, displaying the value of “EntryDescription” while binding “EntryID” to the underlying field – which is called “BoundItemID”.

  We then provide an empty list item to handle cases where no entry has been selected.

  As always, please drop a note in the comments if this code helps you out.

• .NET, Programming, Visual Web Developer

  Changing the value of a data-bound control using ASP.NET’s LinkButton Control – UPDATEDx2

  07.03.06 | Permalink | Comment?

  (UPDATE #2: When I wrote this post a couple of years ago, I thought this was a pretty brain-dead way to handle things. Turns out that I really know what I’m talking about when the subject is my own incompetence. There is a better way: create a custom update procedure to store the value before it’s saved to the database.

  Incidentally, adding JavaScript event handlers in this way can still do very powerful things. For example, I use the OnChange event on a name field to populate a “sortable” name field, changing “Graham Powell Jr.” to “Powell Jr., Graham”.

  It’s just a retarded way to handle THIS problem.)

  Or, the second entry in my unofficial series “Confessions of an ASP.NET newbie” (first entry is here).

  I was recently working on an ASP.NET 2.0 project where I wanted to be able to click an LinkButton control and have it change the values of a number of fields. Google found a number of posts on various message boards asking for help doing the same thing. The most common response was, “Why would you want to do that?”

  So I had to figure it out on my own. There are probably better ways of doing this – possibly during data validation, for instance – but this is the simplest method I could come up with.

  Caveat: This method requires JavaScript. If it’s disabled in the client’s browser, this code won’t work.

  Let’s approach this problem backwards. First we’ll create a JavaScript function that carries out the actions we want, then we’ll figure out how to call it from the LinkButton.

  This simple function takes the control ID of a checkbox and a boolean value, then sets the “checked” property of the control:

  <script language="javascript">
<!--

  function SetCheckbox(ControlID, ControlState)
{
var CheckControl = document.getElementById(ControlID);

  CheckControl.checked = ControlState;
}

  -->
</script>

  Please note that this control ID is from the JavaScript document object model, not from ASP.NET. Place this function in the <head> section of your ASP.NET page.

  Now let’s look at the settings for the LinkButton control. Normally you would use this control to initiate a PostBack for a web form, but there is also a property called OnClientClick that carries out an action in the client browser before the form is submitted (ASP.NET does this by supplying both OnClick and href attributes in the HTML sent to the client). So we just put in a call to the SetCheckbox function and provide the target field’s ID, right?

  Not so fast. In order to maintain globally unique IDs within each web page, the ClientID field of an ASP.NET control is defined both by the ID of the field itself and of any containers the field is within. This value is not obvious at design time and may change if the design is modified, so we don’t want to try to statically code it.

  Happily, there’s a simple solution: the ASP.NET page itself knows what this client ID will be, and sticks it in a property called ClientID.

  So at runtime you can use the following code to dynamically determine the client ID of the checkbox field and to set the OnClientClick property of the LinkButton control appropriately (this example assumes the checkbox is contained with a FormView control named ‘ExampleFormView’):

  Dim ExampleLinkButton as LinkButton
Dim ExampleCheckbox as CheckBox
Dim ExampleID as String

  ExampleLinkButton = ExampleFormView.FindControl("ExampleLinkButton")

  ExampleCheckbox = ExampleFormView.FindControl("ExampleCheckbox")
ExampleID = ExampleCheckbox.ClientID

  ExampleLinkButton.OnClientClick = "javascript:SetCheckbox('" & ExampleID & "', true)"

  This code is in the Page_PreRenderComplete event handler in my page because the visible property of the LinkButton is data-bound, so I need to make changes to this control’s properties after databinding is complete. In most cases you should be able to use it in the Page_Load handler. Please note the single quotes around the client ID parameter – it’s easy to lose sight of them up against the double quotes marking the string literals.

  If the target field (a checkbox, in this case) is data-bound, this function will only change the value but will not save it in the datasource. If you want to immediately save the value, just set the LinkButton’s CommandName value to “Update”. ASP.NET will first execute the client click, which will update the checkbox’s value, and will then post the changed data back to the server with instructions to update the database.

  UPDATE: I found an error in the description above. Specifically, a LinkButton always causes a PostBack round-trip, so if you don’t want to lose any changes you made in JavaScript, you must specify that the CommandName is “Update”. This will save your changes to the underlying database. If this is not what you want to do, use a HyperLink object instead of a LinkButton, and put the JavaScript command into the NavigateUrl property instead of into OnClientClick. Otherwise the code works as described.

  If you find this code helpful, please leave a note in the comments.

• .NET, Programming, Visual Web Developer

  INSERTing records into Access using ASP.NET’s FormView control

  06.20.06 | Permalink | 8 Comments

  Off and on for the past couple of weeks I’ve been using Microsoft’s free Visual Web Developer 2005 Express to redesign a little Access database-driven website at work. I had hoped to make it simpler and easier to maintain, by using standard components instead of the custom code I had been using.

  It turned out to be a little more complicated than I had thought. In particular I discovered a big problem with Access autoincrement fields. An autoincrement field is a long (32-bit) integer that is automatically assigned a unique ID when a new record is inserted. But when you are inserting records with ASP.NET, there’s no good way to retrieve this value.

  This is not as much of a problem with SQL Server, because you can create stored procedures, or even simple queries, that carry out both INSERT and SELECT statements – that is, they send and receive data in one operation. Access doesn’t do this.

  Background. In ASP.NET 2.0, there’s a free-form data bound container called a FormView. This is similar to the GridView and DetailsView objects, but it is more flexible, as you are not limited in how you can lay out the controls within it (text boxes, drop-down boxes, etc.).

  Each FormView contains templates for different views, such as an Item view (read-only), and ItemEdit view (updates existing records), and an ItemInsert view (just what it says). Each template contains markup defining the data fields and their layouts.

  In my case, this FormView is connected to a control called an AccessDatasource control. This datasource has the location of the data file and SQL statements for the SELECT, UPDATE, INSERT, and DELETE procedures. This control also has events that are triggered during these procedures, which will become important when we get to the code.

  Now, most ASP.NET pages only post data back to themselves, but I am using separate pages for viewing data and for editing it, and I’m using a standard HTML input field to pass the ID between them. This is called “cross-posting” in ASP.NET, and I’m sure it’s a terrible idea and an example of my inexperience and general cluelessness, but I have good reasons for doing it that way.

  To handle this, my Page_Load event handler contains the following code (all examples in VB):

  Dim IDParam As New Parameter

  If Not IsPostBack Then
 If String.IsNullOrEmpty(Request.Form("ID")) Then

    ServerFormView.ChangeMode(FormViewMode.Insert)
 Else

    IDParam = ServerDatasource.SelectParameters.Item("ID")
  IDParam.DefaultValue = Request.Form("ID")
 End If
End If

  This FormView control is set to default to Edit mode. The first line basically says that if no ID form field is suppled, put the FormView control in Insert mode. If a parameter is supplied, it’s added to the data source. I could have used a FormParameter in the data source definition, but I want to be able to filter in other situations, as we’ll see in a minute.

  INSERTing records. So let’s assume we are now in Insert mode. We fill in all the fields and click an Insert button to add the new record to the database. What happens next?

  Among other things, the AccessDatasource_Inserting event fires. This event is raised once an INSERT procedure has started, but before the record is actually inserted. Here’s the code to handle this event – note that “e” is the event arguments passed to this procedure automatically:

  Dim InsertCommand As DbCommand
Dim InsertConnection As DbConnection
Dim InsertTransaction As DbTransaction

  InsertCommand = e.Command
InsertConnection = InsertCommand.Connection

  InsertConnection.Open()

  InsertTransaction = InsertConnection.BeginTransaction

  InsertCommand.Transaction = InsertTransaction

  All we’re really doing here is starting a transaction. Although my testing indicates that this may not always be required, using transactions guarantees that the data source won’t be polluted by other database operations during this insert.

  After this event completes, the record is inserted and the AccessDatasource_Inserted event fires. This is where the work gets done:

  Dim InsertCommand As DbCommand
Dim InsertTransaction As DbTransaction
Dim ID As Int32
Dim IDParam As New Parameter

  InsertCommand = e.Command
InsertTransaction = InsertCommand.Transaction

  InsertCommand.CommandText = "SELECT @@IDENTITY AS [ID]"
ID = InsertCommand.ExecuteScalar()

  InsertTransaction.Commit()

  IDParam = ServerDatasource.SelectParameters.Item("ID")
IDParam.DefaultValue = ID

  ServerFormView.ChangeMode(FormViewMode.Edit)
ServerDatasource.DataBind()

  So, what are we doing here? First, we’re grabbing the INSERT command and the transaction we set in the Inserting event handler. Then we’re reusing the command to run a SELECT statement that retrieves the @@IDENTITY (autoincrement) value for the last record inserted. Once that is complete, we commit the transaction.

  Next we set the same parameter that we used in the Page_Load procedure to the value of the new row. We no longer want to be in Insert mode, so we change the FormView’s mode to Edit. Lastly, we refresh the data using the DataBind command.

  And that’s all there is to it. Just make sure that the FormView’s ItemEdit template has a form field named “ID” that is bound to the data source’s “ID” field, and you are good to go.

  If you’re code objects to manipulate Access data, you will run into a similar problem. You can handle it by handling the DataAdapter’s RowUpdated event. It’s similar to the procedure above.

  If you get this to work for you, please leave a note in the comments so I can see if anyone’s actually using this code.

My Boog Pages

feeds

• Entries (RSS)
• Comments (RSS)

Copyright

Designed by Upstart Blogger.