Connect to PostgreSQL using VB.NET, AJAX and NPGSQL
After some digging I was able to find a solution to this problem. Here is my solution to query from a PostGresql database using the NPGSQL data connector, ASP.NET (VB.NET), Javascript, and AJAX. In my application the AJAX function called "ajaxyThing" receives a value from a custom control in an OpenLayers map but you could pass a value from anything into the function.
Here is the code from the Ajax function in my javascript file:
function ajaxyThing (selectedFT) {
var xmlhttp; //CREATE THE VARIABLE TO HOLD THE XMLHTTPRequest OBJEcT
if (window.XMLHttpRequest)
{// code for IE7+, Firefox, Chrome, Opera, Safari THESE BROWSERS SUPPORT THE XMLHTTPRequest OBJECT
xmlhttp=new XMLHttpRequest(); //CREATE THE XMLHTTPRequest OBJECT
}
else
{// code for IE6, IE5 THESE BROWSERS DO NOT SUPPORT THE XMLHTTPRequest OBJECT AND NEED AND ACTIVEXOBJECT
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP"); //CREATE THE ActiveXObject
}
//When using Async = true specify a function to execute when the reponse is ready in the onradystatechange event
xmlhttp.onreadystatechange = function () {
if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
document.getElementById("divAttributes").innerHTML = xmlhttp.responseText;
}
}
//TO SEND A REQUEST TO A SERVER, WE USE THE open() AND send() METHODS OF THE XMLHttpRequest object
xmlhttp.open("GET", "Handler.ashx?q="+ selectedFT, true);
xmlhttp.send();
}
Then in Microsoft Visual Web Developer Express 2010 I created a Web Handler (.ashx) file and passed the variable from my AJAX function into it. I pass the variable "selectedFT" into the Visual Studio Web Handler. Here is the code from the web handler:
<%@ WebHandler Language="VB" Class="Handler" %>
Imports System
Imports System.Web
Imports Npgsql
Imports System.Data
Public Class Handler : Implements IHttpHandler
Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
context.Response.Expires = -1
Dim q = context.Request.QueryString("q")
Dim pgConnection As NpgsqlConnection = New NpgsqlConnection
Dim pgCommand As NpgsqlCommand = New NpgsqlCommand
Dim pgConnectionString As String
Dim sda As NpgsqlDataAdapter
Dim ds As DataSet
ds = New DataSet
pgConnectionString = "Server=localhost;Port=5432;Userid=myPostGresUserId;Database=myDatabaseName;password=myPassword;Protocol=3;SSL=false;Pooling=true;MinPoolSize=1;MaxPoolSize=20;Encoding=UNICODE;Timeout=15;SslMode=Disable"
pgConnection.ConnectionString = pgConnectionString
pgConnection.Open()
pgCommand.Connection = pgConnection
pgCommand.CommandType = CommandType.Text
pgCommand.CommandText = "SELECT * FROM ""myTable"" WHERE ""myValue"" = '" & q & "';"
sda = New NpgsqlDataAdapter(pgCommand)
sda.Fill(ds)
If pgConnection.FullState = ConnectionState.Open Then
context.Response.Write("<table>")
Dim dr As DataRow
Dim dt As DataTable
dt = ds.Tables(0)
For Each dr In dt.Rows
context.Response.Write("<tr><td><b>Column #1 Name:</b></td><td>" & dr.Item(0) & "</td></tr>")
context.Response.Write("<tr><td><b>Column #2 Name:</b></td><td>" & dr.Item(1) & "</td></tr>")
context.Response.Write("<tr><td><b>Column #3 Name:</b></td><td>" & dr.Item(2) & "</td></tr>")
Next
ds.Dispose()
context.Response.Write("</table>")
Else
context.Response.Write("pgConnection did not open successfully.")
End If
pgConnection.Close()
End Sub
Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
Get
Return False
End Get
End Property
End Class
I won't claim that this is the best solution to the problem - but it works. I would be open to any suggestions you may have to make this more elegant. Thanks!
Todd Krueger
Updated on June 04, 2022Comments
-
Todd Krueger almost 2 years
I am trying to write a script in VB.NET and/or .asp that will connect to my PostgreSQL database using the NPGSQL data provider.
This function uses AJAX to get a value (selectedFT) and send that value to the helloWorld.asp script. This part works fine.
function ajaxyThing (selectedFT) { var xmlhttp; //CREATE THE VARIABLE TO HOLD THE XMLHTTPRequest OBJEcT if (window.XMLHttpRequest) {// code for IE7+, Firefox, Chrome, Opera, Safari THESE BROWSERS SUPPORT THE XMLHTTPRequest OBJECT xmlhttp=new XMLHttpRequest(); //CREATE THE XMLHTTPRequest OBJECT } else {// code for IE6, IE5 THESE BROWSERS DO NOT SUPPORT THE XMLHTTPRequest OBJECT AND NEED AND ACTIVEXOBJECT xmlhttp=new ActiveXObject("Microsoft.XMLHTTP"); //CREATE THE ActiveXObject } //When using Async = true specify a function to execute when the reponse is ready in the onreadystatechange event xmlhttp.onreadystatechange = function () { if (xmlhttp.readyState == 4 && xmlhttp.status == 200) { document.getElementById("responseText").innerHTML = xmlhttp.responseText; } } //TO SEND A REQUEST TO A SERVER, WE USE THE open() AND send() METHODS OF THE XMLHttpRequest object xmlhttp.open("GET", "helloWorld.asp?q="+ selectedFT, true); xmlhttp.send(); }
Next I need the ASP script (or an .aspx, or an .ashx. I don't know what is the best way) to use the selectedFT value to query my PostgreSQL database. This is where I run into trouble. I know I need to do the following things but I don't know how to put it all together:
1) Get the value from the AJAX http request. For example I should probably use:
response.expires=-1 q= request.querystring("q")
2) Then I need to establish the connection to the PostgreSQL database. I use the following code (taken from this website http://www.techrepublic.com/article/easily-integrate-postgresql-with-net/6102826) to run in the PageLoad of a standard .aspx page and it works fine to bind the data to a gridview. But what I really need is not to connect the result set to a gridview but have my connection script stand alone so I can use the output to do many different things. I am not sure how to implement this code in an .asp script (or .aspx, or .ashx) and make it run when I call the .asp script (or .aspx or .ashx) from my AJAX function.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)Handles MyBase.Load Dim pgConnection As NpgsqlConnection = New NpgsqlConnection Dim pgCommand As NpgsqlCommand = New NpgsqlCommand Dim pgConnectionString As String Dim sda As NpgsqlDataAdapter Dim ds As DataSet ds = New DataSet pgConnectionString = "Server=localhost;Port=5432;Userid=myUserId;Database=myDatabaseName;password=myPassword;Protocol=3;SSL=false;Pooling=true;MinPoolSize=1;MaxPoolSize=20;Encoding=UNICODE;Timeout=15;SslMode=Disable" pgConnection.ConnectionString = pgConnectionString pgConnection.Open() pgCommand.Connection = pgConnection pgCommand.CommandType = CommandType.Text pgCommand.CommandText = "SELECT * FROM ""myTable"";" If pgConnection.FullState = ConnectionState.Open Then MsgBox("Connection To PostGres is open", MsgBoxStyle.MsgBoxSetForeground) End If sda = New NpgsqlDataAdapter(pgCommand) sda.Fill(ds) GridView1.DataSource = ds GridView1.DataBind() pgConnection.Close() End sub
Any suggestions would be greatly appreciated. Thanks!