August 2009 Entries

Relevant Microsoft Developer Links (Good Reading/Learning)

posted on posted @ Thursday, August 27, 2009 9:07 AM | Feedback (1)

Web Sites 

1.) Microsoft Data Platform Site – news and updated releases of their most recent data products

2.) ADO.NET Team blog - With so many new technologies and approaches this seems to be the source of the news with respect to data.

 

Development 

3.) Excel generation on server (CodePlex)

4.) James Newton-King Json.NET - The Json.NET library makes working with JavaScript and JSON formatted data in .NET simple. Quickly read and write JSON using the JsonReader and JsonWriter or serialize your .NET objects with a single method call using the JsonSerializer.

5.) LinqToSQL A Detailed Review by Jesse Libery

6.) JQuery – 10 Tips for Better Code – check out also Best JQuery Tutorials here

 

Collaboration

8.) EtherPad – web based word processor that allows people to work together

9.) Microsoft SharedView - Connect with up to 15 people in different locations and get your point across by showing them what's on your screen. Share, review, and update documents with multiple people in real time.A Windows Live ID (Passport, Hotmail, or MSN) is required to start sessions, but not to join sessions. New in version 1.0: we have added a web based join experience to make SharedView even easier.

 

Operating Systems  

10.)  Windows 7 Edition Comparison Chart 

11.)  Windows XP to Windows 7 Upgrade

 

Tools
12.)  ImgBurn – DVD Burner utility (Free)

13.) WinDirStat:Windows Directory Statistics (Free)

 

Fun

14.)  Mondays – fun (sometimes crude) podcast with Carl Franklin(DotNetRocks), Karen Greenwald, Mark Miller(CodeRush) and Richard Campbell(RunAs Radio)

category: Tools

Asp.net UpdatePanel/UpdateProgress Controls and Overlays (Wait Messages)

posted on posted @ Wednesday, August 26, 2009 5:17 PM | Feedback (2)

Scenario: While a partial page postback is occurring disable all form fields and give the user a pleasant please wait message that can be used consistently across your application.  We need to create an overlay that works in conjunction with the updatepanel as well as direct from JavaScript.

Problem: Design the update progress html and css so that the entire page is overlaid with gray background.  This works pretty good until scrolling on the page is involved.  When the page is tall enough that the browser scrolls the overlay must by sized dynamically with each post.  If you fail to do this, you will end up with odd visual with the overlay only covering a portion of the page.

The problem above is resolved in the solution described within this post.  It covers the asp.net, html, css and JavaScript code to accomplish the above in re-usable fashion.

I was using the UpdatePanel control which enables you to build rich, client-centric web applications.  By using the UpdatePanel controls you can refresh selected parts of the page instead of refreshing the whole page with a postback.  While the partial page post back occurs an UpdateProgress control is available to provide the user with a friendly ‘please wait’ message.  This all works seamlessly out of the box. 

In my scenario I needed to overlay and disable all controls (make unavailable) for the user while the partial page update happens.  The application allows for file uploads (which cannot participate in partial page updates).  A full post back must occur for this to work correctly. 

My ASPX page has the following structure with typical UpdatePanel, ContentTemplate, Triggers and UpdateProgress controls.  The trigger designates that the btnSubmitAdmin will perform a full postback.  I want the full postback user interaction to look similar as the partial page updates.  As a result, I will be using the same updateprogress visual for both partial and full postbacks.

<body onload="fncOnLoad()">
<form id="form1" runat="server">
    <asp:ScriptManager ID="Scriptmanager1" runat="server" EnablePartialRendering="true"></asp:ScriptManager>
                
    <asp:UpdatePanel ID="updatePanel1" runat="server"  UpdateMode="Conditional" >
       <ContentTemplate>               
                
        <!-- Content Goes Here -->                
            
          </ContentTemplate>              
        <Triggers>
            <asp:PostBackTrigger ControlID="btnSubmitAdmin" />            
        </Triggers>     
   </asp:UpdatePanel>                    
       
   <asp:UpdateProgress ID="UpdateProgress1" runat="server" AssociatedUpdatePanelID="updatePanel1" DynamicLayout="true" DisplayAfter="1000">
        <ProgressTemplate >
             <uc1:WaitPanel ID="WaitPanel1" runat="server" />
        </ProgressTemplate>
    </asp:UpdateProgress>    
