Thomas’ Developer Blog

February 20, 2008

Many to Many repeaters

Filed under: Asp.Net Framework 2.0, sql, vb.net — Tags: , , , , , , , , — sanzon @ 2:55 am

When I created this script a while back there was nothing on the web about it.  They had some basic examples, but those examples only explained how to run a set of commands that are hard written into the code.  Let me lay out what the problem exactly is with Many to Many relationships.

 In a database information is stored into tables in which you can set specific data to certain columns for each row (record).  The problem is when you have to insert multiple entries into those rows.  For example going with the theme I have been working on.  Genres of TV series.  Obviously TV series have more than on genre normally.  To store this information we create a joint table that combines the primary key from the original table to the key for the secondary table.  Such as….

Video_ID Genre_ID
1 2
1 3
1 5
2 1
2 3
2 4
3 2
3 3

So generally Video 1 has 3 genres just like Video 2, but Video 3 has only 2 genres.  Well in a repeater we can create one command to select information from the video table.  Problem is we have to use ANOTHER command to produce information for the genre BASED OFF OF THE VIDEO_ID PRODUCED.  Well we can’t hard code a video_id if we don’t know what it is.  Well this again is where you friends parent and child nodes come into play. 

 I’m going to just go ahead and skip the code for the first repeater, since you should know how to send information to the repeater.  So let’s jump into the code.

<asp:repater ID="rptResults" runat="server">
 <ItemTemplate>
  Video Title: <%# DataBinder.Eval(Container.DataItem, "title") %>
  <asp:Label id="lblDSet_video_id" Text='<%# DataBinder.Eval(Container.DataItem, "video_id") %>' Runat="server" visible="false" />
 </ItemTemplate>
</asp:repeater>

That’s a very simple version of the base code for the main repeater.  Generally what this code does is list out the video titles along with placing the video_id in a LABEL that is HIDDEN.  It’s very important to make this label hidden!  This label is used to help retrieve the Video_ID in the next part.

 Since you know have a least we use a OnDataBinding event to bind a dynamic result for the genres!  The code is very simple in theory to my previous blog on the checkboxlist uncheck all script.  We’re using parent and child nodes again to gather information!

Personally I try to avoid using parent and child nodes when possible, since it’s not always the best method since you have to worry about changing the code behind everytime you change the HTML structure.  BUT sometimes you can’t get around it, and I find a lot of programers just tend to ignore the idea of searching through nodes.  It’s a highly useful skill to know, but just know that using nodes can have issues down to road, so make sure you document it somewhere in a comment in your code.

'Sets OdbcConnection variables to reuse {Commented more bellow}
 Private adoConn_Genre As OdbcConnection, adoRdr_Genre As OdbcDataReader
Sub Repeater_Genre_ODB(Sender As Object, e As System.EventArgs)
  Dim Dset_video_id As String = ""
  Dim adoConnStr As String, adoCmd As OdbcCommand

Dset_video_id = CType(CType(Sender, Repeater).Parent.FindControl("lblDSet_video_id"), Label).Text
  adoConnStr = 'insert your Connection String Here
  adoConn_Genre = New OdbcConnection(adoConnStr)
  adoCmd = New OdbcCommand("SELECT distinct video2genre.video_id, video2genre.genre_id, genre.name from video2genre inner join genre on video2genre.genre_id = genre.genre_id WHERE video_id ='" & Dset_video_id & "' ORDER by name", adoConn_Genre)
  adoConn_Genre.Open()

adoRdr_Genre = adoCmd.ExecuteReader(CommandBehavior.CloseConnection Or CommandBehavior.SingleResult)
  CType(Sender, Repeater).DataSource = adoRdr_Genre
 end sub

The code above is actually quite simple.  Once you place the repeater within the main repeater you have to do the following information.  Yes both the connection and reader have to be set outside the function!  This is so that the information can be set to close once the repeater is done with it!  It’ll make sense in a second, so don’t worry.

Everyone should be familar with connection strings and how to connected to a database.  Generally that whole chunk is connecting to an Odbc database, if you use an other connection method just replace the code.  Overall it’s the same method.  Once you have your connection information together you need to prepare the Video_ID so it can be used in the command string.  To get the information you use the code:

