Programming Journal C#, Java, SQL and to a lesser extent HTML, CSS, XML, and regex. I made this so other programmers could benefit from my experience.

Tuesday, December 23, 2008

Add Google Charts with .NET

Add Google Charts with .NET.
Go to:
Download DLL.
Save and Unzip to location.

From your project, add reference.
Select DLL.
Copy source code from below:

// using GoogleChartSharp;

int[] data = new int[] { 40, 30, 20, 10, 0 };

// Specify chart width and height in pixels
LineChart chart = new LineChart(150, 150);

// SetData will accept int[] and float[] for single data sets

string imageUrl = chart.GetUrl();
Image1.ImageUrl = imageUrl;
Run. Enjoy. Modify. Visit these sites for more:


Get the ApplicationName for Stored Procedure Parameter

Sometimes the ApplicatonName is necessary for Membership stored procedures. You can access the ApplicationName as follows:

string appName = Membership.ApplicationName;


Get the Current Time for Database Storage

To get the current datetime in UTC format in .net use following:

DateTime dateUTC = DateTime.Now.ToUniversalTime();
You can then store this date in your database.
If you want to display it back to local time use:

DateTime dateLocal = dateUTC.ToLocalTime();


The Difference Between NVARCHAR and VARCHAR

The main difference between NVARCHAR and VARCHAR is that NVARCHAR extra space allows for easier multi-language support. Therefore, I switched my database's VARCHARs to NVARCHARS.


Accessing ConnectionString from CodeBehind

Accessing a ConnectionString from CodeBehind requires your connection string to be defined in the web.config file.

Then use this source code:

ConnectionString = System.Configuration.ConfigurationManager.AppSettings["MySetting"].ToString();

I customized my BasePage with a utility function as follows:

public string getConnectionString(string vConnectionString)
System.Configuration.Configuration rootWebConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath);
System.Configuration.ConnectionStringSettings connString = null;
string connectionString = string.Empty;
if (0 < rootWebConfig.ConnectionStrings.ConnectionStrings.Count)
connString =
if (connString != null)
connectionString = connString.ConnectionString;
return connectionString;


Use ConnectionString Web.config

I setup the ConnectionString in my Web.config file and encountered a "'ConnectionStringSettings' is not defined error. The solution was to add the 'system.configuration' library to the project! (Project, Add reference.., then find the library then add it!).


Keeping a DropDownList's DataSource Updated

The DropDownList does not manage selected state like other controls. Therefore, I use a Session variable to store an index to keep track of the currently selected item:

protected void Page_Load(object sender, EventArgs e)
if (!Page.IsPostBack)
Session["listId"] = ddlList.SelectedValue;

Now, I add the SelectedIndexChanged event with the following code:

protected void ddlList_SelectedIndexChanged(object sender, EventArgs e)
Session["listId"] = ddlList.SelectedValue;


Use a CustomControl to Set UserId and Use as a DataBindable Property

Use a CustomControl (named MembershipUser) to Set UserId and Use as a DataBindable Property. Create a MembershipUser CustomControl that has a property named Value that can be DataBound. For example MembershipUser1.Value would fetch the userId:

[Browsable (true)]
public string Value
MembershipUser currentUser;
currentUser = Membership.GetUser();
if (currentUser == null)
return string.Empty;
return currentUser.ProviderUserKey.ToString();
Reference: This is from

Be Careful About uniqueId Parameter in Stored Procedure

Here is an error I received when my UniqueId parameter in a stored procedure was not valid:

Server Error in '/' Application.
Conversion failed when converting from a character string to uniqueidentifier.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Conversion failed when converting from a character string to uniqueidentifier.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

I corrected to make sure the parameter was a valid uniqueId paramater and not null.

Modifying DataBound Hyperlinks Text

Modifying a GridView's Databound Hyperlink text since I couldn't do this easily in the ItemTemplate, I moved the Replace function to the DataBound of the GridView:

protected void GridViewStocksInLists_DataBound(object sender, EventArgs e)
int rowId;
string imgUrl;
string navUrl;
for (rowId=0; rowId<GridViewStocksInLists.Rows.Count;rowId++) {
imgUrl = string.Empty;
navUrl = string.Empty;
imgUrl = ((HyperLink)GridViewStocksInLists.Rows[rowId].FindControl("hlChart")).ImageUrl.Replace('$', '^');
((HyperLink)GridViewStocksInLists.Rows[rowId].FindControl("hlChart")).ImageUrl = imgUrl;
navUrl = ((HyperLink)GridViewStocksInLists.Rows[rowId].FindControl("hlChart")).NavigateUrl.Replace('$', '^');
((HyperLink)GridViewStocksInLists.Rows[rowId].FindControl("hlChart")).NavigateUrl = navUrl;
This replaced $ with ^ in my HyperLink.

Here is the GridView that contains the HyperLink:

<asp:GridView ID="GridViewStocksInLists" runat="server" DataSourceID="SqlDataSourceGetStocksInList">
<RowStyle CssClass="tableAnalysisRow" />
<HeaderStyle CssClass="tableAnalysisHeader" />
<AlternatingRowStyle CssClass="tableAnalysisAlternatingRow" />
<asp:TemplateField HeaderText="Chart">
<asp:HyperLink ID="hlChart" runat="server"
ImageUrl='<%# Eval("Symbol", "{0}").ToLower() %>'
NavigateUrl='<%# Eval("Symbol", "{0}&t=1y").ToLower() %>' >


Remove HTML Tags

Remove HTML Tags from a string:

public static string RemoveHtml(string txt)
return Regex.Replace(txt, @"<[^>]*>", string.Empty);


Wednesday, December 17, 2008

Using AutoComplete Ajax Control With Separate ID Field from Name Field

In the previous post, I detailed how to use the SQL stored procedure to select matches.

AutoComplete Extender offers a convenient way to select values froma TextBox. One problem is that multiple details can be displayed, but the whole text is selected by default. I wanted to allow multiple AutoCompletes, select the id without the other details and append the appropriate delimeter in between entries. First, I setup the WebService method. Note that the Stock object has Symbol and Name properties:

public string[] GetStocksInPrefix(string prefixText, int count)
int curCount = 0;
if (count == 0)
count = 10;
List<string> items = new List<string>();
JavaScriptSerializer jss = new JavaScriptSerializer();
string[] stocks = null;
string connectionString = getConnectionString("ConnectionString");
SqlDataReader rdr = null;
SqlConnection conn = new SqlConnection(connectionString);
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("sproc_aspnet_GetStocksByPrefix", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Prefix", prefixText+"%");
rdr = command.ExecuteReader();
string tmp = string.Empty;
Stock _stock = null;
while (rdr.Read() && curCount<count )
_stock = new Stock(rdr["Symbol"].ToString(), rdr["Name"].ToString(), rdr["Exchange"].ToString());
tmp = rdr["Symbol"] + "\t" + rdr["Name"];
items.Add(AutoCompleteExtender.CreateAutoCompleteItem(tmp, jss.Serialize(_stock)));
catch (Exception ex)
if (rdr != null) rdr.Close();
if(conn!=null) conn.Close();
return items.ToArray();

Then, I add the javascript event, 'OnSymbolSelected',to the OnClientItemSelected event in the AutoComplete Extender:

<asp:TextBox ID="txtSymbols" runat="server" Width="300px"></asp:TextBox>
<cc1:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server"
TargetControlID="txtSymbols" ServiceMethod="GetStocksInPrefix"
ServicePath="~/WebServiceAutoCompleteSymbol.asmx" MinimumPrefixLength="2"
CompletionListHighlightedItemCssClass="watermark" CompletionSetCount="10"
DelimiterCharacters=", "
EnableCaching="true" CompletionInterval="1000" OnClientItemSelected="OnSymbolSelected"

Finally, add the javascript function to the aspx page. This function reads the Stock object into results, takes the SelectedText and gets the original string before the new text was completed by replacing the completed text with an empty string. If it is an additional field (that does not contain ' ' or ',', then only the id (symbol in this case) is added. Othewise, the original id plus the ' ' delimeter and the new id is added:

<script type="text/javascript" language="javascript">
function OnSymbolSelected(source, eventArgs)
var results = eval('(' + eventArgs.get_value() + ')');
if (results.symbol != null) {
var symbols = document.getElementById('<%= txtSymbols.ClientID %>').value;
var original = symbols.replace((results.symbol+'\t','');
if (original.indexOf(' ')>0 || original.indexOf(',')>0)
document.getElementById('<%= txtSymbols.ClientID %>').value = original + (' '+ results.symbol);
document.getElementById('<%= txtSymbols.ClientID %>').value = results.symbol;


Using LIKE in SQL

Be careful in the order of parameters while using LIKE in T-SQL. The following stored procedure did not work @Prefix LIKE dbo.aspnet_Stocks.Name. It must be reversed:

ALTER PROCEDURE [dbo].[sproc_aspnet_GetStocksByPrefix]
@Prefix as varchar(50)=NULL
FROM dbo.aspnet_Stocks
WHERE ((Symbol LIKE @Prefix) OR ( [Name]LIKE @Prefix))

This was used in an AutoComplete AJAX control. The following reference describes the setup.


A more efficient stored procedure would use a FullText Catalog indexed on a Text Column as described here:

Tuesday, December 16, 2008

Sorting a GridView

Sorting a GridView with a DataSource requires an external storage variable for a the last SortDirection. First, I call the GridViewReport_Sorting in the Sorting event in the GridView. A GridView has e.SortExpression and e.SortDirection. I combine the two to form a unique Session variable and then set the SortDirection to compare at later sorts:

protected void GridViewReport_Sorting(object sender, GridViewSortEventArgs e)
DataTable dt;
DataView dv;
dt = (DataTable)Cache["dvStocks"];
dv = new DataView(dt);
string direction = SortDirection.Descending.ToString();
string curDirecton = SortDirection.Ascending.ToString(); // set to default
string key = string.Concat(e.SortExpression, e.SortDirection);
if(Session[key]!=null) {
curDirecton = (string)Session[key];
if (curDirecton.Contains("Ascending"))
direction = Global.DESC; //DESC
Session[key] = SortDirection.Descending.ToString();
direction = Global.ASC; //ASC
Session[key] = SortDirection.Ascending.ToString();
dv.Sort = e.SortExpression + " " + direction;
Cache["dtCalculated"] = dv.ToTable();
GridViewReport.DataSource = dv;


Monday, December 15, 2008

Using static strings and objects to increase performance and maintainence

I found this global variable article below to help me with using static strings and objects to increase performance and maintainence.

For example, suppose I have an error message, "Error: ", that I might want to change to "There was a problem: ". Instead of search and replace n times, I could just use a static string variable to change the string value.

Sample Global class:

/// <summary>
/// Summary description for Global
/// </summary>
public static class Global
public static string CSS_HIDDEN
get { return "hidden"; }
public static string CSS_EMPTY
get { return string.Empty; }
public static string CSS_CONTENT
get { return "content1"; }
public static string ERROR_HEADER_HTML
get { return "<red>There was a problem:</red></br>"; }
public static string ERROR_HEADER
get { return "There was a problem:"; }
Then one just accesses it with Global.ERROR_HEADER. Notice that there is no declarative instantiation for the static class.


Sunday, December 14, 2008

Use securitytrimming to limit role views of Menu and SiteMap

Use securitytrimming to limit role views of Menu and SiteMap.

Modify the Web.config file's configuration tag with:

<siteMap defaultProvider="secureProvider">
<add name="secureProvider" type="System.Web.Xml


siteMapFile="web.sitemap" securityTrimmingEnabled="true"/>
Then modify Web.sitemap to make sure roles parameter is set to * or admin. Notice how I explicitly set the nodes roles parameter in the links outside the website

<?xml version="1.0" encoding="utf-8" ?>
<siteMap xmlns="" >
<siteMapNode url="SiteMap.aspx" title="Site Map" description="Site Map">
<siteMapNode url="Default.aspx" title="Home" description="Home" roles="*">
<siteMapNode url="Login.aspx" title="Login/Logout" description="Login or Logout" />
<siteMapNode url="About.aspx" title="About" description="About" />
<siteMapNode url="~/Tools/Tools.aspx" title="Tools" description="" roles="*">
<siteMapNode url="~/Tools/GrowthAnalysis.aspx" title="Growth Analysis" description="" />
<siteMapNode url="~/Tools/FundamentalAnalysis.aspx" title="Fundamental Analysis" description="" />
<siteMapNode url="~/Tools/PriceToSalesAnalysis.aspx" title="Price To Sales Analysis" description="" />
<siteMapNode url="~/Tools/TechnicalAnalysis.aspx" title="Technical Analysis" description="" />
<siteMapNode url="~/MyLists/MyLists.aspx" title="My Lists" description="" roles="*">
<siteMapNode url="~/MyLists/Management.aspx" title="Management" description="" />
<siteMapNode url="~/MyLists/Tracker.aspx" title="Tracker" description="" />
<siteMapNode url="~/MarketWatch/MarketWatch.aspx" title="Market Watch" description="" roles="*">
<siteMapNode url="~/MarketWatch/MarketSummary.aspx" title="Market Summary" description="" />
<siteMapNode url="~/MarketWatch/SectorSummary.aspx" title="Sector Summary" description="" />
<siteMapNode url="~/MarketWatch/IntermarketSummary.aspx" title="Inter-market Summary" description="" />
<siteMapNode url="~/MarketWatch/WorldMarketSummary.aspx" title="World Market Summary" description="" />
<siteMapNode url="~/Media.aspx" title="Media" description="" roles="*">
<siteMapNode url="" title="Bloomberg Audio/Video" description="" roles="*" />
<siteMapNode url="" title="Real Story With Aaron Task" description="" roles="*" />
<siteMapNode url="" title=" TV" description="" roles="*" />
<siteMapNode url="" title="Wall Street Journal" description="" roles="*" />
<siteMapNode url="" title="New York Times Business" description="" roles="*" />
<siteMapNode url="" title="USA Today Markets" description="" roles="*" />
<siteMapNode url="" title=" Headline Hits" description="" roles="*" />
<siteMapNode url="" title="PBS Nightly Business Report" description="" roles="*" />
<siteMapNode url="" title="RedOption Shadow Trader Video" description="Weekend Update" roles="*" />
<siteMapNode url="~/Commentary.aspx" title="Commentary" description="" roles="*">
<siteMapNode url="" title="Stock Monger" description="" roles="*" />
<siteMapNode url=";t=Cramer" title="Jim Cramer" description="" roles="*" />
<siteMapNode url="" title="Richard Suttmeier" description="" roles="*" />
<siteMapNode url="" title="Jim Jubak" description="" roles="*" />
<siteMapNode url="" title="The Big Picture With Barry Ritholtz" description="" roles="*" />
<siteMapNode url="~/Admin/Admin.aspx" title="Admin" description="" roles="Admin">
<siteMapNode url="~/Admin/UpdateAllStocks.aspx" title="Update All Stocks" description="" roles="Admin" />
<siteMapNode url="~/Admin/StockScaffold.aspx" title="Stock Scaffold" description="" roles="Admin" />
<siteMapNode url="~/Admin/ManageRoles.aspx" title="Manager User Roles" description="" roles="Admin" />
<siteMapNode url="~/Admin/ManageLists.aspx" title="Manage Lists" description="" roles="Admin" />