Daniel, The code you are seeking is really just C# code nothing really specific to mono per se.
In my mojoportal web site framework I am supporting MS SQL, MySQL (using the MySQL Connector for .NET), and PostgreSQL (using npgsql) and have plenty of good code I'd be happy to share. You can download the source at http://forge.novell.com/modules/xfmod/project/?mojoportal and find out more about the project at http://mojoportal.com Each layer has a helper class that simplifies the standard ADO.NET code, ie SQLHelper, MySQLHelper npgSQLHelper The npgSqlHelper class was written by Joseph Hill for mojoportal, I suggested he push it upstream to the npgsql project but not sure whether he did, I'm not using the very newest npgsql code yet. Here is sample CRUD code for npgsl using the helper class and stored procedures public static int Role_Create( int SiteID, string RoleName) { NpgsqlConnection Connection = GetConnection(); NpgsqlParameter[] arParams = new NpgsqlParameter[2]; if(ConfigurationSettings.AppSettings.Get("CachePostgreSQLParameters").ToLower() == "true") { arParams = NpgsqlHelperParameterCache.GetSpParameterSet(ConfigurationSettings.AppSettings.Get("PostgreSQLConnectionString"), "mp_roles_insert(:siteid,:rolename)"); arParams[0].Value = SiteID; arParams[1].Value = RoleName; } else { arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = SiteID; arParams[1] = new NpgsqlParameter("rolename", NpgsqlTypes.NpgsqlDbType.Text,50); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = RoleName; } try { int newID = Convert.ToInt32(NpgsqlHelper.ExecuteScalar(Connection, CommandType.StoredProcedure, "mp_roles_insert(:siteid,:rolename)", arParams)); Connection.Close(); return newID; } catch(Exception ex) { Connection.Close(); throw ex; } } public static bool Role_Update(int RoleID, string RoleName) { NpgsqlConnection Connection = GetConnection(); NpgsqlParameter[] arParams = new NpgsqlParameter[2]; if(ConfigurationSettings.AppSettings.Get("CachePostgreSQLParameters").ToLower() == "true") { arParams = NpgsqlHelperParameterCache.GetSpParameterSet(ConfigurationSettings.AppSettings.Get("PostgreSQLConnectionString"), "mp_roles_update(:roleid,:rolename)"); arParams[0].Value = RoleID; arParams[1].Value = RoleName; } else { arParams[0] = new NpgsqlParameter("roleid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = RoleID; arParams[1] = new NpgsqlParameter("rolename", NpgsqlTypes.NpgsqlDbType.Text,50); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = RoleName; } try { int rowsAffected = NpgsqlHelper.ExecuteNonQuery(Connection, CommandType.StoredProcedure, "mp_roles_update(:roleid,:rolename)", arParams); Connection.Close(); return (rowsAffected > -1); } catch(Exception ex) { Connection.Close(); throw ex; } } public static IDataReader Role_GetByID(int RoleID) { NpgsqlParameter[] arParams = new NpgsqlParameter[1]; if(ConfigurationSettings.AppSettings.Get("CachePostgreSQLParameters").ToLower() == "true") { arParams = NpgsqlHelperParameterCache.GetSpParameterSet(ConfigurationSettings.AppSettings.Get("PostgreSQLConnectionString"), "mp_roles_selectone(:roleid)"); arParams[0].Value = RoleID; } else { arParams[0] = new NpgsqlParameter("roleid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = RoleID; } return NpgsqlHelper.ExecuteReader( ConfigurationSettings.AppSettings.Get("PostgreSQLConnectionString"), CommandType.StoredProcedure, "mp_roles_selectone(:roleid)", arParams); } -------------- Here is the same crud for MySQL using the MySQL Connector for .NET public static int Role_Create( int SiteID, string RoleName) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("INSERT INTO mp_Roles (SiteID, RoleName, DisplayName) "); sqlCommand.Append("VALUES ("); sqlCommand.Append(" ?SiteID , ?RoleName, ?RoleName"); sqlCommand.Append(");"); sqlCommand.Append("SELECT LAST_INSERT_ID();"); MySqlParameter[] arParams = new MySqlParameter[2]; arParams[0] = new MySqlParameter("?SiteID", MySqlDbType.Int32); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = SiteID; arParams[1] = new MySqlParameter("?RoleName", MySqlDbType.VarChar, 50); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = RoleName; MySqlConnection connection = GetConnection(); try { int newID = Convert.ToInt32(MySqlHelper.ExecuteScalar(connection, sqlCommand.ToString(), arParams).ToString()); connection.Close(); return newID; } catch(Exception ex) { connection.Close(); throw ex; } } public static bool Role_Update(int RoleID, string RoleName) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("UPDATE mp_Roles "); sqlCommand.Append("SET DisplayName = ?RoleName "); sqlCommand.Append("WHERE RoleID = ?RoleID ;"); MySqlParameter[] arParams = new MySqlParameter[2]; arParams[0] = new MySqlParameter("?RoleID", MySqlDbType.Int32); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = RoleID; arParams[1] = new MySqlParameter("?RoleName", MySqlDbType.VarChar, 50); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = RoleName; int rowsAffected = 0; MySqlConnection connection = GetConnection(); try { rowsAffected = MySqlHelper.ExecuteNonQuery(connection, sqlCommand.ToString(), arParams); connection.Close(); } catch(Exception ex) { connection.Close(); throw ex; } return (rowsAffected > 0); } public static IDataReader Role_GetByID(int RoleID) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_Roles "); sqlCommand.Append("WHERE RoleID = ?RoleID ; "); MySqlParameter[] arParams = new MySqlParameter[1]; arParams[0] = new MySqlParameter("?RoleID", MySqlDbType.Int32); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = RoleID; return MySqlHelper.ExecuteReader( ConfigurationSettings.AppSettings.Get("mySqlConnectionString"), sqlCommand.ToString(), arParams); } I avoid use of DataSet if I can help it but would be glad to produce some sample code if needed. Let me know if any of this is helpful or you'd like me to put together some more samples. Cheers, Joe --- Daniel Morgan <[EMAIL PROTECTED]> wrote: > After seeing programmers struggling to connect to > various databases using Mono, I realized we need > some documentation and examples to help people get > started. > > For instance, I could not find in a reasonable > amount of time to figure out how to use parameters > with ODBC to get data from a PostgreSQL database. > > Does anyone have working examples to contribute? I > would like to add them to Mono's wiki. > > http://www.mono-project.com/Database_Access > > Other examples could include using gtk# to load a > TreeView with data from a PostgreSQL database would > be nice. > > Or how to execute a stored procedure on PostgreSQL > using the Npgsql provider. > > Or populating an ASP.NET data grid via a DataSet > that was populated from SQL Lite or MySQL. > > Examples for various databases including Sybase, > Oracle, SQL Server, MySQL, SQL Lite, ODBC, ADP > (Advanced Data Provider), etc... > > Thanks, > Daniel > > > > --------------------------------- > Start your day with Yahoo! - make it your home page > _______________________________________________ > Mono-list maillist - [email protected] > http://lists.ximian.com/mailman/listinfo/mono-list > joe_audette [at] yahoo dotcom http://www.joeaudette.com http://www.mojoportal.com _______________________________________________ Mono-list maillist - [email protected] http://lists.ximian.com/mailman/listinfo/mono-list