</form>

As you can see above the updateprogress has a user control WaitPanel.  It is this html/css that will be shown when the user performs a postback.  The WaitPanel is registered at the top of the page.

<%@ Control Language="VB" AutoEventWireup="false" CodeFile="WaitPanel.ascx.vb" Inherits="Uc_WaitPanel" %>
        
        <div id="OuterTableCellOverlay">
            <div id="InnerTableCellOverlay">
                <b>... Please Wait ...</b>
                <br />
                <asp:Image runat="server" ImageUrl="~/Images/indicator_waitanim.gif" />                
            </div>
        </div>

I wanted this solution to be easily applied to other pages as well as other projects.  As a result, I created both wait.css and wait.js files that can be copied across projects.  These files functional aspects of the wait.  The user control in the page load registers both the style sheet (css) and includes the JavaScript library.

Partial Class Uc_WaitPanel
    Inherits System.Web.UI.UserControl
 
    Protected Sub Uc_WaitPanel_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
 
        Dim css As New HtmlLink()
        With css
            .Href = ResolveClientUrl("~/Style/Wait.css")
            .Attributes("rel") = "stylesheet"
            .Attributes("type") = "text/css"
            .Attributes("media") = "all"
        End With
        Page.Header.Controls.Add(css)
 
        Dim url As String = ResolveClientUrl("~/Scripts/Wait.js")
        Page.ClientScript.RegisterClientScriptInclude("wait", url)
 
    End Sub
End Class

So far I have shown the aspx code and the code that displays the please wait message.  When this is displayed I am using CSS to overlay a gray and place ‘please wait..’ in the forefront.

/* updateprogress css */
    #OuterTableCellOverlay
    {
        background-color: white;  
        filter:alpha(opacity=85);
        -moz-opacity:0.85;        
        z-index: 999;
        width: 100%;
        height: 100%;
        position: absolute;
        top: 0;
        left: 0;                        
    }
    
    #InnerTableCellOverlay
    {
        border: 1px solid black;
        padding: 10px;
        background-color: #eee;
        z-index: 998;
        background-color: #eee;
        filter:alpha(opacity=100);
        position: absolute;
        top: 0pt;
        left: 0pt;            
        text-align: center;        
    }

As it stands right now when a partial page update is performed the ‘please wait’ message will be shown in the forefront with gray background on the complete page.
I now want the following… 1.)  to show this same image when performing full postbacks 2.) I want to be able to use this from JavaScript without the update panel.  In order to use from JavaScript (after the user selects submit) I attach a function via

 
<asp:Button ID="btnSubmitAdmin" runat="server" CssClass="btn" style="width:100px;" 
          OnClientClick="return fncValidateSubmit('Ready to Submit?');" Text="Save" 
          ToolTip="Save" Visible="false" />
 
 
//validate before submitting signature authority            
    function fncValidateSubmit(msg) {
        //perform some client side validation checks here...
        if (valid == true) {
            var answer = confirm(msg);                    
            if (answer) {
                ShowWait('UpdateProgress1');
                return answer;
            }
            
        } else {
            alert(errmsg);
            return false;
        }
    }

Here you will notice a call to ShowWait(‘UpdateProgress1’);  This function is within the Wait.js file that was included with the user control.

function ShowWait(progressControlId) {
    SetWaitDimensions();
    document.getElementById(progressControlId).style.display = 'block';
}

So all very cool that accomplishes 1.) above showing the same update progress message when performing a full postback.  If I want to show this message at any time I can simply call ShowWait and pass in the id of the container for the Wait message.  At this point, the code that is needed is simply the user control, the style sheet and the wait JavaScript library.

