Linq 2 entities and XML

When using Linq 2 Sql with Linq to Xml in VB you could write code like this to generate xml

Dim db As New NorthwindDataContext
 

Dim xmlLinq2Sql = <root><%= From emp In db.Employees _
                            Select <Employee id=<%= emp.EmployeeID %>><%= emp.FirstName & " " & emp.LastName %>
                                   </Employee> %>
                  </root>

Which will generate xml  like this

<root>
  <Employee id="1">Nancy Davolio</Employee>
  <Employee id="2">Andrew Fuller</Employee>
  <Employee id="3">Janet Leverling</Employee>
  <Employee id="4">Margaret Peacock</Employee>
  <Employee id="5">Steven Buchanan</Employee>
  <Employee id="6">Michael Suyama</Employee>
  <Employee id="7">Robert King</Employee>
  <Employee id="8">Laura Callahan</Employee>
  <Employee id="9">Anne Dodsworth</Employee>
</root>

The entity framework requires a more explicit format for the query

Dim entDB As New NorthwindModel.NorthwindEntities

Dim xmlLinq2Entities = <root><%= From ent In entDB.Employees.AsEnumerable _
                                 Select <Employee id=<%= ent.EmployeeID %>><%= ent.FirstName & " " & ent.LastName %>
                                        </Employee> %>
                       </root>

Hope this helps

Validating Data entered in a DataRepeater control

In this example I will show how to validate the data entered into a datarepeater control. For this example I am added the northwind SQL compact edition database to the project and created a typed dataset for the products table.  So from the toolbox drop a datarepeater on the form.  Inside the datarepeater drag the ProductName, UnitPrice, and Units in stock fields. Your form should look something like

Now in the drawitem event for the datarepeater we can add a handler to validating event.

Private Sub DataRepeater1_DrawItem(ByVal sender As Object, ByVal e As Microsoft.VisualBasic.PowerPacks.DataRepeaterItemEventArgs) Handles DataRepeater1.DrawItem
    Dim currItem As DataRowView = DirectCast(ProductsBindingSource.Item(e.DataRepeaterItem.ItemIndex), DataRowView)
    Dim txt As TextBox = DirectCast(e.DataRepeaterItem.Controls("Unit_PriceTextBox"), TextBox)
    AddHandler txt.Validating, AddressOf TextBox_Validating
End Sub

Private Sub TextBox_Validating(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs)
    Dim dec As Decimal
    If Not Decimal.TryParse(DirectCast(sender, TextBox).Text, dec) Then
        MessageBox.Show("Please enter a valid number")
        e.Cancel = True
    End If
End Sub

The complete code

Public Class Form1

    Private Sub ProductsBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ProductsBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.ProductsBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.NorthwindDataSet)

    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'NorthwindDataSet.Products' table. You can move, or remove it, as needed.
        Me.ProductsTableAdapter.Fill(Me.NorthwindDataSet.Products)

    End Sub

    Private Sub DataRepeater1_DrawItem(ByVal sender As Object, ByVal e As Microsoft.VisualBasic.PowerPacks.DataRepeaterItemEventArgs) Handles DataRepeater1.DrawItem
        Dim currItem As DataRowView = DirectCast(ProductsBindingSource.Item(e.DataRepeaterItem.ItemIndex), DataRowView)
        Dim txt As TextBox = DirectCast(e.DataRepeaterItem.Controls("Unit_PriceTextBox"), TextBox)
        AddHandler txt.Validating, AddressOf TextBox_Validating
    End Sub

    Private Sub TextBox_Validating(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs)
        Dim dec As Decimal
        If Not Decimal.TryParse(DirectCast(sender, TextBox).Text, dec) Then
            MessageBox.Show("Please enter a valid number")
            e.Cancel = True
        End If
    End Sub
End Class

Regular Expression Help

I got an regualr expression question today from one of my friends.  Basically she was using a regular expression  to validate a number was 4 or 6 digits long but the expression she was using ^\d{4,6}$ would validate numbers 5 digits long.  Lets look at this regular expression ^ means starts with. The \d means number and the {4,6} means 4 to 6 digits long.  The $ means ends with. The answer is to use a regular express with an or (the | means or)

Dim regNum As New Regex("^\d{4}$|^\d{6}$")

Debug.Print(regNum.IsMatch("1234").ToString)

Debug.Print(regNum.IsMatch( "12345").ToString)

Debug.Print(regNum.IsMatch("123456").ToString)

Output

True

False

True

Print to PDF

The .Net framework provides a print document class for printing.  There are times that it would be nice to redirect what you are printing to a pdf.   In this example we are going to use the Sharp Pdf lib version 1.3.1 to print to a pdf. 

