Tuesday, March 27, 2012

Exporting an ASP.NET gridview to PDF and CSV formats

You will need to download and add a reference to the iTextSharp dll in your ASP.NET application.
Here query1Gridview is the Gridview that I want to export to PDF. The assumption is that the gridview is already provided the datasource before you're ready to export.

Response.ContentType = "application/pdf"

        Response.AddHeader("content-disposition", _
        Dim m As New MemoryStream
        Dim sw As New StringWriter()
        Dim hw As New HtmlTextWriter(sw)
        query1GridView.AllowPaging = False
        Dim dtNow As DateTime
        dtNow = Now()
        Dim todaysDate = dtNow.ToLongDateString
        Dim sr As New StringReader(sw.ToString())

        Dim header As String
        header = "YOUR HEADER" + Environment.NewLine
        Dim c As Chunk = New Chunk(header & vbLf, FontFactory.GetFont("Verdana", 15))
        Dim p As New Paragraph()
        p.Alignment = Element.ALIGN_CENTER

        Dim c1 As Chunk = New Chunk("ADD YOUR QUERY TEXT" + Environment.NewLine & vbLf, FontFactory.GetFont("Verdana", 15))
        Dim p1 As New Paragraph()
        p1.Alignment = Element.ALIGN_CENTER

        Dim c2 As Chunk = New Chunk(todaysDate + Environment.NewLine & vbLf, FontFactory.GetFont("Times New Roman", 12))
        Dim p2 As New Paragraph()
        p2.Alignment = Element.ALIGN_RIGHT

        Dim logo As iTextSharp.text.Image = iTextSharp.text.Image.GetInstance(Server.MapPath("~\Images\YOURLOGO.jpg"))
        logo.Alignment = Element.ALIGN_LEFT
        logo.Alignment = iTextSharp.text.Image.TEXTWRAP

        Dim pdfDoc As New Document(PageSize.A3, 10.0F, 10.0F, 10.0F, 0.0F)
            Dim htmlparser As New HTMLWorker(pdfDoc)
            PdfWriter.GetInstance(pdfDoc, Response.OutputStream)
        Catch ex As Exception
            MessageBox("Unable to create pdf")
        End Try

For exporting to CSV use the below code:


        Response.Buffer = True

        Response.AddHeader("content-disposition", "attachment;filename=YOURFILENAME.csv")
        Response.Charset = ""
        Response.ContentType = "application/text"
        query1GridView.AllowPaging = False

        Dim sb As New StringBuilder()

        For k As Integer = 0 To query1GridView.Columns.Count - 1

            sb.Append(query1GridView.Columns(k).HeaderText + ","c)

        sb.Append(vbCr & vbLf)
        For i As Integer = 0 To query1GridView.Rows.Count - 1

            For k As Integer = 0 To query1GridView.Columns.Count - 1

                'add separator

                sb.Append(query1GridView.Rows(i).Cells(k).Text + ","c)

            sb.Append(vbCr & vbLf)


Monday, March 26, 2012

Autocomplete using VB.NET and AJAX

For using the Autocomplete extender using AJAX and VB.NET you need to create a webservice (.asmx) first. Below is the code for the webservice I'm using to get the autocomplete strings. The webservice uses a SQL statement to get the results. The statement has the "like" clause which matches the first letter and gets all the strings that are supersets of the string entered.

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data.SqlClient

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
' <System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class AutoComplete
    Inherits System.Web.Services.WebService

<WebMethod()> _
   Public Function GetProducts(ByVal prefixText As String, _
                                 ByVal count As Integer) As String()

        Dim cn As New SqlConnection(ConfigurationManager.ConnectionStrings("SQLServer2008DBConnectionString").ToString())
        Dim myCommand As New SqlCommand()
        myCommand.Connection = cn
        myCommand.CommandText = "Select ac_id from dbo.tblAccountCarrier where ac_id like '" & prefixText & "%'"
        Dim Results As New ArrayList
            Using Command As New SqlCommand(myCommand.CommandText, cn)
                Using dr As SqlDataReader = myCommand.ExecuteReader()

                    Dim Counter As Integer
                    While dr.Read
                        If (Counter = count) Then Exit While
                        Counter += 1
                    End While
                End Using
                Dim ResultsArray(Results.Count - 1) As String
                ResultsArray = Results.ToArray(GetType(System.String))
                Return ResultsArray
            End Using
        Catch ex As Exception
            Throw ex
        End Try
    End Function

The second part is using the autocomplete AJAX extender in the .aspx page. You need to register the AJAXtoolkit at the top of the page using this tag:
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>

In the Autocomplete tag you need to specify the  ServicePath which is a path to the above webs service and ServiceMethod which is the name of the web method created in the class.

In the .aspx page

<asp:TextBox ID="acct_idTextBox" runat="server"></asp:TextBox>
                    <cc1:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" ServicePath="~/AutoComplete.asmx"
                       ServiceMethod ="GetProducts" MinimumPrefixLength="1" TargetControlID="acct_idTextBox">

Thursday, March 22, 2012

Importing data from Excel spreadsheet into SQL Server database using VB.NET

I was just asked to do this at my work and here's the code that did the trick for me.
This uses the new SqlBulkCopy class in ASP.NET 2.0 which lets you easily copy bulk amounts of data from one source to another.

The function that does the SQL bulk import is as follows
Protected Sub SqlBulkCopyImport(ByVal dtExcel As DataTable)
Using conn As New SqlConnection(YOURCONNECTIONSTRING)
' Open the connection. 

Using bulkCopy As New SqlBulkCopy(conn)
' Specify the destination table name. 

bulkCopy.DestinationTableName = "dbo.tblImportRG"
' System.Data.IDataReader()

' For Each dc As DataColumn In dtExcel.Columns
' Because the number of the test Excel columns is not  
' equal to the number of table columns, we need to map  
' columns. 
'Here we manually add the mappings from Excel file (First paratmeter) to SQL table (Second parameter)

bulkCopy.ColumnMappings.Add("id", "NWCarrierID")
bulkCopy.ColumnMappings.Add("acct_master_id", "acct_master_id")
'....And so on

' Next

' Write from the source to the destination. 
End Using
End Using
End sub
This function can be called from another function as follows:

Protected Function RetrieveData(ByVal strConn As String) As DataTable
Dim dtExcel As New DataTable()

Using conn As New OleDbConnection(strConn)
' Initialize an OleDbDataAdapter object.
Dim da As New OleDbDataAdapter("select * from [Sheet1$]", conn)

' Fill the DataTable with data from the Excel spreadsheet.

End Using
Return dtExcel
End Function

Protected Sub btnImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnImport.Click
If importFileUpload.HasFile Then
' Get the name of the Excel spreadsheet to upload.
Dim strFileName As String = Server.HtmlEncode(importFileUpload.FileName)

' Get the extension of the Excel spreadsheet.
_strExtension = Path.GetExtension(strFileName)

' Validate the file extension.
If _strExtension <> ".xls" AndAlso _strExtension <> ".xlsx" Then
'Tell the user to upload an excel file.
End If

' Generate the file name to save.
_strUploadFileName = "~/Library/UploadFiles/" & DateTime.Now.ToString("yyyyMMddHHmmss") & _strExtension

' Save the Excel spreadsheet on server.

' Generate the connection string for Excel file.
Dim strExcelConn As String = ""

' There is no column name In a Excel spreadsheet.
' So we specify "HDR=YES" in the connection string to use
' the values in the first row as column names.
If _strExtension = ".xls" Then
' Excel 97-2003
strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(_strUploadFileName) & ";Extended Properties='Excel 8.0;HDR=YES;'"
strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Server.MapPath(_strUploadFileName) & ";Extended Properties='Excel 12.0 Xml;HDR=YES;'"
End If

Dim dtExcel As DataTable = RetrieveData(strExcelConn)

' Get the row counts before importing.


' Get the row counts after importing.

' Display the number of imported rows.
lblMessages.Text = Convert.ToString(GetRowCounts()) & " rows were imported into tblImportRG table"
btnSaveTariff.Visible = True

End If

End If

End Sub
Here GetRowCounts() is a simple function that returns the number of rows in the table.

Wednesday, March 21, 2012

Internet service in Des Moines

In today's world Internet has become a basic necessity. A recent survey in the US showed that teenagers would rather prefer Internet over food!

After having moved into my new apartment, I was looking for Internet service providers in the Des Moines area. And as it turns out there're just two of them who provide high speed broadband internet services - Qwest (Centurylink) and Mediacom. After comparing the two I decided to go with Centurylink as I was told they have a better customer service.

So far it has been a good experience with them - no interruption in internet and customer service that is willing to help out with all kinds of issues whether technical or billing. Currently they're also offering a promotional discount where you can get any speed (1.5 Mbps - 40 Mbps) for a flat $25. This discount is valid only for the first six months though, after which regular rates apply. Also, if you're a Verizon wireless customer like me, you'll be getting an additional $10 discount on the bill. A great value deal!

Monday, March 19, 2012

Kahaani - 4.5 out of 5

Kahaani is the latest Hindi move that I saw after "The dirty picture". Coincidentally, both movies being female- centric with Vidya Balan playing the lead protagonist. It is definitely not your regular Bollywood masala film but more on the lines of a Hollywood psychological thriller.

The film begins with the pretext of a pregnant Vidya Bagchi (Vidya Balan) coming to Kolkata from London in search of her missing husband. The film then follows many twists during it's 114 minutes and turns on it's head in the last ten minutes of the climax.

The things to watch out of are: Vidya Balan's tremendous acting, the portrayal of Kolkata and weather you're able to unlock the mystery before the climax.

Life in Des Moines, IA

I recently moved to Des Moines, IA for a job after having stayed in New Jersey for more than a year. While it's only been a little more than a month since I've moved, for the past few days I'm really missing the Indian restaurants of NJ (The Dosa Grill on Route 27 in particular! My God, their Sambhar is the best I've ever had, and that includes those that I've tasted in Mumbai, Kerala, Karnataka and Tamil Nadu).

While moving out of a certain place after having resided there for a fair amount of time does have it's own inertia associated with it, it does come with the possibility of exploring new places. Having said that, Des Moines, although the capital of the US state of Iowa, is only about 80 Sq miles including suburbs. To put that bluntly, if you were to stand in the middle of the city and walk about 10 miles in any direction you'd end up in the middle of nowhere! The city however, is surrounded by four other major US cities - Chicago to the East, Kansas City to the South, Omaha to the West and Minneapolis to the North. I'm definitely planning to visit a couple of these in the near future.

In the process of settling into the new town, I've rented my own studio apartment (Which costs about half of what you would pay if you were in New Jersey). I've also met a few Indian origin people in the city which is always good and am cooking my own Indian food.

PS - If you're ever in Des Moines, IA be sure to visit the Iowa State Capitol, the Des Moines Downtown and the Botanical center here.