.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!