The sharp pdf lib allows you to add an image to a page in a pdf.  To make it possible to print to a pdf we are going to create a new print controller class which creates a bitmap and has the print document draw the page on the bitmap.  Then it adds the bitmap as a pdf page.  Once the document is done printing it saves the pdf to disk. 

http://sharppdf.sourceforge.net/

Update this Project is now available on CodePlex

http://www.codeplex.com/Print2Pdf

Imports sharpPDF

Public Class PdfPrintController
    Inherits Printing.PrintController
    Dim pdf As pdfDocument
    Dim bm As Image

    Private _Author As String = "Unknown"

    Public Property Author() As String
        Get
            Return _Author
        End Get
        Set(ByVal value As String)
            _Author = value
        End Set
    End Property

    Private _FileName As String = "Printed.pdf"
    Public Property FileName() As String
        Get
            Return _FileName
        End Get
        Set(ByVal value As String)
            _FileName = value
        End Set
    End Property

    Private _Title As String = "Unknown"
    Public Property Title() As String
        Get
            Return _Title
        End Get
        Set(ByVal value As String)
            _Title = value
        End Set
    End Property

    Public Overrides ReadOnly Property IsPreview() As Boolean
        Get
            Return True
        End Get
    End Property

    Public Overrides Function OnStartPage(ByVal document As System.Drawing.Printing.PrintDocument, ByVal e As System.Drawing.Printing.PrintPageEventArgs) As System.Drawing.Graphics
        bm = New Bitmap(e.PageBounds.Width, e.PageBounds.Height)
        Dim g As Graphics = Graphics.FromImage(bm)
        g.Clear(Color.White)
        Return g
    End Function

    Public Overrides Sub OnStartPrint(ByVal document As System.Drawing.Printing.PrintDocument, ByVal e As System.Drawing.Printing.PrintEventArgs)
        pdf = New pdfDocument(Title, Author)
        MyBase.OnStartPrint(document, e)
    End Sub

    Public Overrides Sub OnEndPage(ByVal document As System.Drawing.Printing.PrintDocument, ByVal e As System.Drawing.Printing.PrintPageEventArgs)
        Dim p As pdfPage = pdf.addPage(e.PageBounds.Height, e.PageBounds.Width)
        p.addImage(bm, 0, 0)
        MyBase.OnEndPage(document, e)
    End Sub

    Public Overrides Sub OnEndPrint(ByVal document As System.Drawing.Printing.PrintDocument, ByVal e As System.Drawing.Printing.PrintEventArgs)
        pdf.createPDF(FileName)
        MyBase.OnEndPrint(document, e)
    End Sub

End Class

Here is a sample which creates a pdf of the Northwind product list.

Imports System.Data.SqlClient

Public Class Form1
    Public WithEvents p As New Printing.PrintDocument
    Dim iRecord As Integer = 0
    Dim fntPrice As New Font("Arial", 12)
    Dim ds As New DataSet

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim strConn As String
        Dim conn As SqlConnection
        Dim da As SqlDataAdapter

        strConn = "Server = .\SQLEXPRESS;"
        strConn &= "Database = Northwind; Integrated Security = SSPI;"
        conn = New SqlConnection(strConn)
        da = New SqlDataAdapter("Select ProductName, UnitPrice From Products", conn)
        da.Fill(ds, "Products")

        DataGridView1.DataSource = ds.Tables("Products")
    End Sub

    Private Sub p_BeginPrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs) Handles p.BeginPrint
        iRecord = 0
    End Sub

    Private Sub p_PrintPage(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintPageEventArgs) Handles p.PrintPage
        Dim g As Graphics = e.Graphics
        Dim iPageHeight As Integer = e.PageBounds.Height
        Dim iPageWidth As Integer = e.PageBounds.Width
        Dim iFntHeight As Integer = CInt(g.MeasureString("Test", fntPrice).Height)
        Dim iLinesPerPage As Integer = iPageHeight \ iFntHeight - 15
        Dim yPos As Integer = 0
        Dim iTop As Integer
        Dim iMax As Integer = ds.Tables("Products").Rows.Count
        Dim strDescription As String
        Dim x As Integer
        Dim xPos As Integer
        Dim strPrice As String
        Dim fntTitle As Font = New Font("Microsoft Sans Serf", 14)
        Dim iCount As Integer = ds.Tables("Products").Rows.Count
        Dim strDate As String = Trim(Now.ToLongDateString)
        Dim sf As New StringFormat
        sf.Alignment = StringAlignment.Far

        xPos = CInt(iPageWidth - g.MeasureString("Price List", fntTitle).Width) \ 2
        g.DrawString("Price List", fntTitle, Brushes.Black, xPos, 10)
        yPos = 10 + CInt(g.MeasureString("Price List", fntTitle).Height)

        xPos = CInt(iPageWidth - g.MeasureString(strDate, fntPrice).Width) \ 2
        g.DrawString(strDate, fntPrice, Brushes.Black, xPos, yPos)
        yPos += 2 * iFntHeight
        g.DrawString("Product", fntPrice, Brushes.Black, 50, yPos)

        g.DrawString("Price", fntPrice, Brushes.Black, _
                New Rectangle(430, yPos, 100, 2 * iFntHeight), sf)

        yPos += iFntHeight
        g.DrawLine(Pens.Black, 0, yPos, iPageWidth, yPos)

        e.HasMorePages = True
        iTop = yPos

        For x = 0 To iLinesPerPage
            If iRecord < imax Then
                With ds.Tables("Products").Rows(iRecord)
                    strDescription = .Item("ProductName").ToString
                    strPrice = Convert.ToDecimal(.Item("UnitPrice")).ToString("c")
                End With
                Dim rName As New Rectangle(5, yPos, 400, iFntHeight)
                Dim rPrice As New Rectangle(430, yPos, 100, iFntHeight)

                g.DrawString(strDescription, fntPrice, Brushes.Black, rName)
                g.DrawString(strPrice, fntPrice, Brushes.Black, rPrice, sf)
            Else
                e.HasMorePages = False
            End If
            yPos += iFntHeight
            iRecord += 1
        Next
        fntTitle.Dispose()
        If e.HasMorePages = False Then iRecord = 0
    End Sub

    Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrint.Click
        Dim pc As New PdfPrintController
        pc.Title = "Test Pdf"
        pc.Author = "Ken Tucker"
        pc.FileName = "Test.pdf"
        p.PrintController = pc
        p.Print()
    End Sub