The big issue that remains is to have the overlay cover the entire browser window independent of any scrolling.  This was an problem.  Without this adjustment after the user scrolled down half the page and performed any sort of postback the gray overlay was only shown on a portion of the page leaving a very ugly user interface.

The solution: involved calling a JavaScript function upon body load.  Upon page load loadAjaxHandlers() is called.   This function is necessary to intercept between the user clicking the button and the partial post occurring.  This enables us to run some JavaScript to size the overlay and center the wait message properly.  Below you can see that I am calling SetWaitDimensions before the post.  This function determines the browser window as well as the scroll dimensions and determines the size of the required overlay and also how to center the wait message.

////call the following from <body onload="load()">
////required so that background blur is dimensioned correctly (with respect to scroll issues with browser)
////enable js to be called after ajax postback
function loadAjaxHandlers() {
    Sys.WebForms.PageRequestManager.getInstance().add_endRequest(EndRequestHandler);
    //will process during the initialization of the postback
    Sys.WebForms.PageRequestManager.getInstance().add_initializeRequest(
                    function() {
                        SetWaitDimensions();
                    }
               )
}


The following is my complete Wait.js file which you can see sizes the overlay and positions the wait message.

////call the following from <body onload="load()">
////required so that background blur is dimensioned correctly (with respect to scroll issues with browser)
////enable js to be called after ajax postback
function loadAjaxHandlers() {
    Sys.WebForms.PageRequestManager.getInstance().add_endRequest(EndRequestHandler);
    //will process during the initialization of the postback
    Sys.WebForms.PageRequestManager.getInstance().add_initializeRequest(
                    function() {
                        SetWaitDimensions();
                    }
               )
}
 
function ShowWait(progressControlId) {
    SetWaitDimensions();
    document.getElementById(progressControlId).style.display = 'block';
}
 
//set dimensions of blur element, positions progress box
function SetWaitDimensions() {
    if (document.getElementById) {
        var blur = document.getElementById('OuterTableCellOverlay');
        var progress = document.getElementById('InnerTableCellOverlay');
 
        progress.style.width = '444px';
        progress.style.height = '100px';
 
        var vp = getViewport();
        var dm = getElementDimensions(document.body);
        var sp = getScrollPosition();
 
        if (vp.height > dm.height)
            blur.style.height = vp.height + 'px';
        else
            blur.style.height = dm.height + 'px';
        blur.style.width = '100%';
 
        blur.style.top = (sp.y + ((vp.height - dm.height) / 3)) + 'px';
        blur.style.left = (sp.x + ((vp.width - dm.width) / 2)) + 'px';
 
        progress.style.top = document.documentElement.clientHeight / 3 - progress.style.height.replace('px', '') / 2 + 'px';
        progress.style.left = document.body.offsetWidth / 2 - progress.style.width.replace('px', '') / 2 + 'px';
 
    }
}
//returns view port dimensions
function getViewport() {
    var v = { width: 0, height: 0 };
    if (window.innerHeight) {
        v.height = window.innerHeight;
        v.width = window.innerWidth;
    } else if (document.documentElement.clientHeight) {
        v.height = document.documentElement.clientHeight;
        v.width = document.documentElement.clientWidth;
    } else {
        v.height = document.body.clientHeight;
        v.width = document.body.clientWidth;
    }
    return v;
}
 
//returns dimensions of element
function getElementDimensions(el) {
    var dim = { width: 0, height: 0 };
    dim.width = el.offsetWidth;
    dim.height = el.offsetHeight;
    return dim;
}
 
//returns window scroll position
function getScrollPosition() {
    var pos = { x: 0, y: 0 };
    pos.x = window.pageXOffset ? window.pageXOffset : document.documentElement.scrollLeft;
    pos.y = window.pageYOffset ? window.pageYOffset : document.documentElement.scrollTop;
    return pos;
}

While writing this post, I recognized that there was a nice feature that I would like to include.  Currently, the gray overlay covers the entire browser window.  I would like modify so that I can pass in the container object.  By doing this I could use in smaller portions on the page quite easily. 

