Steve Wellens

Programming in the .Net environment

Sponsors

Links

December 2009 - Posts

Automatically Generate Stored Procedures with Visual Studio

This is one of those tucked-away features in Visual Studio that, once you find it, can make you slap your forehead so hard that it hurts. Warning: You may need an aspirin after reading this.

Notes:

I'm using Microsoft Visual Studio Team System 2008. It also works with the Express editions (thanks to @bhitalks for checking this). 

I'm also using SQL Server Express 2005. To get this to work on my system, I had to download and install: SQLSysClrTypes.msi.

Step 1:

Right mouse-click the App_Code folder in the application and select "Add New Item..." When the "Add New Item" window appears, select DataSet. It doesn't matter what you name the DataSet but remember it for later.

Step 2:

Right mouse click in the XSD window and select "Add, TableAdapter…"

Step 3:

After a few seconds, the "Choose Your Data Connection" window should appear. Choose, or create, a connection and click "Next".

Step 4:

Here's the magic. In the "Choose a Command Type" window, choose "Create new stored procedures" and click "Next."

Step 5:

Enter an SQL select statement for a single table and click "Next."

Step 6:

The "Create the Stored Procedures" window appears. Rename the stored procedures to something meaningful and click "Next", or go ahead and click "Finish." Since I selected the Employees table, I renamed the procedures with the Employee prefix. You can click Preview SQL Script to see what is going to be run:

Step 7:

You should see the Wizard Results window…click "Finish" one more time and the Stored Procedures will be created.

Step 8:

Delete the .XSD file from the App_Code directory that was created in Step 1. It's not needed.

You are done.

Here's how the generated Stored Procedures look in SQL Server Management Studio:

The generated SQL code is very clean. Of course, you can modify it to fit your requirements.

Now, ain't that better than typing them by hand?

I hope someone finds this useful.

Steve Wellens

A ‘Simple’ Asp.Net Custom Server Control

A user on the Asp.Net forums, where I moderate, asked how to list a bunch of files in a directory as hyperlinks on a web page so the user could click on them. I thought this was probably an often performed task and decided create a Server Control to encapsulate it.

Initially, I tried a Web User Control but I wanted to allow setting borders, fonts, background colors, etc. With a Web User Control I would have to create a property for each setting manually. Following the paradigm of, "The less code you write, the fewer bugs you have," I looked for a better way.

I decided to create my first ever Custom Server Control. I looked at inheriting from a Label control but the Label control has no support for scroll bars. So, I inherited from the Panel control. The final control has all the properties of the Panel control (colors, borders, scrollbar support, etc.)  plus a few custom properties I added.  Using the Panel control minimized the effort.

Part I: The Custom Server Control

The initial Server Control was relatively easy. Here's the final code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Drawing.Design;
using System.IO;
using System.Text;
using System.Web;
using System.Web.Caching;
using System.Web.UI;
using System.Web.UI.WebControls;
 
[assembly: TagPrefix("EndWell", "EW")]
namespace EndWell
{
    [DefaultProperty("Text")]
    [ToolboxData("<{0}:HyperlinkFileList runat=server></{0}:HyperlinkFileList>")]
    [ToolboxBitmap("HyperlinkFileList.ico")]
 
    public class HyperlinkFileList : Panel
    {
        [Bindable(true)]
        [Category("Files List")]
        [Description("The Title of the list of files")]
        public string FilesTitle {get; set;}
 
        [Bindable(true)]
        [Category("Files List")]     
        [Description("The directory of the files to list:  (~/Files/)")]
        // these two built in editors were lacking:
        //[EditorAttribute(typeof(System.Web.UI.Design.UrlEditor), typeof(UITypeEditor))]
        //[EditorAttribute(typeof(System.Windows.Forms.Design.FolderNameEditor), typeof(UITypeEditor))]
        [EditorAttribute(typeof(EndWell.DualModeFolderEditor), typeof(UITypeEditor))]  
        public string FilesDirectory { get; set; }
 
        [Bindable(true)]
        [Category("Files List")]
        [Description("The filter for the files to show:  (*.*)")]
        public string FilesFilter { get; set; }
 
