使用 VB.NET、AJAX 和 NPGSQL 连接到 PostgreSQL

Connect to PostgreSQL using VB.NET, AJAX and NPGSQL

提问by Todd Krueger

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.

我正在尝试在 VB.NET 和/或 .asp 中编写一个脚本，该脚本将使用 NPGSQL 数据提供程序连接到我的 PostgreSQL 数据库。

This function uses AJAX to get a value (selectedFT) and send that value to the helloWorld.asp script. This part works fine.

此函数使用 AJAX 获取值 (selectedFT) 并将该值发送到 helloWorld.asp 脚本。这部分工作正常。

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:

接下来我需要 ASP 脚本（或者一个 .aspx，或者一个 .ashx。我不知道什么是最好的方法）来使用 selectedFT 值来查询我的 PostgreSQL 数据库。这是我遇到麻烦的地方。我知道我需要做以下事情，但我不知道如何把它们放在一起：

1) Get the value from the AJAX http request. For example I should probably use:

1) 从 AJAX http 请求中获取值。例如，我可能应该使用：

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.

2）然后我需要建立到PostgreSQL数据库的连接。我使用以下代码（取自本网站http://www.techrepublic.com/article/easily-integrate-postgresql-with-net/6102826）在标准 .aspx 页面的 PageLoad 中运行，它可以正常工作将数据绑定到 gridview。但是我真正需要的不是将结果集连接到 gridview 而是让我的连接脚本独立，这样我就可以使用输出来做许多不同的事情。我不确定如何在 .asp 脚本（或 .aspx 或 .ashx）中实现此代码，并在我从我的 AJAX 函数调用 .asp 脚本（或 .aspx 或 .ashx）时让它运行。

 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!

任何建议将不胜感激。谢谢！

采纳答案by Todd Krueger

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.

经过一番挖掘，我找到了解决这个问题的方法。这是我使用 NPGSQL 数据连接器、ASP.NET (VB.NET)、Javascript 和 AJAX 从 PostGresql 数据库进行查询的解决方案。在我的应用程序中，名为“ajaxyThing”的 AJAX 函数从 OpenLayers 映射中的自定义控件接收值，但您可以将任何值传递给函数。

Here is the code from the Ajax function in my javascript file:

这是我的 javascript 文件中 Ajax 函数的代码：

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:

然后在 Microsoft Visual Web Developer Express 2010 中，我创建了一个 Web 处理程序 (.ashx) 文件并将变量从我的 AJAX 函数传递到其中。我将变量“selectedFT”传递给 Visual Studio Web 处理程序。这是来自 Web 处理程序的代码：

<%@ 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!

我不会声称这是问题的最佳解决方案 - 但它确实有效。我愿意接受您可能需要使这更优雅的任何建议。谢谢！