This is quite easily moved between projects which was one of my goals with this solution. If you have any comments or improvements please let me know.

category: Feature

SQL COALESCE Command Features

posted on posted @ Monday, August 24, 2009 5:37 PM | Feedback (1)

In order to concatenate strings (delimited by with a string) from multiple rows in a SQL Table to a single field the Coalesce command is the one to use.  Typically COALESCE is used to return a single field value which represents multiple rows concatenated by a string. 

DECLARE @EmployeeList varchar(100)
 
SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + 
   CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1
 
SELECT @EmployeeList


The output from the following would be something like 1,2, 3

The following example uses a function to return a joined table field with the primary data selected.  The challenge was to return a single row from one table while returning values from the joined table into a single field.  To accomplish this I used syntax similar to the following:

i.e.

SELECT personID,dbo.fn_CombineValues(personID) Roles
  FROM [People]

 

The following function was created to support the above sql query.  The function accepts the key to be used in the the joined table and returns a string value representing in this case the roles related to the primary table.

CREATE FUNCTION fn_CombineValues
 (
     @FK_ID INT --The foreign key from TableA which is used to fetch corresponding records
 )
 RETURNS VARCHAR(8000)
 AS
 BEGIN
 DECLARE @SomeColumnList VARCHAR(8000);
 
 SELECT @SomeColumnList = COALESCE(@SomeColumnList + ', ', '') + CAST(Role AS varchar(20)) 
 FROM dbo.SA_PeopleRoles C
 INNER JOIN dbo.SA_Roles r ON r.roleid=c.RoleID
 WHERE C.personID = @FK_ID;
 
 RETURN 
 (
 SELECT @SomeColumnList
 )
 END

References:http://stackoverflow.com/questions/111341/combine-multiple-results-in-a-subquery-into-a-single-comma-separated-value

category: SQL

Export SQL Data to Microsoft Excel (using Visual Studio 2008, c#)

posted on posted @ Monday, August 24, 2009 5:09 PM | Feedback (11)

Over the years there have been a number of methods to move SQL data into Microsoft Excel.  Using Automation you can call methods and properties that are specific to excel which gives you the greatest flexibility for specifying the location of your data in the workbook.  The following are two recent approaches to export Sql Server table row data to an excel workbook. 

1.) Using Automation you can use transfer data cell by cell

2.) Transfer data in an array to a range of cells


I created a WPF application with a button "Export" for this code.  The click event creates performs the the following actions

a.) Gets a reference to the automation object
b.) Adds a default workbook
c.) Gets the list of tables within the database
d.) Gets data
e.) Exports the data to excel worksheets (there are 2 methods defined (1) range method (2) cell by cell
f.) Saves the excel sheet
g.) Clean up

 

This blog posts shows two ways to extract data from SQL Server and place into an Excel document. The Range method was much quicker than the cell by cell approach.  The code below gets a list of all tables within a particular SQL Database and exports the data to Excel. 

I didn’t use was Excel 2007 document format at this.  If you have a good post or web link using this technique I would happily reference.  Let me know if you would like the Visual Studio project.

private void btnExport_Click(object sender, RoutedEventArgs e) {
    lblMessage.Content="Export Started..";
 
    WpfApplication.DoEvents();
    ApplicationClass app = CreateExcelDocument(false);
 
    //add workbook to excel document
    Workbook workbook = app.Workbooks.Add(Type.Missing);
 
    DataSet ds = GetData(GetTableList());            
    
    //shown below are 2 method of moving data from sql to excel, only use one of them
    //(1)call method to export to worksheet using range functionality (much faster than cell by cell method
    Export(ds, ref workbook);
 
    //(2)cell by cell method
    //InsertIntoExcel(app, ds);
    
     string fileName = @"C:\Safety3.xlsx";
     SaveDoc(workbook, fileName);
     CloseExcelDocument(app);
     lblMessage.Content = "Finished..";
}

a.) Create Excel document 