        [Bindable(true)]
        [Category("Files List")]
        [Description("Text to show when there are no files")]
        public string NoFilesText { get; set; }
 
        // ---- Private vars --------------------------
 
        private String[] m_FilesArray;  // cached for performance
 
        // ---- Default constants-------------------
 
        const String DEF_FILES_DIR = "~/xml/";
        const String DEF_FILES_FILT = "*.xml";
        const String DEF_FILES_TITLE = "XML Files:";
        const String DEF_NOFILES_TEXT = "<No Files>";
 
        // ---- Constructor -------------------------- 
 
        public HyperlinkFileList()
        {
            // set defaults for our properties
            FilesDirectory = DEF_FILES_DIR;
            FilesFilter = DEF_FILES_FILT;
            FilesTitle = DEF_FILES_TITLE;
            NoFilesText = DEF_NOFILES_TEXT;
 
            // Set defaults for panel properties
            // I don't like the default width to be full screen
            // And a border looks better
            Width = new Unit("300px");
            BorderStyle = BorderStyle.Solid;
            BorderWidth = 1;
            BorderColor = Color.Black;
 
            // If height is set, force scroll bars to keep list
            // from spilling over the panel/div boundaries.
            if ((Height != null) && (ScrollBars == ScrollBars.None))
                ScrollBars = ScrollBars.Auto;
 
            // Allow multiple controls to be placed horizontally
            // (normally each div get's its own line)           
            Style["display"] = "inline-block";
 
            // add spacing outside the control
            Style["margin"] = "0.5em";
 
            // add space inside the control           
            Style["padding-left"] = "0.5em";
            Style["padding-right"] = "0.5em";
            Style["padding-bottom"] = "0.5em";
            // top space usually comes from the title...
            if (String.IsNullOrEmpty(FilesTitle) == true)
                Style["padding-top"] = "0.5em";
        }
 
        // ---- RenderContents ----------------------------
        //
        // Spit out the HTML
 
        protected override void RenderContents(HtmlTextWriter Output)
        {
            // output the title if one was set
            if (String.IsNullOrEmpty(FilesTitle) == false)
            {
                Output.Write("<h3>&nbsp;&nbsp;");  // cosmetic spacing
                Output.Write(FilesTitle);
                Output.Write("</h3>");
            }
 
            GetFilesArray();
 
            if (m_FilesArray.Length == 0)
            {
                Output.Write(HttpUtility.HtmlEncode(NoFilesText));              
            }
            else
            {
                foreach (String OneFile in m_FilesArray)
                {
                    HyperLink Link = new HyperLink();
                    Link.NavigateUrl = Path.Combine(FilesDirectory, Path.GetFileName(OneFile));
                    Link.Text = Path.GetFileNameWithoutExtension(OneFile);                  
                    Link.RenderControl(Output);
                    Output.WriteBreak();
                }
            }
        }
 
        // ---- GetFilesArray -------------------------
        //
        // Fill the m_FilesArray with a list of files
        // either from disk or the cache
 
        private void GetFilesArray()
        {
            // see if the file list is in the cache.
            // use directory and filter as unique key
            m_FilesArray = Page.Cache[FilesDirectory + FilesFilter] as String[];
 
            if (m_FilesArray != null)
                return;
 
            // if no files filter set, use the default one.
            if (String.IsNullOrEmpty(FilesFilter))
                FilesFilter = DEF_FILES_FILT;
 
            // if no files directory set, use the default one.
            if (String.IsNullOrEmpty(FilesDirectory))
                FilesDirectory = DEF_FILES_DIR;
 
            // if a virtual path is detected, map to full path
            String FullPath;
            if (FilesDirectory.StartsWith("~"))
                FullPath = Context.Server.MapPath(FilesDirectory);
            else
                FullPath = FilesDirectory;
 
            // get the files
            m_FilesArray = Directory.GetFiles(FullPath, FilesFilter, SearchOption.TopDirectoryOnly);
 
            // put the list in the cache so we don't have to read the disk again
            // use a dependency on the directory being read from for auto refreshing
            Page.Cache.Insert(FilesDirectory + FilesFilter,   // unique key
                              m_FilesArray,                   // list of files to store
                              new CacheDependency(FullPath)); // dependency on directory
 
        }
    }
}

          

