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)
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
