Today I battled with some interesting problems writing changes to a hierarchical datatable to a SQL Server database. Hopefully by writing about this I can save you some time.

You may need the public key token for purposes such as registering an HTTP module.  Use the strong naming tool with the -T option to extract the public key.  Be sure to call sn from the command line that comes with the .NET framework SDK:

C:\Program Files\telerik\r.a.d.controlsQ4 2006\NET2\bin>sn -T radupload.net2.dll

Microsoft (R) .NET Framework Strong Name Utility  Version 2.0.50727.42
Copyright (c) Microsoft Corporation.  All rights reserved.

Public key token is b4e93c26a31a21f0

I was doing some work the other day, and found that I had the need to implement a template programatically (rather than creating it at design time). I love the internet search engines, and the (sometimes overwhelming) amount of information you can usually find on any subject, but there wasn't a whole lot on this subject. To make matters worse, the articles I did find hinted around the edges of what I was looking for, but none seemed to describe what I needed to do very closely - they all addressed the aspect of the problem that they were interested in solving. So, at the risk of introducing one more post that addresses the issue that I was trying to solve, perhaps this post will help you.

The basic idea behind using a template is that you can place controls into it that are bound to data at run-time. In a very general sense, that's what a grid control does - you provide a list of columns you're interested in, hook the grid up to a datasource, issue a databind command, and the data gets displayed where you want it to be. The next step is to take your own asp.net controls of choice and put them into the itemtemplate or edittemplate for a column. If you know what column you're binding to at run-time, the task is fairly straightforward, and we've all done it countless times.

The problem I was trying to solve was that I had a control (the radRotator control by telerik) that I needed to place an unknown number of copies (at design time) onto a web page, each of which would dind to a differently-named column in the data table it was attaching to. Yes, I probably could have created a bunch of different controls, and altered the "visible" property, but that wasn't how I wanted to skin this cat. Doing it the way I wanted to, however, required that I instantiate the controls dynamically at run time, and populate the control's template with the controls I wanted to use in the display, which would themselves bind to data columns that varied from instance to instance.

The first step is to create a helper class that creates your template. The template in this case is a very simple one, containing only a label control which will take as its text the name of a color, and change the background color of the label to that same color. The class looks like this:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

class RotatorTemplate : ITemplate { string columnName; public RotatorTemplate(string column) { columnName = column; } public void InstantiateIn(Control c) { Label lbl = new Label(); lbl.DataBinding += new EventHandler(OnDisplayDataBinding); c.Controls.Add(lbl); } void OnDisplayDataBinding(object sender, EventArgs e) { Label lbl = (Label)sender; RadRotatorFrame iContainer = (RadRotatorFrame)lbl.NamingContainer; DataRowView drv = ((DataRowView) iContainer.DataItem); lbl.Text = drv[columnName].ToString(); lbl.BackColor = System.Drawing.Color.FromName(lbl.Text); } }

What's going on here is that when the constructor is called, the name of the column that we're going to bind to is passed in to the constructor, and stored in the local variable "columnName" at line 6. The InstantiateIn method is called by the control wrapping the template each time it needs a new copy of the template contents. In our example, we create a new label, hook up the OnDisplayDataBinding event handler. Note that there's no magic in the name internal to the helper class - I named it that because it's descriptive of what the routine does. The only important piece here is that the "lbl.DataBinding" event be specified properly. You should also realize that you could create and add any number of controls of different types here, you're not limited to a single control. Finally, we add the control to the controls collection of whatever control requested the new template instance.

The most interesting part is the event handler. I don't see how this helper class can avoid having a lot of knowledge about the environment it's working in, because of the code on line 17 that typecasts the naming container to be of type RadRotatorFrame - though I suspect that the RotatorFrame descends from a more generic class that still has  a dataitem property; in any event, this solved my immediate problem, I'll look for the generic class later, and you should just be aware that you'd substitute your own naming container in the typecast.

The real breakthrough for me was the realization that the DataItem was available to be cast into a dataRowView, which I could then extract the desired data from, and set the text of the control. Because I was writing this for a very specific application, I knew I'd always be getting a color name, so I also took the opportunity to set the label's background color. As a general rule, you'd want to do better data checking before making the assignment on line 20, though it turns out that an attempt to convert a non-color into a color fails gracefully, and returns white.. Also, if you're adding more that one control in the InstantiateIn event, you'll want to name them when you create them, so that you can find them in the databinding event.

That's the helper class. After that, the rest of the process is trivial. As an example, say you wanted to hook up a table that contains proverbs. You could do it like this (and here, I'm populating the template of a radRotator control, but the containing control could be anything that would take a template):

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