/// <summary>
/// creates excel document
/// </summary>
private ApplicationClass CreateExcelDocument(bool visible) {
    ApplicationClass app = null;
    try {
        app = new Microsoft.Office.Interop.Excel.ApplicationClass();
        app.Visible = visible;
 
    } catch (Exception ex) {
        MessageBox.Show(ex.ToString());
    }
    return app;
}


c.) The following method GetTableList() returns a List<ExportInfo> where ExportInfo is a structure that has the Name and SQL to be used for each table to be exported.

public class ExportInfo {
    public string Name { get; set; }
    public string Sql { get; set; }
}

/// <summary>
/// get list of sql tables in database
/// </summary>
/// <returns>list of ExportInfo objects</returns>
private List<ExportInfo> GetTableList() {
    System.Data.DataTable tables = new System.Data.DataTable("Tables");
    using (SqlConnection connection = new SqlConnection(GetConnectionString())) {
        
        SqlCommand command = connection.CreateCommand();
        command.CommandText = "select table_name as Name from INFORMATION_SCHEMA.tables where TABLE_TYPE = 'BASE TABLE'";
        connection.Open();
        tables.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
        connection.Close();
    }
 
    List<ExportInfo> exps = new List<ExportInfo>();
    foreach (DataRow row in tables.Rows) {
        exps.Add(new ExportInfo(){Name=row[0].ToString(), Sql = String.Format("select top 1 * from {0}",row[0].ToString())});
    }
    return exps;            
}

d.) Gets Data by interating over collection of exportInfo’s to retrieve sql statements

/// <summary>
/// get data from sql server
/// </summary>
/// <param name="exps"></param>
/// <returns>dataset with return results</returns>
private System.Data.DataSet GetData(List<ExportInfo> exps) {
    DataSet ds = new DataSet();
    string executeSql = string.Empty;
    foreach (ExportInfo exp in exps) {
        executeSql += exp.Sql + ";";
    }
 
    string connectionString = GetConnectionString();
    SqlConnection conn = new SqlConnection(connectionString);
    SqlDataAdapter adapter = new SqlDataAdapter(executeSql, conn);
    try {
        adapter.Fill(ds);
    } catch (Exception ex) {
 
    }
 
    int index = 0;
    foreach (System.Data.DataTable dt in ds.Tables) {
        dt.TableName = exps[index].Name;
        index += 1;
    }
    return ds;
}

e.) Export using range (1)

