Formatting Currency in a GridView

To format data in a gridview boundcolumn or Eval statement using the property of the boundcolumn called DataFormatString or include the format in the Eval statement like this: [I added an extra space between the brackets so this would format correctly]
< %# Server.HtmlEncode( Eval("InvoiceAmount", "{0:$#,#.#0}").ToString() ) % >"

I got this from a great article http://blog.stevex.net/string-formatting-in-csharp/

Using the decimal value 4219.60 as my number from the database for a product.

{0}
4219.60

{0:c}
$4,219.60

{0:c2}
$4,219.60

{0:c4}
$4,219.6000

{0:$#.#}
$4219.6

{0:$#,#.#}
$4,219.6

{0:#,#.##}
$4,219.6

{0:$#,#.#0}
$4,219.60

The Value of Datakeys in GridView RowDataBound

To find the value of your gridview attribute Datakeys use this C# code

protected void gvSummary_RowDataBound(object sender, GridViewRowEventArgs e)

{
   DataTable dt = new DataTable();
   if (e.Row.RowType == DataControlRowType.DataRow)
   {
      //find the invoice number - which is the Datakeys
      int inv = Convert.ToInt32(((GridView)sender).DataKeys[e.Row.RowIndex].Value);

    //find the charges grid - populated based on the invoice number
    GridView gvCh = (GridView)e.Row.FindControl("gvCharges");

    dt = maData.TrapRetrievalInvoiceChargesGet(inv);
    //populate a nested grid
    gvCh.DataSource = dt;
    gvCh.DataBind();

  }
}


ASP Code
asp:GridView ID="gvSummary" runat="server" DataKeyNames="Invoice"

...

T-SQL scalar function looping through a cursor

Scalar return valued function - example using a cursor, looping through the records, returning a string of data from all the rows in the cursor.  People say using a cursor in SQL server is not a great idea b/c of performance, so here's a link to do a simlar thing w/o a cursor:
http://www.sql-server-performance.com/articles/per/operations_no_cursors_p2.aspx



To call this function in a select:
select csl.dbo.testfn()

Function code:
CREATE Function dbo.testfn()
returns varchar(5000)
AS
BEGIN

/*
** Cursor method to cycle through the table
to retrieve data for each row
*/

-- declare all variables!
DECLARE @iLic int,
@nLicNum nvarchar(10),
@nLictype nvarchar(15) ,
@return nvarchar(2000)

-- declare the cursor
DECLARE Lic CURSOR FOR
SELECT TOP 10 pk_license,
LicenseNumber,
FK_LicenseType
FROM CSL.dbo.License

OPEN Lic
FETCH Lic INTO @iLic,
@nLicNum,
@nLictype

-- start the main processing loop.
set @return=''

WHILE @@Fetch_Status = 0

  BEGIN
  -- This is where you perform your detailed row-by-row processing.
  --concat fields
  set @return += '*' + convert(nvarchar, @iLic)
  set @return += '*' + @nLicNum
  set @return += '*' + @nLictype

  -- Get the next row.
  FETCH Lic INTO @iLic,
  @nLicNum,
  @nLictype
END

CLOSE Lic
DEALLOCATE Lic

RETURN @return

END

GO

Search SQL Server Trigger Code

If you need to search through trigger code for table or column  names use this code:

SELECT OBJECT_NAME(id)
FROM syscomments
WHERE OBJECTPROPERTY(id, 'IsTrigger') = 1
and lower(text) like '%wordtosearchfor%'

Finding Table Columns in SQL Server

Somestimes you need to know all the tables that have a specific column name.  Use this query:

select o.name
from sys.all_objects o, sys.all_columns c
where o.object_id = c.object_id
and o.type = 'U'
and lower(c.name) = 'thecolumnnameyouwant'
 
type = 'U' is for user tables

Searching SQL Server TSQL Procedure code

In Sql server this is the query you'd use to find procedures with certain words (like tables etc).
SELECT OBJECT_NAME(id)
FROM syscomments
WHERE lower(text) LIKE '%the code i am trying to find%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)
 
In Oracle we would have queried user_source for this information.

.Net - write output text file

public Boolean WriteCSVFile() { try { SqlDataReader dr = this.GetTable("SELECT MtgName,CONVERT(VARCHAR(10),MtgBeginDate,101) as MtgBeginDate," + "CONVERT(VARCHAR(10),MtgEndDate,101) as MtgEndDate," + "MtgCityStZip,MtgLoc1,MtgLoc2,MtgLoc3,MeetingMinutesURL FROM MTGMain WHERE MtgId=@Id"); //if (!Directory.Exists(@"c:\magenda\")) //{ // //Directory.CreateDirectory(@"c:\magenda\"); // Directory.CreateDirectory(@"c:\magenda\"); //} //string outfile = @"c:\magenda\meeting.csv"; string outfile = @".\meeting.csv"; using (StreamWriter sw = File.CreateText(outfile)) { while (dr.Read()) { sw.WriteLine("Meeting: {0}, Begin Date: {1}, End Date: {2}, City State Zip: {3}," + "Location 1 {4}, Location 2 {5}, Location 3 {6}, Meeting URL {7}", dr["MtgName"].ToString(), dr["MtgBeginDate"].ToString(), dr["MtgEndDate"].ToString(), dr["MtgCityStZip"].ToString(), dr["MtgLoc1"].ToString(), dr["MtgLoc2"].ToString(), dr["MtgLoc3"].ToString(), dr["MeetingMinutesURL"].ToString()); } dr = this.GetTable("SELECT CONVERT(VARCHAR(10),DayDate,101) as DayDate," + "StartTime,EndTime,Location FROM MTGDay WHERE MtgId=@Id"); sw.WriteLine("Meeting Day Times:"); while (dr.Read()) { sw.WriteLine("Day: {0},Start: {1}, End: {2} Location {3}", dr["DayDate"].ToString(), dr["StartTime"].ToString(), dr["EndTime"].ToString(), dr["Location"].ToString()); } sw.Close(); System.IO.FileInfo file = new System.IO.FileInfo(outfile); HttpContext.Current.Response.Clear(); HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + file.Name); HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString()); HttpContext.Current.Response.ContentType = "text/csv"; HttpContext.Current.Response.WriteFile(file.FullName); HttpContext.Current.Response.End(); } return true; } catch { return false; } }

.Net method to format phone numbers

public string formatPhone(string phone) { // strips nonnumeric characters from string string strPhone = System.Text.RegularExpressions.Regex.Replace(phone, "\\D", ""); if (strPhone.Length != 10) { return "failed"; } else { try { Int64 iPhone = Convert.ToInt64(strPhone); return ("(" + Convert.ToString(strPhone).Substring(0, 3) + ")" + Convert.ToString (strPhone).Substring(3, 3) + "-" + Convert.ToString(strPhone).Substring(6, 4)); } catch { return "failed"; } } }

T-SQL procedure to increment an alpha character

USE [MARIAS] GO /****** Object: StoredProcedure [dbo].[prEndorsementSuffixGet] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Sherry Lake -- Create date: 09/04/2009 -- Description: Retrieves all endorsements with suffixes and -- parses the endorsement number and suffix -- used to determine next appropriate suffix for Endorsement Transfers -- ============================================= CREATE PROCEDURE [dbo].[prEndorsementSuffixGet] @EndorsementNumber nvarchar(15) ,@EndorsementType nvarchar(10) ,@NewSuffix nvarchar(5) OUTPUT AS SET NOCOUNT ON DECLARE @ProgramName nvarchar(200) ,@DerSuffix nvarchar(5) ,@MaxSuffix nvarchar(5) ,@asciiValue int ,@DerEndNum nvarchar(15) ,@SuffixExists int SET @ProgramName = 'prEndorsementSuffixGet' SET @SuffixExists = ISNUMERIC(substring(@EndorsementNumber, LEN(@EndorsementNumber)-0, LEN(@EndorsementNumber))) BEGIN --if there is already a suffix on the endorsement number IF @SuffixExists = 0 BEGIN set @DerSuffix = substring(@EndorsementNumber, LEN(@EndorsementNumber)-0, LEN(@EndorsementNumber)) set @DerEndNum = substring(@EndorsementNumber, LEN(@EndorsementNumber)-LEN(@EndorsementNumber), LEN(@EndorsementNumber)) END --if not already an endorsement number ELSE IF NOT @SuffixExists = 0 BEGIN set @DerEndNum = @EndorsementNumber set @DerSuffix = NULL END --get the max suffix already used for this endorsement number in the database select @MaxSuffix = MAX(v.DerSuffix) from ( select distinct e.FK_EndorsementType, e.EndorsementNumber, substring(EndorsementNumber, LEN(EndorsementNumber)-0, LEN (EndorsementNumber)) as DerSuffix, substring(EndorsementNumber, LEN(EndorsementNumber)-LEN(EndorsementNumber), LEN(EndorsementNumber)) as DerEndNum from dbo.Endorsement e where EndorsementNumber is not null and ISNUMERIC(substring(EndorsementNumber, LEN(EndorsementNumber)-0, LEN(EndorsementNumber))) = 0 ) v where v.DerEndNum = @DerEndNum and v.FK_EndorsementType = @EndorsementType --if there are no suffixes used on this endorsement number in the DB, assign suffix A if @MaxSuffix is not null BEGIN set @asciiValue = ASCII(@MaxSuffix) +1 --increment the suffix after converting to ASCII number set @NewSuffix = CHAR(@asciiValue) --cast incremented ASCII number back to char suffix END else BEGIN set @NewSuffix = 'A' END END GO

Populating nested gridviews

Sometimes it’s useful to nest gridviews within another gridview, especially for traditional master/detail output.  Unfortunately the .NET code behind pages do not play well with nested objects (they are not always accessible to refer to in the code), so we have to write code to get around this.

We need, for each outer grid row, to find the inner grid and populate it, based on the data in the outer grid.  Of course there are two components, the aspx source (using grid templates), and the code behind.  Some important things to notice: gvReport is the outer grid, gvReport_RowDataBound is the method that populates the inner grid, and gvLicenses is the nested grid.

ASPx source:

<asp:GridView ID="gvReport"
HeaderStyle-CssClass="Grid_HeaderRowStyle"
runat="server"  AutoGenerateColumns="False"        
onrowdatabound="gvReport_RowDataBound"
RowStyle-CssClass="Grid_RowStyle"
AlternatingRowStyle-CssClass="Grid_AlternateRowStyle" AllowSorting="True"
Width="100%"  RowStyle-BackColor="#CCCCCC">
    <Columns>                  
      <asp:TemplateField HeaderText="">
            <ItemTemplate>
            <b>Authority: <%# Eval("citation") %><%# Eval("subchapter") %> - <%# Eval("title") %></b> <br />                   
            <b>Exemption: <%# Eval("shortdescription") %></b>: <%# Eval("longdescription") %>
      <%—nested  grid -->                  
            <asp:GridView ID="gvLicenses"                    
            runat="server" DataKeyNames="licensepermitid" AutoGenerateColumns="False"
                AllowSorting="True"                        
                GridLines="None" RowStyle-CssClass="Grid_RowStyle" Width="100%"  RowStyle-BackColor="White">
                <Columns>      
                   <asp:TemplateField ItemStyle-Width="3%" HeaderStyle-BackColor="White" HeaderText="" ShowHeader="False">
                        <ItemTemplate>&nbsp;</ItemTemplate>
                   </asp:TemplateField>                 
                  <asp:TemplateField HeaderText="Licenses For Authority/Exemption" HeaderStyle-HorizontalAlign="Left" HeaderStyle-BackColor="White">
                        <ItemTemplate>
                            <%# Eval("name") %>                   
                        </ItemTemplate>
                   </asp:TemplateField>                                     
                </Columns>                               
              </asp:GridView>
                    </ItemTemplate>
   </asp:TemplateField>   

   <%-- hide these but include them so they are available in the code-->
        <asp:TemplateField Visible="false">
            <ItemTemplate>
                <asp:TextBox  id="subchapter"  Visible="false" Text='<%# Bind("subchapter")%>' runat="server"  />                      
                <asp:TextBox  id="authorityID"  Visible="false" Text='<%# Bind("authorityID")%>' runat="server"  />          
                <asp:TextBox  id="exemptionid"  Visible="false" Text='<%# Bind("exemptionid")%>' runat="server"  />          
           </ItemTemplate>
        </asp:TemplateField>
    </Columns>    
    </asp:GridView>

 

Code Behind:

protected void gvReport_RowDataBound(object sender, GridViewRowEventArgs e)
{       
    //e.Row is the current row

    if (e.Row.RowType == DataControlRowType.DataRow)
    {  //find the nested objects
        TextBox aTb = (TextBox)e.Row.FindControl("authorityid");
        TextBox eTb = (TextBox)e.Row.FindControl("exemptionid");
        TextBox scTb = (TextBox)e.Row.FindControl("subchapter");

        //nested grid
        GridView gv = (GridView)e.Row.FindControl("gvLicenses");   
        //populate with data from the outer/master grid
        SqlDataReader dr = ReportUtility.GetExemptionAuthoritySubchapterLicenses(Convert.ToInt32(eTb.Text), Convert.ToInt32(aTb.Text), scTb.Text, lpID, ""); 
        gv.DataSource = dr;
        gv.DataBind();
        dr.Close();   
         }

}

A bit of light reading...

Terradata has white papers at http://www.teradata.com/t/white-papers/ as well as podcasts, demos and videos (accessible from the left hand menu).

And so it begins...

Bring it on Marias!