navigation
 Wednesday, March 21, 2007

I am guessing a lot of people out there are like me and have projects that they started working on in ASP.Net 1.1, and later migrated to 2.0. This blog is about how to move your old files into the brave new world...

One of the neat features with web forms created from scratch in 2.0 is the separation of the Code Behind files into two partial classes: one partial class in a file called formname.aspx.designer.cs, and one in formname.aspx.cs. The designer file is autogenerated and contains declarations of all the form level variables, for instance if you drop a TextBox tbMyTextBox onto your form, it will add a line

System.Web.UI.WebControls.TextBox tbMyTextBox

to your designer file.

The other Code Behind file is the one where you write your code, event handlers, etc. The two partial class files are then combined at compile time into one class.

So, how do I take an old 1.1 Code Behind file and separate it into the two files I would have had if I had created it in 2.0? Well, follow these steps:

1. Go to the aspx (same applies for asxc files) file, and change the Page directive to say CodeFile="formname.aspx.cs" instead of Codebehind="formname.aspx.cs"

2. Go to your Code Behind file and delete all the member control declarations that should be in the designer file, for instance the tbMyTextBox in my example (be careful not to delete any other private member variables you may have interspersed with the generated code).

3. Still in the old Code Behind file, add partial in front of the class name of the form, for instance

public partial class formname : System.Web.UI.Page

These three steps are sometimes enough for the IDE to kick in and generate your Code Behind file, but if not, you can prod it on its way a little:

4. Create a file called formname.aspx.designer.cs in the same directory as your formnam.aspx.cs, and copy the basic partial class skeleton structure from your code behind (dont include the : System.Web.UI.Page, and use the same namespace as your partial class):

namespace YourNameSpace {

public partial class formname {

   }
}

5. Add this new file to the project (Project, Add Existing Item). It should line up nicely with your Code Behind file under the aspx node in the Solution Explorer.

6. Go back to the aspx file and change the ID of some arbitrary form level control, then hit Save.... change it back and save again. Now look at your empty stub of a partial class again from step 4...TADA! It now contains declarations for all of your form controls (not just the one you changed!).

By the way, if you for some reason want to declare a member control differently from the way the designer autogenerates it, you can just move it from the generated file back into your file and change for instance the visibility of the variable. The generator checks for existing declarations before it adds them to the generated file (hence step 2 above).

I know this is a tad tedious, I wish there was a wizard, but at least you now know how to make the conversion manually.

posted on March 21, 2007  #    by John Waters  Comments [0] Trackback
 Thursday, March 15, 2007

When querying an external data source through a linked server, you have two choices: you can write a typical T-SQL query, specifying the external tables with their four-part name in the FROM clause, or use the OPENQUERY function. The first option has advantages, as long as the external provider supports enough SQL so that SQL Server can take your query and translate it into a query that the external data source can use. On the other hand, if th external provider doesn't support enough SQL for SQL Server to optimize the query, or if the external provider is just too plain buggy, you will have to resort to passing through your own provider-specific queries with OPENQUERY. This gives you a great deal of control over how the external data source is accessed, but comes with a frustrating and puzzling limitation: OPENQUERY requires that the query be a string constant. That means no string variables you built with conditional logic, no expressions that result in a string, and especially no support for parameter substitution!

Fortunately, there is an option besides pulling the entire external table over and then processing it on SQL Server. It's an extremely powerful built-in procedure called sp_executesql. This stored procedure accepts a string containing SQL statements and attempts to execute it. However, with this great power comes great responsibility! Since you are constructing a string of ad-hoc SQL, you must be extremely careful to validate all external input lest you fall victim to a SQL Injection attack. Let's look at a simple example of how to use sp_executesql to create a parameterized OPENQUERY against a linked server named NAVISION.

CREATE PROCEDURE OpenQuery_Customer_ByDate( @date datetime )
AS
set nocount on

declare @stmt nvarchar(max)
set @stmt = 'select * from OPENQUERY( NAVISION, ''select * from Customer where "Last Date Modified" >= ' + convert( char(10), @date, 120 ) + ''' )'

exec sp_executesql @stmt

This procedure illustrates the basic idea of the technique: build a string containing the OPENQUERY query, dynamically building the pass-through query within this string. After the string has been built and passed to sp_executesql, the pass-through query appears as if it were a string constant to OPENQUERY. The same technique could be used to dynamically create a view or function with a predictable name that other SQL could use in a SELECT statement. With sp_executesql, you are limited only by what you can write to a string, and that could be anything!

posted on March 15, 2007  #    by Adam Anderson  Comments [2] Trackback
 Wednesday, March 14, 2007
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.
posted on March 14, 2007  #    by John Waters  Comments [0] Trackback
 Tuesday, March 13, 2007

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

posted on March 13, 2007  #    by Noel Rice  Comments [0] Trackback
 Tuesday, March 06, 2007

There was a bug in SP2 regarding maintenance plans, see official note below:

An issue was recently discovered with SQL Server 2005 SP2 which might impact customers using cleanup tasks in Maintenance Plans and SQL Server Integration Services packages. The issue is described in detail at http://support.microsoft.com/kb/933508. We have created a General Distribution Release (GDR) #50000939 to address this issue which is available for download and we have included the GDR in a refresh of the SP2 installation package

More information can be found here.

posted on March 6, 2007  #    by John Waters  Comments [0] Trackback
 Friday, March 02, 2007

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.

posted on March 2, 2007  #    by Rick Miller  Comments [0] Trackback
 Wednesday, February 28, 2007

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.

posted on February 28, 2007  #    by Adam Anderson  Comments [1] Trackback

The T-SQL PRINT statement is the way to output plain text from SQL scripts and stored procedures. Unfortunately, it has a built-in limitation of printing 8000 non-Unicode or 4000 Unicode characters, and the rest is truncated. I was writing some SQL that generated lengthy SQL scripts that exceeded this limitation, and I wanted to create my own version of PRINT that would split my string at the nearest CRLF, print the part preceding the CRLF, then repeat on the rest of the string to the right of the CRLF. The CRLF itself would be omitted, since each PRINT statement appends its own to the output. However, my own string would sometimes contain two consecutive CRLF sequences for formatting purposes. My printing procedure would correctly strip the second one and leave the first, passing it to PRINT. But instead of my CRLF plus PRINT's, I only got one, spoiling my formatting. I ran the following test:

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

print 'cr + lf' + @cr + @lf
print '---'

print 'cr only' + @cr
print '---'

print 'lf only' + @lf
print '---'

print 'lf + cr' + @lf + @cr
print '---'

And got the following output:

cr + lf
---
cr only

---
lf only

---
lf + cr

---

Interesting results! Notice that when a string is followed by CRLF, there is only one, not one plus one from PRINT. A solitary CR or LF will result in a new line plus one from PRINT, and LFCR results in two new lines plus one from PRINT. Since SQL Server honors a single CR or LF and they work as I expected, I updated my SQL generation code to use LF only as new line characters.

posted on February 28, 2007  #    by Adam Anderson  Comments [0] Trackback

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

posted on February 28, 2007  #    by Adam Anderson  Comments [1] Trackback
 Wednesday, February 21, 2007

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.

posted on February 21, 2007  #    by Bary Nusz  Comments [0] Trackback