/// <summary>
/// Copy data from dataset into workbook
/// </summary>
/// <param name="dataSet"></param>
/// <param name="excelWorkbook"></param>        
private void Export(DataSet dataSet, ref Workbook excelWorkbook) {
 
    int sheetIndex = 0;
 
    // build rawData 2 dimensional array with data for each datatable            
    foreach (System.Data.DataTable dt in dataSet.Tables) {
 
        // Copy the DataTable to an object array
        object[,] data = new object[dt.Rows.Count + 1, dt.Columns.Count];
 
        // Copy the column names to the first row of the object array
        for (int col = 0; col < dt.Columns.Count; col++) {
            data[0, col] = dt.Columns[col].ColumnName;
        }
 
        // Copy the values to the object array
        for (int col = 0; col < dt.Columns.Count; col++) {
            for (int row = 0; row < dt.Rows.Count; row++) {
                data[row + 1, col] = dt.Rows[row].ItemArray[col];
            }
        }
 
        // Calculate the final column letter
        string finalColLetter = string.Empty;
        string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
        int colCharsetLen = colCharset.Length;
 
        if (dt.Columns.Count > colCharsetLen) {
            finalColLetter = colCharset.Substring(
                (dt.Columns.Count - 1) / colCharsetLen - 1, 1);
        }
 
        finalColLetter += colCharset.Substring(
                (dt.Columns.Count - 1) % colCharsetLen, 1);
 
        // Create a new Sheet
        Worksheet excelSheet = (Worksheet)excelWorkbook.Sheets.Add(excelWorkbook.Sheets.get_Item(++sheetIndex),
                                                                    Type.Missing, 1, XlSheetType.xlWorksheet);
 
        excelSheet.Name = dt.TableName; // name new sheet name of table
 
        // Fast data export to Excel
        string excelRange = string.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1);
        excelSheet.get_Range(excelRange, Type.Missing).Value2 = data;
 
        // Mark the first row as BOLD
        ((Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;
    }
}

Export by iterating of Cells (2)

/// <summary>
/// inserts data into excel row by row
/// </summary>
/// <param name="excel"></param>
/// <param name="ds"></param>
private void InsertIntoExcel(Microsoft.Office.Interop.Excel.Application excel, System.Data.DataSet ds) {
    foreach (System.Data.DataTable dt in ds.Tables) {
        Microsoft.Office.Interop.Excel.Worksheet theSheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Workbooks[1].Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        theSheet.Name = dt.TableName;
 
        int colIndex = 0;
        int rowIndex = 1;
        string err = string.Empty;
        try {
            
            colIndex = 0;
            foreach (DataColumn col in dt.Columns) {
                colIndex += 1;
                excel.Cells[1, colIndex] = col.ColumnName;
            }
 
            rowIndex = 1;
            foreach (DataRow row in dt.Rows) {
                rowIndex += 1;
                colIndex = 0;
                foreach (DataColumn col in dt.Columns) {
                    colIndex += 1;
                    excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                }
            }
        } catch (Exception ex) {
 
            err += "RowIndex=" + rowIndex.ToString() + " ColIndex=" + colIndex.ToString();
        }
    } // next 
}

 

f.)  g.) Save and clean up 

/// <summary>
/// Save workbook to file system
/// </summary>
/// <param name="excelWorkbook"></param>
/// <param name="fileName"></param>
private void SaveDoc(Workbook excelWorkbook, string fileName) {
    // Save and Close the Workbook
    excelWorkbook.SaveAs(fileName, XlFileFormat.xlWorkbookNormal, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    excelWorkbook.Close(true, Type.Missing, Type.Missing);
    excelWorkbook = null;
}

 

/// <summary>
/// Clean up and close document
/// </summary>
/// <param name="excelApp"></param>
private void CloseExcelDocument(ApplicationClass excelApp) {
    // Release the Application object
    excelApp.Quit();
    excelApp = null;
 
    // Collect the unreferenced objects
    GC.Collect();
    GC.WaitForPendingFinalizers();
}
category: Feature

List Indexes in SQL Server

posted on posted @ Tuesday, August 18, 2009 12:16 PM | Feedback (0)

The following SQL will list all indexes in within the database that you run the sql script.  I was looking for the option within Visual Studio - Data Dude add-on that displayed index differences between two databases.  I eventually discovered the line by line index comparison by exploding the Table-Indexes however I was unable to create the newly added script index in the 2nd database.  Is this possible?

In lieu of the shortcomings within Data-Dude I used the following script to manually compare and script the indexes in both databases.

DECLARE GetTables CURSOR READ_ONLY
FOR
  SELECT TABLE_NAME
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_TYPE = 'BASE TABLE'
  AND OBJECTPROPERTY (OBJECT_ID(TABLE_NAME), 'IsMSShipped') = 0

DECLARE @TableName sysname
OPEN GetTables

FETCH NEXT FROM GetTables INTO @TableName
WHILE (@@fetch_status = 0)
BEGIN
  EXEC sp_helpindex @TableName
  FETCH NEXT FROM GetTables INTO @TableName
END

CLOSE GetTables
DEALLOCATE GetTables
category: SQL Server

About Me

An engineer by training and a software developer at heart. My techniques and approaches meld engineering approaches with software technology.

Core to these principles is a systematic approach to the development of software with a strong lifecycle and process management emphasis through adoption of mature technologies.

Ten years designing heavy structural steel and concrete structures and 12 years in the software development profession have embedded strong project management and business knowledge in my approaches.

 

Subscribe to Rss Feed


Follow me on twitter @dyardy