Hi, I'm doing a platform in asp.net with vb language and I need to export data from a sql table giving it a parameter in the application I'm doing, the only one way I could export this data was using a handler (generic driver), but only doing a normal select, now I need to do it but giving a parameter from the web application.
I leave the code of HANDLER
%@ WebHandler Language="VB" Class="Handler" Debug="true" %>
Imports System
Imports System.Web
Imports System.Data
Imports System.Data.SqlClient
Public Class Handler : Implements IHttpHandler
Public ReadOnly Property IsReusable As Boolean Implements IHttpHandler.IsReusable
Get
Throw New NotImplementedException()
End Get
End Property
Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
Dim connectionString As String = "Data Source=(Local); Initial Catalog= SPC; Integrated Security= true"
Using cnn As New SqlConnection(connectionString)
Dim selectCommand As String = "select tbl_asignacion_criterios.cod_asigcriterio,tbl_asignacion_criterios.cod_edicion,tbl_edicion.nombre_edicion ,tbl_criterios.nombre_criterio,tbl_detalle_asignacion.cod_criterio from tbl_asignacion_criterios inner join tbl_edicion on tbl_asignacion_criterios.cod_edicion=tbl_edicion.cod_edicion inner join tbl_detalle_asignacion on tbl_asignacion_criterios.cod_asigcriterio=tbl_detalle_asignacion.cod_criterio inner join tbl_criterios on tbl_detalle_asignacion.cod_criterio=tbl_criterios.cod_criterio "
Dim da As New SqlDataAdapter(selectCommand, cnn)
Dim ds As New DataSet
da.Fill(ds)
ExportDataTableToExcel(ds.Tables.Item(0))
End Using
End Sub
Private Sub DoSomething()
End Sub
Public Overloads Shared Sub ExportDataTableToExcel(ByVal table As DataTable)
ExportDataTableToExcel(table, String.Empty, Nothing)
End Sub
Public Overloads Shared Sub ExportDataTableToExcel(ByVal table As DataTable, ByVal captions As Dictionary(Of String, String))
ExportDataTableToExcel(table, String.Empty, captions)
End Sub
Public Overloads Shared Sub ExportDataTableToExcel(ByVal table As DataTable, ByVal name As String)
ExportDataTableToExcel(table, name, Nothing)
End Sub
Public Overloads Shared Sub ExportDataTableToExcel(ByVal table As DataTable, ByVal name As String, ByVal captions As Dictionary(Of String, String))
Dim content As New Text.StringBuilder()
Dim columnName As String = String.Empty
For Each column As DataColumn In table.Columns
If Not captions Is Nothing Then
If Not captions.TryGetValue(column.ColumnName, columnName) Then
columnName = column.ColumnName
End If
Else
columnName = column.ColumnName
End If
content.Append(columnName & ";")
Next
content.Append(Environment.NewLine)
Dim value As String
For Each row As DataRow In table.Rows
For i As Integer = 0 To table.Columns.Count - 1
value = String.Empty
If Not row.IsNull(i) Then
value = row(i).ToString().Replace(";", String.Empty)
End If
content.Append(value & ";")
Next
content.Append(Environment.NewLine)
Next
content.Length = content.Length - 1
Dim context As HttpContext = HttpContext.Current
With context.Response
.Clear()
.ContentType = "text/csv"
If String.IsNullOrEmpty(name) Then
name = DateTime.Now.ToString("ddMMyyyyHHmmss")
End If
.AppendHeader("Content-Disposition", String.Format("attachment; filename={0}.csv", name))
.Charset = Encoding.Unicode.WebName
.ContentEncoding = Encoding.Unicode
.Write(content.ToString())
.End()
End With
End Sub