Notes:

The Control Name:

It's silly to agonize over the name of a control, right? Wrong. Naming a control (or any variable for that matter) is like getting married. You are going to be stuck with it for a very long time and changing it in the future can be extremely painful. Try to get your names right the first time.

    I called the control: HyperlinkFileList.

Spillage Problem:

If height of the control is set and the list of files exceeds the height of the control, the files "spill over" the control's boundaries.

To fix this, I added this to the control's constructor:

     if ((Height != null) && (ScrollBars == ScrollBars.None))
          ScrollBars = ScrollBars.Auto; 

CSS Layout:

Since the control is basically a div (the Panel control renders as a div) only one control could be placed on a line. So, I set the "display" attribute to "inline-block". This allows multiple controls to be side-by-side.

    Style["display"] = "inline-block";

CSS Box Model Tweaks:

I didn't like the text jammed up against the left edge of the control so I added some CSS padding. I also applied a CSS Margin around the control so it would not butt up against other controls:

   // add spacing outside the control
   Style["margin"] = "0.5em";
 
   // add space inside the control   
   Style["padding-left"] = "0.5em"; 

State Management:

During initial testing, I found that each time the control ran, it reread the directory of files. File IO is expensive. I looked at using the integrated Server Control "State" but it used a type of View State and it seemed inefficient to send a list of files to the client twice: Once as the html list and once in View State.

I looked at using Session State, Application State and the Cache.

I decided to put the list of files in the Cache object so the lists are shared among sessions .  If memory is at a premium, the cached lists are discarded.

I used the files directory and files filter, concatenated, as the unique key into the cache. This allows multiple controls to be used simultaneously and share file lists. 

I initially added a function so the developer could force a re-reading of the files as needed. But the Cache object can use dependencies: Any change in a dependent directory causes the cache to expire. The final code was ridiculously simple:

 // put the list in the cache so we don't have to read the disk again
 // use a dependency on the directory being read from for auto refreshing
 Page.Cache.Insert(FilesDirectory + FilesFilter,   // unique key
                   m_FilesArray,                   // list of files to store
                   new CacheDependency(FullPath)); // dependency on directory

Side Note: Sure, it's just one line of code but it took hours to do the research to decide this was the best way to handle the issue of state management. Sometimes it takes longer to write less code.

Property Editor:

I grouped all the custom properties of the control under the "Files List" heading so they are all in one place separate from the Panel properties.

Here's the markup for 4 controls on one page:

    <EW:HyperlinkFileList ID="HyperlinkFileList5" runat="server" BackColor="#FFFF66"
        Height="200px">
    </EW:HyperlinkFileList>
    <EW:HyperlinkFileList ID="HyperlinkFileList6" runat="server" FilesTitle="The Same XML Files"
        Height="200px">
    </EW:HyperlinkFileList>
    <br />
    <EW:HyperlinkFileList ID="HyperlinkFileList7" runat="server" BackColor="#66FFFF"
        BorderColor="#FF3300" BorderWidth="3px" FilesDirectory="C:/Peachw/EndSofi/BAK/"
        FilesFilter="*.Zip" FilesTitle="Whole lotta files!" ForeColor="#3333CC" Width="293px"
        Height="156px">
    </EW:HyperlinkFileList>
    <EW:HyperlinkFileList ID="HyperlinkFileList8" runat="server" BackColor="#66CCFF"
        Height="156px" Width="198px" FilesDirectory="~/Images/" FilesFilter="*.jpg" 
        FilesTitle="Pretty Pictures">
    </EW:HyperlinkFileList>

 

Here's what they look like rendered:

Part II: The Custom Server Control Editor

Selecting the Files Directory:

I thought it was amateurish to have the developer type, or paste in, the path to the directory of the files to list so I decided to add a directory browser. Yikes, talk about opening a can of worms!

    Developing the Server Control Editor took longer than developing the actual control.

I thought the control's file directory should be settable in two ways:

Absolute Path: C:\PublicData\ImageFiles\

