[Mono-list] Npgsql.dll

[Carl Olsen]

I guess I should provide the code I've already written so you have a
definition for RunProcedure that actually uses Npgsql:

	public abstract class DbObject
	{
		protected NpgsqlConnection Connection;
		private string connectionString;

		public DbObject( string newConnectionString )
		{
			connectionString = newConnectionString;
			Connection = new NpgsqlConnection( connectionString
);
		}

		protected string ConnectionString
		{
			get 
			{
				return connectionString;
			}
		}

		private NpgsqlCommand BuildIntCommand(string storedProcName,
IDataParameter[] parameters)
		{
			NpgsqlCommand command = BuildQueryCommand(
storedProcName, parameters );			

			command.Parameters.Add( new NpgsqlParameter (
"ReturnValue",
				DbType.Int32 ) );

			return command;
		}

		private NpgsqlCommand BuildQueryCommand(string
storedProcName, IDataParameter[] parameters)
		{
			NpgsqlCommand command = new NpgsqlCommand(
storedProcName, Connection );
			command.CommandType = CommandType.StoredProcedure;

			foreach (NpgsqlParameter parameter in parameters)
			{
				command.Parameters.Add( parameter );
			}

			return command;
		}

		protected int RunProcedure(string storedProcName,
IDataParameter[] parameters, out int rowsAffected )
		{
			int result;

			Connection.Open();
			NpgsqlCommand command = BuildIntCommand(
storedProcName, parameters );
			rowsAffected = command.ExecuteNonQuery();
			result =
(int)command.Parameters["ReturnValue"].Value;
			Connection.Close();
			return result;
		}

		protected NpgsqlDataReader RunProcedure(string
storedProcName, IDataParameter[] parameters )
		{
			NpgsqlDataReader returnReader;

			Connection.Open();
			NpgsqlCommand command = BuildQueryCommand(
storedProcName, parameters );
			command.CommandType = CommandType.StoredProcedure;

			returnReader = command.ExecuteReader();
			//Connection.Close();
			return returnReader;
		}

		protected DataSet RunProcedure(string storedProcName,
IDataParameter[] parameters, string tableName )
		{
			DataSet dataSet = new DataSet();
			Connection.Open();
			NpgsqlDataAdapter sqlDA = new NpgsqlDataAdapter();
			sqlDA.SelectCommand = BuildQueryCommand(
storedProcName, parameters );
			sqlDA.Fill( dataSet, tableName );
			Connection.Close();

			return dataSet;
		}

		protected void RunProcedure(string storedProcName,
IDataParameter[] parameters, DataSet dataSet, string tableName )
		{
			Connection.Open();
			NpgsqlDataAdapter sqlDA = new NpgsqlDataAdapter();
			sqlDA.SelectCommand = BuildIntCommand(
storedProcName, parameters );
			sqlDA.Fill( dataSet, tableName );
			Connection.Close();			
		}
	}
}

How about some examples of inserts and updates using parameters?  I think
I'm using strings, integers, and booleans, and byte[]s for parameters.

Also, and example of NpgsqlCommand, such as would be used to call a stored
procedure?

Like:

		public User(string newConnectionString):
base(newConnectionString)
		{	}

		public int Create(string emailAddress,
			byte[] password,
			string firstName,
			string lastName,
			string address1,
			string address2,
			string city,
			string state,
			string zipCode,
			string homePhone,
			string country)
		{
			int rowsAffected;
			SqlParameter[] 
				parameters = {
								 new
SqlParameter("@EmailAddress", SqlDbType.VarChar, 255),
								 new
SqlParameter("@Password", SqlDbType.Binary, 20),
								 new
SqlParameter("@FirstName", SqlDbType.VarChar, 30),
								 new
SqlParameter("@LastName", SqlDbType.VarChar, 50),
								 new
SqlParameter("@Address1", SqlDbType.VarChar, 80),
								 new
SqlParameter("@Address2", SqlDbType.VarChar, 80),
								 new
SqlParameter("@City", SqlDbType.VarChar, 40),
								 new
SqlParameter("@State", SqlDbType.VarChar, 2),

								 new
SqlParameter("@ZipCode", SqlDbType.VarChar, 10),
								 new
SqlParameter("@HomePhone", SqlDbType.VarChar, 14),
								 new
SqlParameter("@Country", SqlDbType.VarChar, 50),
								 new
SqlParameter("@UserID", SqlDbType.Int, 4)
							 };

			parameters[0].Value = emailAddress;
			parameters[1].Value = password;
			parameters[2].Value = firstName;
			parameters[3].Value = lastName;
			parameters[4].Value = address1;
			parameters[5].Value = address2;
			parameters[6].Value = city;
			parameters[7].Value = state;
			parameters[8].Value = zipCode;
			parameters[9].Value = homePhone;
			parameters[10].Value = country;
			parameters[11].Direction =
ParameterDirection.Output;
	
			try 
			{
				RunProcedure("sp_Accounts_CreateUser",
parameters, out rowsAffected);
			}
			catch ( SqlException e )
			{
				// 2601 is the number returned when the
primary key
				// is violated. We know if we're violating
the primary
				// key that the e-mail address is already on
file.
				// Its cheaper than actually searching for
that address before
				// inserting.
				if (e.Number == 2601) 
				{
					return
(int)CarlsWebs.WebModules.Accounts.ProcResultCodes.AccountAlreadyOnFile;
				}
				else 
				{
					throw new AppException("An error
occurred while executing the Accounts_CreateUser stored procedure",
						e );
				}
			}

			return (int)parameters[11].Value;
		}
Carl

-----Original Message-----
From: mono-list-bounces at lists.ximian.com
[mailto:mono-list-bounces at lists.ximian.com] On Behalf Of Chris Aitken
Sent: Tuesday, June 07, 2005 9:24 AM
To: carl at carl-olsen.com; mono-list at lists.ximian.com
Subject: RE: [Mono-list] Npgsql.dll

> It's me again, back to try and write some SPROC.  I want to 
> try the second method (PL/PgSQL), but I'm assuming you get 
> back two arrays each with the same index, one for the 
> customername and one for the postcode.  Is that how you 
> capture the returned data in a dataset?
> 

Hi Carl,

If you set up as per the original example. It will return a set - i.e. a
table, much the same as a pure SQL query. My example here returns (within
pgAdminIII):

Row	customername(varchar)	postcode(varchar)
1	data				data
2	data				data
...
...
N	data				data

Obviously the above is substituted for the actual data (apart from row
numbers). As I mentioned, is is returning a set of 'type', where in this
case the 'type' is called 'tp_company' is 2 columns of varchar, one named
customername, the other postcode. The type must be defined first:

	CREATE TYPE tp_company AS
		(customername varchar,
		postcode varchar);

Now the actual SPROC is written:

	CREATE FUNCTION plpgsql_company()
		RETURNS SETOF tp_company AS
		'
		DECLARE
			r tp_company%rowtype;
		BEGIN
			FOR r IN SELECT customername,postcode FROM
tblcustomerinformation LOOP
				RETURN NEXT r;
			END LOOP;
			RETURN;
		END
		'
	LANGUAGE 'plpgsql' VOLATILE;


As can be seen, the function is defined as returning a set of 'tp_company'.
We then delare 'r' as being a row of 'tp_company'.

Then for each 'r' (row of 'tp_company', remember) in the SQL query, return
it, then loop until the end.
Return the whole shebang.
End

Hope this helps.

If you want me to write you an example for your data, let me know which
columns you want, table names & column types.

Chris


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

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