Friday, September 30, 2011

Converting and Formatting DataSet Strings

Consider the following. You have a DataSet and you need to assign the contest of this dataset to another string. In the example below, several TextBoxes.

You could do the below to convert the non-string data in the DataSet row to a String and then apply a String Format.

TextBoxDecimal.Text = ((decimal)lProjectsDS.Tables[0].Rows[0]["Budget"]).ToString("c");
TextBoxDouble.Text = ((double)lProjectsDS.Tables[0].Rows[0]["MaxHours"]).ToString("0.00");
TextBoxDate.Text = ((DateTime)lProjectsDS.Tables[0].Rows[0]["ExpDate"]).ToString("d");

The ToString format work as follows:
- Format: "c" converts to a currency using the default locate.
- Format: "0.00" converts the number so it has two decimals, if no decimals found then displays 00
- Format: "d" shortdate using the default locate.

You could also apply formatting to the TextBoxes or the target string, however in my case the need was to format the data you receive right out of the Dataset.

Happy Coding,
Will




Thursday, September 29, 2011

How to Find the UserID using Membership

I have a table which is driven by the UserID which is assigned when a new user is added to the database using Membership.

My application needs to retrieve the UserID on the fly in order to be able to find the correct record.
One way to do this is the following:

Membership.GetUser().ProviderUserKey.ToString();

I hope this helps,
Will




Saturday, September 24, 2011

Where is the iPhone, iPad, MAC Address?

Wondering where is the MAC address of your Apple mobile device?
Go to: Settings -> General -> About

The Wi-Fi Address is the device's MAC Address

Will

How to Format an Eval Statement to Display a Date using Date Locale

I need to format a DateTime value that I'm using inside an ItemTemplate via an Eval expression, so it displays a short date using the locale. I tried working with the format syntax on the Eval statement without any luck.
I came across the following syntax to format an Eval expression so it displays a short date formatted according to the user's locale:

<%#((DateTime)Eval("ExpDate")).ToString("d")%>

The key is casting Eval as a DateTime which you can then convert to String. At this point you can take advantage of the ToString format "d" which uses a locale short date format.
This works for ASP.Net 2.0 and later, it will save you having to create a function to determine the users locale.
Will





Wednesday, September 21, 2011

Using ASP.Net Profile Feature in a Web Application Project

So, I decided to take advantage of the Profile feature of .NET to store some personal data per user. Shouldn't be a big deal right? After all, you just call the appropriate assembly and then, you should be able to invoke Profile.Save() and like magic the data is now in your ASP database on the Profiles table.

This is when I came across a huge set of posts and threads about how complex and confusing it can be to implement the Profile feature if you have a Web Application Project.

Among the confusing, credit should be due to Lee Dumond and Scott Gu as usual for posting some awesome examples in their blogs.

By following Lee's blog, below is my implementation of the same technique to be able to use Profiles in my Web Application Project. I thought it would be a good idea to post it, in order to give you a second example.

Step 1. Created a Class named ProfileInfo which exposes the properties that I want to save in my profile, in this case, let's work with two FirstName and Last Name

namespace Project1.Account
{
    [Serializable]
    public class ProfileInfo
    {
        public string FirstName { getset; } 
        public string LastName { getset; } 
    }
}

Step 2. Created a second Class named wProfile, this class is inherited from the ProfileBase class and will be used to expose the profile functionality on other pages in the site.

using System.Web;
using System.Web.Profile;
namespace Project1.Account
{
    public class wProfile : ProfileBase
    {
 
        public ProfileInfo ProfileInfo
        { 
            get { return (ProfileInfo) GetPropertyValue("ProfileInfo"); }       
        } 
 
        public static wProfile GetProfile() 
        { 
            return (wProfileHttpContext.Current.Profile; 
        } 
 
        public static wProfile GetProfile(string userName) 
        { 
            return (wProfile) Create(userName); 
        }  
    }
}

Step 3. Modify your Web.config file to implement a SQLProfileProvider. Notice the "inherits" on the defaultProvider definition. Also noticed that the connection ApplicationServices was previously defined.

