Blog Post by Murat Yaşar
Posted on Sunday, October 10, 2010 6:54:57 PM and it has been read 12468 times since then.
DataBinding GridView In Asp.NET Application By Using PostgreSQL as DataSource
I wrote this sample application by using following system:
Operating System: Windows 7 Ultimate 64 Bit
Visual Studio 2010, .NET Framework 4, PostgreSQL 9.0
To be able to connect PostgreSQL from my .NET application I needed to install Npgsql.
Npgsql is a .Net data provider for Postgresql. It allows any program developed for .Net framework to access database server. It is implemented in 100% C# code. Works with Postgresql 7.x and 8.x. You can download the necessary files from the following link.
http://pgfoundry.org/frs/?group_id=1000140
After that I just referenced the dll for my Asp.NET application.
After referencing the dll, I put a GridView control onto my Asp.NET form.
Then I wrote the following code on code behind page.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Npgsql;
using NpgsqlTypes;
using System.Data;
namespace SamplePostgreSQL1
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataTable table = GetData();
GridView1.DataSource = GetData();
GridView1.DataBind();
}
public DataTable GetData()
{
string conString = "Server=127.0.0.1;Port=5433; Userid=postgres;Password=mypassword; Protocol=3;SSL=false;Pooling=true;MinPoolSize=1; MaxPoolSize=20;Encoding=UNICODE; Timeout=15;SslMode=Disable;Database=Northwind";
DataTable ourDataTable = null;
using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(conString))
{
try
{
ourDataTable = new DataTable();
Npgsql.NpgsqlDataAdapter ourAdapter = new NpgsqlDataAdapter(@"customerList1", connection);
ourAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
ourAdapter.Fill(ourDataTable);
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
}
return ourDataTable;
}
}
}
In the above code block I used a stored procedure (function in terms of PostgreSQL) to populate a table for giving as data source to GridView control.
The function has been written as follows:
CREATE OR REPLACE FUNCTION customerlist1() RETURNS SETOF "Customers" AS
$BODY$
SELECT * FROM "Customers";
$BODY$
LANGUAGE sql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION customerlist1() OWNER TO postgres;
After pressing F5 the magic happens and everything worked smoothly.
This is a very brief introduction for using PostgreSQL from Asp.NET application. I am sure there are lots of things to learn and apply in order to work with this open source database system.
Have a great day.
(In order to use this feature, you have to register.)