End Class

Using the ExcelPackage class on a web page

Dr. John Tunicliffe wrote a nice class for creating excel 2007 spreadsheets and was nice enough to post his work on the codeplex web site.

http://www.codeplex.com/ExcelPackage

I was looking to use the ExcelPackage to create a spreadsheet on a website with out having to save the spreadsheet on the server first.  Well to do this we are going to have to modify the ExcelPackage class to have an constructor which accepts a stream.  After you download the ExcelPackageclass from codeplex add the following code to ExcelPackage.cs contructors region.

        #region ExcelPackage Constructors
        /// <summary>
        /// Creates a new instance of the ExcelPackage class based on a stream.
        /// </summary>
        /// <param name="stream">Creates a new ExcelPackage from a stream</param>
        public ExcelPackage(Stream stream)
        {
            _package = Package.Open(stream, FileMode.OpenOrCreate);
            // save a temporary part to create the default application/xml content type
            Uri uriDefaultContentType = new Uri("/default.xml", UriKind.Relative);
            PackagePart partTemp = _package.CreatePart(uriDefaultContentType, "application/xml");
            XmlDocument workbook = Workbook.WorkbookXml; // this will create the workbook xml in the package
            // create the relationship to the main part
            _package.CreateRelationship(Workbook.WorkbookUri, TargetMode.Internal, schemaRelationships + "/officeDocument");
            // remove the temporary part that created the default xml content type
            _package.DeletePart(uriDefaultContentType);
        }
Once you compile the class we can add it as a reference to a website which targets the .Net framework 3.0 or 3.5.
So to write a spreadsheet to webpage we need to first create a MemoryStream to save the spreadsheet to.  I use a MemoryStream because the Response.OutputStream will give you an error about the FileMode or FileAccess type not being valid for this stream. Once we write the spreadsheet to the MemoryStream  set the Response's ContextType. Then we can write the MemoryStream to the webpage.
Imports OfficeOpenXml
Imports System.IO
Partial Public Class _Default
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim ms As New MemoryStream
        Using pack As New ExcelPackage(ms)
            Dim ws As ExcelWorksheet = pack.Workbook.Worksheets.Add("Sheet1")
            ws.Cell(1, 1).Value = "Product Name"
            ws.Column(1).Width = 30
            ws.Cell(1, 2).Value = "Price"
            ws.Column(2).Width = 10
            For r As Integer = 0 To 9
                ws.Cell(r + 2, 1).Value = "Product " & r.ToString
                ws.Cell(r + 2, 2).Value = r.ToString()
            Next
            pack.Workbook.Properties.Author = "Ken Tucker"
            pack.Workbook.Properties.Title = "Create spreadsheet from web"
            pack.Save()
        End Using
        Response.ContentType = "application/vnd.openxmlformats"
        Response.AddHeader("Content-Disposition", "attachment; filename= Data.xlsx;")
        Response.OutputStream.Write(ms.GetBuffer, 0, CInt(ms.Length))
        Response.Flush()
        Response.Close()
        Response.End()
    End Sub
End Class
Hope this helps