Creating web service in VB.NET that outputs to JSON raw data

Here is a quick tutorial on creating a web service in VB.NET that output the data in JSON.
Imports System
Imports System.Web
Imports System.Collections
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Script.Serialization
Imports System.Web.Script.Services
Imports System.ComponentModel
 
<System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class config
    Inherits System.Web.Services.WebService
 
    <WebMethod()> _
    <ScriptMethod(ResponseFormat:=ResponseFormat.Json)> _
    Public Function getConfigurationValueByKey(ByVal strKeyName As String) As String
        Dim sqlConnection As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("ConnStr").ConnectionString)
        sqlConnection.Open()
        Dim sqlCommand As SqlCommand = New SqlCommand("SELECT * FROM table1 WHERE column1 ='" + strKeyName + "';", sqlConnection)
        Dim sqlDataset As DataSet = New DataSet()
        Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdapter(sqlCommand)
 
        sqlDataAdapter.Fill(sqlDataset)
        sqlConnection.Close()
 
        Dim MArray()() As String = New String(sqlDataset.Tables(0).Rows.Count)() {}
        Dim i As Integer = 0
 
        For Each rs As DataRow In sqlDataset.Tables(0).Rows
            MArray(i) = New String() {rs("KeyName").ToString(), rs("ValueName").ToString()}
            i = i + 1
        Next
 
        Dim js As JavaScriptSerializer = New JavaScriptSerializer()
        Dim sJSON As String = js.Serialize(MArray)
        Return sJSON
    End Function
 
End Class
Output will be something like this.
  <?xml version="1.0" encoding="utf-8" ?> 
  <string xmlns="http://tempuri.org/">[["key1","value1"],["key2","value2"]]</string>
FacebookTwitterGoogle+Share

Converting a grid to excel document in vb.net

A little script to convert a gridview to an excel document. This always came handy when someone needed to create a report right out of gridview ... You can call the function like this ...
ExportGridToExcel(grd, "report.xls")
 
---Copy the code below and paste it in your script
 
Public Sub ExportGridToExcel(ByRef grdGridView As GridView, ByVal fileName As String)
 
Response.Clear()
 
Response.AddHeader("content-disposition", String.Format("attachment;filename={0}.xls", fileName))
 
Response.Charset = ""
 
Response.ContentType = "application/vnd.xls"
 
Dim strWriter As New StringWriter()
 
Dim HtmlWriter As New HtmlTextWriter(strWriter)
 
grdGridView.RenderControl(HtmlWriter)
 
Response.Write(strWriter.ToString)
 
Response.End()
 
End Sub
 
Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
 
'You will need this part else you will get error like "runat=server needs to be before ....."
 
End Sub