    <profile defaultProvider="AspNetSqlProfileProvider" inherits="Project1.Account.wProfile"
      <providers>
        <clear/>
        <add name="AspNetSqlProfileProvider" type="System.Web.Profile.SqlProfileProvider" connectionStringName="ApplicationServices" applicationName="/wMyApp"/>
      </providers>
    </profile>

Step 4. Implementing the logic in my ASPX pages

        protected void Page_Load(object sender, EventArgs e)
        {
            wProfile lProfile = wProfile.GetProfile(Membership.GetUser().UserName);
            Label1.Text = "Welcome, " + lProfile.ProfileInfo.FirstName + " " + lProfile.ProfileInfo.LastName;
        }

Happy Coding, Hope this helps,
Will




Monday, September 19, 2011

Displaying Grouped Records with ListView and LINQ

I came across an interesting problem while enhancing a ASP.Net site, I needed to add a Grouped ListView to use it as a query screen with print capabilities.
The scenario is as follows: You have a list of records being displayed in a ListView. Records are grouped (In my case, by Project) and you want to have a group footer to total each group.


How to get this done?

First, we can use the ListView Layout Template to create the desired table structure:

<asp:ListView ID="lvGroupedLabor" runat="server"                                                                                                
    style="text-aligncenterfont-familyArial, Helvetica, sans-serif; 
    font-sizexx-small" ondatabound="lvGroupedLabor_DataBound">
<LayoutTemplate>
    <table cellspacing="0" cellpadding="5" rules="all" border="1" width="100%">                                                                            
        <thead class='grouptot'>
        <tr>      
        <th>Project</th>      
        <th>Work Order</th>    
        <th>Date</th>
        <th>Time</th>
        <th>Comment</th>
        </tr>    
        </thead>
        <tbody>
        <asp:PlaceHolder ID="itemPlaceholder" runat="server" />
        </tbody>
        <tfoot>
            <tr class='grouptot'>        
            <td colspan="6"><asp:Literal ID="litSubTotal" runat="server" /></td>    
            </tr>
            <tr class='group'>        
            <td colspan="6"><asp:Literal ID="litGrandTotal" runat="server" /></td>
            </tr>    
        </tfoot>
    </table>
</LayoutTemplate>
<ItemTemplate>
    <%# fTotalbyProject(false%>
    <%# fGroupingRowIfNewProject() %>
    <tr class='data<%# Container.DataItemIndex % 2 %>'>
    <td><%#Eval("project_cd")%></td>
    <td><%#Eval("woid")%></td>
    <td><%#Eval("labor_date""{0:M/dd/yyyy}")%></td>
    <td><%#Eval("labor_time")%></td>
    <td><%#Eval("comment")%></td>
    </tr>
</ItemTemplate>
</asp:ListView>                      

There are several interesting things to point out here:
  • Notice how there is a row in the template for the group footer <tfoot> in my case I decided to have a row to total per group and another row to total all the values.
  • The structure of the template is such, that will insert a header row for the group, followed by the Total of the last group, then the record rows. Therefore we will need to address how to insert a total for the last row and the grand total
  • The method fTotalbyProject(false) is used to display to numeric total of each group
  • The method fGroupingRowIfNewProject() is used to signal a group break per row
  • The sytle <tr class='data<%# Container.DataItemIndex % 2 %>'> is a nice little trick to zebra the rows in the ListView
The code assumes that you have a few static variables defined that we'll be using with our class

        static string sLastProject = "";
        static string sTotLastProj = "";
        static decimal dLastSumTotal = 0;
        static decimal dGrandTotal = 0;
        static bool bTotalCntSt = false;
Let's review the fTotalbyProject method, this one is used to display the "Total for Project (XXX): "