Virtual Path: ~\xmlFiles\

I tried two built-in designer browsers by setting attributes on the FilesDirectory property:

[EditorAttribute(typeof(System.Web.UI.Design.UrlEditor), typeof(UITypeEditor))]

I rejected the UrlEditor because it doesn't allow browsing outside the site's home directory.

[EditorAttribute(typeof(System.Windows.Forms.Design.FolderNameEditor), typeof(UITypeEditor))]

I rejected the FolderNameEditor because there is no provision to select a virtual path. Also, it forces the user to select a file which I did not want.

To create a Custom Server Control Editor you create a class inheriting from UITypeEditor and override two functions…one of which launches a DialogBox.

Here's the code:  

using System;
using System.Collections.Generic;
using System.Drawing.Design;
using System.ComponentModel;
using System.Windows.Forms.Design;
using System.Text;
 
namespace EndWell
{
    class DualModeFolderEditor : UITypeEditor
    {
        // ---- GetEditStyle --------------------------------
        //
        // tell designer what kind of UI we are (Dropdown or Modal DialogBox)
 
        public override UITypeEditorEditStyle GetEditStyle(ITypeDescriptorContext context)
        {
             return UITypeEditorEditStyle.Modal;
        }
 
        // ---- EditValue ----------------------------------------
        //
        // Called by IDE designer when user clicks the ... button
        // A DialogBox is launched
 
        public override object EditValue(ITypeDescriptorContext Context,
                                         IServiceProvider Provider,
                                         object Value)
        {           
            IWindowsFormsEditorService EditorService = null;
            if (Provider != null)
            {
                EditorService = (IWindowsFormsEditorService)Provider.GetService(typeof(IWindowsFormsEditorService));
            }
            if (EditorService != null)
            {
                // launch the dialog box
                DualModeFolderEditorForm Editor = new DualModeFolderEditorForm(Value.ToString(), Context);
 
                EditorService.ShowDialog(Editor);
                return Editor.m_Value;
            }
            else
            {
                 return Value;
            }
        }
    }
}  

Here's what the editor's DialogBox looks like:

   

I'm not going to show the DialogBox code since it's a bit long and involved.   Note: You can download the project if you wish. There was a lot of trial and error in developing it because the documentation is lacking. But there were a few things of interest…

Directory Separators (Slashes):

The GetProjectItemFromUrl function did not work when a backslash was used like this: "\~". It did work with a forward slash like this: "/~".

So, I made sure all the directory separators used forward slashes. BUT, the directories returned from the Directory browser uses backslashes. So I also 'fixed' those for consistency…sigh L.   It made the code a bit messier than I prefer but there really was no other choice.

Server Control Development Tip:

Once the control is on a page, you can automatically update the DLL in the bin directory by right clicking the control and selecting "Refresh". This worked most of the time.

Other times I had to delete the control from the bin directory and then re-add it to the project by dropping it on a web page to get the latest version.

Debugging the Editor:

Debugging the control was easy. Debugging the control editor was hard because it runs in Visual Studio. I added this line at various places in the editor code:

    System.Diagnostics.Debugger.Break();

When the breakpoint is hit, you get this delightful screen:

Click "Debug the program" and a new instance of Visual Studio is launched so you can debug the control editor. However, the original running Visual Studio is locked-up (at least on my box it was) and had to be ungracefully terminated.

Since the documentation on Custom Server Control editors is somewhat lacking, it was invaluable to be able to poke around and see what was being passed in and what was happening.

Possible Enhancements:

As with most tasks like this, you can get carried away and start adding features 'until the cows come home':

  • Make the title font settable (size, color, background color...)
  • Put the title in one fixed div and the list of files in another resizable or scrollable div.
  • Add a Boolean field to optionally display the file extensions in the links.

Conclusion:

I learned a lot building the control mostly because I got stuck a few times. But, now I have a working template for any future Server Controls that can be contained inside a Panel Control…and so do you.

The full project, including debug and release build dlls can be downloaded here. It's targeted for .Net 3.5 but can probably be rebuilt for other versions since it's not using any special features.

I hope someone finds this useful.

Steve Wellens

More Posts