[Mono-list] Npgsql.dll

[Carl Olsen]

Hi Chris,

Thanks for the example.  I've downloaded a bunch of stuff from various web
sites, including the Npgsql manual, so I'll be doing some reading this
weekend.

I'm not having any trouble with making a direct connection from a page.  I'm
just trying the abstract the database connections, and I really can't say
I'm having any problems yet.  I just haven't finished writing all the code.
I'll get back to you once I start getting some actual error messages.

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: Wednesday, June 08, 2005 7:05 AM
To: carl at carl-olsen.com
Cc: mono-list at lists.ximian.com
Subject: RE: [Mono-list] Npgsql.dll

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

Inserting and updating is pretty simple.

Once you have written your sproc, you call it as:

SELECT update_sproc("string",int,"bool","byte[]s")

An app I wrote had:

Public static void Update ()
{
	OpenCon();

	string strSQL;
	int result;

	strSQL = SELECT update_squash(" + "" + playername + "" + "," +
playedtoday + "," + wontoday + ")";

	NpgsqlCommand UpdateCmd = new NpgsqlCommand(strSQL,connSquash);

	result = (int)UpdateCmd.ExecuteScalar();

	if (result ==1)
		Console.Writeline("Update Successful");
	else
		Console.Writeline("Oops!");

	CloseCon();
}

The sproc update_squash tool a players name(string), their games played that
day (int), and their games won that day (int). The sproc returned a 1 for
success, or a 0 for a FUBAR. I'll try to dig out the actual pl/PgSQL if
possible.




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

Hopefully the above is a good enough example of how to call the command?
I'll email you the entire C# file if you wish, for the above example?

> 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.AccountAlre
> adyOnFile;
> 				}
> 				else 
> 				{
> 					throw new AppException("An error
> occurred while executing the Accounts_CreateUser stored procedure",
> 						e );
> 				}
> 			}
> 
> 			return (int)parameters[11].Value;
> 		}

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