    protected string fTotalbyProject(bool aMode)
    {
        if (aMode == false)
        {
            //For the first row, set the flag to true so initialization happens only once
            String sCurrentProject = (string)Eval("project_cd");
            if (bTotalCntSt == false)
            {
                sTotLastProj = (string)Eval("project_cd");
                bTotalCntSt = true;
            }
            //No changes, do nothing
            if (sTotLastProj == sCurrentProject)
            {
                return string.Empty;
            }
            else
            {
                //There is a change, therefore compute the total.
                Decimal dGetTotalbyProject = fSumTotalbyProject(sTotLastProj, false);
                String sNewRow = "";
                if (sTotLastProj == "&nbsp")
                    sNewRow = String.Format("<tr class='grouptot'><td colspan='6'>" + 
                                            "Total for tasks with unassigned Project: {1}</td></tr>", 
                                            sTotLastProj, dGetTotalbyProject);
                else
                    sNewRow = String.Format("<tr class='grouptot'><td colspan='6'>"+
                                            "Total for Project {0}: {1}</td></tr>", 
                                            sTotLastProj, dGetTotalbyProject);
                sTotLastProj = (string)Eval("project_cd");
                return sNewRow;
            }
        }
        else
        {
            Decimal dGetTotalbyProject = fSumTotalbyProject(sTotLastProj, false);
            String sNewRow = String.Format("Total for Project {0}: {1}", sTotLastProj, dGetTotalbyProject);
            return sNewRow;
        }
    }

What we do here is to return some HTML so the contained in the template displays the appropiate text for each group break.
As you probably noticed the fTotalbyProject method calls another method named fSumTotalbyProject, which is the one that does the numeric sum of the records, it takes a second argument to indicate if total should be done by Project or calculate the grand total.
This function uses LINQ and assumes you have a LINQ DB Model previously defined.

    protected Decimal fSumTotalbyProject(string aProject, bool bIsGrandTotal)
    {
        string aUserName = aUserNameVariable;
        DateTime dStDate = Convert.ToDateTime(YourTextBox1.Text);
        DateTime dEndDate = Convert.ToDateTime(YourTextBox2.Text);
        TasksLinqDataContext db = new TasksLinqDataContext();
        if (aProject == "&nbsp") aProject = "";
        try
        {
            if (!bIsGrandTotal)
            {
                var dbTaskLabor = (from vtlbr in db.tasksTable
                                    where vtlbr.staff_name == aUserName &&
                                    vtlbr.labor_date >= dStDate && 
                                    vtlbr.labor_date <= dEndDate &&
                                    vtlbr.project_cd == aProject
                                    select vtlbr.labor_time).Sum();
                return dbTaskLabor;
            }
            else
            {
                var dbTaskLabor = (from vtlbr in db.tasksTable
                                    where vtlbr.staff_name == aUserName &&
                                            vtlbr.labor_date >= dStDate && 
                                            vtlbr.labor_date <= dEndDate
                                    select vtlbr.labor_time).Sum();
                return dbTaskLabor;
            }
        }
        catch (Exception Exc)
        {
            ErrorLbl.ForeColor = System.Drawing.Color.Red;
            ErrorLbl.Text = Exc.Message;
            return 0;
        }
    }

 

To signal that there is a new Project, we defined a row in our template to display the project name each time that there is a change. The method fGroupingRowIfNewProject handles that logic. Again, we are just returning some HTML to add a cell to the table and display the text "Project : " the HTML is placed in the appropiate row thanks to our template.

    protected string fGroupingRowIfNewProject()
    {
        //Get the data field value of interest for this row
        String sCurrentProject = (string)Eval("project_cd");
        //Specify name to display if current project is NULL
        if (sCurrentProject == "&nbsp")
            sCurrentProject = "Unassigned Project";
        else
            sCurrentProject = "Project: " + sCurrentProject;
        //See if there's been a change in value
        if (sLastProject != sCurrentProject)
        {
            //There's been a change! Record the change and emit the table row
            sLastProject = sCurrentProject;
            return String.Format("<tr class='group'>"+
                                  "<td colspan='6'>{0}</td></tr>", 
                                  sCurrentProject);
        }
        else
        {
            //No Change, return an empty string
            return String.Empty;
        }
    }

Finally, the below code in the DataBound event of the ListView is used to invoke the above functions one last time to display and compute the Total for the last group and then display the GrandTotal for all records.

