Tuesday, December 18, 2007

SQL Server Reporting with Expressions


I wanted to talk about Expressions within the SQL Text of the Dataset in SQL Reporting. Since I don't see any blogs or posts in google on the subject I thought I would shed some light to help you out. Have you ever been tasked with a report that had say 4 filters? Instinctively you would write your SQL and have this massive un-maintainable Where statement that was full of OR clauses right? It may look something like SELECT * FROM TableA WHERE (@StartDate IS NULL AND @EndDate IS NULL AND @Parameter1 = '-1' AND @Parameter2 = 'All') OR (...) which is normal if you didn't know that you could build this sql statement using Expressions. Once this Where clause is done after you have thought out every scenario the user may choose you are left with 100 lines of sql code. Now this may be an over exaggeration but you get the point. To start this off correctly, the best method to begin with is your Select statement. Add your select then execute it. This will have SQL Reporting automatically add the fields for you. Then take your select statement and change it like so (Using same example):

= "SELECT * FROM TableA"
& "WHERE 1=1"
& IIF(IsNothing(Parameters!StartDate.Value), "", " AND StartDate >= '" & Parameters!StartDate.Value & "'')
& IIF(
IsNothing(Parameters!EndDate.Value), "", " AND EndDate <= '" & Parameters!EndDate.Value & "'')
& IIF(Parameters!Parameter1.Value = "-1", "", " AND Parameter1 = '" & Parameters!Parameter1.Value & "'")
& IIF(Parameters!Parameter2.Value = "All", "", " AND Parameter2 = '" & Parameters!Parameter2.Value & "'")

As you can see we are starting off our Where clause to be 1=1 just to alleviate complication, then based on the values of our parameters we are injecting our filters into the where clause. Using this technique you have covered all your scenarios and are now ready to move on.

Friday, September 07, 2007

Sea Gull Eats Trash

While eating at a restaurant in San Diego, CA we noticed a bird hopping into the trash, removing a takeout box and open it.  As we continued watching we noticed the bird was smart enough to open the box and eat the contents.  Notice the trash all over the street, yeah busted on tape.  Sorry for the bad resolution I used a Treo Phone to record this crime in progress.

Thursday, August 09, 2007

Vista Pre-SP1 Release from Scott Hanselman's blog

Scott wrote an article on a Pre-SP1 update for Vista users.  This patch made my wifes pc respond much better after installing the Performance Update.  Not sure if its the computerized "Placebo Effect" or what but it sure seems faster.  Anyway I wanted to share this with you. 

The link is http://www.hanselman.com

Wednesday, August 08, 2007

The Market is looking UP

Finally a day when we can look at our stock and smile.  This summer was a drag in the market but it's soon coming to an end.  A great story of what happened to me today.  I bought this stock FCSX for $52 and change, when it was only 23 cents up today.  The stock in just a few hours went up nearly 3 dollars so I sold it.  Literally like a minute after I sold it and I'm not exaggerating it dropped $5 in about 2 minutes.  Had I went to get a drink or something I would have been out a few hundred dollars.  What a day...

Friday, August 03, 2007

A little extra for everyone

Ever try to bust a beer bottle with your bare hands only blowing the bottom of the bottle out.  I did it here in this clip so you can see how it is done.
Disclaimer: No beer was waisted in the making of this movie. Water is the liquid used.

The Ultimate Grid for ASP.NET

All of you ASP.NET developers out there I have a grid of choice for you.  After using DevExpress's ASP.NET Grid Control I am sold.  This grid does it all and with little effort.  If your looking for a grid component either web or winforms I highly recommend this product.  Its the next best thing since automobiles... :)  And no I don't get any handouts from any the companies products I recommend.  I inform you on great tools I use daily and hope they make your life as easy as they have made mine.

Raising the bar for E-commerce Solutions

Imagine your e-commerce site out of the box with a nice customizable theme suitable to fit your sites needs.  Now imagine that same site with the capability to drag and drop a shopping cart, a price module, a user account module, or a payment gateway to name a few without writing a line of code onto your live site and it working.  Take all this and throw in a QA process that allows you to funnel your site changes through a flow of sign-offs before it can be published as a live change with the ability to save as a draft until the final sign-off.  Now take a strong back-end with a built-in CMS that gives you full control of your e-commerce site without having to write a line of code.  And finally, throw in an IS layer that communicates with Microsoft Dynamics and you have Mediachase's ECF 5.0.  Mediachase is the leader of e-commerce solutions for the .NET Framework.  This release is not yet available and is scheduled for the beginning of next year 2008.  This will be a must have if you want a stable e-commerce site up and running that you can trust and depend on today as well as tomorrow. 

Stock Market Goes down yet again

Another day with the market down on behalf of our friendly mortgages and banks.  With the anticipation of VMWare going into IPO August 14th 2007 the best buy is of course the parent company of which will receive a lot of attention EMC.  EMC owns around 93% of VMWare with the backing of Cisco and Intel.  The suspense to make some good money soon is about to materialize.  Happy Trading!!

Thursday, July 26, 2007

Using our Task we built earlier

Using our Task we built in Automating your Team Builds with Team Foundation Server we are now going to configure it within the build project file. To start off we need to reference the compiled dll.

<UsingTask TaskName="Microsoft.Sdc.Tasks.File.Execute"
AssemblyFile="C:\TaskBuilds\Microsoft.Sdc.Tasks.dll" />


Lets say after the source is compiled you are now ready to execute an external application you wrote to configure an Web Site or something using the compiled output code you could do something like this:



<Target Name="AfterCompile">
<File.Execute TreatErrorsAsWarnings="true"
File="C:\MyFilePath\myfile.exe"
CommandLineArgs="-ws $(BinariesRoot)" />
</Target>
Here the MSBUILD engine will call your File.Execute method
after it has finished compiling the solution.





Automating your builds in TFS

In development we consistently try to find ways to make our processes faster and more useful. As for deployment and test builds, Team Foundation Server has made it easy to automate your builds by use of Tasks performed within a build project. Developers can build classes implementing the Microsoft.Sdc.Tasks.TaskBase class (derived from Microsoft.Build.Utilities.Task) which has an overrided method named InternalExecute. As a short example to the powerful uses of this class I will demonstrate writing your own class that will handle executing an external application.

In this demonstration I will be using Micsoft.Sdc.Tasks source project to add my class.

First we need to create a class, I am doing so in the File folder as this pertains to files and System.IO. I will name this class Execute. Add the basic namespaces we will need:

using System;
using System.Xml;
using System.Globalization;
using System.IO;
using Microsoft.Build.Framework;
using System.Collections;
using System.Diagnostics;
public class Execute : TaskBase




We might want to add a property that allows us to
specify if an error occurs to treat it as a warning and move on.
/// <summary>
/// If TRUE any errors will be logged as warnings
/// </summary>
public bool TreatErrorsAsWarnings
{
get { return treatErrorsAsWarnings; }
set { treatErrorsAsWarnings = value; }
}
And of course we need Command Line Arguments:
private string _CommandLineArgs;
public string CommandLineArgs
{
get
{
return _CommandLineArgs;
}
set
{
_CommandLineArgs = value;
}
}
private ITaskItem file;

/// <summary>
/// The file to be Executed
/// </summary>
/// <value>An ITaskItem of file</value>
[Required]
public ITaskItem File
{
get { return this.file; }
set { this.file = value; }
}








Now we need to override the Internal Execute method
so our code is called from the MSBUILD.
/// <summary>
/// Performs the action of this task.
/// </summary>
protected override void InternalExecute()
{
try
{
if (System.IO.File.Exists(File.ItemSpec))
{
// Display the File we are about to execute
base.Log.LogMessage("Executing File - " +
File.ItemSpec + "\rCommand Line Args - "+CommandLineArgs);
// Setup our startup information
ProcessStartInfo psi = new ProcessStartInfo();
string[] args = CommandLineArgs.Split(' ');

// Retrieve the arguments
if (args.Length > 0)
{
args[0] = "\"" + args[0] + "\"";
foreach (string str in args)
{
CommandLineArgs = str + " ";
}

CommandLineArgs = CommandLineArgs.TrimEnd(' ');
}
// Execute
psi.FileName = File.ItemSpec;
psi.Arguments = CommandLineArgs;
Process.Start(psi);
}
else
{
if (!TreatErrorsAsWarnings)
throw new
FileNotFoundException("File does not exists - " + File.ItemSpec);
else
base.Log.LogWarning("File does not exists - " +
File.ItemSpec);
}

}
catch (Exception ex)
{
if (TreatErrorsAsWarnings)
{
Utilities.RethrowUnlessFileIO(ex);
}

this.LogError(File, ex);
    }
}

Now we have a class we can use to execute external files within
our team build. Notice the CommandLineArgs is expecting the
argument to be separated using the standard spacing (CHAR 32).
You can customize this to use commas or semicolons if you like.
Compile the Microsoft.Sdc.Tasks project and copy the dll to your team
server in the location you are keeping your automated build files.
To configure look out for Configuring your Customized Task in this blog.



Sunday, April 08, 2007

Happy Easter

Today is the day Christ has risen according to scripture.  I just wanted to post a very happy and blessed Easter to all. 

Friday, April 06, 2007

Programming for Microsoft Dynamics (Navision)

Navision as we all know is very powerful with a very robust architecture.  The application allows you to add, edit, and design your own table and forms as well as reports.  The only problem is connecting to dynamics with your own application and retrieving data from it.  Western Computer an Oxnard, CA based company developed their admiral program named Fusion which breaks the barriers of connecting to Navision from within your .NET applications.  The company wrote a client they called the QueryLanguage to communicate with Fusion and push or pull data to Navision.  An example code snippet would look like so:

 

 public DataSet GetProfessionalAffiliations()

{

DataSet ds = null;

QueryGroup queryGroup = new QueryGroup(true);

Query query = queryGroup.NewQuery("ProAffiliation", "Professional Affiliation", WesternComputer.Fusion.QueryLanguage.CommandType.Select);

 

#region Set Columns

 

query.Columns.Add("Code");

query.Columns.Add("Description");

 

#endregion

 

// Execute query

ds = Globals.Gateway.ExecuteXmlToDataSet(queryGroup.ToXml());

 

// Check for Errors

string strMessage = "";

if (Globals.HasErrors(ds, out strMessage))

{

throw new Exception(strMessage);

}

 

return ds;

}


This simple query would go out to the FusionGateway (Web Service) and pull down the Professional Affiliation table records with the Code and Description Columns.  If any errors occurred in your query or on the Navision side a table is returned with TableName Query#Error.  This table will hold all errors that occurred so you can display them to the end user.  The best part, the error messages returned are the same error messages you are already accustomed to seeing in Navision.  They are Navision Error Messages.  The learning curve on this product is very low as it took myself 2 weeks to get my first application for Navision up and running on a Mobile Q device.  There are other features this provides such as StoredProcedure which will query a function written in Navision and return the result.  If you work with Navision then Fusion is a must have.

 

Friday, March 09, 2007

Implementing a Double Buffer

I recently had a project with the Motorola Q pulling data from a web service and displaying it in a graphical skin. I ran into a problem with flickering when painting the new changes using GDI+. To overcome this problem I set a Bitmap class up and painted to it then used the device graphics to paint the final image to the screen instead of painting to the screen directly. To accomplish this simply create the back image like so:


Bitmap _backBuffer = null;

private void Paint(object sender, PaintEventArgs e)
{
if (_backBuffer == null)
_backBuffer = new Bitmap(this.ClientSize.Width, this.ClientSize.Height);

try
{
using (Graphics g = Graphics.FromImage(_backBuffer))
{
g.Clear(Color.White);

// Do your stuff here like paint the back image and any text
g.DrawImage(MyImage, 0, 0);
g.DrawString("My Text", myFont, new SolidBrush(Color.Blue), myRectangleF);
}
} finally
{
e.Graphics.DrawImage(_backBuffer, 0, 0);
}
}


This will prevent the flicker and load images nicely. Also note when you make changes to the underlying dataset or collection or what ever you are using to hold the data you intend to paint make sure you call Invalidate to get the screen to paint again with the new changes.

Tuesday, February 13, 2007

Coderush

One more note before closing tonight, if your not using Coderush or Refactor Pro!, get it now!!!
These two combined will make your daily coding so much faster. I use these everyday and when I work on someones box that doesnt have them, I feel like I have a handycap. Mark Miller is the creator of this magnificant product formerly Eagle Software which I believe he now works for DevExpress which owns the product today. Either way, get to DevExpress.com and purchase this fantastic product as it will make your coding fly...

Nice one Jason

After reading Jasons blog at http://jasonf-blog.blogspot.com I came across an interesting article to myself being a former WoW player as well as Star Craft (Brood War). This type of mistake probably cost someone their job.

http://jasonf-blog.blogspot.com/2007/01/wow-someone-messed-up.html

Serial Port Usage

I had a project assigned to me reading information from a scanner and taking the information retrieved and converting the bytes to base64 and passing the base64 to a decoder which could take a USPS barcode in PDF-417 or DataMatrix and parse out its information into human readable information of all information contained within the barcode using an IBI Dictionary. I cannot demonstrate all parts of the project but I would like to demonstrate gathering information from a port and converting it to base64. This is rather simple and probably elementary for most developers.

First of all, you must create a serial port like so:

SerialPort port = null;

On Load of Form, or constructor do the following:

port = new SerialPort(Port, 9600, Parity.None, 8, StopBits.One);
port.DataReceived += new SerialDataReceivedEventHandler(port_DataReceived);


string Base64Scan = string.Empty;
void port_DataReceived(object sender, SerialDataReceivedEventArgs e)
{
byte[] buffer = new byte[port.BytesToRead];
port.Read(buffer, 0, port.BytesToRead);

// Convert to Base64
Base64Scan = Convert.ToBase64String(buffer);
}

This is it, now some would say why even bother with such a simple blog. The reason, I ran into a problem in the first phase of reading the information into a string using
port.ReadExisting(); which caused a problem because the .NET encodation caused the results to be wrong. When char 255 or FF was recieved, it was converted to char 63 or 3F. By reading the bytes directly, the correct results were returned. Hopefully this will help others that fall into this problem.

Monday, February 05, 2007

Here is a snippet I put together on using UserName token with Web Services3.

MyWebService myService = new MyWebService();
// Create the policy
Microsoft.Web.Services3.Design.Policy policy = new Microsoft.Web.Services3.Design.Policy();
policy.Assertions.Add(new Microsoft.Web.Services3.Design.UsernameOverTransportAssertion());
myService .SetPolicy(policy);
myService.Url = http://whatever the url is;
UsernameToken token = new UsernameToken(username, password, PasswordOption.SendPlainText);
ISecurityTokenManager securityTokenManager = SecurityTokenManager.GetSecurityTokenManagerByTokenType(WSTrust.TokenTypes.UsernameToken);
securityTokenManager.CacheSecurityToken(token);
myService.SetClientCredential(token);
// If your service needs Credentials for connection pass something like this
if (CredentialUserName.Length > 0)
{
System.Net.NetworkCredential cred = null;
// User Credentials
if (CredentialDomain.Trim().Length == 0)
cred = new System.Net.NetworkCredential(CredentialUserName, CredentialPassword);
else
cred = new System.Net.NetworkCredential(CredentialUserName, CredentialPassword, CredentialDomain);
System.Net.CredentialCache credCache = new System.Net.CredentialCache();
credCache.Add(new Uri(myService.Url), "Basic", cred);
if (CredentialDomain.Trim().Length > 0)
credCache.Add(new Uri(myService.Url), "Digest", cred);
credCache.Add(new Uri(myService.Url), "Negotiate", cred);
myService.Credentials = credCache;
}
// Now do your stuff

 
Creative Commons License
Blogged Information and Code is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 United States License.