Dset_video_id = CType(CType(Sender, Repeater).Parent.FindControl("lblDSet_video_id"), Label).Text

This code basically will set the .Net nodes to their correct types with CType.  Generally you search for the nested repeaters parent, which is returned as a repater.  That repeater holds the label with the video_id, so you used findcontrol and search by ID.  That idea holds the label text, so once you have it you used CType again to return it as a label, then simply get the text!  It’s simple once you glance it over a few times.  The best part of Asp.Net is the beauty of ignoring excess HTML code!  If you notice the parent contorl is a repeater, despite having a ton of excess code and in my case it was in a Div tag, but ASP.Net will search for only .Net controls!  So it makes it so much easier to get information in this case.  So due to this we now have our ID to insert into the command string.  Then you just run the code and you have all the listed genre for that video!  It’s very simple.

Note that I did not use a parameter.  Yes ALWAYS use parameters.. but ONLY when the user inputs information.  In this case the information is gathered from self generated code that the user has no control over, so it’s safe to use a string injection. 

Ok final part.  Why did we choose to set the two variables outside the sub procedure?  Simply becasue we have to close the connection!  But if we close the connection in a binding event, then the information won’t be present to bind!  So we have to bind it afterwards!  Simply use a OnUnLoad event to close the connections afterwards.  When you’re done the code looks like this:

HTML code:<asp:Repeater ID="rptResults" runat="server">
  <ItemTemplate>
  <asp:Label id="lblDSet_video_id" Text='<%# DataBinder.Eval(Container.DataItem, "video_id") %>' Runat="server" visible="false" />
  <%# DataBinder.Eval(Container.DataItem, "title") %></a>
  Genre(s):
  <asp:Repeater ID="Repeater_Genre" OnDataBinding="Repeater_Genre_ODB" onUnLoad="Repeater_Genre_OnUnLoad" runat="server">
  <ItemTemplate>
  <%# DataBinder.Eval(Container.DataItem, "name") %>
  </ItemTemplate>
  </asp:Repeater>
  </ItemTemplate>
  </asp:Repeater>

Code Behind:
 '--Many to Many results for Genre [start]--

Private adoConn_Genre As OdbcConnection, adoRdr_Genre As OdbcDataReader

Sub Repeater_Genre_ODB(Sender As Object, e As System.EventArgs)
  Dim Dset_video_id As String = ""
  Dim adoConnStr As String, adoCmd As OdbcCommand

Dset_video_id = CType(CType(Sender, Repeater).Parent.FindControl("lblDSet_video_id"), Label).Text
  adoConnStr = 'Your Connection String
  adoConn_Genre = New OdbcConnection(adoConnStr)
  adoCmd = New OdbcCommand("SELECT distinct video2genre.video_id, video2genre.genre_id, genre.name from video2genre inner join genre on video2genre.genre_id = genre.genre_id WHERE video_id ='" & Dset_video_id & "' ORDER by name", adoConn_Genre)
  adoConn_Genre.Open()

adoRdr_Genre = adoCmd.ExecuteReader(CommandBehavior.CloseConnection Or CommandBehavior.SingleResult)
  CType(Sender, Repeater).DataSource = adoRdr_Genre
 end sub

'Close ado.net Connection and Reader
 Sub Repeater_Genre_OnUnLoad(Sender As Object, e As System.EventArgs)
  adoRdr_Genre.Close()
  adoConn_Genre.Close()
 End Sub
 '--Many to Many results for Genre [end]--

As you see it’s actually a very simple script!  Yet very powerful!  Remember to ALWAY close your connections!  Well good luck.

Advertisements

2 Comments »

  1. certainly ⅼike yօur website however you need to take a look ɑt
    the spelling on quite a few of your posts. Several
    of them are rife with spelling problems and I to fіnd it very
    bothersome to inform the truth neveгtheless Ι’ⅼl surely come again again.

    Comment by phone — September 5, 2017 @ 12:57 pm

  2. Wow! Finally I got a weblog from where I know how to truly
    obtain useful facts regarding my study and knowledge.

    Comment by Remove deleted Gravatar Image!!!! — October 21, 2017 @ 5:55 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: