[Mono-list] Mono Database Documentation and Examples

[Joe Audette]

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 <monodanmorg at yahoo.com> 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  -  Mono-list at lists.ximian.com
> http://lists.ximian.com/mailman/listinfo/mono-list
> 


joe_audette [at] yahoo dotcom
http://www.joeaudette.com
http://www.mojoportal.com