    protected void lvGroupedLabor_DataBound(object sender, EventArgs e)
    {
        Literal lSubTotal = lvGroupedLabor.FindControl("litSubTotal"as Literal;
        lSubTotal.Text = fTotalbyProject(true);
        Literal lGrandTotal = lvGroupedLabor.FindControl("litGrandTotal"as Literal;
        lGrandTotal.Text = "Grand Total: " + 
                Convert.ToString(fSumTotalbyProject(String.Empty, true));
    }

Happy Coding!
Will 






Saturday, September 17, 2011

Configuring Visual Studio 2010 to Use a Specific Page with a WCF Application Project

Consider the following scenario, you have a VS Solution and you may have multiple projects on it. Some of them could be a WCF Application project with multiple WCF Service Classes.
By default Visual Studio will configure the project options to start the WCFTest Client with the Service1 reference when you run the project, however this does not happen if you have a solution with multiple projects in it.
After some investigation, the best way to do this is in my opinion is to go to the Project Properties, there click on the Web tab and select Specific Page, browse to the Service Class that you wish to use as a default. This will force the WCFTestClient to always use that page when you debug or run.

Happy coding!

Friday, September 16, 2011

Panoramic pictures with iPhone

There is a great app out there from Microsoft that allows you to take 360 panoramic pictures. Check out Microsoft Photosynth from the Apple App Store.
You can publish the pictures to Facebook and upload them directly to the Photosynth site which offers a very nice interface to view your creation.
Have fun,
Will

How to load .Net Membership and Role Capabilities on a SQL Server database

When you incorporate the Membership and Role capabilities to your ASP.Net project, Visual Studio configures a database connection that assumes you are using SQLSERVEREXPRESS.
However, most of the time you probably want to deploy your application in SQL Server 2005/2008, to do this you can use the ASP.NET SQL Server Setup application.
It provides a wizard that is very easy to use.

To open the Wizard, go to the Start Menu and click on the Visual Studio 2010 folder, then go to Visual Studio Tools and click on Visual Studio 2008 Command Prompt

Type: aspnet_regsql

The wizard will guide you, upon completion all the necessary database schema and stored procedures are loaded and ready for you to program your application in ASP.Net using the features provided by the Membership objects.

Below are some screenshots of the wizard in action.










Thursday, September 15, 2011

How to Create and Execute Dynamic SQL on T-SQL

Have you ever had the need to use dynamic SQL?
Chances are that you had the need to manipulate a string to conform a SQL statement and then execute it via a SQLCommand -> CommandType.Text
This is common technique in the .NET world, b
ut How about using dynamic SQL on a Stored Procedure?


At times, I had the need to bring data from a table column and then use it to conform a SQL Statement that will then be executed on demand. For example:

Imagine you have a table named t_settings, with a field named target_db. In this field we store a database name.

Now, using the data on this field you could do something like:


declare @sql varchar(255)
select @sql = 'select * from ' + rtrim(target_db) + '..another_table where 1=1' from t_settings
execute (@sql)

The execute statement provides all the magic. It allows you to execute a string command just as you would do with a SP.

Below is a somewhat silly example that you can try if you have the AdventureWorks database available.

declare @ContactID varchar(3)
declare @Sql varchar(255)
select @ContactID = Cast(ContactID as varchar) from person.contact where EmailAddress = 'kim3@adventure-works.com'
select @ContactID
--Manipulate the String that will become the query that we need to execute.
select @Sql = 'Select * from Sales.SalesOrderHeader where contactid = ' + @ContactID
execute (@sql)


Happy Coding,
Will


Where to Download the AdventureWorks Databases for SQL Server 2008 and 2008 R2



The sample AdventureWorks sample databases are no longer available on the SQL Server site.
They are now located on the CodePlex site.
Below are the URLs:

AdventureWorks for SQL Server 2008
AdventureWorks for SQL Server 2008 R2

How to store external files to your iPhone



As you know, storing files to your iPhone is not a feature provided by iTunes, the obvious work around is to e-mail your self a file and view it on-line, however it would be nice to read a Word document or Excel Sheet while you are seated on your plane's seat offline since you have no access to the internet.

I tried several apps that give you that ability, Air Sharing is a product that comes in two versions, I started with the basic version and then upgraded. In fact you can move a file from your desktop and then view it on your iPhone.

However, I came across a much better solution, the best part is that is free. Go to DropBox and sign up for an account. DropBox is a free service that allows you to install a small application on your desktop, then you can drop files on the DropBox area of your disk.
The interesting part is that they have a free iPhone App, you download it, sign to you account and then you can view the same files, you uploaded from your desktop. The best part is that from the iPhone App, you can then make a local copy which you can view any time offline.

I've used it succesfully with Microsoft Word, Excel, PDFs and even Zipped files. Very cool!

Will

Short Battery Life on iPhone 4



I usually have my iPhone configured to connect to a number of wireless networks, home, work and other public networks.
The other day, my battery that normally lasts all day long (even on days with heavy usage) was almost empty after only three hours.

I tried a hard reset (pressing the Sleep/Wake and Home buttons simultaneously for about 10 seconds) but that did not work.

I was getting ready to reset to factory settings or taking the phone to the Apple Store - Genius Bar which probably means spending a few dollars, then I read about trying to reset the Network Settings

I was a little bit skeptical.

However, It worked like a charm, my iPhone 4 is back to normal.

The only caveat was having to setup the email accounts and network connections, but that really didn't take that long.

For those who don't know how to reset the network settings, go to Settings -> General -> Reset and select Reset Network Settings.

HTH,
Will



How to limit the amount of memory used by Microsoft SQL Server



My good buddy in Bangkok asked me: How can we limit the amount of memory used by SQL Server?
It is a good question, because by default SQL Server will try to allocate as much memory as possible.
Assuming that performance is not an issue for you, (perhaps because you are working on a development box with limited resources) you may want to limit how much memory your instance of SQL Server uses.

To configure this setting, open the SQL Server Management Studio, then connect to the server.
Right click on the Server Name and select Properties, a window will open and here you will able to limit the amount of memory SQL Server uses.

Notice that the amount is specified in MB.

Since this is a server option, you may need to either stop the MSSQLSERVER service.
I didn't try using the 'Reconfigure' statement but it would be worth a try.

Cheers,
Will

September 15

Happy Independence Day, Mexico!

How to Manually Add the SQL Server Reporting Services Report Viewer Web Part



While working with a customer on the setup of a brand new SQL Server 2008 Report Server with SharePoint integrated mode, we discovered that the SQL Server Report Viewer WebPart was not available in SharePoint 2007 when we tried to create a new Web-Part Page.
Although we did install the SQL Reporting Services Add-In the web part was not displayed. I have experienced this issue randomly in the past.
One fact that is misleading is that when you create a new Web-Part page, and you try to add the web part from the selection window, you have a couple of choices that would appear that may give you the ability to Display an RDL Report, Two Web-Parts named Report Viewer and Report Viewer Explorer were available, but based on previous experienced I've never been able to configure them to display the RDL since they appear to work using a relative URL which I'm not familiar with. Therefore I concluded that the SSRS Web Part for the Add-In was not setup in the server.

Below is what we did to manually enable the SQL Reporting Services Web Part.

  • Go to the Main Site and click on Site Actions -> Site Settings -> Modify all Site Settings
  • Once there, go to Galleries -> Web Parts
  • This should display a list of the webparts, notice the New button in the top left corner

  • At this point you should see a list of WebParts available in the server, scroll down or find the web part named Microsoft.ReportingServices.SharePoint...

  • Click on the checkbox and hit Populate Gallery
  • The web part is now available on the Server
  • Sometimes, when you manually add the web part, the description that you will see when adding it to the web part is not very meaningful, therefore is a good idea to edit the description and maybe add some comments. To do this notice the Edit icon next to the newly added WebPart

  • Below is the edit screen, click OK when done

  • After this step, try adding a new Web-Part Page, when you click on Add Web-Part the SQL Server Reporting Services Web Part should be available

  • Now you can edit the web part properties and use the Browse button (...) to easily select the report you want to display
  • Happy Programming!

Blog Start

Hello,
During the lenght of my career, I have noticed at times that Software Development can be more of an art than a science. This is why I decide to start this blog, so I can share my experiences with other people and hopefully help other fellow developers in their work.
The internet is an inmense library of knowledge that I use every day to perform my work, so I would like to add my two cents to that library.
Hope my posts can be of help.
Sincerely
Will