[Mono-list] MySQL / Firebird

[Michael J. Ryan]

This is a multi-part message in MIME format.
--------------080508080304090900020507
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Replace ... System.Data.SqlClient.* with ByteFX.Data.MySqlClient.*
Replace .... *.Sql* with *.MySql*

there ya go...  it is an ADO.Net provider, works about 85% the same, I
find that I have to manually do some type conversion, since 99% of my queries
are returned as a System.Data.DataTable, then from there into my own native
objects...

See attached... Rename the MyCompany.MyProject.MyData as appropriate...
Use the docs for System.Data.SqlClient

Query example:
DataTable t = DB.Query("SELECT column1,column2 FROM MyTable;");
//you can use foreach, or other methods of access, this is what I use.
//  usually using an id, or feeding it to a class()
for (int i=0; i<t.Rows.Length; i++) {
	string column1 = DB.ToString(t.Rows[i][0]);
	int column2 = DB.ToInt(t.Rows[i][1]);
	Response.Write(String.Format("{0} - {1}",column1,column2);
}

Insert/Update Example:
string SQL = "INSERT INTO Table (column1,column2) VALUES ({0},{1});"
DB.Exec( String.Format( SQL, DB.ToData(column1), DB.ToData(column2) ) );

the .ToData() is overloaded, and will wrap '' around strings, dates, and
guid's as appropriate, I like System.Guid as a key field, generate them
in .Net, and store them as a CHAR(36) ...

I hope this is helpfull...  the connection string is stored in web.config

most of what I do can be done in either .Exec(string) or .Query(string)
Exec returns the int from the native execution, Query returns a DataTable,
the methods are static, meaning you don't initialize it first... also,
the connection is isolated for late-bind, early-release methodology.


Sijmen Mulder wrote:
> Great! It's shipped with mono!
> 
> But now I am looking for documentation on it (not SQL itself ofcourse ;-]).
> Any idea where I can find it? I already did a few searches, without too 
> much results...

-- 
Michael J. Ryan - tracker1(at)theroughnecks(dot)com - www.theroughnecks.net
icq: 4935386  -  AIM/AOL: azTracker1  -  Y!: azTracker1  -  MSN/Win: (email)

--------------080508080304090900020507
Content-Type: text/plain;
 name="MyCompany.MyProject.MyData.DB.cs"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="MyCompany.MyProject.MyData.DB.cs"

using System;
using System.Collections;
using System.Data;
using ByteFX.Data.MySqlClient;
using MyCompany.MyProject.MyData;

namespace MyCompany.MyProject.MyData {
	public class DB {
		private DB() {} //Not instantiable

		private static void OpenDB(MySqlConnection conDB) {
			Context.Trace.Write("AzTravelCenter.Common.Data","OpenDB");

			conDB.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["MySiteConn"];
			conDB.Open();
		} //OpenDB

		private static void OpenReadDB(MySqlConnection conDB) {
			Context.Trace.Write("AzTravelCenter.Common.Data","OpenReadDB");

//can use a random read db, for clustered MySql Servers.. :D
//			string strReadConn = "MySiteReadConn" + (new Random()).Next(1).ToString();

//single connection string for db reads
			string strReadConn = "MySiteReadConn"
			
			conDB.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings[strReadConn];
			conDB.Open();
		} //OpenReadDB

		private static void CloseDB(MySqlConnection conDB) {
			Context.Trace.Write("AzTravelCenter.Common.Data","CloseDB");

			conDB.Close();
			conDB = null;
		} //CloseDB

		public static string ToData(decimal subject) {
			if (object.Equals(subject,null))
				return "NULL";
			else
				return String.Format("{0:0.0000}",subject);
		}

		public static string ToData(int subject) {
			if (object.Equals(subject,null))
				return "NULL";
			else
				return subject.ToString();
		}

		public static string ToData(string subject) {
			if (object.Equals(subject,null))
				return "NULL";
			else
				return "'" + subject.Replace("'","''") + "'";
		}

		public static string ToData(bool subject) {
			return (subject)?"'1'":"'0'";
		}

		public static string ToData(Guid subject) {
			if (subject == new Guid())
				return "NULL";
			else
				return "'" + subject.ToString() + "'";
		}

		public static string ToData(DateTime subject) {
			if (object.Equals(subject,null))
				return "NULL";
			else
				return string.Format("{0:yyyy-MM-dd HH:mm:ss}",subject);
		}
		
		public static Guid ToGuid(Object subject) {
			if (DBNull.Value.Equals(subject))
				return new Guid();
			else
				return new Guid(subject.ToString());
		}

		public static String ToString(Object subject) {
			if (DBNull.Value.Equals(subject))
				return "";
			else
				return subject.ToString();
		}

		public static int ToInt(Object subject) {
			if (DBNull.Value.Equals(subject))
				return 0;
			else
				return int.Parse(subject.ToString());
		}
		public static int ToInt(Object subject, int Default) {
			if (DBNull.Value.Equals(subject))
				return Default;
			else
				return int.Parse(subject.ToString());
		}

		public static decimal ToDecimal(Object subject) {
			return ToDecimal(subject, (decimal)0);
		}
		public static decimal ToDecimal(Object subject, decimal Default) {
			if (DBNull.Value.Equals(subject))
				return Default;
			else
				return decimal.Parse(subject.ToString());
		}
		
		public static DateTime ToDateTime(Object subject) {
			if (DBNull.Value.Equals(subject))
				return DateTime.MinValue;
			else
				return DateTime.Parse(subject.ToString());
		}

		public static DateTime ToDateTime(Object subject, DateTime Default) {
			if (DBNull.Value.Equals(subject))
				return Default;
			else
				return DateTime.Parse(subject.ToString());
		}

		public static bool ToBool(Object subject) {
			if (DBNull.Value.Equals(subject))
				return false;
			else
				return (subject.ToString() == "1");
		}
		public static bool ToBool(Object subject, bool Default) {
			if (DBNull.Value.Equals(subject))
				return Default;
			else
				return (subject.ToString() == "1");
		}
		public static Object NotNull(Object subject, Object Default) {
			if (DBNull.Value.Equals(subject))
				return Default;
			else
				return subject;
		} //NotNull

		public static DataTable Query(string strSQL) {
			Context.Trace.Write("AzTravelCenter.Common.Data",string.Format("Begin DTBL_Query({0})",strSQL));

			MySqlConnection conDB = new MySqlConnection();
			DataTable dtblResult = new DataTable("entity_info");
			MySqlDataAdapter dadEntityInfo = new MySqlDataAdapter(strSQL,conDB);

			OpenReadDB(conDB); //Open Database Connection
				dadEntityInfo.Fill(dtblResult); //Fill the datatable with the results
			CloseDB(conDB); //Close Database Connection

			Context.Trace.Write("AzTravelCenter.Common.Data",string.Format("End DTBL_Query({0}) (results:{1})",strSQL,dtblResult.Rows.Count));
			return dtblResult;
		} // Query

		public static int Exec(string strSQL) {
			int intRet;
			MySqlConnection conDB  = new MySqlConnection();
			MySqlCommand cmdQuery = new MySqlCommand(strSQL, conDB);
			OpenDB(conDB);
				intRet = cmdQuery.ExecuteNonQuery();
			CloseDB(conDB);

			return intRet;
		} //Exec

	} //DB class
} //MyCompany.MyProject.MyData
--------------080508080304090900020507--