Blog Post by Murat Yaşar


Posted on Sunday, October 10, 2010 6:54:57 PM and it has been read 4172 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.

/BlogEntryImages/71/databinding_1.png

After referencing the dll, I put a GridView control onto my Asp.NET form.

/BlogEntryImages/71/databinding_2.png

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.

/BlogEntryImages/71/databinding_3.png

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.)

Tag Related Blog Entries

Compare Tables Row By Row in PostgreSQL

Sunday, June 21, 2015 2   4940  

Successfully Going Live With The New yasarmurat.com

Tuesday, May 26, 2015 0   1692  

Some Helpful Links For Software Developers

Saturday, April 28, 2012 0   3527  

Show All Functions and Views In PostgreSQL By Using Select Statement And Querying pgCatalog

Saturday, February 18, 2012 0   1088  

File Is Being Used By Another Process

Monday, August 29, 2011 1   1347  

Populate Nested TreeView In Asp.Net Using Common Table Expression In SQL Server

Sunday, May 01, 2011 0   3239   5

Npgsql Data Types And Equivalents

Monday, March 21, 2011 2   3182  

PostgreSQL - PL/pgSQL Little Code Snippets

Friday, October 08, 2010 3   1779  

LINQ

Friday, October 08, 2010 0   1367  

PostgreSQL 8.4.4 Data Types

Monday, September 27, 2010 0   1014  

Indicative Exchange Rates Announced by the Central Bank of Turkey For Daily Basis

Saturday, May 22, 2010 0   861   1

Asp.Net Application Warm Up By Using Windows Service

Thursday, February 04, 2010 6   8337   6

Tuning, Optimizing, Increasing and Improving Performance of Asp.Net Application - Part III

Saturday, January 23, 2010 0   1516  

Tuning, Optimizing, Increasing and Improving Performance of Asp.Net Application - Part II

Saturday, January 23, 2010 0   3329  

Tuning, Optimizing, Increasing and Improving Performance of Asp.Net Application - Part I

Saturday, January 23, 2010 0   2733  

Syntax not understood error for robots.txt file in Google Webmaster Tools

Tuesday, December 29, 2009 2   2469