[Mono-list] Npgsql.dll (correction)

[Carl Olsen]

It seems to be working fine now since I rebooted the server, so I'm not sure
it actually slows down or just stops working.  I'm going to convert the
stored procedures from SQL to PL/pgsql right now, so I'll see if that makes
any difference.

-----Original Message-----
From: mono-list-bounces at lists.ximian.com
[mailto:mono-list-bounces at lists.ximian.com] On Behalf Of Carl Olsen
Sent: Friday, June 17, 2005 4:55 PM
To: 'Francisco Figueiredo Jr.'; mono-list at lists.ximian.com
Subject: RE: [Mono-list] Npgsql.dll (correction)

It works just fine after I reboot the server, but stopping and starting
PostgreSQL doesn't help.  That leads me to believe it's a problem with
Apache or Npgsql.  I'm going to rewrite the query, but I'll send you a copy
before I change it, so you can see what I'm trying to do now.

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using Npgsql;

namespace CarlsWebs.CarlOlsen.Web.Legislators
{
	/// <summary>
	/// Summary description for Senators.
	/// </summary>
	public class Senators : System.Web.UI.Page
	{
		protected System.Web.UI.WebControls.DropDownList
DropDownParty;
		protected System.Web.UI.WebControls.DataGrid
DataGridSenators;
		protected System.Web.UI.WebControls.HyperLink HyperLink1;

		private void Page_Load(object sender, System.EventArgs e)
		{
			if (!Page.IsPostBack)
			{
				ArrayList options = new ArrayList();
				options.Add("All");
				options.Add("Democrat");
				options.Add("Republican");
				DropDownParty.DataSource = options;
				DropDownParty.DataBind();
				NpgsqlConnection conn = new
NpgsqlConnection("Server=localhost;Port=5432;User
Id=user;Password=pswd;Database=legislators;");
				conn.Open();
				NpgsqlCommand command = new
NpgsqlCommand("select s_fname(get_senators()), s_lname(get_senators()),
s_email(get_senators())", conn);
				command.CommandType = CommandType.Text;
				NpgsqlDataReader dr =
command.ExecuteReader();
				DataGridSenators.DataSource = dr;
				DataGridSenators.DataBind();
				dr.Close();
				dr.Dispose();
				dr = null;
				conn.Close();
				conn.Dispose();
				conn = null;
			}
		}

		override protected void OnInit(EventArgs e)
		{
			InitializeComponent();
			base.OnInit(e);
		}
		
		private void InitializeComponent()
		{    
			this.DropDownParty.SelectedIndexChanged += new
System.EventHandler(this.DropDownParty_SelectedIndexChanged);
			this.Load += new
System.EventHandler(this.Page_Load);

		}

		private void DropDownParty_SelectedIndexChanged(object
sender, System.EventArgs e)
		{
			string text1 = DropDownParty.SelectedValue;
			NpgsqlConnection conn2 = new
NpgsqlConnection("Server=localhost;Port=5432;User
Id=carl;Password=ianorml;Database=legislators;");
			conn2.Open();
			string commandString = "";
			if (text1 == "All")
			{
				commandString = "select
s_fname(get_senators()), s_lname(get_senators()), s_email(get_senators())";
			}
			else
			{
				if (text1 == "Democrat")
				{
					text1 = "D";
				}
				else
				{
					text1 = "R";
				}
				commandString = "select
s_fname(get_senators('" + text1 + "')), s_lname(get_senators('" + text1 +
"')), s_email(get_senators('" + text1 + "'))";
			}
			NpgsqlCommand command2 = new
NpgsqlCommand(commandString, conn2);
			command2.CommandType = CommandType.Text;
			NpgsqlDataReader dr2 = command2.ExecuteReader();
			DataGridSenators.DataSource = dr2;
			DataGridSenators.DataBind();
			dr2.Close();
			dr2.Dispose();
			dr2 = null;
			conn2.Close();
			conn2.Dispose();
			conn2 = null;
		}	
	}
}

Here are my two stored procedure functions which I'm going to rewrite using
PL/pgsql:

Get_senators() RETURNS SETOF senate_members
SELECT * FROM senate_members;
Language SQL

Get_senators(text) RETURNS SETOF senate_members
SELECT * FROM senate_members WHERE s_party = $1;
Language SQL

-----Original Message-----
From: mono-list-bounces at lists.ximian.com
[mailto:mono-list-bounces at lists.ximian.com] On Behalf Of Francisco
Figueiredo Jr.
Sent: Friday, June 17, 2005 1:32 AM
To: mono-list at lists.ximian.com
Subject: Re: [Mono-list] Npgsql.dll (correction)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Carl Olsen wrote:
> I'm having trouble with the page timing out before it returns any data.
It
> seems to work fine after the server is rebooted, and just keeps getting
> slower each time the page is opened.
> 

Hmmmmm, this is very strange :(


Do you have some samples of query code you are doing?

I don't see right now what could be happening in Npgsql which could be
causing it to make server slower each time page is opened. Maybe with
your sample queries I can do a better investigation.

Did you try do some repeating queries on console to see if you can
reproduce this problem?


- --
Regards,

Francisco Figueiredo Jr.
Npgsql Lead Developer
http://gborg.postgresql.org/project/npgsql
MonoBrasil Project Founder Member
http://monobrasil.softwarelivre.org


- -------------
"Science without religion is lame;
religion without science is blind."

                  ~ Albert Einstein
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iQEVAwUBQrJuR/7iFmsNzeXfAQI/vQf+OLA9sFY7XlCA+DACqWe4N7k6AmAPQSdZ
pCKBxYTzu17pl/OVwzs+0ovSWQUBASmGjoT6RSkZ3G4Z88TFJf+O1fh5u0oznL09
EPNyc07G7VttoKLUqKBS40nimY4Pfgp2bI/7+1UgzPJ3qWyvZPqWQwB3NmEYliza
RARvGEj/EE2EXhhA9YEz5BCQ/PspptLhm0aLJ/azJ32foAwFx4qjSYWeJdkZxJGM
bLGDGDoGoTjN4EbmJGET6H1t5EYBrzvXKu8madrKSIyNH6zm3vYLTNfOhTZDDTLd
w8vzmaSB3UCOSu3oFxGaVhajpMA61v0r4czDKxB+bzUobU1s2pAvqA==
=4ut1
-----END PGP SIGNATURE-----

	
	
		
_______________________________________________________ 
Yahoo! Acesso Grátis - Internet rápida e grátis. 
Instale o discador agora! http://br.acesso.yahoo.com/
_______________________________________________
Mono-list maillist  -  Mono-list at lists.ximian.com
http://lists.ximian.com/mailman/listinfo/mono-list



_______________________________________________
Mono-list maillist  -  Mono-list at lists.ximian.com
http://lists.ximian.com/mailman/listinfo/mono-list