protected void Page_Load(object sender, EventArgs e) { DataTable proverbsTable = new DataTable(); proverbsTable.Columns.Add("proverb"); proverbsTable.Rows.Add("A stitch in time saves nine"); proverbsTable.Rows.Add("A rolling stone gathers no moss"); proverbsTable.Rows.Add("The early bird catches the worm"); proverbsTable.Rows.Add("A penny saved is virtually worthless"); RadRotator rotator1 = new RadRotator(); rotator1.ID = "rotator1"; rotator1.DataSource = proverbsTable; rotator1.FrameTemplate = new RotatorTemplate("proverb"); rotator1.TransitionType = RadRotator.RotatorTransitionType.Slideshow; rotator1.TransitionEffect = RadRotator.RotatorTransitionEffect.GradientWipe; rotator1.Width = 500; rotator1.Height = 50; rotator1.AutoPostBack = true; PlaceHolder1.Controls.Add(rotator1); rotator1.DataBind(); }

The only really interesting thing here is the creation of the RotatorTemplate on line 12 - here's where you pass the variable information (in this case, the name of the data column you're going to use to populate the label text).

That's it for ITemplate Instantiation. I hope it saves you some time and sheds a little light on your own problem.

While I was writing a stored procedure that did some string manipulation and was debugging some unexpected behavior, I came across a fascinating, infurating, hidden-in-plain-sight "feature" of the LEN function: it returns the length of a string expression excluding trailing blanks. So, for example:

declare @string varchar(10)
set @string = '123456789 '

declare @len int
set @len = len( @string )

print @len
print right( @string, @len - 5 )

returns 9 and '789 ', not the expected 10 and '6789 '. Also, with this code:

declare @string varchar(10)
set @string = '123456789 '

declare @len int, @reverse_len int
set @len = len( @string )
set @reverse_len = len( reverse( @string ) )

print @len
print @reverse_len

One might expect that the length of a string would be the same forwards and backwards, but it isn't! @len = 9 and @reverse_len = 10!

A workaround for non-Unicode character data is to use the DATALENGTH function, which returns the size of an expression in bytes. If you choose to use DATALENGTH with Unicode data, you must divide by 2 to get the actual string length. Another workaround would be to append a non-blank character to the end of the string, get the length, then subtract one. Neither is very nice. If we had wanted to exclude trailing blanks, we could have called RTRIM before LEN ourselves.

I've been writing a lot of dynamic SQL that generates other SQL lately. While SQL isn't the best string manipulation language, it's got great native access to database schema information. However, my latest project was resulting in some very long scripts that coudn't be printed with PRINT, because it truncates the output after 8000 bytes, which is 8000 chars or 4000 nchars. One solution to this would have been to execute dynamic SQL to create a stored procedure containing the text, but in this case, I wanted to append multiple scripts together, so I chose to use PRINT as a simple way to output them all. The concept was simple: For each 4000 character Unicode block, find the last occurrance of CR, LF, or both, print the left part, and repeat on the right part. However, I ran into a few obstacles on the way; see my last two T-SQL Gotchas. Here is the final result, a general-purpose PRINT replacement that can handle any Unicode string, assuming that it contains at least one CR or LF every 4000 characters. The delimiter(s) could be parameterized, but I'll leave that as an exercise for you.

-- Works around the 4000/8000 character limit of the print statement
CREATE PROCEDURE dbo.LongPrint( @string nvarchar(max) )
AS
SET NOCOUNT ON

set @string = rtrim( @string )

declare @cr char(1), @lf char(1)
set @cr = char(13)
set @lf = char(10)

declare @len int, @cr_index int, @lf_index int, @crlf_index int, @has_cr_and_lf bit, @left nvarchar(4000), @reverse nvarchar(4000)
set @len = 4000

while ( len( @string ) > @len )
begin
   set @left = left( @string, @len )
   set @reverse = reverse( @left )
   set @cr_index = @len - charindex( @cr, @reverse ) + 1
   set @lf_index = @len - charindex( @lf, @reverse ) + 1
   set @crlf_index = case when @cr_index < @lf_index then @cr_index else @lf_index end
   set @has_cr_and_lf = case when @cr_index < @len and @lf_index < @len then 1 else 0 end
   print left( @string, @crlf_index - 1 )
   set @string = right( @string, len( @string ) - @crlf_index - @has_cr_and_lf )
end

print @string

Using is one of those dual use keywords in C#. Everybody uses using at the top of your code file so that you don’t have to type all of those long namespaces. The other use is for taking control over the closing and disposing of objects that support the IDisposable interface. Sometimes a little using goes a long way. One IDisposable class that is a good candidate for using is SqlConnection. Without using code to access a database, you would need to use a try-finally block to make sure you free up the resource.

 

SqlConnection conn = new SqlConnection(ConnectionString);

try

{

            ….

}

finally

{

      conn.Close();

}

 

Using using the code becomes much cleaner and logical.

 

using (SqlConnection conn = new SqlConnection(ConnectionString))

{

….

}

 

Next time you consider using resources, consider using using and clean your code up.